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

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

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

Introduction

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

Prototype

Sheet getSheetAt(int index);

Source Link

Document

Get the Sheet object at the given index.

Usage

From source file:egovframework.rte.fdl.excel.impl.EgovExcelServiceImpl.java

License:Apache License

/**
 * ??  DB? ?.<br/>// w w  w  . jav a2  s.c  o  m
 *  ?      .
 * 
 * @param queryId
 * @param fileIn
 * @param sheetIndex
 * @param start (default : 0)
 * @param commitCnt (default : 0)
 * @return
 * @throws Exception
 */
public Integer uploadExcel(String queryId, InputStream fileIn, short sheetIndex, int start, long commitCnt)
        throws BaseException, Exception {
    Workbook wb = loadWorkbook(fileIn);
    Sheet sheet = wb.getSheetAt(sheetIndex);

    return uploadExcel(queryId, sheet, start, commitCnt);
}

From source file:eionet.gdem.conversion.excel.reader.ExcelReaderFormulaTest.java

License:Mozilla Public License

@Test
public void testGetFormulaValueXls() throws Exception {
    File inFile = new File(
            this.getClass().getClassLoader().getResource(TestConstants.SEED_FORMULAS_XLS).getFile());

    ExcelReader excel = new ExcelReader(false);
    excel.initReader(inFile);/*from  www  .  j  a  v a  2s  . co  m*/

    Workbook workbook = excel.getWorkbook();

    //test integer formula
    Cell cell = workbook.getSheetAt(0).getRow(1).getCell(3);
    String value = excel.cellValueToString(cell, "xs:integer");
    assertEquals("2011", value);

    //test string formula
    Cell cell2 = workbook.getSheetAt(0).getRow(1).getCell(1);
    String value2 = excel.cellValueToString(cell2, "xs:string");
    assertEquals("EE11", value2);

    //test sum formula
    Cell cell3 = workbook.getSheetAt(0).getRow(3).getCell(3);
    String value3 = excel.cellValueToString(cell3, "xs:integer");
    assertEquals("4011", value3);

    //test decimal formula
    Cell cell4 = workbook.getSheetAt(0).getRow(1).getCell(4);
    String value4 = excel.cellValueToString(cell4, "xs:decimal");
    assertEquals("2010.123", value4);
}

From source file:eionet.gdem.conversion.excel.reader.ExcelReaderFormulaTest.java

License:Mozilla Public License

public void testGetFormulaValueXls2007() throws Exception {
    File inFile = new File(
            this.getClass().getClassLoader().getResource(TestConstants.SEED_FORMULAS_XLSX).getFile());

    ExcelReader excel = new ExcelReader(true);
    excel.initReader(inFile);/*from w  w  w  .  j  av  a2 s  .c  om*/

    Workbook workbook = excel.getWorkbook();

    //test integer formula
    Cell cell = workbook.getSheetAt(0).getRow(1).getCell(3);
    String value = excel.cellValueToString(cell, "xs:integer");
    assertEquals("2011", value);

    //test string formula
    Cell cell2 = workbook.getSheetAt(0).getRow(1).getCell(1);
    String value2 = excel.cellValueToString(cell2, "xs:string");
    assertEquals("EE11", value2);

    //test sum formula
    Cell cell3 = workbook.getSheetAt(0).getRow(3).getCell(3);
    String value3 = excel.cellValueToString(cell3, "xs:integer");
    assertEquals("4011", value3);

    //test decimal formula
    Cell cell4 = workbook.getSheetAt(0).getRow(1).getCell(4);
    String value4 = excel.cellValueToString(cell4, "xs:decimal");
    assertEquals("2010.123", value4);
}

From source file:eionet.gdem.conversion.excel.reader.ExcelReaderTest.java

License:Mozilla Public License

@Test
public void testTrimIntegerValues() throws Exception {
    File inFile = new File(
            this.getClass().getClassLoader().getResource(TestConstants.SEED_RIVERS_XLS).getFile());

    ExcelReader excel = new ExcelReader(false);
    excel.initReader(inFile);/*from   w  ww .j a  v  a  2 s. c  o  m*/

    Workbook workbook = excel.getWorkbook();

    //test value in Mean column
    Cell cell = workbook.getSheetAt(0).getRow(1).getCell(13);
    String value = excel.cellValueToString(cell, "xs:integer");
    assertEquals("54.675000", value);
}

From source file:eionet.gdem.conversion.excel.reader.ExcelReaderTest.java

License:Mozilla Public License

@Test
public void testTrimStringValues() throws Exception {
    File inFile = new File(
            this.getClass().getClassLoader().getResource(TestConstants.SEED_RIVERS_XLS).getFile());

    ExcelReader excel = new ExcelReader(false);
    excel.initReader(inFile);/*from  w w w . ja v a  2 s  .  co  m*/

    Workbook workbook = excel.getWorkbook();

    //test string value trimming. PeriodLength column
    Cell cell = workbook.getSheetAt(0).getRow(1).getCell(4);
    String value = excel.cellValueToString(cell, "xs:string");
    assertEquals("Trim this string", value);
}

From source file:eu.esdihumboldt.hale.app.bgis.ade.common.AbstractAnalyseTable.java

License:Open Source License

/**
 * Load table to analyse from an Excel file.
 * //from   w w  w  .ja  v a  2  s  .  c o m
 * @param location the file location
 * @throws Exception if an error occurs loading the file
 */
protected void analyse(URI location) throws Exception {
    InputStream inp = new BufferedInputStream(location.toURL().openStream());

    try {
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);
        evaluator = wb.getCreationHelper().createFormulaEvaluator();

        // the first row represents the header
        analyseHeader(sheet);

        // load configuration entries
        analyseContent(sheet);
    } finally {
        // reset evaluator reference
        evaluator = null;

        // unclear whether the POI API closes the stream
        inp.close();
    }
}

From source file:eu.esdihumboldt.hale.io.csv.ui.LookupTablePage.java

License:Open Source License

private String[] readHeader() {
    LookupTableImport provider = getWizard().getProvider();
    List<String> items = new ArrayList<String>();
    try {// www. j a va 2 s  .c  o  m
        if (provider instanceof CSVLookupReader) {
            CSVReader reader = CSVUtil.readFirst(getWizard().getProvider());
            return reader.readNext();
        } else {
            Workbook workbook;
            // write xls file
            String file = provider.getSource().getLocation().getPath();
            String fileExtension = file.substring(file.lastIndexOf("."), file.length());
            if (fileExtension.equals(".xls")) {
                workbook = new HSSFWorkbook(provider.getSource().getInput());
            }
            // write xlsx file
            else if (fileExtension.equals(".xlsx")) {
                workbook = new XSSFWorkbook(provider.getSource().getInput());
            } else
                return new String[0];
            Sheet sheet = workbook.getSheetAt(0);
            Row currentRow = sheet.getRow(0);
            for (int cell = 0; cell < currentRow.getPhysicalNumberOfCells(); cell++) {
                items.add(currentRow.getCell(cell).getStringCellValue());
            }
            return items.toArray(new String[0]);
        }
    } catch (IOException e) {
        return new String[0];
    }
}

From source file:eu.esdihumboldt.hale.io.xls.AbstractAnalyseTable.java

License:Open Source License

/**
 * Load table to analyse from an Excel file.
 * /*ww w.  ja v  a 2s .  c o m*/
 * @param location the file location
 * @param sheetNum number of the sheet that should be loaded (0-based)
 * @throws Exception if an error occurs loading the file
 */
protected void analyse(URI location, int sheetNum) throws Exception {
    InputStream inp = new BufferedInputStream(location.toURL().openStream());

    try {
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(sheetNum);
        evaluator = wb.getCreationHelper().createFormulaEvaluator();

        // the first row represents the header
        analyseHeader(sheet);

        // load configuration entries
        analyseContent(sheet);
    } finally {
        // reset evaluator reference
        evaluator = null;

        // unclear whether the POI API closes the stream
        inp.close();
    }
}

From source file:eu.esdihumboldt.hale.io.xls.reader.DefaultXLSLookupTableReader.java

License:Open Source License

/**
 * Reads a xls/xlsx lookup table workbook (from apache POI). The selected
 * columns specified by parameters keyColumn and valueColumn are mapped
 * together.//ww  w.  ja va 2s.  com
 * 
 * @param workbook the workbook to read
 * @param skipFirst true, if first row should be skipped
 * @param keyColumn source column of the lookup table
 * @param valueColumn target column of the lookup table
 * @return the lookup table as map
 */
public Map<Value, Value> read(Workbook workbook, boolean skipFirst, int keyColumn, int valueColumn) {
    Map<Value, Value> map = new LinkedHashMap<Value, Value>();
    Sheet sheet = workbook.getSheetAt(0);
    int row = 0;
    if (skipFirst)
        row++;
    for (; row < sheet.getPhysicalNumberOfRows(); row++) {
        Row currentRow = sheet.getRow(row);
        map.put(Value.of(currentRow.getCell(keyColumn).getStringCellValue()),
                Value.of(currentRow.getCell(valueColumn).getStringCellValue()));
    }

    return map;
}

From source file:eu.esdihumboldt.hale.io.xls.test.writer.XLSInstanceWriterTest.java

License:Open Source License

/**
 * Test - write data of complex schema and analyze result
 * //from w ww  . j  a va 2 s  . c o  m
 * @throws Exception , if an error occurs
 */
@Test
public void testWriteComplexSchema() throws Exception {

    TransformationExample example = TransformationExamples.getExample(TransformationExamples.SIMPLE_COMPLEX);
    // alternative the data could be generated by iterating through the
    // exempleproject's sourcedata
    List<String> header = Arrays.asList("id", "name", "details.age", "details.income", "details.address.street",
            "details.address.city");
    List<String> firstDataRow = Arrays.asList("id0", "name0", "age0", "income0", "street0", "city0");

    // set instances to xls instance writer
    XLSInstanceWriter writer = new XLSInstanceWriter();
    IContentType contentType = Platform.getContentTypeManager()
            .getContentType("eu.esdihumboldt.hale.io.xls.xls");
    writer.setParameter(InstanceTableIOConstants.SOLVE_NESTED_PROPERTIES, Value.of(true));

    File tmpFile = tmpFolder.newFile("excelTestWriteComplexSchema.xls");

    writer.setInstances(example.getSourceInstances());
    // write instances to a temporary XLS file
    writer.setTarget(new FileIOSupplier(tmpFile));
    writer.setContentType(contentType);
    IOReport report = writer.execute(null);
    assertTrue(report.isSuccess());

    Workbook wb = WorkbookFactory.create(tmpFile);
    Sheet sheet = wb.getSheetAt(0);

    checkHeader(sheet, header);

    checkSheetName(sheet, "person");

    checkFirstDataRow(sheet, firstDataRow);
}