Introduction to Python methods for reading and writing Excel files

I. read excel

Here's a nice package, XLRS, that works on any platform. This means that you can read Excel files on Linux.

First, open the workbook;

import xlrd

wb = xlrd.open_workbook('myworkbook.xls')

Check form name:

wb.sheet_names()

You get the first form in two ways: the index and the name      

sh = wb.sheet_by_index(0)

sh = wb.sheet_by_name(u'Sheet1')

Recursively prints out the information for each line:    

for rownum in range(sh.nrows):

    print sh.row_values(rownum)

If you just want to return the first column of data:

first_column = sh.col_values(0)

[code]

 Reading data by index: 

[code]

cell_A1 =  sh.cell(0,0).value

cell_C4 = sh.cell(rowx=3,colx=2).value

Note: the indexes here all start at 0.

Second, write excel

Here's a nice package, XLWT, that works on any platform. This means that you can save Excel files on Linux.

Basic parts

Before writing to the Excel spreadsheet, you must initialize the workbook object and then add a workbook object. Such as:

import xlwt

wbk = xlwt.Workbook()

sheet = wbk.add_sheet('sheet 1')

The form is created, and writing data is easy:

# indexing is zero based, row then column

sheet.write(0,1,'test text')

After that, you can save the file (you don't need to close the file like you would open it) :

wbk.save('test.xls')

explore

A worksheet object that warns you when you change the contents of the form.

sheet.write(0,0,'test')

sheet.write(0,0,'oops')

 

# returns error:

# Exception: Attempt to overwrite cell:

# sheetname=u'sheet 1' rowx=0 colx=0

Solution: use cell_overwrite_ok=True to create the worksheet:

sheet2 =  wbk.add_sheet('sheet 2', cell_overwrite_ok=True)

sheet2.write(0,0,'some text')

sheet2.write(0,0,'this should overwrite')

Then you can change the contents of form 2.

More:

# Initialize a style

style = xlwt.XFStyle()

 

# Create a font to use with the style

font = xlwt.Font()

font.name = 'Times New Roman'

font.bold = True

 

# Set the style's font to this new one you set up

style.font = font

 

# Use the style when writing

sheet.write(0, 0, 'some bold Times text', style)

XLWT allows you to format each cell or entire row. It also allows you to add links and formulas. You can actually read the source code, where there are many examples:

      Dates.py, showing how to set different data formats
      Hyperlinks.py, showing how to create hyperlinks (hint: you need to use a formula)
      Merge.py, showing how to merge a grid
      Row_styles. Py, showing how to apply Style to an entire row of cells.

Iii. Modify excel

Python generally USES XLRD (excel read) to read excel files, and XLWT (excel write) to generate excel files (you can control the format of cells in excel). It is important to note that reading excel with XLRD cannot operate on it: the xlrd.open_workbook() method returns type xlrd.book, which is read-only and cannot be operated on. The xlwt.workbook () method returns the xlwt.workbook type save(filepath) to save the excel file.

Therefore, it is easy to read and generate Excel files, but it is troublesome to modify existing Excel files. However, there is also the ability for xlutils (which depends on XLRD and XLWT) to copy the contents of excel files and modify them. It's really just a pipeline between xlrd.book and xlwt.workbook, as shown below:

The copy() method of xlutils.copy module implements this function. The sample code is as follows:

from xlrd import open_workbook

from xlutils.copy import copy

 

rb = open_workbook('m:\1.xls')

# sheet obtained through sheet_by_index() has no write() method

rs = rb.sheet_by_index(0)

 

wb = copy(rb)
# through get_sheet() To obtain the sheet There are write() methods 

ws = wb.get_sheet(0)

ws.write(0, 0, 'changed!')

 

wb.save('m:\1.xls')

Four, reference

(link: http://pypi.python.org/pypi/xlrd)
(link: http://pypi.python.org/pypi/xlwt)
(link: http://pypi.python.org/pypi/xlutils)