CFXL 2.0 Released
For those of you that aren't familiar with my CFXL project. It started many years ago as a very basic API to modify an existing spreadsheet. Well, I've been working on CFXL for a while now. I recently came to the realization that I needed to change the underlying API I was using from JExcel API to Apache POI if CFXL was ever going to be truly powerful. Well, now CFXL has been updated under the covers to work just the way so many people wanted it to. :)
First, parsing is now built in and really trivial. Here is some example code that will read every cell in a spreadsheet and output it.
cfxl = createObject("component","com.jasondelmore.cfxl.cfxl").init("#GetDirectoryFromPath(GetCurrentTemplatePath())#/svbn.xls");
cfxl.setSheet(0);
writeoutput("<table>");
for (i=1;i<=cfxl.getSheet().getLastRowNum();i++) {
writeoutput("<tr>");
for (j=1;j<=cfxl.getRow().getLastCellNum();j++) {
writeoutput("<td>");
writeOutput(cfxl.getCellValue(j,i));
writeoutput("</td>");
}
writeoutput("</tr>");
}
writeoutput("</table>");
cfxl.closeWorkbook();
</cfscript>
Or... how about some code for creating a new spreadsheet from scratch using tag syntax.
<cf_xlparam column="A" row="13" value="My Example Excel File"/>
<cf_xlparam column="A" row="14" value="100.5"/>
<cf_xlparam column="A" row="15" value="200.4"/>
<cf_xlparam column="A" row="16" value="300.3"/>
<cf_xlparam column="A" row="17" value="400.2"/>
<cf_xlparam column="A" row="18" value="500.1"/>
</cf_xl>
Or... how about doing the same, but this time calling the CFXL API directly, and using some of the functions that are in the POI API directly as well...
cfxl = createObject("component","com.jasondelmore.cfxl.cfxl").init("#GetDirectoryFromPath(GetCurrentTemplatePath())#/demo.xls");
cfxl.setSheet("CFInsider Demo");
//cfxl.setSheet(1);
cfxl.setCell("A",13,"My Example Excel File");
cfxl.setCell("A",14,100);
cfxl.setCell("A",15,100);
cfxl.setCell("A",16,200);
cfxl.setCell("A",17,500);
cfxl.setCell("A",18,1000);
cfxl.setCell("B",36,now());
cfxl.setCell("B",37,True);
cfxl.setCell("A",38,cfxl.getCellValue("A",16));
// Note, you can go right in and grab the current sheet and use all of the cfxl APIs directly... same goes for cells and workbooks.
cfxl.getSheet().setZoom(4, 1);
cfxl.viewWorkbook();
</cfscript>
CFXL is now extraordinarily powerful and flexible. Please give it a try, tell me what you like and what you don't like!
CFXL is on RIAForge at http://cfxl.riaforge.org/ :)
Best, Jason
What formats will this support?
I've been testing this a lot recently and it is solid. I've also been using Open Office to read and create the test files so interoperability doesn't seem like it is a problem at all.
:)
I tested with 30 columns of numeric data... and then here is the number of rows and how long it took to process.
100 - 3.744 seconds
1000 - 9.422 seconds
5000 - 34.369 seconds
10000 - 63.109 seconds
20000 - 121.99 seconds
You can see the speed is better than linear up to 20,000 records. I didn't try past that.
CFXL is written with performance front and center. It reads in from a FileInputStream (which loads in the raw bytes of the file as needed... not the whole file), and a byteArrayOutputStream (which allows CFXL to output to the browser without writing to disk.)
Here's an example of the benefit of this... I tested the file with 20,000 rows and just parsed the last 1000. How much time did it take? 10.83 seconds. Skipping 19,000 rows took about 1 second, so there is almost no penalty for file size. It's just a matter of what you are doing with what you've parsed.
I bet some of you are thinking... yeah, numbers, but what about something else, like a date field?
10000 records processed in 59.481 seconds - even faster than numbers...
Please feel free to try it out, and if you find any issues, please let me know.
Cheers, :)
Jason
i've a question: if i have marged cells, horizontally and / or vertically, may i use your cfc to set or get values in these big cells? how?
regards
salvatore
i say horizonatal and / or vertical merged cells.......
regards
Actually, there is a simple way to do this. If you want to set or get the value of a set of merged cells, all you need to do is get or set the value of the topmost and leftmost cell.
Ie. cfxl.setCell("A",14,100) will set the value of any merged cells starting at that location (horizontally, vertically, or both).
Hope that helps, :)
Jason
now i'm sure that cfxl component treats mergerd cell as excel does.
thanks and regards
salvatore
I just had to add the following jar files to the javaloader:
xmlbeans.jar
ooxml-schemas.jar
dom4j.jar
I found this out after reading this post:
http://article.gmane.org/gmane.comp.jakarta.poi.us...
So far so good. Thanks a lot Jason!
Regarding getting support for .xlsx files, how did you add the 3 jar files to the javaloader?
I downloaded the 3 jar files to:
com\jasondelmore\cfxl
And added the 3 files to the cfxl.cfc file:
<cfset paths[5] = thisPath & "xmlbeans-2.3.0.jar"/>
<cfset paths[6] = thisPath & "poi-ooxml-schemas-3.6-20091214.jar"/>
<cfset paths[7] = thisPath & "dom4j-1.6.1.jar"/>
When I run it, I get a 500 JRun Server error:
ROOT CAUSE:
java.lang.NoClassDefFoundError: org/apache/log4j/Logger
Thank you for any ideas or support,
Josh