python使用xlwt形成合并单元格的excel并且读取合并单元格的excel

妖狐艹你老母 2023-06-12 09:50 16阅读 0赞

**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月份的"累计应发数"、"累计专项扣除-五险一金"、"累计免税收入"、"累计扣个税"这些指标。最终展示结果如下图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70][]

**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、运行结果如下：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70 1][]

参考:  [python处理excel合并单元格][python_excel]  
          https://blog.csdn.net/wangsiji\_buaa/article/details/86094328

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191112130851661.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2019111213144715.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlbGxveGlhb3poZQ==,size_16,color_FFFFFF,t_70
[python_excel]: https://www.wxy.email/2019/05/06/python%E5%A4%84%E7%90%86%E5%90%88%E5%B9%B6%E5%8D%95%E5%85%83%E6%A0%BC/