python中利用xlrd与xlwd处理Excel数据

python中利用xlrd与xlwd处理Excel数据

代码参考来源：https://www.jb51.net/article/136353.htm

文件一：提取Excel表格前10行

import xlrd
"""
提取Excel表格前10行
"""

#填写路径
data = xlrd.open_workbook('milkPowderData.xlsx')

#打开第0张表
table = data.sheets()[0]

nrows = table.nrows

for i in range(10):
    print(table.row_values(i)[0])

文件二：对一张表格中按行输出，按列输出，单个位置值输出

#!  conda env
# -*- coding:utf-8 -*-
# @Time:2020/4/6 8:44 上午
# @Author : nishizzma

import xlrd

workbook = xlrd.open_workbook('milkPowderData.xlsx')
#抓取所有sheet页的标签
worksheets = workbook.sheet_names()
print("worksheets is %s" %worksheets)

#定位到sheet1
worksheet1 = workbook.sheet_by_index(0)

#提取行数
num_rows = worksheet1.nrows

for curr_row in range(num_rows):
    row = worksheet1.row_values(curr_row)
    print("row%s is %s" %(curr_row,row))

#提取列数
num_cols = worksheet1.ncols

for curr_col in range(num_cols):
    col = worksheet1.row_values(curr_col)
    print("col%s is %s" %(curr_col,col))

#可以指定输出,这里可以考虑将Excel表格数据放入矩阵中，结合numpy，pandas进行数据处理
for rown in range(10):
    for coln in range(10):
        cell = worksheet1.cell_value(rown,coln)
        print("%s %s %s" %(rown,coln,cell))

文件三：如何创建和写入Excel表格

#!  conda env
# -*- coding:utf-8 -*-
# Time:2020/4/6 9:09 上午
# Author : nishizzma
# File : ExcelUse_3.py

"""
如何创建和写入Excel表格
"""

import  xlwt

workbook = xlwt.Workbook()

#cell_overwrite_ok用于确认同一个cell里面的值是否可以重填
sheet1 = workbook.add_sheet('sheet1',cell_overwrite_ok=True)
sheet2 = workbook.add_sheet('sheet2',cell_overwrite_ok=True)

sheet1.write(0,0,'this should overwrite1')
sheet1.write(0,1,'aaaaaaaaaaaa')
sheet2.write(0,0,'this should overwrite2')
sheet2.write(1,2,'bbbbbbbbbbbbb')

#保存文件，为当下目录
workbook.save('xlwtWrite.xlsx')
print("success!")

文件四：向Excel表格中写入数据

#!  conda env
# -*- coding:utf-8 -*-
# Time:2020/4/6 9:27 上午
# Author : nishizzma
# File : ExcelUse_4.py
"""
向Excel表格中写入数据
"""

import xlrd
import xlutils.copy

#读入数据
rb = xlrd.open_workbook('xlwtWrite.xlsx')
wb = xlutils.copy.copy(rb)

#获取sheet对象，通过sheet_by_index()获取的sheet对象没有write()方法
ws = wb.get_sheet(0)

#写入数据
ws.write(1,1,'changed!')

#添加sheet页
wb.add_sheet('sheetnnn2',cell_overwrite_ok=True)
#保存并覆盖
wb.save('xlwtWrite.xlsx')