excel split函数_Python对excel的读写汇总

一、概述

1、各模块对比

Excel文件后缀，存在xls和xlsx两种主要区分，其中xls是03版Office Excel 工作表的格式，xlsx是07版Office Excel 工作表的格式。

因为excel文件格式的差异，Python对excel的读写操作，也存在多种方式：

图1-1

1)xlrd模块，版本2.0之前，只支持后缀“.xls”格式；版本2.0之后开始同时支持“.xls”、和“.xlsx”。

2)xlwt模块，实现对excel的写操作，是对xlrd的补充。但是目前，xlwt依旧只支持对格式“.xls”的写操作。

3)openpyxl模块，支持“.xlsx”的读和写,但是不支持后缀为“.xls”的读和写。

此外，写操作，可以区分为新建写和修改写两种方式，其中新建写是指，写出的文件需要通过新建，不能修改读取时的原文件；修改写是指，写成的文件可以通过新建实现，也可以直接修改保存读取时的原文件。

2、开发环境说明

整体开发测试的环境版本信息，如下：

二、xldr读取excel操作

1、新建handlexlsx工程

2、安装xldr模块

2.1、打开File菜单，通过选择settings->project->project Interpreter将会出现如下界面：

图2-1

2.2、点击右上角的“+”，搜索“xlrd”模块，点击“Install Package”完成模块安装。

图2-2

3、新建python文件handle_xlsx.py文件，实现编码

#模块导入

import os

import xlrd

############################

#子函数编写

def read_excel(filename):

# 判断文件是否存在

if not os.path.exists(filename):

print('文件不存在:%s' % filename)

return

# 读取sheet页，可以根据名称获取

wb = xlrd.open_workbook(filename)

sheet = wb.sheet_by_name('Sheet1')

# 获得sheet数据规模

nrows = sheet.nrows

ncols = sheet.ncols

print("sheet的行数为:%s,列数为：%s" % (nrows,ncols))

#对单元格的读取，对行的操作

for irow in range(1, nrows):

#对行的操作

print(sheet.row(irow))

# 对单元格的读取，对单元格的操作

for irow in range(1, nrows):

for icol in range(1, ncols):

print(sheet.cell_value(rowx=irow, colx=icol))

if __name__=="__main__":

file_name = r'E:2019成绩表.xlsx'

#处理文件调用

read_excel(file_name)

4、测试验证

4.1、新建测试文件'E:2019成绩表.xlsx'

内容如下：

图2-3

4.2、运行及结果

图2-4

图2-5

三、xlwt新建写excel操作

1、选择handlexlsx工程

2、安装xlwt模块

2.1、打开File菜单，通过选择settings->project->project Interpreter，搜索安装xlwt模块。

3、新建python文件write_xls.py文件，实现编码

#模块导入

import os

import xlwt

############################

#子函数编写

def write_excel(filename):

wb= xlwt.Workbook()

ws = wb.add_sheet('Sheet1')

#标题行

irow = 0

ws.write(irow, 0, '序号')

ws.write(irow, 1, '姓名')

ws.write(irow, 2, '评定')

# 第一行

irow = 1

ws.write(irow, 0, 1)

ws.write(irow, 1, '小张')

ws.write(irow, 2, 'A')

# 第二行

irow = 2

ws.write(irow, 0, 2)

ws.write(irow, 1, '小李')

ws.write(irow, 2, 'B')

# 第三行

irow = 3

ws.write(irow, 0, 3)

ws.write(irow, 1, '小黄')

ws.write(irow, 2, 'C')

#保存

wb.save(filename)

if __name__=="__main__":

file_name = r'E:2019成绩表-结果.xls'

#处理文件调用

write_excel(file_name)

4、测试验证

4.1、保存文件为'.xlsx'格式，运行正常，但是实际文件是无法使用的，因为xlwt不支持'.xlsx'格式。

图3-1

图3-2

4.2、修改参数file_name，保存文件为'.xls'格式，程序运行正常，文件正常

图3-3

图3-4

四、openpyxl读、修改写excel操作

1、选择handlexlsx工程

2、安装openpyxl模块

2.1、打开File菜单，通过选择settings->project->project Interpreter，搜索安装openpyxl模块。

3、新建python文件handle_openpyxl.py文件，实现编码

#模块导入

import os

from openpyxl import load_workbook

############################

#子函数编写

def practise_excel(filename):

# 判断文件是否存在

if not os.path.exists(filename):

print('文件不存在:%s' % filename)

return

# 文件位置

wb = load_workbook(filename)

ws = wb['Sheet1']

# 获得sheet数据规模

nrows = ws.max_row

ncols = ws.max_column

# 对行的操作

for row in ws.values:

print(row)

# 对单元格的读取

for irow in range(1, nrows + 1):

for icol in range(1, ncols + 1):

print(ws.cell(row=irow,column=icol).value)

# 对单元格的写操作

ws.cell(row=2, column=6).value = 'A'

ws.cell(row=3, column=6).value = 'B'

ws.cell(row=4, column=6).value = 'C'

wb.save(filename)

############################

if __name__ == "__main__":

file_name = r'E:2019成绩表.xlsx'

# 处理文件调用

practise_excel(file_name)

4、测试验证

4.1、修改参数file_name，读取文件为'.xls'格式，程序将无法正常运行，因为不支持'.xls'格式

图4-1

4.2、修改参数file_name，文件为'.xlsx'格式，程序运行正常，结果文件正常

图4-2

图4-3

五、“.xls”、和“.xlsx”的转换问题

1、选择handlexlsx工程

2、安装pypiwin32模块

2.1、打开File菜单，通过选择settings->project->project Interpreter，搜索安装pypiwin32模块。

图5-1

3、新建python文件xls_and_xlsx.py文件，实现编码

#模块导入

import os

import win32com.client as win32

############################

#子函数编写

def xls_2_xlsx(filename):

filepath, tempfile = os.path.split(filename)

filetemp, fileext = tempfile.split('.')

#判断文件格式是否需要转换

if fileext == 'xls':

excel = win32.gencache.EnsureDispatch('Excel.Application')

wb = excel.Workbooks.Open(filename)

# FileFormat = 51 格式为.xlsx

# FileFormat = 56 格式为 .xls

wb.SaveAs(filename + "x", FileFormat=51)

wb.Close()

excel.Application.Quit()

def xlsx_2_xls(filename):

filepath, tempfile = os.path.split(filename)

filetemp, fileext = tempfile.split('.')

# 判断文件格式是否需要转换

if fileext == 'xlsx':

#拼接新的文件,新文件在原文件基础添加后缀“_2"(当然也可以复用原文件名称)

filename_new = ("%s%s_2.xls" %(filepath,filetemp))

#print("filename_new is : %s" % filename_new)

excel = win32.gencache.EnsureDispatch('Excel.Application')

wb = excel.Workbooks.Open(filename)

# FileFormat = 51 格式为.xlsx

# FileFormat = 56 格式为 .xls

wb.SaveAs(filename_new, FileFormat=56)

wb.Close()

excel.Application.Quit()

if __name__ == "__main__":

#1、实现xls到xlsx的转换

file_name = r'E:2019成绩表.xls'

# 处理文件调用

xls_2_xlsx(file_name)

#2、实现xlsx到xls的转换

file_name2 = r'E:2019成绩表.xlsx'

# 处理文件调用

xlsx_2_xls(file_name2)

4、测试验证

4.1、子函数xls_2_xlsx，可以实现“.xls”转换为“.xlsx”

图5-2

4.2、子函数xlsx_2_xls，可以实现“.xlsx”转换为“.xls”

图5-3

六、关于excel文件的复制备份

1、“.xlsx”和“.xls”的相互转换，如果保存的文件与原文件不同名，实际实现了文件的复制备份功能。

2、excel的复制，可以通过模块xlutils实现，编码如下：

shutil.copy(“原文件名称”, “新文件名称”)

两者的差异是，方法2的性能优于方法1。

七、总结

“条条大路通罗马”是对编程最好的诠释，程序员的工作就是通过不断尝试完善，用更好的方式去满足需求。