Bug 60561 - Values wrote in the first visible sheet are overwriting hidden sheet values
Summary: Values wrote in the first visible sheet are overwriting hidden sheet values
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.16-dev
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-01-09 11:07 UTC by Guillaume SANON
Modified: 2017-01-25 16:07 UTC (History)
0 users



Attachments
passing test case (1.36 KB, text/x-java)
2017-01-19 07:11 UTC, Javen O'Neal
Details
longer unit test that more closely resembles original bug report (2.24 KB, text/x-java)
2017-01-19 07:12 UTC, Javen O'Neal
Details
Unit test describing the bug (but pass) (1.42 KB, text/plain)
2017-01-25 16:06 UTC, Guillaume SANON
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Guillaume SANON 2017-01-09 11:07:24 UTC
Hi,

When I create a XSSFWorkbook with a hidden sheet and two visible sheets : 
0 : "HiddenSheet"
1 : "DataSheet 1"
2 : "DataSheet 2"

All modifications done with Excel on the first visible "DataSheet 1" are reported on the "HiddenSheet". 
Modifications done on the third sheet "DataSheet 2" has no effect neither on "HiddenSheet" or "DataSheet 1"
Data wrote on the "DataSheet 1" shold not be reported on the "HiddenSheet"

I tried to modify with Excel 2007, Excel 2013same problem.
I tried to generate with Apache POI  3.15 avec 3.16-beta2 same problem. 
Use a very hidden sheet with "setHiddenSheet(0, 2)" correct the problem.

To reproduce generate a workbook with the code below then open the generated file with Excel and modify values in the "DataSheet 1". Unhide the "HiddenSheet" you will see the modifications you did on the "DataSheet 1" copied on the "HiddenSheet".

Regards,

Exemple code :

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class HiddenSheetTest {

	public static final int ROW_NUMBER = 15;
	public static final int COLUMN_NUMBER = 10;
	
	public static void main(String[] args) {

        Workbook wb = new XSSFWorkbook(); 
        Sheet hiddenSheet = wb.createSheet("HiddenSheet"); 
        Sheet dataSheet = wb.createSheet("DataSheet 1"); 
        Sheet dataSheet2 = wb.createSheet("DataSheet 2"); 
        populateSheetWithNumber(hiddenSheet, 5);
        populateSheetWithNumber(dataSheet, 10);
        populateSheetWithNumber(dataSheet2, 20);
        // set Active Sheet "DataSheet"
        wb.setActiveSheet(1);
        
        // Hide "hiddenSheet"
        wb.setSheetHidden(0, true);
        try {
          	FileOutputStream out = new FileOutputStream("WBTest.xlsx");
          	wb.write(out);
        	out.close();
			wb.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private static void populateSheetWithNumber(Sheet sheet, int number)
	{
		for(int i = 0; i < ROW_NUMBER; i++)
		{
			Row row = sheet.createRow(i);
			for(int j = 0;  j < COLUMN_NUMBER; j++)
			{
				Cell cell = row.createCell(j);
				cell.setCellValue(number);
			}
		}
	}
}
Comment 1 Javen O'Neal 2017-01-19 07:11:35 UTC
Created attachment 34644 [details]
passing test case

I was unable to reproduce the reported problem using trunk (post 3.16 beta 1).

See attached passing unit test
Comment 2 Javen O'Neal 2017-01-19 07:12:14 UTC
Created attachment 34645 [details]
longer unit test that more closely resembles original bug report
Comment 3 Javen O'Neal 2017-01-19 07:24:57 UTC
> All modifications done with Excel on the first visible "DataSheet 1" are 
> reported on the "HiddenSheet".

This doesn't make sense. Modifying one sheet should not modify a different sheet unless there are formulas on the second sheet. The only other way this would make sense is if you Ctrl+Click the sheets in Excel to create a sheet [Group], in which case modifications are applied to all selected sheets in that group.

Group editing is a GUI feature of Excel (not something that is stored in the OOXML format, except perhaps for group state).

I did not see a difference in POI's behavior with respect to the values that were written to the sheets when the hidden sheet was made very hidden.

Please provide a unit test that demonstrates the problem that you have. If at all possible, test for the problem in POI java code, as this eliminates the variable of Excel's behavior (different versions, macros that run when the workbook is opened).
Comment 4 Guillaume SANON 2017-01-25 12:57:31 UTC
I tried to reproduce the bug by writting data only with Apache POI without success, data are not copied from the first visible sheet to the hidden sheet.  Sheets are independant and we get what we expected.

But still the problem is when I open the generate workbook with an external tool (Excel, open Office), I didn't know about Group Editing but it's exactly what is happening here. When I open the generated workbook the "HiddenSheet" and the "DataSheet 1" are associated. I don't think I did something in the code I pasted in this purpose. As you said it doesn't make sense we don't expected this kind of behavior with the "simple" code I pasted. I don't have any macro running and tested with Excel 2007 2013 and Open Office with the same result.

I don't know if it's something related to Apache POI and OOXML Format or something with the softwares that don't support the generated file but I think an unexpected behavior occurs.
Comment 5 Javen O'Neal 2017-01-25 15:05:49 UTC
Can you run both unit tests that I attached to this bug and let me know if they pass or fail on your setup?

When you tested this bug, did you use the code sample from comment 0 or did you have more complicated code that may be writing the same contents to multiple sheets?

Can you try running this with 3.16 beta 1 or a trunk build [1] to see if it's a problem with your version of POI?

[1] https://builds.apache.org/view/POI/job/POI-DSL-1.6/lastSuccessfulBuild/artifact/
Comment 6 Guillaume SANON 2017-01-25 16:06:50 UTC
Created attachment 34675 [details]
Unit test describing the bug (but pass)
Comment 7 Guillaume SANON 2017-01-25 16:07:04 UTC
I have forgotten to mention that your tests pass. I did an other test (see attachments) more relevant with some data wrote in the "DataSheet" and it pass too. But when I open the workbook generated by this test I have the "bug" ("HiddenSheet" and "DataSheet 1" are associated, modification on "Datasheet 1" impacts "HiddenSheet").

I have the same issue with the code posted on "comment 0". When I open "WBTest.xlsx" the "HiddenSheet" and the "Datasheet 1" are associated.

I juste tried with the last successful build 3.16-beta2 #125 and same problem.