From 409b9af3807f60754ca30cd26f58964e813f6ffa Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Sun, 5 Sep 2010 12:53:07 +0000 Subject: [PATCH] improved API for hiding sheets, see Bugzilla 49878 git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@992772 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/status.xml | 1 + .../poi/hssf/usermodel/HSSFWorkbook.java | 2 + .../org/apache/poi/ss/usermodel/Workbook.java | 46 +++++- .../org/apache/poi/ss/util/WorkbookUtil.java | 12 ++ .../poi/xssf/usermodel/XSSFWorkbook.java | 56 ++++++- .../poi/xssf/usermodel/TestSheetHiding.java | 79 +--------- .../apache/poi/hssf/usermodel/TestBugs.java | 2 +- .../poi/hssf/usermodel/TestSheetHiding.java | 91 +---------- .../poi/ss/usermodel/BaseTestSheetHiding.java | 148 ++++++++++++++++++ 9 files changed, 268 insertions(+), 169 deletions(-) create mode 100644 src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetHiding.java diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 1a3771540a..cac2afc449 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -34,6 +34,7 @@ + 49878 - improved API for hiding sheets 49875 - fixed XSSFWorkbook.createSheet to throw exception if sheet name begins or ends with a single quote (') 49873 - fixed XSSFFormulaEvaluator to support blank cells 49850 - added a getter for _iStartAt in ListFormatOverrideLevel diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java b/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java index 4d5e752ad3..54c9d36e04 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java @@ -69,6 +69,7 @@ import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.Row.MissingCellPolicy; import org.apache.poi.ss.formula.FormulaType; +import org.apache.poi.ss.util.WorkbookUtil; import org.apache.poi.util.POILogFactory; import org.apache.poi.util.POILogger; @@ -557,6 +558,7 @@ public final class HSSFWorkbook extends POIDocument implements org.apache.poi.ss public void setSheetHidden(int sheetIx, int hidden) { validateSheetIndex(sheetIx); + WorkbookUtil.validateSheetState(hidden); workbook.setSheetHidden(sheetIx, hidden); } diff --git a/src/java/org/apache/poi/ss/usermodel/Workbook.java b/src/java/org/apache/poi/ss/usermodel/Workbook.java index 5967d8dedb..11425877bc 100644 --- a/src/java/org/apache/poi/ss/usermodel/Workbook.java +++ b/src/java/org/apache/poi/ss/usermodel/Workbook.java @@ -48,6 +48,33 @@ public interface Workbook { /** Device independent bitmap */ public static final int PICTURE_TYPE_DIB = 7; + + /** + * Indicates the sheet is visible. + * + * @see #setSheetHidden(int, int) + */ + public static final int SHEET_STATE_VISIBLE = 0; + + /** + * Indicates the book window is hidden, but can be shown by the user via the user interface. + * + * @see #setSheetHidden(int, int) + */ + public static final int SHEET_STATE_HIDDEN = 1; + + /** + * Indicates the sheet is hidden and cannot be shown in the user interface (UI). + * + *

+ * In Excel this state is only available programmatically in VBA: + * ThisWorkbook.Sheets("MySheetName").Visible = xlSheetVeryHidden + *

+ * + * @see #setSheetHidden(int, int) + */ + public static final int SHEET_STATE_VERY_HIDDEN = 2; + /** * Convenience method to get the active sheet. The active sheet is is the sheet * which is currently displayed when the workbook is viewed in Excel. @@ -455,13 +482,18 @@ public interface Workbook { /** * Hide or unhide a sheet. - * 
-     *  0 = not hidden
-     *  1 = hidden
-     *  2 = very hidden.
-     *
- * @param sheetIx The sheet number - * @param hidden 0 for not hidden, 1 for hidden, 2 for very hidden + * + *
    + *
  • 0 - visible.
    • + *
  • 1 - hidden.
    • + *
  • 2 - very hidden.
    • + *
+ * @param sheetIx the sheet index (0-based) + * @param hidden one of the following Workbook constants: + * Workbook.SHEET_STATE_VISIBLE, + * Workbook.SHEET_STATE_HIDDEN, or + * Workbook.SHEET_STATE_VERY_HIDDEN. + * @throws IllegalArgumentException if the supplied sheet index or state is invalid */ void setSheetHidden(int sheetIx, int hidden); } diff --git a/src/java/org/apache/poi/ss/util/WorkbookUtil.java b/src/java/org/apache/poi/ss/util/WorkbookUtil.java index 6592ad96e9..b8c60da6ed 100644 --- a/src/java/org/apache/poi/ss/util/WorkbookUtil.java +++ b/src/java/org/apache/poi/ss/util/WorkbookUtil.java @@ -17,6 +17,8 @@ package org.apache.poi.ss.util; +import org.apache.poi.ss.usermodel.Workbook; + /** * Helper methods for when working with Usermodel Workbooks @@ -129,4 +131,14 @@ public class WorkbookUtil { } } + + public static void validateSheetState(int state) { + switch(state){ + case Workbook.SHEET_STATE_VISIBLE: break; + case Workbook.SHEET_STATE_HIDDEN: break; + case Workbook.SHEET_STATE_VERY_HIDDEN: break; + default: throw new IllegalArgumentException("Ivalid sheet state : " + state + "\n" + + "Sheet state must beone of the Workbook.SHEET_STATE_* constants"); + } + } } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java index d5256209a7..71a42677bb 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java @@ -1271,28 +1271,74 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable + * Note that a sheet could instead be set to be very hidden, which is different + * ({@link #isSheetVeryHidden(int)}) + *

+ * @param sheetIx Number + * @return true if sheet is hidden + */ public boolean isSheetHidden(int sheetIx) { validateSheetIndex(sheetIx); CTSheet ctSheet = sheets.get(sheetIx).sheet; return ctSheet.getState() == STSheetState.HIDDEN; } + /** + * Check whether a sheet is very hidden. + *

+ * This is different from the normal hidden status + * ({@link #isSheetHidden(int)}) + *

+ * @param sheetIx sheet index to check + * @return true if sheet is very hidden + */ public boolean isSheetVeryHidden(int sheetIx) { validateSheetIndex(sheetIx); CTSheet ctSheet = sheets.get(sheetIx).sheet; return ctSheet.getState() == STSheetState.VERY_HIDDEN; } + /** + * Sets the visible state of this sheet. + *

+ * Calling setSheetHidden(sheetIndex, true) is equivalent to + * setSheetHidden(sheetIndex, Workbook.SHEET_STATE_HIDDEN). + *
+ * Calling setSheetHidden(sheetIndex, false) is equivalent to + * setSheetHidden(sheetIndex, Workbook.SHEET_STATE_VISIBLE). + *

+ * + * @param sheetIx the 0-based index of the sheet + * @param hidden whether this sheet is hidden + * @see #setSheetHidden(int, int) + */ public void setSheetHidden(int sheetIx, boolean hidden) { - validateSheetIndex(sheetIx); - CTSheet ctSheet = sheets.get(sheetIx).sheet; - ctSheet.setState(hidden ? STSheetState.HIDDEN : STSheetState.VISIBLE); + setSheetHidden(sheetIx, hidden ? SHEET_STATE_HIDDEN : SHEET_STATE_VISIBLE); } - public void setSheetHidden(int sheetIx, int hidden) { + /** + * Hide or unhide a sheet. + * + *
    + *
  • 0 - visible.
    • + *
  • 1 - hidden.
    • + *
  • 2 - very hidden.
    • + *
+ * @param sheetIx the sheet index (0-based) + * @param state one of the following Workbook constants: + * Workbook.SHEET_STATE_VISIBLE, + * Workbook.SHEET_STATE_HIDDEN, or + * Workbook.SHEET_STATE_VERY_HIDDEN. + * @throws IllegalArgumentException if the supplied sheet index or state is invalid + */ + public void setSheetHidden(int sheetIx, int state) { validateSheetIndex(sheetIx); + WorkbookUtil.validateSheetState(state); CTSheet ctSheet = sheets.get(sheetIx).sheet; - ctSheet.setState(STSheetState.Enum.forInt(hidden)); + ctSheet.setState(STSheetState.Enum.forInt(state + 1)); } /** diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestSheetHiding.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestSheetHiding.java index 564f2dee8c..83b952fc31 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestSheetHiding.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestSheetHiding.java @@ -19,82 +19,15 @@ package org.apache.poi.xssf.usermodel; import junit.framework.TestCase; import org.apache.poi.xssf.XSSFTestDataSamples; +import org.apache.poi.xssf.XSSFITestDataProvider; +import org.apache.poi.ss.usermodel.BaseTestSheetHiding; /** * @author Yegor Kozlov */ -public final class TestSheetHiding extends TestCase { - private XSSFWorkbook wbH; - private XSSFWorkbook wbU; - - protected void setUp() { - wbH = XSSFTestDataSamples.openSampleWorkbook("TwoSheetsOneHidden.xlsx"); - wbU = XSSFTestDataSamples.openSampleWorkbook("TwoSheetsNoneHidden.xlsx"); - } - - /** - * Test that we get the right number of sheets, - * with the right text on them, no matter what - * the hidden flags are - */ - public void testTextSheets() { - // Both should have two sheets - assertEquals(2, wbH.getNumberOfSheets()); - assertEquals(2, wbU.getNumberOfSheets()); - - // All sheets should have one row - assertEquals(0, wbH.getSheetAt(0).getLastRowNum()); - assertEquals(0, wbH.getSheetAt(1).getLastRowNum()); - assertEquals(0, wbU.getSheetAt(0).getLastRowNum()); - assertEquals(0, wbU.getSheetAt(1).getLastRowNum()); - - // All rows should have one column - assertEquals(1, wbH.getSheetAt(0).getRow(0).getLastCellNum()); - assertEquals(1, wbH.getSheetAt(1).getRow(0).getLastCellNum()); - assertEquals(1, wbU.getSheetAt(0).getRow(0).getLastCellNum()); - assertEquals(1, wbU.getSheetAt(1).getRow(0).getLastCellNum()); - - // Text should be sheet based - assertEquals("Sheet1A1", wbH.getSheetAt(0).getRow(0).getCell(0).getRichStringCellValue().getString()); - assertEquals("Sheet2A1", wbH.getSheetAt(1).getRow(0).getCell(0).getRichStringCellValue().getString()); - assertEquals("Sheet1A1", wbU.getSheetAt(0).getRow(0).getCell(0).getRichStringCellValue().getString()); - assertEquals("Sheet2A1", wbU.getSheetAt(1).getRow(0).getCell(0).getRichStringCellValue().getString()); - } - - /** - * Check that we can get and set the hidden flags - * as expected - */ - public void testHideUnHideFlags() { - assertTrue(wbH.isSheetHidden(0)); - assertFalse(wbH.isSheetHidden(1)); - assertFalse(wbU.isSheetHidden(0)); - assertFalse(wbU.isSheetHidden(1)); - } - - /** - * Turn the sheet with none hidden into the one with - * one hidden - */ - public void testHide() { - wbU.setSheetHidden(0, true); - assertTrue(wbU.isSheetHidden(0)); - assertFalse(wbU.isSheetHidden(1)); - XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wbU); - assertTrue(wb2.isSheetHidden(0)); - assertFalse(wb2.isSheetHidden(1)); - } - - /** - * Turn the sheet with one hidden into the one with - * none hidden - */ - public void testUnHide() { - wbH.setSheetHidden(0, false); - assertFalse(wbH.isSheetHidden(0)); - assertFalse(wbH.isSheetHidden(1)); - XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(wbH); - assertFalse(wb2.isSheetHidden(0)); - assertFalse(wb2.isSheetHidden(1)); +public final class TestSheetHiding extends BaseTestSheetHiding { + public TestSheetHiding() { + super(XSSFITestDataProvider.instance, + "TwoSheetsOneHidden.xlsx", "TwoSheetsNoneHidden.xlsx"); } } diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java index 293e237b0e..a7f52e9a6d 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java @@ -1240,7 +1240,7 @@ public final class TestBugs extends BaseTestBugzillaIssues { assertTrue(wb.isSheetVeryHidden(2)); // Change 0 to be very hidden, and re-load - wb.setSheetHidden(0, 2); + wb.setSheetHidden(0, HSSFWorkbook.SHEET_STATE_VERY_HIDDEN); HSSFWorkbook nwb = writeOutAndReadBack(wb); diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestSheetHiding.java b/src/testcases/org/apache/poi/hssf/usermodel/TestSheetHiding.java index 52b88d34ef..4e64d97dcd 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestSheetHiding.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestSheetHiding.java @@ -23,90 +23,15 @@ import java.io.ByteArrayOutputStream; import junit.framework.TestCase; import org.apache.poi.hssf.HSSFTestDataSamples; +import org.apache.poi.hssf.HSSFITestDataProvider; +import org.apache.poi.ss.usermodel.BaseTestSheetHiding; /** - * Tests for how HSSFWorkbook behaves with XLS files - * with a WORKBOOK directory entry (instead of the more - * usual, Workbook) + * @author Yegor Kozlov */ -public final class TestSheetHiding extends TestCase { - private HSSFWorkbook wbH; - private HSSFWorkbook wbU; - - protected void setUp() { - wbH = HSSFTestDataSamples.openSampleWorkbook("TwoSheetsOneHidden.xls"); - wbU = HSSFTestDataSamples.openSampleWorkbook("TwoSheetsNoneHidden.xls"); - } - - /** - * Test that we get the right number of sheets, - * with the right text on them, no matter what - * the hidden flags are - */ - public void testTextSheets() { - // Both should have two sheets - assertEquals(2, wbH.getNumberOfSheets()); - assertEquals(2, wbU.getNumberOfSheets()); - - // All sheets should have one row - assertEquals(0, wbH.getSheetAt(0).getLastRowNum()); - assertEquals(0, wbH.getSheetAt(1).getLastRowNum()); - assertEquals(0, wbU.getSheetAt(0).getLastRowNum()); - assertEquals(0, wbU.getSheetAt(1).getLastRowNum()); - - // All rows should have one column - assertEquals(1, wbH.getSheetAt(0).getRow(0).getLastCellNum()); - assertEquals(1, wbH.getSheetAt(1).getRow(0).getLastCellNum()); - assertEquals(1, wbU.getSheetAt(0).getRow(0).getLastCellNum()); - assertEquals(1, wbU.getSheetAt(1).getRow(0).getLastCellNum()); - - // Text should be sheet based - assertEquals("Sheet1A1", wbH.getSheetAt(0).getRow(0).getCell(0).getRichStringCellValue().getString()); - assertEquals("Sheet2A1", wbH.getSheetAt(1).getRow(0).getCell(0).getRichStringCellValue().getString()); - assertEquals("Sheet1A1", wbU.getSheetAt(0).getRow(0).getCell(0).getRichStringCellValue().getString()); - assertEquals("Sheet2A1", wbU.getSheetAt(1).getRow(0).getCell(0).getRichStringCellValue().getString()); - } - - /** - * Check that we can get and set the hidden flags - * as expected - */ - public void testHideUnHideFlags() { - assertTrue(wbH.isSheetHidden(0)); - assertFalse(wbH.isSheetHidden(1)); - assertFalse(wbU.isSheetHidden(0)); - assertFalse(wbU.isSheetHidden(1)); - } - - /** - * Turn the sheet with none hidden into the one with - * one hidden - */ - public void testHide() throws Exception { - wbU.setSheetHidden(0, true); - assertTrue(wbU.isSheetHidden(0)); - assertFalse(wbU.isSheetHidden(1)); - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wbU.write(out); - out.close(); - HSSFWorkbook wb2 = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertTrue(wb2.isSheetHidden(0)); - assertFalse(wb2.isSheetHidden(1)); - } - - /** - * Turn the sheet with one hidden into the one with - * none hidden - */ - public void testUnHide() throws Exception { - wbH.setSheetHidden(0, false); - assertFalse(wbH.isSheetHidden(0)); - assertFalse(wbH.isSheetHidden(1)); - ByteArrayOutputStream out = new ByteArrayOutputStream(); - wbH.write(out); - out.close(); - HSSFWorkbook wb2 = new HSSFWorkbook(new ByteArrayInputStream(out.toByteArray())); - assertFalse(wb2.isSheetHidden(0)); - assertFalse(wb2.isSheetHidden(1)); - } +public final class TestSheetHiding extends BaseTestSheetHiding { + public TestSheetHiding() { + super(HSSFITestDataProvider.instance, + "TwoSheetsOneHidden.xls", "TwoSheetsNoneHidden.xls"); + } } diff --git a/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetHiding.java b/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetHiding.java new file mode 100644 index 0000000000..1fe3c8a175 --- /dev/null +++ b/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetHiding.java @@ -0,0 +1,148 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.ss.usermodel; + +import junit.framework.TestCase; + +import org.apache.poi.ss.ITestDataProvider; + +/** + */ +public abstract class BaseTestSheetHiding extends TestCase { + + protected final ITestDataProvider _testDataProvider; + protected Workbook wbH; + protected Workbook wbU; + + private String _file1, _file2; + + /** + * @param testDataProvider an object that provides test data in HSSF / specific way + */ + protected BaseTestSheetHiding(ITestDataProvider testDataProvider, + String file1, String file2) { + _testDataProvider = testDataProvider; + _file1 = file1; + _file2 = file2; + } + + protected void setUp() { + wbH = _testDataProvider.openSampleWorkbook(_file1); + wbU = _testDataProvider.openSampleWorkbook(_file2); + } + + + public final void testSheetHidden() { + Workbook wb = _testDataProvider.createWorkbook(); + Sheet sh = wb.createSheet("MySheet"); + + assertFalse(wb.isSheetHidden(0)); + assertFalse(wb.isSheetVeryHidden(0)); + + wb.setSheetHidden(0, Workbook.SHEET_STATE_HIDDEN); + assertTrue(wb.isSheetHidden(0)); + assertFalse(wb.isSheetVeryHidden(0)); + + wb.setSheetHidden(0, Workbook.SHEET_STATE_VERY_HIDDEN); + assertFalse(wb.isSheetHidden(0)); + assertTrue(wb.isSheetVeryHidden(0)); + + wb.setSheetHidden(0, Workbook.SHEET_STATE_VISIBLE); + assertFalse(wb.isSheetHidden(0)); + assertFalse(wb.isSheetVeryHidden(0)); + + try { + wb.setSheetHidden(0, -1); + fail("expectd exception"); + } catch (IllegalArgumentException e){ + // ok + } + try { + wb.setSheetHidden(0, 3); + fail("expectd exception"); + } catch (IllegalArgumentException e){ + // ok + } + } + + /** + * Test that we get the right number of sheets, + * with the right text on them, no matter what + * the hidden flags are + */ + public void testTextSheets() { + // Both should have two sheets + assertEquals(2, wbH.getNumberOfSheets()); + assertEquals(2, wbU.getNumberOfSheets()); + + // All sheets should have one row + assertEquals(0, wbH.getSheetAt(0).getLastRowNum()); + assertEquals(0, wbH.getSheetAt(1).getLastRowNum()); + assertEquals(0, wbU.getSheetAt(0).getLastRowNum()); + assertEquals(0, wbU.getSheetAt(1).getLastRowNum()); + + // All rows should have one column + assertEquals(1, wbH.getSheetAt(0).getRow(0).getLastCellNum()); + assertEquals(1, wbH.getSheetAt(1).getRow(0).getLastCellNum()); + assertEquals(1, wbU.getSheetAt(0).getRow(0).getLastCellNum()); + assertEquals(1, wbU.getSheetAt(1).getRow(0).getLastCellNum()); + + // Text should be sheet based + assertEquals("Sheet1A1", wbH.getSheetAt(0).getRow(0).getCell(0).getRichStringCellValue().getString()); + assertEquals("Sheet2A1", wbH.getSheetAt(1).getRow(0).getCell(0).getRichStringCellValue().getString()); + assertEquals("Sheet1A1", wbU.getSheetAt(0).getRow(0).getCell(0).getRichStringCellValue().getString()); + assertEquals("Sheet2A1", wbU.getSheetAt(1).getRow(0).getCell(0).getRichStringCellValue().getString()); + } + + /** + * Check that we can get and set the hidden flags + * as expected + */ + public void testHideUnHideFlags() { + assertTrue(wbH.isSheetHidden(0)); + assertFalse(wbH.isSheetHidden(1)); + assertFalse(wbU.isSheetHidden(0)); + assertFalse(wbU.isSheetHidden(1)); + } + + /** + * Turn the sheet with none hidden into the one with + * one hidden + */ + public void testHide() { + wbU.setSheetHidden(0, true); + assertTrue(wbU.isSheetHidden(0)); + assertFalse(wbU.isSheetHidden(1)); + Workbook wb2 = _testDataProvider.writeOutAndReadBack(wbU); + assertTrue(wb2.isSheetHidden(0)); + assertFalse(wb2.isSheetHidden(1)); + } + + /** + * Turn the sheet with one hidden into the one with + * none hidden + */ + public void testUnHide() { + wbH.setSheetHidden(0, false); + assertFalse(wbH.isSheetHidden(0)); + assertFalse(wbH.isSheetHidden(1)); + Workbook wb2 = _testDataProvider.writeOutAndReadBack(wbH); + assertFalse(wb2.isSheetHidden(0)); + assertFalse(wb2.isSheetHidden(1)); + } +} \ No newline at end of file -- 2.39.5