python使用xlwt形成合并单元格的excel并且读取合并单元格的excel
在日常数据报表数据处理中，经常会遇到看某个大类下面每个小类的各自的情况，此时形成的合并一些单元格作为表头的excel。在excel中，手动合并单元格比较容易，但是怎么利用python构造某些列或者行合并作为标题行或列的excel呢？废话不多说，直接上代码。
1、安装python处理excel需要的包
安装命令:
pip install xlwt
pip install xlrd
查看安装版本:
pip list
Package Version
---------------- -----------
………… …………
………… …………
xlrd 1.2.0
xlwt 1.3.0
2、 需求描述如下图所示：即按照序号+姓名，汇总下每个人1月份到5月份的"累计应发数"、"累计专项扣除-五险一金"、"累计免税收入"、"累计扣个税"这些指标。最终展示结果如下图：
3、代码如下:
# coding:utf-8
"""
File Name: mergeExcelCell.py
Function: 实现 产出复杂表头并且读取复杂表头的 excel 的工具
Comments: 将计算结果形成复杂结构的excel，从而方便对数据的分析和使用
Author: yyz
Update Time: 2019-11-12 10:34:16
"""
# 系统包
import xlwt
import xlrd
import sys
reload(sys)
sys.setdefaultencoding("utf-8")
class MergeExcelWriteData(object):
def __init__(self):
self.f = xlwt.Workbook()
self.sheet1 = self.f.add_sheet('sheet1', cell_overwrite_ok=True)
# 准备相关表头及合并项
self.headers = [u'序号', u'姓名', u'列表', u'1月', u'2月', u'3月', u'4月', u'5月']
self.name = [u'1-AAA', u'2-BBB', u'3-CCC', u'4-DDD']
self.status = [u'累计应发数', u'累计专项扣除-五险一金', u'累计免税收入', u'累计扣个税']
# 列式数据，数据形式是以列存储的
self.col_data_list = [
[10100, 1500, 2500, 100, 20000, 2500, 3500, 200, 30000, 3500, 4500, 300, 40000, 4500, 5500, 400],
[10200, 1600, 2600, 200, 20200, 2600, 3600, 300, 40000, 3600, 4600, 400, 40200, 4600, 5600, 410],
[10300, 1700, 2700, 300, 20300, 2700, 3700, 400, 50000, 3700, 4700, 500, 40300, 4700, 5780, 450],
[10400, 1800, 2800, 400, 20400, 2800, 3800, 500, 60000, 3800, 4800, 600, 40400, 4800, 5900, 470],
[10500, 1900, 2900, 500, 20500, 2900, 3900, 600, 70000, 3900, 4900, 700, 40600, 4900, 5580, 490]
]
self.col_data_save_path = "col_data_list.xls"
# 行式数据，数据形式是以行存储的
self.row_data_list = [
[10100, 10200, 10300, 10400, 10500],
[1500, 1600, 1700, 1800, 1900],
[2500, 2600, 2700, 2800, 2900],
[100, 200, 300, 400, 500],
[20000, 20200, 20300, 20400, 20500],
[2500, 2600, 2700, 2800, 2900],
[3500, 3600, 3700, 3800, 3900],
[200, 300, 400, 500, 600],
[30000, 40000, 50000, 60000, 70000],
[3500, 3600, 3700, 3800, 3900],
[4500, 4600, 4700, 4800, 4900],
[300, 400, 500, 600, 700],
[40000, 40200, 40300, 40400, 40600],
[4500, 4600, 4700, 4800, 4900],
[5500, 5600, 5780, 5900, 5580],
[400, 410, 450, 470, 490]
]
self.row_data_save_path = "row_data_list.xls"
def write_header_data(self):
# 1）、生成第一行header
for pos in range(0, len(self.headers)):
self.sheet1.write(0, pos, self.headers[pos])
# 2）、生成序号、姓名单元格
i, j = 1, 0
while i < 10*len(self.name) and j < len(self.name):
print "@@@@@@@@@@@@ %s @@@@@@@@@@@@@" % str(i)
print '分别将前两列的 第"%s"列 到 第"%s"列合并，并将数据 "%s" 写入到合并后的第 "%s" 单元格，同时将数据 "%s" 写入到合并后的第 "%s" 单元格' % (str(i), str(i+3), str(self.name[j].split('-')[0]), str(0+1), str(self.name[j].split('-')[1]), str(1+1))
self.sheet1.write_merge(i, i+3, 0, 0, self.name[j].split('-')[0])
self.sheet1.write_merge(i, i+3, 1, 1, self.name[j].split('-')[1])
i += 4
j += 1
# 3）、生成列表
column0 = [11, 22, 33, 44]
i = 0
while i < 4*len(column0):
print "***** %s *****" % str(i+1)
for j in range(0, len(self.status)):
print '将 "%s" 写入第 %s 行，第 %s 列 ' % (str(self.status[j]), str(j+i+1), str(2))
self.sheet1.write(j+i+1, 2, self.status[j])
i += 4
def write_col_data_into_excel(self, in_col_data_list, start_row_pos=1, start_col_pos=3):
for p in range(len(in_col_data_list)):
print "***** %s *****" % str(p+1)
for q in range(0, len(in_col_data_list[p])):
print '将 "%s" 写入第 %s 行，第 %s 列 ' % (str(in_col_data_list[p][q]), str(q + start_row_pos), str(p + start_col_pos))
self.sheet1.write(q+1, p+3, in_col_data_list[p][q])
print in_col_data_list
print len(in_col_data_list) # 5行
print len(in_col_data_list[1]) # 16列
# 保存
self.f.save(self.col_data_save_path)
def run_main_for_col_data_merge_excel(self):
"""
将列式数据写入合并列或者行的excel
:return:
"""
print "@@@@@@@@@@@@@@@@@@@@@@@@@@@开始将列数据写入excel@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@"
self.write_header_data()
self.write_col_data_into_excel(in_col_data_list=self.col_data_list, start_row_pos=1, start_col_pos=3)
print "@@@@@@@@@@@@@@@@@@@@@@@@@@@完成将列数据写入excel@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@"
def run_main_for_row_data_merge_excel(self):
"""
将行式数据写入合并列或者行的excel
:return:
"""
print "@@@@@@@@@@@@@@@@@@@@@@@@@@@开始将行数据写入excel@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@"
self.write_header_data()
self.write_row_data_into_excel(in_row_data_list=self.row_data_list, start_row_pos=1, start_col_pos=3)
print "@@@@@@@@@@@@@@@@@@@@@@@@@@@完成将行数据写入excel@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@"
def transfer_col_data_to_row_data(self):
"""
将列式excel转换成行式excel
:return:
"""
in_col_data_list = self.col_data_list
# 列数据转行数据
row_data_list = []
for ii in range(len(in_col_data_list[0])):
temp_list = []
for jj in range(len(in_col_data_list)):
temp_list.append(in_col_data_list[jj][ii])
row_data_list.append(temp_list)
print "转化前的列数据：\n", in_col_data_list
print "转化前的行数据：\n", row_data_list
return row_data_list
def write_row_data_into_excel(self, in_row_data_list, start_row_pos=1, start_col_pos=3):
for p in range(len(in_row_data_list)):
print "***** %s *****" % str(p+1)
for q in range(0, len(in_row_data_list[p])):
print '将 "%s" 写入第 %s 行，第 %s 列 ' % (str(in_row_data_list[p][q]), str(p+start_row_pos), str(q+start_col_pos))
self.sheet1.write(p+1, q+3, in_row_data_list[p][q])
print in_row_data_list
print len(in_row_data_list) # 16行
print len(in_row_data_list[1]) # 5列
# 保存
self.f.save(self.row_data_save_path)
def read_merged_excel(self, in_excel_name):
"""
读取列或者行合并的excel的数据
:param in_excel_name:
:return:
"""
# 获取数据
data = xlrd.open_workbook(in_excel_name)
# 获取所有sheet名字
sheet_names = data.sheet_names()
for sheet in sheet_names:
# 获取sheet
table = data.sheet_by_name(sheet)
# 获取总行数
nrows = table.nrows # 包括标题
# 获取总列数
ncols = table.ncols
# 计算出合并的单元格有哪些
colspan = {}
if table.merged_cells:
for item in table.merged_cells:
for row in range(item[0], item[1]):
for col in range(item[2], item[3]):
# 合并单元格的首格是有值的，所以在这里进行了去重
if (row, col) != (item[0], item[2]):
colspan.update({(row, col): (item[0], item[2])})
print "************************开始按行读取数据并且进行输出\n"
# 读取每行数据
for i in range(1, nrows):
row = []
for j in range(ncols):
# 假如碰见合并的单元格坐标，取合并的首格的值即可
if colspan.get((i, j)):
row.append(table.cell_value(*colspan.get((i, j))))
else:
row.append(table.cell_value(i, j))
print(row)
print "\t".join([str(x) for x in row])
print "************************完成按行读取数据并且进行输出\n"
print "************************完成按列读取数据并且进行输出"
# 读取每列数据
for j in range(ncols):
col = []
for i in range(1, nrows):
# 假如碰见合并的单元格坐标，取合并的首格的值即可
if colspan.get((i, j)):
col.append(table.cell_value(*colspan.get((i, j))))
else:
col.append(table.cell_value(i, j))
print(col)
print "\t".join([str(x) for x in col])
print "************************完成按列读取数据并且进行输出"
def write_excel(self, in_data_list, in_save_excel_name='data.xls'):
"""
将传入的list类型的数据写入到excel中
:param in_data_list:
:param in_save_excel_name:
:return:
"""
xls = xlwt.Workbook()
sheet = xls.add_sheet('sheet', cell_overwrite_ok=True)
for row in range(len(in_data_list)):
for col in range(len(in_data_list[0])):
sheet.write(row, col, str(in_data_list[row][col]))
xls.save(in_save_excel_name)
if __name__ == '__main__':
obj_MergeExcelWriteData = MergeExcelWriteData()
print "demo1、将列式数据写入到合并列或者行的excel中"
obj_MergeExcelWriteData.run_main_for_col_data_merge_excel()
print "demo2、将行式数据写入到合并列或者行的excel中"
obj_MergeExcelWriteData.run_main_for_row_data_merge_excel()
print "demo3、读取合并列或者合并行的excel"
obj_MergeExcelWriteData.read_merged_excel(in_excel_name="col_data_list.xls")
print "demo4、实现[[1,2,3],[4,5,6]]类型的转置：可将列式数据转换为行式数据"
obj_MergeExcelWriteData.transfer_col_data_to_row_data()
print "demo5、简单将数据写入excel中"
obj_MergeExcelWriteData.write_excel(in_data_list=[['title1', 'title2', 'title2'], [111, 222, 333], ['aaa', 'bbb', 'ccc']])
4、运行结果如下：
参考: python处理excel合并单元格
https://blog.csdn.net/wangsiji_buaa/article/details/86094328