首页 > 编程语言 >Python处理Excel数据的方法，这一篇文章就够了！！

Python处理Excel数据的方法，这一篇文章就够了！！

时间：2025-01-14 21:02:28浏览次数：3  
标签：xlsx sheet 文件 Python Excel 就够 print xls

Excel 是数据处理的“瑞士军刀”，在日常工作中扮演着重要角色。然而，面对复杂的 Excel 文件时，手动处理显然效率低下。那么，如何利用 Python 高效地处理 Excel 数据？xlrd、xlwt、openpyxl 和 pandas 是不可或缺的利器。今天，我们就来深度剖析这些工具，教你用 Python 优雅地操作 Excel！

你是否在为多张表格数据的格式清洗而烦恼？是否想过自动化生成报表却无从下手？哪种库更适合你的需求？本文将对比解析 Python 中的四大 Excel 操作库：xlrd、xlwt、openpyxl 和 pandas，帮你找到最佳选择！

当Excel中有大量需要进行处理的数据时，使用Python不失为一种便捷易学的方法。接下来，本文将详细介绍多种Python方法来处理Excel数据。Excel处理经常用于数据可视化，那么如何利用提取到的Excel数据绘图呢？

本文搭配Python绘图 \ 数据可视化一起使用效果更佳。

电子表格格式

我们在日常工作中常常见到各种后缀的电子表格，例如最常见的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 是一个专门用于读取 .xls 和 .xlsx 文件的库。

    • 支持的格式：Excel 97-2003（.xls）、Excel 2007+（.xlsx）。
    • 官方已停止对 .xlsx 的支持，新项目推荐使用 openpyxl。

  • 核心功能：

    1. 打开 Excel 文件
    2. 获取工作表及其数据
  • 优点：简单轻量，易于读取老版本 Excel 文件。
  • 缺点：不支持写操作；对 .xlsx 格式支持有限。

  • 代码示例：

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(i)  # i是列数，从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)

worksheet1 = workbook.sheet_by_name(u'Sheet1')  # 如果存在sheet1文件则定位到sheet1

"""
#遍历所有sheet对象
for worksheet_name in worksheets:
    worksheet = workbook.sheet_by_name(worksheet_name)
"""

# 遍历sheet1中所有行row
num_rows = worksheet1.nrows
for curr_row in range(num_rows):
    row = worksheet1.row_values(curr_row)
    print('row%s is %s' %(curr_row, row))

# 遍历sheet1中所有列col
num_cols = worksheet1.ncols
for curr_col in range(num_cols):
    col = worksheet1.col_values(curr_col)
    print('col%s is %s' %(curr_col, col))

# 遍历sheet1中所有单元格cell
for rown in range(num_rows):
    for coln in range(num_cols):
        cell = worksheet1.cell_value(rown, coln)
        print(cell)

 

2.使用 xlwt 来处理

使用xlwt模块写入Excel文件

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

  • 简介：
    xlwt 是专门用来写入 .xls 文件的库。适合生成简单的 Excel 报表。

  • 核心功能：

    1. 创建新工作簿
    2. 写入数据并设置样式
  • 优点：对 .xls 格式支持良好；样式设置灵活。
  • 缺点：不支持 .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)  # 写入A3，数值等于1
sheet.write(2, 0, 1)  # 写入B3，数值等于1
sheet.write(2, 1, 1)  # 写入C3，数值等于2（A3+B3），xlwt支持写入公式
sheet.write(2, 2, xlwt.Formula("A3+B3"))

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

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

import xlwt


# 创建workbook和sheet对象
workbook = xlwt.Workbook()  # 注意Workbook的开头W要大写
sheet1 = workbook.add_sheet('sheet1', cell_overwrite_ok=True)
sheet2 = workbook.add_sheet('sheet2', cell_overwrite_ok=True)

# 向sheet页中写入数据
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 来处理

  • 简介：
    xlrd 是一个专门用于读取 .xls 和 .xlsx 文件的库。

    • 支持的格式：Excel 97-2003（.xls）、Excel 2007+（.xlsx）。
    • 官方已停止对 .xlsx 的支持，新项目推荐使用 openpyxl。

  • 核心功能：

    1. 打开 Excel 文件
    2. 获取工作表及其数据
  • 优点：简单轻量，易于读取老版本 Excel 文件。
  • 缺点：不支持写操作；对 .xlsx 格式支持有限。

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)
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的数据
# print(cell)
# 打印A1到A5的数据
for i in cell:
    for j in i:
        print(j.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.xlsx')  # 保存工作簿，默认保存在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 导入数据。

  • 简介：
    xlwt 是专门用来写入 .xls 文件的库。适合生成简单的 Excel 报表。

  • 核心功能：

    1. 创建新工作簿
    2. 写入数据并设置样式
  • 优点：对 .xls 格式支持良好；样式设置灵活。
  • 缺点：不支持 .xlsx 格式；功能相对单一。

  • 代码示例：

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数据

# 导入pandas模块
import pandas as pd


# 直接默认读取到这个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 = 51 is for .xlsx extension
wb.SaveAs(fname[:-1], FileFormat = 56)     # FileFormat = 56 is for .xls extension
wb.Close()

excel.Application.Quit()

对比分析：哪个库更适合你？

功能点xlrdxlwtopenpyxlpandas
支持格式.xls, .xlsx.xls.xlsx.xls, .xlsx
读取数据✅❌✅✅
写入数据❌✅✅✅
样式支持❌✅✅❌
数据分析能力❌❌❌✅
性能高高中等中等

通过对比我们发现，xlrd 和 xlwt 适合轻量级的 Excel 操作；openpyxl 功能全面，适合处理现代 Excel 文件；而 pandas 则是数据分析领域的王者，适合大规模数据处理。根据你的需求选择合适的工具，能够大大提升工作效率。

Excel 的操作千变万化，但方法不止一条路。掌握这些工具后，你的 Excel 处理将从手动走向自动化，迈向全新的效率高度！ 

标签：xlsx,sheet,文件,Python,Excel,就够,print,xls
From： https://blog.csdn.net/m0_58552717/article/details/145146548

相关文章

  • python语言A站视频爬虫程序代码QZQ1
    importrequestsimportosimportsubprocess#https://ali-safety-video.acfun.cn/mediacloud/acfun/acfun_video/3fd2d78e1ebba085-529617cf38bbad5860227fbdf3a41546-hls_720p_2.00003.ts?pkey=ABC_F8k9Ed6OSnAdir8rrRmbYfeU39b5CvYeJQ3ttw8ZLQzlfk1NZNLJOlmwW-9ENIIuNL......
  • python语言tengxunshipin爬虫程序代码QZQ2
    importrequests#找媒体的请求url即可。url=‘https://f3e3963e336d9d3bdc18adcb0240e796.v.smtcdns.com/music.qqvideo.tc.qq.com/AIRFhqAd3UEXqwLOz5sfupz_V8TD-xZxVeAZnZUXZJYg/B_JxNyiJmktHRgresXhfyMep_mLAvgwYmAjetftmCCCW-f7a09P0_-_3BS3XuKJsUR/k0012md5982.mp4......
  • Python 文件和异常捕获（详解）
            前言：在Python编码中，我们会学到python中的文件的读取与写入，当然还有对文件夹的操作，在文章的最后还有异常捕获的详细解释~~一.文件的概念：        有名称：每个文件都有一个文件名，用于在特定的文件系统中唯一标识该文件，方便用户和系统对文件进行识别、访......
  • Python用Lasso改进线性混合模型Linear Mixed Model分析拟南芥和小鼠复杂性状遗传机制
    全文链接：https://tecdat.cn/?p=38800原文出处：拓端数据部落公众号在生物医学领域，探究可遗传性状的遗传基础是关键挑战之一。对于受多基因位点多因素控制的性状，准确检测其关联存在诸多困难，且易受群体结构等混杂因素影响产生假阳性结果。本文帮助客户建立Lasso线性混合模型，它能实现......
  • Python线性混合效应回归LMER分析大鼠幼崽体重数据、假设检验可视化
    全文链接：https://tecdat.cn/?p=38816原文出处：拓端数据部落公众号在数据分析领域，当数据呈现出层次结构时，传统的一般线性模型（GLM）可能无法充分捕捉数据的特征。混合效应回归作为GLM的扩展，能够有效处理这类具有层次结构的数据，如聚类数据、重复测量数据和纵向数据等。本文将深入探讨......
  • 关于python的初步学习
    python中的数据类型：整数（int）：用于表示整数，例如-5，0，10等。浮点数（float）：用于表示小数，例如3.14，-0.5等。字符串（str）：用于表示文本，例如"Hello"，'World'等。布尔值（bool）：用于表示真或假，只有两个值，True和False。列表（list）：用于存储一组有序的数据，可以包含不同类型的元素，例如[1,2,3]......
  • 【python】OpenCV—Extract Horizontal and Vertical Lines—Morphology
    文章目录1、功能描述2、代码实现3、效果展示4、完整代码5、参考更多有趣的代码示例，可参考【Programming】1、功能描述基于opencv-python库，利用形态学的腐蚀和膨胀，提取图片中的水平或者竖直线条2、代码实现导入基本的库函数importnumpyasnpimportcv2as......
  • ExcelConvert【Excel转换为XML JSON HTML CSV TXT】
    ExcelConverter是用VB6开发的应用软件。界面如下 第一部分：转换为XML首先在Excel选择一部分数据。 软件里面勾选“首列作为元素名称”，单击“转换”勾选与不勾选，得到如下XML数据，注意对比。<root><金龙宇性别="男"年龄="29"民族="汉族"籍贯="上海"/><姚晨......
  • 批量生成并打印文档Excel2Word
    Excel2Word是一款利用Excel数据生成Word文档的工具。 使用过程主要分为两部分：Word模板制作批量生成Word（生成、保存、打印可选） 其中Word模板制作，是在Word模板中插入书签，并且与Excel标题行的列名关联。批量生成Word，是在Excel中选中多行，然后点击生成即可。 以下结......
  • AI绘画stable diffusion入门基础教程（非常详细），AI绘画入门到精通，收藏这一篇就够了！
    现在学习AI绘画的人越来越多，这是属于"生成式人工智能（AIGC）"的春天。4月初，第一批被AI取代的人已出现，据媒体报道，一家游戏公司的原画团队被大面积裁员；4月末，AI又悄悄将手"伸"向网店模特岗位，使用AI模特取代真人模特只需要多投喂数据集，例如采用loRa在stablediffusion上训练出一个......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99