s3.codecs.xls

1 # -*- coding: utf-8 -*- 2 3 """ 4 S3 Microsoft Excel codec 5 6 @copyright: 2011-2019 (c) Sahana Software Foundation 7 @license: MIT 8 9 Permission is hereby granted, free of charge, to any person 10 obtaining a copy of this software and associated documentation 11 files (the "Software"), to deal in the Software without 12 restriction, including without limitation the rights to use, 13 copy, modify, merge, publish, distribute, sublicense, and/or sell 14 copies of the Software, and to permit persons to whom the 15 Software is furnished to do so, subject to the following 16 conditions: 17 18 The above copyright notice and this permission notice shall be 19 included in all copies or substantial portions of the Software. 20 21 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, 22 EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES 23 OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND 24 NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT 25 HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, 26 WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING 27 FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR 28 OTHER DEALINGS IN THE SOFTWARE. 29 """ 30 31 __all__ = ("S3XLS", 32 ) 33 34 try: 35 from cStringIO import StringIO # Faster, where available 36 except: 37 from StringIO import StringIO 38 39 from gluon import * 40 from gluon.contenttype import contenttype 41 from gluon.storage import Storage 42 43 from ..s3codec import S3Codec 44 from ..s3utils import s3_str, s3_strip_markup, s3_unicode

45 46 # ============================================================================= 47 -class S3XLS(S3Codec):

48 """ 49 Simple Microsoft Excel format codec 50 """ 51 52 # The xlwt library supports a maximum of 182 characters in a single cell 53 MAX_CELL_SIZE = 182 54 55 # Customizable styles 56 COL_WIDTH_MULTIPLIER = 310 57 # Python xlwt Colours 58 # https://docs.google.com/spreadsheets/d/1ihNaZcUh7961yU7db1-Db0lbws4NT24B7koY8v8GHNQ/pubhtml?gid=1072579560&single=true 59 LARGE_HEADER_COLOUR = 0x2C # pale_blue 60 HEADER_COLOUR = 0x2C # pale_blue 61 SUB_HEADER_COLOUR = 0x18 # periwinkle 62 SUB_TOTALS_COLOUR = 0x96 63 TOTALS_COLOUR = 0x00 64 ROW_ALTERNATING_COLOURS = [0x2A, # light_green 65 0x2B, # light_yellow 66 ] 67 68 ERROR = Storage( 69 XLRD_ERROR = "XLS export requires python-xlrd module to be installed on server", 70 XLWT_ERROR = "XLS export requires python-xlwt module to be installed on server", 71 ) 72 73 # -------------------------------------------------------------------------

74 - def extract(self, resource, list_fields):

75 """ 76 Extract the rows from the resource 77 78 @param resource: the resource 79 @param list_fields: fields to include in list views 80 """ 81 82 title = self.crud_string(resource.tablename, "title_list") 83 84 get_vars = dict(current.request.vars) 85 get_vars["iColumns"] = len(list_fields) 86 query, orderby, left = resource.datatable_filter(list_fields, 87 get_vars, 88 ) 89 resource.add_filter(query) 90 91 if orderby is None: 92 orderby = resource.get_config("orderby") 93 94 data = resource.select(list_fields, 95 left = left, 96 limit = None, 97 count = True, 98 getids = True, 99 orderby = orderby, 100 represent = True, 101 show_links = False, 102 ) 103 104 rfields = data.rfields 105 rows = data.rows 106 107 types = [] 108 lfields = [] 109 heading = {} 110 for rfield in rfields: 111 if rfield.show: 112 lfields.append(rfield.colname) 113 heading[rfield.colname] = rfield.label or \ 114 rfield.field.name.capitalize().replace("_", " ") 115 if rfield.ftype == "virtual": 116 types.append("string") 117 else: 118 types.append(rfield.ftype) 119 120 return (title, types, lfields, heading, rows)

121 122 # -------------------------------------------------------------------------

123 - def encode(self, data_source, title=None, as_stream=False, **attr):

124 """ 125 Export data as a Microsoft Excel spreadsheet 126 127 @param data_source: the source of the data that is to be encoded 128 as a spreadsheet, can be either of: 129 1) an S3Resource 130 2) an array of value dicts (dict of 131 column labels as first item, list of 132 field types as second item) 133 3) a dict like: 134 {columns: [key, ...], 135 headers: {key: label}, 136 types: {key: type}, 137 rows: [{key:value}], 138 } 139 @param title: the title for the output document 140 @param as_stream: return the buffer (StringIO) rather than 141 its contents (str), useful when the output 142 is supposed to be stored locally 143 @param attr: keyword parameters 144 145 @keyword title: the main title of the report 146 @keyword list_fields: fields to include in list views 147 @keyword report_groupby: used to create a grouping of the result: 148 either a Field object of the resource 149 or a string which matches a value in 150 the heading 151 @keyword use_colour: True to add colour to the cells, default False 152 @keyword evenodd: render different background colours 153 for even/odd rows ("stripes") 154 """ 155 156 # Do not redirect from here! 157 # ...but raise proper status code, which can be caught by caller 158 try: 159 import xlwt 160 except ImportError: 161 error = self.ERROR.XLWT_ERROR 162 current.log.error(error) 163 raise HTTP(503, body=error) 164 try: 165 from xlrd.xldate import xldate_from_date_tuple, \ 166 xldate_from_time_tuple, \ 167 xldate_from_datetime_tuple 168 except ImportError: 169 error = self.ERROR.XLRD_ERROR 170 current.log.error(error) 171 raise HTTP(503, body=error) 172 173 import datetime 174 175 MAX_CELL_SIZE = self.MAX_CELL_SIZE 176 COL_WIDTH_MULTIPLIER = self.COL_WIDTH_MULTIPLIER 177 178 # Get the attributes 179 title = attr.get("title") 180 if title is None: 181 title = current.T("Report") 182 list_fields = attr.get("list_fields") 183 group = attr.get("dt_group") 184 use_colour = attr.get("use_colour", False) 185 evenodd = attr.get("evenodd", True) 186 187 # Extract the data from the data_source 188 if isinstance(data_source, dict): 189 headers = data_source.get("headers", {}) 190 lfields = data_source.get("columns", list_fields) 191 column_types = data_source.get("types") 192 types = [column_types[col] for col in lfields] 193 rows = data_source.get("rows") 194 elif isinstance(data_source, (list, tuple)): 195 headers = data_source[0] 196 types = data_source[1] 197 rows = data_source[2:] 198 lfields = list_fields 199 else: 200 if not list_fields: 201 list_fields = data_source.list_fields() 202 (title, types, lfields, headers, rows) = self.extract(data_source, 203 list_fields, 204 ) 205 206 # Verify columns in items 207 request = current.request 208 if len(rows) > 0 and len(lfields) > len(rows[0]): 209 msg = """modules/s3/codecs/xls: There is an error in the list items, a field doesn't exist 210 requesting url %s 211 Headers = %d, Data Items = %d 212 Headers %s 213 List Fields %s""" % (request.url, len(lfields), len(rows[0]), headers, lfields) 214 current.log.error(msg) 215 216 # Grouping 217 report_groupby = lfields[group] if group else None 218 groupby_label = headers[report_groupby] if report_groupby else None 219 220 # Date/Time formats from L10N deployment settings 221 settings = current.deployment_settings 222 date_format = settings.get_L10n_date_format() 223 date_format_str = str(date_format) 224 225 dt_format_translate = self.dt_format_translate 226 date_format = dt_format_translate(date_format) 227 time_format = dt_format_translate(settings.get_L10n_time_format()) 228 datetime_format = dt_format_translate(settings.get_L10n_datetime_format()) 229 230 title_row = settings.get_xls_title_row() 231 232 # Get styles 233 styles = self._styles(use_colour = use_colour, 234 evenodd = evenodd, 235 datetime_format = datetime_format, 236 ) 237 238 # Create the workbook 239 book = xlwt.Workbook(encoding="utf-8") 240 241 # Add sheets 242 sheets = [] 243 # XLS exports are limited to 65536 rows per sheet, we bypass 244 # this by creating multiple sheets 245 row_limit = 65536 246 sheetnum = len(rows) / row_limit 247 # Can't have a / in the sheet_name, so replace any with a space 248 sheet_name = str(title.replace("/", " ")) 249 if len(sheet_name) > 31: 250 # Sheet name cannot be over 31 chars 251 # (take sheet number suffix into account) 252 sheet_name = sheet_name[:31] if sheetnum == 1 else sheet_name[:28] 253 count = 1 254 while len(sheets) <= sheetnum: 255 sheets.append(book.add_sheet("%s-%s" % (sheet_name, count))) 256 count += 1 257 258 if callable(title_row): 259 # Calling with sheet None to get the number of title rows 260 title_row_length = title_row(None) 261 else: 262 title_row_length = 2 263 264 # Add header row to all sheets, determine columns widths 265 header_style = styles["header"] 266 for sheet in sheets: 267 # Move this down if a title row will be added 268 if title_row: 269 header_row = sheet.row(title_row_length) 270 else: 271 header_row = sheet.row(0) 272 column_widths = [] 273 has_id = False 274 col_index = 0 275 for selector in lfields: 276 if selector == report_groupby: 277 continue 278 label = headers[selector] 279 if label == "Id": 280 # Indicate to adjust col_index when writing out 281 has_id = True 282 column_widths.append(0) 283 col_index += 1 284 continue 285 if label == "Sort": 286 continue 287 if has_id: 288 # Adjust for the skipped column 289 write_col_index = col_index - 1 290 else: 291 write_col_index = col_index 292 header_row.write(write_col_index, str(label), header_style) 293 width = max(len(label) * COL_WIDTH_MULTIPLIER, 2000) 294 width = min(width, 65535) # USHRT_MAX 295 column_widths.append(width) 296 sheet.col(write_col_index).width = width 297 col_index += 1 298 299 title = s3_str(title) 300 301 # Title row (optional, deployment setting) 302 if title_row: 303 T = current.T 304 large_header_style = styles["large_header"] 305 notes_style = styles["notes"] 306 for sheet in sheets: 307 if callable(title_row): 308 # Custom title rows 309 title_row(sheet) 310 else: 311 # First row => Title (standard = "title_list" CRUD string) 312 current_row = sheet.row(0) 313 if col_index > 0: 314 sheet.write_merge(0, 0, 0, col_index, 315 title, 316 large_header_style, 317 ) 318 current_row.height = 500 319 # Second row => Export date/time 320 current_row = sheet.row(1) 321 current_row.write(0, "%s:" % T("Date Exported"), notes_style) 322 current_row.write(1, request.now, notes_style) 323 # Fix the size of the last column to display the date 324 if 16 * COL_WIDTH_MULTIPLIER > width: 325 sheet.col(col_index).width = 16 * COL_WIDTH_MULTIPLIER 326 327 # Initialize counters 328 totalCols = col_index 329 # Move the rows down if a title row is included 330 if title_row: 331 row_index = title_row_length 332 else: 333 row_index = 0 334 335 # Helper function to get the current row 336 def get_current_row(row_count, row_limit): 337 338 sheet_count = int(row_count / row_limit) 339 row_number = row_count - (sheet_count * row_limit) 340 if sheet_count > 0: 341 row_number += 1 342 return sheets[sheet_count], sheets[sheet_count].row(row_number)

343 344 # Write the table contents 345 subheading = None 346 odd_style = styles["odd"] 347 even_style = styles["even"] 348 subheader_style = styles["subheader"] 349 for row in rows: 350 # Current row 351 row_index += 1 352 current_sheet, current_row = get_current_row(row_index, row_limit) 353 style = even_style if row_index % 2 == 0 else odd_style 354 355 # Group headers 356 if report_groupby: 357 represent = s3_strip_markup(s3_unicode(row[report_groupby])) 358 if subheading != represent: 359 # Start of new group - write group header 360 subheading = represent 361 current_sheet.write_merge(row_index, row_index, 0, totalCols, 362 subheading, 363 subheader_style, 364 ) 365 # Move on to next row 366 row_index += 1 367 current_sheet, current_row = get_current_row(row_index, row_limit) 368 style = even_style if row_index % 2 == 0 else odd_style 369 370 col_index = 0 371 remaining_fields = lfields 372 373 # Custom row style? 374 row_style = None 375 if "_style" in row: 376 stylename = row["_style"] 377 if stylename in styles: 378 row_style = styles[stylename] 379 380 # Group header/footer row? 381 if "_group" in row: 382 group_info = row["_group"] 383 label = group_info.get("label") 384 totals = group_info.get("totals") 385 if label: 386 label = s3_strip_markup(s3_unicode(label)) 387 style = row_style or subheader_style 388 span = group_info.get("span") 389 if span == 0: 390 current_sheet.write_merge(row_index, 391 row_index, 392 0, 393 totalCols - 1, 394 label, 395 style, 396 ) 397 if totals: 398 # Write totals into the next row 399 row_index += 1 400 current_sheet, current_row = \ 401 get_current_row(row_index, row_limit) 402 else: 403 current_sheet.write_merge(row_index, 404 row_index, 405 0, 406 span - 1, 407 label, 408 style, 409 ) 410 col_index = span 411 remaining_fields = lfields[span:] 412 if not totals: 413 continue 414 415 for field in remaining_fields: 416 label = headers[field] 417 if label == groupby_label: 418 continue 419 if label == "Id": 420 # Skip the ID column from XLS exports 421 col_index += 1 422 continue 423 424 if field not in row: 425 represent = "" 426 else: 427 represent = s3_strip_markup(s3_unicode(row[field])) 428 429 coltype = types[col_index] 430 if coltype == "sort": 431 continue 432 if len(represent) > MAX_CELL_SIZE: 433 represent = represent[:MAX_CELL_SIZE] 434 value = represent 435 if coltype == "date": 436 try: 437 cell_datetime = datetime.datetime.strptime(value, 438 date_format_str) 439 date_tuple = (cell_datetime.year, 440 cell_datetime.month, 441 cell_datetime.day) 442 value = xldate_from_date_tuple(date_tuple, 0) 443 style.num_format_str = date_format 444 except: 445 pass 446 elif coltype == "datetime": 447 try: 448 cell_datetime = datetime.datetime.strptime(value, 449 date_format_str) 450 date_tuple = (cell_datetime.year, 451 cell_datetime.month, 452 cell_datetime.day, 453 cell_datetime.hour, 454 cell_datetime.minute, 455 cell_datetime.second) 456 value = xldate_from_datetime_tuple(date_tuple, 0) 457 style.num_format_str = datetime_format 458 except: 459 pass 460 elif coltype == "time": 461 try: 462 cell_datetime = datetime.datetime.strptime(value, 463 date_format_str) 464 date_tuple = (cell_datetime.hour, 465 cell_datetime.minute, 466 cell_datetime.second) 467 value = xldate_from_time_tuple(date_tuple) 468 style.num_format_str = time_format 469 except: 470 pass 471 elif coltype == "integer": 472 try: 473 value = int(value) 474 style.num_format_str = "0" 475 except: 476 pass 477 elif coltype == "double": 478 try: 479 value = float(value) 480 style.num_format_str = "0.00" 481 except: 482 pass 483 if has_id: 484 # Adjust for the skipped column 485 write_col_index = col_index - 1 486 else: 487 write_col_index = col_index 488 489 current_row.write(write_col_index, value, style) 490 width = len(represent) * COL_WIDTH_MULTIPLIER 491 if width > column_widths[col_index]: 492 column_widths[col_index] = width 493 current_sheet.col(write_col_index).width = width 494 col_index += 1 495 496 # Additional sheet settings 497 for sheet in sheets: 498 sheet.panes_frozen = True 499 sheet.horz_split_pos = 1 500 501 # Write output 502 output = StringIO() 503 book.save(output) 504 output.seek(0) 505 506 if as_stream: 507 return output 508 509 # Response headers 510 filename = "%s_%s.xls" % (request.env.server_name, title) 511 disposition = "attachment; filename=\"%s\"" % filename 512 response = current.response 513 response.headers["Content-Type"] = contenttype(".xls") 514 response.headers["Content-disposition"] = disposition 515 516 return output.read()

517 518 # ------------------------------------------------------------------------- 519 @staticmethod

520 - def encode_pt(pt, title):

521 """ 522 Encode a S3PivotTable as XLS sheet 523 524 @param pt: the S3PivotTable 525 @param title: the title for the report 526 527 @returns: the XLS file as stream 528 """ 529 530 output = StringIO() 531 532 book = S3PivotTableXLS(pt).encode(title) 533 book.save(output) 534 535 output.seek(0) 536 537 return output

538 539 # ------------------------------------------------------------------------- 540 @staticmethod

541 - def dt_format_translate(pyfmt):

542 """ 543 Translate a Python datetime format string into an 544 Excel datetime format string 545 546 @param pyfmt: the Python format string 547 """ 548 549 translate = {"%a": "ddd", 550 "%A": "dddd", 551 "%b": "mmm", 552 "%B": "mmmm", 553 "%c": "", 554 "%d": "dd", 555 "%f": "", 556 "%H": "hh", 557 "%I": "hh", 558 "%j": "", 559 "%m": "mm", 560 "%M": "mm", 561 "%p": "AM/PM", 562 "%S": "ss", 563 "%U": "", 564 "%w": "", 565 "%W": "", 566 "%x": "", 567 "%X": "", 568 "%y": "yy", 569 "%Y": "yyyy", 570 "%z": "", 571 "%Z": "", 572 } 573 574 PERCENT = "__percent__" 575 xlfmt = str(pyfmt).replace("%%", PERCENT) 576 577 for tag, translation in translate.items(): 578 xlfmt = xlfmt.replace(tag, translation) 579 580 return xlfmt.replace(PERCENT, "%")

581 582 # ------------------------------------------------------------------------- 583 @classmethod

584 - def _styles(cls, 585 use_colour=False, 586 evenodd=True, 587 datetime_format=None, 588 ):

589 """ 590 XLS encoder standard cell styles 591 592 @param use_colour: use background colour in cells 593 @param evenodd: render different background colours 594 for even/odd rows ("stripes") 595 @param datetime_format: the date/time format 596 """ 597 598 import xlwt 599 600 if datetime_format is None: 601 # Support easier usage from external functions 602 datetime_format = cls.dt_format_translate(current.deployment_settings.get_L10n_datetime_format()) 603 604 605 # Styles 606 large_header = xlwt.XFStyle() 607 large_header.font.bold = True 608 large_header.font.height = 400 609 if use_colour: 610 SOLID_PATTERN = large_header.pattern.SOLID_PATTERN 611 large_header.alignment.horz = large_header.alignment.HORZ_CENTER 612 large_header.pattern.pattern = SOLID_PATTERN 613 large_header.pattern.pattern_fore_colour = cls.LARGE_HEADER_COLOUR 614 615 notes = xlwt.XFStyle() 616 notes.font.italic = True 617 notes.font.height = 160 # 160 Twips = 8 point 618 notes.num_format_str = datetime_format 619 620 header = xlwt.XFStyle() 621 header.font.bold = True 622 header.num_format_str = datetime_format 623 if use_colour: 624 header.pattern.pattern = SOLID_PATTERN 625 header.pattern.pattern_fore_colour = cls.HEADER_COLOUR 626 627 subheader = xlwt.XFStyle() 628 subheader.font.bold = True 629 if use_colour: 630 subheader.pattern.pattern = SOLID_PATTERN 631 subheader.pattern.pattern_fore_colour = cls.SUB_HEADER_COLOUR 632 633 subtotals = xlwt.XFStyle() 634 subtotals.font.bold = True 635 if use_colour: 636 subtotals.pattern.pattern = SOLID_PATTERN 637 subtotals.pattern.pattern_fore_colour = cls.SUB_TOTALS_COLOUR 638 639 totals = xlwt.XFStyle() 640 totals.font.bold = True 641 if use_colour: 642 totals.pattern.pattern = SOLID_PATTERN 643 totals.pattern.pattern_fore_colour = cls.TOTALS_COLOUR 644 645 odd = xlwt.XFStyle() 646 if use_colour and evenodd: 647 odd.pattern.pattern = SOLID_PATTERN 648 odd.pattern.pattern_fore_colour = cls.ROW_ALTERNATING_COLOURS[0] 649 650 even = xlwt.XFStyle() 651 if use_colour and evenodd: 652 even.pattern.pattern = SOLID_PATTERN 653 even.pattern.pattern_fore_colour = cls.ROW_ALTERNATING_COLOURS[1] 654 655 return {"large_header": large_header, 656 "notes": notes, 657 "header": header, 658 "subheader": subheader, 659 "subtotals": subtotals, 660 "totals": totals, 661 "odd": odd, 662 "even": even, 663 }

664

665 # ============================================================================= 666 -class S3PivotTableXLS(object):

667 """ 668 XLS encoder for S3PivotTables 669 670 @todo: merge+DRY with S3XLS? 671 @todo: support multiple layers (=write multiple sheets) 672 @todo: handle huge pivot tables (=exceeding XLS rows/cols limits) 673 """ 674

675 - def __init__(self, pt):

676 """ 677 Constructor 678 679 @param pt: the S3PivotTable to encode 680 """ 681 682 self.pt = pt 683 684 # Initialize properties 685 self._styles = None 686 self._formats = None 687 688 self.lookup = {} 689 self.valuemap = {}

690 691 # -------------------------------------------------------------------------

692 - def encode(self, title):

693 """ 694 Convert this pivot table into an XLS file 695 696 @param title: the title of the report 697 698 @returns: the XLS workbook 699 """ 700 701 try: 702 import xlwt 703 except ImportError: 704 error = S3XLS.ERROR.XLWT_ERROR 705 current.log.error(error) 706 raise HTTP(503, body=error) 707 708 T = current.T 709 710 TOTAL = s3_str(s3_unicode(T("Total")).upper()) 711 712 pt = self.pt 713 714 # Get report options 715 report_options = pt.resource.get_config("report_options", {}) 716 717 # Report dimensions 718 fact = pt.facts[0] 719 layer = fact.layer 720 rows_dim = pt.rows 721 cols_dim = pt.cols 722 723 numrows = pt.numrows 724 numcols = pt.numcols 725 726 # Resource fields for dimensions 727 rfields = pt.rfields 728 fact_rfield = rfields[fact.selector] 729 rows_rfield = rfields[rows_dim] if rows_dim else None 730 cols_rfield = rfields[cols_dim] if cols_dim else None 731 732 # Dimension labels 733 get_label = fact._get_field_label 734 if rows_dim: 735 # Get row axis label 736 rows_label = s3_str(get_label(rows_rfield, 737 report_options.get("rows"), 738 )) 739 else: 740 rows_label = "" 741 if cols_dim: 742 cols_label = s3_str(get_label(cols_rfield, 743 report_options.get("cols"), 744 )) 745 else: 746 cols_label = "" 747 fact_label = s3_str(fact.get_label(fact_rfield, 748 report_options.get("fact"), 749 )) 750 751 # Index of the column for row totals 752 total_column = (numcols + 1) if cols_dim else 1 753 754 # Sort+represent rows and columns 755 rows, cols = self.sortrepr() 756 757 # Create workbook and sheet 758 book = xlwt.Workbook(encoding="utf-8") 759 sheet = book.add_sheet(s3_str(title)) 760 761 write = self.write 762 763 # Write header 764 title_row = current.deployment_settings.get_xls_title_row() 765 if callable(title_row): 766 # Custom header (returns number of header rows) 767 title_length = title_row(sheet) 768 769 elif title_row: 770 # Default header 771 title_length = 2 772 773 # Report title 774 write(sheet, 0, 0, s3_str(title), 775 colspan = numcols + 2, 776 style = "title", 777 ) 778 779 # Current date/time (in local timezone) 780 import datetime 781 from ..s3datetime import S3DateTime 782 dt = current.request.utcnow 783 offset = S3DateTime.get_offset_value(current.session.s3.utc_offset) 784 if offset: 785 dt += datetime.timedelta(seconds=offset) 786 write(sheet, 1, 0, dt, style = "subheader", numfmt = "datetime") 787 788 else: 789 # No header 790 title_length = -1 791 792 rowindex = title_length + 1 793 794 # Fact label 795 if rows_dim and cols_dim: 796 write(sheet, rowindex, 0, fact_label, style="fact_label") 797 798 # Columns axis title 799 if cols_dim: 800 write(sheet, rowindex, 1, cols_label, 801 colspan = numcols, 802 style = "axis_title", 803 ) 804 rowindex += 1 805 806 # Row axis title 807 write(sheet, rowindex, 0, rows_label, style="axis_title") 808 809 # Column labels 810 if cols_dim: 811 for idx, col in enumerate(cols): 812 write(sheet, rowindex, idx + 1, col[2]["text"], 813 style = "col_label", 814 ) 815 total_label = TOTAL 816 else: 817 # Use fact title as row total label if there is no column axis 818 total_label = fact_label 819 820 # Row totals label 821 write(sheet, rowindex, total_column, total_label, style="total_right") 822 823 # Determine the number format for cell values 824 numfmt = self.number_format() 825 totfmt = "integer" if fact.method in ("count", "list") else numfmt 826 827 # Choose cell value style according to number format 828 fact_style = "numeric" if numfmt else None 829 830 # Get fact representation method 831 if fact.method == "list": 832 listrepr = self.listrepr 833 fk, fact_repr = pt._represents([layer])[fact.selector] 834 else: 835 listrepr = fk = fact_repr = None 836 837 # Write data rows (if any) 838 rowindex += 1 839 if rows_dim: 840 icell = pt.cell 841 for i in xrange(numrows): 842 843 row = rows[i] 844 845 # Row-label 846 write(sheet, rowindex + i, 0, row[2]["text"], 847 style = "row_label", 848 ) 849 850 # Cell column values (if any) 851 if cols_dim: 852 for j in xrange(numcols): 853 cell = icell[row[0]][cols[j][0]] 854 if listrepr: 855 value = listrepr(cell, fact_rfield, fact_repr, fk=fk) 856 else: 857 value = cell[layer] 858 write(sheet, rowindex + i, j + 1, value, 859 numfmt = numfmt, 860 style = fact_style, 861 ) 862 863 # Row-total 864 write(sheet, rowindex + i, total_column, row[1], 865 style = "total", 866 numfmt = totfmt, 867 ) 868 869 rowindex += numrows 870 total_label = TOTAL 871 else: 872 # Use fact label as column totals label if 873 # there is no row dimension 874 total_label = fact_label 875 876 # Column totals label 877 write(sheet, rowindex, 0, total_label, style="total_left") 878 879 # Column totals 880 if cols_dim: 881 for i in xrange(numcols): 882 write(sheet, rowindex, i + 1, cols[i][1], 883 style = "total", 884 numfmt = totfmt, 885 ) 886 887 # Grand total 888 total = pt.totals[layer] 889 write(sheet, rowindex, total_column, total, 890 style = "grand_total", 891 numfmt = totfmt, 892 ) 893 894 return book

895 896 # -------------------------------------------------------------------------

897 - def write(self, 898 sheet, 899 rowindex, 900 colindex, 901 value, 902 style=None, 903 numfmt=None, 904 rowspan=None, 905 colspan=None, 906 adjust=True 907 ):

908 """ 909 Write a value to a spreadsheet cell 910 911 @param sheet: the work sheet 912 @param rowindex: the row index of the cell 913 @param colindex: the column index of the cell 914 @param value: the value to write 915 @param style: a style name (see styles property) 916 @param numfmt: a number format name (see formats property) 917 @param rowspan: number of rows to merge 918 @param colspan: number of columns to merge 919 @param adjust: True to adjust column width and row height, 920 False to suppress automatic adjustment 921 """ 922 923 styles = self.styles 924 if style: 925 style = styles.get(style) 926 if style is None: 927 style = styles["default"] 928 929 # Apply number format 930 if numfmt: 931 style.num_format_str = self.formats.get(numfmt, "") 932 933 # Get the row 934 row = sheet.row(rowindex) 935 936 if type(value) is list: 937 labels = [s3_str(v) for v in value] 938 contents = "\n".join(labels) 939 else: 940 labels = [s3_str(value)] 941 contents = value 942 943 # Apply rowspan and colspan 944 rowspan = 0 if not rowspan or rowspan < 1 else rowspan - 1 945 colspan = 0 if not colspan or colspan < 1 else colspan - 1 946 if rowspan > 1 or colspan > 1: 947 # Write-merge 948 sheet.write_merge(rowindex, rowindex + rowspan, 949 colindex, colindex + colspan, 950 contents, 951 style, 952 ) 953 else: 954 # Just write 955 row.write(colindex, contents, style) 956 957 # Reset number format 958 style.num_format_str = "" 959 960 # Adjust column width and row height 961 # NB approximations, no exact science (not possible except by 962 # enforcing a particular fixed-width font, which we don't 963 # want), so manual adjustments after export may still be 964 # necessary. Better solutions welcome! 965 if adjust: 966 967 fontsize = float(style.font.height) 968 969 # Adjust column width 970 col = sheet.col(colindex) 971 if not colspan: 972 if labels: 973 width = int(min(max(len(l) for l in labels), 28) * 974 fontsize * 5.0 / 3.0) 975 else: 976 width = 0 977 if width > col.width: 978 col.width = width 979 980 # Adjust row height 981 if not rowspan: 982 983 lineheight = 1.2 if style.font.bold else 1.0 984 985 import math 986 numlines = 0 987 width = (col.width * 0.8 * (colspan + 1)) 988 for label in labels: 989 numlines += math.ceil(len(label) * fontsize / width) 990 991 if numlines > 1: 992 lines = min(numlines, 10) 993 height = int((lines + 0.8 / lineheight) * 994 fontsize * lineheight) 995 else: 996 height = int(fontsize * lineheight) 997 if height > row.height: 998 row.height = height 999 row.height_mismatch = 1

1000 1001 # ------------------------------------------------------------------------- 1002 @property

1003 - def styles(self):

1004 """ 1005 Style definitions for pivot tables (lazy property) 1006 1007 @returns: dict of named XFStyle instances 1008 """ 1009 1010 styles = self._styles 1011 if styles is None: 1012 1013 import xlwt 1014 1015 # Alignments 1016 Alignment = xlwt.Alignment 1017 1018 center = Alignment() 1019 center.horz = Alignment.HORZ_CENTER 1020 center.vert = Alignment.VERT_CENTER 1021 center.wrap = 1 1022 1023 centerleft = Alignment() 1024 centerleft.horz = Alignment.HORZ_LEFT 1025 centerleft.vert = Alignment.VERT_CENTER 1026 centerleft.wrap = 1 1027 1028 bottomcentered = Alignment() 1029 bottomcentered.horz = Alignment.HORZ_CENTER 1030 bottomcentered.vert = Alignment.VERT_BOTTOM 1031 bottomcentered.wrap = 1 1032 1033 bottomleft = Alignment() 1034 bottomleft.horz = Alignment.HORZ_LEFT 1035 bottomleft.vert = Alignment.VERT_BOTTOM 1036 bottomleft.wrap = 1 1037 1038 bottomright = Alignment() 1039 bottomright.horz = Alignment.HORZ_RIGHT 1040 bottomright.vert = Alignment.VERT_BOTTOM 1041 bottomright.wrap = 1 1042 1043 topleft = Alignment() 1044 topleft.horz = Alignment.HORZ_LEFT 1045 topleft.vert = Alignment.VERT_TOP 1046 topleft.wrap = 1 1047 1048 topright = Alignment() 1049 topright.horz = Alignment.HORZ_RIGHT 1050 topright.vert = Alignment.VERT_TOP 1051 topright.wrap = 1 1052 1053 # Determine XLS datetime format 1054 settings = current.deployment_settings 1055 dtfmt = S3XLS.dt_format_translate(settings.get_L10n_datetime_format()) 1056 1057 # Styles 1058 XFStyle = xlwt.XFStyle 1059 1060 # Points to Twips 1061 twips = lambda pt: 20 * pt 1062 1063 def style(fontsize=10, bold=False, italic=False, align=None): 1064 """ XFStyle builder helper """ 1065 style = XFStyle() 1066 style.font.height = twips(fontsize) 1067 style.font.bold = bold 1068 style.font.italic = italic 1069 if align is not None: 1070 style.alignment = align 1071 return style

1072 1073 self._styles = styles = { 1074 "default": style(align=topleft), 1075 "numeric": style(align=bottomright), 1076 "title": style(fontsize=14, bold=True, align=bottomleft), 1077 "subheader": style(fontsize=8, italic=True, align=bottomleft), 1078 "row_label": style(bold=True, align=topleft), 1079 "col_label": style(bold=True, align=bottomcentered), 1080 "fact_label": style(fontsize=13, bold=True, align=centerleft), 1081 "axis_title": style(fontsize=11, bold=True, align=center), 1082 "total": style(fontsize=11, bold=True, italic=True, align=topright), 1083 "total_left": style(fontsize=11, bold=True, italic=True, align=topleft), 1084 "total_right": style(fontsize=11, bold=True, italic=True, align=center), 1085 "grand_total": style(fontsize=12, bold=True, italic=True, align=topright), 1086 } 1087 1088 return styles

1089 1090 # ------------------------------------------------------------------------- 1091 @property

1092 - def formats(self):

1093 """ 1094 Number formats for pivot tables (lazy property) 1095 1096 @returns: dict of format strings 1097 """ 1098 1099 formats = self._formats 1100 if formats is None: 1101 1102 # Date/Time formats from L10N deployment settings 1103 settings = current.deployment_settings 1104 1105 translate = S3XLS.dt_format_translate 1106 date_format = translate(settings.get_L10n_date_format()) 1107 datetime_format = translate(settings.get_L10n_datetime_format()) 1108 time_format = translate(settings.get_L10n_time_format()) 1109 1110 formats = { 1111 "date": date_format, 1112 "datetime": datetime_format, 1113 "time": time_format, 1114 "integer": "0", 1115 "double": "0.00" 1116 } 1117 1118 self._formats = formats 1119 1120 return formats

1121 1122 # -------------------------------------------------------------------------

1123 - def number_format(self):

1124 """ 1125 Determine the number format for this pivot table 1126 1127 @returns: the number format key (see formats property) 1128 """ 1129 1130 numfmt = None 1131 1132 pt = self.pt 1133 1134 fact = pt.facts[0] 1135 rfield = pt.rfields[fact.selector] 1136 1137 ftype = rfield.ftype 1138 1139 if fact.method == "count": 1140 numfmt = "integer" 1141 1142 elif ftype == "integer": 1143 if fact.method == "avg": 1144 # Average value of ints is a float 1145 numfmt = "double" 1146 else: 1147 numfmt = "integer" 1148 1149 elif ftype in ("date", "datetime", "time", "double"): 1150 numfmt = ftype 1151 1152 elif ftype == "virtual": 1153 # Probe the first value 1154 value = pt.cell[0][0][fact.layer] 1155 if isinstance(value, (int, long)): 1156 numfmt = "integer" 1157 elif isinstance(value, float): 1158 numfmt = "double" 1159 else: 1160 import datetime 1161 if isinstance(value, datetime.datetime): 1162 numfmt = "datetime" 1163 elif isinstance(value, datetime.date): 1164 numfmt = "date" 1165 elif isinstance(value, datetime.time): 1166 numfmt = "time" 1167 1168 return numfmt

1169 1170 # -------------------------------------------------------------------------

1171 - def sortrepr(self):

1172 """ 1173 Sort and represent pivot table axes 1174 1175 @returns: tuple (rows, cols), each a list of tuples: 1176 (index, ...the index of the row/column in 1177 the original cell array 1178 total, ...total value of the row/column 1179 {value: axis_value, ...group value of the row/column 1180 text: axis_repr, ...representation of the group value 1181 }, 1182 ) 1183 """ 1184 1185 pt = self.pt 1186 1187 rfields = pt.rfields 1188 layer = pt.facts[0].layer 1189 1190 # Sort rows 1191 rows_dim = pt.rows 1192 rows_rfield = rfields[rows_dim] if rows_dim else None 1193 row_repr = pt._represent_method(rows_dim) 1194 irows = pt.row 1195 rows = [] 1196 for i in xrange(pt.numrows): 1197 irow = irows[i] 1198 header = {"value": irow.value, 1199 "text": irow.text if "text" in irow 1200 else row_repr(irow.value), 1201 } 1202 rows.append((i, irow[layer], header)) 1203 pt._sortdim(rows, rows_rfield, index=2) 1204 1205 # Sort columns 1206 cols_dim = pt.cols 1207 cols_rfield = rfields[cols_dim] if cols_dim else None 1208 col_repr = pt._represent_method(cols_dim) 1209 icols = pt.col 1210 cols = [] 1211 for i in xrange(pt.numcols): 1212 icol = icols[i] 1213 header = {"value": icol.value, 1214 "text": icol.text if "text" in icol 1215 else col_repr(icol.value), 1216 } 1217 cols.append((i, icol[layer], header)) 1218 pt._sortdim(cols, cols_rfield, index=2) 1219 1220 return rows, cols

1221 1222 # -------------------------------------------------------------------------

1223 - def listrepr(self, cell, rfield, represent, fk=True):

1224 """ 1225 Represent and sort a list of cell values (for "list" aggregation 1226 method) 1227 1228 @param cell - the cell data 1229 @param rfield - the fact S3ResourceField 1230 @param represent - representation method for the fact field 1231 @param fk - fact field is a foreign key 1232 1233 @returns: sorted list of represented cell values 1234 """ 1235 1236 pt = self.pt 1237 records = pt.records 1238 1239 colname = rfield.colname 1240 1241 lookup = self.lookup 1242 valuemap = self.valuemap 1243 1244 keys = [] 1245 1246 for record_id in cell["records"]: 1247 record = records[record_id] 1248 try: 1249 fvalue = record[colname] 1250 except AttributeError: 1251 continue 1252 1253 if fvalue is None: 1254 continue 1255 if type(fvalue) is not list: 1256 fvalue = [fvalue] 1257 1258 for v in fvalue: 1259 if v is None: 1260 continue 1261 if fk: 1262 if v not in keys: 1263 keys.append(v) 1264 if v not in lookup: 1265 lookup[v] = represent(v) 1266 else: 1267 if v not in valuemap: 1268 next_id = len(valuemap) 1269 valuemap[v] = next_id 1270 keys.append(next_id) 1271 lookup[next_id] = represent(v) 1272 else: 1273 prev_id = valuemap[v] 1274 if prev_id not in keys: 1275 keys.append(prev_id) 1276 1277 keys.sort(key=lambda i: lookup[i]) 1278 items = [s3_str(lookup[key]) for key in keys if key in lookup] 1279 1280 return items

1281 1282 # ============================================================================= 1283 #class S3HTML2XLS(object): 1284 # """ 1285 # Class that takes HTML in the form of web2py helper objects 1286 # and converts it to XLS 1287 # 1288 # @ToDo: Complete this (e.g. start with a copy of S3html2pdf) 1289 # See https://gist.github.com/JustOnce/2be3e4d951a66c22c5e0 1290 # & http://pydoc.net/Python/Kiowa/0.2w.rc9/kiowa.utils.xls.html2xls/ 1291 # 1292 # Places to use this: 1293 # org_CapacityReport() 1294 # """ 1295 # 1296 # def __init__(self): 1297 # 1298 # pass 1299 # 1300 # # ------------------------------------------------------------------------- 1301 # def parse(self, html): 1302 # """ 1303 # Entry point for class 1304 # """ 1305 # 1306 # return None 1307 # 1308 # END ========================================================================= 1309

Source Code for Module s3.codecs.xls