You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Michele Nardelli <m....@nikasistemi.com> on 2007/09/17 08:51:28 UTC
Exporting in Excel is too slow
I have got a problem when I try to write an Excel file using the POI framework.
I fetch the data from a Jtable. It's really slow, if the jtable has around 100 records, the process takes around 5 seconds but if the jtable has around 2000 record takes around 3 hours! Please see the code below the way I use POI in order to write the excel file, maybe there is somethings wrong.
code
private HSSFCell createHSSFCell(HSSFSheet sheet, Object value, int row, int col) {
// create row if not yet created
HSSFRow hssfRow = sheet.getRow(row);
hssfRow = (hssfRow == null) ? sheet.createRow(row) : hssfRow;
HSSFCell cell = null;
try {
// create cell if not yet created
cell = hssfRow.getCell((short) col);
cell = (cell == null) ? hssfRow.createCell((short) col) : cell;
sheet.autoSizeColumn((short)col);
}catch(Exception e){
}
// HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("dd/mm/yyyy"));
cell.setCellStyle(cellStyle);
// set the cell value
Object cellValue;
if (types[col].equalsIgnoreCase("NUMERIC")){
cellValue = Double.parseDouble(value.toString().replaceAll(" ", ""));
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue((Double) cellValue);
}else
if (types[col].equalsIgnoreCase("NUMERICINT")){
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cellValue = Integer.parseInt(value.toString().replaceAll(" ", "").substring(0, value.toString().replaceAll(" ", "").lastIndexOf(".")));
cell.setCellValue((Integer) cellValue);
}else
if (types[col].equalsIgnoreCase("DATE")){
if (value.toString().replaceAll(" ", "").length()==8)
cellValue = DateUtils.formatDate(value.toString().replaceAll(" ", ""));
else
cellValue = "";
cell.setCellValue((String)cellValue);
}else
if (types[col].equalsIgnoreCase("TIME")){
if ((value.toString().replaceAll(" ", "").length()>=4)&&(value.toString().replaceAll(" ", "").length()<=6))
cellValue = DateUtils.formatTime(value.toString().replaceAll(" ", ""));
else
cellValue = "";
cell.setCellValue((String)cellValue);
}else{
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellValue = (value == null) ? "" : value.toString();
cell.setCellValue((String)cellValue);
}
return cell;
Thanks to eveybody, I really appreciate your help!
Regards,
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Exporting in Excel is too slow
Posted by Yegor Kozlov <ye...@dinom.ru>.
sheet.autoSizeColumn((short)col) is an expensive operation.
You should call it after the sheet is created. That is, after you
create all the cells insert the following line:
for(short col=0; col<columnCount; col++) sheet.autoSizeColumn(col);
Regards,
Yegor
> I have got a problem when I try to write an Excel file using the POI framework.
> I fetch the data from a Jtable. It's really slow, if the jtable has
> around 100 records, the process takes around 5 seconds but if the
> jtable has around 2000 record takes around 3 hours! Please see the
> code below the way I use POI in order to write the excel file, maybe there is somethings wrong.
> code
> private HSSFCell createHSSFCell(HSSFSheet sheet, Object value, int row, int col) {
> // create row if not yet created
> HSSFRow hssfRow = sheet.getRow(row);
> hssfRow = (hssfRow == null) ? sheet.createRow(row) : hssfRow;
> HSSFCell cell = null;
> try {
> // create cell if not yet created
> cell = hssfRow.getCell((short) col);
> cell = (cell == null) ? hssfRow.createCell((short) col) : cell;
> sheet.autoSizeColumn((short)col);
> }catch(Exception e){
>
>
> }
>
>
>
> // HSSFCellStyle cellStyle = wb.createCellStyle();
>
> cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("dd/mm/yyyy"));
> cell.setCellStyle(cellStyle);
>
> // set the cell value
> Object cellValue;
> if (types[col].equalsIgnoreCase("NUMERIC")){
> cellValue =
> Double.parseDouble(value.toString().replaceAll(" ", ""));
> cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
> cell.setCellValue((Double) cellValue);
> }else
> if (types[col].equalsIgnoreCase("NUMERICINT")){
> cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
> cellValue =
> Integer.parseInt(value.toString().replaceAll(" ", "").substring(0,
> value.toString().replaceAll(" ", "").lastIndexOf(".")));
> cell.setCellValue((Integer) cellValue);
> }else
> if (types[col].equalsIgnoreCase("DATE")){
> if (value.toString().replaceAll(" ", "").length()==8)
> cellValue =
> DateUtils.formatDate(value.toString().replaceAll(" ", ""));
> else
> cellValue = "";
> cell.setCellValue((String)cellValue);
>
> }else
> if (types[col].equalsIgnoreCase("TIME")){
> if ((value.toString().replaceAll(" ",
> "").length()>=4)&&(value.toString().replaceAll(" ", "").length()<=6))
> cellValue =
> DateUtils.formatTime(value.toString().replaceAll(" ", ""));
> else
> cellValue = "";
> cell.setCellValue((String)cellValue);
> }else{
> cell.setCellType(HSSFCell.CELL_TYPE_STRING);
> cellValue = (value == null) ? "" : value.toString();
> cell.setCellValue((String)cellValue);
> }
>
>
> return cell;
> Thanks to eveybody, I really appreciate your help!
> Regards,
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
AW: Exporting in Excel is too slow
Posted by Sebastian Moebius <se...@kittelberger.de>.
Hi all!
I encounter the same problem but not only on exporting but also when reading
data. Is there a way to speed up the process? I read something about the
Default Loggin, but afaik I have no logging enabled.
Thank You!
Sebastian Moebius
> -----Ursprüngliche Nachricht-----
> Von: Michele Nardelli [mailto:m.nardelli@nikasistemi.com]
> Gesendet: Montag, 17. September 2007 08:51
> An: user@poi.apache.org
> Betreff: Exporting in Excel is too slow
>
>
> I have got a problem when I try to write an Excel file using the
> POI framework.
> I fetch the data from a Jtable. It's really slow, if the jtable
> has around 100 records, the process takes around 5 seconds but if
> the jtable has around 2000 record takes around 3 hours! Please
> see the code below the way I use POI in order to write the excel
> file, maybe there is somethings wrong.
>
> code
>
> private HSSFCell createHSSFCell(HSSFSheet sheet, Object value,
> int row, int col) {
> // create row if not yet created
> HSSFRow hssfRow = sheet.getRow(row);
> hssfRow = (hssfRow == null) ? sheet.createRow(row) : hssfRow;
> HSSFCell cell = null;
> try {
> // create cell if not yet created
> cell = hssfRow.getCell((short) col);
> cell = (cell == null) ? hssfRow.createCell((short) col) : cell;
> sheet.autoSizeColumn((short)col);
> }catch(Exception e){
>
>
> }
>
>
>
>
> // HSSFCellStyle cellStyle = wb.createCellStyle();
>
> cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("dd/mm/yyyy"));
> cell.setCellStyle(cellStyle);
>
> // set the cell value
> Object cellValue;
> if (types[col].equalsIgnoreCase("NUMERIC")){
> cellValue =
> Double.parseDouble(value.toString().replaceAll(" ", ""));
> cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
> cell.setCellValue((Double) cellValue);
> }else
> if (types[col].equalsIgnoreCase("NUMERICINT")){
> cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
> cellValue =
> Integer.parseInt(value.toString().replaceAll(" ",
> "").substring(0, value.toString().replaceAll(" ", "").lastIndexOf(".")));
> cell.setCellValue((Integer) cellValue);
> }else
> if (types[col].equalsIgnoreCase("DATE")){
> if (value.toString().replaceAll(" ", "").length()==8)
> cellValue =
> DateUtils.formatDate(value.toString().replaceAll(" ", ""));
> else
> cellValue = "";
> cell.setCellValue((String)cellValue);
>
> }else
> if (types[col].equalsIgnoreCase("TIME")){
> if ((value.toString().replaceAll(" ",
> "").length()>=4)&&(value.toString().replaceAll(" ", "").length()<=6))
> cellValue =
> DateUtils.formatTime(value.toString().replaceAll(" ", ""));
>
> else
> cellValue = "";
> cell.setCellValue((String)cellValue);
>
> }else{
> cell.setCellType(HSSFCell.CELL_TYPE_STRING);
> cellValue = (value == null) ? "" : value.toString();
> cell.setCellValue((String)cellValue);
> }
>
>
> return cell;
>
> Thanks to eveybody, I really appreciate your help!
> Regards,
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org