#!/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)