There are many friends who are interested in Python handling excel, although I am not an expert in this field. Python is completely unfamiliar if you use C# openxl to do things in minutes or Npoi again.
Since learning is the goal to accomplish this, it doesn't have to be complicated (neither can I do it), let's assume we're working on a sales system.If not professional, spray gently.
One Excel product order, one customer order, one order, with simple fields.
Build from scratch
Now that you're using Python, of course, you'll look at the tool library:
There are openpyxl, xlwt, xlrd, Pandas (this is really possible), xlsxwriter
First analyze and compare waves
Advantages: Read and write Excel quickly and efficiently, control most Excel elements, and write with formulas
Disadvantages: very unfriendly to beginners, super many attributes, and control by xml format; style can only be defined once again, modify the style must first copy and reconstruct, workload geometry level increases
Methods specific to writing data
Advantage: Quick, can control most styles, formulas can only be processed as strings, write data as xy positioning or cell name as primary
Disadvantage: No poison, no poison, no poison, so don't use the same excel document for processing (I suffered this loss, good hundreds of thousands of records were cleaned out), don't know how to append to the original document and add a new sheet
Readable and Writable excel Library
Advantage: Essentially for reading excel data, very precise, clear data type
Disadvantage: Write apparently slow, 100,000 records write me for 10 minutes (PC, supercomputer might be fast), and can't operate styles?I didn't find a way to do that anyway
To summarize the preliminary study, tentatively read the file by xlrd, write a new document by xlwt, and start when you are ready
2. Preparing before starting
Install the corresponding library, how to install it is not described here, you can chat privately.
Environment: Python 3.8, excel xlsx
pip install xlwt \ pip install xlrd \ pip install xlsxwriter
3. Data preparation
Next, we need to simulate customer information. To facilitate administrative processing, we random, just make simple fields: ClientID, ClientName, Discount, Members, Industry
Product information: ProID, ProName, Catalog, PurchasePrice, UnitPrice
Regardless of stock in stock, assume that there are diligent couriers who are constantly replenishing
# Random generation of customer information import random import string import CreateName import xlsxwriter # key initial position ckbase = 10000 def CreateClients(): clients = for i in range(100000): clients.append( CreateClient()) return clients # Generate a single customer def CreateClient(): # ClientID,ClientName,Discount,Members,Industry global ckbase ckbase += 1 cid = 'ck' + str(ckbase) cname = CreateName.create_name2() dis = discountRandom(1, 10) mem = random.randint(1, 5) indu = IndustryCreate() client = [cid,cname,dis,mem, indu] return client def p(text): print(text) # discount random function def discountRandom(_min, _max): r1 = random.randint(0, 10) result = 0 if(r1 > 6): result = random.randint(_min, _max) return result def IndustryCreate(): xlist = [ 'Computer','Finance','education','Travel?','Social contact','Online retailers'] n = random.randint(0, len(xlist) - 1) rs = xlist[n] return rs def write2excel(path,clients): # todo creates excel file xl = xlsxwriter.Workbook(path) # todo add sheet sheet = xl.add_worksheet('Client') #Header row ClientID, ClientName, Discount, Members, Industry sheet.write_string(0,0,'ClientID') sheet.write_string(0,1,'ClientName') sheet.write_string(0,2,'Discount') sheet.write_string(0,3,'Members') sheet.write_string(0,4,'Industry') rowIndex = 0 colIndex =0 for row in clients: colIndex = 0 for col in row : sheet.write_string(rowIndex+1,colIndex,str(col)) colIndex += 1 rowIndex += 1 # todo close file xl.close() return "-- Done --" if __name__ == '__main__': cs = CreateClients() p(cs) write2excel(r'client1.xlsx',cs)
Some points to explain
- CreateName is a stand-alone py file that requires a partner to keep the mailbox
- id is generated sequentially, discount, member are generated randomly, industry tentative several
- xlsxwriter.Workbook(path) is the path you save, modify it yourself
Next comes the product, using a similar script to generate, and I don't paste code
IV. Generating Transaction Data
The first two excel s correspond to products and customers, and we simulate transactions next.
- Any customer can do anything (we'll limit the types of purchases in the next stage)
- There is no inventory limit on the quantity purchased (next stage)
- UnitPrice is the unit price for sale. We randomly add discounts to overlay customer discounts, that is, 1 - 0.08 - 0.06. At a discount on Purchase, we get a profit Profit (discount, next stage)
Transfer to Part2