Dataquality/Dataquality/BasicMethods/excel_tools.py

#!/usr/bin/env python
# -*- coding:utf-8 -*-
# author : zhaolongyue
#date : 2021/4/28

import xlsxwriter
import pandas as pd
import openpyxl


class ExcelUtil:
    @staticmethod
    def writer_excel(path, sheet_name, title_name, alist, vlist):
        """
		mongo数据写入excel
		:param path:  路径
		:param vlist: mongo游标
		:param columns: 所需字段
		:param headings: 表头
		:return:
		"""
        workbook = xlsxwriter.Workbook(path, {'constant_memory': True, 'strings_to_urls': False})
        worksheet = workbook.add_worksheet(name=sheet_name)
        # 加了格式会慢
        header_format = workbook.add_format({
            'font_size': 12,  # 字体
            'bold': True,  # 加粗
            # 'text_wrap': True,#换行
            'align': 'center',  # 居中
            'valign': 'vcenter',
            # 'fg_color': '#D7E4BC',#颜色
            # 'border': customer_program#边框
        })
        title_format = workbook.add_format({  # 样式设置
            'font_size': 16,  # 字体
            'bold': True,  # 加粗
            'text_wrap': True,  # 换行
            'align': 'center',  # 水平居中
            'valign': 'vcenter',  # 垂直居中
            # 'fg_color': '#D7E4BC',    # 颜色
            # 'border': customer_program   # 边框
        })
        count = 2
        worksheet.merge_range('A1:U1', title_name, title_format)
        # worksheet.write_row(customer_program, 0, headings,header_format)

        # 写入表头并添加格式
        for row_header, rowdata_header in enumerate(alist):
            for col_header, coldata_header in enumerate(rowdata_header):
                if row_header == 1:
                    worksheet.write(row_header, col_header, coldata_header, header_format)
        for item in alist:
            value = []
            i = 0
            for k in vlist:
                sum = 0
                for key in item:
                    sum += 1
                    if k == key:
                        value.insert(i, item[key])
                        break
                    elif sum == len(item) and k != key:
                        value.insert(i, '')
                i += 1
            data = value
            worksheet.write_row(count, 0, data)
            count += 1
            if count % 1000 == 0:
                print(count)
            worksheet.set_column('A:A', 10, header_format)  # 指定列设置样式
        workbook.close()

    @staticmethod
    def add_excel(path, sheet_name, title_name, alist, vlist):
        """
		mongo数据写入excel
		:param path:  路径
		:param vlist: mongo游标
		:param columns: 所需字段
		:param headings: 表头
		:return:
		"""
        workbook = xlsxwriter.Workbook(path, {'constant_memory': True, 'strings_to_urls': False})
        worksheet = workbook.add_worksheet(name=sheet_name)  # 加了格式会慢
        header_format = workbook.add_format({
            'font_size': 12,  # 字体
            'bold': True,  # 加粗
            # 'text_wrap': True,#换行
            'align': 'center',  # 居中
            'valign': 'vcenter',
            # 'fg_color': '#D7E4BC',#颜色
            # 'border': customer_program#边框
        })
        title_format = workbook.add_format({  # 样式设置
            'font_size': 16,  # 字体
            'bold': True,  # 加粗
            'text_wrap': True,  # 换行
            'align': 'center',  # 水平居中
            'valign': 'vcenter',  # 垂直居中
            # 'fg_color': '#D7E4BC',    # 颜色
            # 'border': 1   # 边框
        })
        count = 2
        worksheet.merge_range('A1:U1', title_name, title_format)
        # worksheet.write_row(1, 0, headings,header_format)

        # 写入表头并添加格式
        for row_header, rowdata_header in enumerate(alist):
            for col_header, coldata_header in enumerate(rowdata_header):
                if row_header == 1:
                    worksheet.write(row_header, col_header, coldata_header, header_format)
        for item in alist:
            value = []
            i = 0
            for k in vlist:
                sum = 0
                for key in item:
                    sum += 1
                    if k == key:
                        value.insert(i, item[key])
                        break
                    elif sum == len(item) and k != key:
                        value.insert(i, '')
                i += 1
            data = value
            worksheet.write_row(count, 0, data)
            count += 1
            if count % 1000 == 0:
                print(count)
            worksheet.set_column('A:A', 10, header_format)  # 指定列设置样式
        workbook.close()

    @staticmethod
    def from_excel(path,sheet_name):
        # 读取表中关键词
        data_all_keys = pd.read_excel(path, sheet_name=sheet_name).fillna("")
        #读取所有表头
        data_header = list(data_all_keys.columns)
        #读取指定表头 data_all_keys["spidercode"]