XSSFSheet.addMergedRegion giving excel error

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

XSSFSheet.addMergedRegion giving excel error

hartford123

After doing some research, I found out that XSSF.addMergedRegion method is giving Excel error

Excel found unreadable content in xyz.xlsm, Do you want to recover the contents of the workbook? If you trust the source of this workbook, click yes."
  When I click Yes, I get another popu up "Excel was able to open the file by repairing or removing the unreadable content. Removed Records: Merge Cells from /xl/worksheets/sheet1.xm"



basically I am using

sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(),  row.getRowNum(),Column.a$, Column.e$));

which should merge the cells from column a to column e, when I comment this line of code , I am not getting excel error, this error is occuring when opening the .xlsm file from java application , basically template.xlsm is already existing for me and java application will use this existing template to write the data in to it and when needed creates new rows, during that process it has to merge cells with above line of code.

how do I avoid the excel error which is caused because of merging of cells.

Reply | Threaded
Open this post in threaded view
|

Re: XSSFSheet.addMergedRegion giving excel error

Nick Burch-11
On Thu, 17 Feb 2011, hartford123 wrote:
> basically I am using
>
> sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(),
> row.getRowNum(),Column.a$, Column.e$));

Can you do a bit of digging and see what POI generates, and what Excel
does? If you create a simple file in POI with a merged region that causes
problems, then create a file in Excel itself with the same region. Next,
unzip the two files (.xlsx is a zip of xml files), and spot what was done
differently. That should identify what's going wrong.

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: XSSFSheet.addMergedRegion giving excel error

hartford123
In reply to this post by hartford123


more info...

In fact merging cell from two rows, for example row 49 and row 50 for cells in certain region say column f to column l is giving excel error(warning), if it is merging the cell in the same row there is no excel error.

sheet.addMergedRegion(new CellRangeAddress( 49, 50,Column.f$,Column.l$));should we not merge cell of two rows using addMergedRegion method? how to achieve it.

Sujatha
Reply | Threaded
Open this post in threaded view
|

Re: XSSFSheet.addMergedRegion giving excel error

Mark Beardsley
Sorry to say this but I cannot re-produce the problem.

Yesterday evening, I put toghether and ran this very simple piece of code;

    public static void mergeTest(String filename) {
        File file = null;
        FileOutputStream fos = null;
        XSSFWorkbook workbook = null;
        XSSFSheet sheet = null;
        XSSFRow row = null;
        XSSFCell cell = null;
         try {
            workbook = new XSSFWorkbook();
            sheet = workbook.createSheet("Merge Test");
            row = sheet.createRow(0);
            cell = row.createCell(0);
            cell.setCellValue("Region One.");
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
            row = sheet.createRow(1);
            cell = row.createCell(1);
            cell.setCellValue("Region Two.");
            sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 6));

            file = new File(filename);
            fos = new FileOutputStream(file);
            workbook.write(fos);
        }
        catch(Exception ex) {
            System.out.println("Caught an: " + ex.getClass().getName());
            System.out.println("Message: " + ex.getMessage());
            System.out.println("Stacktrace follows............");
            ex.printStackTrace(System.out);
        }
        finally {
            if(fos != null) {
                try {
                   fos.close();
                   fos = null;
                }
                catch(IOException ioEx) {
                    // I G N O R E
                }
            }
        }
    }

which creates two merged regions on one sheet. One region consists of a single line of cells, the other of two ines of cells. Compiled and run against an early beta of version 3.8 - poi-3.8-beta1-20101213.jar and associated libraries - the xlsx file it produced opened in Excel without any complaint.

May I ask, which version of POI you are using, which version of Excel and whether you have tried to create a merged region in an xlsx file rather than an xlsm. I am wondering if there is some aspect of your template that is 'clashing' somhow either with the xml POI creates or the markup that Excel expects to read. As a test, try using your xlsm template and creating only the merged regions to see if that works; it may be the case that we will have to work through the file you are creating until it is possible to identify just what is causing the problem. As NIck suggested, if the attempt to create just the merged regions does fail, then repaet a similar operation using Excel, rename both files with the .zip extension and then unzip then into separate folders. Then it will be possible to look at the xml markup to see just how the two files differ and that should point the way to a solution.

Yours

Mark B
Reply | Threaded
Open this post in threaded view
|

Re: XSSFSheet.addMergedRegion giving excel error

rlloyd
In reply to this post by Nick Burch-11
I have encountered this problem also. I've written an app that iterates over a hierarchy of data and uses POI XSSF to format that in a vertical table. e.g. converting

Hazard
     Risk
          Risk Control
     Risk
          Risk Control

to an Excel output of:

Hazard     Risk     Risk Control
              Risk     Risk Control

I use the merge region capability to try and merge cells in the case where 1 Hazard has multiple Risks, or where 1 Risk has multiple Risk Controls.

I'm getting the same error as the posted above. I've attached the sheet1.xml file from the ZIP that is outputted from POI and the version that MS Excel repairs, to see if the difference is obvious.
XML_Sheets.zip


Reply | Threaded
Open this post in threaded view
|

Re: XSSFSheet.addMergedRegion giving excel error

atulk
Hi,
We are using POI 3.1 and are trying to upgrade to POI 3.7 to provide XSLX file support. I am facing a similar problem as being described above.

The problem seems to be that if you have a duplicate mergecell definition (you add the same merge region twice) or if you have a conflicting mergecell definition then you get this error when opening the file in Excel. Excel tells us that some data might be lost.

However this does work fine for the older .xls files and Excel does not complain.

Is this a POI bug or Excel bug? Seems like POI could provide some error if conflicting merge regions are added or provide a validation routine to see if a sheet contains conflicting merge regions and drop duplicates if they are specified that would be great.

// Sample code to generate such an error

// Case 1: Duplicate regions
CellRangeAddress cellRangeAddress = new CellRangeAddress(1, 1, 1, 2);
sheet.addMergedRegion(cellRangeAddress);
sheet.addMergedRegion(cellRangeAddress);

// Case 2: Conflicting regions
CellRangeAddress cellRangeAddress = new CellRangeAddress(1, 1, 1, 2);
sheet.addMergedRegion(cellRangeAddress);
cellRangeAddress = new CellRangeAddress(1, 1, 1, 3);
sheet.addMergedRegion(cellRangeAddress);
Reply | Threaded
Open this post in threaded view
|

Re: XSSFSheet.addMergedRegion giving excel error

Nick Burch-11
On Fri, 3 Jun 2011, atulk wrote:
> However this does work fine for the older .xls files and Excel does not
> complain.

I guess this means that the .xlsx format is stricter. OOI, if you open a
.xls file with overlapping regions in excel and save it as a .xlsx, how
does excel itself resolve it?

> Is this a POI bug or Excel bug? Seems like POI could provide some error
> if conflicting merge regions are added or provide a validation routine
> to see if a sheet contains conflicting merge regions and drop duplicates
> if they are specified that would be great.

Sounds like a handy XSSF specific feature, please send in a patch if you
manage to code something up to do it!

Cheers
Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]