Refreshing Excel through ColdFusion

For lovable music visit http://musiktag.eu

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>

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.

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.

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?

an idea to access Excel formula: Jakarta POI-HSSF