def test_xldate_from_datetime_tuple(self):
date = xldate.xldate_from_datetime_tuple( (1907, 7, 3, 6, 34, 0), DATEMODE)
self.assertAlmostEqual(date, 2741.273611, places=6)
date = xldate.xldate_from_datetime_tuple( (2005, 2, 23, 12, 56, 0), DATEMODE)
self.assertAlmostEqual(date, 38406.538889, places=6)
date = xldate.xldate_from_datetime_tuple( (1988, 5, 3, 17, 47, 13), DATEMODE)
self.assertAlmostEqual(date, 32266.741123, places=6)
def test_xldate_from_datetime_tuple():
date = xldate.xldate_from_datetime_tuple((1907, 7, 3, 6, 34, 0), DATEMODE)
assert date == pytest.approx(2741.273611)
date = xldate.xldate_from_datetime_tuple((2005, 2, 23, 12, 56, 0), DATEMODE)
assert date == pytest.approx(38406.538889)
date = xldate.xldate_from_datetime_tuple((1988, 5, 3, 17, 47, 13), DATEMODE)
assert date == pytest.approx(32266.741123)
def get_brfperiod(self):
"""
Get the period over which the BRF would be evaluated as a list of
two numerical Excel date values.
"""
year, month, day = self.date_start_edit.date().getDate()
hour = self.date_start_edit.time().hour()
minute = self.date_start_edit.time().minute()
dstart = xldate_from_datetime_tuple(
(year, month, day, hour, minute, 0), 0)
year, month, day = self.date_end_edit.date().getDate()
hour = self.date_end_edit.time().hour()
minute = self.date_end_edit.time().minute()
dend = xldate_from_datetime_tuple((year, month, day, hour, minute, 0),
0)
return [dstart, dend]
def get_brfperiod(self):
"""
Get the period over which the BRF would be evaluated as a list of
two numerical Excel date values.
"""
year, month, day = self.date_start_edit.date().getDate()
hour = self.date_start_edit.time().hour()
minute = self.date_start_edit.time().minute()
dstart = xldate_from_datetime_tuple(
(year, month, day, hour, minute, 0), 0)
year, month, day = self.date_end_edit.date().getDate()
hour = self.date_end_edit.time().hour()
minute = self.date_end_edit.time().minute()
dend = xldate_from_datetime_tuple(
(year, month, day, hour, minute, 0), 0)
return [dstart, dend]
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 xls2fix(s, settings, output_filename):
fixture_list = []
# 与えられたyamlの設定をしておく
for row in range(s.nrows):
rows = []
for col in range(s.ncols):
rows.append(s.cell(row, col).value)
if row <= settings.row:
continue
fields = {}
id = 0
for column, col in enumerate(rows):
# Excelのカラムがコンバート対象かチェックする
setting_column = settings.get_setting_column(row, column)
if setting_column:
# コンバート対象カラム
value = col
if setting_column.type == 'datetime':
if value != '':
value = str(
datetime.datetime(*xldate_as_tuple(
xldate_from_datetime_tuple(eval(value), 0),
0)))
else:
value = None
elif setting_column.type == 'char':
pass
elif setting_column.type == 'int':
try:
if col == u'':
value = 0
else:
value = int(col)
except ValueError, UnicodeEncodeError:
# 置換できなかった場合、import_dictの中に変換可能なカラムがあるかをチェック
print settings.import_dict, "****************"
print setting_column.name, "this column is"
if setting_column.name in settings.import_dict:
print setting_column.name, "this column is"
column_dict = settings.import_dict[
setting_column.name]
try:
value = column_dict[col]
except KeyError:
print >> sys.stderr, u'%s:%sはintでなくdictを使っても変換できない' % (
cellnameabs(row, column), col)
raise
else:
print >> sys.stderr, u'%s:%sはintに変換できない' % (
cellnameabs(column, row), col)
raise
value = 0
elif setting_column.type == 'float':
try:
try:
tmp = "%s.%sf" % ("%", len(str(col).split('.')[1]))
value = float(tmp % float(col))
except:
value = float(col)
except ValueError:
value = 0.0
elif setting_column.type == 'foreign_key':
if col == u'':
value = 0
else:
try:
value = int(col)
except ValueError:
# リレーション設定があるか？
if setting_column.has_relation():
value = setting_column.relation(col)
else:
print >> sys.stderr, u'%s:%sはリレーションIDに変換できない' % (
cellnameabs(row, column), col)
raise
if value == 0:
value = None
elif setting_column.type == 'boolean':
if len(unicode(col)) == 0:
value = False
else:
value = True
else:
print u'存在しないカラムタイプ[%s]を指定されている' % (setting_column.type)
raise
if setting_column.name == 'id':
try:
id = int(value)
except:
id = str(value)
else:
fields[setting_column.name] = value
# 未設定カラムを順番に処理する
for setting_column in settings.settings_none_exist_columns:
fields[setting_column.name] = str(setting_column.default)
fixture_list.append({
'model': settings.model,
'pk': id,
'fields': fields,
})
def encode(self, data_source, **attr):
"""
Export data as a Microsoft Excel spreadsheet
@param data_source: the source of the data that is to be encoded
as a spreadsheet. This may be:
resource: the resource
item: a list of pre-fetched values
the headings are in the first row
the data types are in the second row
@param attr: dictionary of parameters:
* title: The main title of the report
* list_fields: Fields to include in list views
* 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
* use_colour: True to add colour to the cells. default False
"""
try:
import xlwt
except ImportError:
from ..s3rest import S3Request
if current.auth.permission.format in S3Request.INTERACTIVE_FORMATS:
current.session.error = self.ERROR.XLWT_ERROR
redirect(URL(extension=""))
else:
error = self.ERROR.XLWT_ERROR
current.log.error(error)
return error
try:
from xlrd.xldate import xldate_from_date_tuple, \
xldate_from_time_tuple, \
xldate_from_datetime_tuple
except ImportError:
from ..s3rest import S3Request
if current.auth.permission.format in S3Request.INTERACTIVE_FORMATS:
current.session.error = self.ERROR.XLRD_ERROR
redirect(URL(extension=""))
else:
error = self.ERROR.XLRD_ERROR
current.log.error(error)
return error
import datetime
request = current.request
# The xlwt library supports a maximum of 182 characters in a single cell
max_cell_size = 182
COL_WIDTH_MULTIPLIER = S3XLS.COL_WIDTH_MULTIPLIER
# Get the attributes
title = attr.get("title")
list_fields = attr.get("list_fields")
if not list_fields:
list_fields = data_source.list_fields()
group = attr.get("dt_group")
use_colour = attr.get("use_colour", False)
# Extract the data from the data_source
if isinstance(data_source, (list, tuple)):
headers = data_source[0]
types = data_source[1]
rows = data_source[2:]
lfields = list_fields
else:
(title, types, lfields, headers,
rows) = self.extractResource(data_source, list_fields)
report_groupby = lfields[group] if group else None
if len(rows) > 0 and len(headers) != 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(headers), len(
items[0]), headers, list_fields)
current.log.error(msg)
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)
date_format = S3XLS.dt_format_translate(date_format)
time_format = S3XLS.dt_format_translate(
settings.get_L10n_time_format())
datetime_format = S3XLS.dt_format_translate(
settings.get_L10n_datetime_format())
# Create the workbook
book = xlwt.Workbook(encoding="utf-8")
# Add a sheet
# Can't have a / in the sheet_name, so replace any with a space
sheet_name = str(title.replace("/", " "))
# sheet_name cannot be over 31 chars
if len(sheet_name) > 31:
sheet_name = sheet_name[:31]
sheets = []
rowLimit = 65536 #.xls exports are limited to 65536 rows per sheet, we bypass this by creating multiple sheets
sheetnum = len(rows) / rowLimit
count = 1
while len(sheets) <= sheetnum:
sheets.append(book.add_sheet('%s-%s' % (sheet_name, count)))
count += 1
# Styles
styleLargeHeader = xlwt.XFStyle()
styleLargeHeader.font.bold = True
styleLargeHeader.font.height = 400
if use_colour:
styleLargeHeader.alignment.horz = styleLargeHeader.alignment.HORZ_CENTER
styleLargeHeader.pattern.pattern = styleLargeHeader.pattern.SOLID_PATTERN
styleLargeHeader.pattern.pattern_fore_colour = S3XLS.LARGE_HEADER_COLOUR
styleNotes = xlwt.XFStyle()
styleNotes.font.italic = True
styleNotes.font.height = 160 # 160 Twips = 8 point
styleNotes.num_format_str = datetime_format
styleHeader = xlwt.XFStyle()
styleHeader.font.bold = True
styleHeader.num_format_str = datetime_format
if use_colour:
styleHeader.pattern.pattern = styleHeader.pattern.SOLID_PATTERN
styleHeader.pattern.pattern_fore_colour = S3XLS.HEADER_COLOUR
styleSubHeader = xlwt.XFStyle()
styleSubHeader.font.bold = True
if use_colour:
styleSubHeader.pattern.pattern = styleHeader.pattern.SOLID_PATTERN
styleSubHeader.pattern.pattern_fore_colour = S3XLS.SUB_HEADER_COLOUR
styleOdd = xlwt.XFStyle()
if use_colour:
styleOdd.pattern.pattern = styleOdd.pattern.SOLID_PATTERN
styleOdd.pattern.pattern_fore_colour = S3XLS.ROW_ALTERNATING_COLOURS[
0]
styleEven = xlwt.XFStyle()
if use_colour:
styleEven.pattern.pattern = styleEven.pattern.SOLID_PATTERN
styleEven.pattern.pattern_fore_colour = S3XLS.ROW_ALTERNATING_COLOURS[
1]
for sheet in sheets:
# Header row
colCnt = 0
# Move this down if a title row will be added
if settings.get_xls_title_row():
headerRow = sheet.row(2)
else:
headerRow = sheet.row(0)
fieldWidths = []
id = False
for selector in lfields:
if selector == report_groupby:
continue
label = headers[selector]
if label == "Id":
# Indicate to adjust colCnt when writing out
id = True
fieldWidths.append(0)
colCnt += 1
continue
if label == "Sort":
continue
if id:
# Adjust for the skipped column
writeCol = colCnt - 1
else:
writeCol = colCnt
headerRow.write(writeCol, str(label), styleHeader)
width = max(len(label) * COL_WIDTH_MULTIPLIER, 2000)
width = min(width, 65535) # USHRT_MAX
fieldWidths.append(width)
sheet.col(writeCol).width = width
colCnt += 1
# Title row (optional, deployment setting)
if settings.get_xls_title_row():
for sheet in sheets:
# First row => Title (standard = "title_list" CRUD string)
currentRow = sheet.row(0)
if colCnt > 0:
sheet.write_merge(0, 0, 0, colCnt, str(title),
styleLargeHeader)
currentRow.height = 500
# Second row => Export date/time
currentRow = sheet.row(1)
currentRow.write(0, str(current.T("Date Exported:")),
styleNotes)
currentRow.write(1, request.now, styleNotes)
# Fix the size of the last column to display the date
if 16 * COL_WIDTH_MULTIPLIER > width:
sheet.col(colCnt).width = 16 * COL_WIDTH_MULTIPLIER
# Initialize counters
totalCols = colCnt
# Move the rows down if a title row is included
if settings.get_xls_title_row():
rowCnt = 2
else:
rowCnt = 0
subheading = None
for row in rows:
# Item details
rowCnt += 1
sheetCnt = (rowCnt / rowLimit)
if sheetCnt == 0:
currentRow = sheets[sheetCnt].row(rowCnt -
(sheetCnt * rowLimit))
else:
currentRow = sheets[sheetCnt].row(rowCnt -
(sheetCnt * rowLimit) + 1)
colCnt = 0
if rowCnt % 2 == 0:
style = styleEven
else:
style = styleOdd
if report_groupby:
represent = s3_strip_markup(s3_unicode(row[report_groupby]))
if subheading != represent:
subheading = represent
sheets[sheetCnt].write_merge(rowCnt, rowCnt, 0, totalCols,
subheading, styleSubHeader)
rowCnt += 1
currentRow = sheets[sheetCnt].row(rowCnt)
if rowCnt % 2 == 0:
style = styleEven
else:
style = styleOdd
for field in lfields:
label = headers[field]
if label == groupby_label:
continue
if label == "Id":
# Skip the ID column from XLS exports
colCnt += 1
continue
represent = s3_strip_markup(s3_unicode(row[field]))
coltype = types[colCnt]
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 id:
# Adjust for the skipped column
writeCol = colCnt - 1
else:
writeCol = colCnt
currentRow.write(writeCol, value, style)
width = len(represent) * COL_WIDTH_MULTIPLIER
if width > fieldWidths[colCnt]:
fieldWidths[colCnt] = width
sheets[sheetCnt].col(writeCol).width = width
colCnt += 1
for sheet in sheets:
sheet.panes_frozen = True
sheet.horz_split_pos = 1
output = StringIO()
book.save(output)
# Response headers
filename = "%s_%s.xls" % (request.env.server_name, str(title))
disposition = "attachment; filename=\"%s\"" % filename
response = current.response
response.headers["Content-Type"] = contenttype(".xls")
response.headers["Content-disposition"] = disposition
output.seek(0)
return output.read()
def encode(self, data_source, **attr):
"""
Export data as a Microsoft Excel spreadsheet
@param data_source: the source of the data that is to be encoded
as a spreadsheet. This may be:
resource: the resource
item: a list of pre-fetched values
the headings are in the first row
the data types are in the second row
@param attr: dictionary of parameters:
* title: The main title of the report
* list_fields: Fields to include in list views
* 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
* use_colour: True to add colour to the cells. default False
"""
import datetime
try:
import xlwt
except ImportError:
current.session.error = self.ERROR.XLWT_ERROR
redirect(URL(extension=""))
try:
from xlrd.xldate import xldate_from_date_tuple, \
xldate_from_time_tuple, \
xldate_from_datetime_tuple
except ImportError:
current.session.error = self.ERROR.XLRD_ERROR
redirect(URL(extension=""))
request = current.request
# The xlwt library supports a maximum of 182 characters in a single cell
max_cell_size = 182
COL_WIDTH_MULTIPLIER = S3XLS.COL_WIDTH_MULTIPLIER
# Get the attributes
title = attr.get("title")
list_fields = attr.get("list_fields")
group = attr.get("dt_group")
report_groupby = list_fields[group] if group else None
use_colour = attr.get("use_colour", False)
# Extract the data from the data_source
if isinstance(data_source, (list, tuple)):
headers = data_source[0]
types = data_source[1]
items = data_source[2:]
else:
(title, types, lfields, headers, items) = self.extractResource(data_source,
list_fields)
report_groupby = lfields[group] if group else None
if len(items) > 0 and len(headers) != len(items[0]):
from ..s3utils import s3_debug
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(headers), len(items[0]), headers, list_fields)
s3_debug(msg)
groupby_label = headers[report_groupby] if report_groupby else None
# Date/Time formats from L10N deployment settings
settings = current.deployment_settings
date_format = S3XLS.dt_format_translate(settings.get_L10n_date_format())
time_format = S3XLS.dt_format_translate(settings.get_L10n_time_format())
datetime_format = S3XLS.dt_format_translate(settings.get_L10n_datetime_format())
# Create the workbook
book = xlwt.Workbook(encoding="utf-8")
# Add a sheet
# Can't have a / in the sheet_name, so replace any with a space
sheet_name = str(title.replace("/", " "))
# sheet_name cannot be over 31 chars
if len(sheet_name) > 31:
sheet_name = sheet_name[:31]
sheet1 = book.add_sheet(sheet_name)
# Styles
styleLargeHeader = xlwt.XFStyle()
styleLargeHeader.font.bold = True
styleLargeHeader.font.height = 400
if use_colour:
styleLargeHeader.alignment.horz = styleLargeHeader.alignment.HORZ_CENTER
styleLargeHeader.pattern.pattern = styleLargeHeader.pattern.SOLID_PATTERN
styleLargeHeader.pattern.pattern_fore_colour = S3XLS.LARGE_HEADER_COLOUR
styleNotes = xlwt.XFStyle()
styleNotes.font.italic = True
styleNotes.font.height = 160 # 160 Twips = 8 point
styleNotes.num_format_str = datetime_format
styleHeader = xlwt.XFStyle()
styleHeader.font.bold = True
styleHeader.num_format_str = datetime_format
if use_colour:
styleHeader.pattern.pattern = styleHeader.pattern.SOLID_PATTERN
styleHeader.pattern.pattern_fore_colour = S3XLS.HEADER_COLOUR
styleSubHeader = xlwt.XFStyle()
styleSubHeader.font.bold = True
if use_colour:
styleSubHeader.pattern.pattern = styleHeader.pattern.SOLID_PATTERN
styleSubHeader.pattern.pattern_fore_colour = S3XLS.SUB_HEADER_COLOUR
styleOdd = xlwt.XFStyle()
if use_colour:
styleOdd.pattern.pattern = styleOdd.pattern.SOLID_PATTERN
styleOdd.pattern.pattern_fore_colour = S3XLS.ROW_ALTERNATING_COLOURS[0]
styleEven = xlwt.XFStyle()
if use_colour:
styleEven.pattern.pattern = styleEven.pattern.SOLID_PATTERN
styleEven.pattern.pattern_fore_colour = S3XLS.ROW_ALTERNATING_COLOURS[1]
# Header row
colCnt = -1
headerRow = sheet1.row(2)
fieldWidth = []
for selector in lfields:
if selector == report_groupby:
continue
label = headers[selector]
if label == "Id":
fieldWidth.append(0)
continue
if label == "Sort":
continue
colCnt += 1
headerRow.write(colCnt, str(label), styleHeader)
width = min(len(label) * COL_WIDTH_MULTIPLIER, 2000)
fieldWidth.append(width)
sheet1.col(colCnt).width = width
# Title row
currentRow = sheet1.row(0)
if colCnt > 0:
sheet1.write_merge(0, 0, 0, colCnt, str(title),
styleLargeHeader)
currentRow.height = 500
currentRow = sheet1.row(1)
currentRow.write(0, str(current.T("Date Exported:")), styleNotes)
currentRow.write(1, request.now, styleNotes)
# Fix the size of the last column to display the date
if 16 * COL_WIDTH_MULTIPLIER > width:
sheet1.col(colCnt).width = 16 * COL_WIDTH_MULTIPLIER
# Initialize counters
totalCols = colCnt
rowCnt = 2
colCnt = 0
subheading = None
for item in items:
# Item details
rowCnt += 1
currentRow = sheet1.row(rowCnt)
colCnt = 0
if rowCnt % 2 == 0:
style = styleEven
else:
style = styleOdd
if report_groupby:
represent = s3_strip_markup(s3_unicode(item[report_groupby]))
if subheading != represent:
subheading = represent
sheet1.write_merge(rowCnt, rowCnt, 0, totalCols,
subheading, styleSubHeader)
rowCnt += 1
currentRow = sheet1.row(rowCnt)
if rowCnt % 2 == 0:
style = styleEven
else:
style = styleOdd
for field in lfields:
label = headers[field]
if label == groupby_label:
continue
if label == "Id":
colCnt += 1
continue
represent = s3_strip_markup(s3_unicode(item[field]))
coltype = types[colCnt]
if coltype == "sort":
continue
if len(represent) > max_cell_size:
represent = represent[:max_cell_size]
value = represent
if coltype == "date":
try:
format = str(settings.get_L10n_date_format())
cell_datetime = datetime.datetime.strptime(value,
format)
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:
format = str(settings.get_L10n_date_format())
cell_datetime = datetime.datetime.strptime(value,
format)
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:
format = str(settings.get_L10n_date_format())
cell_datetime = datetime.datetime.strptime(value,
format)
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
currentRow.write(colCnt - 1, value, style)
width = len(represent) * COL_WIDTH_MULTIPLIER
if width > fieldWidth[colCnt]:
fieldWidth[colCnt] = width
sheet1.col(colCnt - 1).width = width
colCnt += 1
sheet1.panes_frozen = True
sheet1.horz_split_pos = 3
output = StringIO()
book.save(output)
# Response headers
filename = "%s_%s.xls" % (request.env.server_name, str(title))
disposition = "attachment; filename=\"%s\"" % filename
response = current.response
response.headers["Content-Type"] = contenttype(".xls")
response.headers["Content-disposition"] = disposition
output.seek(0)
return output.read()
def encode(self, data_source, title=None, as_stream=False, **attr):
"""
Export data as a Microsoft Excel spreadsheet
@param data_source: 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 title: the title for the output document
@param as_stream: return the buffer (StringIO) rather than
its contents (str), useful when the output
is supposed to be stored locally
@param attr: keyword parameters
@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 data_source
if isinstance(data_source, dict):
headers = data_source.get("headers", {})
lfields = data_source.get("columns", list_fields)
column_types = data_source.get("types")
types = [column_types[col] for col in lfields]
rows = data_source.get("rows")
elif isinstance(data_source, (list, tuple)):
headers = data_source[0]
types = data_source[1]
rows = data_source[2:]
lfields = list_fields
else:
if not list_fields:
list_fields = data_source.list_fields()
(title, types, lfields, headers, rows) = self.extract(data_source,
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 = str(title.replace("/", " "))
if len(sheet_name) > 31:
# Sheet name cannot be over 31 chars
# (take sheet number suffix into account)
sheet_name = sheet_name[:31] if sheetnum == 1 else 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
totalCols = 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, totalCols,
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,
totalCols - 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 = StringIO()
book.save(output)
output.seek(0)
if as_stream:
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 read_cweeds_file(filename, format_to_daily=True):
"""
Reads and formats data from a CWEEDS file, either version WY2 or WY3.
Returns a dictionary, which includes a numpy array of the global
solar irradiance in MJ/m², as well as corresponding arrays of the years,
months, days, and hours. By default, the hourly data from the CWEEDS file
are formated to daily values. The data are kept in a hourly format if
format_to_daily is set to False.
"""
# Determine if the CWEEDS file is in the WY2 or WY3 format :
root, ext = osp.splitext(filename)
ext = ext.replace('.', '')
if ext not in ['WY2', 'WY3']:
raise ValueError("%s is not a valid file extension. CWEEHDS files must"
" have either a WY2 or WY3 extension" % ext)
# Open and format the data from the CWEEDS file :
with open(filename, 'r') as f:
reader = list(csv.reader(f))
header_df = {}
if ext == 'WY3':
# We remove the header line from the data if the format is WY3.
header_list = reader.pop(0)
header_df['HORZ version'] = header_list[0]
header_df['Location'] = header_list[1]
header_df['Province'] = header_list[2]
header_df['Country'] = header_list[3]
header_df['Station ID'] = header_list[4]
header_df['Latitude'] = float(header_list[5])
header_df['Longitude'] = float(header_list[6])
header_df['Time Zone'] = float(header_list[7])
header_df['Elevation'] = float(header_list[8])
char_offset = 0 if ext == 'WY2' else 2
hourly_df = {}
hourly_df['Years'] = np.empty(len(reader)).astype(int)
hourly_df['Months'] = np.empty(len(reader)).astype(int)
hourly_df['Days'] = np.empty(len(reader)).astype(int)
hourly_df['Hours'] = np.empty(len(reader)).astype(int)
hourly_df['Time'] = np.empty(len(reader)).astype('float64')
# Global horizontal irradiance, kJ/m²
hourly_df['Irradiance'] = np.empty(len(reader)).astype('float64')
for i, line in enumerate(reader):
hourly_df['Years'][i] = year = int(line[0][char_offset:][6:10])
hourly_df['Months'][i] = month = int(line[0][char_offset:][10:12])
hourly_df['Days'][i] = day = int(line[0][char_offset:][12:14])
hourly_df['Hours'][i] = hour = int(line[0][char_offset:][14:16]) - 1
# The global horizontal irradiance is converted from kJ/m² to MJ/m².
hourly_df['Irradiance'][i] = float(line[0][char_offset:][20:24]) / 1000
# Compute time in Excel numeric format :
hourly_df['Time'][i] = xldate_from_datetime_tuple(
(year, month, day, hour, 0, 0), 0)
if format_to_daily:
# Convert the hourly data to daily format.
assert len(hourly_df['Irradiance']) % 24 == 0
new_shape = (len(hourly_df['Irradiance']) // 24, 24)
daily_df = {}
daily_df['Irradiance'] = np.sum(
hourly_df['Irradiance'].reshape(new_shape), axis=1)
for key in ['Years', 'Months', 'Days', 'Time']:
daily_df[key] = hourly_df[key].reshape(new_shape)[:, 0]
daily_df['Hours'] = np.zeros(len(daily_df['Irradiance']))
daily_df.update(header_df)
daily_df['Time Format'] = 'daily'
daily_df['CWEEDS Format'] = ext
return daily_df
else:
hourly_df.update(header_df)
hourly_df['Time Format'] = 'hourly'
hourly_df['CWEEDS Format'] = ext
return hourly_df
def encode(self, data_source, **attr):
"""
Export data as a Microsoft Excel spreadsheet
@param data_source: the source of the data that is to be encoded
as a spreadsheet. This may be:
resource: the resource
item: a list of pre-fetched values
the headings are in the first row
the data types are in the second row
@param attr: dictionary of parameters:
* title: The main title of the report
* list_fields: Fields to include in list views
* 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
* use_colour: True to add colour to the cells. default False
"""
import datetime
try:
import xlwt
except ImportError:
current.session.error = self.ERROR.XLWT_ERROR
redirect(URL(extension=""))
try:
from xlrd.xldate import xldate_from_date_tuple, \
xldate_from_time_tuple, \
xldate_from_datetime_tuple
except ImportError:
current.session.error = self.ERROR.XLRD_ERROR
redirect(URL(extension=""))
# Environment
request = current.request
# The xlwt library supports a maximum of 182 character in a single cell
max_cell_size = 182
# Get the attributes
title = attr.get("title")
list_fields = attr.get("list_fields")
report_groupby = attr.get("report_groupby")
use_colour = attr.get("use_colour", False)
# Extract the data from the data_source
if isinstance(data_source, (list, tuple)):
headers = data_source[0]
types = data_source[1]
items = data_source[2:]
else:
(title, types, headers,
items) = self.extractResource(data_source, list_fields,
report_groupby)
if len(items) > 0 and len(headers) != len(items[0]):
from ..s3utils import s3_debug
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(headers), len(
items[0]), headers, list_fields)
s3_debug(msg)
if report_groupby != None:
if isinstance(report_groupby, Field):
groupby_label = report_groupby.label
else:
groupby_label = report_groupby
# Date/Time formats from L10N deployment settings
settings = current.deployment_settings
date_format = S3XLS.dt_format_translate(
settings.get_L10n_date_format())
time_format = S3XLS.dt_format_translate(
settings.get_L10n_time_format())
datetime_format = S3XLS.dt_format_translate(
settings.get_L10n_datetime_format())
# Initialize output
output = StringIO()
# Create the workbook and a sheet in it
book = xlwt.Workbook(encoding="utf-8")
# The spreadsheet doesn't like a / in the sheet name, so replace any with a space
sheet1 = book.add_sheet(str(title.replace("/", " ")))
# Styles
styleLargeHeader = xlwt.XFStyle()
styleLargeHeader.font.bold = True
styleLargeHeader.font.height = 400
if use_colour:
styleLargeHeader.alignment.horz = styleLargeHeader.alignment.HORZ_CENTER
styleLargeHeader.pattern.pattern = styleLargeHeader.pattern.SOLID_PATTERN
styleLargeHeader.pattern.pattern_fore_colour = S3XLS.LARGE_HEADER_COLOUR
styleNotes = xlwt.XFStyle()
styleNotes.font.italic = True
styleNotes.font.height = 160 # 160 Twips = 8point
styleNotes.num_format_str = datetime_format
styleHeader = xlwt.XFStyle()
styleHeader.font.bold = True
styleHeader.num_format_str = datetime_format
if use_colour:
styleHeader.pattern.pattern = styleHeader.pattern.SOLID_PATTERN
styleHeader.pattern.pattern_fore_colour = S3XLS.HEADER_COLOUR
styleSubHeader = xlwt.XFStyle()
styleSubHeader.font.bold = True
if use_colour:
styleSubHeader.pattern.pattern = styleHeader.pattern.SOLID_PATTERN
styleSubHeader.pattern.pattern_fore_colour = S3XLS.SUB_HEADER_COLOUR
styleOdd = xlwt.XFStyle()
if use_colour:
styleOdd.pattern.pattern = styleOdd.pattern.SOLID_PATTERN
styleOdd.pattern.pattern_fore_colour = S3XLS.ROW_ALTERNATING_COLOURS[
0]
styleEven = xlwt.XFStyle()
if use_colour:
styleEven.pattern.pattern = styleEven.pattern.SOLID_PATTERN
styleEven.pattern.pattern_fore_colour = S3XLS.ROW_ALTERNATING_COLOURS[
1]
# Header row
colCnt = -1
headerRow = sheet1.row(2)
fieldWidth = []
for label in headers:
if label == "Sort":
continue
if report_groupby != None:
if label == groupby_label:
continue
colCnt += 1
headerRow.write(colCnt, str(label), styleHeader)
width = len(label) * S3XLS.COL_WIDTH_MULTIPLIER
fieldWidth.append(width)
sheet1.col(colCnt).width = width
# Title row
currentRow = sheet1.row(0)
if colCnt > 0:
sheet1.write_merge(0, 0, 0, colCnt, str(title), styleLargeHeader)
currentRow = sheet1.row(1)
currentRow.height = 440
currentRow.write(colCnt, request.now, styleNotes)
# fix the size of the last column to display the date
if 16 * S3XLS.COL_WIDTH_MULTIPLIER > width:
sheet1.col(colCnt).width = 16 * S3XLS.COL_WIDTH_MULTIPLIER
# Initialize counters
totalCols = colCnt
rowCnt = 3
colCnt = 0
subheading = None
for item in items:
# Item details
rowCnt += 1
currentRow = sheet1.row(rowCnt)
colCnt = 0
if rowCnt % 2 == 0:
style = styleEven
else:
style = styleOdd
for represent in item:
coltype = types[colCnt]
if coltype == "sort":
continue
label = headers[colCnt]
if type(represent) is not str:
represent = unicode(represent)
if len(represent) > max_cell_size:
represent = represent[:max_cell_size]
# Strip away markup from representation
try:
markup = etree.XML(str(represent))
text = markup.xpath(".//text()")
if text:
text = " ".join(text)
else:
text = ""
represent = text
except:
pass
if report_groupby != None:
if label == groupby_label:
if subheading != represent:
subheading = represent
sheet1.write_merge(rowCnt, rowCnt, 0, totalCols,
represent, styleSubHeader)
rowCnt += 1
currentRow = sheet1.row(rowCnt)
if rowCnt % 2 == 0:
style = styleEven
else:
style = styleOdd
continue
value = represent
if coltype == "date":
try:
format = str(settings.get_L10n_date_format())
cell_datetime = datetime.datetime.strptime(
value, format)
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:
format = str(settings.get_L10n_date_format())
cell_datetime = datetime.datetime.strptime(
value, format)
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:
format = str(settings.get_L10n_date_format())
cell_datetime = datetime.datetime.strptime(
value, format)
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
currentRow.write(colCnt, value, style)
width = len(represent) * S3XLS.COL_WIDTH_MULTIPLIER
if width > fieldWidth[colCnt]:
fieldWidth[colCnt] = width
sheet1.col(colCnt).width = width
colCnt += 1
sheet1.panes_frozen = True
sheet1.horz_split_pos = 3
book.save(output)
# Response headers
filename = "%s_%s.xls" % (request.env.server_name, str(title))
disposition = "attachment; filename=\"%s\"" % filename
response = current.response
response.headers["Content-Type"] = contenttype(".xls")
response.headers["Content-disposition"] = disposition
output.seek(0)
return output.read()
def encode(self, data_source, **attr):
"""
Export data as a Microsoft Excel spreadsheet
@param data_source: the source of the data that is to be encoded
as a spreadsheet. This may be:
resource: the resource
item: a list of pre-fetched values
the headings are in the first row
the data types are in the second row
@param attr: dictionary of parameters:
* title: The main title of the report
* list_fields: Fields to include in list views
* 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
* use_colour: True to add colour to the cells. default False
"""
try:
import xlwt
except ImportError:
from ..s3rest import S3Request
if current.auth.permission.format in S3Request.INTERACTIVE_FORMATS:
current.session.error = self.ERROR.XLWT_ERROR
redirect(URL(extension=""))
else:
error = self.ERROR.XLWT_ERROR
current.log.error(error)
return error
try:
from xlrd.xldate import xldate_from_date_tuple, \
xldate_from_time_tuple, \
xldate_from_datetime_tuple
except ImportError:
from ..s3rest import S3Request
if current.auth.permission.format in S3Request.INTERACTIVE_FORMATS:
current.session.error = self.ERROR.XLRD_ERROR
redirect(URL(extension=""))
else:
error = self.ERROR.XLRD_ERROR
current.log.error(error)
return error
import datetime
request = current.request
# The xlwt library supports a maximum of 182 characters in a single cell
max_cell_size = 182
COL_WIDTH_MULTIPLIER = S3XLS.COL_WIDTH_MULTIPLIER
# Get the attributes
title = attr.get("title")
list_fields = attr.get("list_fields")
if not list_fields:
list_fields = data_source.list_fields()
group = attr.get("dt_group")
use_colour = attr.get("use_colour", False)
# Extract the data from the data_source
if isinstance(data_source, (list, tuple)):
headers = data_source[0]
types = data_source[1]
rows = data_source[2:]
lfields = list_fields
else:
(title, types, lfields, headers, rows) = self.extractResource(data_source,
list_fields)
report_groupby = lfields[group] if group else None
if len(rows) > 0 and len(headers) != 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(headers), len(items[0]), headers, list_fields)
current.log.error(msg)
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)
date_format = S3XLS.dt_format_translate(date_format)
time_format = S3XLS.dt_format_translate(settings.get_L10n_time_format())
datetime_format = S3XLS.dt_format_translate(settings.get_L10n_datetime_format())
# Create the workbook
book = xlwt.Workbook(encoding="utf-8")
# Add a sheet
# Can't have a / in the sheet_name, so replace any with a space
sheet_name = str(title.replace("/", " "))
# sheet_name cannot be over 31 chars
if len(sheet_name) > 31:
sheet_name = sheet_name[:31]
sheets = []
rowLimit = 65536 #.xls exports are limited to 65536 rows per sheet, we bypass this by creating multiple sheets
sheetnum = len(rows) / rowLimit
count = 1
while len(sheets) <= sheetnum:
sheets.append(book.add_sheet('%s-%s' % (sheet_name, count)))
count += 1
# Styles
styleLargeHeader = xlwt.XFStyle()
styleLargeHeader.font.bold = True
styleLargeHeader.font.height = 400
if use_colour:
styleLargeHeader.alignment.horz = styleLargeHeader.alignment.HORZ_CENTER
styleLargeHeader.pattern.pattern = styleLargeHeader.pattern.SOLID_PATTERN
styleLargeHeader.pattern.pattern_fore_colour = S3XLS.LARGE_HEADER_COLOUR
styleNotes = xlwt.XFStyle()
styleNotes.font.italic = True
styleNotes.font.height = 160 # 160 Twips = 8 point
styleNotes.num_format_str = datetime_format
styleHeader = xlwt.XFStyle()
styleHeader.font.bold = True
styleHeader.num_format_str = datetime_format
if use_colour:
styleHeader.pattern.pattern = styleHeader.pattern.SOLID_PATTERN
styleHeader.pattern.pattern_fore_colour = S3XLS.HEADER_COLOUR
styleSubHeader = xlwt.XFStyle()
styleSubHeader.font.bold = True
if use_colour:
styleSubHeader.pattern.pattern = styleHeader.pattern.SOLID_PATTERN
styleSubHeader.pattern.pattern_fore_colour = S3XLS.SUB_HEADER_COLOUR
styleOdd = xlwt.XFStyle()
if use_colour:
styleOdd.pattern.pattern = styleOdd.pattern.SOLID_PATTERN
styleOdd.pattern.pattern_fore_colour = S3XLS.ROW_ALTERNATING_COLOURS[0]
styleEven = xlwt.XFStyle()
if use_colour:
styleEven.pattern.pattern = styleEven.pattern.SOLID_PATTERN
styleEven.pattern.pattern_fore_colour = S3XLS.ROW_ALTERNATING_COLOURS[1]
for sheet in sheets:
# Header row
colCnt = 0
# Move this down if a title row will be added
if settings.get_xls_title_row():
headerRow = sheet.row(2)
else:
headerRow = sheet.row(0)
fieldWidths = []
id = False
for selector in lfields:
if selector == report_groupby:
continue
label = headers[selector]
if label == "Id":
# Indicate to adjust colCnt when writing out
id = True
fieldWidths.append(0)
colCnt += 1
continue
if label == "Sort":
continue
if id:
# Adjust for the skipped column
writeCol = colCnt - 1
else:
writeCol = colCnt
headerRow.write(writeCol, str(label), styleHeader)
width = max(len(label) * COL_WIDTH_MULTIPLIER, 2000)
width = min(width, 65535) # USHRT_MAX
fieldWidths.append(width)
sheet.col(writeCol).width = width
colCnt += 1
# Title row (optional, deployment setting)
if settings.get_xls_title_row():
for sheet in sheets:
# First row => Title (standard = "title_list" CRUD string)
currentRow = sheet.row(0)
if colCnt > 0:
sheet.write_merge(0, 0, 0, colCnt, str(title),
styleLargeHeader)
currentRow.height = 500
# Second row => Export date/time
currentRow = sheet.row(1)
currentRow.write(0, str(current.T("Date Exported:")), styleNotes)
currentRow.write(1, request.now, styleNotes)
# Fix the size of the last column to display the date
if 16 * COL_WIDTH_MULTIPLIER > width:
sheet.col(colCnt).width = 16 * COL_WIDTH_MULTIPLIER
# Initialize counters
totalCols = colCnt
# Move the rows down if a title row is included
if settings.get_xls_title_row():
rowCnt = 2
else:
rowCnt = 0
subheading = None
for row in rows:
# Item details
rowCnt += 1
sheetCnt = (rowCnt / rowLimit)
if sheetCnt == 0:
currentRow = sheets[sheetCnt].row(rowCnt - (sheetCnt * rowLimit))
else:
currentRow = sheets[sheetCnt].row(rowCnt - (sheetCnt * rowLimit) + 1)
colCnt = 0
if rowCnt % 2 == 0:
style = styleEven
else:
style = styleOdd
if report_groupby:
represent = s3_strip_markup(s3_unicode(row[report_groupby]))
if subheading != represent:
subheading = represent
sheets[sheetCnt].write_merge(rowCnt, rowCnt, 0, totalCols,
subheading, styleSubHeader)
rowCnt += 1
currentRow = sheets[sheetCnt].row(rowCnt)
if rowCnt % 2 == 0:
style = styleEven
else:
style = styleOdd
for field in lfields:
label = headers[field]
if label == groupby_label:
continue
if label == "Id":
# Skip the ID column from XLS exports
colCnt += 1
continue
represent = s3_strip_markup(s3_unicode(row[field]))
coltype = types[colCnt]
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 id:
# Adjust for the skipped column
writeCol = colCnt - 1
else:
writeCol = colCnt
currentRow.write(writeCol, value, style)
width = len(represent) * COL_WIDTH_MULTIPLIER
if width > fieldWidths[colCnt]:
fieldWidths[colCnt] = width
sheets[sheetCnt].col(writeCol).width = width
colCnt += 1
for sheet in sheets:
sheet.panes_frozen = True
sheet.horz_split_pos = 1
output = StringIO()
book.save(output)
# Response headers
filename = "%s_%s.xls" % (request.env.server_name, str(title))
disposition = "attachment; filename=\"%s\"" % filename
response = current.response
response.headers["Content-Type"] = contenttype(".xls")
response.headers["Content-disposition"] = disposition
output.seek(0)
return output.read()
def encode(self, data_source, **attr):
"""
Export data as a Microsoft Excel spreadsheet
@param data_source: the source of the data that is to be encoded
as a spreadsheet. This may be:
resource: the resource
item: a list of pre-fetched values
the headings are in the first row
the data types are in the second row
@param attr: dictionary of parameters:
* title: The main title of the report
* list_fields: Fields to include in list views
* 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
* use_colour: True to add colour to the cells. default False
"""
import datetime
try:
import xlwt
except ImportError:
current.session.error = self.ERROR.XLWT_ERROR
redirect(URL(extension=""))
try:
from xlrd.xldate import xldate_from_date_tuple, \
xldate_from_time_tuple, \
xldate_from_datetime_tuple
except ImportError:
current.session.error = self.ERROR.XLRD_ERROR
redirect(URL(extension=""))
# The xlwt library supports a maximum of 182 character in a single cell
max_cell_size = 182
# Get the attributes
title = attr.get("title")
list_fields = attr.get("list_fields")
report_groupby = attr.get("report_groupby")
use_colour = attr.get("use_colour", False)
# Extract the data from the data_source
if isinstance(data_source, (list, tuple)):
headers = data_source[0]
types = data_source[1]
items = data_source[2:]
else:
(title, types, headers, items) = self.extractResource(data_source,
list_fields,
report_groupby)
if len(headers) != len(items):
import sys
print >> sys.stderr, "modules/s3/codecs/xls: There is an error in the list_items, a field doesn't exist"
print >> sys.stderr, list_fields
if report_groupby != None:
if isinstance(report_groupby, Field):
groupby_label = report_groupby.label
else:
groupby_label = report_groupby
# Date/Time formats from L10N deployment settings
settings = current.deployment_settings
date_format = S3XLS.dt_format_translate(settings.get_L10n_date_format())
time_format = S3XLS.dt_format_translate(settings.get_L10n_time_format())
datetime_format = S3XLS.dt_format_translate(settings.get_L10n_datetime_format())
# Initialize output
output = StringIO()
# Create the workbook and a sheet in it
book = xlwt.Workbook(encoding="utf-8")
# The spreadsheet doesn't like a / in the sheet name, so replace any with a space
sheet1 = book.add_sheet(str(title.replace("/"," ")))
# Styles
styleLargeHeader = xlwt.XFStyle()
styleLargeHeader.font.bold = True
styleLargeHeader.font.height = 400
if use_colour:
styleLargeHeader.alignment.horz = styleLargeHeader.alignment.HORZ_CENTER
styleLargeHeader.pattern.pattern = styleLargeHeader.pattern.SOLID_PATTERN
styleLargeHeader.pattern.pattern_fore_colour = S3XLS.LARGE_HEADER_COLOUR
styleNotes = xlwt.XFStyle()
styleNotes.font.italic = True
styleNotes.font.height = 160 # 160 Twips = 8point
styleNotes.num_format_str = datetime_format
styleHeader = xlwt.XFStyle()
styleHeader.font.bold = True
styleHeader.num_format_str = datetime_format
if use_colour:
styleHeader.pattern.pattern = styleHeader.pattern.SOLID_PATTERN
styleHeader.pattern.pattern_fore_colour = S3XLS.HEADER_COLOUR
styleSubHeader = xlwt.XFStyle()
styleSubHeader.font.bold = True
if use_colour:
styleSubHeader.pattern.pattern = styleHeader.pattern.SOLID_PATTERN
styleSubHeader.pattern.pattern_fore_colour = S3XLS.SUB_HEADER_COLOUR
styleOdd = xlwt.XFStyle()
if use_colour:
styleOdd.pattern.pattern = styleOdd.pattern.SOLID_PATTERN
styleOdd.pattern.pattern_fore_colour = S3XLS.ROW_ALTERNATING_COLOURS[0]
styleEven = xlwt.XFStyle()
if use_colour:
styleEven.pattern.pattern = styleEven.pattern.SOLID_PATTERN
styleEven.pattern.pattern_fore_colour = S3XLS.ROW_ALTERNATING_COLOURS[1]
# Initialize counters
rowCnt = 0
colCnt = 0
# Environment
request = current.request
# Title row
totalCols = len(headers)-1
if report_groupby != None:
totalCols -= 1
if totalCols > 0:
sheet1.write_merge(rowCnt, rowCnt, 0, totalCols, str(title),
styleLargeHeader)
currentRow = sheet1.row(rowCnt)
currentRow.height = 440
rowCnt += 1
currentRow = sheet1.row(rowCnt)
currentRow.write(totalCols, request.now, styleNotes)
rowCnt += 1
currentRow = sheet1.row(rowCnt)
# Header row
fieldWidth=[]
for label in headers:
if report_groupby != None:
if label == groupby_label:
continue
currentRow.write(colCnt, str(label), styleHeader)
width = len(label) * S3XLS.COL_WIDTH_MULTIPLIER
fieldWidth.append(width)
sheet1.col(colCnt).width = width
colCnt += 1
# fix the size of the last column to display the date
if 16 * S3XLS.COL_WIDTH_MULTIPLIER > width:
sheet1.col(totalCols).width = 16 * S3XLS.COL_WIDTH_MULTIPLIER
subheading = None
for item in items:
# Item details
rowCnt += 1
currentRow = sheet1.row(rowCnt)
colCnt = 0
if rowCnt % 2 == 0:
style = styleEven
else:
style = styleOdd
for represent in item:
label = headers[colCnt]
if type(represent) is not str:
represent = unicode(represent)
if len(represent) > max_cell_size:
represent = represent[:max_cell_size]
# Strip away markup from representation
try:
markup = etree.XML(str(represent))
text = markup.xpath(".//text()")
if text:
text = " ".join(text)
else:
text = ""
represent = text
except:
pass
if report_groupby != None:
if label == groupby_label:
if subheading != represent:
subheading = represent
sheet1.write_merge(rowCnt, rowCnt, 0, totalCols,
represent, styleSubHeader)
rowCnt += 1
currentRow = sheet1.row(rowCnt)
if rowCnt % 2 == 0:
style = styleEven
else:
style = styleOdd
continue
coltype=types[colCnt]
value = represent
if coltype == "date":
try:
format = str(settings.get_L10n_date_format())
cell_datetime = datetime.datetime.strptime(value,
format)
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:
format = str(settings.get_L10n_date_format())
cell_datetime = datetime.datetime.strptime(value,
format)
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:
format = str(settings.get_L10n_date_format())
cell_datetime = datetime.datetime.strptime(value,
format)
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
currentRow.write(colCnt, value, style)
width = len(represent) * S3XLS.COL_WIDTH_MULTIPLIER
if width > fieldWidth[colCnt]:
fieldWidth[colCnt] = width
sheet1.col(colCnt).width = width
colCnt += 1
sheet1.panes_frozen = True
sheet1.horz_split_pos = 3
book.save(output)
# Response headers
filename = "%s_%s.xls" % (request.env.server_name, str(title))
disposition = "attachment; filename=\"%s\"" % filename
response = current.response
response.headers["Content-Type"] = contenttype(".xls")
response.headers["Content-disposition"] = disposition
output.seek(0)
return output.read()
def read_cweeds_file(filename, format_to_daily=True):
"""
Reads and formats data from a CWEEDS file, either version WY2 or WY3.
Returns a dictionary, which includes a numpy array of the global
solar irradiance in MJ/m², as well as corresponding arrays of the years,
months, days, and hours. By default, the hourly data from the CWEEDS file
are formated to daily values. The data are kept in a hourly format if
format_to_daily is set to False.
"""
# Determine if the CWEEDS file is in the WY2 or WY3 format :
root, ext = osp.splitext(filename)
ext = ext.replace('.', '')
if ext not in ['WY2', 'WY3']:
raise ValueError("%s is not a valid file extension. CWEEHDS files must"
" have either a WY2 or WY3 extension" % ext)
# Open and format the data from the CWEEDS file :
with open(filename, 'r') as f:
reader = list(csv.reader(f))
header_df = {}
if ext == 'WY3':
# We remove the header line from the data if the format is WY3.
header_list = reader.pop(0)
header_df['HORZ version'] = header_list[0]
header_df['Location'] = header_list[1]
header_df['Province'] = header_list[2]
header_df['Country'] = header_list[3]
header_df['Station ID'] = header_list[4]
header_df['Latitude'] = float(header_list[5])
header_df['Longitude'] = float(header_list[6])
header_df['Time Zone'] = float(header_list[7])
header_df['Elevation'] = float(header_list[8])
char_offset = 0 if ext == 'WY2' else 2
hourly_df = {}
hourly_df['Years'] = np.empty(len(reader)).astype(int)
hourly_df['Months'] = np.empty(len(reader)).astype(int)
hourly_df['Days'] = np.empty(len(reader)).astype(int)
hourly_df['Hours'] = np.empty(len(reader)).astype(int)
hourly_df['Time'] = np.empty(len(reader)).astype('float64')
# Global horizontal irradiance, kJ/m²
hourly_df['Irradiance'] = np.empty(len(reader)).astype('float64')
for i, line in enumerate(reader):
hourly_df['Years'][i] = year = int(line[0][char_offset:][6:10])
hourly_df['Months'][i] = month = int(line[0][char_offset:][10:12])
hourly_df['Days'][i] = day = int(line[0][char_offset:][12:14])
hourly_df['Hours'][i] = hour = int(line[0][char_offset:][14:16]) - 1
# The global horizontal irradiance is converted from kJ/m² to MJ/m².
hourly_df['Irradiance'][i] = float(line[0][char_offset:][20:24])/1000
# Compute time in Excel numeric format :
hourly_df['Time'][i] = xldate_from_datetime_tuple(
(year, month, day, hour, 0, 0), 0)
if format_to_daily:
# Convert the hourly data to daily format.
assert len(hourly_df['Irradiance']) % 24 == 0
new_shape = (len(hourly_df['Irradiance'])//24, 24)
daily_df = {}
daily_df['Irradiance'] = np.sum(
hourly_df['Irradiance'].reshape(new_shape), axis=1)
for key in ['Years', 'Months', 'Days', 'Time']:
daily_df[key] = hourly_df[key].reshape(new_shape)[:, 0]
daily_df['Hours'] = np.zeros(len(daily_df['Irradiance']))
daily_df.update(header_df)
daily_df['Time Format'] = 'daily'
daily_df['CWEEDS Format'] = ext
return daily_df
else:
hourly_df.update(header_df)
hourly_df['Time Format'] = 'hourly'
hourly_df['CWEEDS Format'] = ext
return hourly_df
