python xlsxwriter 写大数据到excel

会飞的尼古拉斯

已于 2022-11-14 17:14:59 修改

阅读量805

点赞数

分类专栏： Python 文章标签： python

于 2022-11-14 14:49:24 首次发布

本文链接：https://blog.csdn.net/weixin_43632687/article/details/127847207

版权

Python 专栏收录该内容

127 篇文章 6 订阅

订阅专栏

添加表头样式

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())

往单元格写数据

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()

写数组数据

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()

写一行或者一列

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()

获取所有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(r'C:\Users\84977\Desktop\E-Document-Frontend\write_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()