Example usage for org.apache.poi.ss.usermodel Cell setCellFormula

List of usage examples for org.apache.poi.ss.usermodel Cell setCellFormula

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. setCellFormula

Introduction

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

Prototype

void setCellFormula(String formula) throws FormulaParseException, IllegalStateException;

Source Link

Document

Sets formula for this cell.

Usage

From source file:org.h819.commons.file.excel.poi.examples.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;//from   w  ww .j a  va 2s. com

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    // title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    // header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i]);
        headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
            Cell cell = row.createCell(j);
            if (j == 9) {
                // the 10th cell contains sum over week days, e.g.
                // SUM(C3:I3)
                String ref = "C" + rownum + ":I" + rownum;
                cell.setCellFormula("SUM(" + ref + ")");
                cell.setCellStyle(styles.get("formula"));
            } else if (j == 11) {
                cell.setCellFormula("J" + rownum + "-K" + rownum);
                cell.setCellStyle(styles.get("formula"));
            } else {
                cell.setCellStyle(styles.get("cell"));
            }
        }
    }

    // row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
        cell = sumRow.createCell(j);
        String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
        cell.setCellFormula("SUM(" + ref + ")");
        if (j >= 9)
            cell.setCellStyle(styles.get("formula_2"));
        else
            cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    // set sample data
    for (int i = 0; i < sample_data.length; i++) {
        Row row = sheet.getRow(2 + i);
        for (int j = 0; j < sample_data[i].length; j++) {
            if (sample_data[i][j] == null)
                continue;

            if (sample_data[i][j] instanceof String) {
                row.getCell(j).setCellValue((String) sample_data[i][j]);
            } else {
                row.getCell(j).setCellValue((Double) sample_data[i][j]);
            }
        }
    }

    // finally set column widths, the width is measured in units of 1/256th
    // of a character width
    sheet.setColumnWidth(0, 30 * 256); // 30 characters wide
    for (int i = 2; i < 9; i++) {
        sheet.setColumnWidth(i, 6 * 256); // 6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); // 10 characters wide

    // Write the output to a file
    String file = "timesheet.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:org.isource.util.CSVUtils.java

public static void updateSheet(String filename) {

    try {//from   w w  w  .jav  a  2  s  .c  o  m
        FileInputStream file = new FileInputStream(new File(filename));

        //Get the workbook instance for XLS file 
        HSSFWorkbook workbook = new HSSFWorkbook(file);

        //Get first sheet from the workbook
        HSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        int rowNum = 1;
        while (rowIterator.hasNext()) {

            Row row = rowIterator.next();

            List<String> line = new ArrayList<String>();

            Cell newCell = row.createCell(row.getPhysicalNumberOfCells());

            if (rowNum == 1) {
                newCell.setCellValue("New Cell");
            } else {
                newCell.setCellType(Cell.CELL_TYPE_FORMULA);
                newCell.setCellFormula("SUM(B2:B9)");
            }

            rowNum++;
        }

        workbook = evaluateFormulas(workbook);
        FileOutputStream out = new FileOutputStream(new File(filename));
        workbook.write(out);
        out.close();
        file.close();

    } catch (FileNotFoundException ex) {
        Logger.getLogger(CSVUtils.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(CSVUtils.class.getName()).log(Level.SEVERE, null, ex);
    }

}

From source file:org.isource.util.CSVUtils.java

public List<String> createSheet(List<String> titles, List<List> lines, Formula formula_obj, String filename)
        throws FormulaParseException {

    String formula = translate_formula(formula_obj.getFormula(), titles);

    Map<Integer, String> sortedCols = Mapping.sortCols(titles);

    HSSFWorkbook workbook = new HSSFWorkbook();

    HSSFSheet sheet = workbook.createSheet("Calculate Kpi");

    Row header = sheet.createRow(0);/* w ww  .  ja v a 2  s.c  o m*/

    int title_cell = 0;
    for (int col : sortedCols.keySet()) {
        String colFullName = Mapping.getFullLabel(sortedCols.get(col));
        header.createCell(title_cell).setCellValue(colFullName);
        title_cell++;
    }
    header.createCell(sortedCols.size()).setCellValue(formula_obj.getTitle());

    for (int row = 1; row <= lines.size(); row++) {

        Row dataRow = sheet.createRow(row);
        List<String> line = lines.get(row - 1);

        int cell = 0;
        for (int col : sortedCols.keySet()) {
            //              System.out.println(col + "  " + line.get(col));
            Cell c = dataRow.createCell(cell);
            if (isNumeric(line.get(col))) {
                c.setCellValue(Double.parseDouble(line.get(col)));
            } else {
                c.setCellValue(line.get(col));
            }
            cell++;
        }

        // create formula result cell
        Cell formula_cell = dataRow.createCell(line.size());
        formula_cell.setCellType(Cell.CELL_TYPE_FORMULA);

        // replace # with row ranges
        if (formula.contains("#")) {
            formula_cell.setCellFormula(formula.replace("#", (row + 1) + ""));
            System.out.println(formula.replace("#", (row + 1) + ""));
        } else {
            formula_cell.setCellFormula(formula);
        }
    }

    List<List> KpiLines = readWorkbook(workbook);
    List formula_output = new ArrayList<String>();

    for (int i = 0; i < KpiLines.size(); i++) {

        List line = KpiLines.get(i);
        formula_output.add(line.get(line.size() - 1));

    }

    // apply formula 
    workbook = evaluateFormulas(workbook);

    // write into sample.xls
    try {
        FileOutputStream out = new FileOutputStream(new File(Provider.getUpload_path() + filename + ".xls"));
        workbook.write(out);
        out.close();
    } catch (Exception ex) {
    }

    return formula_output;

}

From source file:org.isource.util.CSVUtils.java

public static String validate_formula(String formula) {

    List<String> allCols = Mapping.minify(Mapping.getMap());

    formula = new CSVUtils().translate_formula(formula, allCols).replace("#", "1");

    String v_msg = "valid";
    try {/*from ww  w. j a  v  a2 s . c o m*/
        FileInputStream file = new FileInputStream(new File(Provider.getUpload_path() + "validation.xls"));

        HSSFWorkbook workbook = new HSSFWorkbook(file);
        HSSFSheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(0);
        Cell cell = row.createCell(row.getPhysicalNumberOfCells());
        cell.setCellType(Cell.CELL_TYPE_FORMULA);

        cell.setCellFormula(formula);

        workbook = evaluateFormulas(workbook);

        file.close();

        /*
        FileOutputStream outFile = new FileOutputStream(new File("/media/islam/55247aa2-2234-4e48-8a62-c1fabcb5c84d/opt/apache-tomcat-7.0.70/webapps/data/validation.xls"));
        workbook.write(outFile);
        outFile.close();
        */

    } catch (Exception e) {
        v_msg = e.getMessage();
    }
    return v_msg;
}

From source file:org.joeffice.spreadsheet.cell.CellUtils.java

License:Apache License

public static void copyCell(Cell oldCell, Cell newCell) {
    newCell.setCellStyle(oldCell.getCellStyle());

    if (newCell.getCellComment() != null) {
        newCell.setCellComment(oldCell.getCellComment());
    }//from  w  w w  .j a  v  a  2 s .  c  om

    if (oldCell.getHyperlink() != null) {
        newCell.setHyperlink(oldCell.getHyperlink());
    }

    newCell.setCellType(oldCell.getCellType());

    // Set the cell data value
    switch (oldCell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        newCell.setCellValue(oldCell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_ERROR:
        newCell.setCellErrorValue(oldCell.getErrorCellValue());
        break;
    case Cell.CELL_TYPE_FORMULA:
        newCell.setCellFormula(oldCell.getCellFormula());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        newCell.setCellValue(oldCell.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_STRING:
        newCell.setCellValue(oldCell.getRichStringCellValue());
        break;
    }
}

From source file:org.nekorp.workflow.desktop.servicio.reporte.global.GeneradorReporteGlobal.java

License:Apache License

@Override
public void generaReporte(ParametrosReporteGlobal param) {
    FileOutputStream fileOut = null;
    try {/*  ww  w  .j a va2s.c  o m*/
        //List<Servicio> datos = servicioDAO.getByDate(new DateTime("2013-05-1T00:00:00.000-00:00"), new DateTime("2013-05-1T23:59:59.999-00:00"));
        List<Servicio> datos = servicioDAO.getByDate(param.getFechaInicial(), param.getFechaFinal());
        GeneradorReporteGlobal.LOGGER.debug("file:" + param.getDestination());
        GeneradorReporteGlobal.LOGGER.debug("fecha Inicial:" + param.getFechaInicial());
        GeneradorReporteGlobal.LOGGER.debug("fecha Final:" + param.getFechaFinal());
        XSSFWorkbook wb = new XSSFWorkbook();
        //estilo para las fechas
        XSSFCellStyle dateCellStyle = wb.createCellStyle();
        CreationHelper createHelper = wb.getCreationHelper();
        dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
        //estilos para las cantidades
        XSSFCellStyle moneyCellStyle = wb.createCellStyle();
        XSSFDataFormat mf = wb.createDataFormat();
        moneyCellStyle.setDataFormat(mf.getFormat("$#,##0.00"));
        Sheet sheet = wb.createSheet("Hoja1");
        int rowCount = 1;
        Cell actual;
        Row r;
        for (Servicio x : datos) {
            RenglonRG datRen = renglonFactory.build(x);
            r = sheet.createRow(rowCount);
            rowCount = rowCount + 1;
            for (int i = 0; i < valueMap.length; i++) {
                actual = r.createCell(i);
                if (valueMap[i].getType() == CellValueType.TEXT) {
                    Object obj = PropertyUtils.getProperty(datRen, valueMap[i].getValue());
                    actual.setCellValue(obj.toString());
                }
                if (valueMap[i].getType() == CellValueType.DATE) {
                    Object obj = PropertyUtils.getProperty(datRen, valueMap[i].getValue());
                    if (obj != null) {
                        Date val = (Date) obj;
                        actual.setCellValue(val);
                        actual.setCellStyle(dateCellStyle);
                    }
                }
                if (valueMap[i].getType() == CellValueType.AMOUNT) {
                    Object obj = PropertyUtils.getProperty(datRen, valueMap[i].getValue());
                    double val = (double) obj;
                    actual.setCellValue(val);
                    actual.setCellStyle(moneyCellStyle);
                }
                if (valueMap[i].getType() == CellValueType.FORMULA) {
                    String formulaRaw = valueMap[i].getValue();
                    ST formula = new ST(formulaRaw);
                    formula.add("row", rowCount + "");
                    actual.setCellFormula(formula.render());
                    actual.setCellStyle(moneyCellStyle);
                }
            }
        }
        llenarEncabezado(sheet, 0, 0);
        fileOut = new FileOutputStream(param.getDestination());
        wb.write(fileOut);
    } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException | FormulaParseException
            | IOException ex) {
        GeneradorReporteGlobal.LOGGER.error("error al generar reporte", ex);
    } finally {
        try {
            fileOut.close();
        } catch (IOException | NullPointerException ex) {
            GeneradorReporteGlobal.LOGGER.error("error al cerrar archivo de reporte", ex);
        }
    }
}

From source file:org.netxilia.impexp.impl.ExcelExportService.java

License:Open Source License

private void copyCellValue(CellData nxCell, Cell poiCell) {
    if (nxCell.getValue() == null) {
        return;/* w  w  w.  j av a2 s .  c  o  m*/
    }
    if (nxCell.getFormula() != null) {
        // remove leading =
        poiCell.setCellFormula(nxCell.getFormula().getFormula().substring(1));
        return;
    }
    switch (nxCell.getValue().getValueType()) {
    case BOOLEAN:
        poiCell.setCellValue(nxCell.getValue().getBooleanValue());
        break;
    case NUMBER:
        poiCell.setCellValue(nxCell.getValue().getNumberValue());
        break;
    case DATE:
        poiCell.setCellValue(nxCell.getValue().getDateValue().toDateTime(DateValue.ORIGIN).toDate());
        break;
    case ERROR:
        // TODO translate errors
        // poiCell.setCellErrorValue(((ErrorValue)nxCell.getValue()).getErrorType());
        break;
    case STRING:
        poiCell.setCellValue(nxCell.getValue().getStringValue());
        break;
    }

    poiCell.setCellStyle(PoiUtils.netxiliaStyle2Poi(nxCell.getStyles(), poiCell.getSheet().getWorkbook()));
}

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

License:Open Source License

private void setStatisticCells(HSSFWorkbook wb, HSSFSheet sheet, Value value) {
    Cell cell;

    cell = getCellForName(sheet, getName(wb, sheet, "Mean"));
    if (cell != null) {
        cell.setCellFormula(null);
        setCellValue(cell, DataBaseUtil.toString(value.getMean()));
        setMaxChars(cell, maxChars);//from  www.  j av  a2s  .c  om
    }

    cell = getCellForName(sheet, getName(wb, sheet, "UCL"));
    if (cell != null) {
        cell.setCellFormula(null);
        setCellValue(cell, DataBaseUtil.toString(value.getUCL()));
        setMaxChars(cell, maxChars);
    }

    cell = getCellForName(sheet, getName(wb, sheet, "LCL"));
    if (cell != null) {
        cell.setCellFormula(null);
        setCellValue(cell, DataBaseUtil.toString(value.getLCL()));
        setMaxChars(cell, maxChars);
    }
}

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;/* w ww.j av  a2 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 . ja  va 2  s . 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;
}