You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Alexander Medina (AM)" <am...@offix.de> on 2008/03/06 16:55:39 UTC
problem with too many Date-Cells
Hi,
We're exporting large Excelsheets with plenty of date values. If we have more than ~4000 cells with date values, Excel says that there are too many different cellformats and removes them from the sheet, so that any dates >4000 are not formatted. Is there a way to assign a format to a whole column or range or something? Here is a little sample code, which you can try. I will also attach the xls file, which was generated.
public String export(Vector header, List result, String filename){
String filepath = null;
String sdf = "m/d/yy";
try{
int r = 0;
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFPrintSetup ps = sheet.getPrintSetup();
ps.setLandscape(true);
HSSFRow row = sheet.createRow((short)r++);
short c = 0;
for(int i = 0; i< header.size();i++){
row.createCell((short)c++).setCellValue(new HSSFRichTextString((String)header.elementAt(i)));
}//for
Iterator rows = result.iterator();
while(rows.hasNext()&&!cancelled){
Object[] arow = (Object[]) rows.next();
row = sheet.createRow(r++);
c = 0;
for(int k = 0; k < arow.length;k++){
Object element = arow[k];
if (element == null)
row.createCell(c++).setCellValue(new HSSFRichTextString());
else if (element instanceof Date) {
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(sdf));
HSSFCell cell = row.createCell(c++);
cell.setCellValue((Date)element);
cell.setCellStyle(cellStyle);
} else {
String t = (String) element;
if(t.equals("-"))
element = " ";
row.createCell(c++).setCellValue(new HSSFRichTextString(t));
} // if
}//for
count++;
}//while
if(!cancelled){
BufferedOutputStream fileOut;
try {
fileOut = new BufferedOutputStream (
new FileOutputStream(filename + ".xls"));
wb.write(fileOut);
fileOut.flush();
fileOut.close();
System.out.println("Written to: "+filename + ".xls");
} catch (FileNotFoundException e){
e.printStackTrace();
return "";
}
catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
filepath = filename + ".xls";
}
}//try
catch(Exception e){
e.printStackTrace();
}//catch
return filepath;
}
public static void main(String[] args){
int columns = 50;
int rows = 100;
Vector header = new Vector(columns);
Vector body = new Vector(rows);
for(int rowi=0; rowi<rows;rowi++){
Object[] row = new Object[columns];
for(int columni = 0;columni<columns;columni++){
if(rowi==0)
header.add("column_"+columni);
row[columni] = new Date();
}//for
body.add(row);
}//for
XLSExport export = new XLSExport();
export.export(header, body, System.getProperty("user.home")+File.separator+"export_test");
}//method
Thanks for any help,
Alex
-----------------------------------------------------------------
Alexander Medina fon: +49 521 875 08 37
OffiX GmbH fax: +49 521 875 08 38
Meller Str. 2 http://www.offix.de <http://www.offix.de/>
33613 Bielefeld - Das IT-Systemhaus -
Geschäftsführung: Susanta Stefan Sirker
Sitz der Gesellschaft: Bielefeld
Amtsgericht Bielefeld 20 HRB 37365, Ust-IdNr.: DE 220711066
-----------------------------------------------------------------
Hinweis / Disclaimer:
Diese E-Mail sowie etwaige Anlagen können vertraulich sein und einer Geheimhaltungspflicht unterliegen. Sollten Sie nicht der Adressat dieser E-Mail sein, informieren wir Sie hiermit, dass jede Weiterleitung, Reproduktion, Vervielfältigung oder Nutzung strengstens verboten ist. Bitte vernichten Sie die Dokumente und benachrichtigen Sie den Absender.
This e-mail and any attachment could be confidential and may be legally privileged. If you are not the intended recipient, be advised that any disclosure, reproduction, distribution or other dissemination or use is strictly prohibited. Please destroy the messages and notify the sender immediately.
AW: problem with too many Date-Cells
Posted by "Alexander Medina (AM)" <am...@offix.de>.
Ah thanks,
its working now :)
-----Ursprüngliche Nachricht-----
Von: Nick Burch [mailto:nick@torchbox.com]
Gesendet: Donnerstag, 6. März 2008 16:59
An: POI Users List
Betreff: Re: problem with too many Date-Cells
On Thu, 6 Mar 2008, Alexander Medina (AM) wrote:
> We're exporting large Excelsheets with plenty of date values. If we have
> more than ~4000 cells with date values, Excel says that there are too
> many different cellformats and removes them from the sheet
See http://poi.apache.org/hssf/quick-guide.html#WorkingWithFonts
Your mistake is to create one cell style per cell, which is bad. Create
any styles you need only once, and apply them to each cell that needs it
Nick
---------------------------------------------------------------------
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
Re: problem with too many Date-Cells
Posted by Nick Burch <ni...@torchbox.com>.
On Thu, 6 Mar 2008, Alexander Medina (AM) wrote:
> We're exporting large Excelsheets with plenty of date values. If we have
> more than ~4000 cells with date values, Excel says that there are too
> many different cellformats and removes them from the sheet
See http://poi.apache.org/hssf/quick-guide.html#WorkingWithFonts
Your mistake is to create one cell style per cell, which is bad. Create
any styles you need only once, and apply them to each cell that needs it
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org