Refreshing Excel through ColdFusion

[Magikaru]

Hey guys, I have an excel page that does some calculation. I can use CF to modify the cell values that are inputs to the formula, but the formula only updates once I open up the file with Excel. So, if I use CF to read the excel table, change the input values, and read it again, only the input cell values would have been changed; the output cell stays the same. Is there anyway to have CF 'refresh' the formulas? Thanks for any help you can offer.

[BKBK]

an idea to access Excel formula: Jakarta POI-HSSF

[Magikaru]

Thanks for your suggestion. I've been looking at it and I'm confused about a few things (I'm not a Java person). So, before using those functions, I 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?

[JR__Bob__Dobbs-qSBHQ2]

Take a look at Ben Nadel's POI utility. It wraps the Java POI calls into some custom tags and CFCs.
http://www.bennadel.com/projects/poi-utility.htm

However this doesn't do you any good if you don't have access to the ColdFusion server.

[-__cfSearching__-]

Magikaru wrote:
> I can use CF to modify the cell values ...
> So, if I use CF to read the excel table, change the input values,
> and read it again, only the input cell values would have been changed;

If not with java, how are you reading and modifying the cell values? Are you working with an actual binary excel file or some other format?

> I have to download this Java library and install it somewhere in ColdFusion?

Possibly. You could also use the JavaLoader.cfc to load the jar dynamically. Requirement: You must have access to createObject("java", ...).

I also read an interesting thread over at houseoffusion.com. It mentioned there may be a version of POI already installed with CF. _If_ that is correct, the built-in jar might be enough for your purposes. I imagine it would also allow you to test the POIUtility JR "Bob" Dobbs mentioned, without having to install a new jar. Assuming the POIUtility does what you need it to do here..

[Magikaru]

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

quote:

---

Originally posted by: -==cfSearching==-
If not with java, how are you reading and modifying the cell values?

---

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.

[BKBK]

There are code examples in the POI project for Excel formulas. 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(sheet, workbook);

// 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 Javaloader. 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.

[-__cfSearching__-]

Magikaru wrote:
> could you give me the link to that houseoffusion thread

I do not recall the link, but all it said was that "a version" of POI comes bundled with ColdFusion. Meaning you could use POI without having to install anything new on the server. I thought that built-in version might be enough for your purposes. But from what BKBK said, that version may not have the functionality you require. In which case you could use the javaLoader to load a newer version.

BKBK wrote:
> Unfortunately, it's impossible to use POI or the Javaloader if you're not
> allowed to install code on the server.

True.

Magikaru, when you say "no access" I assume that means you cannot add jars to the classpath but you _can_ install code (scripts, cfc's, add files,...)? If that is the case, you should be able to use the javaLoader.

[BKBK]

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 jan, feb, mar, apr and a total column representing the formula jan + feb + mar + apr. 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>

[musicmp3]

For lovable music visit http://musiktag.eu