Python Read and Write excel Part1

background

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.

target

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

1,Openpyxl

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

2,xlsxwriter

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

3,xlrd,xlwt

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

Generate FilesClientCreator.py

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

Opening-------

Transfer to Part2

Keywords: Excel Python pip xml

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

Programming VIP

Python Read and Write excel Part1

Popular Keywords