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
