Prev: cfmail multipart treated as a virus
Next: Verity Search Error
From: insuractive on 8 Feb 2007 12:22
I've been exporting data to excel for ages now, so I know the drill - build the
data as an HTML table or MSOFFICE XML,set the <cfcontent
type="application/vnd.ms-excel"> and then output the result. At the moment, I
am trying to generate an excel file to import into another application
(Microsoft MapPoint, to be exact).

The problem seems to be that MapPoint won't recognize the generated file as
being an excel file. My guess is its because the file is not an excel file,
per se, but a format that excel recognizes and can convert into a spreadsheet.
If I load the CF-generated file into excel and then save as a new file, then
the new file imports just fine into MapPoint. So my question is this:

Does anyone know of a way to export data from ColdFusion as a real XLS file?
I have some experience working with Jakarta POI, so I'm thinking that's where
this fun little journey is going to wind up. But I thought I'd send it out
there and see if anyone in the community has any better suggestions.

thanks a lot, all!

From: TSB on 8 Feb 2007 13:21
Try
http://cfregex.com/cfcomet/Excel/index.cfm?ArticleID=B5ED33FB-5CB1-4ACC-899689A1
5A0E1539 it looks like it is a archive of the old cfcomet site. I use to visit
cfcomet all the time to learn new tricks.

http://www.burnette.us

From: Kronin555 on 8 Feb 2007 14:04
Ben wrote a nice CFC wrapper around POI.
http://www.bennadel.com/index.cfm?dax=blog:474.view
From: insuractive on 8 Feb 2007 14:50
Thanks for your help TSB and Kronin. I actually just wond up rolling my own
POI code to create the excel file. It seems to work great. I'll include the
code below for anyone else who is interested:



<cfscript>
wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
objSheet = wb.createSheet("AgentTerritories");
objRow = objSheet.createRow(JavaCast("int", iCurrentRow));
objRow.createCell(0).setCellValue("COL01");
objRow.createCell(1).setCellValue("COL02");
objRow.createCell(2).setCellValue("COL03");
objRow.createCell(3).setCellValue("COL04");
</cfscript>

<cfloop query="qMyQuery">
<cfscript>
iCurrentRow = iCurrentRow + 1;
objRow = objSheet.createRow(JavaCast("int", iCurrentRow));
objRow.createCell(0).setCellValue(qMyQuery.col1);
objRow.createCell(1).setCellValue(qMyQuery.col2);
objRow.createCell(2).setCellValue(qMyQuery.col3);
objRow.createCell(3).setCellValue(qMyQuery.col4);
</cfscript>
</cfloop>

<cfscript>
sReportFilePath = "#ExpandPath(".")#\territory_report#createuuid()#.xls";
fileOut =
createObject("java","java.io.FileOutputStream").init(sReportFilePath);
wb.write(fileOut);
fileOut.close();
</cfscript>

 | 
Pages: 1
Prev: cfmail multipart treated as a virus
Next: Verity Search Error