#讀取xls文件中的數據
import xlrd
?
file = "原表.xls"
wb = xlrd.open_workbook(file) #讀取工作簿
ws = wb.sheets()[0] #選第一個工作表
data = []
?
for row in range(7, ws.nrows):
? ? name = ws.cell(row, 1).value.strip() #科室名稱
? ? total1 = ws.cell(row, 2).value #總計
? ? total2 = ws.cell(row, 3).value #計
? ? avg = ws.cell(row, 20).value #平均每日人次
? ? ?
? ? info_list=[name,total1,total2,avg]
?
? ? if info_list[0] != "": #去除空數據
? ? ? ? data.append(info_list)
?
from openpyxl import load_workbook
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment #設置單元格格式
thin = Side(border_style="thin", color="000000")#定義邊框粗細及顏色
?
file = "統計表模板.xlsx"
wb = load_workbook(file)
ws = wb.active
?
#寫入數據
for i in data:
? ? ws.append(i)
? ? ?
#設置字號，對齊，縮小字體填充，加邊框
#Font(bold=True)可加粗字體
for row_number in range(3, ws.max_row+1): ? ?
? ? ws.row_dimensions[row_number].height = 25 #設置行高
? ? for col_number in range(1,5):
? ? ? ? c = ws.cell(row=row_number,column=col_number)
? ? ? ? c.font = Font(size=11,bold=True)
? ? ? ? c.border = Border(top=thin, left=thin, right=thin, bottom=thin)
? ? ? ? c.alignment = Alignment(horizontal="center", vertical="center")
? ? ?
wb.save("統計表.xlsx")