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!