4种Python处理Excel数据的方法

看到非常好用的文章，转载记录一下，以免之后找不到。

原文地址：4种Python处理Excel数据的方法！_python操作excel-CSDN博客

电子表格格式

我们在日常工作中常常见到各种后缀的电子表格，例如最常见的xlsx以及较为常见的csv、xls等格式的表格。同样是电子表格，它们之间有什么区别吗？

• xls为Excel早期表格格式。

xls格式是Excel2003版本及其以前版本所生成的文件格式。其最大的特点就是：仅有65536行、256列。因此规模过大的数据不可以使用xls格式读写。

• xlsx为Excel2007及其之后的表格格式，也是现在Excel表格的主流格式。

与xls相比，它可以存储1048576行、16384列数据，存储相同数据，xlsx格式要比xls格式文件要小得很多。

• CSV为逗号分隔值文件。

CSV逗号分隔值文件格式，其以纯文本形式存储表格数据（数字和文本），可以用Excel软件打开。

• xlrd 模块既可读取 xls 文件也可读取 xlsx 文件；xlwt只可写 xlsx 文件；openpyxl 可以读写 xlsx 文件；pandas 可以同时读写 xls、xlsx 文件。

注：xlrd 2.0.0 及更高版本只能读取 .xls 文件，建议使用其他库读取 .xlsx文件 或 安装 xlrd 早期版本。

1.使用 xlrd 来处理

xlrd命令

import xlrd
book = xlrd.open_workbook('excelFile.xlsx')     #获取工作簿对象
names = book.sheet_names()     #获取所有工作表名称结果为列表

mySheets = book.sheets()        #获取工作表list
sheet = mySheets[0]    #通过索引顺序获取
sheet = book.sheet_by_index(0)  #通过索引顺序获取
sheet = book.sheet_by_name(u'Sheet1')   #通过名称获取 u表示后面字符串以 Unicode 格式 进行编码，一般用在中文字符串前面

#获取行数和列数
nrows = sheet.nrows
ncols = sheet.ncols

#获取一行和一列
row = sheet.row_values(i)   #i是行数，从0开始计数，返回list对象。
col = sheet.col_values(j)   #j是列数，从0开始计数，返回list对象。

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

示例1：Python读取Excel文件特定数据

import xlrd
data = xlrd.open_workbook('excelFile.xlsx') #打开xlsx文件
table = data.sheets()[0]    #打开第一张表
nrows = table.nrows #获取表的行数

#循环逐行输出
for i in range(nrows):
    if i == 0: #跳过第一行
        continue
    print(table.row_values(i)[:5]) #取前五列数据

示例2：Python读取Excel文件所有数据

import xlrd
workbook = xlrd.open_workbook('excelFile.xlsx')     #打开一个xlsx文件
worksheets = workbook.sheet_names() #抓取所有sheet页的名称
print('worksheets is %s' %worksheets)
worksheets1 = workbook.sheet_by_name(u'Sheet1') #如果存在sheet1文件则定位到sheet1

#遍历sheet1中所有行row
num_rows = worksheets1.nrows
for curr_row in range(num_rows):
    row = worksheets1.row_values(curr_row)
    print('row%s is %s' %(curr_row,row))
    
    
#遍历sheet1中所有列col
num_cols = worksheets1.ncols
for curr_col in range(num_cols):
    col = worksheets1.col_values(curr_col)
    print('col%s is %s' %(curr_col,col))

# 遍历sheet1中所有单元格cell

for rown in range(num_cols):
    for coln in range(num_cols):
        cell = worksheets1.cell_value(rown,coln)
        print(cell)

2.使用 xlwt 来处理

使用xlwt模块写入Excel文件

xlwt模块只能写xls文件，不能写xlsx文件(写xlsx程序不会报错，但最后文件无法直接打开，会报错)。

默认保存在py文件相同路径下，如果该路径下有相同文件，会被新创建的文件覆盖。

import xlwt

myWorkbook =xlwt.Workbook() #创建Excel工作簿 若要写入中文则添加参数encoding = 'utf-8'
sheet = myWorkbook.add_sheet('New_Sheet')   #创建Excel工作表

#sheet.write(m,n,'内容1') #向单元格写入内容
myStyle = xlwt.easyxf('font:name Times New Roman,color-index red ,bold on',num_format_str='#,##0.00')
sheet.write(1,1,2022,myStyle)
sheet.write(2,0,1)  #写入A3，数值等于1
sheet.write(2,1,1)  #写入B3，数值等于1
sheet.write(2,2,xlwt.Formula('A3+B3'))  #写入C3，数值等于2（A3+B3）,xlwt支持写入公式

#保存
myWorkbook.save('excelFile.xls')

示例：新建excel文件并写入数据

import xlwt

#创建workbook和sheet对象
workbook =xlwt.Workbook()
sheet1 = workbook.add_sheet('sheet1',cell_overwrite_ok=True)   
sheet2 = workbook.add_sheet('sheet2',cell_overwrite_ok=True)   

#向sheet页中写入数据
myStyle = xlwt.easyxf('font:name Times New Roman,color-index red ,bold on',num_format_str='#,##0.00')
sheet1.write(0,0,'sheet1')
sheet1.write(0,1,'sheet1_content') 
sheet2.write(0,0,'sheet2')
sheet2.write(1,2,'sheet2_content') 

#保存该excel文件，有同名文件时直接覆盖
workbook.save('test.xls')
print('创建excel文件完成！')

3.使用 openpyxl 来处理

openpyxl可以对excel文件进行读写操作

openpyxl模块可实现对excel文件的读、写和修改，只能处理xlsx文件，不能处理xls文件。

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.writer.excel import ExcelWriter

wb = load_workbook(u'成绩单.xlsx')

sheetnames = wb.sheetnames #获取表单名字
print(sheetnames)

title = sheet1.title #获取工作表名称
rows = sheet1.max_row #获取工作表行数
cols = sheet1.max_column #获取工作表列数

sheet = wb[sheetnames[0]]
print(sheet.cell(row = 3,column = 3).value)

sheet['A1'] = 'grade'
wb.save(u'成绩单_new.xlsx') #修改元素值并另存为xlsx文件

sheet = wb.active #获取活动表
print(sheet.dimensions) #获取表格的尺寸大小

cell1 = sheet['B7'] #获取B7单元格的数据
print(cell1.value) #cell1.value获取单元格B7中的值

print(sheet['a2'].value) # 使用excel单元格的表示法，字母不区分大小写 获取第2行第1列的数据

print(cell.value,cell.row,cell.column,cell.coordinate)#获取某个格子的行数、列数以及坐标

cell = sheet['A1:A5'] #使用sheet['A1:A5']获取A1到A5的数据

#打印A1到A5的数据
for i in cell:
    for j in i:
        print(i.value)

# openpyxl读取excel文件
book = openpyxl.Workbook()# 创建工作簿 如果写入中文为乱码，可添加参数encoding ='utf-8'
sheet = book.create_sheet('Sheet_name',0) # 创建工作表,0表示创建的工作表在工作簿最前面
sheet.cell(m,n,'content1') #向单元格写入内容:
book.save('excelFile') # 保存工作簿,默认保存在py文件相同路径下，如果该路径下有相同文件，会被新创建的文件覆盖。

#openpyxl修改excel文件

sheet.insert rows(m)和sheet.insert cols(n)分别表示在第m行、第n列前面插入行、列
sheet.delete_rows(m)和sheet.delete_cols(n)分别表示删除第m行、第n列
修改单元格内容:sheet.cell(m,n)='内容1'或者sheet['B3']='内容2'
在最后追加行:sheet.append(可选代对象)

4.使用Pandas库来处理excel数据

Pandas 可以从各种文件格式比如 CSV、JSON、SQL、Microsoft Excel 导入数据。

import pandas as pd

mydata ={
    'sites':["SZ”,"BJ","SS"]
    'number':[1，2，3]
    
}

myvar = pd.DataFrame(mydata)
print(myvar)

output :
     sites  number
0    SZ     1
1    BJ     2
2    SS     3

import pandas as pd

file path =r'example.xlsx'
df = pd.read_excel(file_path,sheet_name = "test") # sheet_name不指定时默认返回全表数据

print(df) # 打印表数据，如果数据太多，会略去中间部分
print(df.head())#打印头部数据，仅查看数据示例时常用
print(df.columns)#打印列标题
print(df.index)#打印行
print(df["ave"])#打印指定列
# 描述数据
print(df.describe())

写excel

from pandas import DataFrame

data ={'name':['zs','ls','ww'],'age':[11,12,13],'gender':['man','man ,'woman']}
df = DataFrame(data)
df.to excel('new.xlsx')

修改excel–以修改原Excel文件中gender列数据为例，把girl修改为female，boy修改为male：

import pandas  as pd
from  pandas import DataFrame

file_path = r'test.xlsx'
df = pd.read_excel(file_path)

df['gender'][df['gender'] == 'girl'] = 'female'
df['gender'][df['gender'] == 'boy'] = 'male'

print(df)

DataFrame(df).to_excel(file_path,sheet_name = 'Sheet1',index = False,header=True)

df.loc[row_index] = [val1,val2,val3] #新增行
df[colo_name] = None #新增列

示例1：读取excel数据

#直接默认读取到这个Excel的第一个表单
sheet = pd.read_excel('test.xlsx')

# 默认读取前5行数据

data=sheet.head()
print("获取到所有的值:\n{0}".format(data))# 格式化输出

#也可以通过指定表单名来读取数据
sheet2 = pd.read_excel('test.xlsx',sheet_name='test')
data2 = sheet2.head() #默认读取前5行数据
print("获取到所有的值:\n{0}".format(data2)) # 格式化输出

示例2：操作Excel中的行列

#导入pandas模块
import pandas as pd
sheet=pd.read excel('test.xlsx') # 这个会直接默认读取到这个Excel的第一个表单

# 读取指定的某一行数据:

data=sheet.loc[0].values #0表示第一行 这里读取数据并不包含表头
print("读取指定行的数据:\n{0}".format(data))

# 读取指定的多行:
data2=sheet.loc[[0.1]].values
print("读取指定行的数据:\n{0}".format(data2))

#获取行号输出:
print("输出行号列表",sheet.index.values)
#获取列名输出:
print("输出列标题",sheet.columns.values)

其他

当收到的文件既有xls，又有xlsx时，先转换为统一格式再做统计修改更方便。

需要用到pywin32库，方法如下：

import win32com.client as win32

fname = r"C:\User\Desktop\test.xlsx'
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(fname)

# wb.SaveAs(fname+"x",FileFormat =51) #FileFormat =5l is for .xlsx extension
wb.SaveAs(fname[:-1],FileFormat = 56) #FileFormat =56 is for .xls extension

wb.Close()
excel.Application.Quit()