def __init__(self, fileobj=None, compressed=True):
"""
Create or open an archive
@param fileobj: the file object containing the archive,
None to create a new archive
@param compress: enable (or suppress) compression of new
archives
"""
import zipfile
if compressed:
compression = zipfile.ZIP_DEFLATED
else:
compression = zipfile.ZIP_STORED
if fileobj is not None:
if not hasattr(fileobj, "seek"):
# Possibly a addinfourl instance from urlopen,
# => must copy to BytesIO buffer for random access
fileobj = BytesIO(fileobj.read())
try:
archive = zipfile.ZipFile(fileobj, "r")
except RuntimeError:
current.log.warn("invalid ZIP archive: %s" % sys.exc_info()[1])
archive = None
else:
fileobj = BytesIO()
try:
archive = zipfile.ZipFile(fileobj, "w", compression, True)
except RuntimeError:
# Zlib not available? => try falling back to STORED
compression = zipfile.ZIP_STORED
archive = zipfile.ZipFile(fileobj, "w", compression, True)
current.log.warn(
"zlib not available - cannot compress archive")
self.fileobj = fileobj
self.archive = archive
def encode(self, resource, **attr):
"""
Export data as a Microsoft Excel spreadsheet
@param resource: the source of the data that is to be encoded
as a spreadsheet, can be either of:
1) an S3Resource
2) an array of value dicts (dict of
column labels as first item, list of
field types as second item)
3) a dict like:
{columns: [key, ...],
headers: {key: label},
types: {key: type},
rows: [{key:value}],
}
@param attr: keyword arguments (see below)
@keyword as_stream: return the buffer (BytesIO) rather than
its contents (str), useful when the output
is supposed to be stored locally
@keyword title: the main title of the report
@keyword list_fields: fields to include in list views
@keyword report_groupby: used to create a grouping of the result:
either a Field object of the resource
or a string which matches a value in
the heading
@keyword use_colour: True to add colour to the cells, default False
@keyword evenodd: render different background colours
for even/odd rows ("stripes")
"""
# Do not redirect from here!
# ...but raise proper status code, which can be caught by caller
try:
import xlwt
except ImportError:
error = self.ERROR.XLWT_ERROR
current.log.error(error)
raise HTTP(503, body=error)
try:
from xlrd.xldate import xldate_from_date_tuple, \
xldate_from_time_tuple, \
xldate_from_datetime_tuple
except ImportError:
error = self.ERROR.XLRD_ERROR
current.log.error(error)
raise HTTP(503, body=error)
import datetime
MAX_CELL_SIZE = self.MAX_CELL_SIZE
COL_WIDTH_MULTIPLIER = self.COL_WIDTH_MULTIPLIER
# Get the attributes
title = attr.get("title")
if title is None:
title = current.T("Report")
list_fields = attr.get("list_fields")
group = attr.get("dt_group")
use_colour = attr.get("use_colour", False)
evenodd = attr.get("evenodd", True)
# Extract the data from the resource
if isinstance(resource, dict):
headers = resource.get("headers", {})
lfields = resource.get("columns", list_fields)
column_types = resource.get("types")
types = [column_types[col] for col in lfields]
rows = resource.get("rows")
elif isinstance(resource, (list, tuple)):
headers = resource[0]
types = resource[1]
rows = resource[2:]
lfields = list_fields
else:
if not list_fields:
list_fields = resource.list_fields()
(title, types, lfields, headers, rows) = self.extract(resource,
list_fields,
)
# Verify columns in items
request = current.request
if len(rows) > 0 and len(lfields) > len(rows[0]):
msg = """modules/s3/codecs/xls: There is an error in the list items, a field doesn't exist
requesting url %s
Headers = %d, Data Items = %d
Headers %s
List Fields %s""" % (request.url, len(lfields), len(rows[0]), headers, lfields)
current.log.error(msg)
# Grouping
report_groupby = lfields[group] if group else None
groupby_label = headers[report_groupby] if report_groupby else None
# Date/Time formats from L10N deployment settings
settings = current.deployment_settings
date_format = settings.get_L10n_date_format()
date_format_str = str(date_format)
dt_format_translate = self.dt_format_translate
date_format = dt_format_translate(date_format)
time_format = dt_format_translate(settings.get_L10n_time_format())
datetime_format = dt_format_translate(settings.get_L10n_datetime_format())
title_row = settings.get_xls_title_row()
# Get styles
styles = self._styles(use_colour = use_colour,
evenodd = evenodd,
datetime_format = datetime_format,
)
# Create the workbook
book = xlwt.Workbook(encoding="utf-8")
# Add sheets
sheets = []
# XLS exports are limited to 65536 rows per sheet, we bypass
# this by creating multiple sheets
row_limit = 65536
sheetnum = len(rows) / row_limit
# Can't have a / in the sheet_name, so replace any with a space
sheet_name = s3_str(title.replace("/", " "))
if len(sheet_name) > 28:
# Sheet name cannot be over 31 chars
# (take sheet number suffix into account)
sheet_name = sheet_name[:28]
count = 1
while len(sheets) <= sheetnum:
sheets.append(book.add_sheet("%s-%s" % (sheet_name, count)))
count += 1
if callable(title_row):
# Calling with sheet None to get the number of title rows
title_row_length = title_row(None)
else:
title_row_length = 2
# Add header row to all sheets, determine columns widths
header_style = styles["header"]
for sheet in sheets:
# Move this down if a title row will be added
if title_row:
header_row = sheet.row(title_row_length)
else:
header_row = sheet.row(0)
column_widths = []
has_id = False
col_index = 0
for selector in lfields:
if selector == report_groupby:
continue
label = headers[selector]
if label == "Id":
# Indicate to adjust col_index when writing out
has_id = True
column_widths.append(0)
col_index += 1
continue
if label == "Sort":
continue
if has_id:
# Adjust for the skipped column
write_col_index = col_index - 1
else:
write_col_index = col_index
header_row.write(write_col_index, str(label), header_style)
width = max(len(label) * COL_WIDTH_MULTIPLIER, 2000)
width = min(width, 65535) # USHRT_MAX
column_widths.append(width)
sheet.col(write_col_index).width = width
col_index += 1
title = s3_str(title)
# Title row (optional, deployment setting)
if title_row:
T = current.T
large_header_style = styles["large_header"]
notes_style = styles["notes"]
for sheet in sheets:
if callable(title_row):
# Custom title rows
title_row(sheet)
else:
# First row => Title (standard = "title_list" CRUD string)
current_row = sheet.row(0)
if col_index > 0:
sheet.write_merge(0, 0, 0, col_index,
title,
large_header_style,
)
current_row.height = 500
# Second row => Export date/time
current_row = sheet.row(1)
current_row.write(0, "%s:" % T("Date Exported"), notes_style)
current_row.write(1, request.now, notes_style)
# Fix the size of the last column to display the date
if 16 * COL_WIDTH_MULTIPLIER > width:
sheet.col(col_index).width = 16 * COL_WIDTH_MULTIPLIER
# Initialize counters
total_cols = col_index
# Move the rows down if a title row is included
if title_row:
row_index = title_row_length
else:
row_index = 0
# Helper function to get the current row
def get_current_row(row_count, row_limit):
sheet_count = int(row_count / row_limit)
row_number = row_count - (sheet_count * row_limit)
if sheet_count > 0:
row_number += 1
return sheets[sheet_count], sheets[sheet_count].row(row_number)
# Write the table contents
subheading = None
odd_style = styles["odd"]
even_style = styles["even"]
subheader_style = styles["subheader"]
for row in rows:
# Current row
row_index += 1
current_sheet, current_row = get_current_row(row_index, row_limit)
style = even_style if row_index % 2 == 0 else odd_style
# Group headers
if report_groupby:
represent = s3_strip_markup(s3_unicode(row[report_groupby]))
if subheading != represent:
# Start of new group - write group header
subheading = represent
current_sheet.write_merge(row_index, row_index, 0, total_cols,
subheading,
subheader_style,
)
# Move on to next row
row_index += 1
current_sheet, current_row = get_current_row(row_index, row_limit)
style = even_style if row_index % 2 == 0 else odd_style
col_index = 0
remaining_fields = lfields
# Custom row style?
row_style = None
if "_style" in row:
stylename = row["_style"]
if stylename in styles:
row_style = styles[stylename]
# Group header/footer row?
if "_group" in row:
group_info = row["_group"]
label = group_info.get("label")
totals = group_info.get("totals")
if label:
label = s3_strip_markup(s3_unicode(label))
style = row_style or subheader_style
span = group_info.get("span")
if span == 0:
current_sheet.write_merge(row_index,
row_index,
0,
total_cols - 1,
label,
style,
)
if totals:
# Write totals into the next row
row_index += 1
current_sheet, current_row = \
get_current_row(row_index, row_limit)
else:
current_sheet.write_merge(row_index,
row_index,
0,
span - 1,
label,
style,
)
col_index = span
remaining_fields = lfields[span:]
if not totals:
continue
for field in remaining_fields:
label = headers[field]
if label == groupby_label:
continue
if label == "Id":
# Skip the ID column from XLS exports
col_index += 1
continue
if field not in row:
represent = ""
else:
represent = s3_strip_markup(s3_unicode(row[field]))
coltype = types[col_index]
if coltype == "sort":
continue
if len(represent) > MAX_CELL_SIZE:
represent = represent[:MAX_CELL_SIZE]
value = represent
if coltype == "date":
try:
cell_datetime = datetime.datetime.strptime(value,
date_format_str)
date_tuple = (cell_datetime.year,
cell_datetime.month,
cell_datetime.day)
value = xldate_from_date_tuple(date_tuple, 0)
style.num_format_str = date_format
except:
pass
elif coltype == "datetime":
try:
cell_datetime = datetime.datetime.strptime(value,
date_format_str)
date_tuple = (cell_datetime.year,
cell_datetime.month,
cell_datetime.day,
cell_datetime.hour,
cell_datetime.minute,
cell_datetime.second)
value = xldate_from_datetime_tuple(date_tuple, 0)
style.num_format_str = datetime_format
except:
pass
elif coltype == "time":
try:
cell_datetime = datetime.datetime.strptime(value,
date_format_str)
date_tuple = (cell_datetime.hour,
cell_datetime.minute,
cell_datetime.second)
value = xldate_from_time_tuple(date_tuple)
style.num_format_str = time_format
except:
pass
elif coltype == "integer":
try:
value = int(value)
style.num_format_str = "0"
except:
pass
elif coltype == "double":
try:
value = float(value)
style.num_format_str = "0.00"
except:
pass
if has_id:
# Adjust for the skipped column
write_col_index = col_index - 1
else:
write_col_index = col_index
current_row.write(write_col_index, value, style)
width = len(represent) * COL_WIDTH_MULTIPLIER
if width > column_widths[col_index]:
column_widths[col_index] = width
current_sheet.col(write_col_index).width = width
col_index += 1
# Additional sheet settings
for sheet in sheets:
sheet.panes_frozen = True
sheet.horz_split_pos = 1
# Write output
output = BytesIO()
book.save(output)
output.seek(0)
if attr.get("as_stream", False):
return output
# Response headers
filename = "%s_%s.xls" % (request.env.server_name, title)
disposition = "attachment; filename=\"%s\"" % filename
response = current.response
response.headers["Content-Type"] = contenttype(".xls")
response.headers["Content-disposition"] = disposition
return output.read()
def kit_export_pdf():
"""
Export a list of Kits in Adobe PDF format
Uses Geraldo SubReport
"""
try:
from reportlab.lib.units import cm
from reportlab.lib.pagesizes import A4
from reportlab.lib.enums import TA_CENTER, TA_RIGHT
except ImportError:
session.error = "Python needs the ReportLab module installed for PDF export"
redirect(URL(c="kit"))
try:
from geraldo import Report, ReportBand, SubReport, Label, ObjectValue, SystemField, landscape, BAND_WIDTH
from geraldo.generators import PDFGenerator
except ImportError:
session.error = "Python needs the Geraldo module installed for PDF export"
redirect(URL(c="kit"))
table = db.budget_kit
objects_list = db(table.id > 0).select()
if not objects_list:
session.warning = T("No data in this table - cannot create PDF!")
redirect(URL(r=request))
from s3compat import BytesIO
output = BytesIO()
#class MySubReport(SubReport):
# def __init__(self, db=None, **kwargs):
# " Initialise parent class & make any necessary modifications "
# self.db = db
# SubReport.__init__(self, **kwargs)
class MyReport(Report):
def __init__(self, queryset=None, db=None):
" Initialise parent class & make any necessary modifications "
Report.__init__(self, queryset)
self.db = db
# can't use T() here!
title = "Kits"
page_size = landscape(A4)
class band_page_header(ReportBand):
height = 1.3*cm
elements = [
SystemField(expression="%(report_title)s", top=0.1*cm,
left=0, width=BAND_WIDTH, style={"fontName": "Helvetica-Bold",
"fontSize": 14, "alignment": TA_CENTER}
),
Label(text="Code", top=0.8*cm, left=0.2*cm),
Label(text="Description", top=0.8*cm, left=2*cm),
Label(text="Cost", top=0.8*cm, left=10*cm),
Label(text="Monthly", top=0.8*cm, left=12*cm),
Label(text="per Minute", top=0.8*cm, left=14*cm),
Label(text="per Megabyte", top=0.8*cm, left=16*cm),
Label(text="Comments", top=0.8*cm, left=18*cm),
]
borders = {"bottom": True}
class band_page_footer(ReportBand):
height = 0.5*cm
elements = [
Label(text="%s" % request.utcnow.date(), top=0.1*cm, left=0),
SystemField(expression="Page # %(page_number)d of %(page_count)d", top=0.1*cm,
width=BAND_WIDTH, style={"alignment": TA_RIGHT}),
]
borders = {"top": True}
class band_detail(ReportBand):
height = 0.5*cm
auto_expand_height = True
elements = (
ObjectValue(attribute_name="code", left=0.2*cm, width=1.8*cm),
ObjectValue(attribute_name="description", left=2*cm, width=8*cm),
ObjectValue(attribute_name="total_unit_cost", left=10*cm, width=2*cm),
ObjectValue(attribute_name="total_monthly_cost", left=12*cm, width=2*cm),
ObjectValue(attribute_name="total_minute_cost", left=14*cm, width=2*cm),
ObjectValue(attribute_name="total_megabyte_cost", left=16*cm, width=2*cm),
ObjectValue(attribute_name="comments", left=18*cm, width=6*cm),
)
subreports = [
SubReport(
#queryset_string = "db((db.budget_kit_item.kit_id == %(object)s.id) & (db.budget_item.id == db.budget_kit_item.item_id)).select(db.budget_item.code, db.budget_item.description, db.budget_item.unit_cost)",
#queryset_string = "db(db.budget_kit_item.kit_id == %(object)s.id).select()",
band_header = ReportBand(
height=0.5*cm,
elements=[
Label(text="Item ID", top=0, left=0.2*cm, style={"fontName": "Helvetica-Bold"}),
Label(text="Quantity", top=0, left=2*cm, style={"fontName": "Helvetica-Bold"}),
#Label(text="Unit Cost", top=0, left=4*cm, style={"fontName": "Helvetica-Bold"}),
],
borders={"top": True, "left": True, "right": True},
),
detail_band = ReportBand(
height=0.5*cm,
elements=[
ObjectValue(attribute_name="item_id", top=0, left=0.2*cm),
ObjectValue(attribute_name="quantity", top=0, left=2*cm),
#ObjectValue(attribute_name="unit_cost", top=0, left=4*cm),
]
),
),
]
#report = MyReport(queryset=objects_list)
report = MyReport(queryset=objects_list, db=db)
report.generate_by(PDFGenerator, filename=output)
output.seek(0)
import gluon.contenttype
response.headers["Content-Type"] = gluon.contenttype.contenttype(".pdf")
filename = "%s_kits.pdf" % (request.env.server_name)
response.headers["Content-disposition"] = "attachment; filename=\"%s\"" % filename
return output.read()
def item_export_pdf():
"""
Export a list of Items in Adobe PDF format
Uses Geraldo Grouping Report
"""
try:
from reportlab.lib.units import cm
from reportlab.lib.pagesizes import A4
from reportlab.lib.enums import TA_CENTER, TA_RIGHT
except ImportError:
session.error = "Python needs the ReportLab module installed for PDF export"
redirect(URL(c="item"))
try:
from geraldo import Report, ReportBand, ReportGroup, Label, ObjectValue, SystemField, landscape, BAND_WIDTH
from geraldo.generators import PDFGenerator
except ImportError:
session.error = "Python needs the Geraldo module installed for PDF export"
redirect(URL(c="item"))
table = db.budget_item
objects_list = db(table.id > 0).select(orderby=table.category_type)
if not objects_list:
session.warning = T("No data in this table - cannot create PDF!")
redirect(URL(f="item"))
from s3compat import BytesIO
output = BytesIO()
class MyReport(Report):
def __init__(self, queryset=None, T=None):
" Initialise parent class & make any necessary modifications "
Report.__init__(self, queryset)
self.T = T
def _T(self, rawstring):
return self.T(rawstring)
# can't use T() here!
#title = _T("Items")
title = "Items"
page_size = landscape(A4)
class band_page_header(ReportBand):
height = 1.3*cm
elements = [
SystemField(expression="%(report_title)s", top=0.1*cm,
left=0, width=BAND_WIDTH, style={"fontName": "Helvetica-Bold",
"fontSize": 14, "alignment": TA_CENTER}
),
Label(text="Code", top=0.8*cm, left=0.2*cm),
Label(text="Description", top=0.8*cm, left=3*cm),
Label(text="Unit Cost", top=0.8*cm, left=13*cm),
Label(text="per Month", top=0.8*cm, left=15*cm),
Label(text="per Minute", top=0.8*cm, left=17*cm),
Label(text="per Megabyte", top=0.8*cm, left=19*cm),
Label(text="Comments", top=0.8*cm, left=21*cm),
]
borders = {"bottom": True}
class band_page_footer(ReportBand):
height = 0.5*cm
elements = [
Label(text="%s" % request.utcnow.date(), top=0.1*cm, left=0),
SystemField(expression="Page # %(page_number)d of %(page_count)d", top=0.1*cm,
width=BAND_WIDTH, style={"alignment": TA_RIGHT}),
]
borders = {"top": True}
class band_detail(ReportBand):
height = 0.5*cm
auto_expand_height = True
elements = (
ObjectValue(attribute_name="code", left=0.2*cm, width=2.8*cm),
ObjectValue(attribute_name="description", left=3*cm, width=10*cm),
ObjectValue(attribute_name="unit_cost", left=13*cm, width=2*cm),
ObjectValue(attribute_name="monthly_cost", left=15*cm, width=2*cm),
ObjectValue(attribute_name="minute_cost", left=17*cm, width=2*cm),
ObjectValue(attribute_name="megabyte_cost", left=19*cm, width=2*cm),
ObjectValue(attribute_name="comments", left=21*cm, width=6*cm),
)
groups = [
ReportGroup(attribute_name="category_type",
band_header=ReportBand(
height=0.7*cm,
elements=[
ObjectValue(attribute_name="category_type", left=0, top=0.1*cm,
get_value=lambda instance: instance.category_type and budget_category_type_opts[instance.category_type],
style={"fontName": "Helvetica-Bold", "fontSize": 12})
],
borders={"bottom": True},
),
),
]
#report = MyReport(queryset=objects_list)
report = MyReport(queryset=objects_list, T=T)
report.generate_by(PDFGenerator, filename=output)
output.seek(0)
import gluon.contenttype
response.headers["Content-Type"] = gluon.contenttype.contenttype(".pdf")
filename = "%s_items.pdf" % (request.env.server_name)
response.headers["Content-disposition"] = "attachment; filename=\"%s\"" % filename
return output.read()
def kit_export_xls():
"""
Export a list of Kits in Excel XLS format
Sheet 1 is a list of Kits
Then there is a separate sheet per kit, listing it's component items
"""
try:
import xlwt
except ImportError:
session.error = "xlwt module not available within the running Python - this needs installing for XLS output!"
redirect(URL(c="kit"))
from s3compat import BytesIO
output = BytesIO()
book = xlwt.Workbook()
# List of Kits
sheet1 = book.add_sheet("Kits")
# Header row for Kits sheet
row0 = sheet1.row(0)
cell = 0
table = db.budget_kit
kits = db(table.id > 0).select()
fields = [table[f] for f in table.fields if table[f].readable]
for field in fields:
row0.write(cell, field.label, xlwt.easyxf("font: bold True;"))
cell += 1
# For Header row on Items sheets
table = db.budget_item
fields_items = [table[f] for f in table.fields if table[f].readable]
row = 1
for kit in kits:
# The Kit details on Sheet 1
rowx = sheet1.row(row)
row += 1
cell1 = 0
for field in fields:
tab, col = str(field).split(".")
rowx.write(cell1, kit[col])
cell1 += 1
# Sheet per Kit detailing constituent Items
# Replace characters which are illegal in sheetnames
sheetname = kit.code.replace("/", "_")
sheet = book.add_sheet(sheetname)
# Header row for Items sheet
row0 = sheet.row(0)
cell = 0
for field_item in fields_items:
row0.write(cell, field_item.label, xlwt.easyxf("font: bold True;"))
cell += 1
# List Items in each Kit
table = db.budget_kit_item
contents = db(table.kit_id == kit.id).select()
rowy = 1
for content in contents:
table = db.budget_item
item = db(table.id == content.item_id).select().first()
rowx = sheet.row(rowy)
rowy += 1
cell = 0
for field_item in fields_items:
tab, col = str(field_item).split(".")
# Do lookups for option fields
if col == "cost_type":
opt = item[col]
value = str(budget_cost_type_opts[opt])
elif col == "category_type":
opt = item[col]
value = str(budget_category_type_opts[opt])
else:
value = item[col]
rowx.write(cell, value)
cell += 1
book.save(output)
output.seek(0)
import gluon.contenttype
response.headers["Content-Type"] = gluon.contenttype.contenttype(".xls")
filename = "%s_kits.xls" % (request.env.server_name)
response.headers["Content-disposition"] = "attachment; filename=\"%s\"" % filename
return output.read()