poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Alexander Medina (AM)" <amed...@offix.de>
Subject problem with too many Date-Cells
Date Thu, 06 Mar 2008 15:55:39 GMT
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.

 


Mime
View raw message