How Python uses the XLWT module to manipulate Excel

This article mainly and you introduced the Python Use XLWT module operation Excel method, combined with the instance form analysis Python installs the XLWT module and uses the XLWT module to Excel file creation, the establishment, the preservation and so on common operation skill, needs the friend to refer, Hope to help everyone.

Section from official website documentation.

The module is simple to install

$ pip Install XLWT

Let's start with a simple example:

#!/usr/bin/python#coding=utf-8# ==============================================================================# #    filename:demo.py#  description:excel operat#    created:tue Apr 17:10:33 CST 2017#     author:yur## = = ==========================================================================import xlwt# Create a workbook setting encoding workbook = XLWT. Workbook (encoding = ' utf-8 ') # creates a worksheetworksheet = Workbook.add_sheet (' My Worksheet ') # write excel# parameter corresponding to row, column, value Worksheet . Write (1,0, label = ' This is Test ') # Save Workbook.save (' Excel_test.xls ')

After running, a excel_test.xls is generated in the current directory

Official examples:

Error when running this example

I wrote another one:

#!/usr/bin/python#coding=utf-8# ==============================================================================# #    filename:style.py#  description:style#    created:thu Apr 15:07:53 CST 2017#     author:yur## ========= =====================================================================import Xlwtworkbook = xlwt. Workbook (encoding = ' ASCII ') worksheet = Workbook.add_sheet (' My worksheet ') style = XLWT. Xfstyle () # Initialize style font = XLWT. Font () # creates fonts for styles Font.Name = ' Times New Roman ' Font.Bold = true # blackbody Font.underline = true # underscore font.italic = true # italic style . Font = font # set style worksheet.write (0, 0, ' unformatted value ') # Write without style worksheet.write (1, 0, ' formatted value ', style) # Band Style write Workbook.save (' Formatting.xls ') # Save file

Effect:

Set cell width:

Import Xlwtworkbook = XLWT. Workbook () worksheet = Workbook.add_sheet (' My Sheet ') worksheet.write (0, 0, ' My cell Contents ') # Set cell width worksheet.col (0) . width = 3333workbook.save (' Cell_width.xls ')

Enter a date to the cell:

Import Xlwtimport Datetimeworkbook = 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 ' )

Add a formula to a cell:

Import Xlwtworkbook = XLWT.  Workbook () worksheet = Workbook.add_sheet (' 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 ')

To add a hyperlink to a cell:

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 ')

Merge columns and rows:

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 ')

To set the contents of a cell to its way:

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 ')

To add a border 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 Xlwtworkbook = xlwt. Workbook () worksheet = Workbook.add_sheet (' My sheet ') borders = XLWT. Borders () # Create bordersborders.left = XLWT. Borders.dashed dashed dashed no_line no THIN solid line # May Be:no_line, THIN, MEDIUM, dashed, dotted, THICK, DOUBLE, HAIR, Medium_da Shed, thin_dash_dotted, medium_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 ')

To set the background color for a cell:

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 ')