Bug 51083

Summary: Issue with VBA Macro in Excel 2003/2007
Product: POI Reporter: Asha K S <kasala.asha>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED WORKSFORME    
Severity: normal CC: kasala.asha
Priority: P2    
Version: 3.6-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: Input excel sheet

Description Asha K S 2011-04-19 02:41:16 UTC
Created attachment 26903 [details]
Input excel sheet

Results - 
Excel2002/2003       : 0, 0, -
Excel2007            : 0, 0, - 
(If you save the spreadsheet after open it, the data is saved  correctly.[Year,principal,Year-principal])
Excel2007 SP2    : Year,principal,Year-principal


import org.apache.poi.hssf.usermodel.HSSFOptimiser;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;


import java.io.*;


public class ExcelTest {

    public static void main(String[] args) throws Exception
    {
        try{
            InputStream inp = new FileInputStream("C:\\Guesstimate.xls");
            Workbook workbook = WorkbookFactory.create(inp);
            int sheetnumber = workbook.getSheetIndex("data");
            Sheet sheet = workbook.getSheetAt(sheetnumber);
            setCellValue(workbook,sheet,"Year", 1, 1);
            writeExcel("C:\\guesstimateSup.xls",workbook,sheetnumber,"data",null,true);
        }
        catch(Exception e)
        {
            throw e;
        }

    }

public static void setCellValue(Workbook workbook,Sheet sheet1,String value,int row,int column) throws Exception
    {

        try
        {
            Workbook book = workbook;
            CreationHelper createHelper = book.getCreationHelper();
            Sheet sheet = sheet1;

            Row hssfrow = sheet.getRow(row-1);

            if (hssfrow == null)
            {
                hssfrow = sheet.createRow(row-1);
            }

            Cell c  = hssfrow.getCell(column-1);
            CellStyle style= null;

            if (c != null)
            {
                style = c.getCellStyle()!=null?c.getCellStyle():null;
                hssfrow.removeCell(c);
            }
            c = hssfrow.createCell(column-1);
             if (style !=null )
                    c.setCellStyle(style);
            try
            {
                if (style!=null && style.getDataFormatString().equalsIgnoreCase("@"))
                    setCellStringValue(sheet,c,column,value,createHelper);
                else
                {
                    double i = Double.parseDouble(value);
                    c.setCellType(Cell.CELL_TYPE_NUMERIC);
                    c.setCellValue(i);
                }
            }catch(NumberFormatException e)
            {
                setCellStringValue(sheet,c,column,value,createHelper);
            }


            // Add back the modified excel object.

        }catch(Exception e)
        {
            throw e;
        }

    }

    private static void setCellStringValue(Sheet sheet,Cell c,int column,String value,CreationHelper createHelper)
    {
        if (!value.equalsIgnoreCase(""))
        {
            c.setCellType(Cell.CELL_TYPE_STRING);
            RichTextString str  = createHelper.createRichTextString(value);
            c.setCellValue(str);
            int colwidth = sheet.getColumnWidth(column-1);
            short len = (short)value.length();
            len = (short)((len * 8) / (( double ) 1 / 20));
            if (colwidth < len)
            {
                sheet.setColumnWidth(column - 1,len+1);
            }
        } else
        {
            c.setCellType(Cell.CELL_TYPE_BLANK);
            c.setCellValue(createHelper.createRichTextString(""));
        }
    }

    public static void writeExcel(String xlsfile,Workbook book1,int sheetnumber1,String sheetname1,String password,boolean update) throws Exception
        {
            Workbook book = book1;
            String sheetname = sheetname1;
            int sheetnumber = sheetnumber1;
            cleanStyles(book);
            String action="write";
            if (book != null)
            {
                try
                {
                    // If we are adding to an existing workbook, Copy all sheets from the file and
                    // add to the workbook created
                        FileOutputStream out = null;
                        try
                        {
                            if (!book.getSheetName(sheetnumber).equalsIgnoreCase(sheetname))
                            {
                                book.setSheetName(sheetnumber,sheetname);
                            }

                            // Set password if provided
                            if (password != null)
                            {
                                Sheet sheet = book.getSheet(sheetname);
                                if (book instanceof HSSFWorkbook)
                                {
                                    ((HSSFSheet)sheet).protectSheet(password);
                                }
                            }
                            out = new FileOutputStream(xlsfile);
                            book.write(out);

                        }catch(IOException e)
                        {
                            throw e;
                        }finally
                        {
                            if (out != null)
                            {
                                try
                                {
                                    out.close();
                                }catch(Exception e)
                                {
                                    //ignore
                                }
                            }
                        }

                }catch(Exception e)
                {
                    throw e;
                }


            }

        }

    private static void cleanStyles(Workbook book)
    {
        if (book instanceof HSSFWorkbook && (book.getNumCellStyles() >= Short.MAX_VALUE || book.getNumCellStyles()<0))
        {
            try
            {
                HSSFOptimiser.optimiseFonts((HSSFWorkbook) book);
                HSSFOptimiser.optimiseCellStyles((HSSFWorkbook)book);
            }catch(Exception e)
            {
                //Ignore
            }
        }
    }

}
Comment 1 Nick Burch 2011-04-19 07:24:31 UTC
What are you seeing, and what are you expecting to see?
Comment 2 Asha K S 2011-04-25 02:32:18 UTC
The following is the behaviour in different versions of Excel -

Excel2002/2003       : 0, 0, -

Excel2007            : 0, 0, - 
(If you save the spreadsheet after open it, the data is saved 
correctly.[Year,principal,Year-principal])

Excel2007 SP2    : Year,principal,Year-principal

Want to know if there is some issue with Excel or in POI because the same code works fine and shows Year,principal,Year-principal in excel2007 SP2(Macro works fine)
Comment 3 Asha K S 2011-04-25 03:22:59 UTC
We are setting the cell in "data" sheet to "Year" and cell in "deputation" sheet to "principal" ."guesstimate" sheet has macro defined which should show 
Year,principal,Year-principal but it doesnt show in Excel versions 2002/2003,2007   but works fine in Excel 2007 SP2
Comment 4 Javen O'Neal 2016-04-05 16:54:30 UTC
No progress in several years. Please reopen if you can provide a patch.

I don't have access to the older versions of Excel, so I cannot test this. It may be a bug in the Excel application itself. Either way, it would be difficult to test this or prove that POI does or does not create a valid file because the BIFF format is not an open specification like OOXML.

Try using the latest version of POI (3.14), which may include fixes that solve your problem.