Home Backend Development Python Tutorial How python processes excel data

How python processes excel data

Feb 28, 2019 am 10:21 AM
python

python处理excel数据的方法:1、使用xlrd来处理;2、使用【xlutils+xlrd】来处理;3、使用xlwt来处理;4、使用pyExcelerator来处理;5、使用Pandas库来处理。

How python processes excel data

这里有一张excel数据表,下面我们通过示例来看看xlrd、xlwt、xluntils、pyExcelerator和Pandas是如何处理excel文件数据的。【视频教程推荐:python教程

python处理excel数据的方法:

方法一:使用xlrd来处理excel数据

示例1:python读取excel文件特定数据

import xlrd
data = xlrd.open_workbook('test.xls') # 打开xls文件
table = data.sheets()[0] # 打开第一张表
nrows = table.nrows # 获取表的行数
# 循环逐行输出
for i in range(nrows): 
   if i == 0: # 跳过第一行
       continue
   print table.row_values(i)[:13] # 取前十三列数据
Copy after login

示例2:python读取excel文件所有数据

import xlrd
#打开一个xls文件
workbook = xlrd.open_workbook('test.xls')
#抓取所有sheet页的名称
worksheets = workbook.sheet_names()
print('worksheets is %s' %worksheets)
#定位到sheet1
worksheet1 = workbook.sheet_by_name(u'Sheet1')


"""
#通过索引顺序获取
worksheet1 = workbook.sheets()[0]
#或
worksheet1 = workbook.sheet_by_index(0)
"""
"""
#遍历所有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
Copy after login

方法二:使用xlutils+xlrd来处理excel数据

示例:向excel文件中写入数据

import xlrd
import xlutils.copy
#打开一个xls文件
rb = xlrd.open_workbook('test.xls')
wb = xlutils.copy.copy(rb)

#获取sheet对象,通过sheet_by_index()获取的sheet对象没有write()方法
ws = wb.get_sheet(0)

#写入数据
ws.write(1, 1, 'changed!')

#添加sheet页
wb.add_sheet('sheetnnn2',cell_overwrite_ok=True)

#利用保存时同名覆盖达到修改excel文件的目的,注意未被修改的内容保持不变
wb.save('test.xls')
Copy after login

方法三:使用xlwt来处理excel数据

示例1:新建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,'this should overwrite1')
sheet1.write(0,1,'aaaaaaaaaaaa')
sheet2.write(0,0,'this should overwrite2')
sheet2.write(1,2,'bbbbbbbbbbbbb')

#保存该excel文件,有同名文件时直接覆盖
workbook.save('test.xls')
print '创建excel文件完成!'
Copy after login

方法四:使用pyExcelerator来处理excel数据

示例1:读excel文件中的数据

import pyExcelerator
#parse_xls返回一个列表,每项都是一个sheet页的数据。
#每项是一个二元组(表名,单元格数据)。其中单元格数据为一个字典,键值就是单元格的索引(i,j)。如果某个单元格无数据,那么就不存在这个值
sheets = pyExcelerator.parse_xls('test.xls')
print sheets
Copy after login

示例2:新建excel文件并写入数据

import pyExcelerator
#创建workbook和sheet对象
wb = pyExcelerator.Workbook()
ws = wb.add_sheet(u'第一页')

#设置样式
myfont = pyExcelerator.Font()
myfont.name = u'Times New Roman'
myfont.bold = True
mystyle = pyExcelerator.XFStyle()
mystyle.font = myfont

#写入数据,使用样式
ws.write(0,0,u'ni hao 帕索!',mystyle)

#保存该excel文件,有同名文件时直接覆盖
wb.save('E:\\Code\\Python\\mini.xls')
print '创建excel文件完成!'
Copy after login

方法五:使用Pandas库来处理excel数据

示例1:读取excel数据

#导入pandas模块
import pandas as pd
#直接默认读取到这个Excel的第一个表单
sheet = pd.read_excel('test.xls')

#默认读取前5行数据
data=sheet.head()
print("获取到所有的值:\n{0}".format(data))#格式化输出


#也可以通过指定表单名来读取数据
sheet2=pd.read_excel('test.xlsx',sheet_name='userRegister')
data2=sheet2.head()#默认读取前5行数据
print("获取到所有的值:\n{0}".format(data2))#格式化输出
Copy after login

示例2:操作Excel中的行列

#导入pandas模块
import pandas as pd
sheet=pd.read_excel('webservice_testcase.xlsx')#这个会直接默认读取到这个Excel的第一个表单

#读取制定的某一行数据:
data=sheet.ix[0].values   #0表示第一行 这里读取数据并不包含表头
print("读取指定行的数据:\n{0}".format(data))

#读取指定的多行:
data2=sheet.ix[[0,1]].values 
print("读取指定行的数据:\n{0}".format(data2))

#读取指定行列的数据:
data3=sheet.ix[0,1]#读取第一行第二列的值
print("读取指定行的数据:\n{0}".format(data3))

#读取指定的多行多列的值:
data4=sheet.ix[[1,2],['姓名','电话']].values    #读取第二行第三行的姓名以及电话列的值,这里需要嵌套列表
print("读取指定行的数据:\n{0}".format(data4))

#读取所有行指定的列的值:
data5=sheet.ix[:,['姓名','电话']].values   #姓名以及电话列的值
print("读取指定行的数据:\n{0}".format(data5))

#获取行号输出:
print("输出行号列表",sheet.index.values)

#获取列名输出:
print("输出列标题",sheet.columns.values)
Copy after login

以上就是本篇文章的全部内容,希望能对大家的学习有所帮助。更多精彩内容大家可以关注php中文网相关教程栏目!!!

The above is the detailed content of How python processes excel data. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Is the conversion speed fast when converting XML to PDF on mobile phone? Is the conversion speed fast when converting XML to PDF on mobile phone? Apr 02, 2025 pm 10:09 PM

The speed of mobile XML to PDF depends on the following factors: the complexity of XML structure. Mobile hardware configuration conversion method (library, algorithm) code quality optimization methods (select efficient libraries, optimize algorithms, cache data, and utilize multi-threading). Overall, there is no absolute answer and it needs to be optimized according to the specific situation.

How to convert XML files to PDF on your phone? How to convert XML files to PDF on your phone? Apr 02, 2025 pm 10:12 PM

It is impossible to complete XML to PDF conversion directly on your phone with a single application. It is necessary to use cloud services, which can be achieved through two steps: 1. Convert XML to PDF in the cloud, 2. Access or download the converted PDF file on the mobile phone.

What is the function of C language sum? What is the function of C language sum? Apr 03, 2025 pm 02:21 PM

There is no built-in sum function in C language, so it needs to be written by yourself. Sum can be achieved by traversing the array and accumulating elements: Loop version: Sum is calculated using for loop and array length. Pointer version: Use pointers to point to array elements, and efficient summing is achieved through self-increment pointers. Dynamically allocate array version: Dynamically allocate arrays and manage memory yourself, ensuring that allocated memory is freed to prevent memory leaks.

Is there any mobile app that can convert XML into PDF? Is there any mobile app that can convert XML into PDF? Apr 02, 2025 pm 08:54 PM

An application that converts XML directly to PDF cannot be found because they are two fundamentally different formats. XML is used to store data, while PDF is used to display documents. To complete the transformation, you can use programming languages ​​and libraries such as Python and ReportLab to parse XML data and generate PDF documents.

How to convert xml into pictures How to convert xml into pictures Apr 03, 2025 am 07:39 AM

XML can be converted to images by using an XSLT converter or image library. XSLT Converter: Use an XSLT processor and stylesheet to convert XML to images. Image Library: Use libraries such as PIL or ImageMagick to create images from XML data, such as drawing shapes and text.

What is the process of converting XML into images? What is the process of converting XML into images? Apr 02, 2025 pm 08:24 PM

To convert XML images, you need to determine the XML data structure first, then select a suitable graphical library (such as Python's matplotlib) and method, select a visualization strategy based on the data structure, consider the data volume and image format, perform batch processing or use efficient libraries, and finally save it as PNG, JPEG, or SVG according to the needs.

Recommended XML formatting tool Recommended XML formatting tool Apr 02, 2025 pm 09:03 PM

XML formatting tools can type code according to rules to improve readability and understanding. When selecting a tool, pay attention to customization capabilities, handling of special circumstances, performance and ease of use. Commonly used tool types include online tools, IDE plug-ins, and command-line tools.

Is there a mobile app that can convert XML into PDF? Is there a mobile app that can convert XML into PDF? Apr 02, 2025 pm 09:45 PM

There is no APP that can convert all XML files into PDFs because the XML structure is flexible and diverse. The core of XML to PDF is to convert the data structure into a page layout, which requires parsing XML and generating PDF. Common methods include parsing XML using Python libraries such as ElementTree and generating PDFs using ReportLab library. For complex XML, it may be necessary to use XSLT transformation structures. When optimizing performance, consider using multithreaded or multiprocesses and select the appropriate library.

See all articles