python模块xlsxwriter使用

1.安装

pip install XlsxWriter

2.使用

# -*- coding: utf-8 -*-
from io import BytesIO
import qrcode
# import sqlite3
import xlsxwriter

    @http.route('/attachment_detail', auth='public', type='json', methods=['POST'], website=True)
    def attachment_detail(self, **args):
        result = {}
        repair_records = []
        title = ['序号', '备件名称', '规格型(图)号 ', '备件编码', '送修单位', '承修单位', '送修时间', '返回时间', '备注']
        cont = 0
        records = None

        try:
            '''
            权限不同，下载内容不一样
            备件送修明细  序号 备件名称     规格型(图)号    备件编码    送修单位      承修单位       送修时间                           返回时间                                 备注
                            repair_id   model         code   dept_id          repair_unit  bw_goods.attachment.repair(date)  bw_goods.attachment.repair(back_date)  memo
            '''
            # 人员对应部门对象
            dept = http.request.env['bw_org.dept'].search([('id', '=', http.request.env.user.person_id.dept_id.id)])
            # # 登录人对象
            # person = http.request.env['bw_org.person'].search([('name', '=', http.request.env.user.login)])
            # 机关/基层部门人员登录
            obj_ids = []
            print(dept,type(dept))
            if http.request.env.user.id > 6:
                print('dept_login', http.request.env.user.id, type(http.request.env.user.id))
                attachment_obj = http.request.env["bw_goods.attachment"].search([('dept_id', '=', dept.id)])  # 登录部门人对应备件情况明细表
                print(attachment_obj,type(attachment_obj))
                for obj in attachment_obj:
                    # obj_ids.append(obj.repair_id.id)
                    obj_ids.append(obj.id)
                obj_ids = tuple(obj_ids)
                print('obj_ids', obj_ids)
                if obj_ids:
                    records = http.request.env["bw_goods.attachment.repair"].search([('attachment_id', 'in', obj_ids)])
            else:  # admin 。。
                records = http.request.env["bw_goods.attachment.repair"].search([])

            if records:
                print('init_data')
                for record in records:
                    cont = cont + 1
                    repair_id = record.attachment_id.repair_id.name
                    model = record.attachment_id.model
                    code = record.attachment_id.code
                    dept_id = record.attachment_id.repair_id.dept_id.name
                    repair_unit = record.attachment_id.repair_unit
                    date = record.date.strftime("%Y-%m-%d")
                    back_date = record.back_date.strftime("%Y-%m-%d")
                    memo = record.attachment_id.memo
                    if not memo:
                        memo = ''
                    if not repair_unit:
                        repair_unit = ''
                    repair = [str(cont), repair_id, model, code, dept_id, repair_unit, date, back_date, memo]
                    repair_records.append(repair)

                print('获取数据结束', repair_records, type(repair_records[0][-3]))

            index = 0
            if repair_records:
                print('start_xlsx')
                f = xlsxwriter.Workbook('/home/admin/odoo13/bw_addons/bw_goods/static/src/xml/attachment_detail.xlsx')  # 创建excel文件
                worksheet1 = f.add_worksheet('detail')  # 括号内为工作表表名

                workfomat = f.add_format({
                    'bold': True,  # 字体加粗
                    # 'border': 1,  # 单元格边框宽度
                    'align': 'left',  # 对齐方式
                    # 'valign': 'vcenter',  # 字体对齐方式
                    # 'fg_color': '#F4B084',  # 单元格背景颜色
                })

                worksheet1.write_row("A1", title, workfomat)  # 将数据插入到表格中
                for line in repair_records:
                    print("A" + str(index + 2), line)
                    worksheet1.write_row("A" + str(index + 2), line)
                    index += 1

                worksheet1.set_column('A:A', 5)
                worksheet1.set_column('B:C', 15)
                worksheet1.set_column('D:D', 24)
                worksheet1.set_column('E:F', 20)  # 设置E到F列的列宽为20
                worksheet1.set_column('G:H', 12)
                worksheet1.set_column('I:I', 30)
                f.close()
                print('创建excel文件结束')
                result['code'] = 0
                result['msg'] = repair_records
                result['memo'] = '送修明细'
                return result

            result['code'] = 1
            result['memo'] = '无送修明细'
            return result
        except Exception as e:
            result['code'] = 2
            result['msg'] = e
            result['memo'] = 'error'
            return result

 

11