current position:Home>Three ways of generating Excel files with Python

Three ways of generating Excel files with Python

2022-02-02 11:56:57 Tu Tu Tu

In our daily work, we will encounter operation excel, So write an article today , How to use python operation excel

Of course python operation excel There are many libraries , such as pandas,xlwt/xlrd,openpyxl etc. , Each library has different differences , Specific differences , Let's have a look ~

 xlwt/xlrd

xlrd Is for Excel To read ,xlrd The operation is xls/xlxs Format excel

xlwt Is for Excel To read ,xlwt The operation is xls Format excel

install

xlrd yes python Of the 3 Fangku , Need to pass through pip Installation

pip install xlrd

1、 Import xlrd modular

2、 open Excel Complete instantiation

3、 Obtain the corresponding table by subscript ( You can get from the table name )

4、 Pass through column , Row or coordinate to get the data of the table

install

xlwt Belong to python Of the 3 Fangku , Need to pass through pip Installation

pip install xlwt

  write in Excel data

1、 First, import. xlwt The first 3 Fangku

2、 Create a workbook modular , It's equivalent to creating a xlwt file

3、 adopt add_sheet Create a table

4、 Use write Function to complete the operation of writing to the table

5、 Import the written data into Excel in

openpyxl

OpenPyXl It's a Python Module   It can be used to deal with excle form


install :

xlrd yes python Of the 3 Fangku , Need to pass through pip Installation

pip install openpyxl

Use it in the code  from openpyxl import Workbook perhaps from openpyxl import load_workbook
The difference is that the former does not need to have excle File exists   The latter needs to pass in a excle file
The former can produce a   The latter cannot

Use openpyxl  write in Excel data

1、 obtain workbook

2、 obtain worksheet

3、 And then Get the cell   To operate

4、 Save the file

pandas

write in Excel In addition to xlwt and openpyxl outside .Pandas It can also realize this function .

  It incorporates a large number of libraries and some standard data models , Provides the tools needed to operate large datasets efficiently , It enables us to process data quickly and easily . Next, let's see how to use pandas Reading and writing excel.

1. Read excel

Read excel Mainly through read_excel Function implementation , except pandas Third party libraries also need to be installed xlrd.

2. write in excel

write in excel Mainly through pandas structure DataFrame, call to_excel Method realization .

The data we are going to read today are some data from the used car website before climbing melon seeds , Some data are shown below :

 picture

What we want to show today is to use the three methods introduced above to txt The data of the document is written to excel in .

#  Title list 
columns = []
#  Data list 
datas = []

with open(' A used car .txt', encoding='utf-8') as fin:
    #  First line judgment 
    is_first_line = True
    for line in fin:
        line = line[:-1]
        if is_first_line:
            is_first_line = False
            columns = line.split('\t')
            continue
        datas.append(line.split('\t'))

ic(columns)
ic(datas)

The read row list and data list are shown below :

 picture

 

The data was successfully read out , Next, we use three ways to write to excel in

Use xlwt Generate xls Of excel file

#  Use xlwt Generate xls Of excel file 
import xlwt

workbook = xlwt.Workbook(encoding='utf-8')
sheet = workbook.add_sheet(' Melon seed used car ')

for col, column in enumerate(columns):
    sheet.write(0, col, column)

for row, data in enumerate(datas):
    for col, column_data in enumerate(data):
        sheet.write(row+1, col, column_data)

workbook.save(' Melon seed used car 1.xls')

 picture

Use openpyxl Generate xlsx Of excel file

#  Use openpyxl Generate xlsx Of excel file 
from openpyxl import Workbook
workbook = Workbook()

sheet = workbook.active
sheet.title = ' Default title'
sheet.append(columns)
for data in datas:
    sheet.append(data)
workbook.save(' Melon seed used car 2.xlsx')

 picture

  Use pandas Generate xlsx Of excel file

#  Use pandas Generate xlsx Of excel file 
import pandas as pd
rcv_data = pd.read_csv(' A used car .txt', sep='\t')
rcv_data.head()
ic(rcv_data)
rcv_data.to_excel(' Melon seed used car 3.xlsx', index = False)

 picture

These are the three kinds of data writing introduced to you today excel Methods ,

This writing method is still in high demand in practical work , Interested partners can operate

There is a problem , Comment interval ~~

copyright notice
author[Tu Tu Tu],Please bring the original link to reprint, thank you.
https://en.pythonmana.com/2022/02/202202021156550361.html

Random recommended