Example usage for org.apache.poi.ss.usermodel Workbook getSheet

List of usage examples for org.apache.poi.ss.usermodel Workbook getSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getSheet

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook getSheet.

Prototype

Sheet getSheet(String name);

Source Link

Document

Get sheet with the given name

Usage

From source file:org.aludratest.app.excelwizard.JavaBeanExcelDocumentMapperTest.java

License:Apache License

@Test
public void testAdditionalExcelColumns() throws Exception {
    final String EXCEL_URI = "test_additional_columns.xls";
    copyToTarget(EXCEL_URI);//from ww  w . j a v a  2 s  .  com
    Method testMethod = BeanUtil.getMethod(TEST_CLASS, "additionalColumns", TEST_PARAMS);
    Collection<WorkbookTracker> trackers = JavaBeanExcelDocumentMapper.createOrMergeDocuments(testMethod, null,
            TARGET_CLASSES);
    verifyExcelDocument(trackers, EXCEL_URI, new String[] { "name1", "name2" }, new String[] { "sub.name" },
            new String[] { "Alice", "Bob", "x", "Charly", "y" });
    // verify warnings
    WorkbookTracker tracker = trackers.iterator().next();
    List<String> warnings = tracker.getWarnings();
    assertEquals(2, warnings.size());
    assertEquals("Unmappable column 'add' in sheet 'sheet1' of file 'test_additional_columns.xls'",
            warnings.get(0));
    assertEquals("Unmappable column 'sub.add' in sheet 'sheet1' of file 'test_additional_columns.xls'",
            warnings.get(1));
    Workbook workbook = tracker.getWorkbook();
    Sheet sheet1 = workbook.getSheet("sheet1");
    Row headerRow = sheet1.getRow(0);
    assertNotNull(headerRow);
    assertEquals(HSSFColor.RED.index, headerRow.getCell(2).getCellStyle().getFillForegroundColor());
    assertEquals(HSSFColor.RED.index, headerRow.getCell(4).getCellStyle().getFillForegroundColor());
}

From source file:org.aludratest.app.excelwizard.JavaBeanExcelDocumentMapperTest.java

License:Apache License

private static void verifyExcelDocument(Collection<WorkbookTracker> files, String expectedFileName,
        String[] expectedMainHeaders, String[] expectedSubHeaders, String[] expectedData)
        throws IOException, InvalidFormatException {
    assertEquals(1, files.size());/*from   www  . j ava  2 s .  co  m*/
    File file = files.iterator().next().getFile();
    assertEquals(expectedFileName, file.getName());
    Workbook workbook = WorkbookFactory.create(file);
    Sheet sheet1 = workbook.getSheet("sheet1");
    assertNotNull(sheet1);
    Row headerRow = sheet1.getRow(0);
    assertNotNull(headerRow);
    for (String expectedMainHeader : expectedMainHeaders) {
        int index = ExcelUtil.findCellWithText(expectedMainHeader, headerRow);
        assertTrue(index >= 0);
    }
    for (String expectedSubHeader : expectedSubHeaders) {
        int index = ExcelUtil.findCellWithText(expectedSubHeader, headerRow);
        assertTrue(index >= expectedMainHeaders.length);
    }
    if (expectedData != null) {
        Row dataRow1 = sheet1.getRow(1);
        assertNotNull(dataRow1);
        for (int i = 0; i < expectedData.length; i++) {
            Cell dataCell = dataRow1.getCell(i);
            String expectedContent = expectedData[i];
            if (expectedContent != null) {
                assertNotNull(dataCell);
                assertEquals(expectedContent, dataCell.getStringCellValue());
            } else {
                assertNull(dataCell);
            }
        }
    }
}

From source file:org.aludratest.testcase.data.impl.TestConfigInfoHelper.java

License:Apache License

/** Reads the test configuration information from the corresponding tab of the test data file.
 * @param method the method for which to determine the test infos
 * @param invocations the expected number of invocations
 * @return a List of the test infos for the method */
public List<TestDataLoadInfo> testInfos(Method method, int invocations) {
    // check if the Excel document is available
    String excelFilePath = testConfigurationFilePath(method);
    if (excelFilePath == null) {
        return defaultTestInfos(invocations, isIgnored(method));
    }/*from www . j  av a2 s . co m*/
    if (!IOUtil.isURIAvailable(excelFilePath)) {
        Exception e = new AutomationException("Data file not found: " + excelFilePath);
        return errorInfos(e, invocations);
    }

    // fetch the tab (sheet) with the config info
    try {
        Workbook workbook = WorkbookFactory.create(IOUtil.getInputStreamForURI(excelFilePath));
        Sheet sheet = workbook.getSheet(CONFIG_TAB_NAME);
        if (sheet == null) {
            if (aludraConfig.isConfigTabRequired()) {
                Exception e = new AutomationException(
                        "Sheet '" + CONFIG_TAB_NAME + "' not found in file " + excelFilePath);
                return errorInfos(e, invocations);
            } else {
                return defaultTestInfos(invocations, isIgnored(method));
            }
        }
        return parseTestInfos(sheet, invocations, method, excelFilePath);
    } catch (IOException e) {
        throw new AutomationException("Error reading Excel document " + excelFilePath, e);
    } catch (InvalidFormatException e) {
        throw new AutomationException("Error reading Excel document " + excelFilePath, e);
    }
}

From source file:org.apache.metamodel.excel.DefaultSpreadsheetReaderDelegate.java

License:Apache License

@Override
public DataSet executeQuery(Table table, Column[] columns, int maxRows) {
    final Workbook wb = ExcelUtils.readWorkbook(_resource);
    final Sheet sheet = wb.getSheet(table.getName());

    if (sheet == null || sheet.getPhysicalNumberOfRows() == 0) {
        return new EmptyDataSet(columns);
    }/*w  ww  . j  ava  2s . c o  m*/

    DataSet dataSet = ExcelUtils.getDataSet(wb, sheet, table, _configuration);

    if (maxRows > 0) {
        dataSet = new MaxRowsDataSet(dataSet, maxRows);
    }
    return dataSet;
}

From source file:org.apache.metamodel.excel.ExcelDeleteBuilder.java

License:Apache License

@Override
public void execute() throws MetaModelException {
    // close the update callback will flush any changes
    _updateCallback.close();//from  ww  w  .  j av a 2  s .  co  m

    // read the workbook without streaming, since this will not wrap it in a
    // streaming workbook implementation (which do not support random
    // accessing rows).
    final Workbook workbook = _updateCallback.getWorkbook(false);

    final String tableName = getTable().getName();
    final SelectItem[] selectItems = MetaModelHelper.createSelectItems(getTable().getColumns());
    final DataSetHeader header = new SimpleDataSetHeader(selectItems);
    final Sheet sheet = workbook.getSheet(tableName);

    final Iterator<Row> rowIterator = ExcelUtils.getRowIterator(sheet, _updateCallback.getConfiguration(),
            true);
    final List<Row> rowsToDelete = new ArrayList<Row>();
    while (rowIterator.hasNext()) {
        final Row excelRow = rowIterator.next();
        final DefaultRow row = ExcelUtils.createRow(workbook, excelRow, header);

        final boolean deleteRow = deleteRow(row);
        if (deleteRow) {
            rowsToDelete.add(excelRow);
        }
    }

    // reverse the list to not mess up any row numbers
    Collections.reverse(rowsToDelete);

    for (Row row : rowsToDelete) {
        sheet.removeRow(row);
    }
}

From source file:org.bbreak.excella.core.util.PoiUtil.java

License:Open Source License

/**
 * ?/*from w w w  .  ja  v a 2s .c  o m*/
 * 
 * @param fromSheet 
 * @param rangeAddress 
 * @param toSheet 
 * @param toRowNum 
 * @param toColumnNum 
 * @param clearFromRange 
 */
public static void copyRange(Sheet fromSheet, CellRangeAddress rangeAddress, Sheet toSheet, int toRowNum,
        int toColumnNum, boolean clearFromRange) {

    if (fromSheet == null || rangeAddress == null || toSheet == null) {
        return;
    }

    int fromRowIndex = rangeAddress.getFirstRow();
    int fromColumnIndex = rangeAddress.getFirstColumn();

    int rowNumOffset = toRowNum - fromRowIndex;
    int columnNumOffset = toColumnNum - fromColumnIndex;

    // 
    CellRangeAddress toAddress = new CellRangeAddress(rangeAddress.getFirstRow() + rowNumOffset,
            rangeAddress.getLastRow() + rowNumOffset, rangeAddress.getFirstColumn() + columnNumOffset,
            rangeAddress.getLastColumn() + columnNumOffset);

    Workbook fromWorkbook = fromSheet.getWorkbook();
    Sheet baseSheet = fromSheet;

    Sheet tmpSheet = null;
    // ?????
    if (fromSheet.equals(toSheet) && crossRangeAddress(rangeAddress, toAddress)) {
        // ?
        tmpSheet = fromWorkbook.getSheet(TMP_SHEET_NAME);
        if (tmpSheet == null) {
            tmpSheet = fromWorkbook.createSheet(TMP_SHEET_NAME);
        }
        baseSheet = tmpSheet;

        int lastColNum = getLastColNum(fromSheet);
        for (int i = 0; i <= lastColNum; i++) {
            tmpSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
        }

        copyRange(fromSheet, rangeAddress, tmpSheet, rangeAddress.getFirstRow(), rangeAddress.getFirstColumn(),
                false);

        // ?
        if (clearFromRange) {
            clearRange(fromSheet, rangeAddress);
        }
    }

    // ????
    Set<CellRangeAddress> targetCellSet = getMergedAddress(baseSheet, rangeAddress);
    // ???
    clearRange(toSheet, toAddress);

    // ???
    for (CellRangeAddress mergeAddress : targetCellSet) {

        toSheet.addMergedRegion(new CellRangeAddress(mergeAddress.getFirstRow() + rowNumOffset,
                mergeAddress.getLastRow() + rowNumOffset, mergeAddress.getFirstColumn() + columnNumOffset,
                mergeAddress.getLastColumn() + columnNumOffset));

    }

    for (int i = rangeAddress.getFirstRow(); i <= rangeAddress.getLastRow(); i++) {
        // 
        Row fromRow = baseSheet.getRow(i);
        if (fromRow == null) {
            continue;
        }
        Row row = toSheet.getRow(i + rowNumOffset);
        if (row == null) {
            row = toSheet.createRow(i + rowNumOffset);
            row.setHeight((short) 0);
        }

        // ??????
        int fromRowHeight = fromRow.getHeight();
        int toRowHeight = row.getHeight();
        if (toRowHeight < fromRowHeight) {
            row.setHeight(fromRow.getHeight());
        }

        ColumnHelper columnHelper = null;
        if (toSheet instanceof XSSFSheet) {
            XSSFSheet xssfSheet = (XSSFSheet) toSheet.getWorkbook()
                    .getSheetAt(toSheet.getWorkbook().getSheetIndex(toSheet));
            CTWorksheet ctWorksheet = xssfSheet.getCTWorksheet();
            columnHelper = new ColumnHelper(ctWorksheet);
        }

        for (int j = rangeAddress.getFirstColumn(); j <= rangeAddress.getLastColumn(); j++) {
            Cell fromCell = fromRow.getCell(j);
            if (fromCell == null) {
                continue;
            }
            int maxColumn = SpreadsheetVersion.EXCEL97.getMaxColumns();
            if (toSheet instanceof XSSFSheet) {
                maxColumn = SpreadsheetVersion.EXCEL2007.getMaxColumns();
            }
            if (j + columnNumOffset >= maxColumn) {
                break;
            }
            Cell cell = row.getCell(j + columnNumOffset);
            if (cell == null) {
                cell = row.createCell(j + columnNumOffset);
                if (toSheet instanceof XSSFSheet) {
                    // XSSF??????????
                    CTCol col = columnHelper.getColumn(cell.getColumnIndex(), false);
                    if (col == null || !col.isSetWidth()) {
                        toSheet.setColumnWidth(cell.getColumnIndex(), baseSheet.getColumnWidth(j));
                    }
                }
            }

            // ?
            copyCell(fromCell, cell);

            // ??????
            int fromColumnWidth = baseSheet.getColumnWidth(j);
            int toColumnWidth = toSheet.getColumnWidth(j + columnNumOffset);

            if (toColumnWidth < fromColumnWidth) {
                toSheet.setColumnWidth(j + columnNumOffset, baseSheet.getColumnWidth(j));
            }
        }
    }

    if (tmpSheet != null) {
        // 
        fromWorkbook.removeSheetAt(fromWorkbook.getSheetIndex(tmpSheet));
    } else if (clearFromRange) {
        // ????
        clearRange(fromSheet, rangeAddress);
    }

}

From source file:org.bbreak.excella.reports.listener.BreakAdapterTest.java

License:Open Source License

private void checkSheet(String expectedSheetName, Sheet actualSheet, boolean outputExcel) {

    // ???/*from w w  w . ja va  2 s. c om*/
    Workbook expectedWorkbook = getExpectedWorkbook();
    Sheet expectedSheet = expectedWorkbook.getSheet(expectedSheetName);

    try {
        // ?
        ReportsTestUtil.checkSheet(expectedSheet, actualSheet, false);
    } catch (ReportsCheckException e) {
        fail(e.getCheckMessagesToString());
    } finally {
        String tmpDirPath = ReportsTestUtil.getTestOutputDir();
        try {
            String filepath = null;
            Date now = new Date();
            if (version.equals("2007")) {
                filepath = tmpDirPath + this.getClass().getSimpleName() + now.getTime() + ".xlsx";
            } else {
                filepath = tmpDirPath + this.getClass().getSimpleName() + now.getTime() + ".xls";
            }
            PoiUtil.writeBook(actualSheet.getWorkbook(), filepath);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

From source file:org.bbreak.excella.reports.tag.BlockColRepeatParamParserTest.java

License:Open Source License

private void checkSheet(String expectedSheetName, Sheet actualSheet, boolean outputExcel) {

    // ???// ww w  .ja v a2 s .  c o  m
    Workbook expectedWorkbook = getExpectedWorkbook();
    Sheet expectedSheet = expectedWorkbook.getSheet(expectedSheetName);

    expectedSheet.getPrintSetup().getCopies();

    try {
        // ?
        ReportsTestUtil.checkSheet(expectedSheet, actualSheet, false);
    } catch (ReportsCheckException e) {
        fail(e.getCheckMessagesToString());
    } finally {
        String tmpDirPath = ReportsTestUtil.getTestOutputDir();
        try {
            String filepath = null;
            Date now = new Date();
            if (version.equals("2007")) {
                filepath = tmpDirPath + this.getClass().getSimpleName() + now.getTime() + ".xlsx";
            } else {
                filepath = tmpDirPath + this.getClass().getSimpleName() + now.getTime() + ".xls";
            }
            PoiUtil.writeBook(actualSheet.getWorkbook(), filepath);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

From source file:org.bbreak.excella.reports.tag.ImageParamParserTest.java

License:Open Source License

/**
 * {@link org.bbreak.excella.reports.tag.ImageParamParser#parse(org.apache.poi.ss.usermodel.Sheet, org.apache.poi.ss.usermodel.Cell, java.lang.Object)} ????
 *//*from   w  ww.j  a va  2  s  . co  m*/
@Test
public void testParseSheetCellObject() {
    // -----------------------
    // []?
    // -----------------------
    Workbook workbook = getWorkbook();

    Sheet sheet1 = workbook.getSheetAt(0);

    ImageParamParser parser = new ImageParamParser();

    ReportsParserInfo reportsParserInfo = new ReportsParserInfo();
    reportsParserInfo.setParamInfo(createTestData(ImageParamParser.DEFAULT_TAG));

    try {
        parseSheet(parser, sheet1, reportsParserInfo);
    } catch (ParseException e) {
        fail(e.toString());
    }

    // ???
    Set<Integer> delSheetIndexs = new TreeSet<Integer>(Collections.reverseOrder());
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        if (i != 0) {
            delSheetIndexs.add(i);
        }
    }
    for (Integer i : delSheetIndexs) {
        workbook.removeSheetAt(i);
    }

    // ???
    Workbook expectedWorkbook = getExpectedWorkbook();
    Sheet expectedSheet = expectedWorkbook.getSheet("Sheet1");

    try {
        // ?
        ReportsTestUtil.checkSheet(expectedSheet, sheet1, false);
    } catch (ReportsCheckException e) {
        fail(e.getCheckMessagesToString());

    } finally {
        // ?????????????
        String tmpDirPath = System.getProperty("user.dir") + "/work/test/"; // System.getProperty( "java.io.tmpdir");
        File file = new File(tmpDirPath);
        if (!file.exists()) {
            file.mkdirs();
        }

        try {
            String filepath = null;
            if (version.equals("2007")) {
                filepath = tmpDirPath + "ImageParamParserTest1.xlsx";
            } else {
                filepath = tmpDirPath + "ImageParamParserTest1.xls";
            }
            PoiUtil.writeBook(workbook, filepath);
            log.info("????????" + filepath);

        } catch (IOException e) {
            fail(e.toString());
        }
    }

    // -----------------------
    // []
    // -----------------------
    workbook = getWorkbook();

    Sheet sheet2 = workbook.getSheetAt(1);

    parser = new ImageParamParser("$Image");

    reportsParserInfo = new ReportsParserInfo();
    reportsParserInfo.setParamInfo(createTestData("$Image"));

    try {
        parseSheet(parser, sheet2, reportsParserInfo);
    } catch (ParseException e) {
        fail(e.toString());
    }

    // ???
    delSheetIndexs.clear();
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        if (i != 1) {
            delSheetIndexs.add(i);
        }
    }
    for (Integer i : delSheetIndexs) {
        workbook.removeSheetAt(i);
    }

    // ???
    expectedWorkbook = getExpectedWorkbook();
    expectedSheet = expectedWorkbook.getSheet("Sheet2");

    try {
        // ?
        ReportsTestUtil.checkSheet(expectedSheet, sheet2, false);
    } catch (ReportsCheckException e) {
        fail(e.getCheckMessagesToString());
    } finally {
        // ?????????????
        String tmpDirPath = System.getProperty("user.dir") + "/work/test/"; // System.getProperty( "java.io.tmpdir");;
        File file = new File(tmpDirPath);
        if (!file.exists()) {
            file.mkdir();
        }
        try {
            String filepath = null;
            if (version.equals("2007")) {
                filepath = tmpDirPath + "ImageParamParserTest2.xlsx";
            } else {
                filepath = tmpDirPath + "ImageParamParserTest2.xls";
            }
            PoiUtil.writeBook(workbook, filepath);
            log.info("????????" + filepath);

        } catch (IOException e) {
            fail(e.toString());
        }
    }

    String filename = this.getClass().getSimpleName();
    if (version.equals("2007")) {
        filename = filename + "_err.xlsx";
    } else if (version.equals("2003")) {
        filename = filename + "_err.xls";
    }

    URL url = this.getClass().getResource(filename);
    String path = null;
    try {
        path = URLDecoder.decode(url.getFile(), "UTF-8");

    } catch (UnsupportedEncodingException e) {
        Assert.fail();
    }

    // -----------------------
    // []?
    // -----------------------
    workbook = getWorkbook(path);

    Sheet sheet3 = workbook.getSheetAt(2);

    parser = new ImageParamParser();

    reportsParserInfo = new ReportsParserInfo();
    reportsParserInfo.setParamInfo(createTestData(ImageParamParser.DEFAULT_TAG));

    try {
        parseSheet(parser, sheet3, reportsParserInfo);
        fail("?????????");
    } catch (ParseException e) {
    }

    // -----------------------
    // []????
    // -----------------------
    workbook = getWorkbook();

    Sheet sheet4 = workbook.getSheetAt(2);

    parser = new ImageParamParser();

    reportsParserInfo = new ReportsParserInfo();
    reportsParserInfo.setParamInfo(createTestData(ImageParamParser.DEFAULT_TAG));

    try {
        parseSheet(parser, sheet4, reportsParserInfo);
    } catch (ParseException e) {
        fail(e.toString());
    }

    // ???
    expectedWorkbook = getExpectedWorkbook();
    expectedSheet = expectedWorkbook.getSheet("Sheet4");

    try {
        // ?
        ReportsTestUtil.checkSheet(expectedSheet, sheet4, false);
    } catch (ReportsCheckException e) {
        fail(e.getCheckMessagesToString());
    }

    // ----------------------------------------------------------------------
    // []1 / ?? / ???
    // ----------------------------------------------------------------------
    workbook = getWorkbook();

    Sheet sheet5 = workbook.getSheetAt(INDEX_OF_SHEET5);

    parser = new ImageParamParser();

    reportsParserInfo = new ReportsParserInfo();
    reportsParserInfo.setParamInfo(createPluralTestData(ImageParamParser.DEFAULT_TAG));

    try {
        parseSheet(parser, sheet5, reportsParserInfo);
    } catch (ParseException e) {
        fail(e.toString());
    }

    // ???
    delSheetIndexs.clear();
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        if (i != INDEX_OF_SHEET5) {
            delSheetIndexs.add(i);
        }
    }
    for (Integer i : delSheetIndexs) {
        workbook.removeSheetAt(i);
    }

    // ???
    expectedWorkbook = getExpectedWorkbook();
    expectedSheet = expectedWorkbook.getSheet("Sheet5");

    try {
        // ?
        ReportsTestUtil.checkSheet(expectedSheet, sheet5, false);
    } catch (ReportsCheckException e) {
        fail(e.getCheckMessagesToString());
    } finally {
        // ?????????????
        String tmpDirPath = System.getProperty("user.dir") + "/work/test/"; // System.getProperty( "java.io.tmpdir");
        File file = new File(tmpDirPath);
        if (!file.exists()) {
            file.mkdirs();
        }

        try {
            String filepath = null;
            if (version.equals("2007")) {
                filepath = tmpDirPath + "ImageParamParserTest3.xlsx";
            } else {
                filepath = tmpDirPath + "ImageParamParserTest3.xls";
            }
            PoiUtil.writeBook(workbook, filepath);
            log.info("????????" + filepath);

        } catch (IOException e) {
            fail(e.toString());
        }
    }

    // ----------------------------------------------------------------------
    // []1 / ?? / ?1
    // ----------------------------------------------------------------------

    workbook = getWorkbook();

    Sheet sheet6 = workbook.cloneSheet(INDEX_OF_SHEET5);

    parser = new ImageParamParser();

    reportsParserInfo = new ReportsParserInfo();
    reportsParserInfo.setParamInfo(createPluralTestData(ImageParamParser.DEFAULT_TAG));

    try {
        parseSheet(parser, sheet6, reportsParserInfo);
    } catch (ParseException e) {
        fail(e.toString());
    }

    // ???
    delSheetIndexs.clear();
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        if (i != workbook.getNumberOfSheets() - 1) {
            delSheetIndexs.add(i);
        }
    }
    for (Integer i : delSheetIndexs) {
        workbook.removeSheetAt(i);
    }

    // ???
    expectedWorkbook = getExpectedWorkbook();
    expectedSheet = expectedWorkbook.cloneSheet(INDEX_OF_SHEET5);

    try {
        // ?
        ReportsTestUtil.checkSheet(expectedSheet, sheet6, false);
    } catch (ReportsCheckException e) {
        fail(e.getCheckMessagesToString());
    } finally {
        // ?????????????
        String tmpDirPath = System.getProperty("user.dir") + "/work/test/"; // System.getProperty( "java.io.tmpdir");
        File file = new File(tmpDirPath);
        if (!file.exists()) {
            file.mkdirs();
        }

        try {
            String filepath = null;
            if (version.equals("2007")) {
                filepath = tmpDirPath + "ImageParamParserTest4.xlsx";
            } else {
                filepath = tmpDirPath + "ImageParamParserTest4.xls";
            }
            PoiUtil.writeBook(workbook, filepath);
            log.info("????????" + filepath);

        } catch (IOException e) {
            fail(e.toString());
        }
    }

    // ----------------------------------------------------------------------
    // []  / ?? / ?
    // ----------------------------------------------------------------------

    workbook = getWorkbook();

    for (int i = 1; i <= PLURAL_COPY_FIRST_NUM_OF_SHEETS; i++) {

        Sheet sheet = workbook.cloneSheet(INDEX_OF_SHEET5);

        parser = new ImageParamParser();

        reportsParserInfo = new ReportsParserInfo();
        reportsParserInfo.setParamInfo(createPluralTestData(ImageParamParser.DEFAULT_TAG));

        try {
            parseSheet(parser, sheet, reportsParserInfo);
        } catch (ParseException e) {
            fail(e.toString());
        }
    }

    // ???
    delSheetIndexs.clear();
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        if (i < workbook.getNumberOfSheets() - PLURAL_COPY_FIRST_NUM_OF_SHEETS) {
            delSheetIndexs.add(i);
        }
    }
    for (Integer i : delSheetIndexs) {
        workbook.removeSheetAt(i);
    }

    // ???
    expectedWorkbook = getExpectedWorkbook();
    for (int i = 1; i <= PLURAL_COPY_FIRST_NUM_OF_SHEETS; i++) {
        expectedSheet = expectedWorkbook.cloneSheet(INDEX_OF_SHEET5);
    }

    try {
        int startOfTargetSheet = expectedWorkbook.getNumberOfSheets() - PLURAL_COPY_FIRST_NUM_OF_SHEETS;

        for (int i = 0; i < PLURAL_COPY_FIRST_NUM_OF_SHEETS; i++) {
            // ?
            ReportsTestUtil.checkSheet(expectedWorkbook.getSheetAt(startOfTargetSheet + i),
                    workbook.getSheetAt(i), false);
        }
    } catch (ReportsCheckException e) {
        fail(e.getCheckMessagesToString());
    } finally {
        // ?????????????
        String tmpDirPath = System.getProperty("user.dir") + "/work/test/"; // System.getProperty( "java.io.tmpdir");
        File file = new File(tmpDirPath);
        if (!file.exists()) {
            file.mkdirs();
        }

        try {
            String filepath = null;
            if (version.equals("2007")) {
                filepath = tmpDirPath + "ImageParamParserTest5.xlsx";
            } else {
                filepath = tmpDirPath + "ImageParamParserTest5.xls";
            }
            PoiUtil.writeBook(workbook, filepath);
            log.info("????????" + filepath);

        } catch (IOException e) {
            fail(e.toString());
        }

    }

    // ----------------------------------------------------------------------
    // []  / ?? / (2)?
    // ----------------------------------------------------------------------

    workbook = getWorkbook();

    Sheet sheet = null;
    int totalNumOfCopies = PLURAL_COPY_FIRST_NUM_OF_SHEETS + PLURAL_COPY_SECOND_NUM_OF_SHEETS;
    for (int i = 1; i <= totalNumOfCopies; i++) {

        if (i <= PLURAL_COPY_FIRST_NUM_OF_SHEETS) {
            sheet = workbook.cloneSheet(INDEX_OF_SHEET5);
        } else {
            sheet = workbook.cloneSheet(INDEX_OF_SHEET6);
        }

        parser = new ImageParamParser();

        reportsParserInfo = new ReportsParserInfo();
        reportsParserInfo.setParamInfo(createPluralTestData(ImageParamParser.DEFAULT_TAG));

        try {
            parseSheet(parser, sheet, reportsParserInfo);
        } catch (ParseException e) {
            fail(e.toString());
        }
    }

    // ???
    delSheetIndexs.clear();
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        if (i < workbook.getNumberOfSheets() - totalNumOfCopies) {
            delSheetIndexs.add(i);
        }
    }
    for (Integer i : delSheetIndexs) {
        workbook.removeSheetAt(i);
    }

    // ???
    expectedWorkbook = getExpectedWorkbook();
    for (int i = 1; i <= totalNumOfCopies; i++) {
        if (i <= PLURAL_COPY_FIRST_NUM_OF_SHEETS) {
            expectedSheet = expectedWorkbook.cloneSheet(INDEX_OF_SHEET5);
        } else {
            expectedSheet = expectedWorkbook.cloneSheet(INDEX_OF_SHEET6);
        }
    }

    try {
        int startOfTargetSheet = expectedWorkbook.getNumberOfSheets() - totalNumOfCopies;

        for (int i = 0; i < totalNumOfCopies; i++) {
            // ?
            ReportsTestUtil.checkSheet(expectedWorkbook.getSheetAt(startOfTargetSheet + i),
                    workbook.getSheetAt(i), false);
        }
    } catch (ReportsCheckException e) {
        fail(e.getCheckMessagesToString());
    } finally {
        // ?????????????
        String tmpDirPath = System.getProperty("user.dir") + "/work/test/"; // System.getProperty( "java.io.tmpdir");
        File file = new File(tmpDirPath);
        if (!file.exists()) {
            file.mkdirs();
        }

        try {
            String filepath = null;
            if (version.equals("2007")) {
                filepath = tmpDirPath + "ImageParamParserTest6.xlsx";
            } else {
                filepath = tmpDirPath + "ImageParamParserTest6.xls";
            }
            PoiUtil.writeBook(workbook, filepath);
            log.info("????????" + filepath);

        } catch (IOException e) {
            fail(e.toString());
        }
    }

}

From source file:org.bbreak.excella.reports.tag.SingleParamParserTest.java

License:Open Source License

/**
 * {@link org.bbreak.excella.reports.tag.SingleParamParser#parse(org.apache.poi.ss.usermodel.Sheet, org.apache.poi.ss.usermodel.Cell, java.lang.Object)} ????
 *///www  .jav a2 s .com
@Test
public void testParseSheetCellObject() {

    // -----------------------
    // []?
    // -----------------------

    Workbook workbook = getWorkbook();

    Sheet sheet1 = workbook.getSheetAt(0);

    // 
    ParamInfo info = new ParamInfo();
    // 
    info.addParam(SingleParamParser.DEFAULT_TAG, "test", "test!");
    // 2?
    info.addParam(SingleParamParser.DEFAULT_TAG, "", "??!");
    // byte
    info.addParam(SingleParamParser.DEFAULT_TAG, "test01", (byte) 1);
    // Byte
    info.addParam(SingleParamParser.DEFAULT_TAG, "test02", new Byte("1"));
    // short
    info.addParam(SingleParamParser.DEFAULT_TAG, "test03", (short) 2);
    // Short
    info.addParam(SingleParamParser.DEFAULT_TAG, "test04", new Short("2"));
    // int
    info.addParam(SingleParamParser.DEFAULT_TAG, "test05", 3);
    // Integer
    info.addParam(SingleParamParser.DEFAULT_TAG, "test06", new Integer("3"));
    // long
    info.addParam(SingleParamParser.DEFAULT_TAG, "test07", 4L);
    // Long
    info.addParam(SingleParamParser.DEFAULT_TAG, "test08", new Long("4"));
    // float
    info.addParam(SingleParamParser.DEFAULT_TAG, "test09", 5.1f);
    // Float
    info.addParam(SingleParamParser.DEFAULT_TAG, "test10", new Float("5.1"));
    // double
    info.addParam(SingleParamParser.DEFAULT_TAG, "test11", 6.1);
    // Double
    info.addParam(SingleParamParser.DEFAULT_TAG, "test12", new Double(6.1));
    // BigInteger
    info.addParam(SingleParamParser.DEFAULT_TAG, "test13", new BigInteger("7"));
    // BigDecimal
    info.addParam(SingleParamParser.DEFAULT_TAG, "test14", new BigDecimal("8.5"));
    // Date
    Calendar cal = Calendar.getInstance();
    cal.clear();
    cal.set(2009, 4, 22);
    info.addParam(SingleParamParser.DEFAULT_TAG, "test15", cal.getTime());

    SingleParamParser parser = new SingleParamParser();

    ReportsParserInfo reportsParserInfo = new ReportsParserInfo();

    reportsParserInfo.setParamInfo(info);

    // ??
    try {
        parseSheet(parser, sheet1, reportsParserInfo);
    } catch (ParseException e) {
        fail(e.toString());
    }

    // ???
    Workbook expectedWorkbook = getExpectedWorkbook();
    Sheet expectedSheet = expectedWorkbook.getSheet("Sheet1");

    try {
        // ?
        ReportsTestUtil.checkSheet(expectedSheet, sheet1, false);
    } catch (ReportsCheckException e) {
        fail(e.getCheckMessagesToString());
    }

    // -----------------------
    // []???
    // -----------------------

    workbook = getWorkbook();

    // 
    ParamInfo infoP = new ParamInfo();
    infoP.addParam("$P", "test", "test!");
    infoP.addParam("$P", "", "??!");
    infoP.addParam("$P", "test01", (byte) 1);
    infoP.addParam("$P", "test02", new Byte("1"));
    infoP.addParam("$P", "test03", (short) 2);
    infoP.addParam("$P", "test04", new Short("2"));
    infoP.addParam("$P", "test05", 3);
    infoP.addParam("$P", "test06", new Integer("3"));
    infoP.addParam("$P", "test07", 4L);
    infoP.addParam("$P", "test08", new Long("4"));
    infoP.addParam("$P", "test09", 5.1f);
    infoP.addParam("$P", "test10", new Float("5.1"));
    infoP.addParam("$P", "test11", 6.1);
    infoP.addParam("$P", "test12", new Double(6.1));
    infoP.addParam("$P", "test13", new BigInteger("7"));
    infoP.addParam("$P", "test14", new BigDecimal("8.5"));
    cal.clear();
    cal.set(2009, 4, 22);
    infoP.addParam("$P", "test15", cal.getTime());

    // ???
    parser = new SingleParamParser("$P");

    reportsParserInfo = new ReportsParserInfo();
    reportsParserInfo.setParamInfo(infoP);

    Sheet sheet2 = workbook.getSheetAt(1);

    // ??
    try {
        parseSheet(parser, sheet2, reportsParserInfo);
    } catch (ParseException e) {
        fail(e.toString());
    }

    // ???
    expectedWorkbook = getExpectedWorkbook();
    expectedSheet = expectedWorkbook.getSheet("Sheet2");

    try {
        // ?
        ReportsTestUtil.checkSheet(expectedSheet, sheet2, false);
    } catch (ReportsCheckException e) {
        fail(e.getCheckMessagesToString());
    }

    // ???
    parser = new SingleParamParser();
    parser.setTag("$P");

    sheet2 = workbook.getSheetAt(1);

    // ??
    try {
        parseSheet(parser, sheet2/** sheet1 */
                , reportsParserInfo);
    } catch (ParseException e) {
        fail(e.toString());
    }

    // ???
    expectedWorkbook = getExpectedWorkbook();
    expectedSheet = expectedWorkbook.getSheet("Sheet2");

    try {
        // ?
        ReportsTestUtil.checkSheet(expectedSheet, sheet2, false);
    } catch (ReportsCheckException e) {
        fail(e.getCheckMessagesToString());
    }

    // -----------------------
    // []????
    // -----------------------
    parser = new SingleParamParser();

    Sheet sheet3 = workbook.getSheetAt(2);

    // ??
    try {
        parseSheet(parser, sheet3, reportsParserInfo);
    } catch (ParseException e) {

    }
    // ???
    expectedWorkbook = getExpectedWorkbook();
    expectedSheet = expectedWorkbook.getSheet("Sheet3");

    try {
        // ?
        ReportsTestUtil.checkSheet(expectedSheet, sheet3, false);
    } catch (ReportsCheckException e) {
        fail(e.getCheckMessagesToString());
    }
}