You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2017/01/09 11:07:24 UTC
[Bug 60561] New: Values wrote in the first visible sheet are
overwriting hidden sheet values
https://bz.apache.org/bugzilla/show_bug.cgi?id=60561
Bug ID: 60561
Summary: Values wrote in the first visible sheet are
overwriting hidden sheet values
Product: POI
Version: 3.16-dev
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
Assignee: dev@poi.apache.org
Reporter: guill.sanon@gmail.com
Target Milestone: ---
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);
}
}
}
}
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 60561] Values wrote in the first visible sheet are overwriting
hidden sheet values
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60561
Dominik Stadler <do...@gmx.at> changed:
What |Removed |Added
----------------------------------------------------------------------------
OS| |All
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 60561] Values wrote in the first visible sheet are overwriting
hidden sheet values
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60561
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #3 from Javen O'Neal <on...@apache.org> ---
> 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).
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 60561] Values wrote in the first visible sheet are overwriting
hidden sheet values
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60561
--- Comment #6 from Guillaume SANON <gu...@gmail.com> ---
Created attachment 34675
--> https://bz.apache.org/bugzilla/attachment.cgi?id=34675&action=edit
Unit test describing the bug (but pass)
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 60561] Values wrote in the first visible sheet are overwriting
hidden sheet values
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60561
Javen O'Neal <on...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #5 from Javen O'Neal <on...@apache.org> ---
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/
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 60561] Values wrote in the first visible sheet are overwriting
hidden sheet values
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60561
Guillaume SANON <gu...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |NEW
--- Comment #7 from Guillaume SANON <gu...@gmail.com> ---
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 60561] Values wrote in the first visible sheet are overwriting
hidden sheet values
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60561
--- Comment #2 from Javen O'Neal <on...@apache.org> ---
Created attachment 34645
--> https://bz.apache.org/bugzilla/attachment.cgi?id=34645&action=edit
longer unit test that more closely resembles original bug report
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 60561] Values wrote in the first visible sheet are overwriting
hidden sheet values
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60561
--- Comment #1 from Javen O'Neal <on...@apache.org> ---
Created attachment 34644
--> https://bz.apache.org/bugzilla/attachment.cgi?id=34644&action=edit
passing test case
I was unable to reproduce the reported problem using trunk (post 3.16 beta 1).
See attached passing unit test
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 60561] Values wrote in the first visible sheet are overwriting
hidden sheet values
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=60561
Guillaume SANON <gu...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |NEW
--- Comment #4 from Guillaume SANON <gu...@gmail.com> ---
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org