Example usage for org.apache.poi.ss.usermodel Row getRowNum

List of usage examples for org.apache.poi.ss.usermodel Row getRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getRowNum

Introduction

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

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

Usage

From source file:org.openelis.bean.QcChartReport1Bean.java

License:Open Source License

/**
 * Fills all cells in "row" except the ones for result values.
 * //  www . j  a v a  2s  . co m
 * @param value
 *        the object that contains data for the row
 * @param row
 *        the row whose cells are being filled
 * @param maxChars
 *        the list containing the maximum number of characters in each
 *        column; it's updated a when new value is set in a cell
 * @throws Exception
 */
private void setBaseCells(Value value, Row row) throws Exception {
    Cell cell;

    cell = row.createCell(0);
    cell.setCellStyle(baseStyle);
    if (qcColumns != null && qcColumns.size() > 0)
        setCellValue(cell, String.valueOf(row.getRowNum() - 31));
    else
        setCellValue(cell, String.valueOf(row.getRowNum() + 1));
    setMaxChars(cell, maxChars);

    cell = row.createCell(1);
    cell.setCellStyle(baseStyle);
    setCellValue(cell, value.getAccessionNumber() + " / " + value.getWId());
    setMaxChars(cell, maxChars);

    cell = row.createCell(2);
    cell.setCellStyle(baseStyle);
    setCellValue(cell, value.getLotNumber());
    setMaxChars(cell, maxChars);

    cell = row.createCell(3);
    cell.setCellStyle(baseStyle);
    setCellValue(cell, getDateTimeLabel(value.getWorksheetCreatedDate(), Messages.get().gen_dateTimePattern()));
    setMaxChars(cell, maxChars);

    cell = row.createCell(4);
    cell.setCellStyle(baseStyle);
    setCellValue(cell, value.getQcLink());
    setMaxChars(cell, maxChars);
}

From source file:org.openelis.bean.WorksheetExcelHelperBean.java

License:Open Source License

@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
public WorksheetManager1 exportToExcel(WorksheetManager1 manager) throws Exception {
    boolean isEditable;
    int r, i, a, o, aCount, aTotal;
    ArrayList<WorksheetAnalysisViewDO> waList;
    ArrayList<WorksheetResultViewDO> wrList;
    ArrayList<WorksheetQcResultViewDO> wqrList;
    String cellNameIndex, posNum, outFileName;
    File outFile;// w w w  .j  a v  a 2  s.  c  o m
    FileInputStream in;
    FileOutputStream out;
    HashMap<Integer, ArrayList<WorksheetAnalysisViewDO>> waMap;
    HashMap<Integer, ArrayList<WorksheetResultViewDO>> wrMap;
    HashMap<Integer, ArrayList<WorksheetQcResultViewDO>> wqrMap;
    HashMap<Integer, WorksheetAnalysisViewDO> waLinkMap;
    HashMap<String, HashMap<Integer, ArrayList<AnalyteParameterViewDO>>> apMap;
    HashMap<String, String> tCellNames;
    Cell cell;
    CellRangeAddressList /*statusCells, */ reportableColumn;
    DVConstraint /*statusConstraint, */ reportableConstraint;
    HSSFDataValidation /*statusValidation, */ reportableValidation;
    HSSFSheet resultSheet, overrideSheet;
    HSSFWorkbook wb;
    Name cellName;
    Row row, oRow, tRow;
    DictionaryDO formatDO;
    ReportStatus status;
    SimpleDateFormat dateTimeFormat;
    WorksheetAnalysisDO waLinkDO;

    status = new ReportStatus();
    status.setMessage("Exporting to Excel: Initializing");
    status.setPercentComplete(0);
    session.setAttribute("ExportToExcelStatus", status);

    dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");

    outFileName = getWorksheetOutputFileName(manager.getWorksheet().getId(),
            manager.getWorksheet().getSystemUserId());
    outFile = new File(outFileName);
    if (outFile.exists())
        throw new Exception(
                "An Excel file for this worksheet already exists, please delete it before trying to export");

    try {
        formatDO = dictionaryCache.getById(manager.getWorksheet().getFormatId());
    } catch (NotFoundException nfE) {
        formatDO = new DictionaryDO();
        formatDO.setEntry("DefaultTotal");
        formatDO.setSystemName("wf_total");
    } catch (Exception anyE) {
        throw new Exception("Error retrieving worksheet format: " + anyE.getMessage());
    }

    try {
        in = new FileInputStream(getWorksheetTemplateFileName(formatDO));
        wb = new HSSFWorkbook(in, true);
        createStyles(wb);
    } catch (FileNotFoundException fnfE) {
        try {
            wb = buildTemplate(formatDO);
            createStyles(wb);
        } catch (FileNotFoundException fnfE2) {
            throw new Exception("Error loading template file: " + fnfE2.getMessage());
        } catch (IOException ioE) {
            throw new Exception("Error loading workbook from template file: " + ioE.getMessage());
        }
    }

    loadStatuses();
    //        statusCells = new CellRangeAddressList();

    tCellNames = loadNamesByCellReference(wb);

    resultSheet = wb.getSheet("Worksheet");

    tRow = resultSheet.getRow(1);
    resultSheet.removeRow(tRow);

    overrideSheet = wb.getSheet("Overrides");

    status.setPercentComplete(5);
    session.setAttribute("ExportToExcelStatus", status);

    r = 1;
    o = 1;
    i = 0;
    aCount = 0;
    apMap = new HashMap<String, HashMap<Integer, ArrayList<AnalyteParameterViewDO>>>();
    waMap = new HashMap<Integer, ArrayList<WorksheetAnalysisViewDO>>();
    waLinkMap = new HashMap<Integer, WorksheetAnalysisViewDO>();
    wrMap = new HashMap<Integer, ArrayList<WorksheetResultViewDO>>();
    wqrMap = new HashMap<Integer, ArrayList<WorksheetQcResultViewDO>>();
    loadMaps(manager, waMap, waLinkMap, wrMap, wqrMap);
    aTotal = getAnalyses(manager).size();
    if (getItems(manager) != null) {
        for (WorksheetItemDO wiDO : getItems(manager)) {
            a = 0;
            waList = waMap.get(wiDO.getId());
            if (waList != null && waList.size() > 0) {
                for (WorksheetAnalysisViewDO waVDO : waList) {
                    aCount++;
                    status.setMessage("Exporting to Excel: Analysis " + aCount + " of " + aTotal);
                    status.setPercentComplete((int) (((double) (aCount - 1) / aTotal) * 90) + 5);
                    session.setAttribute("ExportToExcelStatus", status);

                    waLinkDO = waLinkMap.get(waVDO.getWorksheetAnalysisId());

                    row = resultSheet.createRow(r);

                    // position number
                    posNum = wiDO.getPosition().toString();
                    cell = row.createCell(0);
                    cell.setCellStyle(styles.get("row_no_edit"));
                    if (a == 0)
                        cell.setCellValue(posNum);

                    // accession number
                    cell = row.createCell(1);
                    cell.setCellStyle(styles.get("row_no_edit"));
                    cell.setCellValue(waVDO.getAccessionNumber());

                    cellNameIndex = i + "." + a;
                    if (waVDO.getAnalysisId() != null) {
                        isEditable = (waVDO.getFromOtherId() == null
                                && !Constants.dictionary().ANALYSIS_INPREP.equals(waVDO.getStatusId())
                                && !Constants.dictionary().ANALYSIS_RELEASED.equals(waVDO.getStatusId())
                                && !Constants.dictionary().ANALYSIS_CANCELLED.equals(waVDO.getStatusId()));

                        // description
                        cell = row.createCell(2);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getDescription());

                        // qc link
                        cell = row.createCell(3);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        if (waLinkDO != null)
                            cell.setCellValue(waLinkDO.getAccessionNumber());
                        else
                            cell.setCellValue("");

                        // test name
                        cell = row.createCell(4);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getTestName());

                        // method name
                        cell = row.createCell(5);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getMethodName());

                        // analysis status
                        cell = row.createCell(6);
                        //                            if (isEditable)
                        //                                cell.setCellStyle(styles.get("row_edit"));
                        //                            else
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(statusIdNameMap.get(waVDO.getStatusId()));
                        cellName = wb.createName();
                        cellName.setNameName("analysis_status." + i + "." + a);
                        cellName.setRefersToFormula("Worksheet!$" + CellReference.convertNumToColString(6) + "$"
                                + (row.getRowNum() + 1));
                        //                            statusCells.addCellRangeAddress(r, 6, r, 6);

                        wrList = wrMap.get(waVDO.getId());
                        if (wrList == null || wrList.size() == 0) {
                            // analyte
                            cell = row.createCell(7);
                            cell.setCellStyle(styles.get("row_no_edit"));
                            cell.setCellValue("NO ANALYTES DEFINED");

                            // reportable
                            cell = row.createCell(8);
                            cell.setCellStyle(styles.get("row_no_edit"));
                            cell.setCellValue("N");

                            createEmptyCellsForFormat(row, tRow);
                            r++;
                        } else {
                            r = createResultCellsForFormat(resultSheet, row, tRow, cellNameIndex, tCellNames,
                                    manager.getWorksheet(), waVDO, wrList, isEditable, apMap);
                        }

                        //
                        // Add override row to override sheet
                        //
                        oRow = overrideSheet.createRow(o);

                        // position number
                        cell = oRow.createCell(0);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        if (a == 0)
                            cell.setCellValue(posNum);

                        // accession number
                        cell = oRow.createCell(1);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getAccessionNumber());

                        // description (override)
                        cell = oRow.createCell(2);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getDescription());

                        // test name (overrride)
                        cell = oRow.createCell(3);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getTestName());

                        // method name (override)
                        cell = oRow.createCell(4);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getMethodName());

                        // users (override)
                        cell = oRow.createCell(5);
                        if (isEditable)
                            cell.setCellStyle(styles.get("row_edit"));
                        else
                            cell.setCellStyle(styles.get("row_no_edit"));
                        if (waVDO.getSystemUsers() != null)
                            cell.setCellValue(waVDO.getSystemUsers());
                        cellName = wb.createName();
                        cellName.setNameName("analysis_users." + cellNameIndex);
                        cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(5) + "$"
                                + (oRow.getRowNum() + 1));

                        // started (override)
                        cell = oRow.createCell(6);
                        if (isEditable)
                            cell.setCellStyle(styles.get("datetime_edit"));
                        else
                            cell.setCellStyle(styles.get("datetime_no_edit"));
                        if (waVDO.getStartedDate() != null)
                            cell.setCellValue(dateTimeFormat.format(waVDO.getStartedDate().getDate()));
                        cellName = wb.createName();
                        cellName.setNameName("analysis_started." + cellNameIndex);
                        cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(6) + "$"
                                + (oRow.getRowNum() + 1));

                        // completed (override)
                        cell = oRow.createCell(7);
                        if (isEditable)
                            cell.setCellStyle(styles.get("datetime_edit"));
                        else
                            cell.setCellStyle(styles.get("datetime_no_edit"));
                        if (waVDO.getCompletedDate() != null)
                            cell.setCellValue(dateTimeFormat.format(waVDO.getCompletedDate().getDate()));
                        cellName = wb.createName();
                        cellName.setNameName("analysis_completed." + cellNameIndex);
                        cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(7) + "$"
                                + (oRow.getRowNum() + 1));
                        o++;
                    } else if (waVDO.getQcLotId() != null) {
                        // description
                        cell = row.createCell(2);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getDescription());

                        // qc link
                        cell = row.createCell(3);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        if (waLinkDO != null)
                            cell.setCellValue(waLinkDO.getAccessionNumber());
                        else
                            cell.setCellValue("");

                        // test name
                        cell = row.createCell(4);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue("");

                        // method name
                        cell = row.createCell(5);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue("");

                        // analysis status
                        cell = row.createCell(6);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue("");

                        wqrList = wqrMap.get(waVDO.getId());
                        if (wqrList == null || wqrList.size() == 0) {
                            // analyte
                            cell = row.createCell(7);
                            cell.setCellStyle(styles.get("row_no_edit"));
                            cell.setCellValue("NO ANALYTES DEFINED");

                            // reportable
                            cell = row.createCell(8);
                            cell.setCellStyle(styles.get("row_no_edit"));
                            cell.setCellValue("N");

                            createEmptyCellsForFormat(row, tRow);

                            r++;
                        } else {
                            cellNameIndex = i + "." + a;
                            r = createQcResultCellsForFormat(resultSheet, row, tRow, cellNameIndex, tCellNames,
                                    manager.getWorksheet(), waVDO.getQcId(), wqrList, apMap);
                        }

                        //
                        // Add override row to override sheet
                        //
                        oRow = overrideSheet.createRow(o);

                        // position number
                        cell = oRow.createCell(0);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        if (a == 0)
                            cell.setCellValue(posNum);

                        // accession number
                        cell = oRow.createCell(1);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getAccessionNumber());

                        // description (override)
                        cell = oRow.createCell(2);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getDescription());

                        // test name (overrride)
                        cell = oRow.createCell(3);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue("");

                        // method name (override)
                        cell = oRow.createCell(4);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue("");

                        // users (override)
                        cell = oRow.createCell(5);
                        cell.setCellStyle(styles.get("row_edit"));
                        if (waVDO.getSystemUsers() != null)
                            cell.setCellValue(waVDO.getSystemUsers());
                        cellName = wb.createName();
                        cellName.setNameName("analysis_users." + cellNameIndex);
                        cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(5) + "$"
                                + (oRow.getRowNum() + 1));

                        // started (override)
                        cell = oRow.createCell(6);
                        cell.setCellStyle(styles.get("datetime_edit"));
                        if (waVDO.getStartedDate() != null)
                            cell.setCellValue(dateTimeFormat.format(waVDO.getStartedDate().getDate()));
                        cellName = wb.createName();
                        cellName.setNameName("analysis_started." + cellNameIndex);
                        cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(6) + "$"
                                + (oRow.getRowNum() + 1));

                        // completed (override)
                        cell = oRow.createCell(7);
                        cell.setCellStyle(styles.get("datetime_no_edit"));
                        cellName = wb.createName();
                        cellName.setNameName("analysis_completed." + cellNameIndex);
                        cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(7) + "$"
                                + (oRow.getRowNum() + 1));
                        o++;
                    }

                    a++;
                }
            }

            i++;
        }
    }

    status.setMessage("Exporting to Excel: Finalizing");
    status.setPercentComplete(95);
    session.setAttribute("ExportToExcelStatus", status);

    //
    // Create validators
    //
    //        statusConstraint = DVConstraint.createExplicitListConstraint(statuses);
    //        statusValidation = new HSSFDataValidation(statusCells, statusConstraint);
    //        statusValidation.setEmptyCellAllowed(true);
    //        statusValidation.setSuppressDropDownArrow(false);
    //        statusValidation.createPromptBox("Statuses", formatTooltip(statuses));
    //        statusValidation.setShowPromptBox(false);
    //        resultSheet.addValidationData(statusValidation);

    reportableColumn = new CellRangeAddressList(1, resultSheet.getPhysicalNumberOfRows() - 1, 8, 8);
    reportableConstraint = DVConstraint.createExplicitListConstraint(new String[] { "Y", "N" });
    reportableValidation = new HSSFDataValidation(reportableColumn, reportableConstraint);
    reportableValidation.setSuppressDropDownArrow(false);
    resultSheet.addValidationData(reportableValidation);

    //
    // Auto resize columns on result sheet and override sheet
    //
    resultSheet.autoSizeColumn(2, true); // Description
    resultSheet.autoSizeColumn(4, true); // Test
    resultSheet.autoSizeColumn(5, true); // Method
    resultSheet.autoSizeColumn(7, true); // Analyte

    overrideSheet.autoSizeColumn(2, true); // Description
    overrideSheet.autoSizeColumn(3, true); // Test
    overrideSheet.autoSizeColumn(4, true); // Method

    try {
        out = new FileOutputStream(outFileName);
        wb.write(out);
        out.close();
        Runtime.getRuntime().exec("chmod go+rw " + outFileName);
    } catch (Exception anyE) {
        throw new Exception("Error writing Excel file: " + anyE.getMessage());
    }

    status.setMessage("Exporting to Excel: Done");
    status.setPercentComplete(100);
    session.setAttribute("ExportToExcelStatus", status);

    return manager;
}

From source file:org.openelis.bean.WorksheetExcelHelperBean.java

License:Open Source License

@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
private int createResultCellsForFormat(HSSFSheet sheet, Row row, Row tRow, String nameIndexPrefix,
        HashMap<String, String> cellNames, WorksheetViewDO wVDO, WorksheetAnalysisViewDO waVDO,
        ArrayList<WorksheetResultViewDO> wrList, boolean isEditable,
        HashMap<String, HashMap<Integer, ArrayList<AnalyteParameterViewDO>>> apMap) {
    int c, i, r;//from  w w  w  .j av  a  2 s .c  om
    String cellNameIndex, name;
    ArrayList<AnalyteParameterViewDO> anaParams, apList;
    DecimalFormat df;
    HashMap<Integer, ArrayList<AnalyteParameterViewDO>> pMap;
    Cell cell, tCell;
    Name cellName;
    AnalyteParameterViewDO apVDO;

    df = new DecimalFormat();
    df.setGroupingUsed(false);
    df.setMaximumFractionDigits(10);

    i = 0;
    r = row.getRowNum();
    for (WorksheetResultViewDO wrVDO : wrList) {
        if (i != 0) {
            row = sheet.createRow(r);
            for (c = 0; c < 7; c++) {
                cell = row.createCell(c);
                cell.setCellStyle(styles.get("row_no_edit"));
            }
        }

        cellNameIndex = nameIndexPrefix + "." + i;

        // analyte
        cell = row.createCell(7);
        cell.setCellStyle(styles.get("row_no_edit"));
        cell.setCellValue(wrVDO.getAnalyteName());

        // reportable
        cell = row.createCell(8);
        cell.setCellStyle(styles.get("row_no_edit"));
        cell.setCellValue(wrVDO.getIsReportable());
        cellName = sheet.getWorkbook().createName();
        cellName.setNameName("analyte_reportable." + cellNameIndex);
        cellName.setRefersToFormula(
                "Worksheet!$" + CellReference.convertNumToColString(8) + "$" + (row.getRowNum() + 1));

        apVDO = null;
        for (c = 9; c < tRow.getLastCellNum() && c < 39; c++) {
            tCell = tRow.getCell(c);

            cell = row.createCell(c);
            if (isEditable)
                cell.setCellStyle(tCell.getCellStyle());
            else
                cell.setCellStyle(styles.get("row_no_edit"));
            name = cellNames.get(
                    sheet.getSheetName() + "!$" + CellReference.convertNumToColString(tCell.getColumnIndex())
                            + "$" + (tCell.getRowIndex() + 1));
            if (name != null) {
                cellName = row.getSheet().getWorkbook().createName();
                cellName.setNameName(name + "." + cellNameIndex);
                cellName.setRefersToFormula(
                        sheet.getSheetName() + "!$" + CellReference.convertNumToColString(cell.getColumnIndex())
                                + "$" + (row.getRowNum() + 1));
            }
            if (tCell.getCellType() == Cell.CELL_TYPE_FORMULA && tCell.getCellFormula() != null) {
                cell.setCellFormula(tCell.getCellFormula());
            } else {
                setCellValue(cell, wrVDO.getValueAt(c - 9));
            }
            if ("p1".equals(name) || "p2".equals(name) || "p3".equals(name) || "p_1".equals(name)
                    || "p_2".equals(name) || "p_3".equals(name)) {
                if (wrVDO.getValueAt(c - 9) == null) {
                    pMap = apMap.get("T" + waVDO.getTestId());
                    if (pMap == null) {
                        pMap = new HashMap<Integer, ArrayList<AnalyteParameterViewDO>>();
                        apMap.put("T" + waVDO.getTestId(), pMap);
                        try {
                            anaParams = analyteParameter.fetchByActiveDate(waVDO.getTestId(),
                                    Constants.table().TEST, wVDO.getCreatedDate().getDate());
                            for (AnalyteParameterViewDO anaParam : anaParams) {
                                apList = pMap.get(anaParam.getAnalyteId());
                                if (apList == null) {
                                    apList = new ArrayList<AnalyteParameterViewDO>();
                                    pMap.put(anaParam.getAnalyteId(), apList);
                                }
                                apList.add(anaParam);
                            }
                        } catch (NotFoundException nfE) {
                            continue;
                        } catch (Exception anyE) {
                            log.log(Level.SEVERE,
                                    "Error retrieving analyte parameters for an analysis on worksheet.", anyE);
                            continue;
                        }
                    }

                    apList = pMap.get(wrVDO.getAnalyteId());
                    apVDO = null;
                    if (apList != null && apList.size() > 0) {
                        for (AnalyteParameterViewDO ap : apList) {
                            if (ap.getUnitOfMeasureId() == null
                                    || ap.getUnitOfMeasureId().equals(waVDO.getUnitOfMeasureId())) {
                                if (ap.getUnitOfMeasureId() != null) {
                                    apVDO = ap;
                                    break;
                                } else if (apVDO == null) {
                                    apVDO = ap;
                                }
                            }
                        }
                    }
                    if (apVDO != null) {
                        if (("p1".equals(name) || "p_1".equals(name)) && apVDO.getP1() != null) {
                            setCellValue(cell, df.format(apVDO.getP1()));
                        } else if (("p2".equals(name) || "p_2".equals(name)) && apVDO.getP2() != null) {
                            setCellValue(cell, df.format(apVDO.getP2()));
                        } else if (("p3".equals(name) || "p_3".equals(name)) && apVDO.getP3() != null) {
                            setCellValue(cell, df.format(apVDO.getP3()));
                        }
                    }
                }
            }
        }

        i++;
        r++;
    }

    return r;
}

From source file:org.openelis.bean.WorksheetExcelHelperBean.java

License:Open Source License

@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
private int createQcResultCellsForFormat(HSSFSheet sheet, Row row, Row tRow, String nameIndexPrefix,
        HashMap<String, String> cellNames, WorksheetViewDO wVDO, Integer qcId,
        ArrayList<WorksheetQcResultViewDO> wqrList,
        HashMap<String, HashMap<Integer, ArrayList<AnalyteParameterViewDO>>> apMap) {
    int c, i, r;//from  ww  w.  j  a  va  2s  .co  m
    String cellNameIndex, name;
    ArrayList<AnalyteParameterViewDO> anaParams, apList;
    DecimalFormat df;
    HashMap<Integer, ArrayList<AnalyteParameterViewDO>> pMap;
    Cell cell, tCell;
    Name cellName;
    AnalyteParameterViewDO apVDO;

    df = new DecimalFormat();
    df.setGroupingUsed(false);
    df.setMaximumFractionDigits(10);

    i = 0;
    r = row.getRowNum();
    for (WorksheetQcResultViewDO wqrVDO : wqrList) {
        if (i != 0) {
            row = sheet.createRow(r);
            for (c = 0; c < 7; c++) {
                cell = row.createCell(c);
                cell.setCellStyle(styles.get("row_no_edit"));
            }
        }

        cellNameIndex = nameIndexPrefix + "." + i;

        // analyte
        cell = row.createCell(7);
        cell.setCellStyle(styles.get("row_no_edit"));
        cell.setCellValue(wqrVDO.getAnalyteName());

        // reportable
        cell = row.createCell(8);
        cell.setCellStyle(styles.get("row_no_edit"));
        cell.setCellValue("N");

        apVDO = null;
        for (c = 9; c < tRow.getLastCellNum() && c < 39; c++) {
            tCell = tRow.getCell(c);

            cell = row.createCell(c);
            cell.setCellStyle(tCell.getCellStyle());
            name = cellNames.get(
                    sheet.getSheetName() + "!$" + CellReference.convertNumToColString(tCell.getColumnIndex())
                            + "$" + (tCell.getRowIndex() + 1));
            if (name != null) {
                cellName = row.getSheet().getWorkbook().createName();
                cellName.setNameName(name + "." + cellNameIndex);
                cellName.setRefersToFormula(
                        sheet.getSheetName() + "!$" + CellReference.convertNumToColString(cell.getColumnIndex())
                                + "$" + (row.getRowNum() + 1));
            }
            if (tCell.getCellType() == Cell.CELL_TYPE_FORMULA && tCell.getCellFormula() != null) {
                cell.setCellFormula(tCell.getCellFormula());
            } else {
                setCellValue(cell, wqrVDO.getValueAt(c - 9));
            }
            if ("p1".equals(name) || "p2".equals(name) || "p3".equals(name) || "p_1".equals(name)
                    || "p_2".equals(name) || "p_3".equals(name)) {
                if (wqrVDO.getValueAt(c - 9) == null) {
                    pMap = apMap.get("Q" + qcId);
                    if (pMap == null) {
                        pMap = new HashMap<Integer, ArrayList<AnalyteParameterViewDO>>();
                        apMap.put("Q" + qcId, pMap);
                        try {
                            anaParams = analyteParameter.fetchByActiveDate(qcId, Constants.table().QC,
                                    wVDO.getCreatedDate().getDate());
                            for (AnalyteParameterViewDO anaParam : anaParams) {
                                apList = pMap.get(anaParam.getAnalyteId());
                                if (apList == null) {
                                    apList = new ArrayList<AnalyteParameterViewDO>();
                                    pMap.put(anaParam.getAnalyteId(), apList);
                                }
                                apList.add(anaParam);
                            }
                        } catch (NotFoundException nfE) {
                            continue;
                        } catch (Exception anyE) {
                            log.log(Level.SEVERE, "Error retrieving analyte parameters for a qc on worksheet.",
                                    anyE);
                            continue;
                        }
                    }

                    apList = pMap.get(wqrVDO.getAnalyteId());
                    apVDO = null;
                    if (apList != null && apList.size() > 0)
                        apVDO = apList.get(0);
                    if (apVDO != null) {
                        if (("p1".equals(name) || "p_1".equals(name)) && apVDO.getP1() != null) {
                            setCellValue(cell, String.valueOf(apVDO.getP1()));
                        } else if (("p2".equals(name) || "p_2".equals(name)) && apVDO.getP2() != null) {
                            setCellValue(cell, String.valueOf(apVDO.getP2()));
                        } else if (("p3".equals(name) || "p_3".equals(name)) && apVDO.getP3() != null) {
                            setCellValue(cell, String.valueOf(apVDO.getP3()));
                        }
                    }
                }
            }
        }

        i++;
        r++;
    }

    return r;
}

From source file:org.openepics.discs.ccdb.core.dl.common.ExcelImportFileReader.java

License:Open Source License

/**
 * This method returns the contents of the first worksheet found in the
 * Excel workbook file./*ww w  .ja va2 s  .com*/
 *
 * @param inputStream
 *              the Excel file to parse. Only Excel file version &gt;=12.0
 *              supported (.xslx).
 * @param dataStartIndex
 *              the index of the row where to start parsing the import data.
 * @param dataRowLength
 *              the length of each row if it contains all the data. This length is usually defined by the Excel
 *              template.
 * @return Only the lines from the first worksheet that contain a string
 *         value. Lines with the empty first cell are not part of the return
 *         set. Each row is represented as a pair of the row number and a list of columns.
 */
public static List<Pair<Integer, List<String>>> importExcelFile(InputStream inputStream, int dataStartIndex,
        final int dataRowLength) {
    final List<Pair<Integer, List<String>>> result = new ArrayList<>();

    try {
        final XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        final XSSFSheet sheet = workbook.getSheetAt(0);

        for (Row excelRow : sheet) {
            if (excelRow.getRowNum() < dataStartIndex) {
                continue;
            }
            final String firstColumnValue = Strings
                    .emptyToNull(ExcelCell.asStringOrNull(excelRow.getCell(0), workbook));
            if (firstColumnValue != null && !firstColumnValue.trim().isEmpty()) {
                final List<String> row = new ArrayList<>();
                final int rowNumber = excelRow.getRowNum() + 1;
                final int lastCellIndex = dataRowLength > excelRow.getLastCellNum() ? dataRowLength
                        : excelRow.getLastCellNum();
                for (int i = 0; i < lastCellIndex; i++) {
                    row.add(ExcelCell.asStringOrNull(excelRow.getCell(i), workbook));
                }
                result.add(new ImmutablePair<Integer, List<String>>(rowNumber, row));
            }
        }
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
    return result;
}

From source file:org.openepics.names.services.InitialDataImportService.java

License:Open Source License

private void fillNameParts(boolean isSection) {
    final XSSFSheet sheet = workbook.getSheet(isSection ? "AreaStructure" : "DeviceStructure");
    for (Row row : sheet) {
        if (row.getRowNum() >= 2) {
            final int parent = (int) ExcelCell.asNumber(row.getCell(0));
            final int id = (int) ExcelCell.asNumber(row.getCell(1));
            final String name = As.notNull(ExcelCell.asString(row.getCell(2)));
            final String mnemonic = As.notNull(ExcelCell.asString(row.getCell(3)));
            @Nullable// w w  w  .  ja va  2  s . c  o  m
            final String description = ExcelCell.asString(row.getCell(4));
            namePartsMap.put(id, isSection ? addSection(namePartsMap.get(parent), name, mnemonic, description)
                    : addDeviceType(namePartsMap.get(parent), name, mnemonic, description));
        }
    }
}

From source file:org.openepics.names.services.InitialDataImportService.java

License:Open Source License

private void fillDeviceNames() {
    final XSSFSheet sheet = workbook.getSheet("DeviceInstances");
    final List<DeviceDefinition> devices = Lists.newArrayList();
    for (Row row : sheet) {
        if (row.getRowNum() >= 1) {
            final int subsectionId = (int) ExcelCell.asNumber(row.getCell(1));
            final int deviceTypeId = (int) ExcelCell.asNumber(row.getCell(2));
            @Nullable/*  w w w  .  ja  v a2s .  co m*/
            final String instanceIndex = ExcelCell.asString(row.getCell(3));
            @Nullable
            final String additionalInfo = ExcelCell.asString(row.getCell(4));
            devices.add(new DeviceDefinition(namePartsMap.get(subsectionId), namePartsMap.get(deviceTypeId),
                    instanceIndex, additionalInfo));
        }
    }
    namePartService.batchAddDevices(devices, null);
}

From source file:org.openepics.names.ui.devices.ExcelImport.java

License:Open Source License

/**
 * Parses the input stream read from an Excel file, creating devices in the database. If the device already exists,
 * it's silently ignored./*from  w w w .  j a  v  a  2 s  . c o m*/
 *
 * @param input the input stream
 * @return an ExcelImportResult object reporting the outcome of the import operation
 */
public ExcelImportResult parseDeviceImportFile(InputStream input) {
    init();

    try {
        final XSSFWorkbook workbook = new XSSFWorkbook(input);
        final XSSFSheet sheet = workbook.getSheetAt(0);
        for (Row row : sheet) {
            if (row.getRowNum() > 0) {
                if (row.getLastCellNum() < 5) {
                    return new ColumnCountFailureExcelImportResult();
                } else {
                    final String superSection = ExcelCell.asString(row.getCell(0));
                    final String section = As.notNull(ExcelCell.asString(row.getCell(1)));
                    final String subsection = As.notNull(ExcelCell.asString(row.getCell(2)));
                    final String discipline = As.notNull(ExcelCell.asString(row.getCell(3)));
                    final String deviceType = As.notNull(ExcelCell.asString(row.getCell(4)));
                    final @Nullable String index = ExcelCell.asString(row.getCell(5));
                    final @Nullable String description = ExcelCell.asString(row.getCell(6));
                    final ExcelImportResult addDeviceNameResult = addDeviceName(superSection, section,
                            subsection, discipline, deviceType, index, description, row.getRowNum());
                    if (addDeviceNameResult instanceof FailureExcelImportResult) {
                        return addDeviceNameResult;
                    }
                }
            }
        }
    } catch (IOException e) {
        throw new RuntimeException(e);
    }

    namePartService.batchAddDevices(newDevices);
    return new SuccessExcelImportResult();
}

From source file:org.openflexo.technologyadapter.excel.view.ExcelSheetView.java

License:Open Source License

private void updateRowHeights() {
    for (Row row : sheet.getSheet()) {
        table.setRowHeight(row.getRowNum(), (int) row.getHeightInPoints());
    }/*  w w  w  . j  a va  2 s .  c  om*/
}

From source file:org.openmrs.module.mksreports.renderer.PatientHistoryExcelTemplateRenderer.java

License:Open Source License

/**
 * Adds in a Row to the given Sheet/*from   w ww . j a v  a 2s.c  o  m*/
 */
public Row addRow(Workbook wb, SheetToAdd sheetToAdd, RowToAdd rowToAdd, int rowIndex, ReportData reportData,
        ReportDesign design, Map<String, String> repeatSections) {

    // Create a new row and copy over style attributes from the row to add
    Row newRow = sheetToAdd.getSheet().createRow(rowIndex);
    Row rowToClone = rowToAdd.getRowToClone();
    try {
        CellStyle rowStyle = rowToClone.getRowStyle();
        if (rowStyle != null) {
            newRow.setRowStyle(rowStyle);
        }
    } catch (Exception e) {
        // No idea why this is necessary, but this has thrown IndexOutOfBounds errors getting the rowStyle.  Mysteries of POI
    }
    newRow.setHeight(rowToClone.getHeight());

    // Iterate across all of the cells in the row, and configure all those that need to be added/cloned
    List<CellToAdd> cellsToAdd = new ArrayList<CellToAdd>();

    int totalCells = rowToClone.getPhysicalNumberOfCells();
    int cellsFound = 0;
    for (int cellNum = 0; cellsFound < totalCells; cellNum++) {
        Cell currentCell = rowToClone.getCell(cellNum);
        log.debug("Handling cell: " + currentCell);
        if (currentCell != null) {
            cellsFound++;
        }
        // If we find that the cell that we are on is a repeating cell, then add the appropriate number of cells to clone
        String repeatingColumnProperty = getRepeatingColumnProperty(sheetToAdd.getOriginalSheetNum(), cellNum,
                repeatSections);
        if (repeatingColumnProperty != null) {
            String[] dataSetSpanSplit = repeatingColumnProperty.split(",");
            String dataSetName = dataSetSpanSplit[0];
            DataSet dataSet = getDataSet(reportData, dataSetName, rowToAdd.getReplacementData());
            int numCellsToRepeat = 1;
            if (dataSetSpanSplit.length == 2) {
                numCellsToRepeat = Integer.parseInt(dataSetSpanSplit[1]);
            }
            log.debug("Repeating this cell with dataset: " + dataSet + " and repeat of " + numCellsToRepeat);
            int repeatNum = 0;
            for (DataSetRow dataSetRow : dataSet) {
                repeatNum++;
                for (int i = 0; i < numCellsToRepeat; i++) {
                    Cell cell = (i == 0 ? currentCell : rowToClone.getCell(cellNum + i));
                    if (repeatNum == 1 && cell != null && cell != currentCell) {
                        cellsFound++;
                    }
                    Map<String, Object> newReplacements = getReplacementData(rowToAdd.getReplacementData(),
                            reportData, design, dataSetName, dataSetRow, repeatNum);
                    cellsToAdd.add(new CellToAdd(cell, newReplacements));
                    log.debug("Adding " + cell + " with dataSetRow: " + dataSetRow);
                }
            }
            cellNum += numCellsToRepeat;
        } else {
            cellsToAdd.add(new CellToAdd(currentCell, rowToAdd.getReplacementData()));
            log.debug("Adding " + currentCell);
        }
    }

    // Now, go through all of the collected cells, and add them back in

    String prefix = getExpressionPrefix(design);
    String suffix = getExpressionSuffix(design);

    List<CellRangeAddress> newMergedRegions = new ArrayList<CellRangeAddress>();

    for (int i = 0; i < cellsToAdd.size(); i++) {
        CellToAdd cellToAdd = cellsToAdd.get(i);
        Cell newCell = newRow.createCell(i);
        Cell cellToClone = cellToAdd.getCellToClone();
        if (cellToClone != null) {
            Object contents = ExcelUtil.getCellContents(cellToClone);
            newCell.setCellStyle(cellToClone.getCellStyle());

            int numFormattings = sheetToAdd.getSheet().getSheetConditionalFormatting()
                    .getNumConditionalFormattings();
            for (int n = 0; n < numFormattings; n++) {
                ConditionalFormatting f = sheetToAdd.getSheet().getSheetConditionalFormatting()
                        .getConditionalFormattingAt(n);
                for (CellRangeAddress add : f.getFormattingRanges()) {

                    if (add.getFirstRow() == rowToAdd.getRowToClone().getRowNum()
                            && add.getLastRow() == rowToClone.getRowNum()) {
                        if (add.getFirstColumn() == cellToClone.getColumnIndex()
                                && add.getLastColumn() == cellToClone.getColumnIndex()) {
                            ConditionalFormattingRule[] rules = new ConditionalFormattingRule[f
                                    .getNumberOfRules()];
                            for (int j = 0; j < f.getNumberOfRules(); j++) {
                                rules[j] = f.getRule(j);
                            }
                            CellRangeAddress[] cellRange = new CellRangeAddress[1];
                            cellRange[0] = new CellRangeAddress(rowIndex, rowIndex, i, i);
                            sheetToAdd.getSheet().getSheetConditionalFormatting()
                                    .addConditionalFormatting(cellRange, rules);
                        }
                    }
                }
            }

            int numMergedRegions = sheetToAdd.getSheet().getNumMergedRegions();
            for (int n = 0; n < numMergedRegions; n++) {
                CellRangeAddress add = sheetToAdd.getSheet().getMergedRegion(n);
                int rowNum = rowToClone.getRowNum();
                if (add.getFirstRow() == rowNum && add.getLastRow() == rowNum) {
                    if (add.getFirstColumn() == cellToClone.getColumnIndex()) {
                        newMergedRegions
                                .add(new CellRangeAddress(rowNum, rowNum, i, i + add.getNumberOfCells() - 1));
                    }
                }
            }

            if (ObjectUtil.notNull(contents)) {
                if (contents instanceof String) {
                    contents = EvaluationUtil.evaluateExpression(contents.toString(),
                            cellToAdd.getReplacementData(), prefix, suffix);
                }
                ExcelUtil.setCellContents(newCell, contents);
            }

            ExcelUtil.copyFormula(cellToClone, newCell);
        }
    }

    for (CellRangeAddress mergedRegion : newMergedRegions) {
        sheetToAdd.getSheet().addMergedRegion(mergedRegion);
    }

    return newRow;
}