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 图像，自定义图表；

内存优化模式支持写入大文件。

# 源码安装方法 # 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