当前位置：物联沃-IOTWORD物联网 > 技术教程 > Python处理Excel数据的方法
代码收藏家代码收藏家 技术教程

Python处理Excel数据的方法

Python处理Excel数据的方法

  • 电子表格格式
  • 1.使用 xlrd 来处理；
  • 2.使用 xlwt 来处理；
  • 3.使用 openpyxl 来处理；
  • 4.使用Pandas库来处理excel数据
  • 其他

    • 当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文件。

    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(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程序不会报错，但最后文件无法直接打开，会报错)。

    默认保存在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() # 注意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 来处理；

    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')  # 保存工作簿,默认保存在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数据

    # 导入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()

    来源：Moster2469

    您可能感兴趣的内容:

    物联沃分享整理
    物联沃-IOTWORD物联网 » Python处理Excel数据的方法

    代码收藏家 普通

    分享到：

    相关推荐

    发表评论