You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "j.balladares@verizon.net" <j....@verizon.net> on 2014/12/06 14:03:14 UTC
Create an excel file using template
Hi all,
I check this link of examples
http://poi.apache.org/spreadsheet/examples.html, I cannot find an example of
creating an excel from an excel template file. The template file contains
predefined pivot table with all formats.
The export will write 90K aprox. rows in the excel file. I am using
ColdFusion 9 with POI 3.9, I can install the new beta.
Anyone have an example?
Thanks in advanced.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Create-an-excel-file-using-template-tp5717313.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Create an excel file using template
Posted by "j.balladares@verizon.net" <j....@verizon.net>.
The POI use more memory when read xlsx files. I follow the guideline using
OPCPackage.
I try:
<cffile action="COPY" source="#filepathtemplate#" destination="#filepath#"
/>
<cfset xlsFileTemplate =
createObject("java","org.apache.poi.openxml4j.opc.OPCPackage").open(JavaCast("string",
filepathtemplate)) />
<cfset templateWorkbook =
createObject("java","org.apache.poi.ss.usermodel.WorkbookFactory").create(xlsFileTemplate)
/>
<cfset writeableWorkbook =
createObject("java","org.apache.poi.xssf.streaming.SXSSFWorkbook").init(templateWorkbook,
1000) />
It reduced the time but not much. Anybody have an idea in how to improved
this?
Thanks.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Create-an-excel-file-using-template-tp5717313p5717333.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Create an excel file using template
Posted by "j.balladares@verizon.net" <j....@verizon.net>.
My previous code was only writing the new rows, it was not copying the tabs
from the template.
Here is the adjustment of my code:
<cffile action="COPY" source="#filepathtemplate#"
destination="#filepath#" />
<cfset xlsFileTemplate =
createObject("java","java.io.FileInputStream").init(filepathtemplate) />
<cfset templateWorkbook =
createObject("java","org.apache.poi.xssf.usermodel.XSSFWorkbook").init(xlsFileTemplate)
/>
<cfset writeableWorkbook =
createObject("java","org.apache.poi.xssf.streaming.SXSSFWorkbook").init(templateWorkbook,
1000) />
<cfset cellStyleStatic =
createObject("java","org.apache.poi.xssf.usermodel.XSSFCellStyle") />
<cfset region =
createObject("java","org.apache.poi.ss.util.CellRangeAddress") />
I check the SSPerformanceTest example from here:
http://poi.apache.org/faq.html#faq-N10109
I cannot use this because xls is limited to 64K rows. But it will be great
if i can write 50K rows in 3 secs.
Please expert, check my code and let me know if I can improved anything.
Thanks !
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Create-an-excel-file-using-template-tp5717313p5717327.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Create an excel file using template
Posted by "j.balladares@verizon.net" <j....@verizon.net>.
Thanks for your reply and help.
I was able to do it as:
<cffile action="COPY" source="#filepathtemplate#"
destination="#filepath#">
<cfset writeableWorkbook =
createObject("java","org.apache.poi.xssf.streaming.SXSSFWorkbook").init(1000)
/>
<cfset cellStyleStatic =
createObject("java","org.apache.poi.xssf.usermodel.XSSFCellStyle") />
<cfset region =
createObject("java","org.apache.poi.ss.util.CellRangeAddress") />
<cfset moneyFormat = writeableWorkbook.createDataFormat() />
<cfset moneyStyle = writeableWorkbook.createCellStyle() />
<cfset
moneyStyle.setDataFormat(moneyFormat.getFormat("$##,####0.00_);[Red]($##,####0.00)"))
/>
<cfset colorLib =
createObject("java","org.apache.poi.ss.usermodel.IndexedColors") />
<cfset fcBlack = colorLib.BLACK.getIndex() />
<cfset fcYellow = colorLib.YELLOW.getIndex() />
<cfset fcRed = colorLib.RED.getIndex() />
<cfset fcPink = colorLib.PINK.getIndex() />
<cfset fcCyan = colorLib.TURQUOISE.getIndex() />
<cfset fcTan = colorLib.TAN.getIndex() />
<cfset fcGrey = colorLib.GREY_25_PERCENT.getIndex() />
<cfset percentStyleBlack = writeableWorkbook.createCellStyle() />
<cfset percentStyleBlack.setAlignment(cellStyleStatic.ALIGN_CENTER)>
<cfset
percentStyleBlack.setFillPattern(percentStyleBlack.SOLID_FOREGROUND) />
<cfset percentStyleBlack.setFillForegroundColor(fcBlack) />
GET DATA AND CREATE SHEETS, ROWS, CELLS .....
<cfset writeableWorkbook.setActiveSheet(0) />
<cfset xlsFile =
createObject("java","java.io.FileOutputStream").init(filepath) />
<cfset writeableWorkbook.write(xlsFile) />
<cfset writeableWorkbook.dispose() />
<cfset xlsFile.close() />
Now my issue is that the file is taking little over 2 mins to write aprox
90K rows in the browser. For some people get browser time out because they
have slow connection.
I was able to improved the speed before with java setting in CF as
https://forums.adobe.com/thread/1476097
anyone here deals with a lot of rows? What is a good time performance for
write the aprox 90K rows?
Am i missing something in my code?
Thanks
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Create-an-excel-file-using-template-tp5717313p5717326.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Create an excel file using template
Posted by Aram Mirzadeh <aw...@mbcli.com>.
Just open the template, manipulate as needed, save to a different location.
Or
Use FileCopy to copy the template to it's destination, then open it and
manipulate it.
On Sat, Dec 6, 2014 at 8:03 AM, j.balladares@verizon.net <
j.balladares@verizon.net> wrote:
> Hi all,
> I check this link of examples
> http://poi.apache.org/spreadsheet/examples.html, I cannot find an example
> of
> creating an excel from an excel template file. The template file contains
> predefined pivot table with all formats.
> The export will write 90K aprox. rows in the excel file. I am using
> ColdFusion 9 with POI 3.9, I can install the new beta.
> Anyone have an example?
> Thanks in advanced.
>
>
>
>
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/Create-an-excel-file-using-template-tp5717313.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>