一、写入 Excel 文件
import xlwt
def set_style(name,height,bold=False):
""" 设置表格样式 """
style = xlwt.XFStyle()
font = xlwt.Font()
font.name = name
font.bold = bold
font.color_index = 4
font.height = height
style.font = font
return style
def write_excel(file_name):
""" 写入 Excel """
f = xlwt.Workbook()
sheet1 = f.add_sheet('学生',cell_overwrite_ok=True)
row0 = ['姓名','年龄','出生日期','爱好'] # 第一行的内容
colum0 = ['黄帝','岐伯','雷公'] # 第一列的内容
# 写第一行
for i in range(len(row0)):
sheet1.write(0,i,row0[i],set_style('Times New Roman',220,True))
# 写第一列
for i in range(len(colum0)):
sheet1.write(i+1,0,colum0[i],set_style('Times New Roman',220,True))
# 写第 2-3行,第 2-3列
rc2 = [[100,'公元前'],[120,'公元前']]
for i in range(len(rc2)):
for j in range(len(rc2[i])):
sheet1.write(i+1,j+1,rc2[i][j],set_style('Times New Roman',220,True))
sheet1.write(1,3,'2018/01/01')
sheet1.write_merge(3,3,1,3,'未知') # 合并行单元格
sheet1.write_merge(1,2,3,3,'中医') # 合并列单元格
# 保存
f.save(file_name)
if __name__ == '__main__':
file_name = '中医.xls'
write_excel(file_name)
执行结果:
二、读取 Excel 文件
import xlrd
def read_excel(file_name):
""" 读取 Excel """
newwb = xlrd.open_workbook(file_name,formatting_info=True) # 带格式导入
table = newwb.sheets()[0] # 第一张表
rows = table.nrows # 行数
cols = table.ncols # 列数
for i in range(rows):
for j in table.row_values(i):
print(j,end='\t')
print()
if __name__ == '__main__':
file_name = '中医.xls'
read_excel(file_name)
执行结果:
三,复制并修改Excel文件,保持原文件样式
import xlrd
import xlutils.copy
def setOutCell(outSheet, col, row, value):
""" Change cell value without changing formatting. """
def _getOutCell(outSheet, colIndex, rowIndex):
""" HACK: Extract the internal xlwt cell representation. """
row = outSheet._Worksheet__rows.get(rowIndex)
if not row: return None
cell = row._Row__cells.get(colIndex)
return cell
# HACK to retain cell style.
previousCell = _getOutCell(outSheet, col, row)
# END HACK, PART I
outSheet.write(row, col, value)
# HACK, PART II
if previousCell:
newCell = _getOutCell(outSheet, col, row)
if newCell:
newCell.xf_idx = previousCell.xf_idx
def update_excel(file_name):
zs1, zs2 = "修改1","修改2"
newwb = xlrd.open_workbook(file_name,formatting_info=True) # 带格式导入
outwb = xlutils.copy.copy(newwb) # 建立一个副本来写
outSheet = outwb.get_sheet(0)
#setOutCell(outSheet, 5, 5, 'Test')
#outwb.save('output.xls')
table = newwb.sheets()[0] # 第一张表
age = table.row_values(1)[1] # 获取第2行第1列的值
age=int(age)+2 # 把年龄修改为 原来的年龄 +2
setOutCell(outSheet, 1, 1, age) # 再写入到新建的副本
setOutCell(outSheet, 2, 1, "公元前2599年") # 修改出生日期
files = 'updata.xls'
outwb.save(files)
if __name__ == '__main__':
file_name = '中医.xls'
update_excel(file_name)
执行结果: