search
HomeBackend DevelopmentPython TutorialHow to read and write excel files in python

How to read and write excel files in python

There are many ways to read and write excel in python. Different modules have slightly different reading and writing methods:

Use xlrd and xlwt to read and write excel;

Use openpyxl to read and write excel;

Use pandas to read and write excel;

For the convenience of demonstration, I created a new data.xlsx file here, the first worksheet The content of sheet1 area "A1:F5" is as follows, used to test the code for reading excel:

How to read and write excel files in python

1. Use xlrd and xlwt to read and write excel (xlwt does not support xlsx)

The first is to install the third-party modules xlrd and xlwt. Just enter the commands "pip install xlrd" and "pip install xlwt" directly, as follows (cmd→CD→c:pythonscripts):

How to read and write excel files in python

1. xlrd reads excel:

import xlrd
book = xlrd.open_workbook('data.xlsx')
sheet1 = book.sheets()[0]
nrows = sheet1.nrows
print('表格总行数',nrows)
ncols = sheet1.ncols
print('表格总列数',ncols)
row3_values = sheet1.row_values(2)
print('第3行值',row3_values)
col3_values = sheet1.col_values(2)
print('第3列值',col3_values)
cell_3_3 = sheet1.cell(2,2).value
print('第3行第3列的单元格的值:',cell_3_3)

Running result:

表格总行数 5
表格总列数 5
第3行值 ['3A', '3B', '3C', '3D', '3F']
第3列值 ['1C', '2C', '3C', '4C', '5C']
第3行第3列的单元格的值: 3C

2. xlwt writes excel

The main code is as follows:

workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('test')
worksheet.write(0,0,'A1data')
workbook.save('excelwrite.xls')

After the program runs, create a new excelwrite.xls workbook and insert the text worksheet. The content of A1 is A1data.

2. Use openpyxl to read and write excel. Note that this can only be the xlsx type of excel.

If you want to install it, just enter the command "pip install openpyxl" directly, and the installation will be completed soon.

Read Excel:

import openpyxl
workbook = openpyxl.load_workbook('data.xlsx')
worksheet = workbook.get_sheet_by_name('Sheet1')
row3=[item.value for item in list(worksheet.rows)[2]]
print('第3行值',row3)
col3=[item.value for item in list(worksheet.columns)[2]]
print('第3行值',col3)
cell_2_3=worksheet.cell(row=2,column=3).value
print('第2行第3列值',cell_2_3)
max_row=worksheet.max_row
print('最大行',max_row)

Run result:

第3行值 ['3A', '3B', '3C', '3D', '3F']
第3行值 ['1C', '2C', '3C', '4C', '5C']
第2行第3列值 2C
最大行 5

Write Excel:

import openpyxl
workbook = openpyxl.Workbook()
sheet=workbook.active
sheet['A1']='hi,wwu'
workbook.save('new.xlsx')

After running the program, create a new New.xls workbook and insert the sheet worksheet, the content of A1 is hi,wwu.

3. Use pandas to read excel

The name of Pandas comes from panel data and python data analysis.

First of all, you need to install the pandas module. Relatively speaking, installing the pandas module is more complicated.

If there is an error after installing with pip install pandas, you can consider installing the previous version: pip install pandas==0.22

pandas is a data processing package that itself provides many reading files. Functions, such as read_csv (read csv files), read_excel (read excel files), etc., can read files with just one line of code.

Read Excel:

import pandas as pd
df = pd.read_excel(r'data.xlsx',sheetname=0)
print(df.head())

Run result:

1A 1B 1C 1D 1F
0 2A 2B 2C 2D 2F
1 3A 3B 3C 3D 3F
2 4A 4B 4C 4D 4F
3 5A 5B 5C 5D 5F

Write Excel:

from pandas import DataFrame
data={
'name':['张三','李四','王五'],
'age':[11,12,13]
'sex':'男','女','男']
}
df=DataFrame(data)
df.to_excel('new.xlsx")

After the program runs, The new.xlsx file will be created (or replaced) and the content will be saved in the A1:D4 area of ​​sheet1 as follows:

How to read and write excel files in python

For more Python related technical articles, please visit Python Tutorial column to learn!

The above is the detailed content of How to read and write excel files in python. For more information, please follow other related articles on the PHP Chinese website!

Statement
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
Building High-Performance Computing Solutions with PythonBuilding High-Performance Computing Solutions with PythonJul 21, 2025 am 03:17 AM

Pythoncanbeusedeffectivelyforhigh-performancecomputing(HPC)byleveragingspecifictoolsandtechniques.1)UsecompiledextensionslikeNumPy,SciPy,Cython,andNumbaforfasternumericalcomputations.2)TakeadvantageofparallelismwithmultiprocessingforCPU-boundtasksand

Factory Method Pattern in PythonFactory Method Pattern in PythonJul 21, 2025 am 03:15 AM

The factory method pattern is a design pattern that instantiates specific classes through subclass decisions. It defines an interface to create objects, delaying the creation of objects to subclass processing, thereby achieving decoupling. This mode is suitable for scenarios such as hidden object creation details, uncertain future subclass types, and the need to call different objects in a unified interface. The implementation steps include: defining the base class or interface; creating multiple subclasses; writing factory functions or methods that return different instances according to parameters. Factory methods can be further encapsulated into classes to facilitate management of complex logic. When using it, you should pay attention to avoiding too many conditional judgments, preventing business logic from being mixed into the factory, avoiding over-design. It is also recommended to deal with abnormal inputs, keep the logic simple, and use it only when scalability is required.

Building a Chatbot with Python NLTKBuilding a Chatbot with Python NLTKJul 21, 2025 am 03:12 AM

It is feasible to use Python and NLTK as chatbots, but the goals and methods need to be clarified. 1. Install Python and NLTK and download the necessary corpus such as punkt, stopwords and wordnet. 2. The implementation process includes text preprocessing (word segmentation, stop word deactivation, word shape restoration), intent recognition or keyword matching, and response generation. 3. Simple response can be achieved through keyword matching, or classification models can be trained to improve the effect. 4. Extension directions include introducing more powerful NLP tools such as spaCy or Transformers, maintaining Q&A databases, and avoiding too much hardcoded logic. In short, it is suitable for introductory and small projects, with low deployment costs but strong controllability.

Image Processing with Python PillowImage Processing with Python PillowJul 21, 2025 am 03:11 AM

Pillow library image processing is very simple and suitable for daily operations. 1. Install pipinstallpillow and import the Image module to start; 2. You can open the picture and view width, height, format and other information; 3. Use crop to extract specific areas; 4. Use resize to zoom, pay attention to maintaining the proportion and avoiding deformation; 5. Use the draw.text method to add text watermarks, and specify the font path, position and color; 6. Use the paste method to overlay transparent layers in the image watermark; 7. Filter processing supports turning grayscale images, adjusting brightness contrast, etc.; 8. Although the Pillow function is basic, it is practical, and mastering common methods and document query can quickly complete the requirements.

Python for Distributed ComputingPython for Distributed ComputingJul 21, 2025 am 03:03 AM

Python is widely used in distributed computing because of its rich ecosystem and efficient development. 1. Distributed computing is to split tasks into multiple machines to perform to improve efficiency. Python is chosen because it has many libraries, easy to debug, and strong compatibility. 2. Common frameworks include Celery (asynchronous tasks), Dask (data science), PySpark (big data processing), and Ray (high-performance scheduling). 3. Celery can be used to build a simple system: install dependencies, write tasks, start worker, and trigger tasks. 4. Note points include task granularity, data lightweighting, failure retry, monitoring logs and task dependency management.

Python Regular Expressions TutorialPython Regular Expressions TutorialJul 21, 2025 am 03:02 AM

Regular expressions are used in Python to find, match, and replace text patterns. 1. Use re.search and re.match to determine whether the text contains a specific pattern. The former searches for the entire string, while the latter only starts from the beginning. 2. Extract content through brackets, such as using match.group(1) to obtain the required part when extracting the email address; 3. Use re.sub to replace sensitive words or format text, such as replacing the email with [EMAIL]; 4. Notes include escaping special characters, controlling greedy matching, ignoring uppercase and uppercase case and multi-line matching. Mastering these can quickly process text on mobile phones.

Building Cross-Platform Mobile Apps with Python BeeWareBuilding Cross-Platform Mobile Apps with Python BeeWareJul 21, 2025 am 03:01 AM

BeeWare is a tool for developing cross-platform mobile applications using Python, which enables a truly native experience through native controls. 1. It is based on the TogaUI toolkit and Briefcase packaging tool, and supports macOS, Windows, Linux, iOS and Android platforms; 2. Unlike Kivy, Flutter or ReactNative, it directly calls the platform API without bridging; 3. It is suitable for developers familiar with Python to carry out rapid prototype development and data-driven gadget-based app development; 4. The current version is more suitable for small and medium-sized or experimental projects, and there are still restrictions on scenarios with high requirements for complex UI and performance; 5. The steps to get started include installing Be

Implementing Edge Computing Solutions with PythonImplementing Edge Computing Solutions with PythonJul 21, 2025 am 02:56 AM

The core of Python's implementation of edge computing is to bring data processing and decision-making close to data sources, and improve efficiency by deploying lightweight services, executing local inference and establishing a cache upload mechanism. 1. Use Flask or FastAPI to deploy local API services on edge nodes to achieve fast response; 2. Use Python to perform data preprocessing and lightweight AI inference to reduce the amount of uploaded data; 3. Use SQLite to implement local cache and combine asynchronous upload to cope with network instability. At the same time, you need to pay attention to details such as dependency control, model size, retry strategy and resource occupation.

See all articles

Hot AI Tools

Undress AI Tool

Undress AI Tool

Undress images for free

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.

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.