Python XLWT Module

Examples generating Excel Documents using Python s xlwthere is some simple Examples using Python's XLWT library to Dynami Cally generate Excel documents. Please note a useful alternative is EZODF, which allows you to generate ODS (Open Document Spreadsheet) files for LIBR Eoffice/openoffice. You can check the them out at:http://packages.python.org/ezodf/index.htmlthe simplest exampleimport Xlwtworkbook = xlwt. Workbook (encoding = ' ASCII ') worksheet = Workbook.add_sheet (' My worksheet ') worksheet.write (0, 0, label = ' Row 0, Column 0 V Alue ') workbook.save (' Excel_workbook.xls ') formatting the Contents of a cellimport Xlwtworkbook = XLWT. Workbook (encoding = ' ASCII ') worksheet = Workbook.add_sheet (' My worksheet ') Font = XLWT. Font () # Create the Fontfont.name = ' Times New Roman ' font.bold = truefont.underline = Truefont.italic = Truestyle = Xlwt.x Fstyle () # Create the Stylestyle.font = font # Apply The font to the Styleworksheet.write (0, 0, label = ' unformatted value ') worksheet.write (1, 0, label = ' Formatted value ', style) # Apply the style to the Cellworkbook.save (' Excel_workbook.xls ') Attributes of the font Object font genus Sex Font.Bold = true # May be:true, Falsefont.italic = true # could be:true, falsefont.struck_out = true # May be:true, Fals Efont.underline = XLWT. Font.underline_single # May Be:underline_none, Underline_single, UNDERLINE_SINGLE_ACC, underline_double, UNDERLINE_ Double_accfont.escapement = XLWT. Font.escapement_superscript # May Be:escapement_none, escapement_superscript, escapement_subscriptfont.family = XLWT. Font.family_roman # May Be:family_none, Family_roman, Family_swiss, Family_modern, Family_script, FAMILY_ Decorativefont.charset = XLWT. Font.charset_ansi_latin # May Be:charset_ansi_latin, Charset_sys_default, Charset_symbol, CHARSET_APPLE_ROMAN, Charset_ansi_jap_shift_jis, Charset_ansi_kor_hangul, Charset_ansi_kor_johab, CHARSET_ANSI_CHINESE_GBK, CHARSET_ Ansi_chinese_big5, Charset_ansi_greek, Charset_ansi_turkish, Charset_ansi_vietnamese, CHARSET_ANSI_HEBREW,Charset_ansi_arabic, Charset_ansi_baltic, Charset_ansi_cyrillic, Charset_ansi_thai, CHARSET_ANSI_LATIN_II, CHARSET_ Oem_latin_ifont.colour_index = Font.get_biff_record =? font.height = 0x00c8 # C8 in Hex (in decimal) = ten points in Heigh T.font.name =? font.outline =? font.shadow =? Setting the width of a cell sets the cell width of import Xltwworkbook = XLWT. Workbook () worksheet = Workbook.add_sheet (' My Sheet ') worksheet.write (0, 0, ' My Cell Contents ') worksheet.col (0). width = 3333 # 3333 = 1 "(one inch). Workbook.save (' Excel_workbook.xls ') Entering a date into a Cell write date import Xlwtimport Datetimew Orkbook = XLWT. Workbook () worksheet = Workbook.add_sheet (' My sheet ') style = XLWT. Xfstyle () style.num_format_str = ' M/d/yy ' # other options:d-mmm-yy, D-mmm, Mmm-yy, h:mm, H:mm:ss, h:mm, H:mm:ss, M/D/YY H: MM, MM:SS, [H]:mm:ss, Mm:ss.0worksheet.write (0, 0, Datetime.datetime.now (), style) Workbook.save (' Excel_workbook.xls ' ) Adding a Formula to a Cell add formula import Xlwtworkbook = XLWT. Workbook () worksheet = Workbook.add_sh(' My Sheet ') worksheet.write (0, 0, 5) # Outputs 5worksheet.write (0, 1, 2) # Outputs 2worksheet.write (1, 0, XLWT. Formula (' a1*b1 ') # should output "ten" (a1[5] * a2[2]) worksheet.write (1, 1, XLWT.  Formula (' SUM (A1,B1) ')) # should output "7" (A1[5] + a2[2]) workbook.save (' Excel_workbook.xls ') Adding a Hyperlink to a Cell Hyperlink Import Xlwtworkbook = XLWT. Workbook () worksheet = Workbook.add_sheet (' My sheet ') worksheet.write (0, 0, XLWT. Formula (' HYPERLINK ' ("http://www.google.com"; Google ")) # Outputs the text" Google "linking to Http://www.google.comworkbook.save (' Excel_workbook.xls ') merging Columns and rows merge rows and columns with import Xlwtworkbook = XLWT. Workbook () worksheet = Workbook.add_sheet (' My sheet ') worksheet.write_merge (0, 0, 0, 3, ' first merge ') # Merges row 0 ' s Colu MNS 0 through 3.font = xlwt. Font () # Create fontfont.bold = True # Set font to Boldstyle = XLWT.  Xfstyle () # Create Stylestyle.font = font # Add Bold font to Styleworksheet.write_merge (1, 2, 0, 3, ' Second merge ', style) # Merges row 1 through 2 ' s columns 0 through 3.workbook.save (' Excel_workbook.xls ') Setting the Alignment for the Contents of a Cell set alignment import Xlwtworkbook = XLWT. Workbook () worksheet = Workbook.add_sheet (' My sheet ') Alignment = XLWT. Alignment () # Create Alignmentalignment.horz = XLWT. Alignment.horz_center # May Be:horz_general, Horz_left, Horz_center, Horz_right, horz_filled, HORZ_JUSTIFIED, HORZ_ Center_across_sel, Horz_distributedalignment.vert = XLWT. Alignment.vert_center # May Be:vert_top, Vert_center, Vert_bottom, vert_justified, Vert_distributedstyle = XLWT. Xfstyle () # Create stylestyle.alignment = alignment # ADD alignment to Styleworksheet.write (0, 0, ' Cell Contents ', style) W Orkbook.save (' Excel_workbook.xls ') Adding Borders to a cell set cell border # Please note:while I were able to find these constants Within the source code, on my system (using LibreOffice,) I is only presented with a solid line, varying from thin to thi ck No dotted or dashed lines.import Xlwtworkbook = xlwt. Workbook () worksheet = WorkbOok.add_sheet (' My sheet ') borders = XLWT. Borders () # Create bordersborders.left = XLWT. Borders.dashed # May Be:no_line, THIN, MEDIUM, dashed, dotted, THICK, DOUBLE, HAIR, medium_dashed, thin_dash_dotted, MEDI um_dash_dotted, thin_dash_dot_dotted, medium_dash_dot_dotted, slanted_medium_dash_dotted, or 0x00 through 0x0d.borders.right = XLWT. Borders.DASHEDborders.top = XLWT. Borders.DASHEDborders.bottom = XLWT. Borders.DASHEDborders.left_colour = 0x40borders.right_colour = 0x40borders.top_colour = 0x40borders.bottom_colour = 0x40style = XLWT. Xfstyle () # Create stylestyle.borders = borders # Add borders to Styleworksheet.write (0, 0, ' Cell Contents ', style) Workboo K.save (' Excel_workbook.xls ') Setting the Background color of a cell sets the cell background color import Xlwtworkbook = XLWT. Workbook () worksheet = Workbook.add_sheet (' My sheet ') pattern = XLWT. Pattern () # Create The Patternpattern.pattern = XLWT. Pattern.solid_pattern # May Be:no_pattern, Solid_pattern, or 0x00 through 0x12pattern.pattern_fore_colour = 5 # MAy be:8 through 63. 0 = Black, 1 = white, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, = maroon, + = Dark Green, 18 = Dark Blue, Dark Yellow, almost brown), = Dark Magenta, = Teal, = Light gray, Dark Gray, the list goes On...style = XLWT. Xfstyle () # Create The Patternstyle.pattern = pattern # ADD pattern to Styleworksheet.write (0, 0, ' Cell Contents ', style) W Orkbook.save (' Excel_workbook.xls ') Todo:things left to Document-panes – separate views which is always in View-border Colors (documented above, but not taking effect as it should)-Border Widths (document above, and not working as expected) -Protection-row styles-zoom/manification-ws Props? Source Code for reference available at:https://secure.simplistix.co.uk/svn/xlwt/trunk/xlwt/

　　

Python XLWT Module