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)
예제 #2
0
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)
예제 #3
0
    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]
예제 #4
0
    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]
예제 #5
0
파일: xls.py 프로젝트: nursix/eden-core
    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()
예제 #6
0
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,
        })
예제 #7
0
    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()
예제 #8
0
파일: xls.py 프로젝트: dmncdr/eden
    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()
예제 #9
0
    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()
예제 #10
0
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
예제 #11
0
    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()
예제 #12
0
파일: xls.py 프로젝트: ahaym/eden
    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()
예제 #13
0
파일: xls.py 프로젝트: RGIMS/eden
    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()
예제 #14
0
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