1.读Excel
import xlrd
data = xlrd.open_workbook(self.filename) # 打开文件
names = data.sheet_names() # 获取当前Excel中所有的sheet的名字,(列表形式)
table = data.sheet_by_name(name) #根据sheet的name获取工作表
table = data.sheets()[0] # 获取book中的某个工作表
rowCount = table.nrows # 获取工作表的总行数
colCount = table.nclos # 获取工作表的总列数
rowData = table.row_values(row) # 获取某一行的所有数据
colData = table.col_values(col) # 获取某一列的所有数据
for row in range(1, nrows):
rowdata = table.row_values(row) # 循环获取每一行数据,然后取每一行中的列
col1 = rowdata[0]
col2 = rowdata[1]
......
2.写Excel
import xlwt workbook = xlwt.Workbook(encoding='ascii') worksheet = workbook.add_sheet('My Worksheet') workbook.save('Excel_Workbook.xls') worksheet.write(line, 0, str(sum1)) # row,col,val workbook.save('Excel_Workbook.xls') # 保存
import os import xlrd import pandas from openpyxl import load_workbook data = xlrd.open_workbook("test.xlsx") table = data.sheets()[0] # 选定表 nrows = table.nrows # 获取行号 columns = table.row_values(0) print(columns) for row in range(1, nrows): alldata = table.row_values(row) # 循环输出excel表中每一行,即所有数据 print(alldata) toFile = './tofile.xlsx' file_is_exist = os.path.exists(toFile) df = pandas.DataFrame([alldata], columns=columns) writer = pandas.ExcelWriter(toFile, engine='openpyxl') if not file_is_exist: # 为了加标题header df.to_excel( writer, header=True, sheet_name="test", index=False, # 列最左侧索引 columns=columns ) else: curBook = load_workbook(toFile) row_start = curBook["test"].max_row print(row_start) writer.book = curBook # print(dict((ws.title, ws) for ws in curBook.worksheets)) writer.sheets = dict((ws.title, ws) for ws in curBook.worksheets) df.to_excel( writer, header=False, sheet_name="test", index=False, startrow=row_start, ) writer.save()