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:com.aurel.track.lucene.util.poi.XLSTextStripper.java

License:Open Source License

public XLSTextStripper(FileInputStream fis, String fileExtension) {
    try {//from   w ww. j a v a  2 s .  c  o m
        StringBuffer sb = new StringBuffer();
        Workbook workbook = null;
        if (LuceneFileExtractor.INDEXABLE_EXTENSIONS.XLS.equalsIgnoreCase(fileExtension)) {
            workbook = new HSSFWorkbook(fis);
        } else {
            if (LuceneFileExtractor.INDEXABLE_EXTENSIONS.XLSX.equalsIgnoreCase(fileExtension)) {
                workbook = new XSSFWorkbook(fis);
            }
        }
        if (workbook != null) {
            int numOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numOfSheets; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                Iterator<Row> rowIterator = sheet.rowIterator();
                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        String cellStringValue = null;
                        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                            boolean booleanValue = cell.getBooleanCellValue();
                            cellStringValue = Boolean.toString(booleanValue);
                        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                            double doubleValue = cell.getNumericCellValue();
                            cellStringValue = Double.toString(doubleValue);
                        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                            cellStringValue = cell.getStringCellValue();
                        }
                        if (cellStringValue != null) {
                            sb.append(cellStringValue);
                            sb.append("\t");
                        }
                    }
                    sb.append("\n");
                }
            }
        }
        _text = sb.toString();
    } catch (Exception e) {
        LOGGER.error(ExceptionUtils.getStackTrace(e));
    }
}

From source file:com.b2international.snowowl.datastore.server.importer.AbstractTerminologyExcelImportJob.java

License:Apache License

public static Set<Sheet> collectSheets(final Workbook workbook) {
    final Set<Sheet> sheets = Sets.newHashSet();
    final int numberOfSheets = workbook.getNumberOfSheets();

    for (int i = 0; i < numberOfSheets; i++) {
        final Sheet sheet = workbook.getSheetAt(i);

        if (!INDEX_SHEET.equalsIgnoreCase(sheet.getSheetName())) {
            sheets.add(sheet);//from www  .  j a va  2 s .c om
        }

    }

    return sheets;
}

From source file:com.b2international.snowowl.snomed.core.refset.automap.XlsParser.java

License:Apache License

public void parse(int sheetNumber) throws SnowowlServiceException {
    fis = null;/*  w  w w  .j  a va 2 s .  c o  m*/
    try {
        fis = new FileInputStream(xlsFile);
        Workbook wb = WorkbookFactory.create(fis);
        Sheet sheet = wb.getSheetAt(sheetNumber);
        parse(sheet);
    } catch (final Exception e) {
        maxWidth = -1;
        throw new SnowowlServiceException(e);
    }
}

From source file:com.b2international.snowowl.snomed.importer.net4j.SnomedSubsetImportUtil.java

License:Apache License

private boolean processExcelFile(final SubsetEntry entry) throws InvalidFormatException, IOException {

    final FileInputStream inputStream = createFileInputStream(entry);
    final Workbook workbook = WorkbookFactory.create(inputStream);
    final List<Integer> list = getSheetAndFirstRowNumber(workbook, workbook.getNumberOfSheets());

    if (null != list) {
        final int sheetNumber = list.get(0);
        final int firstRowNumber = list.get(1);
        final Sheet sheet = workbook.getSheetAt(sheetNumber);
        final List<String> row = collectRowValues(sheet.getRow(firstRowNumber));

        entry.setHeadings(row);/*www .  j av  a2 s.c o  m*/
        entry.setSheetNumber(sheetNumber);

        if (entry.isHasHeader()) {
            Optional<String> match = FluentIterable.from(row).firstMatch(new Predicate<String>() {
                @Override
                public boolean apply(String input) {
                    return input.contains("concept") && (input.contains("id") || input.contains("sctid"));
                }
            });
            entry.setIdColumnNumber(match.isPresent() ? row.indexOf(match.get()) : 0); // default to first?
        } else {
            for (int i = 0; i < row.size(); i++) {
                if (isConceptId(row.get(i).trim())) {
                    entry.setIdColumnNumber(i);
                }
            }
        }

        return true;
    } else {
        return false;
    }
}

From source file:com.b2international.snowowl.snomed.importer.net4j.SnomedSubsetImportUtil.java

License:Apache License

private List<Integer> getSheetAndFirstRowNumber(final Workbook workbook, final int numberOfSheets) {
    for (int i = 0; i < numberOfSheets; i++) {
        final Sheet sheet = workbook.getSheetAt(i);

        int firstRow = -1;

        for (int j = 0; j < sheet.getLastRowNum(); j++) {
            final List<String> row = collectRowValues(sheet.getRow(j));

            for (final String value : row) {
                if (!value.isEmpty() && -1 == firstRow) {
                    firstRow = j;/*from   w w w  .  j  a v  a 2s . com*/
                }
            }

            if (containsConceptId(row)) {
                return Lists.newArrayList(i, firstRow);
            }
        }
    }

    return null;
}

From source file:com.bawan.vims.common.util.ExcelHelper.java

/**
 * ?excel/* www. j  a v  a 2s  .  co m*/
 */
public static Map<String, List<Map<String, Object>>> parserExcel(String excelFilePath) {

    Map<String, List<Map<String, Object>>> result = new HashMap<String, List<Map<String, Object>>>();

    InputStream in = null;
    Workbook wb = null;
    try {
        File excelFile = new File(excelFilePath);
        if (excelFile == null || !excelFile.exists()) {
            logger.error("ExcelHelper[parserExcel]  excel file don't exist!");
            return null;
        }
        in = new FileInputStream(excelFile);

        String suffix = excelFilePath.substring(excelFilePath.lastIndexOf("."));
        if (!".xls".equals(suffix) && !".xlsx".equals(suffix)) {
            logger.error("ExcelHelper[parserExcel]  file suffix do'not match[*.xls, *.xlsx]! ");
            return null;
        } /*else if ("xls".equals(suffix)){
           wb = new HSSFWorkbook(in);
          } else if("xlsx".equals(suffix)) {
           wb = new XSSFWorkbook(in);
          }*/

        wb = WorkbookFactory.create(in); // POI 3.8?, ??xls?xlsx?
        int sheetSize = 0;

        while (true) {
            Sheet sheet = wb.getSheetAt(sheetSize);
            if (sheet == null) {
                break;
            }
            String sheetName = sheet.getSheetName();

            List<Map<String, Object>> sheetContent = new ArrayList<Map<String, Object>>();
            for (int rowNum = 521; rowNum < sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                Map<String, Object> rowMap = new HashMap<String, Object>();
                StringBuffer rowContent = new StringBuffer(
                        "insert into vims_car_market_spread_data(ID, MANUFACTURER, BRAND, VEHICEL_LEVEL, VEHICEL, YEAR, MONTH, AREA, SALE_SIZE, PRODUCTION_SIZE, LICENSED_SIZE, TOTAL_FEE, INTERNET_FEE, TV_FEE, MAGAZINE_FEE, NEWSPAPER_FEE, RADIO_FEE, METRO_FEE, OUTDOORS_FEE) values(");
                rowContent.append("'").append(IDGenerator.getID(32)).append("',");
                for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                    Cell cell = row.getCell(cellIndex);
                    //                  if (cell == null) {
                    //                     rowMap.put(rowNum + "_" + cellIndex, null);
                    //                  } else {
                    //                     rowMap.put(rowNum + "_" + cellIndex, cell.toString());
                    //                  }
                    if (cellIndex == 2) {
                        if (cell == null) {
                            rowContent.append(0).append(",");
                        } else if ("mpv".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(1).append(",");
                        } else if ("suv".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(2).append(",");
                        } else if ("".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(3).append(",");
                        } else if ("".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(4).append(",");
                        } else if ("?".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(5).append(",");
                        } else if ("".equalsIgnoreCase(cell.toString())) {
                            rowContent.append(6).append(",");
                        }
                        continue;
                    }

                    if (cell == null || cell.toString().trim().length() == 0) {
                        if (cellIndex == 0 || cellIndex == 1 || cellIndex == 2 || cellIndex == 3
                                || cellIndex == 6) {
                            rowContent.append("default").append(",");
                        } else {
                            rowContent.append("0").append(",");
                        }
                    } else {
                        if (cellIndex == 0 || cellIndex == 1 || cellIndex == 2 || cellIndex == 3
                                || cellIndex == 6) {
                            rowContent.append("'").append(cell.toString()).append("',");
                        } else if (cellIndex == 4 || cellIndex == 5 || cellIndex == 7 || cellIndex == 8
                                || cellIndex == 9) {
                            String value = cell.toString().substring(0, cell.toString().indexOf("."));
                            rowContent.append(Integer.valueOf(value)).append(",");
                        } else {
                            rowContent.append(cell.toString()).append(",");
                        }
                    }
                }
                String sql = rowContent.toString();
                sql = sql.substring(0, sql.length() - 1);
                sql += ");";
                System.out.println(sql);
                sheetContent.add(rowMap);
            }

            result.put(sheetName, sheetContent);
            sheetSize++;
        }

    } catch (Exception e) {
        e.printStackTrace();
        logger.error("ExcelHelper[parserExcel] excel file not found! error:" + e.getMessage(), e);
    } finally {
        try {
            if (wb != null) {
                wb.close();
                wb = null;
            }
        } catch (IOException e1) {
        }

        try {
            if (in != null) {
                in.close();
                in = null;
            }
        } catch (IOException e) {
        }
    }

    return result;
}

From source file:com.ben12.reta.util.RETAAnalysis.java

License:Open Source License

public void writeExcel(Window parent) throws IOException, InvalidFormatException {
    logger.info("Start write excel output");

    Path outputFile = Paths.get(output);
    if (!outputFile.isAbsolute()) {
        Path root = config.getAbsoluteFile().getParentFile().toPath();
        outputFile = root.resolve(outputFile);
    }//from w  w w  .j a va  2s.c om

    // test using template
    InputStream is = getClass().getResourceAsStream("/com/ben12/reta/resources/template/template.xlsx");
    ExcelTransformer transformer = new ExcelTransformer();
    List<String> sheetNames = new ArrayList<>();
    List<String> sheetTemplateNames = new ArrayList<>();
    for (InputRequirementSource requirementSource : requirementSources.values()) {
        sheetTemplateNames.add("DOCUMENT");
        sheetTemplateNames.add("COVERAGE");
        sheetNames.add(requirementSource.getName());
        sheetNames.add(requirementSource.getName() + " coverage");
    }

    List<Map<String, Object>> sheetValues = new ArrayList<>();
    for (InputRequirementSource source : requirementSources.values()) {
        Map<String, Object> values = new HashMap<>();
        values.put("source", source);
        values.put("null", null);
        values.put("line", "\n");

        Set<String> attributes = new LinkedHashSet<>();
        attributes.add(Requirement.ATTRIBUTE_ID);
        if (source.getAttributesGroup().containsKey(Requirement.ATTRIBUTE_VERSION)) {
            attributes.add(Requirement.ATTRIBUTE_VERSION);
        }
        attributes.addAll(source.getAttributesGroup().keySet());
        attributes.remove(Requirement.ATTRIBUTE_TEXT);
        values.put("attributes", attributes);

        Set<String> refAttributes = new LinkedHashSet<>();
        refAttributes.add(Requirement.ATTRIBUTE_ID);
        if (source.getRefAttributesGroup().containsKey(Requirement.ATTRIBUTE_VERSION)) {
            refAttributes.add(Requirement.ATTRIBUTE_VERSION);
        }
        refAttributes.addAll(source.getRefAttributesGroup().keySet());
        refAttributes.remove(Requirement.ATTRIBUTE_TEXT);
        values.put("refAttributes", refAttributes);

        sheetValues.add(values);
        sheetValues.add(values);
    }

    Workbook wb = transformer.transform(is, sheetTemplateNames, sheetNames, sheetValues);
    int sheetCount = wb.getNumberOfSheets();
    for (int i = 0; i < sheetCount; i++) {
        Sheet sheet = wb.getSheetAt(i);
        int columns = 0;
        for (int j = 0; j <= sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);
            if (row != null) {
                row.setHeight((short) -1);
                columns = Math.max(columns, row.getLastCellNum() + 1);
            }
        }
        for (int j = 0; j < columns; j++) {
            sheet.autoSizeColumn(j);
        }
    }

    try (FileOutputStream fos = new FileOutputStream(outputFile.toFile())) {
        wb.write(fos);
    } catch (FileNotFoundException e) {
        int confirm = MessageDialog.showQuestionMessage(null, "Excel output file must be closed.");

        if (confirm == MessageDialog.OK_OPTION) {
            try (FileOutputStream fos = new FileOutputStream(outputFile.toFile())) {
                wb.write(fos);
            } catch (IOException e2) {
                throw e2;
            }
        } else {
            throw e;
        }
    }

    logger.info("End write excel output");
}

From source file:com.bizosys.dataservice.dao.ReadXLS.java

License:Apache License

@Override
protected List<String> populate() throws SQLException {

    checkCondition();/*from  www . j  a v a2s.  c o m*/

    Workbook workbook = getWorkbook();
    Sheet sheet = workbook.createSheet();

    ResultSetMetaData md = rs.getMetaData();
    int totalCol = md.getColumnCount();
    String[] cols = createLabels(md, totalCol);

    try {

        if (null != templateFile) {
            File templateFileObject = new File(templateFile);
            if (templateFileObject.exists()) {
                Workbook templateWorkbook = new HSSFWorkbook(new FileInputStream(templateFileObject));
                Sheet templatesheet = templateWorkbook.getSheetAt(0);
                Iterator<Row> rowIterator = templatesheet.iterator();

                while (rowIterator.hasNext()) {
                    Row templateRow = rowIterator.next();
                    Row row = sheet.createRow(startRowIndex++);

                    Iterator<Cell> cellIterator = templateRow.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell templateCell = cellIterator.next();
                        Cell cell = row.createCell(templateCell.getColumnIndex());
                        cell.setCellType(templateCell.getCellType());
                        switch (templateCell.getCellType()) {
                        case Cell.CELL_TYPE_BLANK:
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            cell.setCellValue(templateCell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_ERROR:
                            cell.setCellValue(templateCell.getErrorCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            cell.setCellValue(templateCell.getCellFormula());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            cell.setCellValue(templateCell.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            cell.setCellValue(templateCell.getStringCellValue());
                            break;
                        }
                    }
                }
            } else {
                System.err.println("Can not read " + templateFileObject.getAbsolutePath());
            }

        }

        while (this.rs.next()) {
            createRecord(totalCol, cols, sheet);
        }
        workbook.write(out);
    } catch (IOException ex) {
        throw new SQLException(ex);
    }
    return null;
}

From source file:com.bizosys.dataservice.dao.WriteToXls.java

License:Apache License

public void write(List<Object[]> records) throws Exception {
    Workbook workbook = getWorkbook();/*from   w ww  .ja v a2 s  .  c  om*/
    Sheet sheet = workbook.createSheet();

    if (null != templateFile) {
        File templateFileObject = new File(templateFile);
        if (templateFileObject.exists()) {
            Workbook templateWorkbook = new HSSFWorkbook(new FileInputStream(templateFileObject));
            Sheet templatesheet = templateWorkbook.getSheetAt(0);
            Iterator<Row> rowIterator = templatesheet.iterator();

            while (rowIterator.hasNext()) {
                Row templateRow = rowIterator.next();
                Row row = sheet.createRow(startRowIndex++);

                Iterator<Cell> cellIterator = templateRow.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell templateCell = cellIterator.next();
                    Cell cell = row.createCell(templateCell.getColumnIndex());
                    cell.setCellType(templateCell.getCellType());
                    switch (templateCell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        cell.setCellValue(templateCell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        cell.setCellValue(templateCell.getErrorCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        cell.setCellValue(templateCell.getCellFormula());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        cell.setCellValue(templateCell.getNumericCellValue());
                        break;
                    case Cell.CELL_TYPE_STRING:
                        cell.setCellValue(templateCell.getStringCellValue());
                        break;
                    }
                }
            }
        } else {
            System.err.println("Can not read " + templateFileObject.getAbsolutePath());
        }
    }

    for (Object[] cols : records) {
        createRecord(cols, sheet);
    }
    workbook.write(out);

}

From source file:com.blackducksoftware.tools.commonframework.standard.datatable.writer.DataTableWriterExcelManual.java

License:Apache License

@Test
public void testMultiSheet() throws Exception {
    RecordDef recordDef = createSimpleRecordDef();
    DataTable dataSet = new DataTable(recordDef);

    for (int i = 0; i < DataSetWriterExcel.EXCEL_MAX_ROWS; i++) {
        Record record = new Record(recordDef);
        for (FieldDef fieldDef : recordDef) {
            record.setFieldValue(fieldDef.getName(), fieldDef.getName() + " test value " + i);
        }/*from  w w w . j ava 2 s  . co m*/
        dataSet.add(record);
    }

    DataSetWriterExcel writer = new DataSetWriterExcel(); // Pass a filename
    // if you want an
    // output file
    writer.write(dataSet);
    Workbook wb = writer.getWorkbook();
    assertEquals(2, wb.getNumberOfSheets());

    // Second sheet
    Sheet sheet = wb.getSheetAt(1);
    assertEquals(2, sheet.getLastRowNum());

    // Last row
    Row row = sheet.getRow(2);
    assertEquals("applicationVersion test value 1048575", row.getCell(1).getStringCellValue());
}