def __handleSheet(self, sheet, wtSheetname=None, mvIndex=-1, option=2): self.CloseExcel(True); self.__rd=xlrd.open_workbook(self.__path, formatting_info=True); w = HandleSheetFilter(rdSheetName=sheet, wtSheetname=wtSheetname, mvIndex=mvIndex, option=option); process(XLRDReader(self.__rd,'unknown.xls'), w); self.__wt=w.output[0][1]; self.__style_list = w.style_list;
def __handleMerge(self, sheet, cellRange): self.CloseExcel(True); self.__rd=xlrd.open_workbook(self.__path, formatting_info=True); w = HandleMergeFilter(rdSheetName=sheet, cellRange=cellRange); process(XLRDReader(self.__rd,'unknown.xls'), w); self.__wt=w.output[0][1]; self.__style_list = w.style_list;
def copy2(wb): w = XLWTWriter() process( XLRDReader(wb,'unknown.xls'), w ) return w.output[0][1], w.style_list
def create_bfx_reimport_xlbook(book, feature_sheetidxs): assert all([f == feature_sheetidxs[0] for f in feature_sheetidxs]), \ "All raw features must be on the same sheet for BFX format" normalization_sheet_name = 'Normalization 1' provenance_sheet_name = 'BFX Provenance Tracking' from xlutils.filter import process, XLRDReader, XLWTWriter, BaseFilter import xlwt class OnlyRelevantSheet(BaseFilter): def __init__(self): self.__on = False def sheet(self, rdsheet, wtsheet_name): if rdsheet == book.sheet_by_index(feature_sheetidxs[0]): self.__on = True # only write this sheet self.next.sheet(rdsheet, wtsheet_name) else: self.__on = False # no call def set_rdsheet(self, rdsheet): self.next.sheet(rdsheet, wtsheet_name) def row(self, rdrowx, wtrowx): if self.__on: self.next.row(rdrowx, wtrowx) def cell(self, rdrowx, rdcolx, wtrowx, wtcolx): if self.__on: self.next.cell(rdrowx, rdcolx, wtrowx, wtcolx) class WrapWT(XLWTWriter): def sheet(self, rdsheet, wtsheet_name): XLWTWriter.sheet(self, rdsheet, wtsheet_name) def finish(self): # add two new sheets at the end self.normalization_sheet = self.wtbook.add_sheet( normalization_sheet_name) self.provenance_sheet = self.wtbook.add_sheet( provenance_sheet_name) XLWTWriter.finish(self) # hackity hack - need to force non-ascii encoding orig_Workbook_init = xlwt.Workbook.__init__ def replacement(self, encoding='utf-8', style_compression=0): orig_Workbook_init(self, encoding, style_compression) xlwt.Workbook.__init__ = replacement w = WrapWT() process(XLRDReader(book, 'unknown.xls'), OnlyRelevantSheet(), w) outbook = w.output[0][1] xlwt.Workbook.__init__ = orig_Workbook_init return outbook, w.normalization_sheet, w.provenance_sheet
def getStyle1(workbook, sheet, t1, t2): w = XLWTWriter() process(XLRDReader(workbook, 'unknown.xls'), w) s = w.style_list xl_sheet1 = workbook.sheet_by_name(sheet) styles = s[xl_sheet1.cell_xf_index(t1, t2)] return styles
def OpenExcel(self, sPath): self.__rd = xlrd.open_workbook(sPath, formatting_info=True); w = XLWTWriter(); process(XLRDReader(self.__rd, 'unknown.xls'), w); self.__wt = w.output[0][1]; self.__style_list = w.style_list; self.__path=sPath; return self;
def _copy2(self, wb): ''' 附带样式的copy xlrd打开文件,必须加参数formatting_info=True ''' w = XLWTWriter() process(XLRDReader(wb, 'unknown.xls'), w) return w.output[0][1], w.style_list
def copy(wb): """ Copy an :class:`xlrd.Book` into an :class:`xlwt.Workbook` preserving as much information from the source object as possible. See the :doc:`copy` documentation for an example. """ w = XLWTWriter() process(XLRDReader(wb, 'unknown.xls'), w) return w.output[0][1]
def __handleCell(self, sheet, cellRange, way=True, option=True): self.CloseExcel(True) self.__rd = xlrd.open_workbook(self.__path, formatting_info=True) w = HandleCellFilter(rdSheetName=sheet, cellRange=cellRange, way=way, option=option) process(XLRDReader(self.__rd, 'unknown.xls'), w) self.__wt = w.output[0][1] self.__style_list = w.style_list
def open_workbook(self, filename): wb = xlrd.open_workbook(filename, formatting_info=True, on_demand=True, encoding_override='utf-8') w = XLWTWriter() process(XLRDReader(wb, "unknown.xls"), w) self.in_book = wb self.out_book = w.output[0][1] self.style_list = w.style_list return self
def save(wb, filename_or_stream): "Save the supplied :class:`xlrd.Book` to the supplied stream or filename." if isinstance(filename_or_stream, basestring): filename = os.path.split(filename_or_stream)[1] stream = open(filename_or_stream, 'wb') close = True else: filename = 'unknown.xls' stream = filename_or_stream close = False process(XLRDReader(wb, filename), StreamWriter(stream)) if close: stream.close()
def duplicate_xlbook(book): # add two sheets, one for normalization results, one for provenance tracking existing_sheets = book.sheet_names() count = 1 while 'Normalization %d' % (count) in existing_sheets: count += 1 normalization_sheet_name = 'Normalization %d' % (count) provenance_sheet_name = 'BFX Provenance Tracking' add_provenance_sheet = (provenance_sheet_name not in existing_sheets) from xlutils.filter import process, XLRDReader, XLWTWriter import xlwt class WrapWT(XLWTWriter): def sheet(self, rdsheet, wtsheet_name): # if we're just adding a normalization sheet, add it before the provenance sheet if (not add_provenance_sheet) and (wtsheet_name == provenance_sheet_name): self.normalization_sheet = self.wtbook.add_sheet( normalization_sheet_name) # write the sheet as requested XLWTWriter.sheet(self, rdsheet, wtsheet_name) def finish(self): # add two new sheets at the end if we're adding normalization and results if add_provenance_sheet: self.normalization_sheet = self.wtbook.add_sheet( normalization_sheet_name) self.wtbook.add_sheet(provenance_sheet_name) XLWTWriter.finish(self) # hackity hack - need to force non-ascii encoding orig_Workbook_init = xlwt.Workbook.__init__ def replacement(self, encoding='utf-8', style_compression=0): orig_Workbook_init(self, encoding, style_compression) xlwt.Workbook.__init__ = replacement w = WrapWT() process(XLRDReader(book, 'unknown.xls'), w) outbook = w.output[0][1] xlwt.Workbook.__init__ = orig_Workbook_init for provenance_sheet_idx in range(len(existing_sheets) + 2): if outbook.get_sheet( provenance_sheet_idx).name == provenance_sheet_name: break return w.output[0][1], w.normalization_sheet, outbook.get_sheet( provenance_sheet_idx)
def test_single_workbook_no_formatting(self): # create test reader test_xls_path = os.path.join(test_files,'testnoformatting.xls') r = XLRDReader(open_workbook(os.path.join(test_files,'testall.xls')),'testnoformatting.xls') # source sheet must have merged cells for test! book = tuple(r.get_workbooks())[0][0] # send straight to writer w = TestWriter() r(w) # check stuff on the writer self.assertEqual(w.files.keys(),['testnoformatting.xls']) self.failUnless('testnoformatting.xls' in w.closed) self.check_file(w,test_xls_path, l_a_xf_list=17, l_e_xf_list=17, l_a_format_map=37, l_a_font_list=6, l_e_font_list=6, sheet=dict( # as of xlwt 0.7.2, these default values # are written if none are provided :-( cached_page_break_preview_mag_factor=60, cached_normal_view_mag_factor=100, ))
def save(wb,filename_or_stream): if isinstance(filename_or_stream,basestring): filename = os.path.split(filename_or_stream)[1] stream = open(filename_or_stream,'wb') close = True else: filename = 'unknown.xls' stream = filename_or_stream close = False process( XLRDReader(wb,filename), StreamWriter(stream) ) if close: stream.close()
def new_xls_with_template_style(filename): """ 使用某xls文件内的格式创建新的单元格 :param filename: :return: """ rb = xlrd.open_workbook(filename, formatting_info=True) rs = rb.sheet_by_index(0) # 获取要从模板文件中获得的样式 writer = XLWTWriter() process(XLRDReader(rb, 'nothing.xls'), writer) # 'noting.xls'为任意命名,实际不会产生影响 title_style = writer.style_list[rs.cell_xf_index(0, 0)] # 第一行一列单元格格式 content_style = writer.style_list[rs.cell_xf_index(1, 0)] # 第二行一列单元格格式 content_style.num_format_str = 'YYYY/MM/DD' # 防止日期格式丢失,特意设置格式 new_b = copy(rb) new_s = new_b.get_sheet(0) nrows = 10 ncols = 12 for row_index in range(nrows): style = content_style cell_index = datetime.date.today() if row_index == 0: style = title_style cell_index = '标题' for col_index in range(ncols): if isinstance(cell_index, str): new_s.write(row_index, col_index, cell_index, style) else: new_s.write(row_index, col_index, cell_index + datetime.timedelta(days=1), style) new_file = os.path.join(os.path.dirname(filename), 'from_template_style.xls') new_b.save(new_file)
def copy2(rb): wf = XLWTWriter() process(XLRDReader(rb, 'foo.xls'), wf) return wf.output[0][0], wf.style_list
def run(infile, outfile, filter): wb = xlrd.open_workbook(file_contents=infile.read()) reader = XLRDReader(wb, infile.name) writer = StreamWriter(outfile) process(reader, filter, Anonymize(), writer)
import xlrd from xlutils.filter import process, XLRDReader, XLWTWriter rb = xlrd.open_workbook('test.xls', formatting_info=True) table = rb.sheets()[0] print(table.cell(9,2)) # 参考xlutils.copy库内的用法 参考xlutils.filter内的参数定义style_list w = XLWTWriter() process(XLRDReader(rb, 'unknown.xls'), w) wb = w.output[0][1] style_list = w.style_list for n, sheet in enumerate(rb.sheets()): print('n:'+str(n)); print('sheet:'+str(sheet)); sheet2 = wb.get_sheet(n) for r in range(sheet.nrows): for c, cell in enumerate(sheet.row_values(r)): style = style_list[sheet.cell_xf_index(r, c)] if table.cell(r,c) == 66: sheet2.write(r, c, 'no problem sir', style) wb.save('save.xls') print("done")
def __copy_style(wb): w = XLWTWriter() process(XLRDReader(wb, 'unknown.xls'), w) return w.style_list