前言
使用xlrd工具对excel表格进行处理,并使用pandas工具重新生成新的excel表格
使用
任务需求
处理前:
处理后:
- 注
表格未作样式处理,如有需求,可参考此篇文章python使用xlutils模块生成excel相关格式的文件
代码展示
# -*- coding: utf-8 -*-
import xlrd
import pandas as pd
import numpy as np
redaExcel = xlrd.open_workbook("confirm.xls")
copyTable = redaExcel.sheet_by_index(0)
readTableData = []
for i in range(1, copyTable.nrows):
retailer = copyTable.cell(i, 1).value
price = copyTable.cell(i, 3).value
weight = copyTable.cell(i, 4).value
info = {"retailer": retailer, "price": price, "weight": weight}
readTableData.append(info)
getAllData = [list(info.keys()) + ["totalValue"]]
for j in range(len(readTableData)):
getRowData = list(readTableData[j].values())
getAllData.append(getRowData + [getRowData[-1] * getRowData[-2]])
data = np.asarray(getAllData)
# 方法一
writeInfo = [list(data[:, 0][1::]), list(data[:, 1][1::]), list(data[:, 2][1::])]
index = pd.MultiIndex.from_arrays(writeInfo, names=tuple(list(data[0, :][:-1])))
writeInfoToExcel = pd.DataFrame({'{}'.format(list(data[0, :])[-1]): list(data[:, 3][1::])}, index=index)
print(writeInfoToExcel)
writeInfoToExcel.to_excel('debug.xls')
# 方法二
# writeInfoToExcel = pd.DataFrame({'{}'.format(list(data[0, :])[0]): list(data[:, 0][1::]),
# '{}'.format(list(data[0, :])[1]): list(data[:, 1][1::]),
# '{}'.format(list(data[0, :])[2]): list(data[:, 2][1::]),
# '{}'.format(list(data[0, :])[3]): list(data[:, 3][1::]),
# }
# )
# print(writeInfoToExcel)
# writeInfoToExcel.to_excel('debug.xls')
补充
python之numpy的使用
python之pandas的使用
python使用xlrd模块读取excel文件内容
结语
参考python官方指导手册: python-excel