def process_dir(data_dir_path, output_file_path):
# crate a new workbook and sheets
wb_new = Workbook()
ws_deliveries = wb_new.get_active_sheet()
ws_deliveries.title = 'Deliveries'
ws_returns = wb_new.create_sheet(1)
ws_returns.title = 'Returns'
ws_wastage = wb_new.create_sheet(2)
ws_wastage.title = 'Wastage'
ws_staff_meals = wb_new.create_sheet(3)
ws_staff_meals.title = 'Staff Meals'
ws_transfers_in = wb_new.create_sheet(4)
ws_transfers_in.title = 'Transfers In'
ws_transfers_out = wb_new.create_sheet(5)
ws_transfers_out.title = 'Transfers Out'
# get the list of files in the directory
onlyfiles = [ f for f in listdir(data_dir_path) if isfile(join(data_dir_path,f))]
# process each file
for f in onlyfiles:
process_file(data_dir_path + f, wb_new)
# save the new workbook
wb_new.save(output_file_path)
def test_bad_encoding():
test_string = u'Compound Value (£)'.encode('latin1')
utf_book = Workbook()
utf_sheet = utf_book.get_active_sheet()
utf_sheet.cell('A1').value = test_string
def xls2xlsx(filename):
# Old .xls workbook
book_xls = xlrd.open_workbook(filename)
# New .xlsx workbook
book_xlsx = Workbook()
sheet_names = book_xls.sheet_names()
# Iterate through .xls sheet names to create matching ones in new workbook
for sheet_index in range(0, len(sheet_names)):
sheet_xls = book_xls.sheet_by_name(sheet_names[sheet_index])
# If its the first sheet then get the first .xlsx sheet
if sheet_index == 0:
sheet_xlsx = book_xlsx.get_active_sheet()
sheet_xlsx.title = sheet_names[sheet_index]
else:
sheet_xlsx = book_xlsx.create_sheet(title=sheet_names[sheet_index])
# Nested for loop to go through each cell and copy over to the new .xlsx sheet
for row in range(0, sheet_xls.nrows):
for col in range(0, sheet_xls.ncols):
sheet_xlsx.cell(row=row + 1,
column=col + 1).value = sheet_xls.cell_value(
row, col)
# After going through each sheet and copying them over to .xlsx
# save the new .xlsx workbook by appending 'x' to file extension
book_xlsx.save(filename + "x")
def test_bad_encoding():
test_string = u'Compound Value (£)'.encode('latin1')
utf_book = Workbook()
utf_sheet = utf_book.get_active_sheet()
utf_sheet.cell('A1').value = test_string
def test_good_encoding():
test_string = u'Compound Value (£)'.encode('latin1')
lat_book = Workbook(encoding='latin1')
lat_sheet = lat_book.get_active_sheet()
lat_sheet.cell('A1').value = test_string
def make_worksheet(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'data'
ws.append(list(range(10)))
return ws
def setUp(self):
wb = Workbook()
ws = wb.get_active_sheet()
for i in range(10):
ws.cell(row=i, column=0).value = i
self.cell = Reference(ws, (0, 0))
self.range = Reference(ws, (0, 0), (9, 0))
def setup(self):
wb = Workbook()
ws = wb.get_active_sheet()
for i in range(10):
ws.cell(row=i, column=0).value = 1
values = Reference(ws, (0, 0), (0, 9))
self.range = Serie(values=values)
def test_good_encoding():
test_string = u'Compound Value (£)'.encode('latin1')
lat_book = Workbook(encoding='latin1')
lat_sheet = lat_book.get_active_sheet()
lat_sheet.cell('A1').value = test_string
def process_dir(data_dir_path, output_file_path):
# crate a new workbook and sheets
wb_new = Workbook()
ws_deliveries = wb_new.get_active_sheet()
ws_deliveries.title = 'Deliveries'
ws_returns = wb_new.create_sheet(1)
ws_returns.title = 'Returns'
ws_wastage = wb_new.create_sheet(2)
ws_wastage.title = 'Wastage'
ws_staff_meals = wb_new.create_sheet(3)
ws_staff_meals.title = 'Staff Meals'
ws_transfers_in = wb_new.create_sheet(4)
ws_transfers_in.title = 'Transfers In'
ws_transfers_out = wb_new.create_sheet(5)
ws_transfers_out.title = 'Transfers Out'
# get the list of files in the directory
onlyfiles = [
f for f in listdir(data_dir_path) if isfile(join(data_dir_path, f))
]
# process each file
for f in onlyfiles:
process_file(data_dir_path + f, wb_new)
# save the new workbook
wb_new.save(output_file_path)
def open_xls_as_xlsx(filename):
try:
# noinspection PyUnresolvedReferences
import xlrd
# noinspection PyUnresolvedReferences
from openpyxl.workbook import Workbook
# noinspection PyUnresolvedReferences
from openpyxl.reader.excel import load_workbook
book = xlrd.open_workbook(filename)
index = 0
nrows, ncols = 0, 0
sheet = None
while nrows * ncols == 0:
sheet = book.sheet_by_index(index)
nrows = sheet.nrows
ncols = sheet.ncols
index += 1
book1 = Workbook()
sheet1 = book1.get_active_sheet()
for row in range(0, nrows):
for col in range(0, ncols):
sheet1.cell(row=row + 1, column=col + 1).value = sheet.cell_value(row, col)
filename = filename.replace('.xls', '.xlsx')
book1.save(filename)
return Path(filename)
except ImportError:
print('xlrd and openpyxl are required in order to convert an xls file to xlsx')
raise
def test_image_anchor(self):
DummyImage = self._get_dummy_class()
wb = Workbook()
ws = wb.get_active_sheet()
cell = ws.cell('D32')
img = DummyImage(None)
img.anchor(cell)
eq_((img.drawing.top, img.drawing.left), (620, 210))
def make_calenda(task_list, date_from, days, save_as):
date_to = date_from + datetime.timedelta(days=days)
print date_to
from_column = 2
row = 0
# Load the workbook...
wb = Workbook()
ws = wb.get_active_sheet()
ws.cell(row = 0, column = 0).value = u'任务名称'
ws.cell(row = 0, column = 1).value = u'任务说明'
month_set = set()
weekday_column_list = []
for i in range(days):
d = date_from + datetime.timedelta(days=i)
month = d.month
if month not in month_set:
cell = ws.cell(row = row, column = i+from_column)
cell.value = u'%s月' % d.month
month_set.add(month)
cell = ws.cell(row = row+1, column = i+from_column)
cell.value = '%s' % d.day
if d.weekday() in (5,6):
weekday_column_list.append(i+from_column)
for ws_column in range(from_column+1,from_column+days+1):
col_letter = get_column_letter(ws_column)
###~ ws.column_dimensions[col_letter].auto_size = True
ws.column_dimensions[col_letter].width = 2.7
for row in range(1,len(task_list)+2):
for column in weekday_column_list:
cell = ws.cell(row=row,column=column)
cell.style.fill.fill_type = Fill.FILL_SOLID
cell.style.fill.start_color.index = 'CBCACA'
for i,task in enumerate(task_list):
cell = ws.cell(row=i+2,column=0)
cell.value = task
#设置单元格border
for row in range(0,len(task_list)+2):
for col in range(0, days+2):
cell = ws.cell(row=row,column=col)
cell.style.borders.top.border_style = Border.BORDER_THIN
cell.style.borders.right.border_style = Border.BORDER_THIN
cell.style.borders.bottom.border_style = Border.BORDER_THIN
cell.style.borders.left.border_style = Border.BORDER_THIN
#设置第一列宽
ws.column_dimensions['A'].width = 30
wb.save(save_as)
def test_image_end(self):
DummyImage = self._get_dummy_class()
wb = Workbook()
ws = wb.get_active_sheet()
cell = ws.cell('A1')
img = DummyImage(None)
img.drawing.width, img.drawing.height = (50, 50)
end = img.anchor(cell)
eq_(end[1], ('A', 3))
def setup(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'reference'
for i in range(10):
ws.cell(row=i, column=0).value = i
self.sheet = ws
self.cell = Reference(self.sheet, (0, 0))
self.range = Reference(self.sheet, (0, 0), (9, 0))
def new_landscape_a4_sheet(workbook, title1, title2, header=None, add_print_title=True):
if workbook is None:
workbook = Workbook()
worksheet = workbook.get_active_sheet()
else:
worksheet = workbook.create_sheet()
worksheet = worksheet_setup_landscape_a4(worksheet, title1, title2, add_print_title=add_print_title)
if header is not None:
worksheet_set_header(worksheet, header)
return workbook, worksheet
def setup(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'reference'
for i in range(10):
ws.cell(row=i, column=0).value = i
self.sheet = ws
self.cell = Reference(self.sheet, (0, 0))
self.range = Reference(self.sheet, (0, 0), (9, 0))
def setup(self):
"""Setup a worksheet with one column of data and a line chart"""
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = "Line"
for i in range(1, 5):
ws.append([i])
self.piechart = LineChart()
self.piechart.add_serie(Serie(Reference(ws, (0, 0), (4, 0))))
self.cw = LineChartWriter(self.piechart)
self.root = Element("test")
def setup(self):
"""Setup a worksheet with one column of data and a bar chart"""
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'Numbers'
for i in range(10):
ws.append([i])
self.piechart = BarChart()
self.piechart.add_serie(Serie(Reference(ws, (0, 0), (9, 0))))
self.cw = BarChartWriter(self.piechart)
self.root = Element('test')
def setup(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = "Scatter"
for i in range(10):
ws.cell(row=i, column=0).value = i
ws.cell(row=i, column=1).value = i
self.scatterchart = ScatterChart()
self.scatterchart.add_serie(Serie(Reference(ws, (0, 0), (10, 0)), xvalues=Reference(ws, (0, 1), (10, 1))))
self.cw = ScatterChartWriter(self.scatterchart)
self.root = Element("test")
def test_good_encoding():
try:
# Python 2
pound = unichr(163)
except NameError:
# Python 3
pound = chr(163)
test_string = ('Compound Value (' + pound + ')').encode('latin1')
lat_book = Workbook(encoding='latin1')
lat_sheet = lat_book.get_active_sheet()
lat_sheet.cell('A1').value = test_string
def test_write_regular_float():
float_value = 1.0 / 3.0
book = Workbook()
sheet = book.get_active_sheet()
sheet.cell("A1").value = float_value
dest_filename = osp.join(TMPDIR, 'float_read_write_issue.xlsx')
book.save(dest_filename)
test_book = load_workbook(dest_filename)
test_sheet = test_book.get_active_sheet()
eq_(test_sheet.cell("A1").value, float_value)
def test_write_regular_float(tmpdir):
float_value = 1.0 / 3.0
book = Workbook()
sheet = book.get_active_sheet()
sheet.cell("A1").value = float_value
dest_filename = 'float_read_write_issue.xlsx'
book.save(dest_filename)
validate_archive(dest_filename)
test_book = load_workbook(dest_filename)
test_sheet = test_book.get_active_sheet()
assert test_sheet.cell("A1").value == float_value
def test_good_encoding():
try:
# Python 2
pound = unichr(163)
except NameError:
# Python 3
pound = chr(163)
test_string = ("Compound Value (" + pound + ")").encode("latin1")
lat_book = Workbook(encoding="latin1")
lat_sheet = lat_book.get_active_sheet()
lat_sheet.cell("A1").value = test_string
def setUp(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'data'
for i in range(10):
ws.cell(row=i, column=0).value = i
ws.cell(row=i, column=1).value = i
self.scatterchart = ScatterChart()
self.scatterchart.add_serie(Serie(Reference(ws, (0, 0), (10, 0)),
xvalues=Reference(ws, (0, 1), (10, 1))))
self.cw = ChartWriter(self.scatterchart)
self.root = Element('test')
def setUp(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = u'data'
for i in range(10):
ws.cell(row = i, column = 0).value = i
self.chart = BarChart()
self.chart.title = 'TITLE'
self.chart.add_serie(Serie(Reference(ws, (0, 0), (10, 0))))
self.chart._series[-1].color = Color.GREEN
self.cw = ChartWriter(self.chart)
self.root = Element('test')
def test_good_encoding():
try:
# Python 2
pound = unichr(163)
except NameError:
# Python 3
pound = chr(163)
test_string = ('Compound Value (' + pound + ')').encode('latin1')
lat_book = Workbook(encoding='latin1')
lat_sheet = lat_book.get_active_sheet()
lat_sheet.cell('A1').value = test_string
def test_bad_encoding():
try:
# Python 2
pound = unichr(163)
except NameError:
# Python 3
pound = chr(163)
test_string = ('Compound Value (' + pound + ')').encode('latin1')
utf_book = Workbook()
utf_sheet = utf_book.get_active_sheet()
with pytest.raises(UnicodeDecodeError):
utf_sheet.cell('A1').value = test_string
def test_write_regular_float():
float_value = 1.0 / 3.0
book = Workbook()
sheet = book.get_active_sheet()
sheet.cell("A1").value = float_value
dest_filename = osp.join(TMPDIR, 'float_read_write_issue.xlsx')
book.save(dest_filename)
test_book = load_workbook(dest_filename)
test_sheet = test_book.get_active_sheet()
eq_(test_sheet.cell("A1").value, float_value)
def setUp(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = u'data'
for i in range(10):
ws.cell(row=i, column=0).value = i
self.chart = BarChart()
self.chart.title = 'TITLE'
self.chart.add_serie(Serie(Reference(ws, (0, 0), (10, 0))))
self.chart._series[-1].color = Color.GREEN
self.cw = ChartWriter(self.chart)
self.root = Element('test')
def test_write_regular_date(tmpdir):
tmpdir.chdir()
today = datetime.datetime(2010, 1, 18, 14, 15, 20, 1600)
book = Workbook()
sheet = book.get_active_sheet()
sheet.cell("A1").value = today
dest_filename = 'date_read_write_issue.xlsx'
book.save(dest_filename)
validate_archive(dest_filename)
test_book = load_workbook(dest_filename)
test_sheet = test_book.get_active_sheet()
assert test_sheet.cell("A1").value == today
def setup(self):
"""Setup a worksheet with one column of data and a pie chart"""
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'Pie'
for i in range(1, 5):
ws.append([i])
self.piechart = PieChart()
values = Reference(ws, (0, 0), (9, 0))
series = Serie(values, labels=values)
self.piechart.add_serie(series)
ws.add_chart(self.piechart)
self.cw = PieChartWriter(self.piechart)
self.root = Element('test')
def test_write_regular_date():
today = datetime.datetime(2010, 1, 18, 14, 15, 20, 1600)
book = Workbook()
sheet = book.get_active_sheet()
sheet.cell("A1").value = today
dest_filename = osp.join(TMPDIR, 'date_read_write_issue.xlsx')
book.save(dest_filename)
test_book = load_workbook(dest_filename)
test_sheet = test_book.get_active_sheet()
eq_(test_sheet.cell("A1").value, today)
def test_write_regular_date():
today = datetime.datetime(2010, 1, 18, 14, 15, 20, 1600)
book = Workbook()
sheet = book.get_active_sheet()
sheet.cell("A1").value = today
dest_filename = osp.join(TMPDIR, 'date_read_write_issue.xlsx')
book.save(dest_filename)
test_book = load_workbook(dest_filename)
test_sheet = test_book.get_active_sheet()
eq_(test_sheet.cell("A1").value, today)
def create_xlsx_workbook(queryset, fields, creator='Unknown', description=''):
wb = Workbook()
wb.properties.description = description
wb.properties.creator = creator
ws = wb.get_active_sheet()
ws.title = "Search Results"
if queryset.count:
populate_headers(ws, queryset, fields)
populate_data(ws, queryset, fields)
setup_header_cells(ws)
set_column_widths(ws, fields)
return wb
def test_no_write_legend(self):
wb = Workbook()
ws = wb.get_active_sheet()
ws.title = 'data'
for i in range(10):
ws.cell(row=i, column=0).value = i
ws.cell(row=i, column=1).value = i
scatterchart = ScatterChart()
scatterchart.add_serie(Serie(Reference(ws, (0, 0), (10, 0)),
xvalues=Reference(ws, (0, 1), (10, 1))))
cw = ChartWriter(scatterchart)
root = Element('test')
scatterchart.show_legend = False
cw._write_legend(root)
eq_(get_xml(root), '<?xml version=\'1.0\' encoding=\'UTF-8\'?><test />')
def new_landscape_a4_sheet(workbook,
title1,
title2,
header=None,
add_print_title=True):
if workbook is None:
workbook = Workbook()
worksheet = workbook.get_active_sheet()
else:
worksheet = workbook.create_sheet()
worksheet = worksheet_setup_landscape_a4(worksheet,
title1,
title2,
add_print_title=add_print_title)
if header is not None:
worksheet_set_header(worksheet, header)
return workbook, worksheet
def open_xls_as_xlsx(filename):
# first open using xlrd
book = xlrd.open_workbook(filename)
sheet = book.sheet_by_index(0)
nrows = sheet.nrows
ncols = sheet.ncols
# prepare a xlsx sheet
book1 = Workbook()
sheet1 = book1.get_active_sheet()
if nrows == 0 or ncols == 0:
return book1
for row in range(0, nrows):
for col in range(0, ncols):
sheet1.cell(row=row + 1, column=col + 1).value = sheet.cell_value(row, col)
#print(sheet.cell_value(row, col))
return book1
def open_xls_as_xlsx(filename, index): # reading xls into openpyxl workbook
# first open using xlrd
book = xlrd.open_workbook(filename)
nrows, ncols = 0, 0
while nrows * ncols == 0:
sheet = book.sheet_by_index(index)
nrows = sheet.nrows
ncols = sheet.ncols
index += 1
# prepare a xlsx sheet
book1 = Workbook()
sheet1 = book1.get_active_sheet()
for row in range(0, nrows):
for col in range(0, ncols):
sheet1.cell(row=row + 1,
column=col + 1).value = sheet.cell_value(row, col)
return book1
def open_xls_as_xlsx(filename):
# first open using xlrd
book = xlrd.open_workbook(filename)
index = 0
nrows, ncols = 0, 0
while nrows * ncols == 0:
sheet = book.sheet_by_index(index)
nrows = sheet.nrows
ncols = sheet.ncols
index += 1
# prepare a xlsx sheet
book1 = Workbook()
sheet1 = book1.get_active_sheet()
for row in range(1, nrows + 1):
for col in range(1, ncols + 1):
sheet1.cell(row=row,
column=col).value = sheet.cell_value(row - 1, col - 1)
return book1
def xlstoxlsx(filename):
import xlrd
from openpyxl.workbook import Workbook
from openpyxl.reader.excel import load_workbook, InvalidFileException
# first open using xlrd
book = xlrd.open_workbook(filename)
index = 0
nrows, ncols = 0, 0
while nrows * ncols == 0:
sheet = book.sheet_by_index(index)
nrows = sheet.nrows
ncols = sheet.ncols
index += 1
# prepare a xlsx sheet
book1 = Workbook()
sheet1 = book1.get_active_sheet()
for row in xrange(0, nrows):
for col in xrange(0, ncols):
sheet1.cell(row=row, column=col).value = sheet.cell_value(row, col)
return book1
def open_xls_as_xlsx(filename):
"""
Returns a openpyxl.workbook object for a .xls file
http://stackoverflow.com/questions/9918646/how-to-convert-xls-to-xlsx
"""
# first open using xlrd
book = xlrd.open_workbook(filename)
index = 0
nrows, ncols = 0, 0
while nrows * ncols == 0:
sheet = book.sheet_by_index(index)
nrows = sheet.nrows
ncols = sheet.ncols
index += 1
# prepare a xlsx sheet
book1 = Workbook()
sheet1 = book1.get_active_sheet()
for row in xrange(0, nrows):
for col in xrange(0, ncols):
sheet1.cell(row=row, column=col).value = sheet.cell_value(row, col)
return book1
def upgrade(self):
print(self.filename)
# first open using xlrd
book = xlrd.open_workbook(self.filename, formatting_info=True)
index = 0
nrows, ncols = 0, 0
while nrows * ncols == 0:
sheet = book.sheet_by_index(index)
nrows = sheet.nrows
ncols = sheet.ncols
index += 1
# prepare a xlsx sheet
book1 = Workbook()
sheet1 = book1.get_active_sheet()
sheet1.title = 'hoja1'
for row in range(1, nrows+1):
for col in range(1, ncols+1):
sheet1.cell(row=row, column=col).value = self._get_xlrd_cell_value(sheet.cell(row-1, col-1))
book1.save(self.filename + "x")
def test_roundtrip(self):
wb = Workbook()
ws = wb.get_active_sheet()
for address in ('A1', 'D52', 'X11'):
eq_(ws.point_pos(*ws.cell(address).anchor),
coordinate_from_string(address))
def test_point(self):
wb = Workbook()
ws = wb.get_active_sheet()
eq_(ws.point_pos(top=40, left=150), ('C', 3))
class Html2Excel(object):
def __init__(self):
self.list = []
self.start_row = 0
self.start_col = 0
self.workbook = Workbook(encoding='utf8')
self.worksheet = self.workbook.active
def use_existing_wb(self, name_workbook):
self.workbook = load_workbook(filename=name_workbook)
self.worksheet = self.workbook.get_active_sheet()
def create_new_sheet(self, name_sheet):
self.worksheet = self.workbook.create_sheet()
self.worksheet.title = name_sheet
def set_col_width(self, cols_width):
for col_i in cols_width.keys():
self.worksheet.column_dimensions[get_column_letter(
col_i)].width = int(cols_width.get(col_i))
def add_logo(self, logo_filename):
img = drawing.Image(logo_filename)
img.anchor(self.worksheet.cell('D3'))
self.worksheet.add_image(img)
def append_html_table(self, html_string, start_row=1, start_col=1):
html_string = document_fromstring(html_string,
HTMLParser(encoding='utf8'))
last_row = start_row - 1
last_col = start_col
for table_el in html_string.xpath('//table'):
last_row += 1
for row_i, row in enumerate(table_el.xpath('./tr'),
start=last_row):
for col_i, col in enumerate(row.xpath('./td|./th'),
start=last_col):
colspan = int(col.get('colspan', 0))
rowspan = int(col.get('rowspan', 0))
font_bold = False
font_size = 11
font_color = BLACK_COLOR
if rowspan:
rowspan -= 1
if colspan:
colspan -= 1
col_data = col.text_content().encode("utf8")
valign = 'center' if col_i == start_col and col.tag != 'th' else 'top'
while (row_i, col_i) in self.list:
col_i += 1
cell = self.worksheet.cell(row=row_i, column=col_i)
if rowspan or colspan:
self.worksheet.merge_cells(start_row=row_i,
end_row=row_i + rowspan,
start_column=col_i,
end_column=col_i + colspan)
cell.value = col_data
cell.alignment = Alignment(
horizontal=row.get('align', col.get('align'))
or 'left',
vertical=row.get('valign', col.get('valign'))
or valign,
shrink_to_fit=True,
wrap_text=True)
bgcolor = row.get('bgcolor', col.get('bgcolor'))
if bgcolor:
cell.fill = PatternFill(fill_type='solid',
start_color=bgcolor,
end_color=bgcolor)
for el in col.iter():
if el.tag == 'font':
font_color = el.get('color')
elif el.tag == 'b':
font_bold = True
elif el.tag in _TEXT_SIZE:
font_bold = True,
font_size = _TEXT_SIZE.get(el)
cell.font = Font(
color=font_color,
bold=font_bold,
size=font_size,
)
if col.tag == 'th':
cell.font = Font(bold=True)
cell.fill = PatternFill(fill_type='solid',
start_color=TH_COLOR,
end_color=TH_COLOR)
for i in range(0, rowspan + 1, 1):
for j in range(0, colspan + 1, 1):
if i == rowspan:
last_row = row_i + i
self.list.append((row_i + i, col_i + j))
cell = self.worksheet.cell(row=row_i + i,
column=col_i + j)
cell.border = Border(
left=Side(border_style=_BORDER_STYLE.get(
table_el.get('border') or None),
color=BORDER_COLOR),
right=Side(border_style=_BORDER_STYLE.get(
table_el.get('border') or None),
color=BORDER_COLOR),
top=Side(border_style=_BORDER_STYLE.get(
table_el.get('border') or None),
color=BORDER_COLOR),
bottom=Side(border_style=_BORDER_STYLE.get(
table_el.get('border') or None),
color=BORDER_COLOR),
)
return last_row, last_col
def save_wb(self, name):
self.workbook.save(name)
def test_roundtrip(self, value):
wb = Workbook()
ws = wb.get_active_sheet()
assert ws.point_pos(*ws.cell(value).anchor) == coordinate_from_string(value)
def test_roundtrip(self, value):
wb = Workbook()
ws = wb.get_active_sheet()
assert ws.point_pos(
*ws.cell(value).anchor) == coordinate_from_string(value)
def test_point(self):
wb = Workbook()
ws = wb.get_active_sheet()
assert ws.point_pos(top=40, left=150), ('C' == 3)
class XlReport:
""" Wrapper for openpyxl
Using xlsx because xls has limitations and ods has no good python
library
"""
def __init__(self, file_name="Report"):
""" file_name does not need an extention """
if file_name.endswith('.xls'):
file_name = file_name[:-4]
elif file_name.endswith('xlsx'):
file_name = file_name[:-5]
file_name = file_name.replace(
' ', '_') # Some browsers don't deal well with spaces in downloads
self.workbook = Workbook()
self.workbook.remove_sheet(self.workbook.get_active_sheet())
self.file_name = file_name
def add_sheet(self,
data,
title=None,
header_row=None,
heading=None,
auto_width=False,
max_auto_width=50):
""" Add a sheet with data to workbook
title: sheet name
header_row: List - Column header (bold with bottom border)
heading: Sheet heading (very top of sheet)
auto_width: will ESTIMATE the width of each column by counting
max chars in each column. It will not work with a formula.
max_auto_width: is the max number of characters a column to be
"""
sheet = self.workbook.create_sheet()
if title:
sheet.title = unicode(title)
if heading:
sheet.append([unicode(heading)])
if header_row:
header_row = map(unicode, header_row)
sheet.append(header_row)
row = sheet.get_highest_row()
for i, header_cell in enumerate(header_row):
cell = sheet.cell(row=row - 1, column=i)
cell.style.font.bold = True
cell.style.borders.bottom.border_style = openpyxl.style.Border.BORDER_THIN
for row in data:
row = map(unicode, row)
sheet.append(row)
if auto_width:
column_widths = []
for row in data:
row = map(unicode, row)
for i, cell in enumerate(row):
if len(column_widths) > i:
if len(cell) > column_widths[i]:
column_widths[i] = len(cell)
else:
column_widths += [len(cell)]
for i, column_width in enumerate(column_widths):
if column_width > 3:
if column_width < max_auto_width:
# * 0.9 estimates a typical variable width font
sheet.column_dimensions[get_column_letter(
i + 1)].width = column_width * 0.9
else:
sheet.column_dimensions[get_column_letter(
i + 1)].width = max_auto_width
def save(self, filename):
self.workbook.save(settings.MEDIA_ROOT + filename)
def as_download(self):
""" Returns a django HttpResponse with the xlsx file """
myfile = StringIO.StringIO()
myfile.write(save_virtual_workbook(self.workbook))
response = HttpResponse(
myfile.getvalue(),
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
)
response[
'Content-Disposition'] = 'attachment; filename=%s.xlsx' % self.file_name
response['Content-Length'] = myfile.tell()
return response
def test_get_active_sheet():
wb = Workbook()
active_sheet = wb.get_active_sheet()
assert active_sheet == wb.worksheets[0]
def test_get_active_sheet():
wb = Workbook()
active_sheet = wb.get_active_sheet()
eq_(active_sheet, wb.worksheets[0])
class ExcelGenerator(object):
def __init__(self, publication_extracts: List[PubExtract], language_pack, excel_config):
self.publication_extracts = publication_extracts
self.language_pack = language_pack
self.__CURRENT_ROW = 3
self.__LEFT_COLUMNS = ['B', 'C', 'D', 'E']
self.__RIGHT_COLUMNS = ['G', 'H', 'I', 'J']
self.__ACTIVE_COLUMNS = self.__LEFT_COLUMNS
self.excel_config = excel_config
# a new sheet is created for each publication so the program won't
# use the first sheet (it will be empty). Hence, it it removed
self.workbook = Workbook()
self.workbook.remove_sheet(worksheet=self.workbook.get_active_sheet())
def create_excel_doc(self):
if len(self.publication_extracts) == 0:
return
print('creating excel document...')
for publication_extract in self.publication_extracts:
print(f"preparing mwb({self.language_pack.lang_key}) {publication_extract.pub_month}/{publication_extract.pub_year}", end=' ')
self._add_populated_sheet(publication_extract)
print('[\033[92mOK\033[0m]')
file_name = 'WREX_{}_{}.xlsx'.format(datetime.now().strftime("%m-%d-%Y_%H:%M"), self.language_pack.lang_key)
self.workbook.save(file_name)
print('done...', f"saved to '{file_name}'")
def _add_populated_sheet(self, publication_extract: PubExtract):
sheet = self.workbook.create_sheet(publication_extract.pub_name)
meetings_count = 0
self._insert_sheet_title(
sheet,
self.language_pack.get_month_name(publication_extract.pub_month) + ' ' + publication_extract.pub_year)
for meeting in publication_extract.meetings:
self._insert_header_content(meeting.week_span, sheet)
self._insert_section(meeting.treasures_section, sheet)
self._insert_section(meeting.ministry_section, sheet)
self._insert_section(meeting.christian_section, sheet)
self._insert_footer_content(sheet)
meetings_count += 1
if meetings_count == 3:
self.__CURRENT_ROW = 5
self.__ACTIVE_COLUMNS = self.__RIGHT_COLUMNS
self._finalize_styling(sheet)
# reset indexes and make them ready for the next sheet
self.__CURRENT_ROW = 3
self.__ACTIVE_COLUMNS = self.__LEFT_COLUMNS
def _insert_sheet_title(self, sheet: Worksheet, month_name_and_year: str):
current_row = str(self.__CURRENT_ROW)
start_cell = self.__LEFT_COLUMNS[0] + str(current_row)
end_cell = self.__RIGHT_COLUMNS[3] + str(current_row)
sheet[start_cell] = self.language_pack.meeting_name + ' – ' + month_name_and_year
sheet.merge_cells(start_cell + ':' + end_cell)
self.__CURRENT_ROW += 2
self._style_sheet_title(sheet)
def _style_sheet_title(self, sheet: Worksheet):
cell = self.__ACTIVE_COLUMNS[0] + str(3)
sheet[cell].font = Font(bold=True, size=self.excel_config.SHEET_TITLE_FONT_SIZE)
sheet[cell].alignment = Alignment(horizontal='center', vertical='center')
def _insert_header_content(self, week_span: str, sheet: Worksheet):
# week span
week_span_row = self.__CURRENT_ROW
current_row = str(self.__CURRENT_ROW)
start_cell = self.__ACTIVE_COLUMNS[0] + current_row
end_cell = self.__ACTIVE_COLUMNS[2] + current_row
sheet[start_cell] = re.sub('[-|–]', ' – ', week_span)
sheet.merge_cells(start_cell + ':' + end_cell)
self.__CURRENT_ROW += 1
# chairman
current_row = str(self.__CURRENT_ROW)
start_cell = self.__ACTIVE_COLUMNS[0] + current_row
end_cell = self.__ACTIVE_COLUMNS[2] + current_row
sheet[start_cell] = self.language_pack.chairman
sheet.merge_cells(start_cell + ':' + end_cell)
self.__CURRENT_ROW += 1
# opening prayer
current_row = str(self.__CURRENT_ROW)
start_cell = self.__ACTIVE_COLUMNS[2] + current_row
sheet[start_cell] = self.language_pack.opening_prayer
self.__CURRENT_ROW += 1
self._style_header_content(week_span_row, sheet)
def _style_header_content(self, week_span_row: int, sheet: Worksheet):
alignment = Alignment(horizontal='left', vertical='center')
border = Border(bottom=Side(border_style='thin'))
# week span
cell = self.__ACTIVE_COLUMNS[0] + str(week_span_row)
sheet[cell].font = Font(bold=True, size=self.excel_config.LARGE_FONT_SIZE)
sheet[cell].alignment = alignment
# chairman
cell = self.__ACTIVE_COLUMNS[0] + str(week_span_row + 1)
sheet[cell].font = Font(bold=True, size=self.excel_config.LARGE_FONT_SIZE)
sheet[cell].alignment = alignment
sheet[cell].border = border
cell = self.__ACTIVE_COLUMNS[3] + str(week_span_row + 1) # last cell
sheet[cell].border = border
# opening prayer
cell = self.__ACTIVE_COLUMNS[2] + str(week_span_row + 2)
sheet[cell].font = Font(size=self.excel_config.SMALL_FONT_SIZE)
sheet[cell].alignment = alignment
cell = self.__ACTIVE_COLUMNS[3] + str(week_span_row + 2) # last cell
sheet[cell].border = border
def _insert_section_title(self, meeting_section: MeetingSection, sheet: Worksheet):
current_row = str(self.__CURRENT_ROW)
start_cell = self.__ACTIVE_COLUMNS[0] + current_row
sheet[start_cell] = meeting_section.title
# by default the right most cell at which merging cells ends is on the 5th column (including the offset)
end_cell = self.__ACTIVE_COLUMNS[3] + current_row
# but if the section is 'IMPROVE_IN_MINISTRY' and the user requests hall-dividing
# rows then it changes to the 3rd row
if (meeting_section.section_kind == SectionKind.IMPROVE_IN_MINISTRY) and \
self.excel_config.INSERT_HALL_DIVISION_LABELS:
end_cell = self.__ACTIVE_COLUMNS[1] + current_row
self._insert_hall_divider(sheet, meeting_section.section_kind)
sheet.merge_cells(start_cell + ':' + end_cell)
self._style_section_title(self.__CURRENT_ROW, sheet, meeting_section.section_kind)
self.__CURRENT_ROW += 1
def _style_section_title(self, title_row: int, sheet: Worksheet, section_kind: SectionKind):
if section_kind == SectionKind.TREASURES:
bg_color = self.excel_config.TREASURES_SECTION_TITLE_BG_COLOR
elif section_kind == SectionKind.IMPROVE_IN_MINISTRY:
bg_color = self.excel_config.MINISTRY_SECTION_TITLE_BG_COLOR
else:
bg_color = self.excel_config.CHRISTIAN_LIFE_SECTION_TITLE_BG_COLOR
cell = self.__ACTIVE_COLUMNS[0] + str(title_row)
sheet[cell].font = Font(bold=True, size=self.excel_config.LARGE_FONT_SIZE)
sheet[cell].alignment = Alignment(horizontal='left', vertical='center')
sheet[cell].fill = PatternFill(patternType='solid', fgColor=bg_color)
sheet[cell].border = Border(top=Side(border_style='thin'))
def _insert_hall_divider(self, sheet, section_kind: SectionKind):
current_row = str(self.__CURRENT_ROW)
main_hall_cell = self.__ACTIVE_COLUMNS[2] + current_row
second_hall_cell = self.__ACTIVE_COLUMNS[3] + current_row
sheet[main_hall_cell] = self.language_pack.main_hall
sheet[second_hall_cell] = self.language_pack.second_hall
self._style_hall_divider(self.__CURRENT_ROW, sheet, section_kind)
def _style_hall_divider(self, divider_row: int, sheet: Worksheet, section_kind: SectionKind):
main_hall_cell = self.__ACTIVE_COLUMNS[2] + str(divider_row)
second_hall_cell = self.__ACTIVE_COLUMNS[3] + str(divider_row)
font = Font(bold=False, size=self.excel_config.SMALL_FONT_SIZE)
alignment = Alignment(horizontal='center', vertical='center')
if section_kind == SectionKind.IMPROVE_IN_MINISTRY:
fg_color = self.excel_config.MINISTRY_SECTION_TITLE_BG_COLOR
else:
fg_color = self.excel_config.DEFAULT_BG_COLOR
fill = PatternFill(patternType='solid', fgColor=fg_color)
border = Border(top=Side(border_style='thin'))
sheet[main_hall_cell].font = font
sheet[main_hall_cell].alignment = alignment
sheet[main_hall_cell].fill = fill
sheet[main_hall_cell].border = border
sheet[second_hall_cell].font = font
sheet[second_hall_cell].alignment = alignment
sheet[second_hall_cell].fill = fill
sheet[second_hall_cell].border = border
def _insert_section(self, meeting_section: MeetingSection, sheet: Worksheet):
self._insert_section_title(meeting_section, sheet)
bible_reading = self.language_pack.bible_reading
for presentation in meeting_section.presentations:
if meeting_section.section_kind == SectionKind.TREASURES:
if (bible_reading in presentation) and self.excel_config.INSERT_HALL_DIVISION_LABELS:
self._insert_hall_divider(sheet, meeting_section.section_kind)
self.__CURRENT_ROW += 1
current_row = str(self.__CURRENT_ROW)
start_cell = self.__ACTIVE_COLUMNS[1] + current_row
if (meeting_section.section_kind == SectionKind.IMPROVE_IN_MINISTRY) and\
self.excel_config.INSERT_HALL_DIVISION_LABELS:
end_cell = self.__ACTIVE_COLUMNS[1] + current_row
else:
end_cell = self.__ACTIVE_COLUMNS[2] + current_row
sheet[start_cell] = presentation
if (bible_reading not in presentation) or \
(bible_reading in presentation and not self.excel_config.INSERT_HALL_DIVISION_LABELS):
sheet.merge_cells(start_cell + ':' + end_cell)
self._style_section(self.__CURRENT_ROW, sheet)
self.__CURRENT_ROW += 1
def _style_section(self, presentation_row: int, sheet: Worksheet):
cell = self.__ACTIVE_COLUMNS[1] + str(presentation_row)
border = Border(bottom=Side(border_style='thin'))
alignment = Alignment(horizontal='center', vertical='center')
sheet[cell].font = Font(size=self.excel_config.LARGE_FONT_SIZE)
sheet[cell].alignment = Alignment(horizontal='left', vertical='center')
sheet[cell].border = border
# underline and center the last two cells of a presentation row (that is where presenter names go)
cell = self.__ACTIVE_COLUMNS[2] + str(presentation_row)
sheet[cell].border = border
sheet[cell].alignment = alignment
cell = self.__ACTIVE_COLUMNS[3] + str(presentation_row)
sheet[cell].border = border
sheet[cell].alignment = alignment
def _insert_footer_content(self, sheet: Worksheet):
footer_row = self.__CURRENT_ROW
current_row = str(self.__CURRENT_ROW)
cell = self.__ACTIVE_COLUMNS[2] + current_row
sheet[cell] = self.language_pack.reader
self.__CURRENT_ROW += 1
current_row = str(self.__CURRENT_ROW)
cell = self.__ACTIVE_COLUMNS[2] + current_row
sheet[cell] = self.language_pack.concluding_prayer
self.__CURRENT_ROW += 3
self._style_footer_content(footer_row, sheet)
def _style_footer_content(self, footer_row: int, sheet: Worksheet):
font = Font(size=self.excel_config.SMALL_FONT_SIZE)
alignment = Alignment(horizontal='left', vertical='center')
border = Border(bottom=Side(border_style='thin'))
cell = self.__ACTIVE_COLUMNS[2] + str(footer_row)
sheet[cell].font = font
sheet[cell].alignment = alignment
sheet[cell].border = border
cell = self.__ACTIVE_COLUMNS[3] + str(footer_row)
sheet[cell].border = border
cell = self.__ACTIVE_COLUMNS[2] + str(footer_row + 1)
sheet[cell].font = font
sheet[cell].alignment = alignment
sheet[cell].border = border
cell = self.__ACTIVE_COLUMNS[3] + str(footer_row + 1)
sheet[cell].border = border
def _finalize_styling(self, sheet: Worksheet):
sheet.page_setup.paperSize = Worksheet.PAPERSIZE_A4
sheet.sheet_properties.pageSetUpPr.fitToPage = True
sheet.page_margins = PageMargins(left=self.excel_config.MARGIN_LENGTH, right=self.excel_config.MARGIN_LENGTH)
sheet.column_dimensions[self.__LEFT_COLUMNS[0]].width = 4
sheet.column_dimensions[self.__LEFT_COLUMNS[1]].width = 42
sheet.column_dimensions[self.__RIGHT_COLUMNS[0]].width = 4
sheet.column_dimensions[self.__RIGHT_COLUMNS[1]].width = 42
for rows in sheet.iter_rows(min_row=3):
for row in rows:
sheet.row_dimensions[row.row].height = self.excel_config.ROW_HEIGHT
