EXCEL 写入数据保持原样式
import xlwt
import xlrd
import xlutils
import xlutils.copy
class ExcelHandle():
def __init__(self, filename):
self.filename = filename
self.test_data = xlrd.open_workbook(self.filename,formatting_info=True) # 保留原格式
self.sheet = self.test_data.sheet_by_index(0)
def writeExcel(self,row,col,value):
self.row = row
self.col = col
self.args = value
style = xlwt.XFStyle()
oldsheet = xlrd.open_workbook(self.filename,formatting_info=True)
newsheet = xlutils.copy.copy(oldsheet)
newwrite = newsheet.get_sheet(0)
newwrite.write(self.row, self.col, str(value), style)
newsheet.save(self.filename)
def readExcel(self, row,col):
comlist = str(self.sheet.cell_value(row, col))
return comlist
if __name__ == '__main__':
t = ExcelHandle('bookname.xlsx')
for i in range(2,15):
for j in range(1, 10):
X = t.readExcel(i,1)
print(X)
print("______________")
t.writeExcel(i,j,"sssss")
#coding=utf-8
import xlwt
import xlrd
import xlutils.copy
#################################################################################
# Create a new sheet1
#workbook = xlwt.Workbook()
#sheet1 = workbook.add_sheet('sheet1',cell_overwrite_ok=True)
# Write something into the sheet1
#sheet1.write(0,0,'connected IP')
#sheet1.write(0,1,'Addr IPV4 IP(s)')
# save excel file
#workbook.save('C:\Users\32065\Desktop\Net\simulation\excel\test.xls')
#print 'create excel file successfully!'
################################################################################
rb = xlrd.open_workbook('C:\Users\32065\Desktop\Net\simulation\excel\test.xls')
wb = xlutils.copy.copy(rb)
#获取sheet对象,通过sheet_by_index()获取的sheet对象没有write()方法
ws = wb.get_sheet(0)
#写入数据
ws.write(0, 1, 'changed!')
ws.write(1, 0, '10-changed!')
ws.write(2, 0, '20-changed!')
#添加sheet页
# wb.add_sheet('sheetnnn2',cell_overwrite_ok=True)
#利用保存时同名覆盖达到修改excel文件的目的,注意未被修改的内容保持不变
wb.save('C:\Users\32065\Desktop\Net\simulation\excel\test.xls')
##############################################################################
# read the excel file
data = xlrd.open_workbook('C:\Users\32065\Desktop\Net\simulation\excel\test.xls')
table = data.sheets()[0] # open the first sheet
nrows = table.nrows # not-null rows
print nrows
for i in range(nrows): # print by rows
if i == 0: # 跳过第一行
continue
print table.row_values(i)[0:2]