Python XLWT write Excel format controls color, mode, encoding, background color

Source: Internet
Author: User

About writing Excel formatting controls, such as color, etc.

1 ImportXLWT2  fromDatetimeImportdatetime3   4Font0 =XLWT. Font ()5Font0.name ='Times New Roman'6Font0.colour_index = 27Font0.bold =True8   9STYLE0 =XLWT. Xfstyle ()TenStyle0.font =font0 One    AStyle1 =XLWT. Xfstyle () -Style1.num_format_str ='D-mmm-yy' -    theWB =XLWT. Workbook () -WS = Wb.add_sheet ('A Test Sheet') -    -Ws.write (0, 0,'Test', STYLE0) +Ws.write (1, 0, DateTime.Now (), Style1) -Ws.write (2, 0, 1) +Ws.write (2, 1, 1) AWs.write (2, 2, XLWT. Formula ("a3+b3")) at    -Wb.save ('Example.xls')
Examples generating Excel Documents Using Python ' s XLWT

Here is some simple examples using Python's XLWT library to dynamically 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.html

The simplest Example
import xlwt
workbook = xlwt.Workbook(encoding = ‘ascii‘)
worksheet = workbook.add_sheet(‘My Worksheet‘)
worksheet.write(0, 0, label = ‘Row 0, Column 0 Value‘)
workbook.save(‘Excel_Workbook.xls‘)

Formatting the Contents of a Cell
import xlwt
workbook = xlwt.Workbook(encoding = ‘ascii‘)
worksheet = workbook.add_sheet(‘My Worksheet‘)
font = xlwt.Font() # Create the Font
font.name = ‘Times New Roman‘
font.bold = True
font.underline = True
font.italic = True
style = xlwt.XFStyle() # Create the Style
style.font = font # Apply the Font to the Style
worksheet.write(0, 0, label = ‘Unformatted value‘)
worksheet.write(1, 0, label = ‘Formatted value‘, style) # Apply the Style to the Cell
workbook.save(‘Excel_Workbook.xls‘)

Attributes of the Font Object
Font.Bold = True # May be:true, False
Font.Italic = True # May be:true, False
Font.struck_out = True # May be:true, False
Font.underline = XLWT. Font.underline_single # May Be:underline_none, Underline_single, UNDERLINE_SINGLE_ACC, underline_double, UNDERLINE_ Double_acc
Font.escapement = XLWT. Font.escapement_superscript # May Be:escapement_none, Escapement_superscript, Escapement_subscript
font.family = XLWT. Font.family_roman # May Be:family_none, Family_roman, Family_swiss, Family_modern, Family_script, FAMILY_DECORATIVE
Font.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_i
Font.colour_index =?
Font.get_biff_record =?
Font.height = 0x00c8 # C8 in Hex (in decimal) = ten points in height.
Font.Name =?
Font.outline =?
Font.shadow =?

Setting the Width of a Cell
import xltw
workbook = 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
import xlwt
import datetime
workbook = 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.0
worksheet.write(0, 0, datetime.datetime.now(), style)
workbook.save(‘Excel_Workbook.xls‘)

Adding a Formula to a Cell
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet(‘My Sheet‘)
worksheet.write(0, 0, 5) # Outputs 5
worksheet.write(0, 1, 2) # Outputs 2
worksheet.write(1, 0, xlwt.Formula(‘A1*B1‘)) # Should output "10" (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
import xlwt
workbook = 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.com
workbook.save(‘Excel_Workbook.xls‘)

Merging Columns and Rows
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet(‘My Sheet‘)
worksheet.write_merge(0, 0, 0, 3, ‘First Merge‘) # Merges row 0‘s columns 0 through 3.
font = xlwt.Font() # Create Font
font.bold = True # Set font to Bold
style = xlwt.XFStyle() # Create Style
style.font = font # Add Bold Font to Style
worksheet.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
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet(‘My Sheet‘)
alignment = xlwt.Alignment() # Create Alignment
alignment.horz = xlwt.Alignment.HORZ_CENTER # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
alignment.vert = xlwt.Alignment.VERT_CENTER # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
style = xlwt.XFStyle() # Create Style
style.alignment = alignment # Add Alignment to Style
worksheet.write(0, 0, ‘Cell Contents‘, style)
workbook.save(‘Excel_Workbook.xls‘)

Adding Borders to a Cell
# Note:while I was able to find these constants within the source code, on my system (using LibreOffice,) I am on Ly presented with a solid line, varying from thin to thick; No dotted or dashed lines.
Import XLWT
Workbook = xlwt. Workbook ()
Worksheet = Workbook.add_sheet (' My sheet ')
Borders = XLWT. Borders () # Create Borders
Borders.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.dashed
Borders.top = XLWT. Borders.dashed
Borders.bottom = XLWT. Borders.dashed
Borders.left_colour = 0x40
Borders.right_colour = 0x40
Borders.top_colour = 0x40
Borders.bottom_colour = 0x40
style = XLWT. Xfstyle () # Create Style
Style.borders = borders # Add borders to Style
Worksheet.write (0, 0, ' Cell Contents ', style)
Workbook.save (' Excel_workbook.xls ')

Setting The Background Color of a Cell
import xlwt
Workbook = xlwt. Workbook ()
worksheet = workbook.add_sheet (' My sheet ')
pattern = xlwt. Pattern () # Create the pattern
Pattern.pattern = XLWT. pattern.solid_pattern # May Be:no_pattern, Solid_pattern, or 0x00 through 0x12
Pattern.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 pattern
Style.pattern = pattern # Add pattern to style
Worksheet.write (0, 0, ' Cell Conte NTS ', style)
Workbook.save (' Excel_workbook.xls ')

Todo:things left to Document
- Panes -- separate views which are always in view
- Border Colors (documented above, but not taking effect as it should)
- Border Widths (document above, but 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/

http://www.youlikeprogramming.com/2011/04/examples-generating-excel-documents-using-pythons-xlwt/

Python XLWT write Excel format controls color, mode, encoding, background color

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.