Home>

Use python to automatically generate excel data files.Python's excel files are mainly third-party module libraries xlrd, xlwt, xluntils, and pyexcelerator. In addition,python processing excel can also use win32com and openpyxl modules

Use python to automatically generate excel data files.Python's excel files are mainly third-party module libraries xlrd, xlwt, xluntils, and pyexcelerator. In addition,You can also use win32com and openpyxl modules for python processing excel.

method one:

Ronaldinho asked me how to read data from excel,Then I doubled up,Take notes

excel data map (Ronaldinho said the data should be kept confidential to customers,I wrote a few lines of data by hand):

Python read excel file code:

#!/usr/bin/env python
#-*-coding:utf-8-*-
#Read excel data
#Ronaldinho's needs,Take the data below the second line,Then take the data of the first 13 columns of each row
import xlrd
data=xlrd.open_workbook ("test.xls") #open xls file
table=data.sheets () [0] #open the first table
nrows=table.nrows #Get the number of rows in the table
for i in range (nrows):#print line by line
if i == 0:#skip the first line
continue
print table.row_values ​​(i) [:13] #Take the first 13 columns

Excel write operations are being recorded when used later

Method Two:

Read the file using xlrd,Use xlwt to generate excel files (you can control the format of cells in excel). But reading excel with xlrd cannot operate it;The excel file generated by xlwt cannot be modified based on the existing excel file.If you need to modify the file, use the xluntils module. The pyexcelerator module is similar to xlwt and can also be used to generate excel files.

1. [Code] test_xlrd.py

#coding=utf-8
#########################################################
#filename:test_xlrd.py
#author:defias
#date:xxxx-xx-xx
#function:Read data in an excel file
#########################################################
import xlrd
#Open a workbook
workbook=xlrd.open_workbook ("e:\\ code \\ python \\ testdata.xls")
#Grab the names of all sheet pages
worksheets=workbook.sheet_names ()
print ("worksheets is%s"%worksheets)
#Locate to sheet1
worksheet1=workbook.sheet_by_name (u "sheet1")
"" "
#Get by index order
worksheet1=workbook.sheets () [0]
#or
worksheet1=workbook.sheet_by_index (0)
"" "
"" "
#Iterate through all sheet objects
for worksheet_name in worksheets:
worksheet=workbook.sheet_by_name (worksheet_name)
"" "
#Loop through all the rows in sheet1
num_rows=worksheet1.nrows
for curr_row in range (num_rows):
row=worksheet1.row_values ​​(curr_row)
print ("row%s is%s"%(curr_row, row))
#Iterate through all columns in sheet1 col
num_cols=worksheet1.ncols
for curr_col in range (num_cols):
col=worksheet1.col_values ​​(curr_col)
print ("col%s is%s"%(curr_col, col))
#Iterate through all cells in sheet1
for rown in range (num_rows):
for coln in range (num_cols):
cell=worksheet1.cell_value (rown, coln)
print cell
"" "
#Other writing:
cell=worksheet1.cell (rown, coln) .value
print cell
#or
cell=worksheet1.row (rown) [coln] .value
print cell
#or
cell=worksheet1.col (coln) [rown] .value
print cell
#Get the type of value in the cell,Type 0 empty, 1 string, 2 number, 3 date, 4 boolean, 5 error
cell_type=worksheet1.cell_type (rown, coln)
print cell_type
"" "

2. [Code] test_xlwt.py

#coding=utf-8
#########################################################
#filename:test_xlwt.py
#author:defias
#date:xxxx-xx-xx
#function:Create new excel file and write data
#########################################################
import xlwt
#Create workbook and sheet objects
workbook=xlwt.workbook () #Note that the beginning of the workbook must be capitalized
sheet1=workbook.add_sheet ("sheet1", cell_overwrite_ok=true)
sheet2=workbook.add_sheet ("sheet2", cell_overwrite_ok=true)
#Write data to sheet page
sheet1.write (0,0, "this should overwrite1")
sheet1.write (0,1, "aaaaaaaaaaaa")
sheet2.write (0,0, "this should overwrite2")
sheet2.write (1,2, "bbbbbbbbbbbbbbb")
"" "
#----------- Use style -----------------------------------
#Initialization style
style=xlwt.xfstyle ()
#Create font for style
font=xlwt.font ()
font.name="times new roman"
font.bold=true
#Set style font
style.font=font
#Use style
sheet.write (0,1, "some bold times text", style)
"" "
#Save the excel file and directly overwrite it if there is a file with the same name
workbook.save ("e:\\ code \\ python \\ test2.xls")
print "Creating excel file is complete!"

3. [Code] test_xlutils.py

#coding=utf-8
#########################################################
#filename:test_xlutils.py
#author:defias
#date:xxxx-xx-xx
#function:Write data to an excel file
#########################################################
import xlrd
import xlutils.copy
#Open a workbook
rb=xlrd.open_workbook ("e:\\ code \\ python \\ test1.xls")
wb=xlutils.copy.copy (rb)
#Get the sheet object, the sheet object obtained through sheet_by_index () has no write () method
ws=wb.get_sheet (0)
#data input
ws.write (1, 1, "changed!")
#Add sheet page
wb.add_sheet ("sheetnnn2", cell_overwrite_ok=true)
#Use the same name when saving to achieve the purpose of modifying the excel file,Note that the unmodified content remains the same
wb.save ("e:\\ code \\ python \\ test1.xls")

4. [Code] test_pyexcelerator_read.py

#coding=utf-8
#########################################################
#filename:test_pyexcelerator_read.py
#author:defias
#date:xxxx-xx-xx
#function:Read data in an excel file
#########################################################
import pyexcelerator
#parse_xls returns a list,Each item is data of a sheet page.
#Each item is a two-tuple (table name, cell data). Where the cell data is a dictionary,The key value is the index (i, j) of the cell. If a cell has no data,Then there is no such value
sheets=pyexcelerator.parse_xls ("e:\\ code \\ python \\ testdata.xls")
print sheets

5. [Code] test_pyexcelerator.py

#coding=utf-8
#########################################################
#filename:test_pyexcelerator.py
#author:defias
#date:xxxx-xx-xx
#function:Create new excel file and write data
#########################################################
import pyexcelerator
#Create workbook and sheet objects
wb=pyexcelerator.workbook ()
ws=wb.add_sheet (u "First page")
#Set style
myfont=pyexcelerator.font ()
myfont.name=u "times new roman"
myfont.bold=true
mystyle=pyexcelerator.xfstyle ()
mystyle.font=myfont
#data input,Use style
ws.write (0,0, u "ni hao Paso!", mystyle)
#Save the excel file and directly overwrite it if there is a file with the same name
wb.save ("e:\\ code \\ python \\ mini.xls")
print "Creating excel file is complete!"
  • Previous JavaScript buffer motion implementation method (2 examples)
  • Next Notification notification usage examples (vibration, light, sound) in Android