excelpy reading excel files

answered 2012-11-13 12:36:14 -0600

chip
419 ●5 ●9 ●22

updated 2012-11-13 12:44:44 -0600

I actually prefer to work with xlrd if I need to muck about with excel spreadsheets. It's also much easier to use than the COM API from win32com.

xlrd Pros:

• Fully documented
• Lots of examples
• You don't need a PSSE license

xlrd Cons:

• It's an extra package you have to install.

I know this isn't a direct answer to your question. I went to grab an excelpy example from my scripts, but I realized I am not using it because of the other tools available. If memory serves, I tried once and decided to stick with xlrd/xlwt. There is also openpyxl. It looks like a nice package, but I haven't tried it yet.

xlrd Example

Here's an example of workbook navigation from the docs::

from xlrd import open_workbook
wb = open_workbook('simple.xls')
for s in wb.sheets():
    print 'Sheet:',s.name
    for row in range(s.nrows):
        values = []
        for col in range(s.ncols):
            values.append(s.cell(row,col).value)
        print ','.join(values)
    print

answered 2018-09-11 17:16:30 -0600

jconto
2701 ●10 ●30 ●41

An example of writing & reading excel files using excelpy follows: (change psse34 to psse33 in line 3 for PSSe v.33)

#excelpy_demo
#______________________________________________________________
import psse34
#import psse33
#______________________________________________________________
import excelpy
xlsfile = r"excelpy_test.xlsx"
testxls = excelpy.workbook(xlsfile,"test",overwritesheet=True, mode='w')
#_____________________Writing test_____________________________
testxls.show()
testxls.font_sheet()
testdata=[
          ['CON#', 'LABEL'    , 'Max MW', 'DESCRIPTION'],
          [' ', 'BASE CASE'   , 756.25  , 'BASE CASE '],
          ['1', 'TRIP1NUCLEAR', 1000.0  , 'REMOVE UNIT 1'],
          ['2', 'TRIP2NUCLEAR', 0.0     , 'REMOVE UNIT 1'],
          ['' , ''            , ''      , 'REMOVE UNIT 2'],
          ['3', 'ADDLARGELOAD', 0.0     , 'INCREASE LOAD'],
          ['4', 'LOSEWESTGEN' , 625.0   , 'REMOVE UNIT 3'],
          ['5', 'LOSEWESTBIGT', 312.5   , 'TRIP LINE 1 '],
         ]
#Within a sheet, goto target cell (1,1), add title
row=1
col=1
testxls.set_cell(value="How to use EXCELPY module?",
                 address=(row,col),
                 fontName="Arial",fontSize=12,fontColor='red',fontStyle=('Bold','italic'))
row += 2  #add one blank row -> (3,1)
bottomRow, rightCol = testxls.set_range(row,col,testdata,transpose=False)
# writing is done, do some coloring & formatting
testxls.font ((row,1,row,rightCol),fontStyle='bold',fontColor='blue')   #<- cell (3,1)
testxls.align((row,1,row,rightCol),alignv='h_center')
testxls.align((row,1,bottomRow,2) ,alignv='h_center')
testxls.autofit_columns((row,2,row,rightCol))
testxls.font((row,3,bottomRow,3),numberFormat='0.000')
#repeat with transpose ON
row = 2 + bottomRow
bottomRow, rightCol = testxls.set_range(row,col,testdata,transpose=True)

testxls.save(xlsfile)
print "Writing demo:"
print "Excel workbook saved to %s file.\n"% xlsfile
testxls.close()
#______________________________________________________________
#_____________________Reading test_____________________________
import excelpy
xlsfile = r"excelpy_test.xlsx"
print "Reading demo:"
print "Data read from Excel %s file.\n"% xlsfile
testxls = excelpy.workbook(xlsfile,"test",mode='r')
row=1
col=1
print testxls.get_cell((row,col))
row=3
col=1
rowe= row+3
#datalst = testxls.get_range(((row,col),(rowe,col+3)))
datalst = testxls.get_range((row,col,rowe,col+3))
for x in datalst: print x
testxls.close()

answered 2018-09-10 06:21:26 -0600

jfconroy
176 ●11 ●14 ●18

updated 2018-09-10 06:24:24 -0600

Hello Andre, I have shared your frustration with finding out how to read data from an Excel file using excelpy.

The v34.3.1 documentation for excelpy lists the functions, but the alphabetical list goes directly from the "freezepanes" function to the "height" function. For some unknown reason, it does not list the following two (very useful) functions.

 |  get_cell(self, address, sheet=None)
 |      Get value from one cell.
 |  
 |  get_range(self, address, transpose=False, sheet=None)
 |      Get data from cells in the range.
 |      data = get_range(address, ...)
 |      Inputs:
 |      address   - range tuple specified as (topRow, leftCol, bottomRow, rightCol)
 |      transpose - Logical
 |          Excel methods read data row by row and returned as Python list of lists.
 |          transpose = False
 |              Each returned list represents data in a speadsheet row.
 |          transpose = True
 |              Each returned list represents data in a speadsheet column.
 |      Returns:
 |          data = List or List of Lists values.

You can see all of the documentation for the excelpy functions by importing excelpy and then running help(excelpy).

This should save you the hassle of installing additional module(s) to read Excel files. Hopefully PSSE will update their documentation so that more users are not confused as you and me were.