How to read and write Excel with Python _python

The example in this article tells you how to read and write excel in Python. Share to everyone for your reference. Specifically as follows:

Recently you need to organize some data from multiple Excel tables in a variety of ways, although it used to be done in Java, but because of the recent learning of Python, of course I decided to try it with Python. Found python much simpler. Here's a quick record. (Because of what to learn, so not too deep, master do not spray, welcome guidance)

First, read Excel table

Read Excel to use the XLRD module, official website installation (HTTP://PYPI.PYTHON.ORG/PYPI/XLRD). Then you can follow the example inside to try it out and know how to use it. The approximate process is this:

1. Import Module

Copy Code code as follows:

Import xlrd

2, open the Excel file read data

Copy Code code as follows:

data = Xlrd.open_workbook (' Excel.xls ')

3. Get a Worksheet

①table = data.sheets () [0] #通过索引顺序获取
②table = Data.sheet_by_index (0) #通过索引顺序获取
③table = data.sheet_by_name (U ' Sheet1 ') #通过名称获取
4, get the whole row and the value of the entire column (return array)

Copy Code code as follows:

Table.row_values (i)
Table.col_values (i)

5, get the number of rows and columns

Copy Code code as follows:

Table.nrows
Table.ncols

6, get the cells

Copy Code code as follows:

Table.cell (0,0). Value
Table.cell (2,3). Value

When I use it, I think it's most useful to get the cell, which is equivalent to giving you a two-dimensional array, and you can do whatever you want with the rest. Thanks to this very handy library code is very concise. But there are a number of pits that have led to a certain time of exploration. Now for posterity's reference:

1, first of all, my statistics are based on the names of the information in each table, but the debugging found that different tables in each name seemingly can not match, began to suspect coding problems, but later found to be because of space. Because when you type in Excel It's likely that you'll be able to add a few spaces or tabs behind some names, which may seem like a lot of difference, but when it comes to processing, that's two completely different strings. My solution is to add a strip () to each fetch string. Good effect

2, or string matching, to determine whether a cell in the string (Chinese) is equal to what I have given the time to find no match, and a variety of Unicode is not very effective, hundreds of solutions, but are more complex or useless. Finally, I used a more flexible way: directly from Excel to get the value I want to compare, the effect is good is not universal line is not very good, one can not solve the problem.
Second, write Excel table

Write Excel table to use the XLWT module, website download (HTTP://PYPI.PYTHON.ORG/PYPI/XLWT). The general use of the process is as follows:

1. Import Module

Copy Code code as follows:

Import XLWT

2, create workbook (in fact, Excel, and then save the line)

Copy Code code as follows:

Workbook = xlwt. Workbook (encoding = ' ASCII ')

3. Create a table

Copy Code code as follows:

Worksheet = Workbook.add_sheet (' My worksheet ')

4, to the cell to write content

Copy Code code as follows:

Worksheet.write (0, 0, label = ' Row 0, Column 0 Value ')

5, Save

Copy Code code as follows:

Workbook.save (' Excel_workbook.xls ')

Because my demand is relatively simple, so there is no problem on the top, the only suggestion or ASCII encoding, otherwise there may be some strange phenomenon.

Of course the XLWT function is much more than that, he can even set various styles and so on. Attach a little example

Copy Code code as follows:

Examples generating Excel Documents Using Python ' s XLWT

Here is are some simple examples using the Python ' s XLWT library to dynamically generate Excel documents.

Please note a useful alternative may is EZODF, which allows to generate ODS (Open Document spreadsheet) files for LIBR Eoffice/openoffice. Can check 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 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 "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
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, ' the ' a ') # 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
# Please note:while I am able to find this constants within the source code, on my system (using LibreOffice,) I is 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 Contents ', 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/

I hope this article will help you with your Python programming.