python读excel文件|python怎么读取excel文件-山九号

① python如何读取excel文件

1.首先说明我是使用的python3.5，我的office版本是2010，首先打开dos命令窗，安装必须的两个库，命令是：pip3 install xlrdPip3 install xlwt2.准备好excel，例如我的一个工作文件，我放在D盘/网络经验/11.xlsx，只有一个页签A，内容是一些销售数据3.打开pycharm，新建一个excel.py的文件，首先导入支持库import xlrdimport xlwt4.针对刚入门的新手，先介绍三个知识，第一个:获取excel的sheet名称，第二：获取excel行数与列数，第三：获取第几行第几列的具体值，这是最常用的三个知识点5.贴出代码，具体分析：(1)要操作excel，首先得打开excel，使用open_workbook(‘路径’)(2)要获取行与列，使用nrows(行)，ncols(列)(3)获取具体的值，使用cell(row,col).valueworkbook=xlrd.open_workbook(r'E:11.xlsx')print (workbook.sheet_names()) sheet2=workbook.sheet_by_name('A') nrows=sheet2.nrows ncols=sheet2.ncols print(nrows,ncols) cell_A=sheet2.cell(1,1).value print(cell_A)6.要在excel里写入值，就要使用write属性,重点说明写入是用到xlwt这个支援库，思路是先新建excel，然后新建页签B，然后将一组数据写入到B，最后保存为excel.xls，这里建议保存为2003的格式，大部分电脑都能打开，特别注意保存的excel的路径是在python工作文件的目录下面，贴出代码：stus = [['年', '月'], ['2018', '10'], ['2017', '9'], ['2016', '8']]Excel = xlwt.Workbook() # 新建excelsheet = Excel.add_sheet('B') #新建页签Brow = 0for stu in stus: col = 0 for s in stu: sheet.write(row, col, s) #开始写入 col = col + 1 row = row + 1Excel.save('Excel.xls') #保存关于python如何读取excel文件，青藤小编就和您分享到这里了。如果您对python编程有浓厚的兴趣，希望这篇文章可以为您提供帮助。如果您还想了解更多关于python编程的技巧及素材等内容，可以点击本站的其他文章进行学习。

② python怎么读取excel文件

1.读取Excel(需要安装xlrd)：

#-*- coding: utf8 -*-import xlrdfname = "reflect.xls"bk = xlrd.open_workbook(fname)shxrange = range(bk.nsheets)try:sh = bk.sheet_by_name("Sheet1")except:print "no sheet in %s named Sheet1" % fname#获取行数nrows = sh.nrows#获取列数ncols = sh.ncolsprint "nrows %d, ncols %d" % (nrows,ncols)#获取第一行第一列数据cell_value = sh.cell_value(1,1)#print cell_valuerow_list = []#获取各行数据for i in range(1,nrows):row_data = sh.row_values(i)row_list.append(row_data)

2.写入Excel（需安装pyExcelerator）

from pyExcelerator import *w = Workbook() #创建一个工作簿ws = w.add_sheet('Hey, Hades') #创建一个工作表ws.write(0,0,'bit') #在1行1列写入bitws.write(0,1,'huang') #在1行2列写入huangws.write(1,0,'xuan') #在2行1列写入xuanw.save('mini.xls') #保存

3.再举个自己写的读写Excel的例子读取reflect.xls中的某些信息进行处理后写入mini.xls文件中。

#-*- coding: utf8 -*-import xlrdfrom pyExcelerator import *w = Workbook()ws = w.add_sheet('Sheet1')fname = "reflect.xls"bk = xlrd.open_workbook(fname)shxrange = range(bk.nsheets)try:sh = bk.sheet_by_name("Sheet1")except:print "no sheet in %s named Sheet1" % fnamenrows = sh.nrowsncols = sh.ncolsprint "nrows %d, ncols %d" % (nrows,ncols)cell_value = sh.cell_value(1,1)#print cell_valuerow_list = []mydata = []for i in range(1,nrows):row_data = sh.row_values(i)pkgdatas = row_data[3].split(',')#pkgdatas.split(',')#获取每个包的前两个字段for pkgdata in pkgdatas:pkgdata = '.'.join((pkgdata.split('.'))[:2])mydata.append(pkgdata)#将列表排序mydata = list(set(mydata))print mydata#将列表转化为字符串mydata = ','.join(mydata)#写入数据到每行的第一列ws.write(i,0,mydata)mydata = []row_list.append(row_data[3])#print row_listw.save('mini.xls')

4.现在我需要根据Excel文件中满足特定要求的apk的md5值来从服务器获取相应的apk样本，就需要这样做：

#-*-coding:utf8-*-import xlrdimport osimport shutilfname = "./excelname.xls"bk = xlrd.open_workbook(fname)shxrange = range(bk.nsheets)try:#打开Sheet1工作表sh = bk.sheet_by_name("Sheet1")except:print "no sheet in %s named Sheet1" % fname#获取行数nrows = sh.nrows#获取列数ncols = sh.ncols#print "nrows %d, ncols %d" % (nrows,ncols)#获取第一行第一列数据cell_value = sh.cell_value(1,1)#print cell_valuerow_list = []#range(起始行,结束行)for i in range(1,nrows):row_data = sh.row_values(i)if row_data[6] == "HXB":filename = row_data[3]+".apk"#print "%s %s %s" %(i,row_data[3],filename)filepath = r"./1/"+filenameprint "%s %s %s" %(i,row_data[3],filepath)if os.path.exists(filepath):shutil.(filepath, r"./myapk/")

补充一个使用xlwt3进行Excel文件的写操作。

import xlwt3if __name__ == '__main__':datas = [['a', 'b', 'c'], ['d', 'e', 'f'], ['g', 'h']]#二维数组file_path = 'D:\test.xlsx'wb = xlwt3.Workbook()sheet = wb.add_sheet('test')#sheet的名称为test#单元格的格式style = 'pattern: pattern solid, fore_colour yellow; '#背景颜色为黄色style += 'font: bold on; '#粗体字style += 'align: horz centre, vert center; '#居中header_style = xlwt3.easyxf(style)row_count = len(datas)col_count = len(datas[0])for row in range(0, row_count):col_count = len(datas[row])for col in range(0, col_count):if row == 0:#设置表头单元格的格式sheet.write(row, col, datas[row][col], header_style)else:sheet.write(row, col, datas[row][col])wb.save(file_path)

输出的文件内容如下图：

注：以上代码在Python 3.x版本测试通过。

③ 如何用python读取excel文件

以下代码调试通过：

importxlrd#打开xls文件book=xlrd.open_workbook("test.xls")print"表单数量:",book.nsheetsprint"表单名称:",book.sheet_names()#获取第1个表单sh=book.sheet_by_index(0)printu"表单%s共%d行%d列"%(sh.name,sh.nrows,sh.ncols)print"第二行第三列:",sh.cell_value(1,2)

运行效果：

④ python怎么读取excel的数据

最近由于经常要用到Excel，需要根据Excel表格中的内容对一些apk进行处理，手动处理很麻烦，于是决定写脚本来处理。首先贴出网上找来的读写Excel的脚本。1.读取Excel(需要安装xlrd)：

2.写入Excel（需安装pyExcelerator）

12345678from pyExcelerator import *w = Workbook() #创建一个工作簿ws = w.add_sheet('Hey, Hades') #创建一个工作表ws.write(0,0,'bit') #在1行1列写入bitws.write(0,1,'huang') #在1行2列写入huangws.write(1,0,'xuan') #在2行1列写入xuanw.save('mini.xls') #保存

3.再举个自己写的读写Excel的例子读取reflect.xls中的某些信息进行处理后写入mini.xls文件中。

4.现在我需要根据Excel文件中满足特定要求的apk的md5值来从服务器获取相应的apk样本，就需要这样做：

252627282930313233#-*-coding:utf8-*-import xlrdimport osimport shutilfname = "./excelname.xls"bk = xlrd.open_workbook(fname)shxrange = range(bk.nsheets)try:#打开Sheet1工作表sh = bk.sheet_by_name("Sheet1")except:print "no sheet in %s named Sheet1" % fname#获取行数nrows = sh.nrows#获取列数ncols = sh.ncols#print "nrows %d, ncols %d" % (nrows,ncols)#获取第一行第一列数据cell_value = sh.cell_value(1,1)#print cell_valuerow_list = []#range(起始行,结束行)for i in range(1,nrows):row_data = sh.row_values(i)if row_data[6] == "HXB":filename = row_data[3]+".apk"#print "%s %s %s" %(i,row_data[3],filename)filepath = r"./1/"+filenameprint "%s %s %s" %(i,row_data[3],filepath)if os.path.exists(filepath):shutil.(filepath, r"./myapk/")

补充一个使用xlwt3进行Excel文件的写操作。

2526import xlwt3if __name__ == '__main__':datas = [['a', 'b', 'c'], ['d', 'e', 'f'], ['g', 'h']]#二维数组file_path = 'D:\test.xlsx'wb = xlwt3.Workbook()sheet = wb.add_sheet('test')#sheet的名称为test#单元格的格式style = 'pattern: pattern solid, fore_colour yellow; '#背景颜色为黄色style += 'font: bold on; '#粗体字style += 'align: horz centre, vert center; '#居中header_style = xlwt3.easyxf(style)row_count = len(datas)col_count = len(datas[0])for row in range(0, row_count):col_count = len(datas[row])for col in range(0, col_count):if row == 0:#设置表头单元格的格式sheet.write(row, col, datas[row][col], header_style)else:sheet.write(row, col, datas[row][col])wb.save(file_path)

输出的文件内容如下图：

注：以上代码在Python 3.x版本测试通过。

好了，python操作Excel就这么！些了，简单吧

⑤ python如何读取excel数据

上搜索一些教程怎么读写这种专业软件，里面的一些数据通过它的叫声好来读取相关的数据。

⑥ 怎么用python读取excel表格的数据

一、读excel表读excel要用到xlrd模块，官网安装（http://pypi.python.org/pypi/xlrd）。然后就可以跟着里面的例子稍微试一下就知道怎么用了。大概的流程是这样的：1、导入模块复制代码代码如下:import xlrd2、打开Excel文件读取数据复制代码代码如下:data = xlrd.open_workbook('excel.xls')3、获取一个工作表① table = data.sheets()[0] #通过索引顺序获取② table = data.sheet_by_index(0) #通过索引顺序获取③ table = data.sheet_by_name(u'Sheet1')#通过名称获取4、获取整行和整列的值（返回数组）复制代码代码如下:table.row_values(i)table.col_values(i)5、获取行数和列数复制代码代码如下:table.nrowstable.ncols6、获取单元格复制代码代码如下:table.cell(0,0).valuetable.cell(2,3).value就我自己使用的时候觉得还是获取cell最有用，这就相当于是给了你一个二维数组，余下你就可以想怎么干就怎么干了。得益于这个十分好用的库代码很是简洁。但是还是有若干坑的存在导致话了一定时间探索。现在列出来供后人参考吧：1、首先就是我的统计是根据姓名统计各个表中的信息的，但是调试发现不同的表中各个名字貌似不能够匹配，开始怀疑过编码问题，不过后来发现是因为空格。因为在excel中输入的时候很可能会顺手在一些名字后面加上几个空格或是tab键，这样看起来没什么差别，但是程序处理的时候这就是两个完全不同的串了。我的解决方法是给每个获取的字符串都加上strip（）处理一下。效果良好2、还是字符串的匹配，在判断某个单元格中的字符串（中文）是否等于我所给出的的时候发现无法匹配，并且各种unicode也不太奏效，网络过一些解决方案，但是都比较复杂或是没用。最后我采用了一个比较变通的方式：直接从excel中获取我想要的值再进行比较，效果是不错就是通用行不太好，个呢不能问题还没解决。二、写excel表写excel表要用到xlwt模块，官网下载（http://pypi.python.org/pypi/xlwt）。大致使用流程如下：1、导入模块复制代码代码如下:import xlwt2、创建workbook（其实就是excel，后来保存一下就行）复制代码代码如下:workbook = xlwt.Workbook(encoding = 'ascii')3、创建表复制代码代码如下:worksheet = workbook.add_sheet('My Worksheet')4、往单元格内写入内容复制代码代码如下:worksheet.write(0, 0, label = 'Row 0, Column 0 Value')5、保存复制代码代码如下:workbook.save('Excel_Workbook.xls')由于我的需求比较简单，所以这上面没遇到什么问题，唯一的就是建议还是用ascii编码，不然可能会有一些诡异的现象。当然xlwt功能远远不止这些，他甚至可以设置各种样式之类的。附上一点例子复制代码代码如下:Examples Generating Excel Documents Using Python's xlwtHere are some simple examples using Python's xlwt library to dynamically generate Excel documents.Please note a useful alternative may be ezodf, which allows you to generate ODS (Open Document Spreadsheet) files for LibreOffice / OpenOffice. You can check them out at:http://packages.python.org/ezodf/index.htmlThe Simplest Exampleimport xlwtworkbook = xlwt.Workbook(encoding = 'ascii')worksheet = workbook.add_sheet('My Worksheet')worksheet.write(0, 0, label = 'Row 0, Column 0 Value')workbook.save('Excel_Workbook.xls')Formatting the Contents of a Cellimport xlwtworkbook = xlwt.Workbook(encoding = 'ascii')worksheet = workbook.add_sheet('My Worksheet')font = xlwt.Font() # Create the Fontfont.name = 'Times New Roman'font.bold = Truefont.underline = Truefont.italic = Truestyle = xlwt.XFStyle() # Create the Stylestyle.font = font # Apply the Font to the Styleworksheet.write(0, 0, label = 'Unformatted value')worksheet.write(1, 0, label = 'Formatted value', style) # Apply the Style to the Cellworkbook.save('Excel_Workbook.xls')Attributes of the Font Objectfont.bold = True # May be: True, Falsefont.italic = True # May be: True, Falsefont.struck_out = True # May be: True, Falsefont.underline = xlwt.Font.UNDERLINE_SINGLE # May be: UNDERLINE_NONE, UNDERLINE_SINGLE, UNDERLINE_SINGLE_ACC, UNDERLINE_DOUBLE, UNDERLINE_DOUBLE_ACCfont.escapement = xlwt.Font.ESCAPEMENT_SUPERSCRIPT # May be: ESCAPEMENT_NONE, ESCAPEMENT_SUPERSCRIPT, ESCAPEMENT_SUBSCRIPTfont.family = xlwt.Font.FAMILY_ROMAN # May be: FAMILY_NONE, FAMILY_ROMAN, FAMILY_SWISS, FAMILY_MODERN, FAMILY_SCRIPT, FAMILY_DECORATIVEfont.charset = xlwt.Font.CHARSET_ANSI_LATIN # May be: CHARSET_ANSI_LATIN, CHARSET_SYS_DEFAULT, CHARSET_SYMBOL, CHARSET_APPLE_ROMAN, CHARSET_ANSI_JAP_SHIFT_JIS, CHARSET_ANSI_KOR_HANGUL, CHARSET_ANSI_KOR_JOHAB, CHARSET_ANSI_CHINESE_GBK, CHARSET_ANSI_CHINESE_BIG5, CHARSET_ANSI_GREEK, CHARSET_ANSI_TURKISH, CHARSET_ANSI_VIETNAMESE, CHARSET_ANSI_HEBREW, CHARSET_ANSI_ARABIC, CHARSET_ANSI_BALTIC, CHARSET_ANSI_CYRILLIC, CHARSET_ANSI_THAI, CHARSET_ANSI_LATIN_II, CHARSET_OEM_LATIN_Ifont.colour_index = ?font.get_biff_record = ?font.height = 0x00C8 # C8 in Hex (in decimal) = 10 points in height.font.name = ?font.outline = ?font.shadow = ?Setting the Width of a Cellimport xltwworkbook = xlwt.Workbook()worksheet = workbook.add_sheet('My Sheet')worksheet.write(0, 0, 'My Cell Contents')worksheet.col(0).width = 3333 # 3333 = 1" (one inch).workbook.save('Excel_Workbook.xls')Entering a Date into a Cellimport xlwtimport datetimeworkbook = xlwt.Workbook()worksheet = workbook.add_sheet('My Sheet')style = xlwt.XFStyle()style.num_format_str = 'M/D/YY' # Other options: D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]:mm:ss, mm:ss.0worksheet.write(0, 0, datetime.datetime.now(), style)workbook.save('Excel_Workbook.xls')Adding a Formula to a Cellimport xlwtworkbook = xlwt.Workbook()worksheet = workbook.add_sheet('My Sheet')worksheet.write(0, 0, 5) # Outputs 5worksheet.write(0, 1, 2) # Outputs 2worksheet.write(1, 0, xlwt.Formula('A1*B1')) # Should output "10" (A1[5] * A2[2])worksheet.write(1, 1, xlwt.Formula('SUM(A1,B1)')) # Should output "7" (A1[5] + A2[2])workbook.save('Excel_Workbook.xls')Adding a Hyperlink to a Cellimport xlwtworkbook = xlwt.Workbook()worksheet = workbook.add_sheet('My Sheet')worksheet.write(0, 0, xlwt.Formula('HYPERLINK("http://www.google.com";"Google")')) # Outputs the text "Google" linking to http://www.google.comworkbook.save('Excel_Workbook.xls')Merging Columns and Rowsimport xlwtworkbook = xlwt.Workbook()worksheet = workbook.add_sheet('My Sheet')worksheet.write_merge(0, 0, 0, 3, 'First Merge') # Merges row 0's columns 0 through 3.font = xlwt.Font() # Create Fontfont.bold = True # Set font to Boldstyle = xlwt.XFStyle() # Create Stylestyle.font = font # Add Bold Font to Styleworksheet.write_merge(1, 2, 0, 3, 'Second Merge', style) # Merges row 1 through 2's columns 0 through 3.workbook.save('Excel_Workbook.xls')Setting the Alignment for the Contents of a Cellimport xlwtworkbook = xlwt.Workbook()worksheet = workbook.add_sheet('My Sheet')alignment = xlwt.Alignment() # Create Alignmentalignment.horz = xlwt.Alignment.HORZ_CENTER # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTEDalignment.vert = xlwt.Alignment.VERT_CENTER # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTEDstyle = xlwt.XFStyle() # Create Stylestyle.alignment = alignment # Add Alignment to Styleworksheet.write(0, 0, 'Cell Contents', style)workbook.save('Excel_Workbook.xls')Adding Borders to a Cell# Please note: While I was able to find these constants within the source code, on my system (using LibreOffice,) I was only presented with a solid line, varying from thin to thick; no dotted or dashed lines.import xlwtworkbook = xlwt.Workbook()worksheet = workbook.add_sheet('My Sheet')borders = xlwt.Borders() # Create Bordersborders.left = xlwt.Borders.DASHED # May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.borders.right = xlwt.Borders.DASHEDborders.top = xlwt.Borders.DASHEDborders.bottom = xlwt.Borders.DASHEDborders.left_colour = 0x40borders.right_colour = 0x40borders.top_colour = 0x40borders.bottom_colour = 0x40style = xlwt.XFStyle() # Create Stylestyle.borders = borders # Add Borders to Styleworksheet.write(0, 0, 'Cell Contents', style)workbook.save('Excel_Workbook.xls')Setting the Background Color of a Cellimport xlwtworkbook = xlwt.Workbook()worksheet = workbook.add_sheet('My Sheet')pattern = xlwt.Pattern() # Create the Patternpattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12pattern.pattern_fore_colour = 5 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on…style = xlwt.XFStyle() # Create the Patternstyle.pattern = pattern # Add Pattern to Styleworksheet.write(0, 0, 'Cell Contents', style)workbook.save('Excel_Workbook.xls')TODO: Things Left to Document- Panes — separate views which are always in view- Border Colors (documented above, but not taking effect as it should)- Border Widths (document above, but not working as expected)- Protection- Row Styles- Zoom / Manification- WS Props?Source Code for reference available at: https://secure.simplistix.co.uk/svn/xlwt/trunk/xlwt/

⑦ 怎样用python批量读写excel文件

只需要一个读excel(比如pyopenxl)和写excel的库即可，其他都是业务逻辑

⑧ python怎么从excel中读取数据

#导入包

import xlrd

#设置路径

path='C:\Users\jyjh\Desktop\datap.xlsx'

#打开文件

data=xlrd.open_workbook(path)

⑨ 如何通过Python实现Excel文件读写

参考代码如下：# -*- coding: utf-8 -*- import xdrlib ,sysimport xlrddef open_excel(file= 'file.xls'):try:data = xlrd.open_workbook(file)return dataexcept Exception,e:print str(e)#根据索引获取Excel表格中的数据参数:file：Excel文件路径 colnameindex：表头列名所在行的所以，by_index：表的索引def excel_table_byindex(file= 'file.xls',colnameindex=0,by_index=0):data = open_excel(file)table = data.sheets()[by_index]nrows = table.nrows #行数ncols = table.ncols #列数colnames = table.row_values(colnameindex) #某一行数据 list =[]for rownum in range(1,nrows):row = table.row_values(rownum)if row:app = {}for i in range(len(colnames)):app[colnames[i]] = row[i] list.append(app)return list#根据名称获取Excel表格中的数据参数:file：Excel文件路径 colnameindex：表头列名所在行的所以，by_name：Sheet1名称def excel_table_byname(file= 'file.xls',colnameindex=0,by_name=u'Sheet1'):data = open_excel(file)table = data.sheet_by_name(by_name)nrows = table.nrows #行数 colnames = table.row_values(colnameindex) #某一行数据 list =[]for rownum in range(1,nrows):row = table.row_values(rownum)if row:app = {}for i in range(len(colnames)):app[colnames[i]] = row[i]list.append(app)return listdef main():tables = excel_table_byindex()for row in tables:print rowtables = excel_table_byname()for row in tables:print rowif __name__=="__main__":main()

⑩ 如何用python读excel文件

想要使用python实现对Excel文件的读写，首先需要安装专用的模块（如果你是大牛可以自己编回写）xlrd模块。答解压以后启动cmd命令窗口，在其中输入xlrd解压后所在的目录，执行安装命令如图所示（cmd命令的使用请自行网络，本机已经配置好了python环境才可以正常安装）在IDE环境中导入使用对应的xlrd模块，以eclipse环境为例如图所示impot xlrd即可打开Excel文件读取数据的简单示例如图所示:import xlrd后（最新的xlrd 0.9.4版本跨平台同时支持.xls和.xlsx）新手们在使用时会遇到：OSError: Invalid argument:XXX错误，这是文件I/O错误。在windows中要使用正斜杠（对：C:/bbbb.xlsx，错：('C:\bbbb.xlsx'）6使用xlrd模块简单读取excel中的sheet和行、列数据。sheets()[i],row_values(i),col_values(i)

未经允许不得转载：山九号 » python读excel文件|python怎么读取excel文件

python读excel文件|python怎么读取excel文件