Python读写Excel

读

#导入库
import xlrd

#打开工作表
file = '7月表.xlsx')
table = xlrd.open_workbook()

#打开工作簿
sheet_n = xlsx.sheet_by_name("7月表")
sheet_i = xlsx.sheet_by_index(0)

#读取单元格
print(table.cell_value(2, 2))
print(table.cell(2, 2).value)
print(table.row(2)[2].value)

 

写

#导入库
import xlwt

#创建工作表
new_book = xlwt.Workbook()

#创建工作簿
new_sheet = new_book.add_sheet("sheet_name") #sheet_name:工作簿名称

#写入单元格
new_sheet.write(0,0,'new_cell')

#保存
file = r'D:\new_file\ceshi.xls'     #ceshi:工作表名称
new_book.save(file)

 

 

复制工作表（无格式）

#导入库
import xlrd
from xlutils.copy import  copy

#打开工作表
old_file = '7月表.xlsx'
old_book = xlrd.open_workbook(old_file)

#复制工作表
new_book = copy(old_book)

#保存工作表
file = r'D:\new_file\ceshi.xls'     #ceshi:工作表名称
new_book.save(file)

 

遍历工作簿

import xlrd,xlwt
ole_tabel = xlrd.open_workbook('d:/7月表.xlsx')
old_sheet = xlsx.sheet_by_index(0)
new_workbook = xlwt.Workbook()
new_sheet = new_workbook.add_sheet('new_test')
#遍历工作簿中的单元格
for i in range(0,old_sheet.nrows):
    for j in range(0,old_sheet.ncols):       
        worksheet.write(i, j, old_sheet.cell_value(i, j))
new_workbook.save('d:/test.xls')

 

写入Excel带格式

#样式名称
style = xlwt.XFStyle()

#字体
font = xlwt.Font()
font.name  = '微软雅黑'
font.bold = True
font.height = 360
style.font = font

#边框
borders = xlwt.Borders()
borders.top = xlwt.Borders.THIN
borders.bottom= xlwt.Borders.THIN
borders.left= xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
style.borders = borders

#对齐方式
aligment = xlwt.Alignment()
aligment.horz = xlwt.Alignment.HORZ_LEFT
aligment.vert = xlwt.Alignment.VERT_TOP
style.alignment = aligment

 

打开工作表，带格式复制，带格式填入舒服

#导入库
import xlrd,xlwt
from xlutils.copy import  copy
#打开旧工作簿
old_file = r'D:/12 用Python自动办公，做职场高手（完结）/01.文件/【12.20更新课程代码】用Python自动办公做职场高手/CourseCode/Chapter1/S1-1-2/LessonCode/日统计.xls'
old_book = xlrd.open_workbook(old_file,formatting_info= True)
old_sheet = old_book.sheet_by_index(0)

#复制工作簿
new_book = copy(old_book)
new_sheet = new_book.get_sheet(0)



#输入样式
style = xlwt.XFStyle()

font = xlwt.Font()
font.name  = '微软雅黑'
font.bold = True
font.height = 360
style.font = font

borders = xlwt.Borders()
borders.top = xlwt.Borders.THIN
borders.bottom= xlwt.Borders.THIN
borders.left= xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
style.borders = borders

aligment = xlwt.Alignment()
aligment.horz = xlwt.Alignment.HORZ_LEFT
aligment.vert = xlwt.Alignment.VERT_TOP
style.alignment = aligment


style_red = xlwt.XFStyle()

font_red = xlwt.Font()
font_red .name  = '微软雅黑'
font_red.colour_index = 2
font_red .bold = True
font_red .height = 360
style_red .font = font_red

borders_red  = xlwt.Borders()
borders_red .top = xlwt.Borders.THIN
borders_red .bottom= xlwt.Borders.THIN
borders_red .left= xlwt.Borders.THIN
borders_red .right = xlwt.Borders.THIN
style_red .borders = borders_red

aligment_red  = xlwt.Alignment()
aligment_red .horz = xlwt.Alignment.HORZ_LEFT
aligment_red .vert = xlwt.Alignment.VERT_TOP
style_red .alignment = aligment_red

style_18 = xlwt.XFStyle()

font_18 = xlwt.Font()
font_18 .name  = '隶书'
font_18 .bold = True
font_18 .height = 360
style_18 .font = font_18

borders_18  = xlwt.Borders()
borders_18 .top = xlwt.Borders.THIN
borders_18 .bottom= xlwt.Borders.THIN
borders_18 .left= xlwt.Borders.THIN
borders_18 .right = xlwt.Borders.THIN
style_18 .borders = borders_18

aligment_18  = xlwt.Alignment()
aligment_18 .horz = xlwt.Alignment.HORZ_LEFT
aligment_18 .vert = xlwt.Alignment.VERT_TOP
style_18 .alignment = aligment_18

style_20 = xlwt.XFStyle()

font_20 = xlwt.Font()
font_20 .name  = '隶书'
font_20 .bold = True
font_20 .height = 400
style_20 .font = font_20

borders_20  = xlwt.Borders()
borders_20 .top = xlwt.Borders.THIN
borders_20 .bottom= xlwt.Borders.THIN
borders_20 .left= xlwt.Borders.THIN
borders_20 .right = xlwt.Borders.THIN
style_20 .borders = borders_20

aligment_20  = xlwt.Alignment()
aligment_20 .horz = xlwt.Alignment.HORZ_LEFT
aligment_20 .vert = xlwt.Alignment.VERT_TOP
style_20 .alignment = aligment_20

stylex = lambda x: style_red if x > 10 else style


#输入数字
zs_n = int(input("请输入张三："))
ls_n = int(input("请输入ls："))
ws_n = int(input("请输入ws："))
zl_n = int(input("请输入zl："))

#填入数据
new_sheet.write(0,0,old_sheet.cell_value(0,0),style_20)
new_sheet.write(1,1,old_sheet.cell_value(1,1),style_18)
new_sheet.write(1, 0, old_sheet.cell_value(1, 0), style_18)
"""new_sheet.write(1, 1, old_sheet.cell_value(1, 1), style_18)
new_sheet.write(2, 0, old_sheet.cell_value(2, 0), style_18)
new_sheet.write(3, 0, old_sheet.cell_value(3, 0), style_18)
new_sheet.write(4, 0, old_sheet.cell_value(4, 0), style_18)
new_sheet.write(5, 0, old_sheet.cell_value(5, 0), style_18)"""
for i in range(1,6):
    new_sheet.write(i,0,old_sheet.cell_value(i,0),style_18)


new_sheet.write(2,1,zs_n,stylex(zs_n))
new_sheet.write(3,1,ls_n,stylex(ls_n))
new_sheet.write(4,1,ws_n,stylex(ws_n))
new_sheet.write(5,1,zl_n,stylex(zl_n))


new_file = r'C:/Users/Administrator/Desktop/新建文件夹/1_2_p2.xls'
new_book.save(new_file)

 

注意：

•  保存的文件格式为 xls，而不是xlsx,否则容易出错。

• old_book = xlrd.open_workbook(old_file,formatting_info= True。如果为False,保存的格式则出错。比如无合并单元格。