Webmaster Forum / ColdFusion / Advanced Techniques / July 2008

an idea to access Excel formula: http://poi.apache.org/hssf/index.html

Hmm, I've been looking at your suggestion and I'm confused about a few things
(I'm not a Java person). So, before using those functions, you have to download
this Java library and install it somewhere in ColdFusion? If that's the case, I
don't have access to the Coldfusion files and folders so I don't believe this
would work for me.

If, however, I can get this to work, my question is how do I implement these
Java commands/functions in Coldfusion?

I checked today and confirmed that I don't have access to the server, so won't
be able to install the Java POI.

[q][i]Originally posted by: [b][b]-==cfSearching==-[/b][/b][/i]
If not with java, how are you reading and modifying the cell values?[/q]

It's a hack one of my coworkers showed me. It's done by using a cfquery and
setting the datasource to an access db (doesnt matter which one as long as it
exists). You'll be able to query the excel sheet as if it was a table if you
use special syntax.

ex:
<cfquery name="hackExcel" datasource="Some_mdb">
  select * from [Sheet1$] in '\\FullDir\test.xls'[Excel 5.0;]
</cfquery>
Note: The first row in the excel sheet will always be the column name. If it
is blank, then it is set to F1, F2, etc. according to the column number.

I'll go take a look at this JavaLoader.cfc. Also, could you give me the link
to that houseoffusion thread? It could be exactly what I'm looking for.

There are http://poi.apache.org/hssf/eval.html. The java code translates easily
into Coldfusion, as the following example shows

<cfscript>
filename= "C:\Documents and Settings\BKBK\My
Documents\jakarta_poi\excel_2003_tutorial_english.xls";
fis = createobject("java","java.io.FileInputStream").init(filename);

workbook =
createobject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init(fis);
sheet = workbook.getSheetAt(1);
formulaEvaluator =
createobject("java","org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator").init(s
heet, wb);

// suppose your formula is in B3
cellReference =
createobject("java","org.apache.poi.hssf.util.CellReference").init("B3");

//...and so on

</cfscript>

 As -==cfSearching==- says, there is a POI library in Coldfusion 8, but it is
version 2.5. Unlike the current version (3.1), the one in Coldfusion doesn't
seem to contain HSSFFormulaEvaluator, which is typically the kind of class you
would require.

There is a catch. It is generally not advisable to copy a newer version of a
JAR file into the Coldfusion lib folder when it contains the old JAR. That is
where you can use the http://javaloader.riaforge.org/. It can load a Java class
without clashing with the version in the Coldfusion lib.

That was just to share some info about POI with you.  Unfortunately, it's
impossible to use POI or the Javaloader if you're not allowed to install code
on the server.

Magikaru,

Thanks for your query. I've learned something new. I was delighted to see this
work:

<cfquery name="excelDBhack" datasource="testAccessDsn">
select top 7 * from [Sheet1$] in 'C:\Documents and
Settings\BKBK\Desktop\excel_2003_tutorial_english.xls'[Excel 8.0;]
</cfquery>
<cfdump var="#excelDBhack#">

Unfortunately I couldn't find much information about it on the web. At least,
not structured information about queries against an Excel sheet via an MS
Access datasource. I'll sure look into it further.

In any case, this sheds new light on your original question. The reason the
data in the formula cells remains unchanged is because the query copies only
the data, not the formulas. One way out would be to forget Excel and proceed
further with database techniques.

For example, you could use a query of a query to update the data in the
formula cells. I'll illustrate with my own query. It has the columns [i]jan,
feb, mar, apr[/i] and a [/i]total[/i] column representing the formula  [i]jan +
feb + mar + apr[/i]. To get the updated total, I simply did

<cfquery name="updatedTBL" dbtype="query">
select jan, feb, mar, apr, (jan+feb+mar+apr) as updatedTotal
from excelDBhack
</cfquery>

For lovable music visit http://musiktag.eu