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

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

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

Introduction

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

Prototype

@Override
public XSSFSheet getSheet(String name)

Source Link

Document

Get sheet with the given name (case insensitive match)

Usage

From source file:org.easybatch.extensions.msexcel.MsExcelSupportIntegrationTest.java

License:Open Source License

@Test
public void integrationTest() throws Exception {

    File inputTweets = new File(this.getClass().getResource("/tweets-in.xlsx").toURI());
    File outputTweets = new File(this.getClass().getResource("/tweets-out.xlsx").toURI());

    String[] fields = { "id", "user", "message" };
    Job job = JobBuilder.aNewJob().reader(new MsExcelRecordReader(inputTweets))
            .mapper(new MsExcelRecordMapper<>(Tweet.class, fields))
            .marshaller(new MsExcelRecordMarshaller<>(Tweet.class, fields))
            .writer(new MsExcelRecordWriter(outputTweets, SHEET_NAME)).build();

    JobReport report = new JobExecutor().execute(job);

    assertThat(report).isNotNull();/*from w w w  .  j  a  va 2 s.  co  m*/
    assertThat(report.getMetrics().getReadCount()).isEqualTo(2);
    assertThat(report.getMetrics().getWriteCount()).isEqualTo(2);
    assertThat(report.getStatus()).isEqualTo(JobStatus.COMPLETED);

    XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(outputTweets));
    XSSFSheet sheet = workbook.getSheet(SHEET_NAME);
    XSSFRow row = sheet.getRow(1);
    assertThat(row.getCell(0).getNumericCellValue()).isEqualTo(1.0);
    assertThat(row.getCell(1).getStringCellValue()).isEqualTo("foo");
    assertThat(row.getCell(2).getStringCellValue()).isEqualTo("hi");
    row = sheet.getRow(2);
    assertThat(row.getCell(0).getNumericCellValue()).isEqualTo(2.0);
    assertThat(row.getCell(1).getStringCellValue()).isEqualTo("bar");
    assertThat(row.getCell(2).getStringCellValue()).isEqualTo("hello");
}

From source file:org.openbase.bco.ontology.lib.testing.Measurement.java

License:Open Source License

private void saveMemoryTestValues(final String sheetName, final List<Long> simpleQuMeasuredValues,
        final List<Long> complexQuMeasuredValues, final DataVolume dataVolume) {
    XSSFWorkbook workbook;
    XSSFSheet sheet;/*from  w w w  . ja  va  2s  .com*/
    Row rowSimple;
    Row rowComplex;

    try {
        FileInputStream excelFile = new FileInputStream(new File(FILE_NAME));
        workbook = new XSSFWorkbook(excelFile);
        sheet = workbook.getSheet(sheetName);
        rowSimple = sheet.getRow(1);
        rowComplex = sheet.getRow(2);
    } catch (IOException ex) {
        workbook = new XSSFWorkbook();
        sheet = workbook.createSheet(sheetName);
        final Row row = sheet.createRow(0);
        rowSimple = sheet.createRow(1);
        rowComplex = sheet.createRow(2);

        row.createCell(1).setCellValue("ConfigData only");
        row.createCell(2).setCellValue("ConfigData and dayData");
        row.createCell(3).setCellValue("ConfigData and 4x dayData");
    }

    long sumSimple = 0L;
    long sumComplex = 0L;

    for (final long valueSimple : simpleQuMeasuredValues) {
        sumSimple += valueSimple;
    }
    for (final long valueComplex : complexQuMeasuredValues) {
        sumComplex += valueComplex;
    }

    int column = 0;

    switch (dataVolume) {
    case CONFIG:
        column = 1;
        break;
    case CONFIG_DAY:
        column = 2;
        break;
    case CONFIG_WEEK:
        column = 3;
        break;
    default:
        break;
    }

    System.out.println("Save date in column: " + column);

    // mean of simple trigger time
    final Cell cellMeanSimple = rowSimple.createCell(column);
    cellMeanSimple.setCellValue(sumSimple / simpleQuMeasuredValues.size());

    // mean of complex trigger time
    final Cell cellMeanComplex = rowComplex.createCell(column);
    cellMeanComplex.setCellValue(sumComplex / complexQuMeasuredValues.size());

    try {
        final File file = new File(FILE_NAME);
        file.setReadable(true, false);
        file.setExecutable(true, false);
        file.setWritable(true, false);
        System.out.println("Save data row ...");
        final FileOutputStream outputStream = new FileOutputStream(file);
        workbook.write(outputStream);
        workbook.close();
    } catch (IOException ex) {
        ExceptionPrinter.printHistory(ex, LOGGER);
    }
}

From source file:org.openbase.bco.ontology.lib.testing.Measurement.java

License:Open Source License

private void putIntoExcelFile(final String sheetName, final List<Long> simpleQuMeasuredValues,
        final List<Long> complexQuMeasuredValues, int daysCurCount) {
    // https://www.mkyong.com/java/apache-poi-reading-and-writing-excel-file-in-java/
    XSSFWorkbook workbook;
    XSSFSheet sheet;//from   w w w . ja v  a2s .  com
    Row row;

    try {
        FileInputStream excelFile = new FileInputStream(new File(FILE_NAME));
        workbook = new XSSFWorkbook(excelFile);
        sheet = workbook.getSheet(sheetName);
    } catch (IOException ex) {
        workbook = new XSSFWorkbook();
        sheet = workbook.createSheet(sheetName);
        row = sheet.createRow(daysCurCount);

        row.createCell(0).setCellValue("Days");
        row.createCell(1).setCellValue("Triple");
        row.createCell(2).setCellValue("Mean of simple trigger");
        row.createCell(3).setCellValue("Mean of complex trigger");
    }

    row = sheet.createRow(daysCurCount + 1);

    System.out.println("simple: " + simpleQuMeasuredValues);
    System.out.println("simple count: " + simpleQuMeasuredValues.size());
    System.out.println("complex: " + complexQuMeasuredValues);
    System.out.println("complex count: " + complexQuMeasuredValues.size());

    long sumSimple = 0L;
    long sumComplex = 0L;

    for (final long valueSimple : simpleQuMeasuredValues) {
        sumSimple += valueSimple;
    }
    for (final long valueComplex : complexQuMeasuredValues) {
        sumComplex += valueComplex;
    }

    // number of days
    final Cell cellDay = row.createCell(0);
    cellDay.setCellValue(daysCurCount + 1);

    // number of triple
    final Cell cellTriple = row.createCell(1);
    cellTriple.setCellValue(numberOfTriple);

    // mean of simple trigger time
    final Cell cellMeanSimple = row.createCell(2);
    cellMeanSimple.setCellValue(sumSimple / simpleQuMeasuredValues.size());

    // mean of complex trigger time
    final Cell cellMeanComplex = row.createCell(3);
    cellMeanComplex.setCellValue(sumComplex / complexQuMeasuredValues.size());

    try {
        final File file = new File(FILE_NAME);
        file.setReadable(true, false);
        file.setExecutable(true, false);
        file.setWritable(true, false);
        System.out.println("Save data row ...");
        final FileOutputStream outputStream = new FileOutputStream(file);
        workbook.write(outputStream);
        workbook.close();
    } catch (IOException ex) {
        ExceptionPrinter.printHistory(ex, LOGGER);
    }
}

From source file:org.tsukuba_bunko.lilac.service.impl.ImportServiceImpl.java

License:Open Source License

/**
 * @see org.tsukuba_bunko.lilac.service.ImportService#importData(int)
 *///from   ww w.ja va 2  s  . c o  m
@Override
public void importData(int fileId) {
    String userId = getCurrentSessionUser();
    ImportFile importFileEntity = jdbcManager.from(ImportFile.class)
            .where(new SimpleWhere().eq("id", fileId).eq("username", userId)).disallowNoResult()
            .getSingleResult();

    File importFile = new File(importFileStore, String.format("%016d", importFileEntity.id));
    FileInputStream source = null;
    try {
        source = new FileInputStream(importFile);
        XSSFWorkbook book = new XSSFWorkbook(source);

        XSSFSheet sheet = book.getSheet("");
        if (sheet != null) {
            importLabelHelper.importData(sheet);
        }

        sheet = book.getSheet("");
        if (sheet != null) {
            importAuthorHelper.importData(sheet);
        }

        sheet = book.getSheet("");
        if (sheet != null) {
            importBibliographyHelper.importData(sheet);
        }

        sheet = book.getSheet("");
        if (sheet != null) {
            importBookshelfHelper.importData(sheet);
        }

        sheet = book.getSheet("");
        if (sheet != null) {
            importBookHelper.importData(sheet);
        }

        sheet = book.getSheet("");
        if (sheet != null) {
            importReadingRecordHelper.importData(sheet);
        }
    } catch (IOException ioe) {
        throw new IORuntimeException(ioe);
    } finally {
        InputStreamUtil.closeSilently(source);
    }

    importFile.delete();
    jdbcManager.delete(importFileEntity).execute();
}

From source file:org.xframium.application.ExcelApplicationProvider.java

License:Open Source License

/**
 * Read elements.//w ww .  jav a 2  s.  co m
 *
 * @param inputStream the input stream
 */
private void readElements(InputStream inputStream) {

    XSSFWorkbook workbook = null;

    try {
        workbook = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = workbook.getSheet(tabName);

        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            XSSFRow currentRow = sheet.getRow(i);

            if (getCellValue(currentRow.getCell(0)) == null || getCellValue(currentRow.getCell(0)).isEmpty())
                break;

            ApplicationRegistry.instance()
                    .addApplicationDescriptor(new ApplicationDescriptor(getCellValue(currentRow.getCell(0)),
                            getCellValue(currentRow.getCell(4)), getCellValue(currentRow.getCell(1)),
                            getCellValue(currentRow.getCell(2)), getCellValue(currentRow.getCell(3)),
                            getCellValue(currentRow.getCell(5)), getCellValue(currentRow.getCell(6)),
                            new HashMap<String, Object>(0)));

        }

    } catch (Exception e) {
        log.fatal("Error reading Excel Element File", e);
    } finally {
        try {
            workbook.close();
        } catch (Exception e) {
        }
    }
}

From source file:org.xframium.content.provider.ExcelContentProvider.java

License:Open Source License

/**
 * Read elements./*from w  ww  .  j a v  a 2s  .c  o  m*/
 *
 * @param inputStream the input stream
 */
private void readElements(InputStream inputStream) {

    XSSFWorkbook workbook = null;

    try {
        workbook = new XSSFWorkbook(inputStream);

        XSSFSheet sheet = workbook.getSheet(tabName);

        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            XSSFRow currentRow = sheet.getRow(i);

            String keyName = getCellValue(currentRow.getCell(keyColumn));

            String[] valueList = new String[lookupColumns.length];

            for (int x = 0; x < lookupColumns.length; x++) {
                valueList[x] = getCellValue(currentRow.getCell(lookupColumns[x]));
            }

            ContentData contentData = new DefaultContentData(keyName, valueList);

            ContentManager.instance().addContentData(contentData);
        }

    } catch (Exception e) {
        log.fatal("Error reading Excel Element File", e);
    } finally {
        try {
            workbook.close();
        } catch (Exception e) {
        }
    }
}

From source file:org.xframium.device.cloud.ExcelCloudProvider.java

License:Open Source License

/**
 * Read elements./*from w ww . j  ava 2s  .c o  m*/
 *
 * @param inputStream the input stream
 */
private void readElements(InputStream inputStream) {
    XSSFWorkbook workbook = null;

    try {
        workbook = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = workbook.getSheet(tabName);

        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            XSSFRow currentRow = sheet.getRow(i);

            if (getCellValue(currentRow.getCell(0)) == null || getCellValue(currentRow.getCell(0)).isEmpty())
                break;

            CloudRegistry.instance()
                    .addCloudDescriptor(new CloudDescriptor(getCellValue(currentRow.getCell(0)),
                            getCellValue(currentRow.getCell(1)), getCellValue(currentRow.getCell(2)),
                            getCellValue(currentRow.getCell(3)), getCellValue(currentRow.getCell(4)),
                            getCellValue(currentRow.getCell(5)), getCellValue(currentRow.getCell(7)),
                            getCellValue(currentRow.getCell(6)), getCellValue(currentRow.getCell(8))));
        }

    } catch (Exception e) {
        log.fatal("Error reading Excel Element File", e);
    } finally {
        try {
            workbook.close();
        } catch (Exception e) {
        }
    }
}

From source file:org.xframium.device.data.ExcelDataProvider.java

License:Open Source License

/**
 * Read data./*  w  w w  .j a v  a  2s . com*/
 *
 * @param inputStream the input stream
 */
private void readData(InputStream inputStream) {
    BufferedReader fileReader = null;

    XSSFWorkbook workbook = null;

    try {
        workbook = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = workbook.getSheet(tabName);

        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            XSSFRow currentRow = sheet.getRow(i);

            if (getCellValue(currentRow.getCell(0)) == null || getCellValue(currentRow.getCell(0)).isEmpty())
                break;

            String driverName = "";
            switch (driverType) {
            case APPIUM:
                if (getCellValue(currentRow.getCell(3)).toUpperCase().equals("IOS"))
                    driverName = "IOS";
                else if (getCellValue(currentRow.getCell(3)).toUpperCase().equals("ANDROID"))
                    driverName = "ANDROID";
                else
                    throw new IllegalArgumentException("Appium is not supported on the following OS "
                            + getCellValue(currentRow.getCell(3)).toUpperCase());
                break;

            case PERFECTO:
                driverName = "PERFECTO";
                break;

            case WEB:
                driverName = "WEB";
                break;
            }

            Device currentDevice = new SimpleDevice(getCellValue(currentRow.getCell(0)),
                    getCellValue(currentRow.getCell(1)), getCellValue(currentRow.getCell(2)),
                    getCellValue(currentRow.getCell(3)), getCellValue(currentRow.getCell(4)),
                    getCellValue(currentRow.getCell(5)), getCellValue(currentRow.getCell(6)),
                    Integer.parseInt(getCellValue(currentRow.getCell(7))), driverName,
                    Boolean.parseBoolean(getCellValue(currentRow.getCell(8))), null);
            if (currentDevice.isActive()) {
                if (log.isDebugEnabled())
                    log.debug("Extracted: " + currentDevice);

                DeviceManager.instance().registerDevice(currentDevice);
            }
        }
    } catch (Exception e) {
        log.fatal("Error reading Excel Element File", e);
    } finally {
        try {
            workbook.close();
        } catch (Exception e) {
        }
    }
}

From source file:org.xframium.page.data.provider.ExcelPageDataProvider.java

License:Open Source License

/**
 * Read elements./*from  w w  w  .  ja va2  s  .  c o m*/
 *
 * @param inputStream the input stream
 */
private void readElements(InputStream inputStream) {

    XSSFWorkbook workbook = null;

    try {
        workbook = new XSSFWorkbook(inputStream);

        String[] tabs = tabNames.split(",");

        for (String tabName : tabs) {
            XSSFSheet sheet = workbook.getSheet(tabName);

            if (sheet == null)
                continue;

            addRecordType(tabName, false);

            XSSFRow firstRow = sheet.getRow(0);

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                XSSFRow currentRow = sheet.getRow(i);

                if (getCellValue(currentRow.getCell(0)) == null
                        || getCellValue(currentRow.getCell(0)).isEmpty())
                    break;

                DefaultPageData currentRecord = new DefaultPageData(tabName, tabName + "-" + i, true);
                for (int x = 0; x < firstRow.getLastCellNum(); x++) {

                    String currentName = getCellValue(firstRow.getCell(x));
                    String currentValue = getCellValue(currentRow.getCell(x));

                    if (currentValue == null)
                        currentValue = "";

                    if (currentValue.startsWith(PageData.TREE_MARKER)
                            && currentValue.endsWith(PageData.TREE_MARKER)) {
                        //
                        // This is a reference to another page data table
                        //
                        currentRecord.addPageData(currentName);
                        currentRecord.addValue(currentName + PageData.DEF, currentValue);
                        currentRecord.setContainsChildren(true);
                    } else
                        currentRecord.addValue(currentName, currentValue);
                }

                addRecord(currentRecord);
            }
        }

    } catch (Exception e) {
        log.fatal("Error reading Excel Element File", e);
    } finally {
        try {
            workbook.close();
        } catch (Exception e) {
        }
    }
}

From source file:org.xframium.page.element.provider.ExcelElementProvider.java

License:Open Source License

/**
 * Read elements.//from  w  ww . j av a 2  s . c o  m
 *
 * @param inputStream the input stream
 */
private void readElements(InputStream inputStream) {

    XSSFWorkbook workbook = null;

    try {
        workbook = new XSSFWorkbook(inputStream);
        boolean elementsRead = true;
        String[] tabs = tabNames.split(",");

        for (String tabName : tabs) {
            XSSFSheet sheet = workbook.getSheet(tabName);
            if (sheet == null)
                continue;

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                System.out.println(i);
                XSSFRow currentRow = sheet.getRow(i);

                if (getCellValue(currentRow.getCell(0)) == null
                        || getCellValue(currentRow.getCell(0)).isEmpty())
                    break;

                ElementDescriptor elementDescriptor = new ElementDescriptor(tabName,
                        getCellValue(currentRow.getCell(0)), getCellValue(currentRow.getCell(1)));

                String contextName = null;
                if (getCellValue(currentRow.getCell(4)) != null
                        && !getCellValue(currentRow.getCell(4)).isEmpty()) {
                    contextName = getCellValue(currentRow.getCell(4));
                }

                Element currentElement = ElementFactory.instance().createElement(
                        BY.valueOf(getCellValue(currentRow.getCell(2))),
                        getCellValue(currentRow.getCell(3)).replace("$$", ","),
                        getCellValue(currentRow.getCell(1)), getCellValue(currentRow.getCell(0)), contextName);

                if (log.isDebugEnabled())
                    log.debug("Adding Excel Element using [" + elementDescriptor.toString() + "] as ["
                            + currentElement);
                elementsRead = elementsRead & validateElement(elementDescriptor, currentElement);
                elementMap.put(elementDescriptor.toString(), currentElement);

            }
        }

        setInitialized(elementsRead);

    } catch (Exception e) {
        log.fatal("Error reading Excel Element File", e);
    } finally {
        try {
            workbook.close();
        } catch (Exception e) {
        }
    }
}