You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Leen Toelen <to...@gmail.com> on 2005/09/23 14:10:49 UTC
Lots of rows in a sheet
Hi,
I am writing a function to export an SWT table to Excel. For most
tables this works fine, but on some of them the Excel sheet gets
corrupted.
First the table is converted to an Object[][], and an array is
generated with the column names. These arrays are passed to the
following function.
Most of the times I create 5 sheets with each 1000 rows of 20 columns
on them. Does anyone see an obvious error I am making? There is no
exception generated in java, but Excel says the file is too heavily
damaged to read it.
Regards,
Leen Toelen
public static void addToSheet(HSSFSheet sheet, Object[][] values,
String[] colnames) {
// Create the Title row
HSSFRow tr = sheet.getRow(0);
if (tr == null)
tr = sheet.createRow(0);
for (int j = 0; j < colnames.length; j++) {
HSSFCell c = tr.getCell((short) j);
if (c == null)
c = tr.createCell((short) j);
String output = "";
if (colnames[j] != null && colnames[j] != null)
output = colnames[j].toString();
c.setCellType(HSSFCell.CELL_TYPE_STRING);
c.setCellValue(output);
}
// Loop all rows and copy them to Excel
for (int i = 0; i < values.length; i++) {
HSSFRow r = sheet.getRow(i + 2);
if (r == null)
r = sheet.createRow(i + 2);
for (int j = 0; j < values[i].length; j++) {
Object value = values[i][j];
if (value == null || value.toString().equals("")) {
continue;
}
HSSFCell c = r.getCell((short) j);
if (c == null)
c = r.createCell((short) j);
double asdouble = -9999;
try {
asdouble = Double.parseDouble(value.toString());
} catch (Exception e) {
}
if (value instanceof Date) {
c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
c.setCellValue((Date) value);
} else if (asdouble != -9999) {
c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
c.setCellValue(asdouble);
} else if (value.toString().startsWith("http://")) {
String url = value.toString();
if(url.length()>255)
url = url.substring(0,250)+"...";
c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
c.setCellFormula("HYPERLINK(\"" + url + "\")");
} else {
c.setCellType(HSSFCell.CELL_TYPE_STRING);
c.setCellValue(value.toString());
}
}
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
Re: Lots of rows in a sheet
Posted by Leen Toelen <to...@gmail.com>.
Hi,
I (kind of) solved the problem by cutting Strings to 255 chars. This
works for my data, but I am not using long Strings.
Regards,
Leen Toelen
On 9/23/05, Carlos Bergueira <ca...@gmail.com> wrote:
> Hi !
> I have a same problem.
> No java exception; titles are writen into excel file and after loop all
> records and copy them to excel, i can't open it !
> If you get the solution, pls send me info about it.
> Thanks.
>
>
> 2005/9/23, Leen Toelen <to...@gmail.com>:
> >
> > Hi,
> >
> > I am writing a function to export an SWT table to Excel. For most
> > tables this works fine, but on some of them the Excel sheet gets
> > corrupted.
> >
> > First the table is converted to an Object[][], and an array is
> > generated with the column names. These arrays are passed to the
> > following function.
> >
> > Most of the times I create 5 sheets with each 1000 rows of 20 columns
> > on them. Does anyone see an obvious error I am making? There is no
> > exception generated in java, but Excel says the file is too heavily
> > damaged to read it.
> >
> > Regards,
> > Leen Toelen
> >
> >
> > public static void addToSheet(HSSFSheet sheet, Object[][] values,
> > String[] colnames) {
> > // Create the Title row
> > HSSFRow tr = sheet.getRow(0);
> > if (tr == null)
> > tr = sheet.createRow(0);
> >
> > for (int j = 0; j < colnames.length; j++) {
> > HSSFCell c = tr.getCell((short) j);
> > if (c == null)
> > c = tr.createCell((short) j);
> >
> > String output = "";
> > if (colnames[j] != null && colnames[j] != null)
> > output = colnames[j].toString();
> > c.setCellType(HSSFCell.CELL_TYPE_STRING);
> > c.setCellValue(output);
> > }
> >
> > // Loop all rows and copy them to Excel
> > for (int i = 0; i < values.length; i++) {
> > HSSFRow r = sheet.getRow(i + 2);
> > if (r == null)
> > r = sheet.createRow(i + 2);
> >
> > for (int j = 0; j < values[i].length; j++) {
> > Object value = values[i][j];
> > if (value == null || value.toString().equals("")) {
> > continue;
> > }
> >
> > HSSFCell c = r.getCell((short) j);
> > if (c == null)
> > c = r.createCell((short) j);
> >
> > double asdouble = -9999;
> > try {
> > asdouble =
> Double.parseDouble(value.toString());
> > } catch (Exception e) {
> > }
> >
> > if (value instanceof Date) {
> > c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
> > c.setCellValue((Date) value);
> > } else if (asdouble != -9999) {
> > c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
> > c.setCellValue(asdouble);
> > } else if (value.toString().startsWith("http://"))
> {
> > String url = value.toString();
> > if(url.length()>255)
> > url =
> url.substring(0,250)+"...";
> >
> > c.setCellType(HSSFCell.CELL_TYPE_FORMULA );
> > c.setCellFormula("HYPERLINK(\"" + url +
> "\")");
> > } else {
> > c.setCellType(HSSFCell.CELL_TYPE_STRING );
> > c.setCellValue(value.toString());
> > }
> >
> > }
> > }
> > }
> >
> >
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail:
> poi-user-unsubscribe@jakarta.apache.org
> > Mailing List:
> http://jakarta.apache.org/site/mail2.html#poi
> > The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
> >
> >
>
>
>
> --
> Cumprts,
> Carlos Bergueira
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
Re: Lots of rows in a sheet
Posted by Carlos Bergueira <ca...@gmail.com>.
Hi !
I have a same problem.
No java exception; titles are writen into excel file and after loop all
records and copy them to excel, i can't open it !
If you get the solution, pls send me info about it.
Thanks.
2005/9/23, Leen Toelen <to...@gmail.com>:
>
> Hi,
>
> I am writing a function to export an SWT table to Excel. For most
> tables this works fine, but on some of them the Excel sheet gets
> corrupted.
>
> First the table is converted to an Object[][], and an array is
> generated with the column names. These arrays are passed to the
> following function.
>
> Most of the times I create 5 sheets with each 1000 rows of 20 columns
> on them. Does anyone see an obvious error I am making? There is no
> exception generated in java, but Excel says the file is too heavily
> damaged to read it.
>
> Regards,
> Leen Toelen
>
>
> public static void addToSheet(HSSFSheet sheet, Object[][] values,
> String[] colnames) {
> // Create the Title row
> HSSFRow tr = sheet.getRow(0);
> if (tr == null)
> tr = sheet.createRow(0);
>
> for (int j = 0; j < colnames.length; j++) {
> HSSFCell c = tr.getCell((short) j);
> if (c == null)
> c = tr.createCell((short) j);
>
> String output = "";
> if (colnames[j] != null && colnames[j] != null)
> output = colnames[j].toString();
> c.setCellType(HSSFCell.CELL_TYPE_STRING);
> c.setCellValue(output);
> }
>
> // Loop all rows and copy them to Excel
> for (int i = 0; i < values.length; i++) {
> HSSFRow r = sheet.getRow(i + 2);
> if (r == null)
> r = sheet.createRow(i + 2);
>
> for (int j = 0; j < values[i].length; j++) {
> Object value = values[i][j];
> if (value == null || value.toString().equals("")) {
> continue;
> }
>
> HSSFCell c = r.getCell((short) j);
> if (c == null)
> c = r.createCell((short) j);
>
> double asdouble = -9999;
> try {
> asdouble = Double.parseDouble(value.toString());
> } catch (Exception e) {
> }
>
> if (value instanceof Date) {
> c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
> c.setCellValue((Date) value);
> } else if (asdouble != -9999) {
> c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
> c.setCellValue(asdouble);
> } else if (value.toString().startsWith("http://")) {
> String url = value.toString();
> if(url.length()>255)
> url = url.substring(0,250)+"...";
>
> c.setCellType(HSSFCell.CELL_TYPE_FORMULA);
> c.setCellFormula("HYPERLINK(\"" + url + "\")");
> } else {
> c.setCellType(HSSFCell.CELL_TYPE_STRING);
> c.setCellValue(value.toString());
> }
>
> }
> }
> }
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
>
--
Cumprts,
Carlos Bergueira