SheetPivoter { public void pivot(final Sheet pivotSourceSheet, final Sheet pivotTargetSheet) { sourceSheet = pivotSourceSheet; targetSheet = pivotTargetSheet; annotations = new AnnotationList(new ArrayList<AnnotationTriplet>()); getAnnotationsFromSource(); setOffsets(); validateAndAdaptSourceDataIfNeeded(); defineSomeCellStyles(); createEmptyHeaderRowsAndCells(); fillInHeaderRows(); pivotSourceRows(); addSummations(); }  void pivot(final Sheet pivotSourceSheet, final Sheet pivotTargetSheet);  }
@Test public void headerRow_Fill_works() { List<String> annotations = Arrays.asList("row", "column", "column", "column", "value", "value", "deco", "deco"); List<Integer> orderNumbers = Arrays.asList(0, 1, 2, 3, 1, 2, 1, 2); List<String> typeList = Arrays.asList(null, null, null, null, "SUM", "SUM", null, null); List<String> fieldNameList = Arrays.asList("fn-r1", "fn-c", "fn-c1", "fn-c2", "fn-v1", "fn-v2", "fn-d1", "fn-d2"); List<?> v1 = Arrays.asList("r1", "a", "c1-1", "c2-1", null, null, "r1d1", null); List<?> v2 = Arrays.asList("r2", "a", "c1-2", "c2-1", null, null, null, "r2d2"); List<?> v3 = Arrays.asList("r3", "b", "c1-3", "c2-2", null, null, "r3d1", "r3d2"); sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1, v2, v3); p.pivot(sourceSheet, targetSheet); Assertions.assertThat(targetSheet.getLastRowNum()).isEqualTo(7); testRow(0, null, null, "fn-c", "a", null, null, null, null, null, null, null, null, null, null, null, "b"); testRow(1, null, null, "fn-c1", "c1-1", null, null, null, "c1-2", null, null, null, "c1-3", null, null, null, "c1-1"); testRow(2, null, null, "fn-c2", "c2-1", null, "c2-2", null, "c2-1", null, "c2-2", null, "c2-1", null, "c2-2", null, "c2-1"); testRow(3, "fn-r1", "fn-d1", "fn-d2", "fn-v1 (sum)", "fn-v2 (sum)", "fn-v1 (sum)", "fn-v2 (sum)", "fn-v1 (sum)", "fn-v2 (sum)"); testRow(4, "r1", "r1d1", null); testRow(5, "r2", null, "r2d2"); testRow(6, "r3", "r3d1", "r3d2"); }
@Test public void testPivoting_works() throws Exception { List<String> annotations = Arrays.asList("row", "value", "column", "deco", "deco"); List<Integer> orderNumbers = Arrays.asList(0, 1, 1, 2, 1); List<String> typeList = Arrays.asList(null, "SUM", null, null, null); List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn3", "fn4", "fn5"); List<?> v1 = Arrays.asList("l1", 1, "c1", "d1-f-l1", "d2-f-l1"); List<?> v2 = Arrays.asList("l2", 2, "c2", "deco for l2", null); List<?> v3 = Arrays.asList("l1", 3, "c1", "other deco not used", null); List<?> v4 = Arrays.asList("l3", 33, "c1", null, null); sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1, v2, v3, v4); p.pivot(sourceSheet, targetSheet); Assertions.assertThat(targetSheet.getLastRowNum()).isEqualTo(5); testRow(0, null, null, "fn3", "c1", "c2"); testRow(1, "fn1", "fn5", "fn4", "fn2 (sum)", "fn2 (sum)"); testRow(2, "l1", "d2-f-l1", "d1-f-l1", 4, null); Assertions.assertThat(targetSheet.getRow(2).getCell(5).getCellFormula()).isEqualTo("SUM(D3:E3)"); testRow(3, "l2", null, "deco for l2", null, 2); Assertions.assertThat(targetSheet.getRow(3).getCell(5).getCellFormula()).isEqualTo("SUM(D4:E4)"); testRow(4, "l3", null, null, 33, null); Assertions.assertThat(targetSheet.getRow(4).getCell(5).getCellFormula()).isEqualTo("SUM(D5:E5)"); Assertions.assertThat(targetSheet.getRow(5).getCell(3).getCellFormula()).isEqualTo("SUM(D3:D5)"); Assertions.assertThat(targetSheet.getRow(5).getCell(4).getCellFormula()).isEqualTo("SUM(E3:E5)"); Assertions.assertThat(targetSheet.getRow(5).getCell(5).getCellFormula()).isEqualTo("SUM(F3:F5)"); }
@Test public void testPivoting_WithTwoValues_works() throws Exception { List<String> annotations = Arrays.asList("row", "value", "column", "deco", "deco", "value"); List<Integer> orderNumbers = Arrays.asList(0, 1, 1, 2, 1, 2); List<String> typeList = Arrays.asList(null, "SUM", null, null, null, "COUNT"); List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn3", "fn4", "fn5", "fn2a"); List<?> v1 = Arrays.asList("l1", 1, "c1", "d1-f-l1", "d2-f-l1", 2); List<?> v2 = Arrays.asList("l2", 2, "c2", "deco for l2", null, null); List<?> v3 = Arrays.asList("l1", 3, "c1", "other deco not used", null, 2); List<?> v4 = Arrays.asList("l3", 33, "c1", null, null, 2); sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1, v2, v3, v4); p.pivot(sourceSheet, targetSheet); Assertions.assertThat(targetSheet.getLastRowNum()).isEqualTo(5); testRow(0, null, null, "fn3", "c1", null, "c2", null); testRow(1, "fn1", "fn5", "fn4", "fn2 (sum)", "fn2a (count)", "fn2 (sum)", "fn2a (count)"); testRow(2, "l1", "d2-f-l1", "d1-f-l1", 4, 2, null, null); testRow(3, "l2", null, "deco for l2", null, null, 2, 1); testRow(4, "l3", null, null, 33, 1, null, null); Assertions.assertThat(targetSheet.getRow(5).getCell(3).getCellFormula()).isEqualTo("SUM(D3:D5)"); Assertions.assertThat(targetSheet.getRow(5).getCell(4).getCellFormula()).isEqualTo("SUM(E3:E5)"); Assertions.assertThat(targetSheet.getRow(5).getCell(5).getCellFormula()).isEqualTo("SUM(F3:F5)"); Assertions.assertThat(targetSheet.getRow(5).getCell(6).getCellFormula()).isEqualTo("SUM(G3:G5)"); }
@Test public void testPivoting_decovalues() throws Exception { List<String> annotations = Arrays.asList("deco", "row", "value", "column"); List<Integer> orderNumbers = Arrays.asList(1, 0, 1, 1); List<String> typeList = Arrays.asList(null, null, "SUM", null); List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn3", "fn4"); List<?> v1 = Arrays.asList("deco used", "l1", 1, "c1"); List<?> v2 = Arrays.asList(null, "l1", 1, "c2"); List<?> v3 = Arrays.asList("deco not used", "l1", 1, "c3"); sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1, v2, v3); p.pivot(sourceSheet, targetSheet); Assertions.assertThat(targetSheet.getLastRowNum()).isEqualTo(3); testRow(0, null, "fn4", "c1", "c2", "c3"); testRow(1, "fn2", "fn1", "fn3 (sum)", "fn3 (sum)", "fn3 (sum)"); testRow(2, "l1", "deco used", 1, 1, 1); }
@Test public void testPivoting_NotSupportedValueType_Boolean() throws Exception { List<String> annotations = Arrays.asList("row", "value", "column"); List<Integer> orderNumbers = Arrays.asList(0, 1, 1); List<String> typeList = Arrays.asList(null, "SUM", null); List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn3"); List<?> v1 = Arrays.asList("l1", true, "c1"); List<?> v2 = Arrays.asList("l2", false, "c2"); List<?> v3 = Arrays.asList("l1", true, "c1"); sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1, v2, v3); p.pivot(sourceSheet, targetSheet); Assertions.assertThat(targetSheet.getLastRowNum()).isEqualTo(4); testRow(0, "fn3", "c1", "c2"); testRow(1, "fn1", "fn2 (sum)", "fn2 (sum)"); testRow(2, "l1", null, null); testRow(3, "l2", null, null); }
@Test public void testPivoting_Numeric_As_Rowlabel() throws Exception { List<String> annotations = Arrays.asList("row", "value", "column"); List<Integer> orderNumbers = Arrays.asList(0, 1, 1); List<String> typeList = Arrays.asList(null, "SUM", null); List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn3"); List<?> v1 = Arrays.asList(1, 1, "c1"); List<?> v2 = Arrays.asList(2, 1, "c2"); List<?> v3 = Arrays.asList(1, 1, "c1"); sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1, v2, v3); p.pivot(sourceSheet, targetSheet); Assertions.assertThat(targetSheet.getLastRowNum()).isEqualTo(4); testRow(0, "fn3", "c1", "c2"); testRow(1, "fn1", "fn2 (sum)", "fn2 (sum)"); testRow(2, 1, 2, null); testRow(3, 2, null, 1); }
@Test public void emptyRowValuesAreSupported() { List<String> annotations = Arrays.asList("row", "value", "column"); List<Integer> orderNumbers = Arrays.asList(0, 1, 1); List<String> typeList = Arrays.asList(null, "SUM", null); List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn3"); List<?> v1 = Arrays.asList(null, 1, "c1"); List<?> v2 = Arrays.asList("", 1, "c1"); sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1, v2); p.pivot(sourceSheet, targetSheet); Assertions.assertThat(targetSheet.getLastRowNum()).isEqualTo(3); testRow(0, "fn3", "c1"); testRow(1, "fn1", "fn2 (sum)"); testRow(2, "(empty)", 2); }
@Test public void emptyColumnValuesAreSupported() { List<String> annotations = Arrays.asList("row", "value", "column"); List<Integer> orderNumbers = Arrays.asList(0, 1, 1); List<String> typeList = Arrays.asList(null, "SUM", null); List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn3"); List<?> v1 = Arrays.asList("l1", 1, null); List<?> v2 = Arrays.asList("l1", 1, ""); sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1, v2); p.pivot(sourceSheet, targetSheet); Assertions.assertThat(targetSheet.getLastRowNum()).isEqualTo(3); testRow(0, "fn3", "(empty)"); testRow(1, "fn1", "fn2 (sum)"); testRow(2, "l1", 2); }
@Test public void empty_value_rows_works(){ List<String> annotations = Arrays.asList("row", "value", "column"); List<Integer> orderNumbers = Arrays.asList(0, 0, 0); List<String> typeList = Arrays.asList(null, "SUM", null); List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn3"); sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList); p.pivot(sourceSheet, targetSheet); Assertions.assertThat(p.valueRowOffsetY).isEqualTo(1); Assertions.assertThat(p.columnLabelOffsetX).isEqualTo(1); Assertions.assertThat(p.decoRowOffsetX).isEqualTo(1); Assertions.assertThat(p.valuesStartAtRownumber).isEqualTo(2); Assertions.assertThat(targetSheet.getLastRowNum()).isEqualTo(2); Assertions.assertThat(targetSheet.getRow(1).getLastCellNum()).isEqualTo((short)2); testRow(0, "fn3", null); testRow(1, "fn1", null); Assertions.assertThat(targetSheet.getRow(2).getCell(0).getCellType()).isEqualTo(CellType.BLANK); Assertions.assertThat(targetSheet.getRow(2).getCell(1).getCellType()).isEqualTo(CellType.FORMULA); Assertions.assertThat(targetSheet.getRow(2).getCell(1).getCellFormula()).isEqualTo("SUM(B3:B2)"); }
@Test public void setOffsets_works(){ List<String> annotations = Arrays.asList("row", "value", "value", "column", "deco"); List<Integer> orderNumbers = Arrays.asList(0, 0, 1, 1, 1); List<String> typeList = Arrays.asList(null, "SUM", "SUM", null, null); List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn2a", "fn3", "fn4"); sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList); p.pivot(sourceSheet, targetSheet); Assertions.assertThat(p.valueRowOffsetY).isEqualTo(1); Assertions.assertThat(p.columnLabelOffsetX).isEqualTo(2); Assertions.assertThat(p.decoRowOffsetX).isEqualTo(1); Assertions.assertThat(p.valuesStartAtRownumber).isEqualTo(2); annotations = Arrays.asList("row", "value", "value", "column", "deco", "column"); orderNumbers = Arrays.asList(0, 0, 1, 1, 1, 2); typeList = Arrays.asList(null, "SUM", "SUM", null, null, null); fieldNameList = Arrays.asList("fn1", "fn2", "fn2a", "fn3", "fn4", "fn3a"); sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList); p.pivot(sourceSheet, targetSheet); Assertions.assertThat(p.valueRowOffsetY).isEqualTo(2); Assertions.assertThat(p.columnLabelOffsetX).isEqualTo(2); Assertions.assertThat(p.decoRowOffsetX).isEqualTo(1); Assertions.assertThat(p.valuesStartAtRownumber).isEqualTo(3); }
@Test public void validateSourceData_works(){ List<String> annotations = Arrays.asList("row", "value", "value", "column"); List<Integer> orderNumbers = Arrays.asList(0, 0, 1, 1); List<String> typeList = Arrays.asList(null, "SUM", "SUM", null); List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn2a", "fn3"); List<?> v1 = Arrays.asList("r1", "a", 1, "c1"); sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1); expectedException.expect(IllegalArgumentException.class); expectedException.expectMessage("Values other than CELL_TYPE_NUMERIC found"); p.pivot(sourceSheet, targetSheet); }
@Test public void replaceEmptyRowAndColumns_when_validating_sourcedata_works(){ List<String> annotations = Arrays.asList("row", "value", "column"); List<Integer> orderNumbers = Arrays.asList(0, 0, 1); List<String> typeList = Arrays.asList(null, "SUM", null); List<String> fieldNameList = Arrays.asList("fn1", "fn2", "fn3"); List<?> v1 = Arrays.asList(null, 1, null); sourceSheetBuilder(annotations, orderNumbers, typeList, fieldNameList, v1); p.pivot(sourceSheet, targetSheet); Assertions.assertThat(sourceSheet.getRow(4).getCell(0).getStringCellValue()).isEqualTo("(empty)"); Assertions.assertThat(sourceSheet.getRow(4).getCell(2).getStringCellValue()).isEqualTo("(empty)"); }