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:
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):
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:
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!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1664
14
1423
52
1317
25
1268
29
1246
24
Python vs. C : Applications and Use Cases Compared
Apr 12, 2025 am 12:01 AM
Python is suitable for data science, web development and automation tasks, while C is suitable for system programming, game development and embedded systems. Python is known for its simplicity and powerful ecosystem, while C is known for its high performance and underlying control capabilities.
Python: Games, GUIs, and More
Apr 13, 2025 am 12:14 AM
Python excels in gaming and GUI development. 1) Game development uses Pygame, providing drawing, audio and other functions, which are suitable for creating 2D games. 2) GUI development can choose Tkinter or PyQt. Tkinter is simple and easy to use, PyQt has rich functions and is suitable for professional development.
Python vs. C : Learning Curves and Ease of Use
Apr 19, 2025 am 12:20 AM
Python is easier to learn and use, while C is more powerful but complex. 1. Python syntax is concise and suitable for beginners. Dynamic typing and automatic memory management make it easy to use, but may cause runtime errors. 2.C provides low-level control and advanced features, suitable for high-performance applications, but has a high learning threshold and requires manual memory and type safety management.
The 2-Hour Python Plan: A Realistic Approach
Apr 11, 2025 am 12:04 AM
You can learn basic programming concepts and skills of Python within 2 hours. 1. Learn variables and data types, 2. Master control flow (conditional statements and loops), 3. Understand the definition and use of functions, 4. Quickly get started with Python programming through simple examples and code snippets.
Python and Time: Making the Most of Your Study Time
Apr 14, 2025 am 12:02 AM
To maximize the efficiency of learning Python in a limited time, you can use Python's datetime, time, and schedule modules. 1. The datetime module is used to record and plan learning time. 2. The time module helps to set study and rest time. 3. The schedule module automatically arranges weekly learning tasks.
Python: Exploring Its Primary Applications
Apr 10, 2025 am 09:41 AM
Python is widely used in the fields of web development, data science, machine learning, automation and scripting. 1) In web development, Django and Flask frameworks simplify the development process. 2) In the fields of data science and machine learning, NumPy, Pandas, Scikit-learn and TensorFlow libraries provide strong support. 3) In terms of automation and scripting, Python is suitable for tasks such as automated testing and system management.
Python vs. C : Exploring Performance and Efficiency
Apr 18, 2025 am 12:20 AM
Python is better than C in development efficiency, but C is higher in execution performance. 1. Python's concise syntax and rich libraries improve development efficiency. 2.C's compilation-type characteristics and hardware control improve execution performance. When making a choice, you need to weigh the development speed and execution efficiency based on project needs.
Python: Automation, Scripting, and Task Management
Apr 16, 2025 am 12:14 AM
Python excels in automation, scripting, and task management. 1) Automation: File backup is realized through standard libraries such as os and shutil. 2) Script writing: Use the psutil library to monitor system resources. 3) Task management: Use the schedule library to schedule tasks. Python's ease of use and rich library support makes it the preferred tool in these areas.
