1、python处理Excel，读取原有表格格式，写入数据，并写入公式
前置条件：excel 支持.xls、待写入表格、数据
2、
import xlwtimport xlrdfrom xlutils import copyfrom xlutils.filter import process, XLRDReader, XLWTWriterimport timeimport datetimedef write_excel_xls_append(value):index = len(value) # 获取需要写入数据的行数style_list = w.style_listfor n, sheet in enumerate(workbook_rb.sheets()):new_worksheet = workbook_wb.get_sheet(n) # 获取转化后工作簿中的第一个表格for i in range(0, index):#读取原有单元格格式stylestyle_gongshi = style_list[sheet.cell_xf_index(i + 5, 5)]style_gongshi1 = style_list[sheet.cell_xf_index(i + 5, 6)]for j in range(0, len(value[i])):print(value[i])# 由于我们这excel前几行是标题、列名等。所以需要从特定行、列写入，顺延。style_danshuju = style_list[sheet.cell_xf_index(i + 5, j+1)] # 获取当前单元格的style# 写入数据（value是list数据，所以用下角标取）print((value[i][j]))print(i,type(i), j)new_worksheet.write(i + 5, j+1, (value[i][j]), style_danshuju)print('写入数据成功')# 添加公式new_worksheet.write(i + 5, 5, xlwt.Formula('SUM(B{}:E{})'.format(i+6, i+6)), style_gongshi)print('公式写入成功')new_worksheet.write(i + 5, 6, xlwt.Formula('E{}-B{}'.format(i + 6, i + 6)), style_gongshi1)print('公式1写入成功')return Truedef add_formula(r, l, formula):new_worksheet = workbook_wb.get_sheet(0)new_worksheet.write(r, l, xlwt.Formula(formula))return Trueif __name__ == '__main__':savepath = 'D:/wqh/python/pythonProject_write_excel/excel表格.xls' # 已有xls的路径data = [[12, 34, 44, 55], [22, 34, 44, 565], [12, 34, 44, 55], [22, 34, 44, 565]]workbook_rb = xlrd.open_workbook(savepath, formatting_info=True) # 打开工作簿得到文件，formatting_info=True必须加才可以保存w = XLWTWriter()process(XLRDReader(workbook_rb, 'unknown.xls'), w)workbook_wb = w.output[0][1]#写入数据write_excel_xls_append(data)workbook_wb.save(savepath)
3、查看运行结果
从王5开始一行一行写入数据，写一行数据，并把当前行的公式写入
