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:gov.opm.scrd.services.impl.DeductionCalculationRuleServiceImpl.java

License:Apache License

/**
 * Generate deduction_table.xls based on deduction rates queried from database.
 *//*from ww  w . j  a v  a  2 s  . co m*/
private void generateDeductionTable() throws OPMConfigurationException {
    OutputStream templateOutput = null;
    try {
        int currentRow = this.deductionTableTemplate.getStartCellRow();
        int startColumn = this.deductionTableTemplate.getStartCellColumn();
        DateFormat df = new SimpleDateFormat("dd-MMM-yyyy", Locale.US);

        Workbook workbook = WorkbookFactory
                .create(new FileInputStream(this.deductionTableTemplate.getTemplateFile()));
        Sheet sheet = workbook.getSheetAt(0);

        // Query deduction rates
        TypedQuery<DeductionRate> query = entityManager.createQuery(
                "SELECT r FROM DeductionRate r JOIN FETCH r.retirementType WHERE r.deleted = false ORDER BY r.id",
                DeductionRate.class);

        for (DeductionRate rate : query.getResultList()) {
            Row row = sheet.getRow(currentRow++);
            if (row == null) {
                row = sheet.createRow(currentRow - 1);
            }
            // Service Type column
            Cell cell = row.getCell(startColumn);
            if (cell == null) {
                cell = row.createCell(startColumn);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(rate.getServiceType());
            // Retirement Type column
            cell = row.getCell(startColumn + 1);
            if (cell == null) {
                cell = row.createCell(startColumn + 1);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(rate.getRetirementType().getName());
            // Date range column
            cell = row.getCell(startColumn + 2);
            if (cell == null) {
                cell = row.createCell(startColumn + 2);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(df.format(rate.getStartDate()) + "," + df.format(rate.getEndDate()));
            // Date range column
            cell = row.getCell(startColumn + 3);
            if (cell == null) {
                cell = row.createCell(startColumn + 3);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(
                    df.format(rate.getStartDate()) + "," + df.format(rate.getEndDate()) + "," + rate.getRate());
        }
        templateOutput = new FileOutputStream(this.deductionTableTemplate.getDecisionTableFile());
        workbook.write(templateOutput);
    } catch (Exception ex) {
        throw new OPMConfigurationException("Failed to generate deduction rates decision table.", ex);
    } finally {
        if (templateOutput != null) {
            try {
                templateOutput.close();
            } catch (IOException e) {
            }
        }
    }
}

From source file:gov.opm.scrd.services.impl.InterestCalculationRuleServiceImpl.java

License:Apache License

/**
 * Generate interest tables.//from   w  w  w  .j av  a  2  s.c  om
 * @throws OPMConfigurationException
 */
private void generateInterestTables() throws OPMConfigurationException {
    OutputStream csrsInterestTemplateOutput = null;
    OutputStream csrsPeaceCorpsInterestTemplateOutput = null;
    OutputStream csrsRedepositInterestTemplateOutput = null;
    OutputStream fersInterestTemplateOutput = null;
    OutputStream fersPeaceCorpsInterestTemplateOutput = null;
    OutputStream fersRedepositInterestTemplateOutput = null;
    try {
        // Query interest rates
        TypedQuery<InterestRate> query = entityManager.createQuery(
                "SELECT r FROM InterestRate r WHERE r.deleted = false ORDER BY r.interestYear",
                InterestRate.class);
        List<InterestRate> interestRates = query.getResultList();

        // csrs_interest.xls
        int currentRow = this.csrsInterestTemplate.getStartCellRow();
        int startColumn = this.csrsInterestTemplate.getStartCellColumn();

        Workbook workbook = WorkbookFactory
                .create(new FileInputStream(this.csrsInterestTemplate.getTemplateFile()));
        Sheet sheet = workbook.getSheetAt(0);

        for (InterestRate rate : interestRates) {
            Row row = sheet.getRow(currentRow++);
            if (row == null) {
                row = sheet.createRow(currentRow - 1);
            }
            // Year column
            Cell cell = row.getCell(startColumn);
            if (cell == null) {
                cell = row.createCell(startColumn);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(String.valueOf(rate.getInterestYear()));
            // Rate column
            cell = row.getCell(startColumn + 1);
            if (cell == null) {
                cell = row.createCell(startColumn + 1);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(rate.getInterestRate().toString());
        }
        csrsInterestTemplateOutput = new FileOutputStream(this.csrsInterestTemplate.getDecisionTableFile());
        workbook.write(csrsInterestTemplateOutput);

        // csrs_peacecorps_interest.xls
        currentRow = this.csrsPeaceCorpsInterestTemplate.getStartCellRow();
        startColumn = this.csrsPeaceCorpsInterestTemplate.getStartCellColumn();

        workbook = WorkbookFactory
                .create(new FileInputStream(this.csrsPeaceCorpsInterestTemplate.getTemplateFile()));
        sheet = workbook.getSheetAt(0);

        for (int i = 0; i < interestRates.size() - 1; i++) {
            InterestRate rate1 = interestRates.get(i);
            if (rate1.getInterestYear() >= 1995) {
                InterestRate rate2 = interestRates.get(i + 1);
                Row row = sheet.getRow(currentRow++);
                if (row == null) {
                    row = sheet.createRow(currentRow - 1);
                }
                // Year column
                Cell cell = row.getCell(startColumn);
                if (cell == null) {
                    cell = row.createCell(startColumn);
                }
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(String.valueOf(rate1.getInterestYear()) + ","
                        + String.valueOf(rate2.getInterestYear()));
                // Rate column
                cell = row.getCell(startColumn + 1);
                if (cell == null) {
                    cell = row.createCell(startColumn + 1);
                }
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(
                        rate1.getInterestRate().toString() + "," + rate2.getInterestRate().toString());
            }
        }
        csrsPeaceCorpsInterestTemplateOutput = new FileOutputStream(
                this.csrsPeaceCorpsInterestTemplate.getDecisionTableFile());
        workbook.write(csrsPeaceCorpsInterestTemplateOutput);

        // csrs_redeposit_interest.xls
        currentRow = this.csrsRedepositInterestTemplate.getStartCellRow();
        startColumn = this.csrsRedepositInterestTemplate.getStartCellColumn();

        workbook = WorkbookFactory
                .create(new FileInputStream(this.csrsRedepositInterestTemplate.getTemplateFile()));
        sheet = workbook.getSheetAt(0);

        for (InterestRate rate : interestRates) {
            Row row = sheet.getRow(currentRow++);
            if (row == null) {
                row = sheet.createRow(currentRow - 1);
            }
            // Year column
            Cell cell = row.getCell(startColumn);
            if (cell == null) {
                cell = row.createCell(startColumn);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(String.valueOf(rate.getInterestYear()));
            // Rate column
            cell = row.getCell(startColumn + 1);
            if (cell == null) {
                cell = row.createCell(startColumn + 1);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(rate.getInterestRate().toString());
        }
        csrsRedepositInterestTemplateOutput = new FileOutputStream(
                this.csrsRedepositInterestTemplate.getDecisionTableFile());
        workbook.write(csrsRedepositInterestTemplateOutput);

        // fers_interest.xls
        currentRow = this.fersInterestTemplate.getStartCellRow();
        startColumn = this.fersInterestTemplate.getStartCellColumn();

        workbook = WorkbookFactory.create(new FileInputStream(this.fersInterestTemplate.getTemplateFile()));
        sheet = workbook.getSheetAt(0);

        for (InterestRate rate : interestRates) {
            Row row = sheet.getRow(currentRow++);
            if (row == null) {
                row = sheet.createRow(currentRow - 1);
            }
            // Year column
            Cell cell = row.getCell(startColumn);
            if (cell == null) {
                cell = row.createCell(startColumn);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(String.valueOf(rate.getInterestYear()));
            // Rate column
            cell = row.getCell(startColumn + 1);
            if (cell == null) {
                cell = row.createCell(startColumn + 1);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(rate.getInterestRate().toString());
        }
        fersInterestTemplateOutput = new FileOutputStream(this.fersInterestTemplate.getDecisionTableFile());
        workbook.write(fersInterestTemplateOutput);

        // fers_peacecorps_interest.xls
        currentRow = this.fersPeaceCorpsInterestTemplate.getStartCellRow();
        startColumn = this.fersPeaceCorpsInterestTemplate.getStartCellColumn();

        workbook = WorkbookFactory
                .create(new FileInputStream(this.fersPeaceCorpsInterestTemplate.getTemplateFile()));
        sheet = workbook.getSheetAt(0);

        for (int i = 0; i < interestRates.size() - 1; i++) {
            InterestRate rate1 = interestRates.get(i);
            if (rate1.getInterestYear() >= 1995) {
                InterestRate rate2 = interestRates.get(i + 1);
                Row row = sheet.getRow(currentRow++);
                if (row == null) {
                    row = sheet.createRow(currentRow - 1);
                }
                // Year column
                Cell cell = row.getCell(startColumn);
                if (cell == null) {
                    cell = row.createCell(startColumn);
                }
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(String.valueOf(rate1.getInterestYear()) + ","
                        + String.valueOf(rate2.getInterestYear()));
                // Rate column
                cell = row.getCell(startColumn + 1);
                if (cell == null) {
                    cell = row.createCell(startColumn + 1);
                }
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(
                        rate1.getInterestRate().toString() + "," + rate2.getInterestRate().toString());
            }
        }
        fersPeaceCorpsInterestTemplateOutput = new FileOutputStream(
                this.fersPeaceCorpsInterestTemplate.getDecisionTableFile());
        workbook.write(fersPeaceCorpsInterestTemplateOutput);

        // fers_redeposit_interest.xls
        currentRow = this.fersRedepositInterestTemplate.getStartCellRow();
        startColumn = this.fersRedepositInterestTemplate.getStartCellColumn();

        workbook = WorkbookFactory
                .create(new FileInputStream(this.fersRedepositInterestTemplate.getTemplateFile()));
        sheet = workbook.getSheetAt(0);

        for (InterestRate rate : interestRates) {
            Row row = sheet.getRow(currentRow++);
            if (row == null) {
                row = sheet.createRow(currentRow - 1);
            }
            // Year column
            Cell cell = row.getCell(startColumn);
            if (cell == null) {
                cell = row.createCell(startColumn);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(String.valueOf(rate.getInterestYear()));
            // Rate column
            cell = row.getCell(startColumn + 1);
            if (cell == null) {
                cell = row.createCell(startColumn + 1);
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(rate.getInterestRate().toString());
        }
        fersRedepositInterestTemplateOutput = new FileOutputStream(
                this.fersRedepositInterestTemplate.getDecisionTableFile());
        workbook.write(fersRedepositInterestTemplateOutput);
    } catch (Exception ex) {
        throw new OPMConfigurationException("Failed to generate interest rates decision table.", ex);
    } finally {
        if (csrsInterestTemplateOutput != null) {
            try {
                csrsInterestTemplateOutput.close();
            } catch (IOException e) {
            }
        }
        if (csrsPeaceCorpsInterestTemplateOutput != null) {
            try {
                csrsPeaceCorpsInterestTemplateOutput.close();
            } catch (IOException e) {
            }
        }
        if (csrsRedepositInterestTemplateOutput != null) {
            try {
                csrsRedepositInterestTemplateOutput.close();
            } catch (IOException e) {
            }
        }
        if (fersInterestTemplateOutput != null) {
            try {
                fersInterestTemplateOutput.close();
            } catch (IOException e) {
            }
        }
        if (fersPeaceCorpsInterestTemplateOutput != null) {
            try {
                fersInterestTemplateOutput.close();
            } catch (IOException e) {
            }
        }
        if (fersRedepositInterestTemplateOutput != null) {
            try {
                fersInterestTemplateOutput.close();
            } catch (IOException e) {
            }
        }
    }
}

From source file:gov.va.isaac.request.uscrs.USCRSBatchTemplate.java

License:Apache License

/**
 * Generate the enums and constants from a template file for use at the top of this class
 *///from  ww w. j a  v a  2s . co  m
public static void main(String[] args) throws IOException {
    //USCRSBatchTemplate b = new USCRSBatchTemplate(USCRSBatchTemplate.class.getResourceAsStream("/USCRS_Batch_Template-2015-01-27.xls"));

    Workbook wb = new HSSFWorkbook(
            USCRSBatchTemplate.class.getResourceAsStream("/USCRS_Batch_Template-2015-01-27.xls"));

    ArrayList<String> sheets = new ArrayList<>();
    HashSet<String> columns = new HashSet<>();

    LinkedHashMap<String, ArrayList<String>> pickLists = new LinkedHashMap<>();

    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);
        String sheetName = sheet.getSheetName();
        sheets.add(sheetName);
        if (sheetName.equals("Help")) {
            continue;
        }

        sheet.getRow(0).forEach(headerCell -> {
            if (sheetName.equals("metadata") && headerCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                //SKIP - the metadata tab has a cell that is just a number - likely the release date
            } else {
                String stringValue = toString(headerCell);
                columns.add(stringValue);
                if (sheetName.equals("metadata")) {
                    pickLists.put(stringValue, new ArrayList<>());
                    for (int row = 1; row < sheet.getLastRowNum(); row++) {
                        Cell valueCell = sheet.getRow(row).getCell(headerCell.getColumnIndex());
                        if (valueCell != null) {
                            String s = toString(valueCell);
                            if (s.length() > 0) {
                                pickLists.get(stringValue).add(s);
                            }
                        }
                    }
                }
            }
        });
    }

    String eol = System.getProperty("line.separator");

    StringBuilder sb = new StringBuilder();
    int i = 0;
    sb.append("public enum SHEET {");
    for (String s : sheets) {
        sb.append(enumSafeCharExchange(s));
        sb.append(", ");
        i++;
        if (i % 8 == 0) {
            i = 0;
            sb.append(eol);
        }
    }
    sb.setLength(sb.length() - (i == 0 ? 3 : 2));
    sb.append("};");
    System.out.println(sb);
    System.out.println();

    sb.setLength(0);
    i = 0;
    sb.append("public enum COLUMN {");
    for (String c : columns) {
        sb.append(enumSafeCharExchange(c));
        sb.append(", ");
        i++;
        if (i % 8 == 0) {
            i = 0;
            sb.append(eol);
        }
    }
    sb.setLength(sb.length() - (i == 0 ? 3 : 2));
    sb.append("};");
    System.out.println(sb);
    sb.setLength(0);
    i = 0;

    for (Entry<String, ArrayList<String>> x : pickLists.entrySet()) {
        sb.append("public enum PICKLIST_");
        sb.append(enumSafeCharExchange(x.getKey()));
        sb.append(" {");
        for (String s : x.getValue()) {
            sb.append(enumSafeCharExchange(s));
            sb.append("(\"");
            sb.append(s);
            sb.append("\")");
            sb.append(", ");
            i++;
            if (i % 2 == 0) {
                i = 0;
                sb.append(eol);
            }
        }
        sb.setLength(sb.length() - (i == 0 ? 3 : 2));
        sb.append(";" + eol);
        sb.append("\tprivate String value;" + eol + eol);
        sb.append("\tprivate PICKLIST_" + enumSafeCharExchange(x.getKey()) + " (String pickListValue)" + eol);
        sb.append("\t{" + eol);
        sb.append("\t\tvalue = pickListValue;" + eol);
        sb.append("\t}" + eol);

        sb.append("" + eol);
        sb.append("\t@Override" + eol);
        sb.append("\tpublic String toString()" + eol);
        sb.append("\t{" + eol);
        sb.append("\t\treturn value;" + eol);
        sb.append("\t}" + eol);

        sb.append("" + eol);
        sb.append("\tpublic static PICKLIST_" + enumSafeCharExchange(x.getKey()) + " find(String value)" + eol);
        sb.append("\t{" + eol);
        sb.append("\t\treturn PICKLIST_" + enumSafeCharExchange(x.getKey())
                + ".valueOf(enumSafeCharExchange(value));" + eol);
        sb.append("\t}" + eol);
        sb.append("};");

        System.out.println(sb);
        sb.setLength(0);
        i = 0;
        System.out.println();
    }
}

From source file:gridgrid.Web.java

License:Apache License

private synchronized void load(File file) throws IOException {
    if (file.lastModified() > lastModified) {
        map = new HashMap<>();
        InputStream is = new FileInputStream(file);
        Workbook book = new XSSFWorkbook(is);
        Sheet sheet = book.getSheetAt(0);
        int pathCelNum = -1;
        int scriptCellNum = -1;
        int viewCellNum = -1;

        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row != null) {
                if (row.getLastCellNum() >= 1 && pathCelNum == -1) {
                    for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                        Cell cell = row.getCell(cellIndex);
                        if (cell != null) {
                            switch (cell.getStringCellValue()) {
                            case "":
                                pathCelNum = cellIndex;
                                break;
                            case "JavaScript":
                                scriptCellNum = cellIndex;
                                break;
                            case "":
                                viewCellNum = cellIndex;
                                break;
                            }/*  w  w  w. ja va2  s  . c  om*/
                        }
                    }

                }

                if (pathCelNum != -1 && row.getCell(pathCelNum) != null && row.getCell(scriptCellNum) != null
                        && row.getCell(viewCellNum) != null) {
                    Cell code = row.getCell(scriptCellNum);
                    String codeStr = code != null ? code.toString() : "";
                    Cell view = row.getCell(viewCellNum);
                    String viewStr = view != null ? view.toString() : "";
                    String path = row.getCell(pathCelNum).toString();
                    map.put(path, new CodeView(codeStr, viewStr));
                }
            }
        }
        is.close();
        lastModified = file.lastModified();
    }
}

From source file:guru.qas.martini.report.MainTest.java

License:Apache License

@Test
public void testMultipleInput() throws Exception {
    getInputFile();/*from ww w  . j a  v a 2 s.c  o  m*/
    getInputFile();

    String inputResource = tmpDir.toURI().resolve("**/*.json").toURL().toExternalForm();

    File outputFile = getOutputFile();
    String outputResource = outputFile.toURI().toURL().toExternalForm();

    String[] args = new String[] { "-i", inputResource, "-o", outputResource };
    Main.main(args);
    checkState(outputFile.exists(), "output file does not exist");

    Workbook workbook = getWorkbook(outputFile);
    assertReportContents(workbook);
    Sheet sheet = workbook.getSheetAt(1);
    int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
    checkState(2 == physicalNumberOfRows, "worksheet 'Suite' should contain two rows but contains %s",
            physicalNumberOfRows);
}

From source file:guru.qas.martini.report.MainTest.java

License:Apache License

private void assertReportContents(Workbook workbook) {
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(3);/*from  w  w  w  .ja  va 2s  . com*/
    Cell cell = row.getCell(5);
    String value = cell.getStringCellValue();
    String expected = "1512752459298\n(Fri Dec 08 11:00:59 CST 2017)";
    Assert.assertEquals(value, expected, "report contains incorrect rendering");
}

From source file:hjow.hgtable.util.XLSXUtil.java

License:Apache License

/**
 * <p>XLSX ? ?  ?? . ?  ?  ?? ?? , ? ? ?? ?  ?  ?? ?.</p>
 * /*from  ww  w  . j  a  v a 2 s  .  c om*/
 * @param file : XLSX ?
 * @return ?  ? 
 */
public static List<TableSet> toTableSets(File file) {
    List<TableSet> tableSets = new Vector<TableSet>();

    org.apache.poi.ss.usermodel.Workbook workbook = null;

    if (file == null)
        throw new NullPointerException(Manager.applyStringTable("Please select file !!"));
    if (!file.exists())
        throw new NullPointerException(Manager.applyStringTable("File") + " " + file.getAbsolutePath() + " "
                + Manager.applyStringTable("is not exist"));

    boolean isHead = true;
    int rowNum = 0;
    int cellNum = 0;

    int cellCount = 0;

    FileInputStream fileStream = null;
    try {
        if (file.getAbsolutePath().endsWith(".xlsx") || file.getAbsolutePath().endsWith(".XLSX")) {
            workbook = new org.apache.poi.xssf.usermodel.XSSFWorkbook(file);
        } else if (file.getAbsolutePath().endsWith(".xls") || file.getAbsolutePath().endsWith(".XLS")) {
            fileStream = new FileInputStream(file);
            workbook = new org.apache.poi.hssf.usermodel.HSSFWorkbook(fileStream);
        }

        org.apache.poi.ss.usermodel.FormulaEvaluator evals = workbook.getCreationHelper()
                .createFormulaEvaluator();

        org.apache.poi.ss.usermodel.Sheet sheet = null;

        for (int x = 0; x < workbook.getNumberOfSheets(); x++) {
            TableSet newTableSet = new DefaultTableSet();
            newTableSet.setColumns(new Vector<Column>());

            sheet = workbook.getSheetAt(x);
            newTableSet.setName(sheet.getSheetName());

            rowNum = 0;
            isHead = true;

            String targetData = null;

            for (org.apache.poi.ss.usermodel.Row row : sheet) {
                cellNum = 0;
                for (org.apache.poi.ss.usermodel.Cell cell : row) {
                    try {
                        if (cellNum >= cellCount) {
                            throw new IndexOutOfBoundsException(
                                    Manager.applyStringTable("There are some cells not have their heads") + ", "
                                            + Manager.applyStringTable("Head count") + " : " + cellCount + ", "
                                            + Manager.applyStringTable("Cell Number") + " : " + cellNum);
                        }

                        switch (cell.getCellType()) {
                        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
                            if (isHead) {
                                newTableSet.getColumns().add(new Column(
                                        cell.getRichStringCellValue().getString(), Column.TYPE_STRING));
                            } else {
                                targetData = cell.getRichStringCellValue().getString();
                                newTableSet.getColumns().get(cellNum).setType(cell.getCellType());
                            }
                            break;
                        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
                            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                                if (isHead) {
                                    newTableSet.getColumns().add(new Column(
                                            String.valueOf(cell.getStringCellValue()), Column.TYPE_DATE));
                                } else {
                                    targetData = String.valueOf(cell.getDateCellValue());
                                    newTableSet.getColumns().get(cellNum).setType(cell.getCellType());
                                }
                            } else {
                                if (isHead) {
                                    newTableSet.getColumns().add(new Column(
                                            String.valueOf(cell.getStringCellValue()), Column.TYPE_NUMERIC));
                                } else {
                                    double values = cell.getNumericCellValue();
                                    double intPart = values - ((double) ((int) values));
                                    if (intPart == 0.0) {
                                        targetData = String.valueOf(((int) values));
                                        newTableSet.getColumns().get(cellNum).setType(Column.TYPE_INTEGER);
                                    } else {
                                        targetData = String.valueOf(values);
                                        newTableSet.getColumns().get(cellNum).setType(cell.getCellType());
                                    }
                                }
                            }
                            break;
                        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
                            if (isHead) {
                                newTableSet.getColumns().add(new Column(
                                        String.valueOf(cell.getStringCellValue()), Column.TYPE_BOOLEAN));
                            } else {
                                targetData = String.valueOf(cell.getBooleanCellValue());
                                newTableSet.getColumns().get(cellNum).setType(cell.getCellType());
                            }
                            break;
                        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
                            if (isHead) {
                                newTableSet.getColumns().add(new Column(
                                        String.valueOf(cell.getStringCellValue()), Column.TYPE_NUMERIC));
                            } else {
                                if (evals.evaluateFormulaCell(cell) == 0) {
                                    targetData = String.valueOf(cell.getNumericCellValue());
                                    newTableSet.getColumns().get(cellNum).setType(Column.TYPE_NUMERIC);
                                } else if (evals.evaluateFormulaCell(cell) == 1) {
                                    targetData = String.valueOf(cell.getStringCellValue());
                                    newTableSet.getColumns().get(cellNum).setType(Column.TYPE_STRING);
                                } else if (evals.evaluateFormulaCell(cell) == 4) {
                                    targetData = String.valueOf(cell.getBooleanCellValue());
                                    newTableSet.getColumns().get(cellNum).setType(Column.TYPE_BOOLEAN);
                                } else {
                                    targetData = String.valueOf(cell.getCellFormula());
                                    newTableSet.getColumns().get(cellNum).setType(cell.getCellType());
                                }
                            }
                            break;
                        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
                            if (isHead) {
                                newTableSet.getColumns().add(new Column("", Column.TYPE_STRING));
                            } else {
                                targetData = "";
                                newTableSet.getColumns().get(cellNum).setType(Column.TYPE_BLANK);
                            }
                            break;
                        default:
                            if (isHead) {
                                newTableSet.getColumns().add(new Column("", Column.TYPE_STRING));
                            } else {
                                try {
                                    targetData = cell.getStringCellValue();
                                } catch (Exception e1) {
                                    e1.printStackTrace();
                                }
                                newTableSet.getColumns().get(cellNum).setType(cell.getCellType());
                            }
                            break;
                        }

                        if (isHead) {
                            cellCount++;
                        } else {
                            while (rowNum > 0
                                    && newTableSet.getColumns().get(cellNum).getData().size() < rowNum) {
                                newTableSet.getColumns().get(cellNum).getData().add("");
                            }
                            if (targetData != null)
                                newTableSet.getColumns().get(cellNum).getData().add(targetData);
                            else {
                                newTableSet.getColumns().get(cellNum).getData().add("");
                            }
                        }
                    } catch (ArrayIndexOutOfBoundsException e1) {
                        StringBuffer err = new StringBuffer("");
                        for (StackTraceElement errEl : e1.getStackTrace()) {
                            err = err.append("\t " + errEl + "\n");
                        }

                        String cellObject = null;
                        try {
                            cellObject = cell.getStringCellValue();
                        } catch (Exception e2) {

                        }

                        throw new ArrayIndexOutOfBoundsException(
                                Manager.applyStringTable("Array index out of range") + " <- "
                                        + Manager.applyStringTable("Reading xlsx file") + " : " + file.getName()
                                        + ", " + sheet.getSheetName() + "\n" + Manager.applyStringTable("On")
                                        + " " + Manager.applyStringTable("Row") + " " + rowNum + ", "
                                        + Manager.applyStringTable("Cell") + " " + cellNum + ", "
                                        + Manager.applyStringTable("Value") + " : " + String.valueOf(cellObject)
                                        + "\n " + Manager.applyStringTable("<-\n") + err + "\n "
                                        + Manager.applyStringTable("Original Message") + "...\n"
                                        + e1.getMessage() + "\n" + Manager.applyStringTable("End"));
                    }

                    cellNum++;
                }

                isHead = false;
                rowNum++;
            }

            fillTableSet(newTableSet);
            newTableSet.removeEmptyColumn(true);

            tableSets.add(newTableSet);
        }

        return tableSets;
    } catch (Throwable e) {
        if (Main.MODE >= DebuggingUtil.DEBUG)
            e.printStackTrace();
        Main.logError(e,
                Manager.applyStringTable("On reading xlsx") + " : " + file + "\n"
                        + Manager.applyStringTable("At rownum") + " " + rowNum + ", "
                        + Manager.applyStringTable("cellnum") + " " + cellNum);

        return null;
    } finally {
        try {
            workbook.close();
        } catch (Throwable e) {

        }
        try {
            if (fileStream != null)
                fileStream.close();
        } catch (Throwable e) {

        }
    }
}

From source file:img.Img.java

/**
 * @param args the command line arguments
 *///from ww w  .j  av a2s  .c om

public static void main(String[] args) throws Exception {

    HSSFWorkbook result = new HSSFWorkbook();

    File dir = new File(UPLOAD_PATH);
    File[] directoryListing = dir.listFiles();

    Workbook wb = WorkbookFactory.create(new File(UPLOAD_PATH + "/T5UBF.XLSX"));

    Sheet sheet = wb.getSheetAt(0);
    System.out.println(sheet.getProtect());
    Row row = sheet.getRow(0);
    Cell cell = row.getCell(0);
    System.out.println(cell.getStringCellValue());

    /*if (directoryListing != null) {
        for (File child : directoryListing) {
    System.out.println("Reading from: "+child.getAbsolutePath());
    Workbook wb = WorkbookFactory.create(child);
    Sheet sheet  = wb.getSheetAt(0);
            
                
                
        }
                
    }*/
    FileOutputStream out = new FileOutputStream(new File("C:/Users/nk91008743/Desktop/result.xls"));
    result.write(out);
    out.close();
}

From source file:info.informationsea.java.excel2csv.Converter.java

License:Open Source License

private void doConvertAllSheets(List<File> inputFiles, File outputFile) throws Exception {
    Workbook workbook;//from  w  w  w .  jav  a2s . co m

    if (outputFile.isFile() && outputFile.length() > 512) {
        switch (Utilities.suggestFileTypeFromName(outputFile.getName())) {
        case FILETYPE_XLS:
        case FILETYPE_XLSX:
            workbook = WorkbookFactory.create(outputFile);
            break;
        default:
            throw new IllegalArgumentException("Output file format should be Excel format");
        }
    } else {
        switch (Utilities.suggestFileTypeFromName(outputFile.getName())) {
        case FILETYPE_XLS:
            workbook = new HSSFWorkbook();
            break;
        case FILETYPE_XLSX:
            if (largeExcelMode)
                workbook = new SXSSFWorkbook();
            else
                workbook = new XSSFWorkbook();
            break;
        default:
            throw new IllegalArgumentException("Output file format should be Excel format");
        }
    }

    if (largeExcelMode && !(workbook instanceof SXSSFWorkbook)) {
        log.warn("Streaming output mode is disabled");
    }
    //log.info("workbook: {}", workbook.getClass());

    for (File oneInput : inputFiles) {
        switch (Utilities.suggestFileTypeFromName(oneInput.getName())) {
        case FILETYPE_XLSX:
        case FILETYPE_XLS: {
            Workbook inputWorkbook = WorkbookFactory.create(oneInput);
            int sheetNum = inputWorkbook.getNumberOfSheets();
            for (int i = 0; i < sheetNum; i++) {
                try (TableReader reader = new ExcelSheetReader(inputWorkbook.getSheetAt(i))) {
                    ExcelSheetWriter sheetWriter = new ExcelSheetWriter(
                            Utilities.createUniqueNameSheetForWorkbook(workbook, inputWorkbook.getSheetName(i),
                                    overwriteSheet));
                    sheetWriter.setPrettyTable(prettyTable);
                    try (TableWriter tableWriter = new FilteredWriter(sheetWriter, convertCellTypes,
                            fistCount)) {
                        Utilities.copyTable(reader, tableWriter, useHeader);
                    }
                }
            }
            break;
        }
        default: {
            try (TableReader reader = Utilities.openReader(oneInput, inputSheetIndex, inputSheetName)) {
                ExcelSheetWriter sheetWriter = new ExcelSheetWriter(Utilities
                        .createUniqueNameSheetForWorkbook(workbook, oneInput.getName(), overwriteSheet));
                sheetWriter.setPrettyTable(prettyTable);
                try (TableWriter tableWriter = new FilteredWriter(sheetWriter, convertCellTypes, fistCount)) {
                    Utilities.copyTable(reader, tableWriter, useHeader);
                }
            }
            break;
        }
        }
    }

    workbook.write(new FileOutputStream(outputFile));
}

From source file:info.informationsea.java.excel2csv.Utilities.java

License:Open Source License

public static TableReader openReader(File inputFile, int sheetIndex, String sheetName) throws IOException {
    if (inputFile == null) {
        return new TableCSVReader(new InputStreamReader(System.in), new TabDelimitedFormat());
    } else {//from   w  w w. ja v  a 2 s  .c  om
        FileType type = suggestFileTypeFromName(inputFile.getName());
        switch (type) {
        case FILETYPE_XLS:
        case FILETYPE_XLSX: {
            Workbook workbook;
            if (type == FileType.FILETYPE_XLSX)
                workbook = new XSSFWorkbook(new FileInputStream(inputFile));
            else
                workbook = new HSSFWorkbook(new FileInputStream(inputFile));

            if (sheetName != null)
                return new ExcelSheetReader(workbook.getSheet(sheetName));
            else
                return new ExcelSheetReader(workbook.getSheetAt(sheetIndex));
        }
        case FILETYPE_CSV:
            return new TableCSVReader(new FileReader(inputFile), new DefaultFormat());
        case FILETYPE_TAB:
        default:
            return new TableCSVReader(new FileReader(inputFile), new TabDelimitedFormat());
        }
    }
}