245 lines
No EOL
6.8 KiB
Python
Executable file
245 lines
No EOL
6.8 KiB
Python
Executable file
#!/usr/bin/python
|
|
# -*- coding: utf-8 -*-
|
|
|
|
'''
|
|
季報
|
|
'''
|
|
|
|
from copy import deepcopy
|
|
|
|
import xlrd, xlwt
|
|
import copy
|
|
import xlutils.copy
|
|
|
|
import os
|
|
import tempfile
|
|
|
|
def listdir_fullpath(d):
|
|
return [os.path.join(d, f) for f in os.listdir(d)]
|
|
|
|
|
|
def find_dir(*paths):
|
|
for path in paths:
|
|
if os.path.isdir(path):
|
|
return path
|
|
return ''
|
|
|
|
DPublic = find_dir('P:', '/shares/Public')
|
|
|
|
TempDir = tempfile.gettempdir()
|
|
|
|
# Fix Python 2.x.
|
|
try:
|
|
UNICODE_EXISTS = bool(type(unicode))
|
|
except NameError:
|
|
unicode = lambda s: str(s)
|
|
|
|
# print(DPublic)
|
|
# exit()
|
|
|
|
template = DPublic+u'/2017/手術病例統計表/台灣神經外科醫學會101年新訂手術病例統計表(20120328).xls'
|
|
template = DPublic+u'/Sync/ByYear/2017/手術病例統計表/台灣神經外科醫學會101年新訂手術病例統計表(20120328).xls'
|
|
|
|
# path = '/Public/2015/手術病例統計表/Q3'
|
|
# dest_path = '/Public/2015/手術病例統計表/2015Q3.xls'
|
|
|
|
path = DPublic+u'//2019/手術病例統計表/Q4'
|
|
dest_path = DPublic+u'//2019/手術病例統計表/2019.xls'
|
|
|
|
|
|
def copy_sheet_old(src_book, source_index, dest_book, new_name):
|
|
'''
|
|
workbook == book in use
|
|
source_index == index of sheet you want to copy (0 start)
|
|
new_name == name of new copied sheet
|
|
'''
|
|
|
|
# make a copy of desired sheet in memory
|
|
new_sheet = copy.copy(src_book._Workbook__worksheets[source_index])
|
|
|
|
# append copy to worksheets list
|
|
dest_book._Workbook__worksheets.append(new_sheet)
|
|
|
|
# find out how many sheets are in the workbook
|
|
append_index = len(dest_book._Workbook__worksheets)-1
|
|
|
|
# activate the last sheet in the workbook
|
|
dest_book.set_active_sheet(append_index)
|
|
|
|
# rename the last sheet in the workbook
|
|
dest_book.get_sheet(append_index).set_name(new_name)
|
|
|
|
|
|
def copy_sheet(src_book, source_index, dest_book, new_name):
|
|
dest_sheet = dest_book.add_sheet(new_name, cell_overwrite_ok=True)
|
|
|
|
src_sheet = src_book.sheets()[source_index]
|
|
for row in range(src_sheet.nrows):
|
|
for col in range(src_sheet.ncols):
|
|
value = src_sheet.cell(row,col).value
|
|
dest_sheet.write(row, col, value)
|
|
|
|
return src_sheet, dest_sheet
|
|
|
|
def copy_sheet_format(src_book, source_index, dest_book, new_name):
|
|
'''
|
|
src_book
|
|
source_index == index of sheet you want to copy (0 start)
|
|
dest_book
|
|
new_name == name of new copied sheet
|
|
'''
|
|
wb = xlutils.copy.copy(src_book)
|
|
|
|
wb.save(TempDir+'/copy.xls')
|
|
|
|
# exit()
|
|
|
|
# make a copy of desired sheet in memory
|
|
|
|
# new_sheet = copy.copy(wb._Workbook__worksheets[source_index])
|
|
new_sheet = deepcopy(wb.get_sheet(source_index))
|
|
|
|
# append copy to worksheets list
|
|
dest_book._Workbook__worksheets.append(new_sheet)
|
|
|
|
|
|
# dest_book.save('/tmp/copy.xls')
|
|
# exit()
|
|
|
|
|
|
# find out how many sheets are in the workbook
|
|
append_index = len(dest_book._Workbook__worksheets)-1
|
|
|
|
# activate the last sheet in the workbook
|
|
dest_book.set_active_sheet(append_index)
|
|
|
|
# rename the last sheet in the workbook
|
|
dest_book.get_sheet(append_index).set_name(new_name)
|
|
|
|
return src_book.sheets()[source_index], dest_book.get_sheet(append_index)
|
|
|
|
def mk_int(cell):
|
|
# print cell.ctype, cell.value
|
|
if cell.ctype in [xlrd.XL_CELL_EMPTY, xlrd.XL_CELL_BLANK]:
|
|
return 0
|
|
if cell.ctype == xlrd.XL_CELL_TEXT:
|
|
s = cell.value.strip()
|
|
try:
|
|
return int(s)
|
|
except:
|
|
return 0
|
|
return int(cell.value)
|
|
|
|
template_book = xlrd.open_workbook(template, formatting_info=True)
|
|
|
|
# book = xlwt.Workbook(encoding='utf-8')
|
|
# book_sum = xlwt.Workbook(encoding='utf-8')
|
|
|
|
book = xlutils.copy.copy(template_book)
|
|
book._Workbook__worksheets = []
|
|
book.encodinfg='utf-8'
|
|
|
|
book_sum = xlutils.copy.copy(template_book)
|
|
book_sum._Workbook__worksheets = []
|
|
book_sum.encodinfg='utf-8'
|
|
|
|
SourceSheets = {}
|
|
|
|
for wb_file in sorted(os.listdir(path)):
|
|
filename = wb_file.lower()
|
|
if 'xls' in filename and 'lock' not in filename and filename[0]!='x':
|
|
sheet_name = unicode(os.path.splitext(wb_file)[0])
|
|
print (sheet_name)
|
|
else:
|
|
continue
|
|
|
|
# print os.path.join(path, wb_file)
|
|
|
|
try:
|
|
wb = xlrd.open_workbook(os.path.join(path, wb_file), formatting_info=True , on_demand=True)
|
|
except:
|
|
wb = xlrd.open_workbook(os.path.join(path, wb_file), formatting_info=False, on_demand=True)
|
|
|
|
# wb = xlrd.open_workbook(os.path.join(path, wb_file), encoding_override="utf-8")
|
|
# encoding = wb.encoding
|
|
# print wb.encoding, wb.codepage
|
|
# wb = xlutils.copy.copy(wb)
|
|
# print wb.encoding
|
|
src_sheet, dest_sheet = copy_sheet_format(wb, 0, book, sheet_name)
|
|
SourceSheets[sheet_name] = src_sheet
|
|
|
|
xxx, month_sheet = copy_sheet_format(template_book, 0, book_sum, u'依月份')
|
|
xxx, center_sheet = copy_sheet(template_book, 0, book_sum, u'依中心')
|
|
|
|
for sheet in [center_sheet, month_sheet]:
|
|
sheet.write(1, 15, '')
|
|
sheet.write(1, 16, '')
|
|
sheet.write(1, 17, '')
|
|
sheet.write(1, 18, '')
|
|
|
|
skip_row = [3, 18, 21, 24, 27, 51, 55, 56, 59]
|
|
|
|
|
|
|
|
for r in range(3, 67):
|
|
for c in range(2, 14):
|
|
if r in skip_row:
|
|
month_sheet.write(r, c, '')
|
|
continue
|
|
sum = 0
|
|
for s in sorted(SourceSheets):
|
|
# print r,c,s, xlwt.Utils.rowcol_to_cell(r, c)
|
|
src_sheet = SourceSheets[s]
|
|
# print src_sheet.cell_value(r, c)
|
|
try:
|
|
cell = src_sheet.cell(r, c)
|
|
except:
|
|
cell = None
|
|
if cell:
|
|
sum += mk_int(cell)
|
|
month_sheet.write(r, c, sum)
|
|
|
|
if r in skip_row:
|
|
month_sheet.write(r, 14, '')
|
|
else:
|
|
cell1 = xlwt.Utils.rowcol_to_cell(r, 2)
|
|
cell2 = xlwt.Utils.rowcol_to_cell(r, 13)
|
|
month_sheet.write(r, 14, xlwt.Formula("SUM(%s:%s)" % (cell1, cell2)))
|
|
|
|
col = 2
|
|
for s in sorted(SourceSheets):
|
|
src_sheet = SourceSheets[s]
|
|
sheet_name = s
|
|
|
|
# print '\n'.join(dir(src_sheet))
|
|
|
|
print( sheet_name)
|
|
center_sheet.write(1, col, sheet_name)
|
|
# center_sheet.write(1, col, u'132')
|
|
|
|
for r in range(3, 67):
|
|
sum = 0
|
|
if r in skip_row:
|
|
center_sheet.write(r, col, '')
|
|
continue
|
|
for c in range(2, 14):
|
|
try:
|
|
cell = src_sheet.cell(r, c)
|
|
except:
|
|
cell = None
|
|
if cell:
|
|
sum += mk_int(cell)
|
|
center_sheet.write(r, col, sum)
|
|
col += 1
|
|
|
|
for r in range(3, 67):
|
|
if r in skip_row:
|
|
center_sheet.write(r, col, '')
|
|
continue
|
|
cell1 = xlwt.Utils.rowcol_to_cell(r, 2)
|
|
cell2 = xlwt.Utils.rowcol_to_cell(r, col-1)
|
|
center_sheet.write(r, col, xlwt.Formula("SUM(%s:%s)" % (cell1, cell2)))
|
|
|
|
# book.save(dest_path)
|
|
book_sum._Workbook__worksheets = book_sum._Workbook__worksheets + book._Workbook__worksheets
|
|
book_sum.save(dest_path) |