extracting data from excel cells

[8ginga8]

i have an excel sheet that is layed out like a form, where some one enters data. So the data that I need to extract from the excel sheet is kind of all over the place. for example a product name could be at cell B3 and its id number at H3. Looking at the print out it looks like a form. Is there anyway to achieve what I am trying to do.

 

[rkolosky]

Using Apache POI (

http://jakarta.apache.org/poi/),

you can load a Java object that can read Excel sheets.

Dave Ross (

http://www.d-ross.org/index.cfm?objectid=9C65ECEC-508B-E116-6F8A9F878188D7CA)

has a blog post about how to do this. It looks like a lot of coding, but it's pretty simple once you start.

I used this in one of my applications and it works very well.

 

[8ginga8]

Thanks, but that app looks more like creating an excel sheet and not extracting from one. I may be wrong.

 

[rkolosky]

While you can definitely create an Excel spreadsheet using Apache POI, you can also read from one.

You would upload the Excel spreadsheet into somewhere Cold Fusion can see it, and then tell it which cells to read and the variables to store the data in.

Here's the code I use to test if the values given to my application from the Excel sheet are any good or if the file should be rejected:

<cfset fileIn = createObject("java","java.io.FileInputStream").init("#fileName#")/>
<cfset fs = createObject("java","org.apache.poi.poifs.filesystem.POIFSFileSystem").init(fileIn)/>
<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init(fs)/>
<cfset sheet = wb.getSheetAt(0)>
<cfset row = sheet.getRow(0) />
<cfset firstCell = row.getFirstCellNum() />
<cfset lastCell = row.getLastCellNum() />
<cfset cellNames = "Project ID,Project Name,Type,Bid Date,Local,County,State,Low Bidder,U/N,Project Amount,DC,Region">
<cfloop from="#firstCell#" to="#DecrementValue(lastCell)#" index="thisCellNum">
<cfset cell = row.getCell(JavaCast("long",thisCellNum)) />
<cfset thisCellType = cell.getCellType() />
<cfswitch expression="#thisCellType#">
  <cfcase value="1">
    <cfif cell.getStringCellValue() eq ListGetAt(cellNames,IncrementValue(thisCellNum))>
    <cfelse>
      <cfset fileGood = false />
    </cfif>
  </cfcase>
  <cfdefaultcase>
    <cfset fileGood = false />
  </cfdefaultcase>
</cfswitch>
</cfloop>

 

[8ginga8]

so you have to be running apache, I am currently running IIS

 

[rkolosky]

No, I have this running under IIS as well.

The Apache Foundatin is just providing services for POI, they don't require you to be running Apache as the webserver.

 

[8ginga8]

thanks

 

[TruthInSatire]

another hack, and this is a serious hack....

create a datasource to an access database. link to the excel file from access and query the excel file like an access table.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[8ginga8]

oh, so let me get this straight, you are saying to create a link between access and excel then query the access database with coldfusion

 

[TruthInSatire]

yeah, pretty much.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[8ginga8]

ok, i will try that, so doing that I will not have to use the script that you had mentioned in the beginning

 

[TruthInSatire]

if its a flat xls folder, you can also save it as a csv file and use cfhttp.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[8ginga8]

cfhttp will allow me to grab specific cells?

 

[TruthInSatire]

cfhttp returns a query object.

http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-p58.htm#wp1632966

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[8ginga8]

another question, a few of the fields in the excel sheet are actually form elements "text boxes" which are not cells, is this possible to extract this data aswell

 

[8ginga8]

with the code that you described above

Using Apache POI (

http://jakarta.apache.org/poi/),

you can load a Java object that can read Excel sheets.

Dave Ross (

http://www.d-ross.org/index.cfm?objectid=9C65ECEC-508B-E116-6F8A9F878188D7CA)

has a blog post about how to do this. It looks like a lot of coding, but it's pretty simple once you start.

I used this in one of my applications and it works very well.

do you need to install any software on the coldfusion server

 

[rkolosky]

Yes, you have to download poi from the Apache POI site and then copy the JAR somewhere on your Cold Fusion server. You would then go to the Cold Fusion Administrator and add the JAR into the class path.

 

[8ginga8]

Thanks, I am assuming when you say a jar file you mean this one "poi-2.5.1-final-20040804.jar

 

[rkolosky]

Actually, I downloaded three JARs:

poi-2.5.1-final-20040804.jar, poi-contrib-2.5.1-final-20040804.jar, poi-scratchpad-2.5.1-final-20040804.jar

I then put them into CFusionMX\

http://wwwroot\WEB-INF\lib\

and then went to the CF Administrator -> Java and JVM Settings and put the exact locations of the JAR files into the Class Path box. I hit Submit CHnages and then CF told me that it needed to be restarted. I restarted and was able to invoke the POI objects immediately.

 

[8ginga8]

thanks, i will try it and let you know.