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

posted @ 2018-06-28 12:35  Py.qi  阅读(10705)  评论(0编辑  收藏  举报