您现在的位置：程式師世界 >> 編程語言 > >> 更多編程語言 >> Python

Making query and generation program of local excel with Python

編輯：Python

Catalog

Preface

Hello everyone

Today I'll teach you how to use Python Make local Excel Query and generate program

demand

Make a program There is a simple query entry Realization Excel Query and generation of

The experimental steps

1 Open one exe An interface pops up

2 There is a query Card number Click to query

3 The query results are shown below At the same time, the result of this query Append to a new result Excel In the document

4 New results Excel file The format is the same as that of the source file But every time I add at the end

Today I'll teach you how to use Python Make local Excel Query and generate program

Excel The preview image

1.2 Import the module and read Excel file

The modules to be used later are ：pandas、os、xlwt and uuid

use import Imported code ：

import pandas, os, xlwt, uuid

After import , You have to read Excel The file . Read Excel Want to use pandas Of read_excel function .

try:
exl = pandas.read_excel(aim_path)
except:
print(' No files found ！ Please check whether the file path or file exists ')
os._exit(0)

Just imported os The module is used to do exception capture and exit when the file cannot be found .

Inquire about

2.1 Excel Index and input of

For the convenience of later query , To put DataFrame The index of （index） Set to query the entered card number . next , Output... Indexed by card number DF, So that users can query . Last , Start the cycle input .

exl.set_index(' Card number ', inplace = True)
print(f'{exl}\n')
while 1:
try:
idx = input(' Card number （ Input “ sign out ” You can exit ）：')
if idx == ' sign out ':
os._exit(0)

2.2 Start searching 、 Enrichment program

For inquiry dataframe.loc[index] To complete , Finally output the returned Series. To prevent users from entering non card number information , Exception capture is added .

 res = exl.loc[idx]
print(f'\n{res}\n')
except KeyError:
print(' You may have entered the wrong card number ！ I can't find the person with this card number ~\n')
continue
except:
print(' Some mistakes have occurred ！\n')
continue

Append query results to Excel

3.1 Read or create Excel

3.1.1 Read

Read as above , use read_excel

 try:
res_exl = pandas.read_excel(res_path)

3.1.2 newly build Workbook and Sheet

Now it's... Turn xlwt The modules are showing their skills ~ use Workbook Function to create a new Workbook; use add_sheet Function added Sheet

 except:
workbook = xlwt.Workbook()
sheet = workbook.add_sheet('new')
col = 0

3.1.2 write in Column

stay Column The location of , You need to fill in the query Excel The column index , use

list(pandas.read_excel(aim_path).columns.values)

Can be obtained . Then index the columns with xlwt.write Fill in , Finally, put DF Save and read this Excel.

for i in list(pandas.read_excel(aim_path).columns.values):
sheet.write(0, col, i)
col += 1
workbook.save(res_path)
res_exl = pandas.read_excel(res_path)

3.2 Append results

First , Result res The variable is set to list type . then , Add a new card number in this list . Finally, set the list to a Series（ The index is a query Excel The column index ）.

 res_series_data = list(res)
res_series_data.insert(2, idx)
res_series = pandas.Series(
res_series_data,
index = list(
pandas.read_excel(aim_path).columns.values
)
)

Now it's built Series, Ready to add . Save this after appending Excel.

 res_exl.loc[str(uuid.uuid1())] = res_series
try:
res_exl.to_excel(res_path, index = False)
except:
print(' Write failure ')

This is used here. uuid.uuid1 To randomly generate indexes , Avoid repetition and modify other people's values . The last few lines are the save operation , python index = False The index is hidden .

Complete code

try:
exl = pandas.read_excel(aim_path)
except:
print(' No files found ！ Please check whether the file path or file exists ')
os._exit(0)
exl.set_index(' Card number ', inplace = True)
print(f'{exl}\n')
while 1:
try:
idx = input(' Card number （ Input “ sign out ” You can exit ）：')
if idx == ' sign out ':
os._exit(0)
res = exl.loc[idx]
print(f'\n{res}\n')
except KeyError:
print(' You may have entered the wrong card number ！ I can't find the person with this card number ~\n')
continue
except:
print(' Some mistakes have occurred ！\n')
continue
try:
res_exl = pandas.read_excel(res_path)
except:
workbook = xlwt.Workbook()
sheet = workbook.add_sheet('new')
col = 0
for i in list(pandas.read_excel(aim_path).columns.values):
sheet.write(0, col, i)
col += 1
workbook.save(res_path)
res_exl = pandas.read_excel(res_path)
res_series_data = list(res)
res_series_data.insert(2, idx)
res_series = pandas.Series(
res_series_data,
index = list(
pandas.read_excel(aim_path).columns.values
)
)
res_exl.loc[str(uuid.uuid1())] = res_series
try:
res_exl.to_excel(res_path, index = False)
except:
print(' Write failure ')