I have used the actual POI calls to obtain specific cells from specific
sheets if you know exactly how things are laid out. The snippet of code
will cycle through all of the rows/cells in a spreadsheet - but you can add
a loop to do the same thing through several worksheets.
<cfset FilePath = "d:\tmp\file.xlx">
<cfset ExcelFileSystem =
CreateObject("java","org.apache.poi.poifs.filesystem.POIFSFileSystem").Init(CreateObject("java","java.io.FileInputStream").Init(FilePath))>
<cfset workbook1 =
CreateObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").Init(ExcelFileSystem)>
<cfset sheet = workbook1.GetSheetAt(JavaCast( "int",0))>
<cfset numRows = sheet.getLastRowNum() - 1> <!--- row starts w/0 so there
are n-1 rows--->
<cfif numRows gt 0>
<cfloop from="1" to="#numRows#" index="rowNum">
<cfset row = sheet.getRow(javaCast("int",rowNum))>
<cfset data= row.getcell(0).getStringCellValue()>
<!--- do something with it --->
</cfloop>
<cfelse>
<!--- no data in worksheet condition --->
</cfif>
On Wed, Jul 27, 2011 at 3:04 PM, Jeff Howard <[email protected]> wrote:
> I'm working in CF9 and trying to use cfspreadsheet to pull the values out
> of an xls and write the values to a db. The xls has 4 sheets with the 4th
> sheet being the raw data being output from an allergen testing machine.
> Beneath the raw data there are formulas that are used to populate a chart on
> the 3rd sheet. I am trying to extract the values that are computed but when
> using cfspreadsheet it is returning the formula for the cell and not the
> computed value.
>
> I've done some searching but have not found a solution to this issue and
> figured I'd throw out here and see if anyone has had to deal with this.
>
> I have come across spreadsheetGetCellValue but it does not let you specify
> the sheet.
>
> Is the only way to do this is to extract the entire sheet from the original
> and then create a new 1 page sheet from this and then use
> spreadsheetGetCellValue to get the value I need or is there a more
> streamlined solution?
>
--
Dawn
