# coding: utf-8
# 读写EXCEL数据的例子
from xlwt import Workbook, Formula
import xlrd
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
sheet1.write(0, 0, 10)
sheet1.write(0, 1, 20)
sheet1.write(1, 0, Formula('A1/B1'))
sheet2 = book.add_sheet('Sheet 2')
row = sheet2.row(0)
row.write(0, Formula('sum(1,2,3)'))
row.write(1, Formula('SuM(1;2;3)'))
row.write(2, Formula("$A$1+$B$1*SUM('ShEEt 1'!$A$1:$b$2)"))
book.save('formula.xlsx') #写入EXCEL
book = xlrd.open_workbook('formula.xlsx') #读取EXCEL
sheet = book.sheets()[0]
nrows = sheet.nrows
ncols = sheet.ncols
for i in range(nrows):
for j in range(ncols):
print(sheet.cell(i, j).value)
for element_index, element in enumerate(output_list):
output_worksheet.write(list_index, element_index, element)
output_worksheet.save(output_file)
#处理多个工作簿
#工作表计数以及每个工作表中的行列计数
import glob,os, sys
input_directory = sys.argv[1]
workbook_counter = 0
for input_file in glob.glob(os.path.join(input_directory, '*,xls*')):
workbook = open_workbook(input_file)
print('Workbook: %s' % os.path.basename(input_file))
print('NUmber of worksheets: %d' % workbook.nsheets)
for worksheet in workbook.sheets():
print('Worksheet name:', worksheet.name, '\tRows:', worksheet.nrows, '\tColumns:', workbook.ncols)
workbook_counter += 1
print('Number of Excel workboos: %d' % (workbook_counter))
#从多个工作簿中连接数据
input_folder = sys.argv[1]
output_file = sys.argv[2]
output_workbook = Workbook()
output_worksheet = output_workbook.add_sheet('All_data_All_workbooks')
data = []
first_worksheet = True
for input_file in glob.glob(os.path.join(input_folder,'*.xls*')):
from xlwt import Workbook, Formula
import xlrd
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
sheet1.write(0,0,10)
sheet1.write(0,1,20)
sheet1.write(1,0,Formula('A1/B1'))
sheet2 = book.add_sheet('Sheet 2')
row = sheet2.row(0)
row.write(0,Formula('sum(1,2,3)'))
row.write(1,Formula('SuM(1;2;3)'))
row.write(2,Formula("$A$1+$B$1*SUM('ShEEt 1'!$A$1:$b$2)"))
book.save('formula.xls')
book = xlrd.open_workbook('formula.xls')
sheet = book.sheets()[0]
nrows=sheet.nrows
ncols=sheet.ncols
for i in range(nrows):
for j in range(ncols):
print (sheet.cell(i,j).value)