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