Python对Excel文件的读取方法及模块安装

今天研究了一下Python对Excel文件的读取&＃xff0c;在此分享一下。

想通过python对Excel文件进行处理&＃xff0c;需要安装以下几个模块&＃xff1a;

• pip install xlrd
• pip install xlwt
• pip install xlutils
• pip install pyExcelerator


import xlrd#1.打开excel文件
workbook &＃61; xlrd.open_workbook(&＃39;test.xlsx&＃39;)
#2.抓取所有sheet的名称&＃xff0c;Python会以列表的方式保存
worksheets &＃61; workbook.sheet_names()
print(worksheets) # [&＃39;Sheet1&＃39;, &＃39;Sheet2&＃39;, &＃39;Sheet3&＃39;]#3.定位到Sheet1表单&＃xff0c;worksheet1是一个表单对象#方式一&＃xff1a;
worksheet1 &＃61; workbook.sheet_by_name(&＃39;Sheet1&＃39;)print(worksheet1) # #方式二&＃xff1a;
worksheet2 &＃61; workbook.sheets()[0]print(worksheet2) ## 4.遍历所有的表单对象
for worksheet_name in worksheets:worksheet &＃61; workbook.sheet_by_name(worksheet_name)print(worksheet)# 5.遍历所有的行
num_rows &＃61; worksheet1.nrows
for curr_row in range(num_rows):row &＃61; worksheet1.row_values(curr_row)print(&＃39;row%s is: %s&＃39;%(curr_row,row))# 6.遍历所有的列
num_cols &＃61; worksheet1.ncols
for curr_clo in range(num_cols):clo &＃61; worksheet1.col_values(curr_clo)print(&＃39;clo %s is: %s&＃39; %(curr_clo,clo))# 7.遍历所有的单元格&＃xff08;方法一从行开始&＃xff09;
for rown in range(num_rows):for coln in range(num_cols):cell &＃61; worksheet1.cell_value(rown,coln)print(cell)# 8.遍历所有的单元格&＃xff08;方法二从列开始&＃xff09;
for coln in range(num_cols):for rown in range(num_rows):cell &＃61; worksheet1.cell_value(rown,coln)print(cell)# 9.遍历所有单元格&＃xff08;方法三&＃xff09;
for rown in range(num_rows):for coln in range(num_cols):cell &＃61; worksheet1.cell(rown,coln).valueprint(cell)# 10.遍历所有单元格&＃xff08;方法四&＃xff09;
for rown in range(num_rows):for coln in range(num_cols):cell &＃61; worksheet1.row(rown)[coln].valueprint(cell)# 11.遍历所有单元格&＃xff08;方法五&＃xff09;
for coln in range(num_cols):for rown in range(num_rows):cell &＃61; worksheet1.col(coln)[rown].valueprint(cell)
3.案例一

读取Excel文件表单某一列的数据并保存至txt文件

&＃39;&＃39;&＃39;
需求&＃xff1a;
读取Excel文件 test.xlsx表单Sheet1里第二列数据&＃xff0c;并写入到以Sheet1名字命名的.txt文件中
&＃39;&＃39;&＃39;
import xlrd#1.打开excel文件
workbook &＃61; xlrd.open_workbook(&＃39;test.xlsx&＃39;)#2.抓取所有sheet的名称&＃xff0c;Python会以列表的方式保存
worksheets &＃61; workbook.sheet_names()
print(worksheets)#3.定位到Sheet1
worksheet &＃61; workbook.sheet_by_name(&＃39;Sheet1&＃39;)
print(worksheet)#4.将第二列数据读取出来col &＃61; worksheet.col_values(1)
print(col)#5.将第二列的内容写入到Sheet1.txt(以表单名字命名的txt文件)文本文件中length &＃61; len(col)
print(length)with open(worksheets[0]&＃43;".txt",&＃39;w&＃39;) as file_obj:for num in range(length):file_obj.write(col[num])file_obj.write(&＃39;\n&＃39;)num&＃43;&＃61;1

4.案例二

读取Excel文件所有表单所有数据并保存至以表单名字命名的txt文件:

&＃39;&＃39;&＃39;
需求&＃xff1a;
读取Excel文件 test.xlsx所有表单里的所有数据&＃xff0c;并写入到以表单名字命名的.txt文件中
&＃39;&＃39;&＃39;
import xlrd#1.打开excel文件
workbook &＃61; xlrd.open_workbook(&＃39;test.xlsx&＃39;)#2.抓取所有sheet的名称&＃xff0c;Python会以列表的方式保存
worksheets &＃61; workbook.sheet_names()
print(worksheets)#3.遍历所有表单
for worksheet_name in worksheets:worksheet &＃61; workbook.sheet_by_name(worksheet_name)print(worksheet)
#4.求表单sheet行数和列数num_rows &＃61; worksheet.nrowsnum_cols &＃61; worksheet.ncols
#5.将表单sheet里面的数据写入到以sheet名字命名的.txt文件中with open(worksheet_name&＃43;".txt",&＃39;w&＃39;) as file_obj:for rown in range(num_rows):for coln in range(num_cols):cell &＃61; worksheet.cell_value(rown,coln)file_obj.write(str(cell))file_obj.write(&＃39;\t&＃39;)file_obj.write(&＃39;\n&＃39;)