Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to manipulate excel tables with Python code". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to manipulate excel tables with Python code.

Xlrd module

Xlrd is a third-party module for reading excle tables in python. Many enterprises use tables to manage data before using computer management, so importing tables is still very common!

Install xlrd

Pip install xlrd

Exlce structure analysis

An excle table contains multiple sheet

A sheet contains multiple rows and columns

Each cell has a unique row number and column number

Common function

Import xlrd

# read files

Work_book = xlrd.open_workbook ("/ Users/jerry/Desktop/ Confidential data .xlsx")

# Select a table

# get all table names

Print (work_book.sheet_names ())

# Select the second index to start with 0

Sheet = work_book.sheet_by_index (1)

# form name

Print (sheet.name)

# number of Lin

Print (sheet.nrows)

# number of columns

Print (sheet.ncols)

# read row data in batch

# extract all the contents of row 6, including the data type

Print (sheet.row (6))

# take out the content of row 6, including the data type, and start with column 3.

Print (sheet.row_slice (6) startstarted colxboxes 3)

# take out the content of row 6, including the data type, and start with column 3.

Print (sheet.row_slice (6) startling colxation 4 ending colxation 5)

# get a numeric representation of all data types in the row

# print (sheet.row_types (6))

# print (sheet.row_values (6))

# handling of cells

Value of print (sheet.cell (0c0) .value) #

Print (sheet.cell (0P0) .ctype) # take the type

Print (sheet.cell_value (2J0)) # take the value directly

Print (sheet.row (0) [0]) # fetch rows first and then cells

Print (sheet.col (0)) # column 0 all data

Print (sheet.col (0)) # fetch the column first and then the cell

Print (sheet.cell_type (0d0))

# Cell position conversion

Print (xlrd.cellname (2pm 1))

Print (xlrd.cellnameabs (0Pol 2))

Print (xlrd.colname (5))

# time type conversion

# print (sheet.cell (6p5) .value)

# print (xlrd.xldate_as_datetime (sheet.cell (6Jing 5). Value,1))

Case study:

Read a quotation 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 cell is in the first position of the merged area.

For ces insheet.merged_cells:

If (row > = ces [0] and row

= ces [2] and col < ces [3]):

Returnsheet.cell (ces [0], ces [2]) .value # fetches the first data in the merged area

Returnsheet.cell (row,col) .value # retrieves the corresponding data normally

Keys = sheet.row_values (1) # get all column headers

Data = []

For row in range (2):

Dic = {}

For col inrange (sheet.ncols):

K = keys [col] # determine key

Res = get_text (row,col)

Dic [k] = res # determine the value and store it

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 a table

Writing exlce in code is very inefficient, so the module can understand it.

Import xlwt

# create a 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 # bold

Font.italic = True # italic

Font.underline = True # underscore

# create a style object

Style = xlwt.XFStyle ()

Style.font = font

# write title

For k in keys:

Sheet.write (0Magnekeys.index (k), KGI 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:

Import xlrd

Import pymysql

# read files

Work_book = xlrd.open_workbook ("/ xxx/xxx.xlsx")

# Select a table

Sheet = work_book.sheet_by_index (0)

# traversing tabular data

Datas = []

For row in range (1):

Temp_list = []

For col inrange (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')

# obtain operation cursors using the cursor () method

Cursor = db.cursor ()

# SQL insert statement

Sql = "INSERT INTO SHOP (shop_code, shop_name, month) VALUES (% SJM% SJM% s)"

Try:

# execute sql statement

Cursor.executemany (sql, datas)

# submit to the database for execution

Db.commit ()

Except:

# Roll back if an error occurs

Db.rollback ()

# close cursors

Cursor.close ()

# close database connection

Db.close ()

At this point, I believe you have a deeper understanding of "how to manipulate excel tables with Python code". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.