教改坑爹啊!!!
当年天真的以为所谓以器官系统为主线只是为了招生宣传而吹的牛,结果是真的啊!!!我们还成了第一届教改的小白鼠啊!!!我勒个去…
没有教材就算了,怎么课表还每周都不一样呢?!淦哦,这也太反人类了。bks百医(x
为了快速排版课表,把公选课灵活的批量写入,俺又把Python拾起来了!
(其实暑假写了几个easygui相关的程序没整理,先咕着x
基础读写
import xlrd
import xlwt
#ExcelRead ExcelWrite
from xlutils.copy import copy
#虽然不知道为啥但这么写就对了,自己写xlutils.copy报错
cla = xlrd.open_workbook("课表.xlsx")
#打开表格读入
print (cla.nsheets)
print (cla.sheet_names())
#据说不能直接修改,tmp存一下
tmp = copy(cla)
s = tmp.get_sheet(0)
#奇怪的读取
for i in range(0, 6):
for j in range(0, 2):
s.write(13 + j * 12, 2 + i * 5, "聆听与歌唱(302)")
for i in range(0, 6):
s.write(13, 4 + i * 5, "ps(816)")
for i in range(0, 6):
s.write(37, 4 + i * 5, "py(812)")
for i in range(0, 6):
if 4 + i * 5 < 16:
s.write(25, 4 + i * 5, "ps(816)")
else:
s.write(25, 4 + i * 5, "py(812)")
for i in range(0, 3):
s.write(13, 5 + i * 5, "英语(3教)")
s.write(37, 15, "英语(3教)")
s.write(25, 15, "物理实验(402/414)")
#批量写入
#s.write(行, 列, "内容")
tmp.save("NewOne.xls")
#保存
几个点注意一下(咕咕咕):
from … import …结构,百度了还有点晕,不太明白
xlrd纯的读入,不能修改的
修改要用xlwt,而且要把表格get出来
xlwt只能存xls,要是存xlsx会报错
高端排版
import xlrd
import xlwt
import xlutils
from xlutils.copy import copy
####预处理####
cla = xlrd.open_workbook("NewOne.xls")
Origin = xlrd.open_workbook("课表.xlsx")
tmp = copy(cla)
s = tmp.get_sheet(0)
sheet = cla.sheet_by_name('19级临床2班')
Merge = Origin.sheet_by_name('19级临床2班').merged_cells
#print(Merge)
####列宽####
for i in range(2, 32):
s.col(i).width = 3000
s.col(0).width = 1500
s.col(1).width = 1550
####行高####
tall_style = xlwt.easyxf("font: height 720")
tall_style_title = xlwt.easyxf("font: height 360")
tall_style_mayuan = xlwt.easyxf("font: height 1440")
tall_style_renxuan = xlwt.easyxf("font: height 2160")
delta = 12
for j in range(0, 4):
for i in range(5 + delta * j, 13 + delta * j):
s.row(i).set_style(tall_style)
for i in range(0, 2):
s.row(i).set_style(tall_style_title)
for j in range(0, 4):
for i in range(2 + delta * j, 5 + delta * j):
s.row(i).set_style(tall_style_title)
'''
for i in range(0, 4):
s.row(12 + i * delta).set_style(tall_style_mayuan)
'''
for i in range(0, 3):
s.row(13 + i * delta).set_style(tall_style_renxuan)
####合并单元格并设置字体格式####
'''
for i in range(0, 4):
for j in range(0, 6):
cell_value = sheet.cell_value(2 + i * delta, 2 + j * 5)
s.write_merge(2 + i * delta, 2 + i * delta, 2 + j * 5, 6 + j * 5, cell_value)
for (row_up, row_down, col_left, col_right) in Merge:
cell_value = Origin.sheet_by_name('19级临床2班').cell_value(row_up, col_left)
s.write_merge(row_up, row_down - 1, col_left, col_right - 1, cell_value)
#print(cell_value)
##这是单纯的合并操作##
'''
font = xlwt.Font()
font.name = "黑体"
font.height = 11 * 20
font.bold = True
alignment = xlwt.Alignment()
alignment.horz = 0x02
alignment.vert = 0x01
alignment.wrap = True
#'''
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.left_colour = 0
borders.right_colour = 0
borders.top_colour = 0
borders.bottom_colour = 0
#'''
style = xlwt.XFStyle()
style.font = font
style.alignment = alignment
style.borders = borders
for (row_up, row_down, col_left, col_right) in Merge:
cell_value = Origin.sheet_by_name('19级临床2班').cell_value(row_up, col_left)
#print(cell_value)
s.write_merge(row_up, row_down - 1, col_left, col_right - 1, cell_value, style)
for i in range(0, 3):
for j in range(0, 32):
cell_value = sheet.cell_value(13 + i * delta, j)
s.write(13 + i * delta, j, cell_value, style)
for i in range(0, 4):
for j in range(5, 14):
cell_value = sheet.cell_value(j + i * delta, 1)
s.write(j + i * delta, 1, cell_value, style)
####一些(赫鲁晓夫的)小修小补####
s.write_merge(9, 12, 3, 3, "实验导论", style)
s.write_merge(9, 12, 4, 4, "实验导论", style)
for i in range(0, 3):
s.write(12 + i * delta, 1, '8', style)
for j in range(0, 6):
if i != 0 or j != 5:
s.write_merge(11 + i * delta, 12 + i * delta, 2 + j * 5, 2 + j * 5, "马原(408)(上至第9节课)", style)
s.write(17, 22, "基础综合(702)", style)
##添加了实验导论课,并修改了马原的课程描述##
font_day = xlwt.Font()
font_day.name = "楷体"
font_day.height = 10 * 20
font_day.bold = True
style_day = xlwt.XFStyle()
style_day.font = font_day
style_day.alignment = alignment
style_day.borders = borders
for i in range(0, 4):
for j in range(2, 32):
cell_value = sheet.cell_value(3 + i * delta, j)
s.write(3 + i * delta, j, cell_value, style_day)
cell_value = sheet.cell_value(4 + i * delta, j)
s.write(4 + i * delta, j, cell_value, style_day)
##调整了日期与星期的字体,并添加边框##
'''
####添加底色####
pMon = xlwt.Pattern()
pTue = xlwt.Pattern()
pWed = xlwt.Pattern()
pThu = xlwt.Pattern()
pFri = xlwt.Pattern()
pMon.pattern = xlwt.Pattern.SOLID_PATTERN
pTue.pattern = xlwt.Pattern.SOLID_PATTERN
pWed.pattern = xlwt.Pattern.SOLID_PATTERN
pThu.pattern = xlwt.Pattern.SOLID_PATTERN
pFri.pattern = xlwt.Pattern.SOLID_PATTERN
pMon.pattern_fore_colour = 7
pTue.pattern_fore_colour = 5
pWed.pattern_fore_colour = 4
pThu.pattern_fore_colour = 3
pFri.pattern_fore_colour = 2
sMon = xlwt.XFStyle()
sTue = xlwt.XFStyle()
sWed = xlwt.XFStyle()
sThu = xlwt.XFStyle()
sFri = xlwt.XFStyle()
sMon = style
sTue = style
sWed = style
sThu = style
sFri = style
sMon.pattern = pMon
sTue.pattern = pTue
sWed.pattern = pWed
sThu.pattern = pThu
sFri.pattern = pFri
for (row_up, row_down, col_left, col_right) in Merge:
cell_value = Origin.sheet_by_name('19级临床2班').cell_value(row_up, col_left)
if col_left + 1 == col_right:
Weekday = col_left % 5
if Weekday == 2:
s.write_merge(row_up, row_down - 1, col_left, col_right - 1, cell_value, sMon)
elif Weekday == 3:
s.write_merge(row_up, row_down - 1, col_left, col_right - 1, cell_value, sTue)
elif Weekday == 4:
s.write_merge(row_up, row_down - 1, col_left, col_right - 1, cell_value, sWed)
elif Weekday == 0:
s.write_merge(row_up, row_down - 1, col_left, col_right - 1, cell_value, sThu)
elif Weekday == 1:
s.write_merge(row_up, row_down - 1, col_left, col_right - 1, cell_value, sFri)
'''
####保存####
tmp.save("NewTwo.xls")
行高和列宽的处理有些不同,留意一下
style里的颜色代码格式代码啥的很多,懒得整理,请直接去百度“xlwt调节字体”啥的,哦对了,write是可覆盖的
添加底色写废了,咕~
这玩意基本就是照着葫芦画瓢,我也是一边百度一边连改带抄写的,如果大二下学期还是反人类课表而这个模板又看不懂了,请滚去百度
p.s.最后放两个小标程吧,示范的
'''
-----------------------------
Part One
-----------------------------
'''
import xlwt
# 创建 xls 文件对象
wb = xlwt.Workbook()
# 新增一个表单
sh = wb.add_sheet('A Test Sheet')
# 按位置添加数据
sh.write(0, 0, 1234.56)
sh.write(1, 0, 8888)
sh.write(2, 0, 'hello')
sh.write(2, 1, 'world')
# 保存文件
wb.save('example.xls')
'''
-----------------------------------
Part Two
-----------------------------------
'''
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0,'My Cell Contents')
# 设置单元格宽度
worksheet.col(0).width = 33333
workbook.save('cell_width.xls')
# Please note: While I was able to find these constants within the source code, on my system (using LibreOffice,) I was only presented with a solid line, varying from thin to thick; no dotted or dashed lines.
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
borders = xlwt.Borders() # Create Borders
borders.left = xlwt.Borders.DASHED
#DASHED虚线
#NO_LINE没有
#THIN实线
# May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.
borders.right = xlwt.Borders.DASHED
borders.top = xlwt.Borders.DASHED
borders.bottom = xlwt.Borders.DASHED
borders.left_colour = 0x40
borders.right_colour = 0x40
borders.top_colour = 0x40
borders.bottom_colour = 0x40
style = xlwt.XFStyle() # Create Style
style.borders = borders # Add Borders to Style
worksheet.write(0, 0, 'Cell Contents', style)
workbook.save('Excel_Workbook.xls')
嗯
以上です。