use python To automatically generate excel Data files .python Handle excel The file is mainly a third-party module library xlrd、xlwt、xluntils and pyExcelerator, besides ,python Handle excel You can also use win32com and openpyxl modular .
Method 1 Xiao Luo asked me how to start from excel Read data from , Then I did a hundred , Make a record excel Data graph ( Xiao Luo said that the data should be kept confidential to customers , I wrote a few lines of data at random )：
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Read excel data
# Xiao Luo's demand , Take the data below the second row , Then take the front of each line 13 Columns of data
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 thirteen columns excel The write operation of is recorded when it is used later
Method 2 Use xlrd Read the file , Use xlwt Generate Excel file （ Can be controlled Excel The format of cells in ）. But with xlrd Read excel It can't be operated on ; and xlwt Generate excel Files cannot be added to existing files excel Modified on the basis of the document , If you need to modify the file, use xluntils modular .pyExcelerator Module and xlwt similar , It can also be used to generate excel file . 1.[ Code ]test_xlrd.py
#coding=utf-8
#######################################################
#filename:test_xlrd.py
#author:defias
#date:xxxx-xx-xx
#function： read excel The data in the file
#######################################################
import xlrd
# Open one workbook
workbook = xlrd.open_workbook('E:\\Code\\Python\\testdata.xls')
# Grab all sheet The name of the page
worksheets = workbook.sheet_names()
print('worksheets is %s' %worksheets)
# Locate the sheet1
worksheet1 = workbook.sheet_by_name(u'Sheet1')
"""
# By index order
worksheet1 = workbook.sheets()[0]
# or
worksheet1 = workbook.sheet_by_index(0)
"""
"""
# Traverse all of sheet object
for worksheet_name in worksheets:
worksheet = workbook.sheet_by_name(worksheet_name)
"""
# Traverse sheet1 All the lines in row
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))
# Traverse sheet1 All columns in 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))
# Traverse sheet1 All cells in cell
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 the 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： newly build excel File and write data
#######################################################
import xlwt
# establish workbook and sheet object
workbook = xlwt.Workbook() # Be careful Workbook The beginning of W Use capital letters
sheet1 = workbook.add_sheet('sheet1',cell_overwrite_ok=True)
sheet2 = workbook.add_sheet('sheet2',cell_overwrite_ok=True)
# towards sheet Write data to page
sheet1.write(0,0,'this should overwrite1')
sheet1.write(0,1,'aaaaaaaaaaaa')
sheet2.write(0,0,'this should overwrite2')
sheet2.write(1,2,'bbbbbbbbbbbbb')
"""
#----------- Use styles -----------------------------------
# Initialize style
style = xlwt.XFStyle()
# Create fonts for styles
font = xlwt.Font()
font.name = 'Times New Roman'
font.bold = True
# Set the font style
style.font = font
# Use styles
sheet.write(0,1,'some bold Times text',style)
"""
# Keep it excel file , If there is a file with the same name, overwrite it directly
workbook.save('E:\\Code\\Python\\test2.xls')
print ' establish excel File complete ！'3. [ Code ]test_xlutils.py
#coding=utf-8
#######################################################
#filename:test_xlutils.py
#author:defias
#date:xxxx-xx-xx
#function： towards excel Write data to file
#######################################################
import xlrd
import xlutils.copy
# Open one workbook
rb = xlrd.open_workbook('E:\\Code\\Python\\test1.xls')
wb = xlutils.copy.copy(rb)
# obtain sheet object , adopt sheet_by_index() Acquired sheet Objects have no write() Method
ws = wb.get_sheet(0)
# Write data
ws.write(1, 1, 'changed!')
# add to sheet page
wb.add_sheet('sheetnnn2',cell_overwrite_ok=True)
# Use the same name overlay to modify excel The purpose of the document , Note that the content that has not been modified 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 excel The data in the file
#######################################################
import pyExcelerator
#parse_xls Return a list , Each item is a sheet Pages of data .
# Each term is a binary ( Table name , Cell data ). The cell data is a dictionary , The key value is the index of the cell (i,j). 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： newly build excel File and write data
#######################################################
import pyExcelerator
# establish workbook and sheet object
wb = pyExcelerator.Workbook()
ws = wb.add_sheet(u' first page ')
# Set the style
myfont = pyExcelerator.Font()
myfont.name = u'Times New Roman'
myfont.bold = True
mystyle = pyExcelerator.XFStyle()
mystyle.font = myfont
# Write data , Use styles
ws.write(0,0,u'ni hao Paso ！',mystyle)
# Keep it excel file , If there is a file with the same name, overwrite it directly
wb.save('E:\\Code\\Python\\mini.xls')
print ' establish excel File complete ！'
