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)
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)
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 create_input_file2():
wtbook = XLWTWriter()
wtsheet = wtbook.add_sheet(u'First')
colours = 'white black red green blue pink turquoise yellow'.split()
fancy_styles = [xlwt.easyxf(
'font: name Times New Roman, italic on;'
'pattern: pattern solid, fore_colour %s;'
% colour) for colour in colours]
for rowx in xrange(8):
wtsheet.write(rowx, 0, rowx)
wtsheet.write(rowx, 1, colours[rowx], fancy_styles[rowx])
wtbook.save(u'C:/D4/test_folder/Mẫu BBBG 2018_nana.xls')
def copy2(wb):
w = XLWTWriter()
process(
XLRDReader(wb,'unknown.xls'),
w
)
return w.output[0][1], w.style_list
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 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 _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 __init__(self, ffile, *args, **kwargs):
"""
"""
self.workbook = xlrd.open_workbook(ffile.file, formatting_info=True)
self.xlwt_writer = XLWTWriter()
self.xlwt_writer.start()
self.xlwt_writer.workbook(self.workbook, '%s.xls' % uuid4())
self.sheets = self._sheet_list()
if self.sheets:
pass
# self.init_active_sheet()
else:
raise SheetNotFoundException('Sheets not found')
for k, v in kwargs.items():
setattr(self, k, v)
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 __init__(self, ffile, *args, **kwargs):
"""
"""
self.workbook = xlrd.open_workbook(ffile.file, formatting_info=True)
self.xlwt_writer = XLWTWriter()
self.xlwt_writer.start()
self.xlwt_writer.workbook(self.workbook, '%s.xls'%uuid4())
self.sheets = self._sheet_list()
if self.sheets:
pass
# self.init_active_sheet()
else:
raise SheetNotFoundException('Sheets not found')
for k, v in kwargs.items():
setattr(self, k, v)
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
class Workbook(object):
def __init__(self, ffile, *args, **kwargs):
"""
"""
self.workbook = xlrd.open_workbook(ffile.file, formatting_info=True)
self.xlwt_writer = XLWTWriter()
self.xlwt_writer.start()
self.xlwt_writer.workbook(self.workbook, '%s.xls' % uuid4())
self.sheets = self._sheet_list()
if self.sheets:
pass
# self.init_active_sheet()
else:
raise SheetNotFoundException('Sheets not found')
for k, v in kwargs.items():
setattr(self, k, v)
def init_active_sheet(self):
"""
инициализация активного листа
:result: None
"""
self._active_sheet = self.sheets[0]
self.xlwt_writer.sheet(self._active_sheet.sheet,
self._active_sheet.sheet.name)
def configure_writer(self):
"""
Настройка конфигураций writer-а
"""
if hasattr(self, 'fit_num_pages'):
self.xlwt_writer.wtsheet.fit_num_pages = self.fit_num_pages
if hasattr(self, 'portrait_orientation'):
self.xlwt_writer.wtsheet.portrait = self.portrait_orientation
if hasattr(self, 'fit_width_to_pages'):
self.xlwt_writer.wtsheet.fit_width_to_pages = self.fit_width_to_pages
if hasattr(self, 'fit_height_to_pages'):
self.xlwt_writer.wtsheet.fit_height_to_pages = self.fit_height_to_pages
else:
# По-умолчанию, указываем значение 0, для того, чтобы не запихивать огромный отчет на одну страницу.
self.xlwt_writer.wtsheet.fit_height_to_pages = 0
if hasattr(self, 'header_str'):
self.xlwt_writer.wtsheet.header_str = self.header_str
else:
#
self.xlwt_writer.wtsheet.header_str = (u'')
if hasattr(self, 'footer_str'):
self.xlwt_writer.wtsheet.footer_str = self.footer_str
else:
#
self.xlwt_writer.wtsheet.footer_str = (u'&P из &N')
def get_section(self, name):
"""
Получение секции по имени
:param name: имя секции
:result: секция
"""
if not hasattr(self, '_active_sheet'):
self._active_sheet = self.sheets[0]
self.xlwt_writer.sheet(self._active_sheet.sheet,
self._active_sheet.sheet.name)
return self._active_sheet.get_section(name)
def get_sections(self):
"""
Получение всех секций
:result: словарь с секциями
"""
workbook_sections = {}
for sheet in self.sheets:
workbook_sections.update(sheet.get_sections())
return workbook_sections
@property
def active_sheet(self):
return self._active_sheet
@active_sheet.setter
def active_sheet(self, value):
assert isinstance(value, int)
try:
self._active_sheet = self.sheets[value]
except IndexError:
raise SheetNotFoundException('Sheet not found')
try:
self.xlwt_writer.sheet(self._active_sheet.sheet,
self.get_sheet_name())
# Проставляем номер листа в выходном документе.
# Для этого берём номер соответствующего узла из шаблона и прибавляем 1, т.к.
# нумерация с нуля.
self.xlwt_writer.wtsheet.start_page_number = self.xlwt_writer.rdsheet.number + 1
# Настраиваем writer для работы с новым листом.
self.configure_writer()
except ValueError:
return
def _sheet_list(self):
all_sheets = self.workbook._sheet_list
sheet_list = []
for sheet in all_sheets:
sheet_list.append(WorkbookSheet(sheet, self.xlwt_writer))
return sheet_list
def get_sheet_name(self):
return self.active_sheet.get_name()
def build(self, dest_file):
"""
Сборка книги
:param dest_file: выходной путь
:result: None
"""
self.configure_writer()
dest_file_name = dest_file.file
self.xlwt_writer.finish()
# Получаем формат файла
dest_file_format = dest_file_name.split('.')[-1]
# Если не xls, то проставляем явно.
if dest_file_format != FileConverter.XLS:
dest_file_name = '%s.%s' % (dest_file_name, FileConverter.XLS)
# self.xlwt_writer.output имеет вид
# [('выходной файл1', Workbook1), ('выходной файл2', Workbook2), ... ]
# Для данного Workbook выбираем первый кортеж
ouput_file, workbook = self.xlwt_writer.output[0]
workbook.save(dest_file_name)
def write_sheet_count(self):
"""
Подсчитываем количество листов в которых есть секции
(другими словами в них будет производиться запись)
"""
# Берём листы из шаблона
read_sheets = self.xlwt_writer.rdbook._sheet_list
return len([
read_sheet for read_sheet in read_sheets
if read_sheet.cell_note_map
])
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)
def sheet(self, rdsheet, wtsheet_name):
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)
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 setUp(self):
self.w = XLWTWriter()
class TestXLWTWriter(TestCase):
def setUp(self):
self.w = XLWTWriter()
def test_inheritance(self):
self.failUnless(isinstance(self.w,BaseWriter))
def test_no_files(self):
r = GlobReader(os.path.join(test_files,'*not.xls'))
r(self.w)
compare(self.w.output,[])
def test_one_file(self):
r = GlobReader(os.path.join(test_files,'test.xls'))
r(self.w)
compare(self.w.output,[
('test.xls',C('xlwt.Workbook'))
])
# make sure wtbook is deleted
compare(self.w.wtbook,None)
def test_multiple_files(self):
r = GlobReader(os.path.join(test_files,'*.xls'))
r(self.w)
compare(self.w.output,[
('test.xls',C('xlwt.Workbook')),
('testall.xls',C('xlwt.Workbook')),
('testnoformatting.xls',C('xlwt.Workbook')),
])
def test_multiple_files_same_name(self):
r = TestReader(
('Sheet1',[['S1R0C0']]),
)
book = tuple(r.get_workbooks())[0][0]
self.w.start()
self.w.workbook(book,'new.xls')
self.w.sheet(book.sheet_by_index(0),'new1')
self.w.cell(0,0,0,0)
self.w.workbook(book,'new.xls')
self.w.sheet(book.sheet_by_index(0),'new2')
self.w.cell(0,0,0,0)
self.w.finish()
compare(self.w.output,[
('new.xls',C('xlwt.Workbook')),
('new.xls',C('xlwt.Workbook')),
])
compare(self.w.output[0][1].get_sheet(0).name,
'new1')
compare(self.w.output[1][1].get_sheet(0).name,
'new2')
class Workbook(object):
def __init__(self, ffile, *args, **kwargs):
"""
"""
self.workbook = xlrd.open_workbook(ffile.file, formatting_info=True)
self.xlwt_writer = XLWTWriter()
self.xlwt_writer.start()
self.xlwt_writer.workbook(self.workbook, '%s.xls'%uuid4())
self.sheets = self._sheet_list()
if self.sheets:
pass
# self.init_active_sheet()
else:
raise SheetNotFoundException('Sheets not found')
for k, v in kwargs.items():
setattr(self, k, v)
def init_active_sheet(self):
"""
инициализация активного листа
:result: None
"""
self._active_sheet = self.sheets[0]
self.xlwt_writer.sheet(self._active_sheet.sheet, self._active_sheet.sheet.name)
def configure_writer(self):
"""
Настройка конфигураций writer-а
"""
if hasattr(self, 'fit_num_pages'):
self.xlwt_writer.wtsheet.fit_num_pages = self.fit_num_pages
if hasattr(self, 'portrait_orientation'):
self.xlwt_writer.wtsheet.portrait = self.portrait_orientation
if hasattr(self, 'fit_width_to_pages'):
self.xlwt_writer.wtsheet.fit_width_to_pages = self.fit_width_to_pages
if hasattr(self, 'fit_height_to_pages'):
self.xlwt_writer.wtsheet.fit_height_to_pages = self.fit_height_to_pages
else:
# По-умолчанию, указываем значение 0, для того, чтобы не запихивать огромный отчет на одну страницу.
self.xlwt_writer.wtsheet.fit_height_to_pages = 0
if hasattr(self, 'header_str'):
self.xlwt_writer.wtsheet.header_str = self.header_str
else:
#
self.xlwt_writer.wtsheet.header_str = (u'')
if hasattr(self, 'footer_str'):
self.xlwt_writer.wtsheet.footer_str = self.footer_str
else:
#
self.xlwt_writer.wtsheet.footer_str = (u'&P из &N')
def get_section(self, name):
"""
Получение секции по имени
:param name: имя секции
:result: секция
"""
if not hasattr(self, '_active_sheet'):
self._active_sheet = self.sheets[0]
self.xlwt_writer.sheet(self._active_sheet.sheet, self._active_sheet.sheet.name)
return self._active_sheet.get_section(name)
def get_sections(self):
"""
Получение всех секций
:result: словарь с секциями
"""
workbook_sections = {}
for sheet in self.sheets:
workbook_sections.update(sheet.get_sections())
return workbook_sections
@property
def active_sheet(self):
return self._active_sheet
@active_sheet.setter
def active_sheet(self, value):
assert isinstance(value, int)
try:
self._active_sheet = self.sheets[value]
except IndexError:
raise SheetNotFoundException('Sheet not found')
try:
self.xlwt_writer.sheet(self._active_sheet.sheet, self.get_sheet_name())
# Проставляем номер листа в выходном документе.
# Для этого берём номер соответствующего узла из шаблона и прибавляем 1, т.к.
# нумерация с нуля.
self.xlwt_writer.wtsheet.start_page_number = self.xlwt_writer.rdsheet.number + 1
# Настраиваем writer для работы с новым листом.
self.configure_writer()
except ValueError:
return
def _sheet_list(self):
all_sheets = self.workbook._sheet_list
sheet_list = []
for sheet in all_sheets:
sheet_list.append(WorkbookSheet(sheet, self.xlwt_writer))
return sheet_list
def get_sheet_name(self):
return self.active_sheet.get_name()
def build(self, dest_file):
"""
Сборка книги
:param dest_file: выходной путь
:result: None
"""
self.configure_writer()
dest_file_name = dest_file.file
self.xlwt_writer.finish()
# Получаем формат файла
dest_file_format = dest_file_name.split('.')[-1]
# Если не xls, то проставляем явно.
if dest_file_format != FileConverter.XLS:
dest_file_name = '%s.%s' % (dest_file_name, FileConverter.XLS)
# self.xlwt_writer.output имеет вид
# [('выходной файл1', Workbook1), ('выходной файл2', Workbook2), ... ]
# Для данного Workbook выбираем первый кортеж
ouput_file, workbook = self.xlwt_writer.output[0]
workbook.save(dest_file_name)
def write_sheet_count(self):
"""
Подсчитываем количество листов в которых есть секции
(другими словами в них будет производиться запись)
"""
# Берём листы из шаблона
read_sheets = self.xlwt_writer.rdbook._sheet_list
return len([read_sheet for read_sheet in read_sheets if read_sheet.cell_note_map])
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 sheet(self, rdsheet, wtsheet_name):
XLWTWriter.sheet(self, rdsheet, wtsheet_name)
def __copy_style(wb):
w = XLWTWriter()
process(XLRDReader(wb, 'unknown.xls'), w)
return w.style_list
