Bug 21571

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: HSSFAssignee: 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
I am able to successfully generate a POI xls spreadsheet. When opening this 
spreadsheet in EXCEL and editing it, then closing and selecting NO to the save 
option, I get the following application error intermittently : 
The instruction at "0x308e0433" referenced memory at "0x00000006". The memory 
could not be "read".
I'm using MS Excel 2000 (9.0.2720) running on Windows 2000 Prof.  I've tested 
the same spreadsheet on Windows XP and the same application error is generated -
 also intermittently.
Comment 1 Andy Oliver 2003-07-24 17:09:59 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 ;-)  
Comment 2 Fred Barnes 2003-07-28 09:54:07 UTC
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;
    }
Comment 3 Andy Oliver 2003-07-28 12:42:25 UTC
You're doign it wrong: http://jakarta.apache.org/poi/hssf/quick-guide.html#WorkingWithFonts
Comment 4 Fred Barnes 2003-07-28 13:03:58 UTC
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
Comment 5 Andy Oliver 2003-07-28 14:11:48 UTC
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.
Comment 6 Fred Barnes 2003-07-29 06:31:53 UTC
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();
    }
}