Python Read and Write excel Part1


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


1. Tools

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):

# 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
    rowIndex = 0
    colIndex =0
    for row in clients:
        colIndex = 0
        for col in row :
            colIndex += 1

        rowIndex += 1
    # todo close file
    return "--  Done  --"

if __name__ == '__main__':
    cs = CreateClients()


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

Keywords: Excel Python pip xml

Added by ephmynus on Tue, 16 Jun 2020 03:21:27 +0300