Summary: | [NEEDS-INFO]EXCEL application error appears when closing and not saving a POI generated xls spreadsheet | ||
---|---|---|---|
Product: | POI | Reporter: | Fred Barnes <fred.barnes> |
Component: | HSSF | Assignee: | POI Developers List <dev> |
Status: | RESOLVED INVALID | ||
Severity: | major | CC: | fred.barnes |
Priority: | P3 | ||
Version: | 2.0-dev | ||
Target Milestone: | --- | ||
Hardware: | PC | ||
OS: | All |
Description
Fred Barnes
2003-07-14 13:54:10 UTC
we need some code and just some at least basic information on when this happens. I mean obviously some folks aren't having this problem ;-) Hi Andy, The generation of the excel sheet works fine. It is when opening the generated sheet in Microsoft Excel 2000, that the problems start. I can sussesfully open the POI generated sheet. After changing the data in the sheet and maybe resizing the columns width I get the aboved mentioned error. I just say that the "The memory could not be read"! Also this happens intermittently, which makes it more difficult to troubleshoot. I included 2 method that generate my sheet. Please advise :( public void generateXls(String outputPath) { HSSFWorkbook wb = new HSSFWorkbook(); //Create company name font. HSSFFont companyFont = wb.createFont(); companyFont.setFontHeightInPoints((short) 16); companyFont.setFontName("Arial"); companyFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); companyStyle = wb.createCellStyle(); companyStyle.setFont(companyFont); //Create heading font. HSSFFont headingFont = wb.createFont(); headingFont.setFontHeightInPoints((short) 14); headingFont.setFontName("Arial"); headingFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headingStyle = wb.createCellStyle(); headingStyle.setFont(headingFont); //Create column heading font. HSSFFont columnHeadingFont = wb.createFont(); columnHeadingFont.setFontHeightInPoints((short) 10); columnHeadingFont.setFontName("Arial"); columnHeadingFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); columnHeadingStyle = wb.createCellStyle(); columnHeadingStyle.setFont(columnHeadingFont); //Create workbook font. HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 10); font.setFontName("Arial"); style = wb.createCellStyle(); style.setFont(font); HSSFSheet sheet = createXlsSheet(wb, "Daily", this.reportTitle, this.dateline); HSSFSheet sheet1 = createXlsSheet(wb, "Monthly", this.monthlyReportTitle, this.monthlyDateline); File outputDir = new File(outputPath + dir); if (!outputDir.exists()) { outputDir.mkdirs(); } FileOutputStream fileOut = null; try { fileOut = new FileOutputStream(outputDir.getAbsolutePath() + File.separator + fileName + ".xls"); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { LogManager.logError(this, e); if (fileOut != null) { try { fileOut.close(); } catch (IOException ioe) { } fileOut = null; } } catch (IOException e) { LogManager.logError(this, e); if (fileOut != null) { try { fileOut.close(); } catch (IOException ioe) { } fileOut = null; } } } private HSSFSheet createXlsSheet(HSSFWorkbook wb, String reportType, String reportName, String reportDateline) { HSSFSheet sheet = wb.createSheet(reportType); sheet.setZoom(4, 5); int rowCounter = 0; //Create report heading. HSSFRow row0 = sheet.createRow(rowCounter); HSSFCell row0Cell0 = row0.createCell((short) 0); row0Cell0.setCellStyle(companyStyle); row0Cell0.setCellValue(this.COMPANYNAME); rowCounter += 2; HSSFRow row2 = sheet.createRow(rowCounter); HSSFCell row2Cell0 = row2.createCell((short) 0); row2Cell0.setCellStyle(headingStyle); row2Cell0.setCellValue(reportName); HSSFRow row3 = sheet.createRow(++rowCounter); HSSFCell row3Cell0 = row3.createCell((short) 0); row3Cell0.setCellStyle(style); row3Cell0.setCellValue(reportDateline); rowCounter += 3; HSSFRow columnHeadingRow = sheet.createRow(rowCounter); //Create column headings. for (int i = 0; i < columnHeading.size(); i++) { HSSFCell headingCell = columnHeadingRow.createCell((short) i); headingCell.setCellStyle(columnHeadingStyle); headingCell.setCellValue((String) columnHeading.get(i)); } String previousPackageType = null; String currentPackageType = null; String currentBrand = null; long packageTypeOpening = 0; long packageTypeNew = 0; long packageTypeIn = 0; long packageTypeOut = 0; long packageTypeTerminated = 0; long packageTypeClosing = 0; long totalOpening = 0; long totalNew = 0; long totalIn = 0; long totalOut = 0; long totalTerminated = 0; long totalClosing = 0; long additionalTotalOpening = 0; long additionalTotalNew = 0; long additionalTotalIn = 0; long additionalTotalOut = 0; long additionalTotalTerminated = 0; long additionalTotalClosing = 0; boolean firstRow = true; boolean addTotal = false; boolean countAdditional = false; List data = getReportData(reportType); for (int i = 0; i < data.size(); i++) { List dataRow = (List) data.get(i); if (dataRow == null) { HSSFRow row = sheet.createRow(++rowCounter); HSSFCell cell1 = row.createCell((short) 0); cell1.setCellStyle(columnHeadingStyle); cell1.setCellValue("Additional Dial Up Total"); sheet.addMergedRegion(new Region(rowCounter, (short) 0, rowCounter, (short) 1)); HSSFCell cell2 = row.createCell((short) 2); cell2.setCellStyle(style); cell2.setCellValue(additionalTotalOpening); HSSFCell cell3 = row.createCell((short) 3); cell3.setCellStyle(style); cell3.setCellValue(additionalTotalNew); HSSFCell cell4 = row.createCell((short) 4); cell4.setCellStyle(style); cell4.setCellValue(additionalTotalIn); HSSFCell cell5 = row.createCell((short) 5); cell5.setCellStyle(style); cell5.setCellValue(additionalTotalOut); HSSFCell cell6 = row.createCell((short) 6); cell6.setCellStyle(style); cell6.setCellValue(additionalTotalTerminated); HSSFCell cell7 = row.createCell((short) 7); cell7.setCellStyle(style); cell7.setCellValue(additionalTotalClosing); packageTypeOpening = 0; packageTypeNew = 0; packageTypeIn = 0; packageTypeOut = 0; packageTypeTerminated = 0; packageTypeClosing = 0; countAdditional = false; continue; } currentPackageType = (String) dataRow.get(0); currentBrand = (String) dataRow.get(1); if (firstRow) { packageTypeOpening += ((Long) dataRow.get(2)).longValue(); packageTypeNew += ((Long) dataRow.get(3)).longValue(); packageTypeIn += ((Long) dataRow.get(4)).longValue(); packageTypeOut += ((Long) dataRow.get(5)).longValue(); packageTypeTerminated += ((Long) dataRow.get(6)).longValue(); packageTypeClosing += ((Long) dataRow.get(7)).longValue(); totalOpening += ((Long) dataRow.get(2)).longValue(); totalNew += ((Long) dataRow.get(3)).longValue(); totalIn += ((Long) dataRow.get(4)).longValue(); totalOut += ((Long) dataRow.get(5)).longValue(); totalTerminated += ((Long) dataRow.get(6)).longValue(); totalClosing += ((Long) dataRow.get(7)).longValue(); HSSFRow row = sheet.createRow(++rowCounter); for (int j = 0; j < dataRow.size(); j++) { HSSFCell cell = row.createCell((short) j); cell.setCellStyle(style); Object type = dataRow.get(j); if (type instanceof String) { cell.setCellValue((String) type); } else if (type instanceof Long) { cell.setCellValue(((Long) type).doubleValue()); } else if (type instanceof java.sql.Date) { cell.setCellValue((java.sql.Date) type); } } previousPackageType = currentPackageType; firstRow = false; continue; } if (currentPackageType.equals("FREE")) { addTotal = true; } if (!currentPackageType.equals(previousPackageType)) { HSSFRow row = sheet.createRow(++rowCounter); HSSFCell cell1 = row.createCell((short) 0); cell1.setCellStyle(columnHeadingStyle); cell1.setCellValue(previousPackageType + " Total"); sheet.addMergedRegion(new Region(rowCounter, (short) 0, rowCounter, (short) 1)); HSSFCell cell3 = row.createCell((short) 2); cell3.setCellStyle(style); cell3.setCellValue(packageTypeOpening); HSSFCell cell4 = row.createCell((short) 3); cell4.setCellStyle(style); cell4.setCellValue(packageTypeNew); HSSFCell cell5 = row.createCell((short) 4); cell5.setCellStyle(style); cell5.setCellValue(packageTypeIn); HSSFCell cell6 = row.createCell((short) 5); cell6.setCellStyle(style); cell6.setCellValue(packageTypeOut); HSSFCell cell7 = row.createCell((short) 6); cell7.setCellStyle(style); cell7.setCellValue(packageTypeTerminated); HSSFCell cell8 = row.createCell((short) 7); cell8.setCellStyle(style); cell8.setCellValue(packageTypeClosing); packageTypeOpening = 0; packageTypeNew = 0; packageTypeIn = 0; packageTypeOut = 0; packageTypeTerminated = 0; packageTypeClosing = 0; } if (currentPackageType.equals("BILLABLE") && addTotal) { HSSFRow row = sheet.createRow(++rowCounter); HSSFCell cell1 = row.createCell((short) 0); cell1.setCellStyle(columnHeadingStyle); cell1.setCellValue("Total"); sheet.addMergedRegion(new Region(rowCounter, (short) 0, rowCounter, (short) 1)); HSSFCell cell2 = row.createCell((short) 2); cell2.setCellStyle(style); cell2.setCellValue(totalOpening); HSSFCell cell3 = row.createCell((short) 3); cell3.setCellStyle(style); cell3.setCellValue(totalNew); HSSFCell cell4 = row.createCell((short) 4); cell4.setCellStyle(style); cell4.setCellValue(totalIn); HSSFCell cell5 = row.createCell((short) 5); cell5.setCellStyle(style); cell5.setCellValue(totalOut); HSSFCell cell6 = row.createCell((short) 6); cell6.setCellStyle(style); cell6.setCellValue(totalTerminated); HSSFCell cell7 = row.createCell((short) 7); cell7.setCellStyle(style); cell7.setCellValue(totalClosing); addTotal = false; countAdditional = true; } packageTypeOpening += ((Long) dataRow.get(2)).longValue(); packageTypeNew += ((Long) dataRow.get(3)).longValue(); packageTypeIn += ((Long) dataRow.get(4)).longValue(); packageTypeOut += ((Long) dataRow.get(5)).longValue(); packageTypeTerminated += ((Long) dataRow.get(6)).longValue(); packageTypeClosing += ((Long) dataRow.get(7)).longValue(); totalOpening += ((Long) dataRow.get(2)).longValue(); totalNew += ((Long) dataRow.get(3)).longValue(); totalIn += ((Long) dataRow.get(4)).longValue(); totalOut += ((Long) dataRow.get(5)).longValue(); totalTerminated += ((Long) dataRow.get(6)).longValue(); totalClosing += ((Long) dataRow.get(7)).longValue(); if (countAdditional) { additionalTotalOpening += ((Long) dataRow.get(2)).longValue(); additionalTotalNew += ((Long) dataRow.get(3)).longValue(); additionalTotalIn += ((Long) dataRow.get(4)).longValue(); additionalTotalOut += ((Long) dataRow.get(5)).longValue(); additionalTotalTerminated += ((Long) dataRow.get(6)).longValue (); additionalTotalClosing += ((Long) dataRow.get(7)).longValue(); } HSSFRow row = sheet.createRow(++rowCounter); for (int j = 0; j < dataRow.size(); j++) { if (currentPackageType.equals(previousPackageType) && (j == 0)) { continue; } HSSFCell cell = row.createCell((short) j); cell.setCellStyle(style); Object type = dataRow.get(j); if (type instanceof String) { cell.setCellValue((String) type); } else if (type instanceof Long) { cell.setCellValue(((Long) type).doubleValue()); } else if (type instanceof java.sql.Date) { cell.setCellValue((java.sql.Date) type); } } previousPackageType = currentPackageType; } HSSFRow row1 = sheet.createRow(++rowCounter); HSSFCell cell9 = row1.createCell((short) 0); cell9.setCellStyle(columnHeadingStyle); cell9.setCellValue("VISP Total"); sheet.addMergedRegion(new Region(rowCounter, (short) 0, rowCounter, (short) 1)); HSSFCell cell10 = row1.createCell((short) 2); cell10.setCellStyle(style); cell10.setCellValue(packageTypeOpening); HSSFCell cell11 = row1.createCell((short) 3); cell11.setCellStyle(style); cell11.setCellValue(packageTypeNew); HSSFCell cell12 = row1.createCell((short) 4); cell12.setCellStyle(style); cell12.setCellValue(packageTypeIn); HSSFCell cell13 = row1.createCell((short) 5); cell13.setCellStyle(style); cell13.setCellValue(packageTypeOut); HSSFCell cell14 = row1.createCell((short) 6); cell14.setCellStyle(style); cell14.setCellValue(packageTypeTerminated); HSSFCell cell15 = row1.createCell((short) 7); cell15.setCellStyle(style); cell15.setCellValue(packageTypeClosing); HSSFRow row5 = sheet.createRow(++rowCounter); HSSFCell cell23 = row5.createCell((short) 0); cell23.setCellStyle(columnHeadingStyle); cell23.setCellValue("Grand Total"); sheet.addMergedRegion(new Region(rowCounter, (short) 0, rowCounter, (short) 1)); HSSFCell cell24 = row5.createCell((short) 2); cell24.setCellStyle(style); cell24.setCellValue(totalOpening); HSSFCell cell25 = row5.createCell((short) 3); cell25.setCellStyle(style); cell25.setCellValue(totalNew); HSSFCell cell26 = row5.createCell((short) 4); cell26.setCellStyle(style); cell26.setCellValue(totalIn); HSSFCell cell27 = row5.createCell((short) 5); cell27.setCellStyle(style); cell27.setCellValue(totalOut); HSSFCell cell28 = row5.createCell((short) 6); cell28.setCellStyle(style); cell28.setCellValue(totalTerminated); HSSFCell cell29 = row5.createCell((short) 7); cell29.setCellStyle(style); cell29.setCellValue(totalClosing); return sheet; } You're doign it wrong: http://jakarta.apache.org/poi/hssf/quick-guide.html#WorkingWithFonts Hi Andy, Thank you for your reply. I had a look at your font creation example versus mine and I can not see any differences. Please advise? Mine: //Create company name font. HSSFFont companyFont = wb.createFont(); companyFont.setFontHeightInPoints((short) 16); companyFont.setFontName("Arial"); companyFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); companyStyle = wb.createCellStyle(); companyStyle.setFont(companyFont); //Create heading font. HSSFFont headingFont = wb.createFont(); headingFont.setFontHeightInPoints((short) 14); headingFont.setFontName("Arial"); headingFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headingStyle = wb.createCellStyle(); headingStyle.setFont(headingFont); //Create column heading font. HSSFFont columnHeadingFont = wb.createFont(); columnHeadingFont.setFontHeightInPoints((short) 10); columnHeadingFont.setFontName("Arial"); columnHeadingFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); columnHeadingStyle = wb.createCellStyle(); columnHeadingStyle.setFont(columnHeadingFont); Your example: // Create a new font and alter it. HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short)24); font.setFontName("Courier New"); font.setItalic(true); font.setStrikeout(true); // Fonts are set into a style so create a new one to use. HSSFCellStyle style = wb.createCellStyle(); style.setFont(font); Kind Regards Fred doh, for some reason I thought I saw you newing one up. Can you create instead a simple program that just reproduces this in a main function? The simplest piece of code which replicates the problem and depends on nothing else. Good morning Andy, I think I may have found a problem. I wrote a small test application that generates a Excel workbook. During my testing/debugging I found that the minute I add a 3rd sheet to the workbook I experience the above mentioned problem/error. Working with less than 3 sheet work 100% :) Please advise? Kind Regards Fred /* * POI.java * * 2003/07/28 * * 1.0 * * Copyright (c) 1999 - 2003 Tiscali (Pty) Ltd. All rights reserved. */ package com.wol.reporting; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.Region; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; /** * @author Fred Barnes * @version 1.0 */ public class POI { private HSSFCellStyle style; private HSSFCellStyle style2; private HSSFCellStyle style3; public POI() { //Create a work book. HSSFWorkbook wb = new HSSFWorkbook(); //Create a font. HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 16); font.setFontName("Arial"); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //Create style. style = wb.createCellStyle(); style.setFont(font); //Create 2nd font. HSSFFont font2 = wb.createFont(); font2.setFontHeightInPoints((short) 14); font2.setFontName("Arial"); font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style2 = wb.createCellStyle(); style2.setFont(font2); //Create 3rd font. HSSFFont font3 = wb.createFont(); font3.setFontHeightInPoints((short) 10); font3.setFontName("Arial"); font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style3 = wb.createCellStyle(); style3.setFont(font3); createSheet(wb); createSheet(wb); createSheet(wb); FileOutputStream fileOut = null; try { fileOut = new FileOutputStream("c:/test.xls"); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { if (fileOut != null) { try { fileOut.close(); } catch (IOException ioe) { } fileOut = null; } } catch (IOException e) { if (fileOut != null) { try { fileOut.close(); } catch (IOException ioe) { } fileOut = null; } } } private void createSheet(HSSFWorkbook wb) { // Create a sheet. HSSFSheet sheet = wb.createSheet("Test sheet"); //Set zoom to 80%. sheet.setZoom(4, 5); //Merge columns. sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 3)); //Create row1. HSSFRow row = sheet.createRow(0); HSSFCell row0Cell0 = row.createCell((short) 0); row0Cell0.setCellStyle(style); row0Cell0.setCellValue("Row 1"); //Create row2. HSSFRow row1 = sheet.createRow(1); HSSFCell row1Cell0 = row1.createCell((short) 0); row1Cell0.setCellStyle(style2); row1Cell0.setCellValue("Row 2"); //Create row3. HSSFRow row2 = sheet.createRow(2); HSSFCell row2Cell0 = row2.createCell((short) 0); row2Cell0.setCellStyle(style3); row2Cell0.setCellValue("Row 3"); //Insert a long value. long aLong = 123; HSSFRow row3 = sheet.createRow(3); HSSFCell row3Cell0 = row3.createCell((short) 0); row3Cell0.setCellStyle(style3); row3Cell0.setCellValue(aLong); //Insert a double value. double aDouble = 456; HSSFRow row4 = sheet.createRow(4); HSSFCell row4Cell0 = row4.createCell((short) 0); row4Cell0.setCellStyle(style3); row4Cell0.setCellValue(aDouble); //Insert data. for (int i = 5; i < 1005; i++) { HSSFRow rowX = sheet.createRow(i); for (short j = 0; j < 22; j++) { HSSFCell rowY = rowX.createCell(j); rowY.setCellStyle(style3); if ((j % 2) == 0) { String nullString = null; rowY.setCellValue(nullString); } else { rowY.setCellValue("test"); } } } } public static void main(String[] args) { POI poi = new POI(); } } |