I am new for Ironpython for ASP.net, earlier i was using only Visual studio
2005 and struggling with to read excel file without M$ Excel object, bt i
could nt get any other object which could read excel sheet. But suddenly i
got one
Lingfo – Python "xlrd" package for extracting data from Excel files – 0.6.1final
which can read successfully excel sheet as i desired.but the problem is i
dont knw hw can i use with IronPython Asp.net(Microsoft IronPython for
ASP.NET). i have seperately installed Python2.4..
i tried with python2.4 its working failrly.while installing xlrd it
automatically search D:\Python24\, there were no option to configure it nad
installed in D:\Python24\Lib\site-packages\.
Then i copy the XLRD package and paste C:\Program Files\Microsoft IronPython
for ASP.NET\Lib\site-packages\
and wrote the python script ExcelModule.py
class readexcel(object):
def __init__(self,filename):
""" Returns a readexcel object of the specified filename - this may
take a little while because the file must be parsed into memory """
import sys
sys.path.append('D:\Python24\Lib')
sys.path.append('D:\Python24\Lib\site-packages')
from xlrd import *
print dir(xlrd)
return printed
import os.path
if not os.path.isfile(filename):
raise NameError, "%s is not a valid filename" % filename
self.__filename__ = filename
self.__book__ = xlrd.open_workbook(filename)
self.__sheets__ = {}
self.__sheetnames__ = []
for i in self.__book__.sheet_names():
uniquevars = []
firstrow = 0
sheet = self.__book__.sheet_by_name(i)
for row in range(sheet.nrows):
types,values = sheet.row_types(row),sheet.row_values(row)
nonblank = False
for j in values:
if j != '':
nonblank=True
break
if nonblank:
# Generate a listing of Unique Variable Names for Use as
# Dictionary Keys In Extraction. Duplicate Names will
# be replaced with "F#"
variables = self.__formatrow__(types,values,False)
unknown = 1
while variables:
var = variables.pop(0)
if var in uniquevars or var == '':
var = 'F' + str(unknown)
unknown += 1
uniquevars.append(str(var))
firstrow = row + 1
break
self.__sheetnames__.append(i)
self.__sheets__.setdefault(i,{}).__setitem__('rows',sheet.nrows)
self.__sheets__.setdefault(i,{}).__setitem__('cols',sheet.ncols)
self.__sheets__.setdefault(i,{}).__setitem__('firstrow',firstrow)
self.__sheets__.setdefault(i,{}).__setitem__('variables',uniquevars[:])
def getiter(self, sheetname, returnlist=False, returntupledate=False):
""" Return an generator object which yields the lines of a
worksheet;
Default returns a dictionary, specifing returnlist=True causes lists
to be returned. Calling returntupledate=True causes dates to
returned
as tuples of (Year, Month, Day, Hour, Min, Second) instead of as a
string """
if sheetname not in self.__sheets__.keys():
raise NameError, "%s is not present in %s" % (sheetname,\
self.__filename__)
if returnlist:
return __iterlist__(self, sheetname, returntupledate)
else:
return __iterdict__(self, sheetname, returntupledate)
def worksheets(self):
""" Returns a list of the Worksheets in the Excel File """
return self.__sheetnames__
def nrows(self, worksheet):
""" Return the number of rows in a worksheet """
return self.__sheets__[worksheet]['rows']
def ncols(self, worksheet):
""" Return the number of columns in a worksheet """
return self.__sheets__[worksheet]['cols']
def variables(self,worksheet):
""" Returns a list of Column Names in the file,
assuming a tabular format of course. """
return self.__sheets__[worksheet]['variables']
def __formatrow__(self, types, values, wanttupledate):
""" Internal function used to clean up the incoming excel data """
## Data Type Codes:
## EMPTY 0
## TEXT 1 a Unicode string
## NUMBER 2 float
## DATE 3 float
## BOOLEAN 4 int; 1 means TRUE, 0 means FALSE
## ERROR 5
#import xlrd
returnrow = []
for i in range(len(types)):
type,value = types[i],values[i]
if type == 2:
if value == int(value):
value = int(value)
elif type == 3:
datetuple = xlrd.xldate_as_tuple(value,
self.__book__.datemode)
if wanttupledate:
value = datetuple
else:
# time only no date component
if datetuple[0] == 0 and datetuple[1] == 0 and \
datetuple[2] == 0:
value = "%02d:%02d:%02d" % datetuple[3:]
# date only, no time
elif datetuple[3] == 0 and datetuple[4] == 0 and \
datetuple[5] == 0:
value = "%04d/%02d/%02d" % datetuple[:3]
else: # full date
value = "%04d/%02d/%02d %02d:%02d:%02d" % datetuple
elif type == 5:
value = xlrd.error_text_from_code[value]
returnrow.append(value)
return returnrow
def __iterlist__(excel, sheetname, tupledate):
""" Function Used To Create the List Iterator """
sheet = excel.__book__.sheet_by_name(sheetname)
for row in range(excel.__sheets__[sheetname]['rows']):
types,values = sheet.row_types(row),sheet.row_values(row)
yield excel.__formatrow__(types, values, tupledate)
def __iterdict__(excel, sheetname, tupledate):
""" Function Used To Create the Dictionary Iterator """
sheet = excel.__book__.sheet_by_name(sheetname)
for row in range(excel.__sheets__[sheetname]['firstrow'],\
excel.__sheets__[sheetname]['rows']):
types,values = sheet.row_types(row),sheet.row_values(row)
formattedrow = excel.__formatrow__(types, values, tupledate)
# Pad a Short Row With Blanks if Needed
for i in range(len(formattedrow),\
len(excel.__sheets__[sheetname]['variables'])):
formattedrow.append('')
yield
dict(zip(excel.__sheets__[sheetname]['variables'],formattedrow))
and in default.aspx.py
import System
import clr
from System.Data import *
from System.Web import *
from System.Web.UI import *
#from IronPython.Hosting import PythonEngine
from clr import *
import SampleModule
import sys
#import SampleModule2
#import ExcelModule
#from ExcelModule import readexcel
from SampleModule import SampleClass
from SampleModule import readexcel
if sys.version.startswith("IronPython"):
# print >> sys.stderr, "...importing encodings"
import encodings
sys.path.append([r'd:\Python24\Lib',r'd:\Python24\Lib\site-packages\xlrd',])
#sys.path.extend([r,'C:\Program Files\Microsoft IronPython for
ASP.NET',r'C:\Program
Files\Micrsoft IronPython for ASP.NET\Lib',r'C:\\Program Files\\Microsoft
IronPython forASP.NET\\lib\\site-packages',r'D:\\python24\\lib',])
import xlrd as ebook
book = ebook.open_workbook('D:\my.xls', encoding_override="none")
#book=xlrd.open_workbook("C:\Program Files\Microsoft IronPython for
ASP.NET\Lib\site-packages\xlrd\examples:\namesdemo.xls"
def Page_Load():
pass
_PrivateMethod("1")
PublicMethod("2")
def _PrivateMethod(text):
lblPrivate.Text="private %s" %text
def PublicMethod(text):
lblPublic.Text="public %s" %text
def Private_Click(sender,args):
_PrivateMethod("3")
def Button1_Click(sender, args):
ss = readexcel(dir);
print ss
book = xlrd.open_workbook('D:\my.xls', encoding_override="cp1252")
#sc = SampleClass()
#excel=readexcel('D:\my.xls')
#sc.TestString = TextBox1.Text
#Label1.Text = sc.TestString
#Label1.Visible="True"
#py=PythonEngine()
#values = {'string': 'Hello World'}
#mod = py.CreateModule('__main__', values, True)
#py.DefaultModule = mod
#script = "print string"
#py.ExecuteToConsole(script)
error-- value can not be null
encoding
please any one can help me out how can i use xlrd module in Microsoft visual
studio 2005 with IronPython(i installed IronPython with Microsoft.net CTP)
Praveen Kumar
Mahiti Infotech Pvt. Ltd.
# 33-34, Hennur Cross
Hennur Main Road
Bangalore, India - 560043
Mobile: +91 9343297314
Phone: +91 80 4148 5080/1 41150580/1
http://www.mahiti.org
_______________________________________________
Users mailing list
Users@lists.ironpython.com
http://lists.ironpython.com/listinfo.cgi/users-ironpython.com
