python xlsxwriter 写大数据到excel

1. 添加表头样式

import datetime
import xlsxwriter


val = {
          
   1: 1,
           2: "AA",
           3: "BB",
           4: "CC",
           5: "DD",
           6: "EE",
           "7":"FF",
           "8":"GG",
           "9":"HH",
           "10":"2022-01-24",
           "11":"MM",
           "12":"JJ",
           "13":"98_Others",
           "14":"ADC",
           "15":"2022-12-31",
           "16":1,
           "17":"FRCN-191317",
           "18":"人事代理服务合同",
           "19":"No",
           "20":"cost",
           "21":"allocation",
           "22":"true",
           "23":118596,
           "24":700000.0,
           "25":"CNY",
           "26":"null"}



def yield_big_data(val):
    for i in range(2):
        yield val
					

workbook = xlsxwriter.Workbook(write_dict.xlsx,{
          
   constant_memory: True})
worksheet = workbook.add_worksheet("ellis")

print(datetime.datetime.now())
row=1

# Add a header format.
header_format = workbook.add_format({
          
   
    bold: True,
    fg_color: #ffcccc,
    border: 1})



col=0
# 设置表头样式
for key in val.keys():
    worksheet.write(0, col, key, header_format)
    col+=1

for my_dict in yield_big_data(val):
    worksheet.write_row(row, 0, list(my_dict.values()))
    row+=1
workbook.close()
print(datetime.datetime.now())

2. 往单元格写数据

import xlsxwriter

workbook = xlsxwriter.Workbook(write_data.xlsx)
worksheet = workbook.add_worksheet()

worksheet.write(0, 0, 1234)     # Writes an int
worksheet.write(1, 0, 1234.56)  # Writes a float
worksheet.write(2, 0, Hello)  # Writes a string
worksheet.write(3, 0, None)     # Writes None
worksheet.write(4, 0, True)     # Writes a bool

workbook.close()

3. 写数组数据

import xlsxwriter

workbook = xlsxwriter.Workbook(write_list.xlsx)
worksheet = workbook.add_worksheet()

my_list = [1, 2, 3, 4, 5]

for row_num, data in enumerate(my_list):
    worksheet.write(row_num, 0, data)

workbook.close()

4. 写一行或者一列

import xlsxwriter

workbook = xlsxwriter.Workbook(write_list.xlsx)
worksheet = workbook.add_worksheet()

my_list = [1, 2, 3, 4, 5]

worksheet.write_row(0, 1, my_list)
worksheet.write_column(1, 0, my_list)

workbook.close()

5. 获取所有sheet以及sheet的行数 需要注意的是excel不能close，即读已存在的excel，将不行

import datetime
import xlsxwriter


val = {
          
   1: 1,
           2: "AA",
           3: "BB",
           4: "CC",
           5: "DD",
           6: "EE",
           "7":"FF",
           "8":"GG",
           "9":"HH",
           "10":"2022-01-24",
           "11":"MM",
           "12":"JJ",
           "13":"98_Others",
           "14":"ADC",
           "15":"2022-12-31",
           "16":1,
           "17":"FRCN-191317",
           "18":"人事代理服务合同",
           "19":"No",
           "20":"cost",
           "21":"allocation",
           "22":"true",
           "23":118596,
           "24":700000.0,
           "25":"CNY",
           "26":"null"}



def yield_big_data(val):
    for i in range(10):
        yield val
					

workbook = xlsxwriter.Workbook(rC:Users84977DesktopE-Document-Frontendwrite_dict.xlsx,{
          
   constant_memory: True})

worksheet = workbook.add_worksheet("ellis")

print(workbook.worksheets())
worksheet = workbook.get_worksheet_by_name(ellis)

print(datetime.datetime.now())
row=1

# Add a header format.
header_format = workbook.add_format({
          
   
    bold: True,
    fg_color: #ffcccc,
    border: 1})



col=0
# 设置表头样式
for key in val.keys():
    worksheet.write(0, col, key, header_format)
    col+=1

for my_dict in yield_big_data(val):
    worksheet.write_row(row, 0, list(my_dict.values()))
    row+=1

#获取所有sheet
for item in workbook.worksheets():
    print(item.name)
#通过名称获取sheet
worksheet = workbook.get_worksheet_by_name(ellis)
#打印sheet的行数
print(len(worksheet.table))

workbook.close()