python 操作Excel文件

 

1   安装xlrd、xlwt、xlutils 

cmd下输入：

pip install xlrd        #读取excel

pip install xlwt        #写入excel

pip install xlutils      #操作 Excel 文件的实用工具，如复制、分割、筛选等

2   模块的基本用法：

xlrd模块用于读取excel文件内容,xlwt用于写入数据，xlutils复制

2.1  打开excel文件

myworkbook = xlwt.Workbook()  #新建一个excel文件并打开

myworkbook = xlrd.open_workbook('文件路径', formatting_info = True)# 打开一个已存在的excel文件

   在默认情况中，合并单元格只在最左上角的子单元格可以读取到值，其他都是空的,加上参数后formatting_info = True （这个只支持excel97-03的xls文件），sheet.merged_cells会返回当前表中所有合并单元格的信息，格式是像[(7,8,2,5),(1,3,4,5)...]这样的一个列表。其中每一项都是一个单元格，比如(7,8,2,5)的意思是这个sheet中的第七行的第2-4列合并，和序列的分片操作一样，是算头不算尾，所以7,8是指合并的仅第七行（这个7不是index而是index+1），2,5则表示第二列到第四列，不包括第五列。这个“不算尾”的做法是区别于xlwt模块中的合并单元格处理的。

2.2  复制一个excel对象

from xlutils.copy import copy

import xlrd

import xlwt

from xlutils.copy import copy

oldWb = xlrd.open_workbook("test.xlsx");#先打开已存在的表

newWb = copy(oldWb)#复制

newWs = newWb.get_sheet(2);#取sheet表

newWs.write(2, 4, "pass");#写入 2行4列写入pass

newWb.save(“bbb.xls"); #保存为bbb.xls

2.3  添加excel工作表

mySheet = myworkbook.add_sheet(“sheet”)

2.4  获取excel工作表sheet

sheet_list = workbook.sheet_names()    #返回所有sheet的列表

mysheet = sheet_list[0]               #获取sheet 列表后，通过索引获取sheet

mysheet  = workbook.sheet_by_index(0)    #通过index来获得一个sheet对象，index从0开始算起

mysheet  = workbook.sheet_by_name(“sheet1”)    #根据sheet名获得相应的那个sheet对象

2.5  获取表名，行数和列数

mysheetname = sheet.name　

nrows = mysheet.nrows

ncols = mysheet.ncols

2.6  获取一行和一列

rowvalue = mySheet.row_values(i)        # i是行数，从0开始计数，返回list对象。

colvalues = mySheet.col_values(i)       # i是列数，从0开始计数，返回list对象。

sheet.get_rows()　　　　# 返回一个迭代器，遍历所有行，给出每个行的值列表

sheet.row(index)   # 返回一个row对象，可以通过row[index]来获取这行里的单元格cell对象

sheet.col_values(index)　　# 返回某一列的值列表

2.7   获取单元格数据

mycell = mySheet.cell(i, j) # 获取单元格，i是行数，j是列数，行数和列数都是从0开始计数。

mycell_value = mycell.value      #通过单元格获取单元格数据。

mycell_value = mysheet.cell(i, j)   #直接获取单元格数据，i是行数，j是列数，行数和列数都是从0开始计数。

sheet.cell_value(x,y)  #直接获取单元格数据，i是行数，j是列数，行数和列数都是从0开始计数。

sheet.row(x)[y].value #直接获取单元格数据，i是行数，j是列数，行数和列数都是从0开始计数。

2.8   获取单元格属性

cell(i, j).ctype   # 返回该单元格数据类型的代码（0表示空，1表示string，2表示number，3表示date，4表示boolean，5表示error）。
当ctype == 3 时，虽是日期，但那时python是按照float来处理的，需要用xldate_as_tuple方法来将其转化为日期格式，
这个方法的用法就是xlrd.xldate_as_tuple(xldate,datemode), xldate表示一个ctype是3的一个值，datemode是属于workbook的一个属性。

2.9   写入数据

     mysheet.write(2, 0, 1)   #写入A3，数值等于1

     mysheet.write(2, 1, 1)   #写入B3，数值等于1

　　    mysheet.write(2, 2, xlwt.Formula("A3+B3"))  #写入C3，数值等于2（A3+B3）

     mystyle = xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00')   #单元格样式

     mysheet.write(2,1,”aaaaa”, mystyle) #写入B3，值等于“aaaaa”,并且单元格样式为mystyle样式

2.10  保存

mybook.save(r"C:\Users\wa\Desktop\test.xls")

2.11  单元格样式设置

myStyle = xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00') 

样式用的模块：

style = xlwt.XFStyle() # 样式的base,通过style.属性  来设置单元格属性：

style.font  # 设置字体

style.alignment  # 设置对齐方式

style. pattern  #设置背景色

style. border #设置边框

import xlwt


def def_style():

    style = xlwt.XFStyle()
    # 这部分设置字体
    font = xlwt.Font()
    # 字体
    font.name = 'Times New Roman'
    # 设为粗体
    font.bold = 'True '
    # 字体高度
    font.height = '100'
    # 字号
    font.size = '5'
    # 字体颜色
    font.colour_index = 0x40
    style.font = font

    # 这部分设置居中格式
    alignment = xlwt.Alignment()
    # 水平居中
    alignment.horz = xlwt.Alignment.HORZ_CENTER
    # 垂直居中
    alignment.vert = xlwt.Alignment.VERT_CENTER
    style.alignment = alignment

    # 设置背景颜色
    ptn = xlwt.Pattern()
    # 设置背景色
    ptn.pattern = xlwt.Pattern.SOLID_PATTERN
    ptn.pattern_fore_colour = 0x40
    style.pattern = ptn

    # 设置边框
    borders = xlwt.Borders()
    borders.left = 1
    borders.right = 1
    borders.top = 1
    borders.bottom = 1
    style.borders = borders
    return style

 

xlwt.Styles的color_map的部分参考值：

aqua 0x31

black 0x08

blue 0x0C

blue_gray 0x36

bright_green 0x0B

brown 0x3C

coral 0x1D

cyan_ega 0x0F

dark_blue 0x12

dark_blue_ega 0x12

dark_green 0x3A

dark_green_ega 0x11

dark_purple 0x1C

dark_red 0x10

dark_red_ega 0x10

dark_teal 0x38

dark_yellow 0x13

gold 0x33

gray_ega 0x17

gray25 0x16

gray40 0x37

gray50 0x17

gray80 0x3F

green 0x11

ice_blue 0x1F

indigo 0x3E

ivory 0x1A

lavender 0x2E

light_blue 0x30

light_green 0x2A

light_orange 0x34

light_turquoise 0x29

light_yellow 0x2B

lime 0x32

magenta_ega 0x0E

ocean_blue 0x1E

olive_ega 0x13

olive_green 0x3B

orange 0x35

pale_blue 0x2C

periwinkle 0x18

pink 0x0E

plum 0x3D

purple_ega 0x14

red 0x0A

rose 0x2D

sea_green 0x39

silver_ega 0x16

sky_blue 0x28

tan 0x2F

teal 0x15

teal_ega 0x15

turquoise 0x0F

violet 0x14

white 0x09

yellow 0x0D