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

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

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

Introduction

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

Prototype

Sheet getSheet(String name);

Source Link

Document

Get sheet with the given name

Usage

From source file:com.dataart.spreadsheetanalytics.engine.DataModelConverters.java

License:Apache License

/** Convertes plain {@link IDataModel} to new {@link XSSFWorkbook} with formatting provided. */
static Workbook toWorkbook(final IDataModel dataModel, final Workbook formatting) {
    Workbook result = formatting == null ? ConverterUtils.newWorkbook()
            : ConverterUtils.clearContent(formatting);

    Sheet wbSheet = result.getSheet(dataModel.getName());
    if (wbSheet == null) {
        wbSheet = result.createSheet(dataModel.getName());
    }//  w w  w.  ja  v a 2 s. c o m

    dataModel.getNamedAddresses().forEach((k, v) -> {
        Name name = result.createName();
        name.setNameName(k);

        name.setRefersToFormula(createPoiNameRef(v.address(), dataModel.getName()));
    });

    dataModel.getNamedValues().forEach((k, v) -> {
        Name name = result.createName();
        name.setNameName(k);

        String refString = v.get() == null ? "" : v.get().toString();
        if (refString.startsWith(FORMULA_PREFIX)) {
            refString = refString.substring(1);
        }

        name.setRefersToFormula(refString);
    });

    for (int rowIdx = dataModel.getFirstRowIndex(); rowIdx <= dataModel.getLastRowIndex(); rowIdx++) {
        IDmRow dmRow = dataModel.getRow(rowIdx);
        if (dmRow == null) {
            continue;
        }
        Row wbRow = wbSheet.getRow(rowIdx);
        if (wbRow == null) {
            wbRow = wbSheet.createRow(rowIdx);
        }

        for (int cellIdx = dmRow.getFirstColumnIndex(); cellIdx <= dmRow.getLastColumnIndex(); cellIdx++) {
            IDmCell dmCell = dmRow.getCell(cellIdx);
            if (dmCell == null) {
                continue;
            }

            Cell wbCell = wbRow.getCell(cellIdx);
            if (wbCell == null) {
                wbCell = wbRow.createCell(cellIdx);
            }

            ConverterUtils.populateCellValue(wbCell, dmCell.getContent());
        }
    }

    return result;
}

From source file:com.dickimawbooks.datatooltk.io.DatatoolExcel.java

License:Open Source License

public DatatoolDb importData(File file) throws DatatoolImportException {
    DatatoolDb db = new DatatoolDb(settings);

    try {/*from  www. j av a2s  .co m*/
        if (!file.exists()) {
            throw new IOException(DatatoolTk.getLabelWithValue("error.io.file_not_found", "" + file));
        }

        if (file.getName().toLowerCase().endsWith(".xlsx")) {
            throw new IOException(DatatoolTk.getLabel("error.xlsx_not_supported"));
        }

        Workbook workBook = WorkbookFactory.create(file);
        Sheet sheet;

        String sheetRef = settings.getSheetRef();

        int sheetIdx = 0;
        String sheetName = null;

        try {
            sheetIdx = Integer.parseInt(sheetRef);
        } catch (NumberFormatException e) {
            sheetName = sheetRef;
        }

        if (sheetName == null) {
            sheet = workBook.getSheetAt(sheetIdx);
            db.setName(sheet.getSheetName());
        } else {
            sheet = workBook.getSheet(sheetName);
            db.setName(sheetName);
        }

        Iterator<Row> rowIter = sheet.rowIterator();
        int rowIdx = 0;

        if (!rowIter.hasNext()) {
            return db;
        }

        Row row = rowIter.next();

        if (settings.hasCSVHeader()) {
            // First row is header

            boolean empty = true;

            while (empty) {
                for (Cell cell : row) {
                    DatatoolHeader header = new DatatoolHeader(db, cell.toString());
                    db.addColumn(header);

                    empty = false;
                }

                if (empty) {
                    if (!rowIter.hasNext()) {
                        return db;
                    }

                    row = rowIter.next();
                }
            }
        } else {
            // First row of data

            int cellIdx = 0;

            for (Cell cell : row) {
                DatatoolHeader header = new DatatoolHeader(db,
                        DatatoolTk.getLabelWithValue("default.field", (cellIdx + 1)));
                db.addColumn(header);

                db.addCell(rowIdx, cellIdx, getCellValue(cell));

                cellIdx++;
            }

            if (cellIdx > 0) {
                rowIdx++;
            }
        }

        while (rowIter.hasNext()) {
            row = rowIter.next();

            int cellIdx = 0;

            for (Cell cell : row) {
                db.addCell(rowIdx, cellIdx, getCellValue(cell));

                cellIdx++;
            }

            if (cellIdx > 0) {
                rowIdx++;
            }
        }
    } catch (Exception e) {
        throw new DatatoolImportException(DatatoolTk.getLabelWithValue("error.import.failed", file.toString()),
                e);
    }

    return db;
}

From source file:com.dituiba.excel.DefaultValidateAdapter.java

License:Apache License

/**
 * ??// w  w w . j a  v  a2 s .  co m
 * @param config
 * @param sheet
 * @param columnIndex
 * @param valueSet
 */
protected void createDicCodeSheet(DicValidateConfig config, Sheet sheet, int columnIndex,
        Set<String> valueSet) {
    Workbook workbook = sheet.getWorkbook();
    Sheet codeSheet = workbook.getSheet(DICCODE_SHEET_NAME);
    if (codeSheet == null) {
        log.debug("?Sheet?Sheet");
        codeSheet = workbook.createSheet(DICCODE_SHEET_NAME);
    }
    int codeIndex = config.columnName() - 'A';
    log.debug("codeIndex{}", codeIndex);
    if (codeSheet.getRow(0) == null || codeSheet.getRow(0).getCell(codeIndex) == null) {
        log.debug("????");
        int i = 0;
        for (String dic : valueSet) {
            Row row = codeSheet.getRow(i);
            if (row == null)
                row = codeSheet.createRow(i);
            Cell cell = row.createCell(codeIndex);
            cell.setCellValue(dic);
            i++;
        }
    } else {
        log.debug("????");
    }
    Name name = workbook.getName(config.columnName() + "");
    if (name == null || name.isDeleted()) {
        log.debug("?Name?Name");
        name = workbook.createName();
        name.setNameName(config.columnName() + "");
    }
    name.setRefersToFormula(DICCODE_SHEET_NAME + "!$" + config.columnName() + "$1:$" + config.columnName() + "$"
            + valueSet.size());
    DVConstraint constraint = DVConstraint.createFormulaListConstraint(name.getNameName());
    CellRangeAddressList addressList = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE,
            columnIndex, columnIndex);
    HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
    workbook.setSheetHidden(workbook.getSheetIndex(DICCODE_SHEET_NAME), Workbook.SHEET_STATE_VERY_HIDDEN);
    setValidationTip(validation, config);
    sheet.addValidationData(validation);
    log.debug("??");
}

From source file:com.github.camaral.sheeco.Sheeco.java

License:Apache License

private <T> Sheet getSheet(final String sheetName, final Workbook wb) throws AssertionError {
    final Sheet sheet = wb.getSheet(sheetName);

    if (sheet == null) {
        throw new SpreasheetUnmarshallingUnrecoverableException(
                "Spreadsheet does not contain a sheet named with \"" + sheetName + "\"");
    }//from  w w w .  j  a  v  a 2s  .  c om

    return sheet;
}

From source file:com.github.camaral.sheeco.SheecoTest.java

License:Apache License

private void assertCatHeaders(Workbook wb) {
    Sheet sheet = wb.getSheet("Cat");
    Assert.assertNotNull(sheet, "Sheet must be created");
    Row row = sheet.getRow(0);/*  w  ww  .  j a  va2 s.  co  m*/
    Assert.assertNotNull(sheet, "Header row must be created");
    assertHeader(row.getCell(0), "name");
    assertHeader(row.getCell(1), "Male?");
    assertHeader(row.getCell(2), "Birth date");
    assertHeader(row.getCell(3), "hairLength");
    assertHeader(row.getCell(4), "hairColor");
    assertHeader(row.getCell(5), "hairLength");
    assertHeader(row.getCell(6), "hairColor");
}

From source file:com.globalsight.everest.edit.offline.upload.UploadApi.java

License:Apache License

private String loadReportData(File p_tempFile, String p_fileName, long p_taskId, String p_reportName) {
    if (cancel)//  w ww.ja v a 2  s  .  c  o  m
        return null;

    File tmpFile = null;
    FileInputStream fis = null;

    try {
        m_errWriter.setFileName(p_fileName);
        if (StringUtil.isEmpty(p_fileName)) {
            m_errWriter.addFileErrorMsg(
                    "The file name is empty. Please make sure it is correct and upload again.");
            return m_errWriter.buildReportErroPage().toString();
        }
        if (!ExcelUtil.isExcel(p_fileName)) {
            m_errWriter
                    .addFileErrorMsg("The file you are trying to upload is not an excel (xls or xlsx format)."
                            + "\r\nPlease make sure it is correct and upload again.");
            return m_errWriter.buildReportErroPage().toString();
        }

        String fileSuff = p_fileName.substring(p_fileName.lastIndexOf("."));
        /**
         * Create a temporary file to get data from excel
         */
        tmpFile = File.createTempFile("RI_", fileSuff);
        FileUtils.copyFile(p_tempFile, tmpFile);
        fis = new FileInputStream(tmpFile);

        Task task = ServerProxy.getTaskManager().getTask(p_taskId);

        Workbook workbook = ExcelUtil.getWorkbook(tmpFile.getAbsolutePath(), fis);
        Sheet sheet = null;

        if (WebAppConstants.LANGUAGE_SIGN_OFF.equals(p_reportName)) {
            String sheetName = task.getTargetLocale().toString();
            sheet = workbook.getSheet(sheetName);
        }
        if (sheet == null)
            sheet = ExcelUtil.getDefaultSheet(workbook);

        if (sheet == null) {
            m_errWriter.addFileErrorMsg("No Sheet detected.");
            return m_errWriter.buildReportErroPage().toString();
        }
        ResourceBundle bundle = SystemResourceBundle.getInstance()
                .getResourceBundle(ResourceBundleConstants.LOCALE_RESOURCE_NAME, Locale.US);

        int languageInfoRow = ImplementedCommentsCheckReportGenerator.LANGUAGE_INFO_ROW;
        int segmentHeaderRow = ImplementedCommentsCheckReportGenerator.SEGMENT_HEADER_ROW;
        int segmentStartRow = ImplementedCommentsCheckReportGenerator.SEGMENT_START_ROW;

        if (p_reportName.equals(WebAppConstants.LANGUAGE_SIGN_OFF)) {
            languageInfoRow = ReviewersCommentsReportGenerator.LANGUAGE_INFO_ROW;
            segmentHeaderRow = ReviewersCommentsReportGenerator.SEGMENT_HEADER_ROW;
            segmentStartRow = ReviewersCommentsReportGenerator.SEGMENT_START_ROW;
        }

        String targetLanguage = sheet.getRow(languageInfoRow).getCell(1).toString();
        if (StringUtil.isEmpty(targetLanguage)) {
            m_errWriter.addFileErrorMsg("No language information detected.");
            return m_errWriter.buildReportErroPage().toString();
        } else if (targetLanguage.indexOf('[') < 0 || targetLanguage.indexOf(']') < 0) {
            m_errWriter.addFileErrorMsg("Target language format is not correct.\r\nIt should "
                    + "contain a portion which is a locale code encolsed by [ ] such as [zh_CN]");
            return m_errWriter.buildReportErroPage().toString();
        }
        reportTargetLocaleId = getLocaleId(targetLanguage);
        GlobalSightLocale tLocale = HibernateUtil.get(GlobalSightLocale.class, reportTargetLocaleId);

        updateProcess(1);
        // Load the TUs and TUVs prior to improve performance.
        if (task != null) {
            for (Iterator spIt = task.getSourcePages(PrimaryFile.EXTRACTED_FILE).iterator(); spIt.hasNext();) {
                if (cancel)
                    return null;

                SourcePage sp = (SourcePage) spIt.next();
                SegmentTuUtil.getTusBySourcePageId(sp.getId());
                SegmentTuvUtil.getSourceTuvs(sp);
            }
            updateProcess(3);
            for (Iterator tpIt = task.getTargetPages(PrimaryFile.EXTRACTED_FILE).iterator(); tpIt.hasNext();) {
                if (cancel)
                    return null;

                TargetPage tp = (TargetPage) tpIt.next();
                SegmentTuvUtil.getTargetTuvs(tp);
            }
        }

        updateProcess(5);

        if (WebAppConstants.TRANSLATION_EDIT.equals(p_reportName)) {
            if (isTERReport(sheet, segmentHeaderRow)) {
                return loadTERReportData(sheet, task, tLocale);
            } else {
                m_errWriter
                        .addFileErrorMsg("The file you are uploading does not keep the report's correct format."
                                + "\r\nMaybe you have changed some column header signatures or orders."
                                + "\r\nThe following column header signatures and orders should keep the source report's format."
                                + "\r\nJob id, Segment id, TargetPage id, Required translation."
                                + "\r\nPlease make sure they are correct and upload again.");
                return m_errWriter.buildReportErroPage().toString();
            }
        } else if (WebAppConstants.LANGUAGE_SIGN_OFF.equals(p_reportName)) {
            if (isRCRSimpleReportAfter855(sheet, segmentHeaderRow)) {
                return loadRCRSimpleReportDataAfter855(sheet, task, tLocale, bundle);
            } else if (isRCRSimpleReportFor855(sheet, segmentHeaderRow)) {
                return loadRCRSimpleReportDataFor855(sheet, task, tLocale, bundle);
            } else if (isRCRReport(sheet, segmentHeaderRow)) {
                return loadRCRReportData(sheet, task, tLocale, bundle);
            } else {
                m_errWriter.addFileErrorMsg("The report type is not correct."
                        + "\r\nPlease make sure the report type is correct and upload again.");
                return m_errWriter.buildReportErroPage().toString();
            }
        } else if (WebAppConstants.POST_REVIEW_QA.equals(p_reportName)) {
            if (isPRRReport(sheet, segmentHeaderRow + 4)) {
                return loadPRRReportData(sheet, task, tLocale);
            } else {
                m_errWriter.addFileErrorMsg("The report type is not correct."
                        + "\r\nPlease make sure the report type is correct and upload again.");
                return m_errWriter.buildReportErroPage().toString();
            }
        } else if (WebAppConstants.TRANSLATION_VERIFICATION.equals(p_reportName)) {
            if (isTVRReport(sheet, segmentHeaderRow)) {
                return loadTVRReportData(sheet, task, tLocale);
            } else {
                m_errWriter.addFileErrorMsg("The report type is not correct."
                        + "\r\nPlease make sure the report type is correct and upload again.");
                return m_errWriter.buildReportErroPage().toString();
            }
        }
    } catch (Throwable ex) {
        String args[] = { EditUtil.encodeHtmlEntities(ex.getMessage()) };
        String errMsg = MessageFormat.format(m_messages.getString("FormatTwoLoadError"), (Object[]) args);

        CATEGORY.error(errMsg);

        m_errWriter.addFileErrorMsg(errMsg);
        return m_errWriter.buildReportErroPage().toString();
    } finally {
        try {
            if (fis != null)
                fis.close();

            if (tmpFile != null)
                tmpFile.delete();
        } catch (Exception e) {
            CATEGORY.error("Cannot close Excel file.", e);
        }
    }

    return null;
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.CommentsAnalysisReportGenerator.java

License:Apache License

/**
 * Create the report sheets/*w  w  w.  j av  a 2  s .  c om*/
 * 
 * @throws Exception
 */
private void createReport(Workbook p_workbook, Job p_job, List<GlobalSightLocale> p_targetLocales,
        String p_dateFormat) throws Exception {
    List<GlobalSightLocale> jobTL = ReportHelper.getTargetLocals(p_job);
    for (GlobalSightLocale trgLocale : p_targetLocales) {
        if (!jobTL.contains(trgLocale))
            continue;

        String srcLang = p_job.getSourceLocale().getDisplayName(uiLocale);
        String trgLang = trgLocale.getDisplayName(uiLocale);

        // Create One Report Sheet/Tab
        Sheet sheet = null;
        if (isCombineAllJobs && p_workbook.getSheet(trgLocale.toString()) != null) {
            sheet = p_workbook.getSheet(trgLocale.toString());
        } else {
            sheet = p_workbook.createSheet(trgLocale.toString());
            sheet.protectSheet("");

            // Add Title
            addTitle(p_workbook, sheet);

            // Add Locale Pair Header
            addLanguageHeader(p_workbook, sheet);

            // Add Segment Header
            addSegmentHeader(p_workbook, sheet);

            // Insert Language Data
            writeLanguageInfo(p_workbook, sheet, srcLang, trgLang);
        }

        // Create Name Areas for drop down list.
        if (p_workbook.getName(CATEGORY_FAILURE_DROP_DOWN_LIST) == null) {
            createCategoryFailureNameArea(p_workbook);
        }

        // Insert Segment Data
        if (isCombineAllJobs) {
            Integer row = null;
            if (rowsMap.get(trgLang) == null) {
                row = writeSegmentInfo(p_workbook, sheet, p_job, trgLocale, p_dateFormat, SEGMENT_START_ROW);
            } else {
                row = writeSegmentInfo(p_workbook, sheet, p_job, trgLocale, p_dateFormat, rowsMap.get(trgLang));
            }
            rowsMap.put(trgLang, row);
        } else {
            writeSegmentInfo(p_workbook, sheet, p_job, trgLocale, p_dateFormat, SEGMENT_START_ROW);
        }
    }
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.ImplementedCommentsCheckReportGenerator.java

License:Apache License

/**
 * Create the report sheets/*from w  w w  .  ja va2  s  .com*/
 * 
 * @throws Exception
 */
private void createReport(Workbook p_workbook, Job p_job, List<GlobalSightLocale> p_targetLocales,
        String p_dateFormat) throws Exception {
    List<GlobalSightLocale> jobTL = ReportHelper.getTargetLocals(p_job);
    for (GlobalSightLocale trgLocale : p_targetLocales) {
        if (!jobTL.contains(trgLocale))
            continue;

        String srcLang = p_job.getSourceLocale().getDisplayName(uiLocale);
        String trgLang = trgLocale.getDisplayName(uiLocale);

        // Create One Report Sheet/Tab
        Sheet sheet = null;
        if (p_workbook.getSheet(trgLocale.toString()) != null) {
            sheet = p_workbook.getSheet(trgLocale.toString());
        } else {
            sheet = p_workbook.createSheet(trgLocale.toString());
            sheet.protectSheet("");

            // Add Title
            addTitle(p_workbook, sheet);

            // Add Locale Pair Header
            addLanguageHeader(p_workbook, sheet);

            // Add Segment Header
            addSegmentHeader(p_workbook, sheet);

            // Insert Language Data
            writeLanguageInfo(p_workbook, sheet, srcLang, trgLang);
        }

        // Create Name Areas for drop down list.
        if (p_workbook.getName(CATEGORY_FAILURE_DROP_DOWN_LIST) == null) {
            createCategoryFailureNameArea(p_workbook);
        }

        writeSegmentInfo(p_workbook, sheet, p_job, trgLocale, p_dateFormat, SEGMENT_START_ROW);
    }
}

From source file:com.globalsight.util.ExcelUtil.java

License:Apache License

public static Sheet getSheet(Workbook workbook, String sheetName) {
    if (workbook == null || StringUtil.isEmpty(sheetName))
        return null;

    return workbook.getSheet(sheetName);
}

From source file:com.gtja.qh.TransferCtrl.TransferCtrl.java

private boolean transferToTxt(String inputFilePath, String outFileDir) {
    File inputFile = new File(inputFilePath);
    String inputFileName = inputFile.getName();
    String extension = inputFileName.lastIndexOf(".") == -1 ? ""
            : inputFileName.substring(inputFileName.lastIndexOf(".") + 1);
    StringBuffer input = null;/*from   w w  w .  j a v  a  2s.  c om*/
    if ("xls".equals(extension)) {
        //JXL?excel 2003??xlsx?
        try {
            //?excel
            InputStream is = new FileInputStream(inputFilePath);
            jxl.Workbook rwb = jxl.Workbook.getWorkbook(is);
            jxl.Sheet rs = rwb.getSheet(0);
            int rsRows = rs.getRows();
            input = new StringBuffer();
            for (int i = 1; i < rsRows; i++) {
                if (rs.getCell(5, i).getContents().equals("")) {
                    String line = "A999@" + rs.getCell(4, i).getContents() + "@"
                            + rs.getCell(6, i).getContents() + "\r\n";
                    line = line.replaceAll(",", "");
                    input.append(line);
                } else {
                    continue;
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    } else if ("xlsx".equals(extension)) {
        //POI ?excel 2007,??excel 2003
        try {
            InputStream fs = new FileInputStream(inputFilePath);
            XSSFWorkbook wb;
            wb = new XSSFWorkbook(fs);
            XSSFSheet sheet = wb.getSheetAt(0);
            int rows = sheet.getPhysicalNumberOfRows();
            input = new StringBuffer();
            for (int i = 1; i < rows; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                if (row.getCell(5).getStringCellValue().equals("")) {
                    if (row.getCell(4) == null) {
                        row.createCell(4);
                        row.getCell(4).setCellValue("");
                    }
                    if (row.getCell(6) == null) {
                        row.createCell(6);
                        row.getCell(6).setCellValue("");
                    }

                    String tradeCode = row.getCell(4).getStringCellValue();
                    Double amount = null;
                    String amt = null;
                    if (row.getCell(6).getCellType() == CELL_TYPE_NUMERIC) {
                        amount = row.getCell(6).getNumericCellValue();
                        amt = new DecimalFormat("0.00").format(amount);
                    } else {
                        if (row.getCell(6).getStringCellValue().length() == 0) {
                            amount = null;
                            amt = "";
                        } else {
                            amount = new DecimalFormat("0.00").parse(row.getCell(6).getStringCellValue())
                                    .doubleValue(); //String?Double
                            amt = amount.toString();
                        }
                    }
                    String line = "A999@" + tradeCode + "@" + amt + "\r\n";
                    line = line.replaceAll(",", "");
                    input.append(line);
                } else {
                    continue;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    try {
        //
        String fileName = "0004_00000001_" + frame.getDate().getText() + "_DailyFundChg";
        String outFile = outFileDir + "\\" + fileName + ".txt";
        File file = new File(outFile);
        if (!file.exists()) {
            file.createNewFile();
        }
        //
        OutputStream os = new FileOutputStream(file);
        os.write(input.toString().getBytes());
        os.flush();
        os.close();
        return true;

    } catch (Exception e) {
        e.printStackTrace();
        JOptionPane.showMessageDialog(null, "???",
                "?", JOptionPane.ERROR_MESSAGE);
        return false;
    }

}