python3csv与xlsx文件操作模块（csv、xlsxwriter）
一、csv模块实现csv文件操作
1、CSV介绍
CSV，全称为Comma-Separated Values,它以逗号分隔值，其文件以纯文本形式存储表格数据，该文件是一个字符序列，可以由任意数目的记录组成，每条记录有字段组成，字段间分隔符是逗号或制表符，相当于结构化的纯文本形式，它比Excel文件更简洁，用来存储数据比较方便
2、CSV常用类与方法
csv.reader(csvfile,dialect='excel',**fmtparams)
遍历CSV文件对象并返回，csvfiel可以是任何支持迭代器协议的对象，如果csvfile是一个文件对象，它需要指定newline=''
csv.writer(csvfile,dialect='excel',**fmtparams)
写入数据到csv文件中，csvfile可以是具有写入方法的任何对象，如果csvfiel是一个文件对象，应该用newline=''指定换行符(unix上位'\n'，windows上位'\r\n')
#!/usr/bin/env python # -*- coding: utf-8 -*- # @Time : 2018/6/27 11:44 # @Author : Py.qi # @File : csv_file1.py # @Software: PyCharm import csv iterable=[['1','zs',20,8998,20180627],['1','zs',20,8998,20180627],['1','zs',20,8998,20180627]] with open('csvfile.csv','w',newline='') as csvf: spanwriter=csv.writer(csvf,dialect='excel') #创建writer对象 spanwriter.writerow(['id','name','age','salary','date']) #使用writer的方法writerow写入到文件 spanwriter.writerows(iterable) #迭代写入数据 with open('csvfile.csv','r',newline='') as csvf: spamreader=csv.reader(csvf) #创建reader对象 for i in spamreader: print('\t'.join(i)) #指定分隔符，读取csv文件数据 # id name age salary date 1 zs 20 8998 20180627 1 zs 20 8998 20180627 1 zs 20 8998 20180627
class csv.DictReader(f, fieldnames=None, restkey=None, restval=None, dialect='excel', *args, **kwds)
以字典的形式读取csv文件的行，fileldnames参数指定键，restkey指定默认key，restval指定默认value，dialect指定方言
class csv.DictWriter(f, fieldnames, restval='', extrasaction='raise', dialect='excel', *args, **kwds)
创建一个字典形式的csv操作对象，f为写入的文件，fieldnames为制定key，restval制定默认value，extrasaction表示，如果在使用writerow()方法写入数据的字典中字段名找不到的键，则此参数将执行操作，如果设置为saise则会引发valueError，如果设置为ignore则字典中的额外值将被忽略
reader对象，DictReader实例和reader()对象具有的方法和属性：
csvreader.__next__()：迭代读取对象的下一行
csvreader.dialect：解析器使用的方言
csvreader.line_num：从源迭代器读取的行数
csvreader.fieldnames：如果在创建对象时为作为参数传递，则在首次访问文件或读取第一条记录是初始化此属性，此属性只适用于DictReader对象
writer对象，DictWriter和writer()实例对象具有的方法和属性：
csvwriter.writerow()：将行参数写入到文件对象，根据当前的方言格式化
csvwriter.writerows(row)：将row中的所有元素，行对象的迭代写入到文件对象
csvwriter.dialect：解析器使用的方言
DictWriter.writeheader()：写入一行字段名,只适用于DictWriter对象
#!/usr/bin/env python # -*- coding: utf-8 -*- # @Time : 2018/6/27 12:13 # @Author : Py.qi # @File : csv_file2.py # @Software: PyCharm import csv import pandas iter=[ {'id':2,'name':'wanwu','age':23,'date':20180627}, {'id':3,'name':'zhaoliu','age':24,'date':20180627}, {'id':4,'name':'tianqi','age':25,'date':20180627} ] #写入文件 with open('names.csv','w',newline='') as csvf: fieldnames=['id','name','age','date'] writer=csv.DictWriter(csvf,fieldnames=fieldnames) writer.writeheader() writer.writerow({'id':1,'name':'lisii','age':22,'date':20180627}) writer.writerows(iter) #读取文件 with open('names.csv','r') as csvf: reader=csv.DictReader(csvf,fieldnames=fieldnames) for i in reader: print(i['id'],i['name'],i['age'],i['date']) #也可以使用pandas模块来读取csv文件 df=pandas.read_csv('names.csv') print(df) # id name age date 1 lisii 22 20180627 2 wanwu 23 20180627 3 zhaoliu 24 20180627 4 tianqi 25 20180627 id name age date 0 1 lisii 22 20180627 1 2 wanwu 23 20180627 2 3 zhaoliu 24 20180627 3 4 tianqi 25 20180627
二、xlsxwriter模块，实现Excel文件操作
Excel 是当今最流行的电子表格处理软件，支持丰富的计算函数及图表，在系统运营方
面广泛用于运营数据报表，比如业务质量、资源利用、安全扫描等报表，时也是应用系统
常见的文件导出格式, 以便数据使用人员做进一步加工处理。
利用 Python 操作Excel 的模块 XlsxWriter（https: // xlsxwriter.readthedocs.org），可以操作多个工作表的文字、数字、公式、图表等。XlsxWriter 模块具有以下功能：
- 100 % 兼容的 Excel XLSX 文件，支持 Excel 2003、Excel 2007 等版本；
- 支持所有 Excel 单元格数据格式；
- 单元格合并、批注、自动筛选、丰富多格式字符串等；
- 支持工作表 PNG、JPEG 图像，自定义图表；
- 内存优化模式支持写入大文件。
1、Xlsx Writer模块的安装
# pip install XlsxWriter #pip 安装方法 # easy_install XlsxWriter #easy_install 安装方法 # 源码安装方法 # curl -O -L http://github.com/jmcnamara/XlsxWriter/archive/master.tar.gz # tar zxvf master.tar.gz # cd XlsxWriter-master/ # sudo python setup.py install
简单示例，创建一个Excel文件，插入文字、数字，计算公式和图片，设置单元格格式
#!/usr/bin/env python # -*- coding: utf-8 -*- # @Time : 2018/6/27 14:00 # @Author : Py.qi # @File : xlsxWriter_f1.py # @Software: PyCharm import xlsxwriter workbook=xlsxwriter.Workbook('demo.xlsx') #创建一个Excel文件 worksheet=workbook.add_worksheet() #创建一个工作表对象 worksheet.set_column('A:A',20) #设置第一列A宽度为20像素 bold=workbook.add_format({'bold':True}) #定义一个加粗的格式对象 worksheet.write('A1','HELLO') #A1单元格写入HELLO worksheet.write('A2','world',bold) #A2写入world并引用加粗格式对象bold worksheet.write('B2',u'中文测试',bold) #B2写入中文引用加粗格式bold worksheet.write(2,0,32) #用行列表示法写入数字32与35.5 worksheet.write(3,0,35.5) #行列表示法的单元格下标以0作为起始值，‘3,0’等价与A3 worksheet.write(4,0,'=SUM(A3:A4)') #求和A3:A4 worksheet.insert_image('B5','14.png') #在B5插入图片 workbook.close()
2、Xlsx Writer模块常用方法
（1）Workbook 类
Workbook 类定义：Workbook(filename[, options])
该类实现创建一个 XlsxWriter 的 Workbook对象。Workbook 类代表整个电子表格文件，并且存储在磁盘上。参数 filename（String 类型）为创建的 Excel 文件存储路径；参数 options（Dict 类型）为可选的 Workbook 参数，一般作为初始化工作表内容格式，例如值为 {'strings_to_numbers': True} 表示使用 worksheet.write() 方法时激活字符串转换数字。
add_worksheet([sheetname])方法，作用是添加一个新的工作表，参数 sheetname（String 类型）为可选的工作表名称，默认为 Sheet1
#添加一个新工作表名称为data worksheet=workbook.add_worksheet('data')
add_format([properties]) 方法，作用是在工作表中创建一个新的格式对象来格式化单元格。参数 properties（dict 类型）为指定一个格式属性的字典，例如设置一个加粗的格式对象，workbook.add_format({'bold': True})
#!/usr/bin/env python #coding:utf-8 import xlsxwriter workfile=xlsxwriter.Workbook('test.xlsx') #创建Excel文件 worksheet=workfile.add_worksheet('sh1') #创建工作表 format=workfile.add_format({'bg_color':'red','font_size':'18','font_color':'yellow'}) #定义格式对象，背景为红色，字体大小18，字体颜色黄色 worksheet.set_column('A:A',20) #设置A列宽度为20像素 worksheet.write('A1','python',format) #写入文件并应用格式 workfile.close() #关闭文件
更多格式化方法见http://xlsxwriter.readthedocs.io/format.html#format
add_chart（options）方法，作用是在工作表中创建一个图表对象，内部是通过 insert_chart() 方法来实现，参数 options（dict 类型）为图表指定一个字典属性，例如设置一个线条类型的图表对象，代码为 chart = workbook.add_chart({'type': 'line'})
close() 方法，作用是关闭工作表文件，如 workbook.close()
（2）Worksheet 类
Worksheet 类代表了一个 Excel 工作表，是 XlsxWriter 模块操作 Excel 内容最核心的一个类，例如将数据写入单元格或工作表格式布局等。Worksheet 对象不能直接实例化，取而代之的是通过 Workbook 对象调用 add_worksheet() 方法来创建。Worksheet 类提供了非常丰富的操作 Excel 内容的方法，其中几个常用的方法如下：
write(row, col, *args) 方法：
作用是写普通数据到工作表的单元格，参数 row 为行坐标，col 为列坐标，坐标索引起始值为 0；* args 无名字参数为数据内容，可以为数字、公式、字符串或格式对象。为了简化不同数据类型的写入过程，write 方法已经作为其他更加具体数据类型方法的别名，包括：
write_string() 写入字符串类型数据，如：worksheet.write_string(0, 0, 'Your text here')
write_number() 写入数字类型数据，如：worksheet.write_number('A2', 2.3451)
write_blank() 写入空类型数据，如：worksheet.write('A2', None)
write_formula() 写入公式类型数据，如：worksheet.write_formula(2, 0, '=SUM(B1:B5)')
write_datetime() 写入日期类型数据，如：worksheet.write_datetime(7, 0, datetime.datetime.strptime('2013-01-23','%Y-%m-%d'),workbook.add_format({'num_format': 'yyyy-mm-dd'}))
write_boolean() 写入逻辑类型数据，如：worksheet.write_boolean(0, 0, True)；
write_url() 写入超链接类型数据，如：worksheet.write_url('A1', 'ftp://www.python.org/')
下面通过具体的示例来观察别名write方法与数据类型方法的对应关系：
#!/usr/bin/env python #coding:utf-8 import xlsxwriter workbook=xlsxwriter.Workbook('test1.xlsx') worksheet=workbook.add_worksheet(name='中文') worksheet.write(0,0,'hello') #写入字符串 worksheet.write(1,0,2) worksheet.write(2,0,3.001) #写入数字 worksheet.write(3,0,'=SIN(PI()/4)') #写入公式 worksheet.write(4,0,'') #写入空 worksheet.write(5,0,'http://www.baidu.com') #写入url workbook.close()
set_row（row, height, cell_format, options）方法：
作用是设置行单元格的属性。参数row（int 类型）指定行位置，起始下标为 0 ；参数 height（float 类型）设置行高，单位像素；参数 cell_format（format 类型）指定格式对象；参数 options（dict 类型）设置行 hidden（隐藏）、level（组合分级）、collapsed（折叠）
worksheet.write('A1','hello') #在A1写入字符串 cell_format=workbook.add_format({'bold':True}) #定义加粗的格式对象 worksheet.set_row(0,40,cell_format) #设置第1行单元格高度为40像素，且引用加粗格式对象 worksheet.set_row(1,None,None,{'hidden':True}) #隐藏第2行单元格
set_column（first_col, last_col, width, cell_format, options）方法：
作用为设置一列或多列单元格属性。参数 first_col（int 类型）指定开始列位置，起始下标为 0 ；参数 last_col（int 类型）指定结束列位置，起始下标为 0，可以设置成与 first_col 一样；参数width（float 类型）设置列宽；参数 cell_format（Format 类型）指定格式对象；参数options（dict 类型）设置行 hidden（隐藏）、level（组合分级）、collapsed（折叠）
#!/usr/bin/env python #coding:utf-8 import xlsxwriter workbook=xlsxwriter.Workbook('test2.xlsx') worksheet=workbook.add_worksheet('test') worksheet.write('A1','Hello') cell_format=workbook.add_format({'bold':True}) worksheet.set_column(0,1,10,cell_format) #设置0到1即A到B列单元格宽度为10像素，且引用加粗格式对象 worksheet.set_column('C:D',20) #设置C到D列单元格宽度为20像素 worksheet.set_column('E:G',None,None,{'hidden':1}) #隐藏E到G列单元格
insert_image(row, col, image[, options]) 方法：
作用是插入图片到指定单元格，支持PNG、JPEG、BMP 等图片格式。参数 row 为行坐标，col 为列坐标，坐标索引起始值为 0 ；参数 image（string 类型）为图片路径；参数 options（dict 类型）为可选参数，作用是指定图片的位置、比例、链接 URL 等信息
#!/usr/bin/env python #coding:utf-8 import xlsxwriter workbook=xlsxwriter.Workbook('test3.xlsx') worksheet=workbook.add_worksheet('test') #在B5单元格插入22.jpg图片，图片超链接为http://python.org worksheet.insert_image('B5','D:/22.jpg',{'url':'http://python.org'}) workbook.close()
（3）Chart 类
Chart 类实现在 XlsxWriter 模块中图表组件的基类，支持的图表类型包括面积、条形图、柱形图、折线图、饼图、散点图、股票和雷达等，一个图表对象是通过 Workbook（工作簿）的 add_chart 方法创建，通过 {type, ' 图表类型 '} 字典参数指定图表的类型，语句如下：
chart =workbook.add_chart({'type':'column'}) #创建一个column柱形图表
更多图表类型说明：
area：创建一个面积样式的图表
bar：创建一个条形样式的图表
column：创建一个柱形样式的图表
line：创建一个线条样式的图表
pie：创建一个饼图样式的图表
scatter：创建一个散点样式的图表
stock：创建一个股票样式的图表
radar：创建一个雷达样式的图表
然后再通过 Worksheet（工作表）的 insert_chart() 方法插入到指定位置，语句如下：
worksheet.insert_chart('A7', chart) # 在 A7 单元格插入图表
下面介绍 chart 类的几个常用方法：
chart.add_series(options) 方法：
作用为添加一个数据系列到图表，参数 options(dict类型) 设置图表系列选项的字典，操作示例如下：
chart.add_series({ 'categories': '=Sheet1!$A$1:$A$5', 'values': '=Sheet1!$B$1:$B$5', 'line': {'color': 'red'}, })
add_series 方法，最常用的三个选项为 categories、values、line，其中 categories 作用是设置图表类别标签范围；values 为设置图表数据范围；line 为设置图表线条属性，包括颜色、宽度等。
其他常用方法及示例：
set_x_axis(options) 方法：设置图表 X 轴选项，示例代码如下：
chart.set_x_axis({ 'name': 'Earnings per Quarter', #设置 X 轴标题名称 'name_font': {'size': 14, 'bold': True}, #设置 X 轴标题字体属性 'num_font': {'italic': True }, #设置 X 轴数字字体属性 })
set_size(options) 方法：设置图表大小，如：chart.set_size({'width':720,'height':576})，其中width为宽度，height为高度
set_title(options) 方法：设置图表标题，如：chart.set_title({'name':'Year End Results'})
set_style(style_id) 方法：设置图表样式，style_id 为不同数字则代表不同样式，如chart.set_style(37)
set_table(options) 方法：设置 X 轴为数据表格形式，如 chart.set_table()
#!/usr/bin/env python # -*- coding: utf-8 -*- # @Time : 2018/6/28 9:31 # @Author : Py.qi # @File : chart_writer.py # @Software: PyCharm import xlsxwriter #创建数据表 workbook=xlsxwriter.Workbook('test.xlsx') worksheet=workbook.add_worksheet() title=[u'部门名称',u'一月份',u'二月份',u'三月份',u'四月份',u'五月份',u'平均值'] department=[u'技术部',u'市场部',u'财务部',u'工程部',u'安全部'] data=[ [123,135,145,159,168], [88,99,105,112,146], [78,75,71,68,89], [110,123,159,189,201], [98,156,198,175,58], ] format=workbook.add_format({'bold':True,'border':1,'bg_color':'#cccccc'}) format_val=workbook.add_format({'border':1,'align':'center'}) worksheet.set_row(0,20) worksheet.set_column('A:F',10) worksheet.write_row('A1',title,format) worksheet.write_column('A2',department,format) worksheet.write_row('B2',data[0],format_val) worksheet.write_row('B3',data[1],format_val) worksheet.write_row('B4',data[2],format_val) worksheet.write_row('B5',data[3],format_val) worksheet.write_row('B6',data[4],format_val) #创建图表 chart=workbook.add_chart({'type':'column'}) #图表数据插入函数 def chart_series(cur_row): worksheet.write_formula('G'+cur_row,'=AVERAGE(B'+cur_row+':F'+cur_row+')',format_val) chart.add_series({ 'categories':'=Sheet1!$B$1:$F$1', 'values':'=Sheet1!$B'+cur_row+':$F$'+cur_row, 'name':'=Sheet1!$A$'+cur_row }) #循环插入数据 for i in range(2,7): chart_series(str(i)) chart.set_size({'width':677,'height':380}) #设置图表宽和高 chart.set_title({'name':u'业绩表'}) #设置图表标题 #chart.set_table() #设置图表table样式 worksheet.insert_chart('A10',chart) #图表插入到工作表中 workbook.close()
3、定制自动化业务流量报表周报
通过XlsxWriter模块将流量数据写入Excel文档，同时自动计算各频道周平均流量，在生成数据图表，具体是通过workbook.add_chart({'tyep':'column'})方法指定图表类型为柱形，使用write_row,write_column方法分别以行，列方式写入数据，使用add_format()方法定制表头，表体的显示风格，使用add_series()方法将数据添加到图表，同时使用chart.set_size，set_title，set_y_axis设置图表的大小及标题属性，最后通过insert_chart方法将图表插入工作表中
#!/usr/bin/env python #coding:utf-8 import xlsxwriter workbook=xlsxwriter.Workbook('test4.xlsx') #创建一个Excel文件 worksheet=workbook.add_worksheet() #创建一个工作表对象 chart=workbook.add_chart({'type':'column'}) #创建柱形图表 #定义数据表头列表 title=[u'业务名称',u'星期一',u'星期二',u'星期三',u'星期四',u'星期五',u'星期六',u'星期日',u'平局流量'] buname=[u'业务官网',u'新闻中心',u'购物频道',u'体育频道',u'亲子频道'] #定义频道名称 #定义5个频道一周7天的数据列表 data=[ [150,152,158,149,155,145,148], [88,89,95,93,98,100,99], [210,200,158,178,170,198,195], [75,77,78,78,74,70,79], [88,77,87,90,93,88,84], ] format=workbook.add_format() #定义format格式对象 format.set_border(1) #定义format对象单元格边框加粗1像素的格式 format_title=workbook.add_format() #定义title格式对象 format_title.set_border(1) #定义title格式 format_title.set_bg_color('#cccccc') #定义title对象背景颜色 format_title.set_align('center') #定义title剧中对齐 format_title.set_bold() format_ave=workbook.add_format() #定义format_ave格式对象 format_ave.set_border(1) format_ave.set_num_format('0.00') #定义数字类别显示格式 #下面分别以行或列写入方式将标题，业务名称，流量数据写入起初单元格，同时引用不同格式对象 worksheet.write_row('A1',title,format_title) worksheet.write_column('A2',buname,format) worksheet.write_row('B2',data[0],format) worksheet.write_row('B3',data[1],format) worksheet.write_row('B4',data[2],format) worksheet.write_row('B5',data[3],format) worksheet.write_row('B6',data[4],format) #定义图表数据系列函数 def chart_series(cur_row): worksheet.write_formula('I'+cur_row,'=AVERAGE(B'+cur_row+':H'+cur_row+')',format_ave) chart.add_series( { 'categories':'=Sheet1!$B$1:$H$1', 'values':'=Sheet1!$B$'+cur_row+':$H$'+cur_row, #'line':{'color':'black'}, 'name':'=Sheet1!$A$'+cur_row, } ) for row in range(2,7): chart_series(str(row)) chart.set_table() #设置X轴表格格式 chart.set_style(30) #设置图表样式 chart.set_size({'width':677,'height':380}) chart.set_title({'name':u'业务流量周报图表'}) chart.set_y_axis({'name':'Mb/s'}) worksheet.insert_chart('A9',chart) workbook.close()
XlsxWriter官网：https://xlsxwriter.readthedocs.io/index.html
