本文详细介绍如何使用Python中的xlrd、xlwt、openpyxl及xlsxwriter包读写Excel文件，包括包的比较、具体用法及源码示例，帮助读者掌握高效的数据处理技巧。
版权声明：本文为博主原创文章，未经博主允许不得转载。作者：沙师弟专栏 https://blog.youkuaiyun.com/u014597198/article/details/83104653 </div>
<link rel="stylesheet" href="https://csdnimg.cn/release/phoenix/template/css/ck_htmledit_views-f57960eb32.css">
<div id="content_views" class="markdown_views prism-atom-one-dark">
<!-- flowchart 箭头图标 勿删 -->**加粗样式**
<svg xmlns="http://www.w3.org/2000/svg" style="display: none;"><path stroke-linecap="round" d="M5,0 0,2.5 5,5z" id="raphael-marker-block" style="-webkit-tap-highlight-color: rgba(0, 0, 0, 0);"></path></svg>
<p></p><div class="toc"><h3><a name="t0"></a>Python3读写Excel文件</h3><ul><li><a href="#APython_1" rel="nofollow" target="_self">A、Python中几种常用包比较</a></li><li><a href="#BxlrdExcel_3" rel="nofollow" target="_self">B、用xlrd包读取Excel文件</a></li><ul><li><a href="#I_4" rel="nofollow" target="_self">I、用法</a></li><ul><li><a href="#1_5" rel="nofollow" target="_self">1、引用包</a></li><li><a href="#2_9" rel="nofollow" target="_self">2、打开文件</a></li><li><a href="#3sheet_13" rel="nofollow" target="_self">3、获取你要打开的sheet文件</a></li><li><a href="#4_21" rel="nofollow" target="_self">4、获取指定单元格里面的值</a></li><li><a href="#5_25" rel="nofollow" target="_self">5、获取某行或者某列的值</a></li><li><a href="#6sheet_31" rel="nofollow" target="_self">6、获取sheet的名称，行数，列数</a></li></ul><li><a href="#II_37" rel="nofollow" target="_self">II、源码示例</a></li></ul><li><a href="#CxlwtExcel_87" rel="nofollow" target="_self">C、尽量不用xlwt包写入Excel文件</a></li><ul><li><a href="#I_88" rel="nofollow" target="_self">I、原因</a></li><li><a href="#II_90" rel="nofollow" target="_self">II、如果写入过多，会报错</a></li><li><a href="#III_95" rel="nofollow" target="_self">III、源码示例</a></li></ul><li><a href="#DopenpyxlExcel_119" rel="nofollow" target="_self">D、用openpyxl包写入Excel文件</a></li><ul><li><a href="#I_120" rel="nofollow" target="_self">I、用法</a></li><ul><li><a href="#1_121" rel="nofollow" target="_self">1、引用包</a></li><li><a href="#2_125" rel="nofollow" target="_self">2、创建工作簿</a></li><li><a href="#3sheet_129" rel="nofollow" target="_self">3、创建sheet</a></li><li><a href="#4_133" rel="nofollow" target="_self">4、设置每个单元格里面的值</a></li><li><a href="#5_144" rel="nofollow" target="_self">5、保存文件</a></li></ul><li><a href="#II_149" rel="nofollow" target="_self">II、源码示例</a></li></ul><li><a href="#ExlsxwriterExcel_184" rel="nofollow" target="_self">E、用xlsxwriter包写入Excel文件</a></li><ul><li><a href="#I_185" rel="nofollow" target="_self">I、简介</a></li><li><a href="#II_187" rel="nofollow" target="_self">II、用法</a></li><ul><li><a href="#1_188" rel="nofollow" target="_self">1、引用包</a></li><li><a href="#2_192" rel="nofollow" target="_self">2、创建工作簿</a></li><li><a href="#3sheet_196" rel="nofollow" target="_self">3、创建sheet</a></li><li><a href="#4_200" rel="nofollow" target="_self">4、设置每个单元格里面的值</a></li><li><a href="#5_204" rel="nofollow" target="_self">5、关闭工作簿</a></li></ul><li><a href="#II_209" rel="nofollow" target="_self">II、源码示例</a></li></ul></ul></div><p></p>
A、Python中几种常用包比较
B、用xlrd包读取Excel文件
I、用法
1、引用包
import xlrd
- 1
2、打开文件
xlrd.open_workbook(r'/root/excel/chat.xls')
- 1
3、获取你要打开的sheet文件
# 获取所有sheet sheet_name = workbook.sheet_names()[0]<span class="token comment"># 根据sheet索引或者名称获取sheet内容</span> sheet <span class="token operator">=</span> workbook<span class="token punctuation">.</span>sheet_by_index<span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span> <span class="token comment"># sheet索引从0开始</span>
- 1
- 2
- 3
- 4
- 5
4、获取指定单元格里面的值
sheet.cell_value(第几行,第几列)
- 1
5、获取某行或者某列的值
# 获取整行和整列的值（数组）
rows = sheet.row_values(1) # 获取第2行内容
cols = sheet.col_values(2) # 获取第3列内容
- 1
- 2
- 3
6、获取sheet的名称，行数，列数
# sheet的名称，行数，列数
print (sheet.name,sheet.nrows,sheet.ncols)
- 1
- 2
II、源码示例
import xlrd
from datetime import date,datetime
arrayNum = 6
#array = {‘L1’:’’,‘L2’:’’,‘L3’:’’,‘L4’:’’,‘Question’:’’,‘Answer’:’’}
tables = []
newTables = []
def read_excel():
# 打开文件
workbook = xlrd.open_workbook(r’/root/chat.xls’)
# 获取所有sheet
sheet_name = workbook.sheet_names()[0]
<span class="token comment"># 根据sheet索引或者名称获取sheet内容</span>
sheet <span class="token operator">=</span> workbook<span class="token punctuation">.</span>sheet_by_index<span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">)</span> <span class="token comment"># sheet索引从0开始</span>
<span class="token comment"># sheet = workbook.sheet_by_name('Sheet1')</span>
<span class="token comment">#print (workboot.sheets()[0])</span>
<span class="token comment"># sheet的名称，行数，列数</span>
<span class="token keyword">print</span> <span class="token punctuation">(</span>sheet<span class="token punctuation">.</span>name<span class="token punctuation">,</span>sheet<span class="token punctuation">.</span>nrows<span class="token punctuation">,</span>sheet<span class="token punctuation">.</span>ncols<span class="token punctuation">)</span>
<span class="token comment"># 获取整行和整列的值（数组）</span>
rows <span class="token operator">=</span> sheet<span class="token punctuation">.</span>row_values<span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span> <span class="token comment"># 获取第2行内容</span>
<span class="token comment"># cols = sheet.col_values(2) # 获取第3列内容</span>
<span class="token keyword">print</span> <span class="token punctuation">(</span>rows<span class="token punctuation">)</span>
<span class="token comment"># print (cols)</span>
<span class="token keyword">for</span> rown <span class="token keyword">in</span> <span class="token builtin">range</span><span class="token punctuation">(</span>sheet<span class="token punctuation">.</span>nrows<span class="token punctuation">)</span><span class="token punctuation">:</span>
array <span class="token operator">=</span> <span class="token punctuation">{</span><span class="token string">'L1'</span><span class="token punctuation">:</span><span class="token string">''</span><span class="token punctuation">,</span><span class="token string">'L2'</span><span class="token punctuation">:</span><span class="token string">''</span><span class="token punctuation">,</span><span class="token string">'L3'</span><span class="token punctuation">:</span><span class="token string">''</span><span class="token punctuation">,</span><span class="token string">'L4'</span><span class="token punctuation">:</span><span class="token string">''</span><span class="token punctuation">,</span><span class="token string">'Question'</span><span class="token punctuation">:</span><span class="token string">''</span><span class="token punctuation">,</span><span class="token string">'Answer'</span><span class="token punctuation">:</span><span class="token string">''</span><span class="token punctuation">}</span>
array<span class="token punctuation">[</span><span class="token string">'L1'</span><span class="token punctuation">]</span> <span class="token operator">=</span> sheet<span class="token punctuation">.</span>cell_value<span class="token punctuation">(</span>rown<span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span>
array<span class="token punctuation">[</span><span class="token string">'L2'</span><span class="token punctuation">]</span> <span class="token operator">=</span> sheet<span class="token punctuation">.</span>cell_value<span class="token punctuation">(</span>rown<span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span>
array<span class="token punctuation">[</span><span class="token string">'L3'</span><span class="token punctuation">]</span> <span class="token operator">=</span> sheet<span class="token punctuation">.</span>cell_value<span class="token punctuation">(</span>rown<span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span>
array<span class="token punctuation">[</span><span class="token string">'L4'</span><span class="token punctuation">]</span> <span class="token operator">=</span> sheet<span class="token punctuation">.</span>cell_value<span class="token punctuation">(</span>rown<span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span>
array<span class="token punctuation">[</span><span class="token string">'Question'</span><span class="token punctuation">]</span> <span class="token operator">=</span> sheet<span class="token punctuation">.</span>cell_value<span class="token punctuation">(</span>rown<span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">)</span>
array<span class="token punctuation">[</span><span class="token string">'Answer'</span><span class="token punctuation">]</span> <span class="token operator">=</span> sheet<span class="token punctuation">.</span>cell_value<span class="token punctuation">(</span>rown<span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">)</span>
tables<span class="token punctuation">.</span>append<span class="token punctuation">(</span>array<span class="token punctuation">)</span>
<span class="token keyword">print</span> <span class="token punctuation">(</span><span class="token builtin">len</span><span class="token punctuation">(</span>tables<span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token comment">#print (tables)</span>
<span class="token comment">#print (tables[5])</span>
if name == ‘main’:
# 读取Excel
read_excel();
print (‘读取成功’)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
C、尽量不用xlwt包写入Excel文件
I、原因
在xlwt中生成的xls文件最多能支持65536行数据。
II、如果写入过多，会报错
由于数据太多，会报这个错误:
ValueError: row index (65536)not an intin range(65536)错误
- 1
III、源码示例
# 1. 导入模块
import xlwt
def write_excel():
# 2. 创建Excel工作薄
myWorkbook = xlwt.Workbook()
# 3. 添加Excel工作表
mySheet = myWorkbook.add_sheet('A Test Sheet')
# 4. 写入数据
myStyle = xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00') #数据格式
mySheet.write(i, j, 1234.56, myStyle)
mySheet.write(2, 0, 1) #写入A3，数值等于1
mySheet.write(2, 1, 1) #写入B3，数值等于1
mySheet.write(2, 2, xlwt.Formula("A3+B3")) #写入C3，数值等于2（A3+B3）
#5. 保存
myWorkbook.save('excelFile.xls')
if __name__ == '__main__':
# 写入Excel
write_excel();
print ('写入成功')
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
D、用openpyxl包写入Excel文件
I、用法
1、引用包
import openpyxl
- 1
2、创建工作簿
f = openpyxl.Workbook() #创建工作簿
- 1
3、创建sheet
sheet1 = f.create_sheet()
- 1
4、设置每个单元格里面的值
for jkey in range(len(newTables)):
jk = 1
for cT in range(arrayNum):
jk = jkey + 1
if cT == 0:
sheet1.cell(row=jk,column=cT+1).value='1'
else:
sheet1.cell(row=jk,column=cT+1).value='2'
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
5、保存文件
f.save("chatPy.xlsx") #保存文件
- 1
II、源码示例
import openpyxl
#写excel
def write_excel():
f = openpyxl.Workbook() #创建工作簿
sheet1 <span class="token operator">=</span> f<span class="token punctuation">.</span>create_sheet<span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token comment">#sheet1 = f.add_sheet(u'sheet1',cell_overwrite_ok=True) #创建sheet</span>
row0 <span class="token operator">=</span> <span class="token punctuation">[</span>u<span class="token string">'L1'</span><span class="token punctuation">,</span>u<span class="token string">'L2'</span><span class="token punctuation">,</span>u<span class="token string">'L3'</span><span class="token punctuation">,</span>u<span class="token string">'L4'</span><span class="token punctuation">,</span>u<span class="token string">'问题'</span><span class="token punctuation">,</span>u<span class="token string">'答案'</span><span class="token punctuation">]</span>
<span class="token comment">#生成第一行</span>
<span class="token comment">#for i in range(len(row0)):</span>
<span class="token comment"># sheet1.cell(column=i,row=0).value='L1')</span>
<span class="token comment">#生成后续</span>
<span class="token keyword">for</span> jkey <span class="token keyword">in</span> <span class="token builtin">range</span><span class="token punctuation">(</span><span class="token builtin">len</span><span class="token punctuation">(</span>newTables<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">:</span>
jk <span class="token operator">=</span> <span class="token number">1</span>
<span class="token keyword">for</span> cT <span class="token keyword">in</span> <span class="token builtin">range</span><span class="token punctuation">(</span>arrayNum<span class="token punctuation">)</span><span class="token punctuation">:</span>
jk <span class="token operator">=</span> jkey <span class="token operator">+</span> <span class="token number">1</span>
<span class="token keyword">if</span> cT <span class="token operator">==</span> <span class="token number">0</span><span class="token punctuation">:</span>
sheet1<span class="token punctuation">.</span>cell<span class="token punctuation">(</span>row<span class="token operator">=</span>jk<span class="token punctuation">,</span>column<span class="token operator">=</span>cT<span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">.</span>value<span class="token operator">=</span><span class="token string">'1'</span>
<span class="token keyword">else</span><span class="token punctuation">:</span>
sheet1<span class="token punctuation">.</span>cell<span class="token punctuation">(</span>row<span class="token operator">=</span>jk<span class="token punctuation">,</span>column<span class="token operator">=</span>cT<span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">.</span>value<span class="token operator">=</span><span class="token string">'2'</span>
f<span class="token punctuation">.</span>save<span class="token punctuation">(</span><span class="token string">"chatPy.xlsx"</span><span class="token punctuation">)</span> <span class="token comment">#保存文件</span>
if name == ‘main’:
# 写入Excel
write_excel();
print (‘写入成功’)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
E、用xlsxwriter包写入Excel文件
I、简介
于是我找到了xlsxwriter这个模块，它生成的文件后缀名为.xlsx，最大能够支持1048576行数据，16384列数据
II、用法
1、引用包
import xlsxwriter
- 1
2、创建工作簿
workbook = xlsxwriter.Workbook('demo1.xlsx')#创建一个excel文件
- 1
3、创建sheet
worksheet = workbook.add_worksheet(u'sheet1')#在文件中创建一个名为TEST的sheet,不加名字默认为sheet1
- 1
4、设置每个单元格里面的值
worksheet.write(3,0,35.5)#第4行的第1列设置值为35.5
- 1
5、关闭工作簿
workbook.close()
- 1
II、源码示例
import xlsxwriter
#写excel
def write_excel():
workbook = xlsxwriter.Workbook(‘chat.xlsx’)#创建一个excel文件
worksheet = workbook.add_worksheet(u’sheet1’)#在文件中创建一个名为TEST的sheet,不加名字默认为sheet1
worksheet.set_column(‘A:A’,20)#设置第一列宽度为20像素
bold= workbook.add_format({‘bold’:True})#设置一个加粗的格式对象
worksheet.write(‘A1’,‘HELLO’)#在A1单元格写上HELLO
worksheet.write(‘A2’,‘WORLD’,bold)#在A2上写上WORLD,并且设置为加粗
worksheet.write(‘B2’,U’中文测试’,bold)#在B2上写上中文加粗
worksheet.write(2,0,32)#使用行列的方式写上数字32,35,5
worksheet.write(3,0,35.5)#使用行列的时候第一行起始为0,所以2,0代表着第三行的第一列,等价于A4
worksheet.write(4,0,’=SUM(A3:A4)’)#写上excel公式
workbook.close()
if name == ‘main’:
# 写入Excel
write_excel();
print (‘写入成功’)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
</div>
<link href="https://csdnimg.cn/release/phoenix/mdeditor/markdown_views-258a4616f7.css" rel="stylesheet">
</div>
</article>
扫一扫
2952
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
