提取excel表头

最新推荐文章于 2023-11-23 09:59:22 发布

nongcunqq

最新推荐文章于 2023-11-23 09:59:22 发布

阅读量734

点赞数

分类专栏： python 笔记文章标签： python 开发语言

本文链接：https://blog.csdn.net/nongcunqq/article/details/125870982

版权

python 同时被 2 个专栏收录

334 篇文章 0 订阅

订阅专栏

笔记

317 篇文章 3 订阅

订阅专栏

import xlrd
from control_excel import convert_to_letter

xlsx_path = r'D:\code\yangming\file\output=CZ16 20220718X.xlsx'
data = xlrd.open_workbook(xlsx_path)
table = data.sheets()[0]
nrows = table.nrows
ncols = table.ncols
print(nrows, ncols)
l = []
for i in range(21):
    name = table.cell(0, i).value
    if i == 20:
        name = 'Unit Price'
    letter = convert_to_letter(i)
    print(i, letter, name)
    item_dict = {
        letter: name
    }
    l.append(item_dict)

print(l)

原始表格

在这里插入图片描述

效果

[{'A': 'TO#'}, {'B': 'Preparation Date'}, {'C': 'Doc No'}, {'D': 'PN'}, {'E': 'Vendor PN'}, {'F': 'Cust'}, {'G': 'RCV Doc NO'}, {'H': 'Exp.Qty'}, {'I': 'Shp.Qty'}, {'J': 'Vendor'}, {'K': 'Sup.Vend.'}, {'L': 'PO'}, {'M': 'REF No'}, {'N': 'Move Type'}, {'O': 'CustRec Date'}, {'P': 'Purchase Date'}, {'Q': 'Conf.Qty'}, {'R': 'Lot#'}, {'S': 'Billing PO'}, {'T': 'Billing PO Item'}, {'U': 'Unit Price'}]

写表头

 workbook = xlsxwriter.Workbook(file_path)
    worksheet = workbook.add_worksheet()

    deep_green = workbook.add_format({'bg_color': '#99cc00'})
    light_green = workbook.add_format({'bg_color': '#d8e4bc'})

    # Widen the first column to make the text clearer.
    worksheet.set_column('A:D', 18)
    worksheet.set_column('G:G', 20)
    worksheet.set_column('K:K', 20)
    worksheet.set_column('M:M', 20)
    worksheet.set_column('O:O', 20)
    l = [{'A': 'S'}, {'B': '收貨 Date'}, {'C': 'Doc No'}, {'D': 'PN'}, {'E': 'Vendor PN'}, {'F': 'Cust'}, {'G': 'RCV Doc NO'}, {'H': 'Rec.Qty'}, {'I': 'Vendor'}, {'J': 'Sup.Vend.'}, {'K': '櫃號'}, {'L': 'Move Type'}, {'M': '轉倉DN'}, {'N': '下載DATE'}, {'O': 'CountryOfOr.'}, {'P': 'DN line'}, {'Q': ''}, {'R': ''}, {'S': ''}]
    for i in l:
        for k, v in i.items():
            letter = f'{k}1'
            worksheet.write(letter, v)