Python读写EXCEL文件方法合集

一、xlrd,xlwt

1、xlrd只能读取.xls后缀的EXCEL

import xlrd
　　
data =xlrd.open_workbook('test2.xls')
table = data.sheets()[0]
print(table.nrows) #打印行数
print(table.ncols) #打印列数
print(table.name)  #打印表名

for row_index in range(table.nrows):
    for col_index in range(table.ncols):
        print(table.cell(row_index,col_index).value)  # 遍历所有的cell的值

2、xlwt只能写入并创建.xls后缀的EXCEL

import xlwt

f = xlwt.Workbook() #创建工作簿

sheet2 = f.add_sheet(u'sheet2',cell_overwrite_ok=True) #创建sheet2
row0 = [u'姓名',u'年龄',u'出生日期',u'爱好',u'关系']
column0 = [u'小杰',u'小胖',u'小明',u'大神',u'大仙',u'小敏',u'无名']

#生成第一行
for i in range(0,len(row0)):
sheet2.write(0,i,row0[i],set_style('Times New Roman',220,True))

#生成第一列
for i in range(0,len(column0)):
sheet2.write(i+1,0,column0[i],set_style('Times New Roman',220))

sheet2.write(1,2,'1991/11/11')
sheet2.write_merge(7,7,2,4,u'暂无') #合并列单元格
sheet2.write_merge(1,2,4,4,u'好朋友') #合并行单元格

f.save('demo1.xlsx') #保存文件

二、openpyxl可以操作.xlsx、.xlsm后缀的文件

1、创建并写入

from openpyxl import Workbook
import time
 
book = Workbook()
sheet = book.active
 
sheet['A1'] = 56
sheet['A2'] = 43
 
now = time.strftime("%x")
sheet['A3'] = now
 
book.save("sample.xlsx")

2、读取

import openpyxl

book = openpyxl.load_workbook('sample.xlsx')
# 使用load_workbook()方法打开.xlsx文件

sheet = book.active

a1 = sheet['A1']
a2 = sheet['A2']
a3 = sheet.cell(row=3, column=1)

print(a1.value)
print(a2.value) 
print(a3.value)

三、xlsxwriter写入表格

1、将指定数据写入表格

# 导入依赖的模块
import xlsxwriter

# 数据准备
datas = (
    ['Rent', 1000],
    ['Gas',  100 ],
    ['Food', 300 ],
    ['Gym',  50  ],
)

# 创建表格
workbook = xlsxwriter.Workbook('ex02.xlsx')
worksheet = workbook.add_worksheet('data')

# 添加全局格式
bold = workbook.add_format({'bold': True})
# 添加money格式
money = workbook.add_format({'num_format': '$#,##0'})

# 添加表格头，附带格式
worksheet.write('A1', 'Item', bold)
worksheet.write('B1', 'Cost', bold)

# 数据表格偏移
row, col = 1, 0
# 添加数据
for item, cost in datas:
    # 按照行列单元格添加数据
    worksheet.write(row, col, item)
    worksheet.write(row, col+1, cost)
    row += 1

# 添加一列求和计数
worksheet.write(row, 0, 'Total',        bold)
worksheet.write(row, 1, '=SUM(B2:B5)', money)

# 存储退出
workbook.close()

2、在表格中绘制图表

# 引入依赖模块
import xlsxwriter

# 操作数据
data = [20, 45, 26, 18, 45]

# 创建表格
workbook = xlsxwriter.Workbook("ex04.xlsx")
worksheet = workbook.add_worksheet("data")

# 添加数据：一次添加多个数据
worksheet.write_column('A1', data)

# 创建图表
chart = workbook.add_chart({'type': 'line'})
# 图表添加数据
chart.add_series({
    'values': '=data!$A1:$A6',
    'name': '图表线名称',
    'marker': {
        'type': 'circle',
        'size': 8,
        'border': {'color': 'black'},
        'fill': {'color': 'red'}
    },
    'data_labels': {'value': True},
    'trendline': {
        'type': 'polynomial',
        'order': 2,
        'name': '示例趋势线',
        'forward': 0.5,
        'backward': 0.5,
        'display_equation': True,
        'line': {'color': 'red', 'width': 1, 'dash_type': 'long_dash'}
    }
})

worksheet.insert_chart('C1', chart)

workbook.close()

四、pandas操作表格

1、读取

import pandas as pd

df=pd.read_excel('test.xlsx')
height,width = df.shape
print(height,width,type(df))
print(df)

2、写入

import pandas as pd
import numpy as np

data = np.arange(1,101).reshape((10,10))
data_df = pd.DataFrame(data)
data_df.columns = ['A','B','C','D','E','F','G','H','I','J']
data_df.index = ['a','b','c','d','e','f','g','h','i','j']

writer = pd.ExcelWriter('my.xlsx')
data_df.to_excel(writer,float_format='%.5f')
writer.save()