Ask Your Question
2

excelpy reading excel files

asked 2012-11-13 11:36:19 -0500

Mongo gravatar image

updated 2012-11-13 13:38:44 -0500

Hi all,

Please, does anyone have an example of how to use excelpy to READ an excel file ? I know XLRD, but I can't have it installed in my machine quickly,

I want to retrieve all data on column A into a new array in Python, please how do I do that ?

PSS/E's API is rich when describing how to write to excel, but not how to read from it.

Thank you very much,

Andre

edit retag flag offensive close merge delete

3 answers

Sort by ยป oldest newest most voted
1

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

chip gravatar image

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

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
edit flag offensive delete link more

Comments

Thanks Chip, actually I am familiar with XLRD, but I have a new job, new PC, and can't install XLRD without going through all the hassle of asking for authorization, etc, etc; that's why I am looking for an example of excelpy for reading. Thanks, Andre

Mongo gravatar imageMongo ( 2012-11-13 13:02:19 -0500 )edit

Hi Andre, you can install xlrd without "installing" it too. Just download and unzip the xlrd zip and copy the xlrd folder into your project folder. Python searches the local directory for imports.

JervisW gravatar imageJervisW ( 2012-11-13 15:28:19 -0500 )edit

Thanks a lot Jervis !

Mongo gravatar imageMongo ( 2012-11-13 16:20:24 -0500 )edit

Jervis, please where exactly may I put the XLRD folder ? Inside C:\Python27 ? Or inside the folder where MY PY file is located ? Thanks, Andre

Mongo gravatar imageMongo ( 2012-12-11 13:11:14 -0500 )edit

You can put it in the same folder where your .py file is located. The other place that Python looks is `c:\python27\Lib\site-packages\` but you probably won't have IT privileges to put xlrd there.

JervisW gravatar imageJervisW ( 2012-12-12 17:15:59 -0500 )edit
0

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

jfconroy gravatar image

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

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.

edit flag offensive delete link more
0

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

jconto gravatar image

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()
edit flag offensive delete link more

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account.

Add Answer

[hide preview]

Question Tools

1 follower

Stats

Asked: 2012-11-13 11:36:19 -0500

Seen: 1,837 times

Last updated: Sep 11