python xlswriter_python-写入excel(xlswriter)

一、安装XlsxWriter模块

pip install XlsxWriter

二、常用方法

importxlsxwriterimportdatetime

workbook= xlsxwriter.Workbook("new_excel.xlsx") #创建Excel

worksheet= workbook.add_worksheet("first_sheet") #添加sheet#写入文本#法一：

worksheet.write('A1', 'write something,this is A1')#法二：

worksheet.write(1, 0, 'hello A2')#写入数字

worksheet.write(0, 1, 32) #B1 = 32

worksheet.write(1, 1, 32.3) #B2 = 32.3#写入函数

worksheet.write(2, 1, '=sum(B1:B2)') #B3 = 64.3#插入图片

worksheet.insert_image(0, 5, '2.jpeg')#worksheet.insert_image(0, 5, 'test.png', {'url': 'http://httpbin.org/'})#写入日期

d = workbook.add_format({'num_format': 'yyyy-mm-dd'})

worksheet.write(0,2, datetime.datetime.strptime('2019-12-17', '%Y-%m-%d'), d) #C1#设置行属性，行高设置为40

worksheet.set_row(0, 40)#设置列属性，把A到B列宽设置为20

worksheet.set_column('A:B', 20)#自定义格式

f = workbook.add_format({'border': 1, 'font_size': 13, 'bold': True, 'align': 'center','bg_color': 'cccccc'})

worksheet.write('A3', "python excel", f)

worksheet.set_row(0,40, f)

worksheet.set_column('A:E', 20, f)#批量往单元格写入数据

worksheet.write_column('A15', [1, 2, 3, 4, 5]) #列写入，从A15开始

worksheet.write_row('A12', [6, 7, 8, 9]) #行写入，从A12开始#合并单元格写入

worksheet.merge_range(7,5, 11, 8, 'merge_range')#关闭文件

workbook.close()

xlsxwriter模块写入格式

write_boolean    写boolean值

write_datetime   写日期

write_number    写数字

write_string       写字符串

write_blank        空

write_url            写连接

write_formula    写公式

insert_image     插入图片

merge_range    合并单元格写入

xlsxwrite单元格格式：add_format

color:red                       字体颜色

bold:True                      字体加粗

font_site:12                   字体大小

num_format:yy-mm-dd  日期格式

url:www.baidu.com         超链接

underline:True              下划线设置

bg_color:red                 单元格颜色

边框：border

对齐方式：align

#导入模块

importxlsxwriter#打开student.xlsx文件

workbook = xlsxwriter.Workbook("student.xlsx")#创建一张工作表

worksheet =workbook.add_worksheet()#设置第一行信息

worksheet.write(0, 0, "学号")

worksheet.write(0,1, "姓名")

worksheet.write(0,2, "年龄")#学生信息列表

student_list = [{"name": "小明", "age": 20, "no": "20170901001"},

{"name": "小红", "age": 21, "no": "20170901002"},

{"name": "小刚", "age": 20, "no": "20170901003"},

{"name": "小海", "age": 23, "no": "20170901004"},

{"name": "小阳", "age": 25, "no": "20170901005"}]#遍历列表

for i, info inenumerate(student_list):#写入数据

#write(第x行, 第x列, 写入的数据)

worksheet.write(i + 1, 0, info["no"])

worksheet.write(i+ 1, 1, info["name"])

worksheet.write(i+ 1, 2, info["age"])#关闭文件

workbook.close()

完