python3csv与xlsx文件操作模块（csv、xlsxwriter）

2022-05-15 00:07:25

一、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

   zs    20    8998    20180627

   zs    20    8998    20180627

   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

lisii 22 20180627

wanwu 23 20180627

zhaoliu 24 20180627

tianqi 25 20180627

   id     name  age      date

  1    lisii   22  20180627

  2    wanwu   23  20180627

  3  zhaoliu   24  20180627

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

参考：

https://www.cnblogs.com/zhangxinqi/p/9231801.html

XlsxWriter官网：https://xlsxwriter.readthedocs.io/index.html

码农公寓