Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. getSheetAt

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt.

Prototype

@Override
public XSSFSheet getSheetAt(int index)

Source Link

Document

Get the XSSFSheet object at the given index.

Usage

From source file:uk.ac.bbsrc.tgac.miso.spring.util.FormUtils.java

License:Open Source License

public static JSONObject preProcessLibraryPoolSheetImport(File inPath, User u, SampleService sampleService)
        throws Exception {
    if (inPath.getName().endsWith(".xlsx")) {
        JSONObject jsonObject = new JSONObject();
        JSONArray sampleArray = new JSONArray();
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inPath));
        XSSFSheet sheet = wb.getSheetAt(0);

        XSSFRow glrow = sheet.getRow(1);

        // process global headers
        XSSFCell pairedCell = glrow.getCell(0);
        jsonObject.put("paired", getCellValueAsString(pairedCell));

        XSSFCell platformCell = glrow.getCell(1);
        if (getCellValueAsString(platformCell) != null) {
            jsonObject.put("platform", getCellValueAsString(platformCell));
        } else {/*from w  w  w.  j a  va2 s .c  o m*/
            throw new InputFormException(
                    "Cannot resolve Platform type from: '" + getCellValueAsString(platformCell) + "'");
        }

        XSSFCell typeCell = glrow.getCell(2);
        if (getCellValueAsString(typeCell) != null) {
            String[] split = getCellValueAsString(typeCell).split("-");
            String plat = split[0];
            String type = split[1];
            if (getCellValueAsString(platformCell).equals(plat)) {
                jsonObject.put("type", type);
            } else {
                throw new InputFormException("Selected library type '" + getCellValueAsString(typeCell)
                        + "' doesn't match platform type: '" + getCellValueAsString(platformCell) + "'");
            }
        } else {
            throw new InputFormException(
                    "Cannot resolve Library type from: '" + getCellValueAsString(typeCell) + "'");
        }

        XSSFCell selectionCell = glrow.getCell(3);
        if (getCellValueAsString(selectionCell) != null) {
            jsonObject.put("selection", getCellValueAsString(selectionCell));
        } else {
            throw new InputFormException("Cannot resolve Library Selection type from: '"
                    + getCellValueAsString(selectionCell) + "'");
        }

        XSSFCell strategyCell = glrow.getCell(4);
        if (getCellValueAsString(strategyCell) != null) {
            jsonObject.put("strategy", getCellValueAsString(strategyCell));
        } else {
            throw new InputFormException(
                    "Cannot resolve Library Strategy type from: '" + getCellValueAsString(strategyCell) + "'");
        }

        int rows = sheet.getPhysicalNumberOfRows();
        for (int ri = 6; ri < rows; ri++) {
            JSONArray rowsJSONArray = new JSONArray();
            XSSFRow row = sheet.getRow(ri);
            XSSFCell sampleNameCell = row.getCell(2);
            XSSFCell sampleAliasCell = row.getCell(3);
            Sample s = null;
            if (getCellValueAsString(sampleAliasCell) != null) {
                String salias = getCellValueAsString(sampleAliasCell);
                Collection<Sample> ss = sampleService.getByAlias(salias);
                if (!ss.isEmpty()) {
                    if (ss.size() == 1) {
                        s = ss.iterator().next();
                        log.info("Got sample: " + s.getAlias());
                    } else {
                        throw new InputFormException("Multiple samples retrieved with this alias: '" + salias
                                + "'. Cannot process.");
                    }
                } else {
                    throw new InputFormException("No such sample '" + salias
                            + "'in database. Samples need to be created before using the form input functionality");
                }
            } else {
                log.info("Blank sample row found. Ending import.");
                break;
            }

            // sample OK - good to go
            if (s != null) {
                XSSFCell indexFamilyCell = row.getCell(9);
                XSSFCell indicesCell = row.getCell(10);
                XSSFCell libraryQubitCell = row.getCell(6);
                XSSFCell libraryQcInsertSizeCell = row.getCell(7);
                XSSFCell libraryQcMolarityCell = row.getCell(8);
                XSSFCell qcPassedCell = row.getCell(11);
                XSSFCell libraryDescriptionCell = row.getCell(12);
                XSSFCell wellCell = row.getCell(4);
                XSSFCell dilutionMolarityCell = row.getCell(16);
                XSSFCell poolNameCell = row.getCell(21);
                XSSFCell poolConvertedMolarityCell = row.getCell(20);

                rowsJSONArray.add(getCellValueAsString(sampleNameCell));
                rowsJSONArray.add(getCellValueAsString(sampleAliasCell));
                rowsJSONArray.add(getCellValueAsString(wellCell).replaceAll("\\s", ""));

                XSSFCell proceedKeyCell = row.getCell(22);

                String proceedKey = "A";

                if (getCellValueAsString(proceedKeyCell) != null) {
                    String proceedKeyString = getCellValueAsString(proceedKeyCell).toUpperCase()
                            .replaceAll("\\s", "");
                    if ("L".equals(proceedKeyString)) {
                        proceedKey = "L";
                    } else if ("U".equals(proceedKeyString)) {
                        proceedKey = "U";
                    } else if ("P".equals(proceedKeyString)) {
                        proceedKey = "P";
                    }
                }

                String libAlias = "";
                Matcher mat = samplePattern.matcher(getCellValueAsString(sampleAliasCell));
                if (mat.matches()) {
                    String platePos = getCellValueAsString(wellCell);
                    libAlias = mat.group(1) + "_" + "L" + mat.group(2) + "-" + platePos.toUpperCase() + "_"
                            + mat.group(3);
                }
                rowsJSONArray.add(libAlias);

                if ("A".equals(proceedKey) || "L".equals(proceedKey) || "U".equals(proceedKey)) {
                    String libDesc = s.getDescription();
                    if (!isStringEmptyOrNull(getCellValueAsString(libraryDescriptionCell))) {
                        libDesc = getCellValueAsString(libraryDescriptionCell);
                    }
                    rowsJSONArray.add(libDesc);
                } else {
                    rowsJSONArray.add("");
                }

                if (getCellValueAsString(libraryQubitCell) != null
                        && ("A".equals(proceedKey) || "L".equals(proceedKey) || "U".equals(proceedKey))) {
                    rowsJSONArray.add(getCellValueAsString(libraryQubitCell));
                } else {
                    rowsJSONArray.add("");
                }

                if (getCellValueAsString(libraryQcInsertSizeCell) != null
                        && ("A".equals(proceedKey) || "L".equals(proceedKey) || "U".equals(proceedKey))) {
                    rowsJSONArray.add(getCellValueAsString(libraryQcInsertSizeCell));
                } else {
                    rowsJSONArray.add("");
                }

                if (getCellValueAsString(libraryQcMolarityCell) != null
                        && ("A".equals(proceedKey) || "L".equals(proceedKey) || "U".equals(proceedKey))) {
                    rowsJSONArray.add(getCellValueAsString(libraryQcMolarityCell));
                } else {
                    rowsJSONArray.add("");
                }

                if (getCellValueAsString(qcPassedCell) != null
                        && ("A".equals(proceedKey) || "L".equals(proceedKey) || "U".equals(proceedKey))) {
                    if ("Y".equals(getCellValueAsString(qcPassedCell))
                            || "y".equals(getCellValueAsString(qcPassedCell))) {
                        rowsJSONArray.add("true");
                    } else if ("N".equals(getCellValueAsString(qcPassedCell))
                            || "n".equals(getCellValueAsString(qcPassedCell))) {
                        rowsJSONArray.add("false");
                    }
                } else {
                    rowsJSONArray.add("");
                }

                if (getCellValueAsString(indexFamilyCell) != null
                        && ("A".equals(proceedKey) || "L".equals(proceedKey) || "U".equals(proceedKey))) {
                    rowsJSONArray.add(getCellValueAsString(indexFamilyCell));
                } else {
                    rowsJSONArray.add("");
                }

                if (getCellValueAsString(indicesCell) != null
                        && ("A".equals(proceedKey) || "L".equals(proceedKey) || "U".equals(proceedKey))) {
                    rowsJSONArray.add(getCellValueAsString(indicesCell));
                } else {
                    rowsJSONArray.add("");
                }

                if (getCellValueAsString(dilutionMolarityCell) != null
                        && ("A".equals(proceedKey) || "P".equals(proceedKey))) {
                    rowsJSONArray.add(getCellValueAsString(dilutionMolarityCell));
                } else {
                    rowsJSONArray.add("");
                }

                if (getCellValueAsString(poolNameCell) != null
                        && ("A".equals(proceedKey) || "P".equals(proceedKey))) {
                    rowsJSONArray.add(getCellValueAsString(poolNameCell));
                } else {
                    rowsJSONArray.add("");
                }

                if (getCellValueAsString(poolConvertedMolarityCell) != null
                        && ("A".equals(proceedKey) || "P".equals(proceedKey))) {
                    rowsJSONArray.add(getCellValueAsString(poolConvertedMolarityCell));
                } else {
                    rowsJSONArray.add("");
                }

                rowsJSONArray.add(proceedKey);
                if ("A".equals(proceedKey)) {
                    rowsJSONArray.add("A: Import everything");
                } else if ("L".equals(proceedKey)) {
                    rowsJSONArray.add("L: Import and create library only");
                } else if ("U".equals(proceedKey)) {
                    rowsJSONArray.add("U: Updated the library info only");
                } else if ("P".equals(proceedKey)) {
                    rowsJSONArray.add("P: import the library dilution and pool based on the library info");
                }
            }
            sampleArray.add(rowsJSONArray);
        }
        jsonObject.put("rows", sampleArray);
        return jsonObject;
    } else {
        throw new UnsupportedOperationException(
                "Cannot process bulk input files other than xls, xlsx, and ods.");
    }
}

From source file:uk.ac.bbsrc.tgac.miso.spring.util.FormUtils.java

License:Open Source License

private static List<Sample> processSampleInputXLSX(XSSFWorkbook wb, User u, SampleService sampleService,
        LibraryService libraryService, QualityControlService qcService, NamingScheme namingScheme,
        IndexService indexService) throws Exception {
    List<Sample> samples = new ArrayList<>();
    XSSFSheet sheet = wb.getSheetAt(0);
    int rows = sheet.getPhysicalNumberOfRows();

    XSSFRow glrow = sheet.getRow(1);//from   w ww  .ja  va 2 s  .  c o  m

    // process global headers
    XSSFCell pairedCell = glrow.getCell(0);
    boolean paired;
    if (getCellValueAsString(pairedCell) != null) {
        paired = pairedCell.getBooleanCellValue();
        log.info("Got paired: " + paired);
    } else {
        throw new InputFormException("'Paired' cell is empty. Please specify TRUE or FALSE.");
    }

    XSSFCell platformCell = glrow.getCell(1);
    PlatformType pt = null;
    if (getCellValueAsString(platformCell) != null) {
        pt = PlatformType.get(getCellValueAsString(platformCell));
    }
    if (pt == null) {
        throw new InputFormException(
                "Cannot resolve Platform type from: '" + getCellValueAsString(platformCell) + "'");
    } else {
        log.info("Got platform type: " + pt.getKey());
    }

    XSSFCell typeCell = glrow.getCell(2);
    LibraryType lt = null;
    if (getCellValueAsString(typeCell) != null) {
        String[] split = getCellValueAsString(typeCell).split("-");
        String plat = split[0];
        String type = split[1];
        if (getCellValueAsString(platformCell).equals(plat)) {
            lt = libraryService.getLibraryTypeByDescriptionAndPlatform(type, pt);
        } else {
            throw new InputFormException("Selected library type '" + getCellValueAsString(typeCell)
                    + "' doesn't match platform type: '" + getCellValueAsString(platformCell) + "'");
        }
    }
    if (lt == null) {
        throw new InputFormException(
                "Cannot resolve Library type from: '" + getCellValueAsString(typeCell) + "'");
    } else {
        log.info("Got library type: " + lt.getDescription());
    }

    XSSFCell selectionCell = glrow.getCell(3);
    LibrarySelectionType ls = null;
    if (getCellValueAsString(selectionCell) != null) {
        ls = libraryService.getLibrarySelectionTypeByName(getCellValueAsString(selectionCell));
    }
    if (ls == null) {
        throw new InputFormException(
                "Cannot resolve Library Selection type from: '" + getCellValueAsString(selectionCell) + "'");
    } else {
        log.info("Got library selection type: " + ls.getName());
    }

    XSSFCell strategyCell = glrow.getCell(4);
    LibraryStrategyType lst = null;
    if (getCellValueAsString(strategyCell) != null) {
        lst = libraryService.getLibraryStrategyTypeByName(getCellValueAsString(strategyCell));
    }
    if (lst == null) {
        throw new InputFormException(
                "Cannot resolve Library Strategy type from: '" + getCellValueAsString(strategyCell) + "'");
    } else {
        log.info("Got library strategy type: " + lst.getName());
    }

    // process entries
    Map<String, Pool> pools = new HashMap<>();

    for (int ri = 4; ri < rows; ri++) {
        XSSFRow row = sheet.getRow(ri);

        // cell defs
        XSSFCell sampleAliasCell = row.getCell(2);

        Sample s = null;
        if (getCellValueAsString(sampleAliasCell) != null) {
            String salias = getCellValueAsString(sampleAliasCell);
            Collection<Sample> ss = sampleService.getByAlias(salias);
            if (!ss.isEmpty()) {
                if (ss.size() == 1) {
                    s = ss.iterator().next();
                    log.info("Got sample: " + s.getAlias());
                } else {
                    throw new InputFormException(
                            "Multiple samples retrieved with this alias: '" + salias + "'. Cannot process.");
                }
            } else {
                throw new InputFormException("No such sample '" + salias
                        + "'in database. Samples need to be created before using the form input functionality");
            }
        } else {
            log.info("Blank sample row found. Ending import.");
            break;
        }

        // sample OK - good to go
        if (s != null) {
            String poolNumberCell = getCellValueAsString(row.getCell(3));
            String sampleQcCell = getCellValueAsString(row.getCell(4));
            String libraryDescriptionCell = getCellValueAsString(row.getCell(7));
            String indexKitCell = getCellValueAsString(row.getCell(8));
            String indexTagsCell = getCellValueAsString(row.getCell(9));
            String libraryQcCell = getCellValueAsString(row.getCell(10));
            String libraryQcInsertSizeCell = getCellValueAsString(row.getCell(11));
            String libraryQcMolarityCell = getCellValueAsString(row.getCell(12));
            String libraryQcPassFailCell = getCellValueAsString(row.getCell(13));
            String dilutionMolarityCell = getCellValueAsString(row.getCell(17));
            String poolConvertedMolarityCell = getCellValueAsString(row.getCell(22));

            // add pool, if any
            processPool(poolNumberCell, poolConvertedMolarityCell, pools);
            processSampleQC(sampleQcCell, s, u, qcService);

            Library library = processLibrary(libraryQcCell, libraryDescriptionCell, libraryQcPassFailCell, s,
                    pt, lt, ls, lst, paired, namingScheme);
            if (library != null) {
                processLibraryQC(libraryQcCell, libraryQcMolarityCell, libraryQcInsertSizeCell, library, u,
                        qcService);
                processIndices(indexKitCell, indexTagsCell, library, indexService);
                processDilutions(dilutionMolarityCell, library, pools.get(poolNumberCell), u);
                log.info("Added library: " + library.toString());
                s.addLibrary(library);
            }
            samples.add(s);
        }
    }
    return samples;
}

From source file:uk.co.spudsoft.birt.emitters.excel.tests.AutoColWidthsTest.java

License:Open Source License

@Test
public void testRunReport() throws BirtException, IOException {

    InputStream inputStream = runAndRenderReport("AutoColWidths.rptdesign", "xlsx");
    assertNotNull(inputStream);//from  ww  w. j  a v a  2  s .  c o m
    try {

        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        assertNotNull(workbook);

        assertEquals(1, workbook.getNumberOfSheets());
        assertEquals("AutoColWidths Test Report", workbook.getSheetAt(0).getSheetName());

        Sheet sheet = workbook.getSheetAt(0);
        assertEquals(23, this.firstNullRow(sheet));

        assertEquals(6127, sheet.getColumnWidth(0));
        assertEquals(2048, sheet.getColumnWidth(1));
        assertEquals(4999, sheet.getColumnWidth(2));
        assertEquals(3812, sheet.getColumnWidth(3));
        assertEquals(3812, sheet.getColumnWidth(4));
        assertEquals(2048, sheet.getColumnWidth(5));
        assertTrue((sheet.getColumnWidth(6) > 3000) && (sheet.getColumnWidth(6) < 3200));
        assertTrue((sheet.getColumnWidth(7) > 2100) && (sheet.getColumnWidth(7) < 2900));
        assertEquals(2048, sheet.getColumnWidth(8));

        DataFormatter formatter = new DataFormatter();

        assertEquals("1", formatter.formatCellValue(sheet.getRow(2).getCell(1)));
        assertEquals("2019-10-11 13:18:46", formatter.formatCellValue(sheet.getRow(2).getCell(2)));
        assertEquals("3.1415926536", formatter.formatCellValue(sheet.getRow(2).getCell(3)));
        assertEquals("3.1415926536", formatter.formatCellValue(sheet.getRow(2).getCell(4)));
        assertEquals("false", formatter.formatCellValue(sheet.getRow(2).getCell(5)));

    } finally {
        inputStream.close();
    }
}

From source file:uk.co.spudsoft.birt.emitters.excel.tests.AutoFilterTest.java

License:Open Source License

@Test
public void autoFilter() throws Exception {
    debug = false;/*from  w w  w. ja v  a2 s .  c o  m*/
    autoFilter = true;
    InputStream inputStream = runAndRenderReport("SideBySideMultiColumns.rptdesign", "xlsx");
    assertNotNull(inputStream);
    try {
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        assertNotNull(workbook);

        assertEquals(1, workbook.getNumberOfSheets());
        assertEquals("Sheet0", workbook.getSheetAt(0).getSheetName());

        XSSFSheet sheet = workbook.getSheetAt(0);
        assertEquals(124, this.firstNullRow(sheet));

        assertEquals(297, sheet.getRow(0).getHeightInPoints(), 1.0);
        assertEquals(2048, sheet.getColumnWidth(0));
        assertEquals(6196, sheet.getColumnWidth(1));
        assertEquals(3749, sheet.getColumnWidth(2));
        assertEquals(2396, sheet.getColumnWidth(3));
        assertEquals(4516, sheet.getColumnWidth(4));
        assertEquals(7072, sheet.getColumnWidth(5));
        assertEquals(2048, sheet.getColumnWidth(6));
        assertEquals(3509, sheet.getColumnWidth(7));
        assertEquals(2048, sheet.getColumnWidth(8));
        assertEquals(2314, sheet.getColumnWidth(9));
        assertEquals(2338, sheet.getColumnWidth(10));
        assertEquals(2048, sheet.getColumnWidth(11));
        assertEquals(2048, sheet.getColumnWidth(12));

        assertTrue(mergedRegion(sheet, 0, 0, 0, 5));
        assertTrue(mergedRegion(sheet, 0, 7, 0, 12));

        XSSFName name = workbook.getName(XSSFName.BUILTIN_FILTER_DB);
        assertEquals(0, name.getSheetIndex());
        assertEquals("Sheet0!$A$1:$M$2", name.getRefersToFormula());
    } finally {
        inputStream.close();
    }
}

From source file:uk.co.spudsoft.birt.emitters.excel.tests.AutoFilterTest.java

License:Open Source License

@Test
public void autoFilterMultiSheets() throws Exception {
    debug = false;//from  w ww.  j av a2 s .co  m
    autoFilter = true;
    InputStream inputStream = runAndRenderReport("MultiSheets1.rptdesign", "xlsx");
    assertNotNull(inputStream);
    try {
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        assertNotNull(workbook);

        assertEquals(3, workbook.getNumberOfSheets());
        assertEquals("Number Formats 1", workbook.getSheetAt(0).getSheetName());
        assertEquals("Number Formats 2", workbook.getSheetAt(1).getSheetName());
        assertEquals("Number Formats 3", workbook.getSheetAt(2).getSheetName());

        assertEquals(4, firstNullRow(workbook.getSheetAt(0)));
        assertEquals(4, firstNullRow(workbook.getSheetAt(1)));
        assertEquals(3, firstNullRow(workbook.getSheetAt(2)));

        assertEquals(true, workbook.getSheetAt(0).isDisplayGridlines());
        assertEquals(false, workbook.getSheetAt(1).isDisplayGridlines());
        assertEquals(false, workbook.getSheetAt(2).isDisplayGridlines());
        assertEquals(true, workbook.getSheetAt(0).isDisplayRowColHeadings());
        assertEquals(false, workbook.getSheetAt(1).isDisplayGridlines());
        assertEquals(true, workbook.getSheetAt(2).isDisplayRowColHeadings());

        XSSFName name = workbook.getName(XSSFName.BUILTIN_FILTER_DB);
        assertEquals(0, name.getSheetIndex());
        assertEquals("'Number Formats 1'!$A$1:$H$3", name.getRefersToFormula());

        assertNotNull(workbook.getSheetAt(0).getCTWorksheet().getAutoFilter());
        assertNotNull(workbook.getSheetAt(1).getCTWorksheet().getAutoFilter());
        assertNotNull(workbook.getSheetAt(2).getCTWorksheet().getAutoFilter());
    } finally {
        inputStream.close();
    }
}

From source file:uk.co.spudsoft.birt.emitters.excel.tests.AutoFilterTest.java

License:Open Source License

@Test
public void autoFilterMultiTables() throws Exception {
    debug = false;/*from   ww w. j  av  a2s  . co m*/
    autoFilter = true;
    InputStream inputStream = runAndRenderReport("NumberFormats.rptdesign", "xlsx");
    assertNotNull(inputStream);
    try {

        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        assertNotNull(workbook);

        assertEquals(1, workbook.getNumberOfSheets());
        assertEquals("Number Formats Test Report", workbook.getSheetAt(0).getSheetName());

        Sheet sheet = workbook.getSheetAt(0);
        assertEquals(22, this.firstNullRow(sheet));

        assertEquals(3035, sheet.getColumnWidth(0));
        assertEquals(3913, sheet.getColumnWidth(1));
        assertEquals(7021, sheet.getColumnWidth(2));
        assertEquals(4205, sheet.getColumnWidth(3));
        assertEquals(3474, sheet.getColumnWidth(4));
        assertEquals(2852, sheet.getColumnWidth(5));
        assertEquals(3510, sheet.getColumnWidth(6));
        assertEquals(2889, sheet.getColumnWidth(7));
        assertEquals(2048, sheet.getColumnWidth(8));

        XSSFName name = workbook.getName(XSSFName.BUILTIN_FILTER_DB);
        assertEquals(0, name.getSheetIndex());
        assertEquals("'Number Formats Test Report'!$A$1:$H$3", name.getRefersToFormula());

        assertNotNull(workbook.getSheetAt(0).getCTWorksheet().getAutoFilter());
    } finally {
        inputStream.close();
    }
}

From source file:uk.co.spudsoft.birt.emitters.excel.tests.AutoRowHeightsTest.java

License:Open Source License

@Test
public void testRunReportXlsx() throws BirtException, IOException {

    InputStream inputStream = runAndRenderReport("AutoRowHeight.rptdesign", "xlsx");
    assertNotNull(inputStream);// w w w .j a va 2s  .co m
    try {
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        assertNotNull(workbook);

        assertEquals(1, workbook.getNumberOfSheets());
        assertEquals("Auto RowHeight Report", workbook.getSheetAt(0).getSheetName());

        Sheet sheet = workbook.getSheetAt(0);
        assertEquals(7, this.firstNullRow(sheet));

        assertEquals(300, sheet.getRow(0).getHeight());
        assertEquals(992, sheet.getRow(1).getHeight());
        assertEquals(826, sheet.getRow(2).getHeight());
        assertEquals(1405, sheet.getRow(3).getHeight());
        assertEquals(2988, sheet.getRow(4).getHeight());
        assertEquals(300, sheet.getRow(5).getHeight());
        assertEquals(4103, sheet.getRow(6).getHeight());

    } finally {
        inputStream.close();
    }
}

From source file:uk.co.spudsoft.birt.emitters.excel.tests.AutoRowHeightsTest2.java

License:Open Source License

@Test
public void testRunReportXlsx() throws BirtException, IOException {

    InputStream inputStream = runAndRenderReport("AutoRowHeight2.rptdesign", "xlsx");
    assertNotNull(inputStream);/*from   www.  j  a v a2  s  . c  o  m*/
    try {
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        assertNotNull(workbook);

        assertEquals(1, workbook.getNumberOfSheets());
        assertEquals("Auto RowHeight Report 2", workbook.getSheetAt(0).getSheetName());

        Sheet sheet = workbook.getSheetAt(0);
        assertEquals(1, this.firstNullRow(sheet));

        assertEquals(2298, sheet.getRow(0).getHeight());
    } finally {
        inputStream.close();
    }
}

From source file:uk.co.spudsoft.birt.emitters.excel.tests.BackgroundFormatsTests.java

License:Open Source License

@Test
public void testRunReportXlsx() throws BirtException, IOException {

    InputStream inputStream = runAndRenderReport("BackgroundColours.rptdesign", "xlsx");
    assertNotNull(inputStream);/*w ww. j av  a 2 s  .  c o m*/
    try {

        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        assertNotNull(workbook);

        assertEquals(1, workbook.getNumberOfSheets());
        assertEquals("Background Colours Report", workbook.getSheetAt(0).getSheetName());

        Sheet sheet = workbook.getSheetAt(0);
        assertEquals(3, this.firstNullRow(sheet));

        DataFormatter formatter = new DataFormatter();

        assertEquals("1", formatter.formatCellValue(sheet.getRow(1).getCell(1)));
        assertEquals("2019-10-11 13:18:46", formatter.formatCellValue(sheet.getRow(1).getCell(2)));
        assertEquals("3.1415926536", formatter.formatCellValue(sheet.getRow(1).getCell(3)));
        assertEquals("3.1415926536", formatter.formatCellValue(sheet.getRow(1).getCell(4)));
        assertEquals("false", formatter.formatCellValue(sheet.getRow(1).getCell(5)));

        assertEquals("FF000000",
                ((XSSFColor) sheet.getRow(0).getCell(0).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF000000",
                ((XSSFColor) sheet.getRow(0).getCell(1).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF000000",
                ((XSSFColor) sheet.getRow(0).getCell(2).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF000000",
                ((XSSFColor) sheet.getRow(0).getCell(3).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF000000",
                ((XSSFColor) sheet.getRow(0).getCell(4).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF000000",
                ((XSSFColor) sheet.getRow(0).getCell(5).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF000000",
                ((XSSFColor) sheet.getRow(0).getCell(6).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        /*
                 assertEquals( null,              ((XSSFColor)sheet.getRow(0).getCell(0).getCellStyle().getFillForegroundColorColor()));
                 assertEquals( null,              ((XSSFColor)sheet.getRow(0).getCell(1).getCellStyle().getFillForegroundColorColor()));
                 assertEquals( null,              ((XSSFColor)sheet.getRow(0).getCell(2).getCellStyle().getFillForegroundColorColor()));
                 assertEquals( null,              ((XSSFColor)sheet.getRow(0).getCell(3).getCellStyle().getFillForegroundColorColor()));
                 assertEquals( null,              ((XSSFColor)sheet.getRow(0).getCell(4).getCellStyle().getFillForegroundColorColor()));
                 assertEquals( null,              ((XSSFColor)sheet.getRow(0).getCell(5).getCellStyle().getFillForegroundColorColor()));
                 assertEquals( null,              ((XSSFColor)sheet.getRow(0).getCell(6).getCellStyle().getFillForegroundColorColor()));
        */
        assertEquals("FFFF0000",
                ((XSSFColor) sheet.getRow(1).getCell(1).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FFFFA500",
                ((XSSFColor) sheet.getRow(1).getCell(2).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FFFFFF00",
                ((XSSFColor) sheet.getRow(1).getCell(3).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF008000",
                ((XSSFColor) sheet.getRow(1).getCell(4).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF0000FF",
                ((XSSFColor) sheet.getRow(1).getCell(5).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF800080",
                ((XSSFColor) sheet.getRow(1).getCell(6).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());
        assertEquals("FF000000",
                ((XSSFColor) sheet.getRow(1).getCell(7).getCellStyle().getFillForegroundColorColor())
                        .getARGBHex());

    } finally {
        inputStream.close();
    }
}

From source file:uk.co.spudsoft.birt.emitters.excel.tests.BasicReportTest.java

License:Open Source License

@Test
public void testRunReport() throws BirtException, IOException {

    InputStream inputStream = runAndRenderReport("Simple.rptdesign", "xlsx");
    assertNotNull(inputStream);//w w w  .j  a v  a 2 s.co  m
    try {

        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        assertNotNull(workbook);

        assertEquals(1, workbook.getNumberOfSheets());
        assertEquals("Simple Test Report", workbook.getSheetAt(0).getSheetName());

        Sheet sheet = workbook.getSheetAt(0);
        assertNotNull(sheet.getRow(0));
        assertNotNull(sheet.getRow(1));
        assertNotNull(sheet.getRow(2));
        assertNotNull(sheet.getRow(3));
        assertNull(sheet.getRow(4));

        assertEquals(1.0, sheet.getRow(1).getCell(0).getNumericCellValue(), 0.001);
        assertEquals(2.0, sheet.getRow(1).getCell(1).getNumericCellValue(), 0.001);
        assertEquals(3.0, sheet.getRow(1).getCell(2).getNumericCellValue(), 0.001);
        assertEquals(2.0, sheet.getRow(2).getCell(0).getNumericCellValue(), 0.001);
        assertEquals(4.0, sheet.getRow(2).getCell(1).getNumericCellValue(), 0.001);
        assertEquals(6.0, sheet.getRow(2).getCell(2).getNumericCellValue(), 0.001);
        assertEquals(3.0, sheet.getRow(3).getCell(0).getNumericCellValue(), 0.001);
        assertEquals(6.0, sheet.getRow(3).getCell(1).getNumericCellValue(), 0.001);
        assertEquals(9.0, sheet.getRow(3).getCell(2).getNumericCellValue(), 0.001);

        assertEquals(3510, sheet.getColumnWidth(0));
        assertEquals(3510, sheet.getColumnWidth(1));
        assertEquals(3510, sheet.getColumnWidth(2));
    } finally {
        inputStream.close();
    }
}