Python's operation on Excel -- classifying and summarizing the data in 278 excel tables

Begin to change 2022-05-14 15:09:29 阅读数:191

pythonoperationexcelclassifyingsummarizing

Catalog

One 、 subject

1、 describe

2、 Exhibition

① Original picture

② design sketch

​ Two 、 analysis

1、 Create workbook objects and worksheet objects

2、 get data

3、 data storage

4、 Data processing

3、 ... and 、 Source code


One 、 subject

1、 describe

from 278 Get the material number from the table 、 Material description, batch number and batch quantity , Add the quantity of products with the same material number , Get the total quantity of this material number , Then put it in a new table in descending order

2、 Exhibition

① Original picture

② design sketch

  Two 、 analysis

1、 Create workbook objects and worksheet objects

         After practicing several pairs excel After the operation of , Sum up a rule , No matter how you want to operate , The first thing to do is to get 、 Create a workbook object and get 、 Create a worksheet object ;

         Because the original workbook is not operated , So write to another workbook , When writing to a workbook, you must first create a workbook object and a worksheet object , To write , The source of the written data is the source file , So first get the source file, workbook object and worksheet object ;

wb = openpyxl.Workbook()# Create workbook object
ws = wb.active# Create a worksheet object
ws.title = ' Summary '# Give a name to a worksheet
ws.append([' Material number ', ' Material description ', ' Batch number '])# Write data , At this time, the incoming data can only be a list or tuple
all_wb = openpyxl.load_workbook(' Daily picking list .xlsx')# Get workbook object
all_ws = all_wb.worksheets# Get worksheet object , Because there are many worksheets in a workbook , Therefore, the worksheet object at this time stores tuples of the addresses of multiple worksheet objects 

2、 get data

        The obtained worksheet object stores the addresses of multiple worksheets , So we use traversal at this time in Take out the address of the stored object table in turn ;

        The object address of each row in the table is stored in the worksheet object , At this time, use the same method to get the address of each row in each table in turn ;

        Then get the object of the desired column through the object of each row , Get the desired value by getting the object value ;

for w in all_ws:
for row in range(4, w.max_row-3):

3、 data storage

        Because it's not easy to get the data , But to merge and sort the data , So the data taken out should be stored ;

        Because every time we get the data, there is a corresponding relationship , So use a list to process , Use the material number as the key of the big dictionary , The value is a small dictionary composed of material description and batch quantity , Then add the large dictionary to another dictionary ;

        When added to the dictionary , To remove the weight and sum the batch quantity , Because the key of the dictionary is unique , And you can judge whether it already exists by the key of the dictionary , If it already exists , Then we need to sum the values of batch data , If it doesn't exist, add it to the dictionary ;

for w in all_ws:
for row in range(4, w.max_row-3):
if w['K'+str(row)].value not in res.keys():
res.update({w['K'+str(row)].value:{' Material description ':w['P'+str(row)].value,
' Batch number ':int(w['N'+str(row)].value)}})
else:
res[w['K'+str(row)].value][' Batch number '] += int(w['N'+str(row)].value)
li = list(res.items())

4、 Data processing

        After the data has been stored in the dictionary , Another operation to be done is to sort according to the value of batch quantity , Because the dictionary is out of order , So first turn the dictionary into a list ; First, read out the keys and values in the dictionary through , A key value pair is stored in a tuple , Convert tuples to lists , Then sort the list ;

        Sorting uses anonymous functions lambda, Because when stored, it is a small dictionary in a large dictionary set , And traversing the big dictionary

, That is to take out the key value pairs in the dictionary and store them in tuples , And there is a small dictionary stored in the dictionary point , So there are dictionaries stored in Yuanzu , The value to be sorted is the value in the dictionary , So we need to get the corresponding value through the key of the dictionary

        A set of small dictionaries can also be replaced by lists , At this point, it will be easier to get the value

3、 ... and 、 Source code

import openpyxl
wb = openpyxl.Workbook()
ws = wb.active
ws.title = ' Summary '
ws.append([' Material number ', ' Material description ', ' Batch number '])
# K4--- Material number ( No repetition ) P4--- Material description ( Repeat )
# N4--- Batch number ( Add up ) O4[-1]--- Product model ( Serial number )
# max_row-3--- The best way
all_wb = openpyxl.load_workbook(' Daily picking list .xlsx')
all_ws = all_wb.worksheets
res = {}
for w in all_ws:
for row in range(4, w.max_row-3):
if w['K'+str(row)].value not in res.keys():
res.update({w['K'+str(row)].value:{' Material description ':w['P'+str(row)].value,
' Batch number ':int(w['N'+str(row)].value)}})
else:
res[w['K'+str(row)].value][' Batch number '] += int(w['N'+str(row)].value)
li = list(res.items())
print(li)
li.sort(key= lambda x:x[1][' Batch number '], reverse=True)
for x in li:
ws.append([x[0], x[1][' Material description '], x[1][' Batch number ']])
wb.save('demo.xlsx')

版权声明:本文为[Begin to change]所创,转载请带上原文链接,感谢。 https://pythonmana.com/2022/134/202205141443436508.html