DOKK Library

pyExcelerator/xlwt Cheatsheet

Authors Matt Harrison

License CC-BY-3.0

#!/bin/env python
                                                                                       pyExcelerator/xlwt Cheatsheet
                                                                                                # Border                                                             def get_font(values):
"""Executable cheatsheet illustrating use of pyExcelerator and its                                                                                                       """
                                                                                                write(ws, 0, 2, "Border",
fork, xlwt                                                                                                                                                               'height' 10pt = 200, 8pt = 160
                                                                                                      {"border": (("bottom",pycel.Formatting.Borders.THIN),
                                                                                                                  ("bottom_colour", YELLOW))})                           """
I recommend using xlwt which is a somewhat unknown fork of                                                                                                               font_key = values
pyExcelerator. There are examples shipped with both projects, use                                                                                                        f = FONT_FACTORY.get(font_key, None)
them if necessary, but the source is usually your best friend. The                                                                                                       if f is None:
                                                                                                # Wrapping
libraries are quite capable but don't support charting. xlwt also as
                                                                   h                                                                                                         f = pycel.Font()
                                                                                                write(ws, 0, 3, "A bunch of long text to wrap",
a mailing list that is active here:                                                                                                                                          for attr, value in values:
                                                                                                      {"alignment":(("wrap", pycel.Alignment.WRAP_AT_RIGHT),)})                                                                                                                                    f.__setattr__(attr, value)
                                                                                                # Set column width                                                           FONT_FACTORY[font_key] = f
Another good link is here:                                                                                                                                               return f
                                                                                                # (see pycel.BIFFRecords.ColInfoRecord for details, width in
                                                                                                # 1/256th of zero character)
                                                                                                write(ws, 0, 4, "A bunch of longer text not wrapped")                if __name__ == "__main__":
This illustrates common usage for .xls generation, but also uses                                                                                                         create_spreadsheet()
                                                                                                ws.col(4).width = len("A bunch of longer text not wrapped")*256
factories to limit object creation of styles (which can crash Excel)
It's meant to show example, but for more details, I recommend the
                                                                                                # Freeze/split headers when scrolling
sources I mention above.
                                                                                                write(ws, 0, 5, "Header")
                                                                                                ws.panes_frozen = True
Please send comments/suggestions my way
                                                                                                ws.horz_split_pos = 1
                                                                                                for row in range(1, 200):
                                                                                                    write(ws, row, 5, row)
                                                                                                # Save the workbook
#import pyExcelerator as pycel
import xlwt as pycel
                                                                                            def write(ws, row, col, data, style=None):
# Excel has issues when creating too many styles/fonts, hence use
# a factory to reuse instances (see FAQ#13
                                                                   .html )
                                                                                                Write data to row, col of worksheet (ws) using the style
                                                                                                Again, I'm wrapping this because you'll have to do it if you
def create_spreadsheet():
                                                                                                create large amounts of formatted entries in your spreadsheet
    # Create a workbook
                                                                                                (else Excel, but probably not OOo will crash).
    wb = pycel.Workbook()
                                                                                                if style:
   # Add a sheet
                                                                                                    s = get_style(style)
   ws = wb.add_sheet("Example Sheet")
                                                                                                    ws.write(row, col, data, s)
   # Tweak printer settings
                                                                                                    ws.write(row, col, data)
   # following makes a landscape layout on Letter paper
   # the width of the columns
                                                                                            def get_style(style):
   ws.fit_num_pages = 1
   ws.fit_height_to_pages = 0
                                                                                                Style is a dict maping key to values.
   ws.fit_width_to_pages = 1
                                                                                                Valid keys are: background, format, alignment, border
   # Set to Letter paper
   # See BiffRecords.SetupPageRecord for paper types/orientation
                                                                                                 The values for keys are lists of tuples containing (attribute,
   ws.paper_size_code = 1
                                                                                                 value) pairs to set on model instances...
   # Set to landscape
   ws.portrait = 0
                                                                                                 print "KEY", style
                                                                                                 style_key = tuple(style.items())
   # Write some stuff using our helper function
                                                                                                 s = STYLE_FACTORY.get(style_key, None)
                                                                                                 if s is None:
   # Formatting - hint, look at Format code in OOo
                                                                                                     s = pycel.XFStyle()
   #               format cells... Numbers tab
                                                                                                     for key, values in style.items():
   # Write a percent
                                                                                                         if key == "background":
   write(ws, 0, 0, .495, {"format":"0%"})
                                                                                                             p = pycel.Pattern()
   # Write a percent with negatives red
                                                                                                             for attr, value in values:
   write(ws, 1, 0, -.495, {"format":"0%;[RED]-0%"})
                                                                                                                 p.__setattr__(attr, value)
   # Dollar amounts
                                                                                                             s.pattern = p
   write(ws, 2, 0, 10.99, {"format":'$#,##0'})
                                                                                                         elif key == "format":
                                                                                                             s.num_format_str = values
   # Font
                                                                                                         elif key == "alignment":
   # Size
                                                                                                             a = pycel.Alignment()
   write(ws, 0, 1, "Size 160(8pt)", {"font": (("height", 160),)})
                                                                                                             for attr, value in values:
   write(ws, 1, 1, "Size 200(10pt)", {"font": (("height", 200),)})
                                                                                                                 a.__setattr__(attr, value)
   # Bold
                                                                                                             s.alignment = a                                              
   write(ws, 2, 1, "Bold text", {"font": (("bold", True),)})
                                                                                                         elif key == "border":
                                                                                                             b = pycel.Formatting.Borders()
   # Background color
   # See
                                                                                                             for attr, value in values:                           Creative Commons Attribution 3.0 License.
                                                                                                                 b.__setattr__(attr, value)
   # for colour indices
                                                                                                             s.borders = b
   YELLOW = 5
                                                                                                         elif key == "font":
   write(ws, 3, 1, "Yellow (5) Background",
                                                                                                             f = get_font(values)
          {"background": (("pattern", pycel.Pattern.SOLID_PATTERN),
                                                                                                             s.font = f
                           ("pattern_fore_colour", YELLOW) )})
                                                                                                     STYLE_FACTORY[style_key] = s
                                                                                                 return s