Processing excel form of python

Posted by lonewolf217 on Thu, 31 Oct 2019 08:34:26 +0100

xlrd

xlrd is a third-party module used to read excle tables in python. Many enterprises use tables to manage data before using computer management, so importing tables is very common!

Install xlrd

pip install xlrd

Analysis of excel structure

An excle table contains multiple sheet s

A sheet contains multiple rows and columns

Each cell has a unique row and column number

Common functions

import xlrd

# read file
work_book = xlrd.open_workbook("/Users/jerry/Desktop/Company confidential data.xlsx")
# Select a table

# Get all table names
print(work_book.sheet_names())

# Select index 2 from 0
sheet = work_book.sheet_by_index(1)

# Table name
print(sheet.name)

# Row number
print(sheet.nrows)
# Column number
print(sheet.ncols)

#Bulk read row data
# Take out all contents of line 6 including data type
print(sheet.row(6))
# Take out the content of Row 6, including the data type, starting from column 3
print(sheet.row_slice(6,start_colx=3))

# Take out the content of Row 6, including the data type, starting from column 3
print(sheet.row_slice(6,start_colx=4,end_colx=5))

# Get a numeric representation of all data types in this row
# print(sheet.row_types(6))
# print(sheet.row_values(6))


# Cell processing
print(sheet.cell(0,0).value) # Value
print(sheet.cell(0,0).ctype) # Fetch type
print(sheet.cell_value(2,0)) # Direct value

print(sheet.row(0)[0]) # First row then cell
print(sheet.col(0)) # All data in column 0
print(sheet.col(0)) # First column then cell

print(sheet.cell_type(0,0))

# Cell position conversion
print(xlrd.cellname(2,1))
print(xlrd.cellnameabs(0,2))
print(xlrd.colname(5))


# Time type conversion
# print(sheet.cell(6,5).value)
# print(xlrd.xldate_as_datetime(sheet.cell(6,5).value,1))

Case study:

Read a quote whose second sheet contains merged cells

File address: https://share.weiyun.com/5GaLY2m

import xlrd
sheet = xlrd.open_workbook("Quotation.xlsx").sheet_by_index(1)
def get_text(row,col):
    # Determine whether the coordinate is the merged cell    The data of the merged cells is in the first position of the merged range 
    for ces in sheet.merged_cells:
        if (row >= ces[0] and row < ces[1]) and (col >= ces[2] and col < ces[3]):
            return sheet.cell(ces[0],ces[2]).value # Fetch the first data of the merge area
    return sheet.cell(row,col).value #Take out corresponding data normally

keys = sheet.row_values(1) # Get all column headers
data = []
for row in range(2,sheet.nrows):
    dic = {}
    for col in range(sheet.ncols):
        k = keys[col] #Determine key
        res = get_text(row,col)
        dic[k] = res # Determine and store values
    data.append(dic)
print(data)
# Serialize to json
import json
json.dump(data,open("test.json","wt"),ensure_ascii=False)

xlwt module

Is a third-party module in python for writing excle data to tables

Using code to write exlce is very inefficient, so this module can understand.

import xlwt
# Create Workbook
work = xlwt.Workbook()
# Create a table
sheet = work.add_sheet("Employee information data")

#Create a font object
font = xlwt.Font()
font.name = "Times New Roman" # Font name
font.bold = True # Thickening
font.italic = True # Italics
font.underline = True # Underline

#Create a style object
style = xlwt.XFStyle()
style.font = font

# Write title
for k in keys:
    sheet.write(0,keys.index(k),k,style)
# Write data 
for i in infos: 
　　for k in keys:
　　　　sheet.write(1 + infos.index(i),keys.index(k),label = i[k])
# Save to file 
work.save("test.xls")

Interview questions:

# read file
work_book = xlrd.open_workbook("/xxx/xxx.xlsx")
# Select a table
sheet = work_book.sheet_by_index(0)

# Traverse table data
datas = []
for row in range(1,sheet.nrows):
    temp_list =[]
    for col in range(sheet.ncols):
        value = sheet.cell_value(row,col)
        temp_list.append(value)
    datas.append(temp_list)

# Open database connection
db = pymysql.connect(host='localhost', port=3306,
                     user='username', passwd='password', db='database_name', charset='utf8')

# Use cursor()Method get operation cursor
cursor = db.cursor()

# SQL Insert statement
sql = "INSERT INTO SHOP(shop_code, shop_name, month) VALUES (%s,%s,%s)"
try:
    # implement sql Sentence
    cursor.executemany(sql, datas)
    # Commit to database execution
    db.commit()
except :
    # Rollback if an error occurs
    db.rollback()
# Close cursor
cursor.close()
# Close database connection
db.close()

Topics: PHP JSON SQL Database Python

Programmer Think