How to read and write excel using python

This article mainly introduces how to read and write excel using python. it involves the application of xlrd module and xlwt module and has some learning reference value, for more information about how to read and write excel using python, see the following example. Share it with you for your reference. The details are as follows:

Recently, some data needs to be organized in various ways from multiple excel tables. although java has been used for such operations, so of course I decided to try it with python. Python is much simpler. Here is a simple record. (Because it is used to learn what to learn, it is not too deep, experts do not spray, welcome to guide)

1. read an excel table

Read excel to use xlrd module, official website installation (http://pypi.python.org/pypi/xlrd ). Then you can try the example in the example to know how to use it. The general process is as follows:

1. import Module

The code is as follows:

Import xlrd

2. open an Excel file to read data

The code is as follows:

Data = xlrd.open_workbook('excel.xls ')

3. get a worksheet

① Table = data. sheets () [0] # obtain from index order
② Table = data. sheet_by_index (0) # obtained by index order
③ Table = data. sheet_by_name (u 'sheet1') # obtain it by name
4. get the value of the whole row and the whole column (return array)

The code is as follows:

Table. row_values (I)
Table. col_values (I)

5. get the number of rows and columns

The code is as follows:

Table. nrows
Table. ncols

6. get cells

The code is as follows:

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

When I used it myself, I thought it was still the most useful to get the cell. this is equivalent to giving you a two-dimensional array, and you can do whatever you want. This very useful library code is very concise. However, there are still a number of pitfalls that lead to some time to explore. List them for future reference:

1. first, my statistics are based on the name of each table, but debugging found that different names in different tables do not seem to be able to match, and began to suspect the encoding problem, but it was later found that it was due to space. It seems that there is no difference when I enter a few spaces or tabs after some names in excel, however, during Program processing, this is two completely different strings. My solution is to add strip () to each obtained string for processing. Good results

2. it is also a String Matching. when determining whether the string (Chinese) in a cell is equal to what I have provided, it cannot be matched, and unicode does not work well. baidu has some solutions, but they are both complicated or useless. Finally, I adopted a more flexible method: directly obtain the value I want from excel and then compare it. The effect is good, that is, the general-purpose row is not very good, it cannot be solved.
II. write an excel table

Write excel table to use xlwt module, download the official website (http://pypi.python.org/pypi/xlwt ). The general procedure is as follows:

1. import Module

The code is as follows:

Import xlwt

2. create a workbook (in fact, it is excel, and save it later)

The code is as follows:

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

3. create a table

The code is as follows:

Worksheet = workbook. add_sheet ('My Worksheet ')

4. write content into cells

The code is as follows:

Worksheet. write (0, 0, label = 'row 0, Column 0 value ')

5. Save

The code is as follows:

Workbook.save('Excel_Workbook.xls ')

My requirements are relatively simple, so there is no problem above. The only difference is that we recommend using ascii encoding. Otherwise, there may be some strange phenomena.

Of course, xlwt functions are far more than that. it can even set various styles. Attached example

The code is as follows:

Examples Generating Excel Documents Ents Using Python's xlwt

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

Please note a useful alternative may be ezodf, which allows you to generate ODS (Open Document Spreadsheet) files for LibreOffice/OpenOffice. You 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 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. pipeline # May be: Pipeline, CHARSET_SYS_DEFAULT, CHARSET_SYMBOL, CHARSET_APPLE_ROMAN, clerk, CHARSET_ANSI_GREEK, clerk, CHARSET_ANSI_BALTIC, clerk, CHARSET_ANSI_LATIN_II, CHARSET_OEM_LATIN_ I
Font. colour_index =?
Font. get_biff_record =?
Font. height = 0x00C8 # C8 in Hex (in decimal) = 10 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 ') # shocould output "10" (A1 [5] * A2 [2])
Worksheet. write (1, 1, xlwt. Formula ('sum (A1, B1) ') # shocould 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, warning, 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 was able to find these constants within the source code, on my system (using LibreOffice,) I was only 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, encrypted, encrypted, 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, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow, almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = 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 (incluented above, but not taking effect as it shoshould)
-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 Python programming.