Example usage for org.apache.poi.ss.usermodel Sheet getRow

List of usage examples for org.apache.poi.ss.usermodel Sheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getRow

Introduction

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

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

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)/*from   w  ww.  j a v  a2  s.  co 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.qachecks.DITAQAChecker.java

License:Apache License

private void writeBlank(Sheet p_sheet, int p_row, int p_colLen) throws Exception {
    for (int col = 0; col < p_colLen; col++) {
        Row row = p_sheet.getRow(p_row);
        Cell cell = getCell(row, col);//from   w  w  w  . j  a v a2 s  .c o  m
        cell.setCellValue("");
        col++;
    }
}

From source file:com.globalsight.everest.qachecks.DITAQAChecker.java

License:Apache License

private Row getRow(Sheet p_sheet, int p_col) {
    Row row = p_sheet.getRow(p_col);
    if (row == null)
        row = p_sheet.createRow(p_col);//from  w  w  w  .  j  av a  2s  .  c o m
    return row;
}

From source file:com.globalsight.everest.qachecks.QAChecker.java

License:Apache License

private Row getRow(Sheet p_sheet, int p_row) {
    Row row = p_sheet.getRow(p_row);
    if (row == null) {
        row = p_sheet.createRow(p_row);/*from   w w  w  .ja v a2s . co  m*/
    }
    return row;
}

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

License:Apache License

/**
 * Create Report File./*from  w w w .j a  va 2 s  .  co m*/
 */
protected File getFile(String p_reportType, Job p_job, Workbook p_workBook) {
    String langInfo = null;
    // If the Workbook has only one sheet, the report name should contain language pair info, such as en_US_de_DE.
    if (p_workBook != null && p_workBook.getNumberOfSheets() == 1) {
        Sheet sheet = p_workBook.getSheetAt(0);
        String srcLang = null, trgLang = null;
        if (p_job != null) {
            srcLang = p_job.getSourceLocale().toString();
        }
        if (srcLang == null) {
            Row languageInfoRow = sheet.getRow(LANGUAGE_INFO_ROW);
            if (languageInfoRow != null) {
                srcLang = languageInfoRow.getCell(0).getStringCellValue();
                srcLang = srcLang.substring(srcLang.indexOf("[") + 1, srcLang.indexOf("]"));
                trgLang = languageInfoRow.getCell(1).getStringCellValue();
                trgLang = trgLang.substring(trgLang.indexOf("[") + 1, trgLang.indexOf("]"));
            } else {
                Row dataRow = sheet.getRow(sheet.getLastRowNum());
                if (dataRow != null) {
                    try {
                        long jobId = (long) dataRow.getCell(0).getNumericCellValue();
                        Job job = ServerProxy.getJobHandler().getJobById(jobId);
                        srcLang = job.getSourceLocale().toString();
                    } catch (Exception e) {
                    }

                }
            }
        }
        if (trgLang == null) {
            trgLang = sheet.getSheetName();
        }
        if (srcLang != null && trgLang != null) {
            langInfo = srcLang + "_" + trgLang;
        }
    }

    return ReportHelper.getReportFile(p_reportType, p_job, ReportConstants.EXTENSION_XLSX, langInfo);
}

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

License:Apache License

/**
 * Set the cell blank//www.  j a v a 2 s.com
 * 
 * @param p_sheet
 *            the sheet
 * @param p_row
 *            the row position
 * @param p_colLen
 *            the blank column length
 * @throws Exception
 */
private void writeBlank(Sheet p_sheet, int p_row, int p_colLen) throws Exception {
    for (int col = 0; col < p_colLen; col++) {
        Row row = p_sheet.getRow(p_row);
        Cell cell = getCell(row, col);
        cell.setCellValue("");
        col++;
    }
}

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

License:Apache License

/**
 * Create Report File./*from ww  w. ja  v a2s  . c om*/
 */
protected File getFile(String p_reportType, Job p_job, Workbook p_workBook) {
    String langInfo = null;
    // If the Workbook has only one sheet, the report name should contain language pair info, such as en_US_de_DE.
    if (p_workBook != null && p_workBook.getNumberOfSheets() == 1) {
        Sheet sheet = p_workBook.getSheetAt(0);
        String srcLang = null, trgLang = null;
        if (p_job != null) {
            srcLang = p_job.getSourceLocale().toString();
        }
        if (srcLang == null) {
            Row languageInfoRow = sheet.getRow(1);
            if (languageInfoRow != null) {
                srcLang = languageInfoRow.getCell(0).getStringCellValue();
                srcLang = srcLang.substring(srcLang.indexOf("[") + 1, srcLang.indexOf("]"));
                trgLang = languageInfoRow.getCell(1).getStringCellValue();
                trgLang = trgLang.substring(trgLang.indexOf("[") + 1, trgLang.indexOf("]"));
            } else {
                Row dataRow = sheet.getRow(sheet.getLastRowNum());
                if (dataRow != null) {
                    try {
                        long jobId = (long) dataRow.getCell(0).getNumericCellValue();
                        Job job = ServerProxy.getJobHandler().getJobById(jobId);
                        srcLang = job.getSourceLocale().toString();
                    } catch (Exception e) {
                    }

                }
            }
        }
        if (trgLang == null) {
            trgLang = sheet.getSheetName();
        }
        if (srcLang != null && trgLang != null) {
            langInfo = srcLang + "_" + trgLang;
        }
    }

    return ReportHelper.getReportFile(p_reportType, p_job, ReportConstants.EXTENSION_XLSX, langInfo);
}

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

License:Apache License

private void addLeveragingSheetTotal(Workbook p_workbook, Sheet p_sheet, int row) throws Exception {
    // Total Row/*from   ww  w  . j a  v a2s  .  c  om*/
    if (row > (ROWNUMBER + 1)) {
        int column = 0;
        Cell cell_GrandTotal = getCell(getRow(p_sheet, row), column++);
        cell_GrandTotal.setCellValue(bundle.getString("lb_grandTotal"));
        cell_GrandTotal.setCellStyle(getHeaderOrangeStyle(p_workbook));
        while (column < (p_sheet.getRow(row - 1).getPhysicalNumberOfCells() - 1)) {
            Cell totalCell = getCell(getRow(p_sheet, row), column);
            totalCell.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column));
            totalCell.setCellStyle(getHeaderOrangeStyle(p_workbook));
            column++;
        }
        String leveraging = "(1-F" + (row + 1) + "/" + getColumnName(column - 1) + (row + 1) + ")*100";

        Cell cell_TotalLeveraging = getCell(getRow(p_sheet, row), column++);
        cell_TotalLeveraging.setCellFormula(leveraging);
        cell_TotalLeveraging.setCellStyle(getFloatSumStyle(p_workbook));
    }
}

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

License:Apache License

/**
 * Creates Leveraging Sheet/*from  w  ww. j a v a 2 s  . c  o m*/
 */
private void createCostsSheet(Workbook p_workbook, Sheet p_sheet, ReportSearchOptions p_options,
        Map<String, ReportWordCount> p_wordCounts) throws Exception {
    int rowLen = p_sheet.getPhysicalNumberOfRows();
    int colLen = p_sheet.getRow(2).getPhysicalNumberOfCells();
    int wordTotalCol = colLen - 2;
    int row = ROWNUMBER, column = colLen - 1;
    int costCol;
    Map<String, Double> p_ratesMap = null;
    for (int r = 2; r < rowLen + ROWNUMBER; r++) {
        Row theRow = getRow(p_sheet, r);
        theRow.removeCell(getCell(theRow, column));
    }
    p_sheet.removeColumnBreak(column);
    // Rates Columns
    for (int dis = column - 1; column < colLen + dis - 2; column++) {
        Cell cell_From = p_sheet.getRow(row).getCell(column - dis);
        Cell cell_To = getCell(p_sheet.getRow(row), column);
        cell_To.setCellValue(cell_From.getStringCellValue());
        cell_To.setCellStyle(cell_From.getCellStyle());
        p_sheet.setColumnWidth(column, p_sheet.getColumnWidth(column - dis));
        // Adds Rates for Match Type
        for (int rateRow = row + 1; rateRow <= rowLen; rateRow++) {
            String matchType = p_sheet.getRow(ROWNUMBER).getCell(column).getStringCellValue();
            String targetLocale = p_sheet.getRow(rateRow).getCell(0).getStringCellValue();
            double rate = getRate(matchType, targetLocale, p_ratesMap);
            addNumberCell(p_sheet, column, rateRow, rate, getMoneyStyle(p_workbook));
        }
    }

    // Cost Columns Head
    costCol = column;
    p_sheet.setColumnWidth(column, 20 * 256);
    Cell cell_CostWithLeveraging = getCell(getRow(p_sheet, row), column++);
    cell_CostWithLeveraging.setCellValue(bundle.getString("lb_report_costWithLeveraging"));
    cell_CostWithLeveraging.setCellStyle(getHeaderOrangeStyle(p_workbook));

    p_sheet.setColumnWidth(column, 20 * 256);
    Cell cell_CostNoLeveraging = getCell(getRow(p_sheet, row), column++);
    cell_CostNoLeveraging.setCellValue(bundle.getString("lb_report_costNoLeveraging"));
    cell_CostNoLeveraging.setCellStyle(getHeaderOrangeStyle(p_workbook));

    p_sheet.setColumnWidth(column, 15 * 256);
    Cell cell_Savings = getCell(getRow(p_sheet, row), column++);
    cell_Savings.setCellValue(bundle.getString("lb_savings"));
    cell_Savings.setCellStyle(getHeaderOrangeStyle(p_workbook));

    Cell cell_Percent = getCell(getRow(p_sheet, row), column++);
    cell_Percent.setCellValue("%");
    cell_Percent.setCellStyle(getHeaderOrangeStyle(p_workbook));
    // Cost Columns Data
    for (row = ROWNUMBER + 1; row < (rowLen + ROWNUMBER); row++) {
        String leveragingForm = getCostWithLeveraging(1, wordTotalCol - 1, wordTotalCol, (row + 1));
        String noLeveragingForm = getColumnName(wordTotalCol) + (row + 1) + "*"
                + getColumnName(wordTotalCol + 5) + (row + 1);
        String savingForm = getColumnName(costCol + 1) + (row + 1) + "-" + getColumnName(costCol) + (row + 1);
        String percent = getColumnName(costCol + 2) + (row + 1) + "/" + getColumnName(costCol + 1) + (row + 1);

        Row theRow = getRow(p_sheet, row);
        Cell cell_Leveraging = getCell(theRow, costCol);
        cell_Leveraging.setCellFormula(leveragingForm);
        cell_Leveraging.setCellStyle(getMoneyStyle(p_workbook));

        Cell cell_NoLeveraging = getCell(theRow, costCol + 1);
        cell_NoLeveraging.setCellFormula(noLeveragingForm);
        cell_NoLeveraging.setCellStyle(getMoneyStyle(p_workbook));

        Cell cell_Saving = getCell(theRow, costCol + 2);
        cell_Saving.setCellFormula(savingForm);
        cell_Saving.setCellStyle(getMoneyStyle(p_workbook));

        Cell cell_PercentData = getCell(theRow, costCol + 3);
        cell_PercentData.setCellFormula(percent);
        cell_PercentData.setCellStyle(getPercentStyle(p_workbook));
    }

    if (rowLen > 1) {
        row = rowLen + 1;
        column = 1;
        for (; column < colLen - 1; column++) {
            Cell cell_Total = getCell(getRow(p_sheet, row), column);
            cell_Total.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column));
            cell_Total.setCellStyle(getHeaderOrangeStyle(p_workbook));
        }
        for (; column < costCol; column++) {
            Cell cell = getCell(getRow(p_sheet, row), column);
            cell.setCellValue("");
            cell.setCellStyle(getHeaderOrangeStyle(p_workbook));
        }

        // Summary Cost Columns
        Cell cell_SumLeveraging = getCell(getRow(p_sheet, row), column);
        cell_SumLeveraging.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column++));
        cell_SumLeveraging.setCellStyle(getMoneySumStyle(p_workbook));

        Cell cell_SumNoLeveraging = getCell(getRow(p_sheet, row), column);
        cell_SumNoLeveraging.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column++));
        cell_SumNoLeveraging.setCellStyle(getMoneySumStyle(p_workbook));

        Cell cell_SumSaving = getCell(getRow(p_sheet, row), column);
        cell_SumSaving.setCellFormula(getSumOfColumn(ROWNUMBER + 1, row - 1, column++));
        cell_SumSaving.setCellStyle(getMoneySumStyle(p_workbook));

        String percent = getColumnName(column - 1) + (row + 1) + "/" + getColumnName(column - 2) + (row + 1);
        Cell cell_AvgPercent = getCell(getRow(p_sheet, row), column);
        cell_AvgPercent.setCellFormula(percent);
        cell_AvgPercent.setCellStyle(getPercentSumStyle(p_workbook));
    }
}

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

License:Apache License

public static String getCellValue(Sheet sheet, int row, int col) {
    String value = "";
    if (sheet == null || row < 0 || col < 0)
        return "";

    Row rowData = sheet.getRow(row);
    if (rowData == null)
        return "";
    Cell cell = rowData.getCell(col);//from  www  .  j  av a  2 s.c  o  m
    if (cell == null)
        return "";
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
        value = String.valueOf((int) cell.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_STRING:
        value = cell.getStringCellValue();
        break;

    default:
        value = cell.toString();
    }

    return value;
}