CF and POI Question...

I am trying to create an Excel file on the fly using Apache POI (CF 7). I can successfully create the file and I know that it works because I can open and read the file in Excel IF I write to a file. What I want to do is create the file and save to the database and then at a later time stream the Excel file to the user. Here is my example code so far:

<cfscript>
	out = createObject("java","java.io.ByteArrayOutputStream").init();
	fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls");
</cfscript>


<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset format = wb.createDataFormat()/>
<cfset sheet = wb.createSheet("new sheet")/>
<cfset cellStyle = wb.createCellStyle()/>
<cfset cellStyle.setDataFormat(createObject("java","org.apache.poi.hssf.usermodel.HSSFDataFormat").getBuiltinFormat("0.00"))/>


<cfloop index = "LoopCount" from = "0" to = "100">
	<cfset row = sheet.createRow(javacast("int",LoopCount))/>

	<cfset cell = row.createCell(0)/>
	<cfset cell.setCellType( 0)/>
	<cfset cell.setCellValue(javacast("int",1))/>

	<cfset cell2 = row.createCell(1)/>
	<cfset cell2.setCellStyle(cellStyle)/>
	<cfset cell2.setCellValue(javacast("double","1.223452345342"))/>
	<cfset row.createCell(2).setCellValue("This is a string")/>
	<cfset row.createCell(3).setCellValue(true)/>
</cfloop>

<cfset wb.write(out)/>
<cfset out.writeTo(fileOut)/>
<!---<cfoutput>#out#</cfoutput>--->
<cfset wb.write(fileOut)/>
<!---<cfset fileOut.close()/>
<cfset out.flush()/>--->

<cfquery datasource="RMSDev" name="insExcelFile">
	INSERT INTO table values(<cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#out#">)
</cfquery>

<cfquery datasource="RMSDev" name="retExcelFile">
	SELECT excel_file FROM table WHERE id = (SELECT TOP 1 id FROM table ORDER BY id DESC)
</cfquery>

<cfheader name="content-disposition" value="attachment; filename=report.xls">

<!--- Dump the XML/XLS --->
<cfcontent type="application/msexcel" reset="true" />
<cfoutput>#retExcelFile.excel_file#</cfoutput>

Code (markup):

The reason I am writing to an Excel file is to prove that the Excel is valid, and it is. I am writing the generated Excel to a database (SQL Server 2000, data type of ntext) and then trying to retrieve it back from the db and then stream to the user. When I do this I get encoded text in the Excel file. Does anybody have any idea where my encoding is off? I have also tried a binary type in the database and had no luck there either.


Thanks...

 

Its been a while but i always remembered doing this kind of operation in a stored procedure to a binary field but I think binary (blob) requires a transaction to work properly.

In any case one other thing you could do is base64 encode the file contents on the way in and decode on the way out that might preserve your values.

This might help: http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx?mfr=true