| /* ==================================================================== |
| 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.xssf.usermodel; |
| |
| import java.io.IOException; |
| |
| import junit.framework.TestCase; |
| |
| 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.XSSFTestDataSamples; |
| |
| public final class TestXSSFSheetRowGrouping extends TestCase { |
| |
| private static final int ROWS_NUMBER = 200; |
| private static final int GROUP_SIZE = 5; |
| |
| //private int o_groupsNumber = 0; |
| |
| public void test55640() throws IOException { |
| //long startTime = System.currentTimeMillis(); |
| Workbook wb = new XSSFWorkbook(); |
| fillData(wb); |
| writeToFile(wb); |
| |
| //System.out.println("Number of groups: " + o_groupsNumber); |
| //System.out.println("Execution time: " + (System.currentTimeMillis()-startTime) + " ms"); |
| } |
| |
| |
| private void fillData(Workbook p_wb) { |
| Sheet sheet = p_wb.createSheet("sheet123"); |
| sheet.setRowSumsBelow(false); |
| |
| for (int i = 0; i < ROWS_NUMBER; i++) { |
| Row row = sheet.createRow(i); |
| Cell cell = row.createCell(0); |
| cell.setCellValue(i+1); |
| } |
| |
| int i = 1; |
| while (i < ROWS_NUMBER) { |
| int end = i+(GROUP_SIZE-2); |
| int start = i; // natural order |
| // int start = end - 1; // reverse order |
| while (start < end) { // natural order |
| // while (start >= i) { // reverse order |
| sheet.groupRow(start, end); |
| //o_groupsNumber++; |
| boolean collapsed = isCollapsed(); |
| //System.out.println("Set group " + start + "->" + end + " to " + collapsed); |
| sheet.setRowGroupCollapsed(start, collapsed); |
| start++; // natural order |
| // start--; // reverse order |
| } |
| i += GROUP_SIZE; |
| } |
| } |
| |
| private boolean isCollapsed() { |
| return Math.random() > 0.5d; |
| } |
| |
| private void writeToFile(Workbook p_wb) throws IOException { |
| // FileOutputStream fileOut = new FileOutputStream("/tmp/55640.xlsx"); |
| // try { |
| // p_wb.write(fileOut); |
| // } finally { |
| // fileOut.close(); |
| // } |
| assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(p_wb)); |
| } |
| |
| public void test55640reduce1() throws IOException { |
| Workbook wb = new XSSFWorkbook(); |
| Sheet sheet = wb.createSheet("sheet123"); |
| sheet.setRowSumsBelow(false); |
| |
| for (int i = 0; i < ROWS_NUMBER; i++) { |
| Row row = sheet.createRow(i); |
| Cell cell = row.createCell(0); |
| cell.setCellValue(i+1); |
| } |
| |
| int i = 1; |
| while (i < ROWS_NUMBER) { |
| int end = i+(GROUP_SIZE-2); |
| int start = i; // natural order |
| while (start < end) { // natural order |
| sheet.groupRow(start, end); |
| //o_groupsNumber++; |
| boolean collapsed = start % 2 == 0 ? false : true; |
| //System.out.println("Set group " + start + "->" + end + " to " + collapsed); |
| sheet.setRowGroupCollapsed(start, collapsed); |
| start++; // natural order |
| } |
| i += GROUP_SIZE; |
| } |
| writeToFile(wb); |
| } |
| |
| |
| public void test55640_VerifyCases() throws IOException { |
| // NOTE: This is currently based on current behavior of POI, somehow |
| // what POI returns in the calls to collapsed/hidden is not fully matching |
| // the examples in the spec or I did not fully understand how POI stores the data internally... |
| |
| // all expanded |
| verifyGroupCollapsed( |
| // level1, level2, level3 |
| false, false, false, |
| // collapsed: |
| new Boolean[] { false, false, false, false, false}, |
| // hidden: |
| new boolean[] { false, false, false, false, false}, |
| // outlineLevel |
| new int[] { 1, 2, 3, 3, 3 } |
| ); |
| |
| |
| // Level 1 collapsed, others expanded, should only have 4 rows, all hidden: |
| verifyGroupCollapsed( |
| // level1, level2, level3 |
| true, false, false, |
| // collapsed: |
| new Boolean[] { false, false, false, false, false}, |
| // hidden: |
| new boolean[] { true, true, true, true, true}, |
| // outlineLevel |
| new int[] { 1, 2, 3, 3, 3 } |
| ); |
| |
| // Level 1 and 2 collapsed, Level 3 expanded, |
| verifyGroupCollapsed( |
| // level1, level2, level3 |
| true, true, false, |
| // collapsed: |
| new Boolean[] { false, false, false, false, true, false}, |
| // hidden: |
| new boolean[] { true, true, true, true, true, false}, |
| // outlineLevel |
| new int[] { 1, 2, 3, 3, 3, 0 } |
| ); |
| |
| // Level 1 collapsed, Level 2 expanded, Level 3 collapsed |
| verifyGroupCollapsed( |
| // level1, level2, level3 |
| true, false, true, |
| // collapsed: |
| new Boolean[] { false, false, false, false, false, true}, |
| // hidden: |
| new boolean[] { true, true, true, true, true, false}, |
| // outlineLevel |
| new int[] { 1, 2, 3, 3, 3, 0 } |
| ); |
| |
| // Level 2 collapsed, others expanded: |
| verifyGroupCollapsed( |
| // level1, level2, level3 |
| false, true, false, |
| // collapsed: |
| new Boolean[] { false, false, false, false, false, false}, |
| // hidden: |
| new boolean[] { false, true, true, true, true, false}, |
| // outlineLevel |
| new int[] { 1, 2, 3, 3, 3, 0 } |
| ); |
| |
| // Level 3 collapsed, others expanded |
| verifyGroupCollapsed( |
| // level1, level2, level3 |
| false, false, true, |
| // collapsed: |
| new Boolean[] { false, false, false, false, false, true}, |
| // hidden: |
| new boolean[] { false, false, true, true, true, false}, |
| // outlineLevel |
| new int[] { 1, 2, 3, 3, 3, 0 } |
| ); |
| |
| // All collapsed |
| verifyGroupCollapsed( |
| // level1, level2, level3 |
| true, true, true, |
| // collapsed: |
| new Boolean[] { false, false, false, false, true, true}, |
| // hidden: |
| new boolean[] { true, true, true, true, true, false}, |
| // outlineLevel |
| new int[] { 1, 2, 3, 3, 3, 0 } |
| ); |
| } |
| |
| |
| private void verifyGroupCollapsed(boolean level1, boolean level2, boolean level3, |
| Boolean[] collapsed, boolean[] hidden, int[] outlineLevel) throws IOException { |
| Workbook wb = new XSSFWorkbook(); |
| Sheet sheet = wb.createSheet("sheet123"); |
| |
| for (int i = 0; i < 4; i++) { |
| sheet.createRow(i); |
| } |
| |
| sheet.groupRow(0, 4); |
| sheet.groupRow(1, 4); |
| sheet.groupRow(2, 4); |
| |
| sheet.setRowGroupCollapsed(0, level1); |
| sheet.setRowGroupCollapsed(1, level2); |
| sheet.setRowGroupCollapsed(2, level3); |
| |
| checkWorkbookGrouping(wb, collapsed, hidden, outlineLevel); |
| } |
| |
| public void test55640_VerifyCasesSpec() throws IOException { |
| // NOTE: This is currently based on current behavior of POI, somehow |
| // what POI returns in the calls to collapsed/hidden is not fully matching |
| // the examples in the spec or I did not fully understand how POI stores the data internally... |
| |
| // all expanded |
| verifyGroupCollapsedSpec( |
| // level3, level2, level1 |
| false, false, false, |
| // collapsed: |
| new Boolean[] { false, false, false, false}, |
| // hidden: |
| new boolean[] { false, false, false, false}, |
| // outlineLevel |
| new int[] { 3, 3, 2, 1 } |
| ); |
| |
| |
| verifyGroupCollapsedSpec( |
| // level3, level2, level1 |
| false, false, true, |
| // collapsed: |
| new Boolean[] { false, false, false, true}, |
| // hidden: |
| new boolean[] { true, true, true, false}, |
| // outlineLevel |
| new int[] { 3, 3, 2, 1 } |
| ); |
| |
| verifyGroupCollapsedSpec( |
| // level3, level2, level1 |
| false, true, false, |
| // collapsed: |
| new Boolean[] { false, false, true, false}, |
| // hidden: |
| new boolean[] { true, true, true, false}, |
| // outlineLevel |
| new int[] { 3, 3, 2, 1 } |
| ); |
| |
| verifyGroupCollapsedSpec( |
| // level3, level2, level1 |
| false, true, true, |
| // collapsed: |
| new Boolean[] { false, false, true, true}, |
| // hidden: |
| new boolean[] { true, true, true, false}, |
| // outlineLevel |
| new int[] { 3, 3, 2, 1 } |
| ); |
| } |
| |
| private void verifyGroupCollapsedSpec(boolean level1, boolean level2, boolean level3, |
| Boolean[] collapsed, boolean[] hidden, int[] outlineLevel) throws IOException { |
| Workbook wb = new XSSFWorkbook(); |
| Sheet sheet = wb.createSheet("sheet123"); |
| |
| for (int i = 5; i < 9; i++) { |
| sheet.createRow(i); |
| } |
| |
| sheet.groupRow(5, 6); |
| sheet.groupRow(5, 7); |
| sheet.groupRow(5, 8); |
| |
| sheet.setRowGroupCollapsed(6, level1); |
| sheet.setRowGroupCollapsed(7, level2); |
| sheet.setRowGroupCollapsed(8, level3); |
| |
| checkWorkbookGrouping(wb, collapsed, hidden, outlineLevel); |
| } |
| |
| private void checkWorkbookGrouping(Workbook wb, Boolean[] collapsed, boolean[] hidden, int[] outlineLevel) throws IOException { |
| printWorkbook(wb); |
| Sheet sheet = wb.getSheetAt(0); |
| |
| assertEquals(collapsed.length, hidden.length); |
| assertEquals(collapsed.length, outlineLevel.length); |
| assertEquals("Expected " + collapsed.length + " rows with collapsed state, but had " + (sheet.getLastRowNum()-sheet.getFirstRowNum()+1) + " rows (" |
| + sheet.getFirstRowNum() + "-" + sheet.getLastRowNum() + ")", |
| collapsed.length, sheet.getLastRowNum()-sheet.getFirstRowNum()+1); |
| for(int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum();i++) { |
| if(collapsed[i-sheet.getFirstRowNum()] == null) { |
| continue; |
| } |
| XSSFRow row = (XSSFRow) sheet.getRow(i); |
| assertNotNull("Could not read row " + i, row); |
| assertNotNull("Could not read row " + i, row.getCTRow()); |
| assertEquals("Row: " + i + ": collapsed", collapsed[i-sheet.getFirstRowNum()].booleanValue(), row.getCTRow().getCollapsed()); |
| assertEquals("Row: " + i + ": hidden", hidden[i-sheet.getFirstRowNum()], row.getCTRow().getHidden()); |
| |
| assertEquals("Row: " + i + ": level", outlineLevel[i-sheet.getFirstRowNum()], row.getCTRow().getOutlineLevel()); |
| } |
| |
| writeToFile(wb); |
| } |
| |
| |
| public void test55640working() throws IOException { |
| Workbook wb = new XSSFWorkbook(); |
| Sheet sheet = wb.createSheet("sheet123"); |
| |
| sheet.groupRow(1, 4); |
| sheet.groupRow(2, 5); |
| sheet.groupRow(3, 6); |
| |
| sheet.setRowGroupCollapsed(1, true); |
| sheet.setRowGroupCollapsed(2, false); |
| sheet.setRowGroupCollapsed(3, false); |
| |
| writeToFile(wb); |
| } |
| |
| // just used for printing out contents of spreadsheets |
| public void notRuntest55640printSample() { |
| XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("55640.xlsx"); |
| printWorkbook(wb); |
| |
| wb = XSSFTestDataSamples.openSampleWorkbook("GroupTest.xlsx"); |
| printWorkbook(wb); |
| } |
| |
| private void printWorkbook(Workbook wb) { |
| // disable all output for now... |
| // Sheet sheet = wb.getSheetAt(0); |
| // |
| // for(Iterator<Row> it = sheet.rowIterator();it.hasNext();) { |
| // XSSFRow row = (XSSFRow) it.next(); |
| // boolean collapsed = row.getCTRow().getCollapsed(); |
| // boolean hidden = row.getCTRow().getHidden(); |
| // short level = row.getCTRow().getOutlineLevel(); |
| // |
| // System.out.println("Row: " + row.getRowNum() + ": Level: " + level + " Collapsed: " + collapsed + " Hidden: " + hidden); |
| // } |
| } |
| |
| public void testGroupingTest() throws IOException { |
| XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("GroupTest.xlsx"); |
| |
| assertEquals(31, wb.getSheetAt(0).getLastRowNum()); |
| |
| // NOTE: This is currently based on current behavior of POI, somehow |
| // what POI returns in the calls to collapsed/hidden is not fully matching |
| // the examples in the spec or I did not fully understand how POI stores the data internally... |
| checkWorkbookGrouping(wb, |
| new Boolean [] { |
| // 0-4 |
| false, false, false, false, false, null, null, |
| // 7-11 |
| false, false, true, true, true, null, null, |
| // 14-18 |
| false, false, true, false, false, null, |
| // 20-24 |
| false, false, true, true, false, null, null, |
| // 27-31 |
| false, false, false, true, false }, |
| new boolean[] { |
| // 0-4 |
| false, false, false, false, false, false, false, |
| // 7-11 |
| true, true, true, true, false, false, false, |
| // 14-18 |
| true, true, false, false, false, false, |
| // 20-24 |
| true, true, true, false, false, false, false, |
| // 27-31 |
| true, true, true, true, false }, |
| // outlineLevel |
| new int[] { |
| // 0-4 |
| 3, 3, 2, 1, 0, 0, 0, |
| // 7-11 |
| 3, 3, 2, 1, 0, 0, 0, |
| // 14-18 |
| 3, 3, 2, 1, 0, 0, |
| // 20-24 |
| 3, 3, 2, 1, 0, 0, 0, |
| // 27-31 |
| 3, 3, 2, 1, 0, |
| } |
| ); |
| /* |
| Row: 0: Level: 3 Collapsed: false Hidden: false |
| Row: 1: Level: 3 Collapsed: false Hidden: false |
| Row: 2: Level: 2 Collapsed: false Hidden: false |
| Row: 3: Level: 1 Collapsed: false Hidden: false |
| Row: 4: Level: 0 Collapsed: false Hidden: false |
| Row: 7: Level: 3 Collapsed: false Hidden: true |
| Row: 8: Level: 3 Collapsed: false Hidden: true |
| Row: 9: Level: 2 Collapsed: true Hidden: true |
| Row: 10: Level: 1 Collapsed: true Hidden: true |
| Row: 11: Level: 0 Collapsed: true Hidden: false |
| Row: 14: Level: 3 Collapsed: false Hidden: true |
| Row: 15: Level: 3 Collapsed: false Hidden: true |
| Row: 16: Level: 2 Collapsed: true Hidden: false |
| Row: 17: Level: 1 Collapsed: false Hidden: false |
| Row: 18: Level: 0 Collapsed: false Hidden: false |
| Row: 20: Level: 3 Collapsed: false Hidden: true |
| Row: 21: Level: 3 Collapsed: false Hidden: true |
| Row: 22: Level: 2 Collapsed: true Hidden: true |
| Row: 23: Level: 1 Collapsed: true Hidden: false |
| Row: 24: Level: 0 Collapsed: false Hidden: false |
| Row: 27: Level: 3 Collapsed: false Hidden: true |
| Row: 28: Level: 3 Collapsed: false Hidden: true |
| Row: 29: Level: 2 Collapsed: false Hidden: true |
| Row: 30: Level: 1 Collapsed: true Hidden: true |
| Row: 31: Level: 0 Collapsed: true Hidden: false |
| */ |
| } |
| } |