导出excel数据

# 导出excel数据
import os
import shutil
import time
import bson
import xlwt
import openpyxl
from django.conf import settings
import pandas as pd
from dimension.libs.ali_oss import AliOss
from dimension.appliance import thousandth
from openpyxl.styles import PatternFill
from openpyxl.styles import Alignment
from openpyxl.styles import Side, Border, colors, Font
from openpyxl.utils import get_column_letter
from multiprocessing.pool import Pool

ALI_OSS = AliOss()


def len_byte(value):
    '''
    excel 表格的宽度
    '''
    # 获取字符串长度,一个中文的长度为2
    length = len(value)
    utf8_length = len(value.encode('utf-8'))
    length = (utf8_length - length) / 2 + length
    return int(length)


def deal_str(data):
    '''
    处理csv较长数字数据导出后变成科学计数法
    '''
    data = str(data) + '\t'
    return data


def check_str(value):
    '''
    将None转为空字符串
    '''
    if value:
        return value
    else:
        return ''


def export_excel(field_data: list, data: list, filename: str, file_path: str = settings.BASE_DIR):
    """
    Excel导出 日成交定制化
    :param data: 数据源
    :param field_data: 首行数据源,表格标题
    :param file_path: 文件保存路径,
    :param filename: 文件保存名字
    :return:
    """
    wbk = xlwt.Workbook(encoding='utf-8')
    sheet = wbk.add_sheet('Sheet1', cell_overwrite_ok=True)  # 第二参数用于确认同一个cell单元是否可以重设值。
    style = xlwt.XFStyle()  # 赋值style为XFStyle(),初始化样式
    # 设置居中
    wbk.set_colour_RGB(0x23, 0, 60, 139)
    xlwt.add_palette_colour("custom_colour_35", 0x23)
    tab_al = xlwt.Alignment()
    tab_al.horz = 0x02  # 设置水平居中
    tab_al.vert = 0x01  # 设置垂直居中
    # 设置表头单元格背景颜色
    tab_pattern = xlwt.Pattern()  # 创建一个模式
    tab_pattern.pattern = xlwt.Pattern.SOLID_PATTERN  # 设置其模式为实型
    tab_pattern.pattern_fore_colour = 55
    # 设置单元格内字体样式
    tab_fnt = xlwt.Font()  # 创建一个文本格式,包括字体、字号和颜色样式特性
    tab_fnt.height = 255
    default_width = 50
    tab_fnt.name = u'楷体'  # 设置其字体为微软雅黑
    tab_fnt.colour_index = 1  # 设置其字体颜色
    # 设置单元格下框线样式
    tab_borders = xlwt.Borders()
    tab_borders.left = xlwt.Borders.THIN
    tab_borders.right = xlwt.Borders.THIN
    tab_borders.top = xlwt.Borders.THIN
    tab_borders.bottom = xlwt.Borders.THIN
    tab_borders.left_colour = 23
    tab_borders.right_colour = 23
    tab_borders.bottom_colour = 23
    tab_borders.top_colour = 23
    #### 把数据写入excel中
    # 所有表格单元格样式
    # 先生成表头
    style.alignment = tab_al  # 设置居中
    style.pattern = tab_pattern  # 设置表头单元格背景颜色
    style.font = tab_fnt  # 设置单元格内字体样式
    style.borders = tab_borders
    for index, ele in enumerate(field_data):
        sheet.write_merge(0, 0, index, index, ele, style)  # (列开始, 列结束, 行开始, 行结束, '数据内容')

    # 确定栏位宽度
    col_width = []
    # 设置单元格的宽度
    # 循环设置第一列至第8列的列宽
    for c in range(8):
        sheet.col(c).width = 256 * 50  # 设置列宽值为20个字符宽度
    # 设置栏位宽度,栏位宽度小于10时候采用默认宽度
    for i in range(len(col_width)):
        if col_width[i] > 10:
            width = col_width[i] if col_width[i] < 36 else 36
            sheet.col(i).width = 256 * (width + 6)
        else:
            sheet.col(i).width = 256 * (default_width)

    row = 1
    # 内容背景颜色
    left_pattern = xlwt.Pattern()  # 创建一个模式
    left_pattern.pattern = xlwt.Pattern.SOLID_PATTERN  # 设置其模式为实型
    left_pattern.pattern_fore_colour = 1

    # 设置单元格内字体样式
    left_fnt = xlwt.Font()  # 创建一个文本格式,包括字体、字号和颜色样式特性
    left_fnt.height = 225
    left_fnt.name = u'楷体'  # 设置其字体为微软雅黑
    left_fnt.colour_index = 0  # 设置其字体颜色

    left_style = style
    left_style.pattern = left_pattern
    left_style.font = left_fnt
    for results in data:
        sheet.write(row, 0, label=results["order_code"], style=left_style)
        sheet.write(row, 1, label=results["customer_name"], style=left_style)
        sheet.write(row, 2, label=results["reagent_class_id"], style=left_style)
        sheet.write(row, 3, label=results["reagent_class_id"], style=left_style)
        sheet.write(row, 4, label=results["format"], style=left_style)
        sheet.write(row, 5, label=results["orders_num"], style=left_style)
        sheet.write(row, 6, label=thousandth(results["unit_price"]), style=left_style)
        sheet.write(row, 7, label=thousandth(results["orders_sum"]), style=left_style)
        row += 1
    time_stamp = int(time.time())
    EXECL_PATH = os.path.join(file_path, "tmp", "excel", f"{time_stamp}")
    if not os.path.exists(EXECL_PATH):
        os.makedirs(EXECL_PATH)
    path_name = os.path.join(EXECL_PATH, filename)
    wbk.save(path_name)
    file_url = ALI_OSS.excel_upload(path_name, f"tmp/excel/{time_stamp}/", filename)
    try:
        shutil.rmtree(EXECL_PATH)
    except FileNotFoundError:
        pass
    return file_url


def export_xls(field_data: list, data: list, file_path: str = settings.BASE_DIR):
    """
    将数据导出xls
    :param data: 数据源
    :param field_data: 首行数据源
    :param file_path: 文件保存路径
    :param FileName: 文件保存名字
    :return:
    """
    wbk = xlwt.Workbook(encoding='utf-8')
    sheet = wbk.add_sheet('Sheet1', cell_overwrite_ok=True)  # 第二参数用于确认同一个cell单元是否可以重设值。
    style = xlwt.XFStyle()  # 赋值style为XFStyle(),初始化样式
    # 设置居中
    wbk.set_colour_RGB(0x23, 0, 60, 139)
    xlwt.add_palette_colour("custom_colour_35", 0x23)
    tab_al = xlwt.Alignment()
    tab_al.horz = 0x02  # 设置水平居中
    tab_al.vert = 0x01  # 设置垂直居中
    # 设置表头单元格背景颜色
    tab_pattern = xlwt.Pattern()  # 创建一个模式
    tab_pattern.pattern = xlwt.Pattern.SOLID_PATTERN  # 设置其模式为实型
    tab_pattern.pattern_fore_colour = 55
    # 设置单元格内字体样式
    tab_fnt = xlwt.Font()  # 创建一个文本格式,包括字体、字号和颜色样式特性
    tab_fnt.height = 200
    default_width = 14
    tab_fnt.name = u'宋体'  # 设置其字体为微软雅黑
    tab_fnt.colour_index = 1  # 设置其字体颜色
    # 设置单元格下框线样式
    tab_borders = xlwt.Borders()
    tab_borders.left = xlwt.Borders.THIN
    tab_borders.right = xlwt.Borders.THIN
    tab_borders.top = xlwt.Borders.THIN
    tab_borders.bottom = xlwt.Borders.THIN
    tab_borders.left_colour = 23
    tab_borders.right_colour = 23
    tab_borders.bottom_colour = 23
    tab_borders.top_colour = 23
    #### 把数据写入excel中
    # 所有表格单元格样式
    # 先生成表头
    style.alignment = tab_al  # 设置居中
    style.pattern = tab_pattern  # 设置表头单元格背景颜色
    style.font = tab_fnt  # 设置单元格内字体样式
    style.borders = tab_borders
    for index, ele in enumerate(field_data):
        sheet.write_merge(0, 0, index, index, ele, style)  # (列开始, 列结束, 行开始, 行结束, '数据内容')

    # 确定栏位宽度
    col_width = []
    for index, ele in enumerate(data):
        for inx, values in enumerate(ele.values()):
            if index == 0:
                col_width.append(len_byte(str(values)))
            else:
                if col_width[inx] < len_byte(str(values)):
                    col_width[inx] = len_byte(str(values))
    # 设置栏位宽度,栏位宽度小于10时候采用默认宽度
    for i in range(len(col_width)):
        if col_width[i] > 10:
            width = col_width[i] if col_width[i] < 36 else 36
            sheet.col(i).width = 256 * (width + 6)
        else:
            sheet.col(i).width = 256 * (default_width)

    row = 1
    # 内容背景颜色
    left_pattern = xlwt.Pattern()  # 创建一个模式
    left_pattern.pattern = xlwt.Pattern.SOLID_PATTERN  # 设置其模式为实型
    left_pattern.pattern_fore_colour = 1

    # 设置单元格内字体样式
    left_fnt = xlwt.Font()  # 创建一个文本格式,包括字体、字号和颜色样式特性
    left_fnt.height = 200
    left_fnt.name = u'宋体'  # 设置其字体为微软雅黑
    left_fnt.colour_index = 0  # 设置其字体颜色

    left_style = style
    left_style.pattern = left_pattern
    left_style.font = left_fnt

    for results in data:
        for index, values in enumerate(results.values()):
            sheet.write(row, index, label=values, style=left_style)
        row += 1
    filename = f"{bson.ObjectId()}.xls"
    execl_path = os.path.join(file_path, "tmp", "excel", )
    if not os.path.exists(execl_path):
        os.makedirs(execl_path)
    path_name = os.path.join(execl_path, filename)
    wbk.save(path_name)
    file_url = ALI_OSS.excel_upload(path_name, f"tmp/excel/", filename)
    try:
        os.remove(path_name)
    except FileNotFoundError:
        pass
    return file_url


def export_xlsx(field_data: list, data: list, file_path: str = settings.BASE_DIR):
    """
    将数据导出xlsx
    :param data:
    :param path:
    :return:
    """
    # 实例化一个wrokbook
    wbk = openpyxl.Workbook()
    sheet = wbk.active
    # 为sheet命名,默认为Sheet
    # sheet.title = 'Sheet1'
    # 设置所有边框加粗
    border = Border(top=Side(border_style='thin', color=colors.BLACK),
                    bottom=Side(border_style='thin', color=colors.BLACK),
                    left=Side(border_style='thin', color=colors.BLACK),
                    right=Side(border_style='thin', color=colors.BLACK))
    # 设置表格居中显示
    align = Alignment(horizontal='center', vertical='center')
    # 设置字体
    font = Font(name="宋体")
    # 设置背景色
    fill_title = PatternFill(start_color="D9D9D9", fill_type="solid")
    fill_content = PatternFill(start_color="E2EFDA", fill_type="solid")
    # 确定栏位宽度
    col_width = []
    for index, ele in enumerate(data):
        for inx, values in enumerate(ele.values()):
            if index == 0:
                col_width.append(len_byte(str(values)))
            else:
                if col_width[inx] < len_byte(str(values)):
                    col_width[inx] = len_byte(str(values))
    # 写标题的第一行
    for index, item in enumerate(field_data):
        sheet.cell(row=1, column=index + 1, value=item).border = border
        sheet.cell(row=1, column=index + 1, value=item).alignment = align
        sheet.cell(row=1, column=index + 1, value=item).font = font
        sheet.cell(row=1, column=index + 1, value=item).fill = fill_title
    # 设置栏位宽度
    for i in range(len(col_width)):
        letter = get_column_letter(i + 1)  # 列字母
        sheet.column_dimensions[letter].width = col_width[i] * 1.2 + 4  # 也就是列宽为最大长度*1.2 可以自己调整
    # 准备写入数据
    row = 1
    for results in data:
        for index, values in enumerate(results.values()):
            sheet.cell(row=row + 1, column=index + 1, value=str(check_str(values))).font = font
            sheet.cell(row=row + 1, column=index + 1, value=str(check_str(values))).border = border
            sheet.cell(row=row + 1, column=index + 1, value=str(check_str(values))).alignment = align
            sheet.cell(row=row + 1, column=index + 1, value=str(check_str(values))).fill = fill_content
        row += 1
    time_stamp = int(time.time())
    execl_path = os.path.join(file_path, "tmp", "excel", )
    if not os.path.exists(execl_path):
        os.makedirs(execl_path)
    # 生成文件名
    filename = "{}.xlsx".format(bson.ObjectId())
    path_name = os.path.join(execl_path, filename)
    # 写入到文件
    wbk.save(path_name)
    file_url = ALI_OSS.excel_upload(path_name, f"tmp/excel/{time_stamp}/", filename)
    try:
        os.remove(path_name)
    except FileNotFoundError:
        pass
    return file_url


def export_csv(field_data, value_list):
    '''
    pandas 导出csv
    '''
    data_list = list()
    for results in value_list:
        data = [deal_str(check_str(values)) for index, values in enumerate(results.values())]
        data_list.append(data)
    file_name = "{}.csv".format(bson.ObjectId())
    file_path = os.path.join(settings.BASE_DIR, "tmp", "excel", )
    file_path = file_path + f"/{file_name}"
    df = pd.DataFrame(columns=field_data, data=data_list)
    # 导出csv
    df.to_csv(file_path)
    file_url = ALI_OSS.excel_upload(file_path, f"tmp/excel/", file_name)
    try:
        os.remove(file_path)
    except FileNotFoundError:
        pass
    return file_url


def export_table(field_data, value_list):
    '''
    根据导出数据量导出格式不同
    '''

    count = len(value_list)
    if count <= 2000:
        file_url = export_xlsx(field_data, value_list)
    elif 2000 < count <= 20000:
        file_url = export_xls(field_data, value_list)
    else:
        '''导出csv文本'''
        # file_url = export_csv(field_data, value_list)
        pool = Pool(processes=5)  # 线程数
        results = pool.apply_async(export_csv, args=(field_data, value_list))
        return results.get()
    return file_url

 

posted @ 2022-07-28 15:27  Οo白麒麟оΟ  阅读(71)  评论(0编辑  收藏  举报