List of usage examples for org.apache.poi.hssf.usermodel HSSFCell setCellFormula
@Override public final void setCellFormula(String formula) throws FormulaParseException, IllegalStateException
From source file:citibob.reports.PoiXlsWriter.java
License:Open Source License
/** Don't do fixup between xrow0 and xrow1, non-inclusive. */ public void fixupFormulas(HSSFSheet sheet, int rowIx, int n, int xrow0, int xrow1) { //System.out.println("--------- fixupFormulas(" + rowIx + "," + n + ")"); int prows = sheet.getPhysicalNumberOfRows(); int pr = 0;/* w w w . ja v a 2 s. c o m*/ for (int r = 0; pr < prows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) continue; ++pr; int pcells = row.getPhysicalNumberOfCells(); int pc = 0; for (int c = 0; pc < pcells; ++c) { HSSFCell cell = row.getCell((short) c); if (cell == null) continue; ++pc; // Fixup the formula if (cell.getCellType() != HSSFCell.CELL_TYPE_FORMULA) continue; //System.out.println("Formula cell: " + cell.getCellFormula()); //System.out.println(" ncells = " + row.getLastCellNum()); FormulaParser fp = new FormulaParser(cell.getCellFormula(), wb.getWorkbook()); fp.parse(); Ptg[] ptg = fp.getRPNPtg(); for (int i = 0; i < ptg.length; ++i) { Ptg pi = ptg[i]; // if (pi.getPtgClass() != Ptg.CLASS_REF) continue; if (pi instanceof AreaPtg) { //System.out.println("Fixing area: " + pi); AreaPtg pp = (AreaPtg) pi; if (pp.getFirstRow() >= rowIx) pp.setFirstRow((short) (pp.getFirstRow() + n)); if (pp.getLastRow() >= rowIx) { pp.setLastRow((short) (pp.getLastRow() + n)); } } else if (pi instanceof ReferencePtg) { ReferencePtg pp = (ReferencePtg) pi; if (r >= xrow0 && r < xrow1) { if (pp.getRow() <= r && pp.isRowRelative()) pp.setRow((short) (r + pp.getRow() - rowIx)); } else if (pp.getRow() >= rowIx) { pp.setRow((short) (pp.getRow() + n)); } } } // Done fixing the formula; set it back String fstr = fp.toFormulaString(wb.getWorkbook(), ptg); //System.out.println("replacing formula string (" + r + "," + c + "): " + fstr); cell.setCellFormula(fstr); } } }
From source file:citibob.reports.PoiXlsWriter.java
License:Open Source License
void copyCell(HSSFCell c0, HSSFCell c1) { copyCellFormatting(c0, c1);// w w w . ja va 2 s .c o m c1.setCellType(c0.getCellType()); switch (c0.getCellType()) { case HSSFCell.CELL_TYPE_STRING: c1.setCellValue(c0.getRichStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: c1.setCellValue(c0.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: c1.setCellFormula(c0.getCellFormula()); break; case HSSFCell.CELL_TYPE_BOOLEAN: c1.setCellValue(c0.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: c1.setCellErrorValue(c0.getErrorCellValue()); break; } }
From source file:com.bayareasoftware.chartengine.ds.ExcelDataSource.java
License:Apache License
/** * work around bug with named cells/*from w ww .j a v a 2 s. c om*/ * */ private static void rewriteFormulas(final HSSFWorkbook workbook) { //if (true) return; // build up a cache of names // this is just an easy way of fetching the HSSFName based on the string // representation of the name final Map<String, HSSFName> nameCache = new HashMap<String, HSSFName>(workbook.getNumberOfNames()); for (int i = 0; i < workbook.getNumberOfNames(); i++) { final HSSFName name = workbook.getNameAt(i); nameCache.put(name.getNameName(), name); } // remove all the sheet names from the name references, having the sheet // names around messes up the formulas for (int i = 0; i < workbook.getNumberOfSheets(); i++) { nameCache.remove(workbook.getSheetName(i)); } //p("Names: " + nameCache.keySet()); // loop over all the cells and rewrite the formula ones for (int sheetCount = 0; sheetCount < workbook.getNumberOfSheets(); sheetCount++) { final HSSFSheet sheet = workbook.getSheetAt(sheetCount); for (final Iterator rowIterator = sheet.rowIterator(); rowIterator.hasNext();) { final HSSFRow row = (HSSFRow) rowIterator.next(); for (final Iterator cellIterator = row.cellIterator(); cellIterator.hasNext();) { final HSSFCell cell = (HSSFCell) cellIterator.next(); if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { String formula = cell.getCellFormula(); for (final String name : nameCache.keySet()) { final Pattern pattern = Pattern.compile("(\\W|^)" + name + "(\\W|$)", Pattern.CASE_INSENSITIVE); final HSSFName hssfName = nameCache.get(name); formula = pattern.matcher(formula) .replaceAll("$1" + hssfName.getReference().replace("$", "\\$") + "$2"); } //p("Resetting Cell (" + cell.toString() // + ") Formula:" + formula); cell.setCellFormula(formula); } // end if } // end for } // end for } // end for }
From source file:com.claim.controller.Center16AndJula2015Controller.java
public ProgrameStatus center16_Jula_2015(OppReport report) { ProgrameStatus programeStatus = new ProgrameStatus(); List<ObjRptChula> listData = new ArrayList<ObjRptChula>(); int col_last = 29; int row_start = 8; // index row int row_formula_start = row_start + 1; int col_txtid_width = col_last + 1; try {/*from w ww . ja v a 2 s . c o m*/ connection = new DBManage().open(); Center16AndChula2015DAO chula2015DAO = new Center16AndChula2015DAO(); chula2015DAO.setConnection(connection); if (report.getServiceCode().equals(HCODE_CENTER16)) { // center 16 listData = chula2015DAO.getListChulaDetail(report.getStmp(), HCODE_CENTER16); EXCELL_HEADER1 = "???? OP : " + chula2015DAO.getMonthPayment(report.getStmp()); EXCELL_HEADER2 = "? 16 13661 Model 2 "; EXCELL_HOSPITAL = "?? ? 16 13661 "; //out out = new FileOutputStream(report.getPathFile() + "" + File.separator + "HC16_13661_" + report.getYearMonth() + "-" + report.getNo() + ".xls"); report.setServiceName( " ? 16 "); } else if (report.getServiceCode().equals(HCODE_CHULA)) { // Jula listData = chula2015DAO.getListChulaDetail(report.getStmp(), HCODE_CHULA); EXCELL_HEADER1 = "???? OP : " + chula2015DAO.getMonthPayment(report.getStmp()); EXCELL_HEADER2 = "? 16 13661 Model 2"; EXCELL_HOSPITAL = "?? ? ? 13756"; //out out = new FileOutputStream(report.getPathFile() + "" + File.separator + "Chula_13756_" + report.getYearMonth() + "-" + report.getNo() + ".xls"); report.setServiceName("?"); } //readTemplate file = new FileInputStream( new File("." + File.separator + "xls" + File.separator + "CH16_CHula_detail_2015.xls")); // style Excell HSSFWorkbook wbCenter16Jula = new HSSFWorkbook(file); this.loadStyle(wbCenter16Jula); // Start sheet 1 ******************************************************************************* HSSFSheet sheet = workbookBase.getSheetAt(0); sheet.createFreezePane(4, 8); // col,row sheet.setColumnWidth(col_txtid_width, WIDTH_TXID); HSSFCell cell = null; HSSFRow row = null; // row 0 HEADER0 row = sheet.createRow(0); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER1); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last)); cell.setCellStyle(csHead); // row 1 HEADER2 row = sheet.createRow(1); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER2); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col_last)); cell.setCellStyle(csHead); // row 1 HOSPITAL row = sheet.createRow(2); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HOSPITAL); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, col_last)); cell.setCellStyle(csHead); int curRow = row_start; int i = 1; for (int j = 0; j < listData.size(); j++) { ObjRptChula data = listData.get(j); int col1 = i; row = sheet.createRow(curRow); row.setHeight((short) 340); /*PoiHssfUtil stylePoi = new PoiHssfUtil(wb, row, cell); stylePoi.setStyleText(0, String.valueOf(col1), PoiHssfUtil.CENTER);*/ cell = row.createCell(0); cell.setCellValue(col1); cell.setCellStyle(csNum4); cell = row.createCell(1); cell.setCellValue(data.getPid()); cell.setCellStyle(csStringPid); cell = row.createCell(2); cell.setCellValue(data.getHn()); cell.setCellStyle(csStringLeft); cell = row.createCell(3); cell.setCellValue(data.getPname()); cell.setCellStyle(csStringLeft); cell = row.createCell(4); cell.setCellValue(data.getHmain()); cell.setCellStyle(csStringLeft); cell = row.createCell(5); cell.setCellValue(data.getDateopd_th()); cell.setCellStyle(csStringLeft); cell = row.createCell(6); cell.setCellValue(data.getPdxcode()); cell.setCellStyle(csNum4); cell = row.createCell(7); cell.setCellValue(data.getChrg_car()); cell.setCellStyle(csDouble2); cell = row.createCell(8); cell.setCellValue(data.getChrg_rehab_inst()); cell.setCellStyle(csDouble2); cell = row.createCell(9); cell.setCellValue(data.getChrg_ophc()); cell.setCellStyle(csDouble2); cell = row.createCell(10); cell.setCellValue(data.getChrg_car_rehabinst_ophc_total()); cell.setCellStyle(csDouble2); cell = row.createCell(11); cell.setCellValue(data.getChrg_202()); cell.setCellStyle(csDouble2); cell = row.createCell(12); cell.setCellValue(data.getChrg_stditem()); cell.setCellStyle(csDouble2); cell = row.createCell(13); cell.setCellValue(data.getChrg_other()); cell.setCellStyle(csDouble2); cell = row.createCell(14); cell.setCellValue(data.getChrg_total()); cell.setCellStyle(csDouble2); cell = row.createCell(15); cell.setCellValue(data.getSum_chrg()); cell.setCellStyle(csDouble2); cell = row.createCell(16); cell.setCellValue(data.getPaid_car()); cell.setCellStyle(csDouble2); cell = row.createCell(17); cell.setCellValue(data.getPaid_rehab_inst()); cell.setCellStyle(csDouble2); cell = row.createCell(18); cell.setCellValue(data.getPaid_ophc()); cell.setCellStyle(csDouble2); cell = row.createCell(19); cell.setCellValue(data.getPaid_car_rehabinst_ophc_total()); cell.setCellStyle(csDouble2); cell = row.createCell(20); cell.setCellValue(data.getPaid_202()); cell.setCellStyle(csDouble2); cell = row.createCell(21); cell.setCellValue(data.getPaid_stditem()); cell.setCellStyle(csDouble2); cell = row.createCell(22); cell.setCellValue(data.getPaid_other()); cell.setCellStyle(csDouble2); cell = row.createCell(23); cell.setCellValue(data.getPaid_202_stditem_other_total()); cell.setCellStyle(csDouble2); cell = row.createCell(24); cell.setCellValue(data.getPaid_cal_point()); cell.setCellStyle(csDouble2); cell = row.createCell(25); cell.setCellValue(data.getPaid_cal_point_total()); cell.setCellStyle(csDouble2); cell = row.createCell(26); cell.setCellValue(data.getPaid_total()); cell.setCellStyle(csDouble2); cell = row.createCell(27); cell.setCellValue(data.getCompensation_fee_total()); cell.setCellStyle(csDouble2); cell = row.createCell(28); cell.setCellValue(data.getRemark()); cell.setCellStyle(csStringLeft); cell = row.createCell(29); cell.setCellValue(data.getInvoice_no()); cell.setCellStyle(csStringLeft); cell = row.createCell(30); cell.setCellValue(data.getTxid()); cell.setCellStyle(csStringtxid); curRow++; i++; } // row = sheet.createRow(curRow); cell = row.createCell(0); cell.setCellValue(""); sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 6)); cell.setCellStyle(csNum4B); row.createCell(1).setCellStyle(csNum4B); row.createCell(2).setCellStyle(csNum4B); row.createCell(3).setCellStyle(csNum4B); row.createCell(4).setCellStyle(csNum4B); row.createCell(5).setCellStyle(csNum4B); row.createCell(6).setCellStyle(csNum4B); cell = row.createCell(7); cell.setCellFormula(builderFormulaSum(7, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(8); cell.setCellFormula(builderFormulaSum(8, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(9); cell.setCellFormula(builderFormulaSum(9, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(10); cell.setCellFormula(builderFormulaSum(10, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(11); cell.setCellFormula(builderFormulaSum(11, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(12); cell.setCellFormula(builderFormulaSum(12, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(13); cell.setCellFormula(builderFormulaSum(13, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(14); cell.setCellFormula(builderFormulaSum(14, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(15); cell.setCellFormula(builderFormulaSum(15, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(16); cell.setCellFormula(builderFormulaSum(16, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(17); cell.setCellFormula(builderFormulaSum(17, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(18); cell.setCellFormula(builderFormulaSum(18, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(19); cell.setCellFormula(builderFormulaSum(19, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(20); cell.setCellFormula(builderFormulaSum(20, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(21); cell.setCellFormula(builderFormulaSum(21, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(22); cell.setCellFormula(builderFormulaSum(22, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(23); cell.setCellFormula(builderFormulaSum(23, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(24); cell.setCellFormula(builderFormulaSum(24, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(25); cell.setCellFormula(builderFormulaSum(25, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(26); cell.setCellFormula(builderFormulaSum(26, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(27); cell.setCellFormula(builderFormulaSum(27, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(28); cell.setCellStyle(csNum4B); workbookBase.setSheetName(0, report.getServiceCode() + " " + report.getServiceName()); workbookBase.write(out); out.close(); file.close(); Console.LOG(Message.exportSuccess(report.getServiceName()), 1); programeStatus.setMessage(ConstantMessage.MSG_REPORT_SUCCESS); programeStatus.setTitle(ConstantMessage.MSG_REPORT_COMPLETE); programeStatus.setProcessStatus(true); } catch (Exception e) { e.printStackTrace(); Console.LOG(e.getMessage(), 0); programeStatus.setMessage(ConstantMessage.MSG_PROCESS_FAILS + e.toString()); programeStatus.setTitle(ConstantMessage.MSG_CONTACT_ADMIN); programeStatus.setProcessStatus(false); } finally { if (connection != null) { try { connection.close(); } catch (SQLException ex) { Logger.getLogger(Center16AndJula2015Controller.class.getName()).log(Level.SEVERE, null, ex); } } } return programeStatus; }
From source file:com.cms.utils.ExcelReader.java
public static void copyCell(HSSFCell oldCell, HSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) { if (styleMap != null) { if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) { newCell.setCellStyle(oldCell.getCellStyle()); } else {/* w ww . j av a 2s. c o m*/ int stHashCode = oldCell.getCellStyle().hashCode(); HSSFCellStyle newCellStyle = styleMap.get(stHashCode); if (newCellStyle == null) { newCellStyle = newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); styleMap.put(stHashCode, newCellStyle); } newCell.setCellStyle(newCellStyle); } } switch (oldCell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: newCell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case HSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } }
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static void copyCell(HSSFWorkbook destwb, HSSFCell dest, HSSFWorkbook srcwb, HSSFCell src) { if (src == null) { dest.setCellType(HSSFCell.CELL_TYPE_BLANK); return;/*www . j av a 2 s . co m*/ } if (src.getCellComment() != null) dest.setCellComment(src.getCellComment()); if (src.getCellStyle() != null) { HSSFCellStyle nstyle = findStyle(src.getCellStyle(), srcwb, destwb); if (nstyle == null) { nstyle = destwb.createCellStyle(); copyCellStyle(destwb, nstyle, srcwb, src.getCellStyle()); } dest.setCellStyle(nstyle); } dest.setCellType(src.getCellType()); switch (src.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_BOOLEAN: dest.setCellValue(src.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: dest.setCellFormula(src.getCellFormula()); break; case HSSFCell.CELL_TYPE_ERROR: dest.setCellErrorValue(src.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: dest.setCellValue(src.getNumericCellValue()); break; default: dest.setCellValue(new HSSFRichTextString(src.getRichStringCellValue().getString())); break; } }
From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
protected void setValueToCell(HSSFCell resultCell, String cellValue, int cellType) { if (StringUtils.isNotEmpty(cellValue)) { switch (cellType) { case HSSFCell.CELL_TYPE_FORMULA: resultCell.setCellFormula(cellValue); break; case HSSFCell.CELL_TYPE_STRING: resultCell.setCellValue(new HSSFRichTextString(cellValue)); break; default://from w w w .j a v a 2 s.c o m resultCell.setCellValue(cellValue); break; } } else { resultCell.setCellType(HSSFCell.CELL_TYPE_BLANK); } }
From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
protected void updateFormulas(Area templateArea, Area dependentResultArea) { HSSFSheet templateSheet = getTemplateSheetForRangeName(templateWorkbook, templateArea.getName()); HSSFSheet resultSheet = templateToResultSheetsMapping.get(templateSheet); AreaReference area = dependentResultArea.toAreaReference(); for (CellReference cell : area.getAllReferencedCells()) { HSSFCell resultCell = getCellFromReference(cell, resultSheet); if (resultCell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { Ptg[] ptgs = HSSFFormulaParser.parse(resultCell.getCellFormula(), resultWorkbook); for (Ptg ptg : ptgs) { if (ptg instanceof AreaPtg) { areaDependencyManager.updateAreaPtg(templateArea, dependentResultArea, (AreaPtg) ptg); } else if (ptg instanceof RefPtg) { areaDependencyManager.updateRefPtg(templateArea, dependentResultArea, (RefPtg) ptg); }// w w w. j a v a2 s .c om } String calculatedFormula = HSSFFormulaParser.toFormulaString(templateWorkbook, ptgs); resultCell.setCellFormula(calculatedFormula); } } }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
private void generarTotal3(HSSFRow rowTotal3, int indexFinGrupo, CellStyle style, HSSFCell cellTerr, HSSFSheet sheet) {/*w ww.j a v a2 s . c o m*/ HSSFCell cellTotal3Territorio = rowTotal3.createCell(indexCol); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 1) + "$" + indexRow)); cellTotal3Territorio.setCellValue("Part. De Emb / " + cellTerr.getStringCellValue()); cellTotal3Territorio.setCellStyle(styleTerritorioTotal3); HSSFCell cellTotal3Territorio1 = rowTotal3.createCell(indexCol + 1); cellTotal3Territorio1.setCellStyle(styleTerritorioTotal3); HSSFCell cellTotal3Boletas = rowTotal3.createCell(indexCol + 2); cellTotal3Boletas.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Red20g = rowTotal3.createCell(indexCol + 3); cellTotal3Red20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Red20g.setCellFormula(CellReference.convertNumToColString(indexCol + 3) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Red20g.setCellStyle(style); HSSFCell cellTotal3Red20c = rowTotal3.createCell(indexCol + 4); cellTotal3Red20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Blue20g = rowTotal3.createCell(indexCol + 5); cellTotal3Blue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Blue20g.setCellFormula(CellReference.convertNumToColString(indexCol + 5) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Blue20g.setCellStyle(style); HSSFCell cellTotal3Blue20c = rowTotal3.createCell(indexCol + 6); cellTotal3Blue20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Green20g = rowTotal3.createCell(indexCol + 7); cellTotal3Green20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Green20g.setCellFormula(CellReference.convertNumToColString(indexCol + 7) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Green20g.setCellStyle(style); HSSFCell cellTotal3Green20c = rowTotal3.createCell(indexCol + 8); cellTotal3Green20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Tres20g = rowTotal3.createCell(indexCol + 9); cellTotal3Tres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Tres20g.setCellFormula(CellReference.convertNumToColString(indexCol + 9) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Tres20g.setCellStyle(style); HSSFCell cellTotal3Tres20c = rowTotal3.createCell(indexCol + 10); cellTotal3Tres20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Duo20g = rowTotal3.createCell(indexCol + 11); cellTotal3Duo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Duo20g.setCellFormula(CellReference.convertNumToColString(indexCol + 11) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Duo20g.setCellStyle(style); HSSFCell cellTotal3Duo20c = rowTotal3.createCell(indexCol + 12); cellTotal3Duo20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Red10g = rowTotal3.createCell(indexCol + 13); cellTotal3Red10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Red10g.setCellFormula(CellReference.convertNumToColString(indexCol + 13) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Red10g.setCellStyle(style); HSSFCell cellTotal3Red10c = rowTotal3.createCell(indexCol + 14); cellTotal3Red10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Blue10g = rowTotal3.createCell(indexCol + 15); cellTotal3Blue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Blue10g.setCellFormula(CellReference.convertNumToColString(indexCol + 15) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Blue10g.setCellStyle(style); HSSFCell cellTotal3Blue10c = rowTotal3.createCell(indexCol + 16); cellTotal3Blue10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Green10g = rowTotal3.createCell(indexCol + 17); cellTotal3Green10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Green10g.setCellFormula(CellReference.convertNumToColString(indexCol + 17) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Green10g.setCellStyle(style); HSSFCell cellTotal3Green10c = rowTotal3.createCell(indexCol + 18); cellTotal3Green10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Tres10g = rowTotal3.createCell(indexCol + 19); cellTotal3Tres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Tres10g.setCellFormula(CellReference.convertNumToColString(indexCol + 19) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Tres10g.setCellStyle(style); HSSFCell cellTotal3Tres10c = rowTotal3.createCell(indexCol + 20); cellTotal3Tres10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3Duo10g = rowTotal3.createCell(indexCol + 21); cellTotal3Duo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3Duo10g.setCellFormula(CellReference.convertNumToColString(indexCol + 21) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3Duo10g.setCellStyle(style); HSSFCell cellTotal3Duo10c = rowTotal3.createCell(indexCol + 22); cellTotal3Duo10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3SM20g = rowTotal3.createCell(indexCol + 23); cellTotal3SM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3SM20g.setCellFormula(CellReference.convertNumToColString(indexCol + 23) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3SM20g.setCellStyle(style); HSSFCell cellTotal3SM20c = rowTotal3.createCell(indexCol + 24); cellTotal3SM20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3SM10g = rowTotal3.createCell(indexCol + 25); cellTotal3SM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3SM10g.setCellFormula(CellReference.convertNumToColString(indexCol + 25) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3SM10g.setCellStyle(style); HSSFCell cellTotal3SM10c = rowTotal3.createCell(indexCol + 26); cellTotal3SM10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3SMSoftg = rowTotal3.createCell(indexCol + 27); cellTotal3SMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3SMSoftg.setCellFormula(CellReference.convertNumToColString(indexCol + 27) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3SMSoftg.setCellStyle(style); HSSFCell cellTotal3SMSoftc = rowTotal3.createCell(indexCol + 28); cellTotal3SMSoftc.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3K20g = rowTotal3.createCell(indexCol + 29); cellTotal3K20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3K20g.setCellFormula(CellReference.convertNumToColString(indexCol + 29) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3K20g.setCellStyle(style); HSSFCell cellTotal3K20c = rowTotal3.createCell(indexCol + 30); cellTotal3K20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3K10g = rowTotal3.createCell(indexCol + 31); cellTotal3K10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3K10g.setCellFormula(CellReference.convertNumToColString(indexCol + 31) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3K10g.setCellStyle(style); HSSFCell cellTotal3K10c = rowTotal3.createCell(indexCol + 32); cellTotal3K10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3KSoftg = rowTotal3.createCell(indexCol + 33); cellTotal3KSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3KSoftg.setCellFormula(CellReference.convertNumToColString(indexCol + 33) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3KSoftg.setCellStyle(style); HSSFCell cellTotal3KSoftc = rowTotal3.createCell(indexCol + 34); cellTotal3KSoftc.setCellStyle(styleCantidadAzul); HSSFCell cellTotal3g = rowTotal3.createCell(indexCol + 35); cellTotal3g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal3g.setCellFormula(CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1) + "/$" + CellReference.convertNumToColString(indexCol + 35) + (indexFinGrupo + 1)); cellTotal3g.setCellStyle(style); HSSFCell cellTotal3c = rowTotal3.createCell(indexCol + 36); cellTotal3c.setCellStyle(styleCantidadGris); HSSFCell cellTotal3caja = rowTotal3.createCell(indexCol + 37); cellTotal3caja.setCellStyle(styleCantidadNegrita); HSSFCell cellTotal3gr = rowTotal3.createCell(indexCol + 38); cellTotal3gr.setCellStyle(styleCantidadNegrita); }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
private void generarTotal2(HSSFRow rowTotal2, int indexFinGrupo, String[] aColsGrue, HSSFCell cellTerr) { HSSFCell cellTotal2Territorio = rowTotal2.createCell(indexCol); cellTotal2Territorio.setCellValue("Total " + cellTerr.getStringCellValue() + " - Cajas"); cellTotal2Territorio.setCellStyle(styleTerritorioTotal1); HSSFCell cellTotal2Boletas = rowTotal2.createCell(indexCol + 2); cellTotal2Boletas.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Red20g = rowTotal2.createCell(indexCol + 3); cellTotal2Red20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Red20g .setCellFormula(CellReference.convertNumToColString(indexCol + 3) + (indexFinGrupo + 1) + "/50"); cellTotal2Red20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Red20c = rowTotal2.createCell(indexCol + 4); cellTotal2Red20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Blue20g = rowTotal2.createCell(indexCol + 5); cellTotal2Blue20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Blue20g//from ww w . j a v a 2s .c o m .setCellFormula(CellReference.convertNumToColString(indexCol + 5) + (indexFinGrupo + 1) + "/50"); cellTotal2Blue20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Blue20c = rowTotal2.createCell(indexCol + 6); cellTotal2Blue20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Green20g = rowTotal2.createCell(indexCol + 7); cellTotal2Green20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Green20g .setCellFormula(CellReference.convertNumToColString(indexCol + 7) + (indexFinGrupo + 1) + "/50"); cellTotal2Green20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Green20c = rowTotal2.createCell(indexCol + 8); cellTotal2Green20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Tres20g = rowTotal2.createCell(indexCol + 9); cellTotal2Tres20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Tres20g .setCellFormula(CellReference.convertNumToColString(indexCol + 9) + (indexFinGrupo + 1) + "/50"); cellTotal2Tres20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Tres20c = rowTotal2.createCell(indexCol + 10); cellTotal2Tres20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Duo20g = rowTotal2.createCell(indexCol + 11); cellTotal2Duo20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Duo20g .setCellFormula(CellReference.convertNumToColString(indexCol + 11) + (indexFinGrupo + 1) + "/50"); cellTotal2Duo20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Duo20c = rowTotal2.createCell(indexCol + 12); cellTotal2Duo20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Red10g = rowTotal2.createCell(indexCol + 13); cellTotal2Red10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Red10g .setCellFormula(CellReference.convertNumToColString(indexCol + 13) + (indexFinGrupo + 1) + "/50"); cellTotal2Red10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Red10c = rowTotal2.createCell(indexCol + 14); cellTotal2Red10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Blue10g = rowTotal2.createCell(indexCol + 15); cellTotal2Blue10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Blue10g .setCellFormula(CellReference.convertNumToColString(indexCol + 15) + (indexFinGrupo + 1) + "/50"); cellTotal2Blue10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Blue10c = rowTotal2.createCell(indexCol + 16); cellTotal2Blue10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Green10g = rowTotal2.createCell(indexCol + 17); cellTotal2Green10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Green10g .setCellFormula(CellReference.convertNumToColString(indexCol + 17) + (indexFinGrupo + 1) + "/50"); cellTotal2Green10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Green10c = rowTotal2.createCell(indexCol + 18); cellTotal2Green10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Tres10g = rowTotal2.createCell(indexCol + 19); cellTotal2Tres10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Tres10g .setCellFormula(CellReference.convertNumToColString(indexCol + 19) + (indexFinGrupo + 1) + "/50"); cellTotal2Tres10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Tres10c = rowTotal2.createCell(indexCol + 20); cellTotal2Tres10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Duo10g = rowTotal2.createCell(indexCol + 21); cellTotal2Duo10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2Duo10g .setCellFormula(CellReference.convertNumToColString(indexCol + 21) + (indexFinGrupo + 1) + "/50"); cellTotal2Duo10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2Duo10c = rowTotal2.createCell(indexCol + 22); cellTotal2Duo10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2SM20g = rowTotal2.createCell(indexCol + 23); cellTotal2SM20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2SM20g .setCellFormula(CellReference.convertNumToColString(indexCol + 23) + (indexFinGrupo + 1) + "/50"); cellTotal2SM20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2SM20c = rowTotal2.createCell(indexCol + 24); cellTotal2SM20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2SM10g = rowTotal2.createCell(indexCol + 25); cellTotal2SM10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2SM10g .setCellFormula(CellReference.convertNumToColString(indexCol + 25) + (indexFinGrupo + 1) + "/50"); cellTotal2SM10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2SM10c = rowTotal2.createCell(indexCol + 26); cellTotal2SM10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2SMSoftg = rowTotal2.createCell(indexCol + 27); cellTotal2SMSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2SMSoftg .setCellFormula(CellReference.convertNumToColString(indexCol + 27) + (indexFinGrupo + 1) + "/50"); cellTotal2SMSoftg.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2SMSoftc = rowTotal2.createCell(indexCol + 28); cellTotal2SMSoftc.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2K20g = rowTotal2.createCell(indexCol + 29); cellTotal2K20g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2K20g .setCellFormula(CellReference.convertNumToColString(indexCol + 29) + (indexFinGrupo + 1) + "/50"); cellTotal2K20g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2K20c = rowTotal2.createCell(indexCol + 30); cellTotal2K20c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2K10g = rowTotal2.createCell(indexCol + 31); cellTotal2K10g.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2K10g .setCellFormula(CellReference.convertNumToColString(indexCol + 31) + (indexFinGrupo + 1) + "/50"); cellTotal2K10g.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2K10c = rowTotal2.createCell(indexCol + 32); cellTotal2K10c.setCellStyle(styleCantidadAzul); HSSFCell cellTotal2KSoftg = rowTotal2.createCell(indexCol + 33); cellTotal2KSoftg.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2KSoftg .setCellFormula(CellReference.convertNumToColString(indexCol + 33) + (indexFinGrupo + 1) + "/50"); cellTotal2KSoftg.setCellStyle(styleCantidadAzul); HSSFCell cellTotal22KSoftc = rowTotal2.createCell(indexCol + 34); cellTotal22KSoftc.setCellStyle(styleCantidadAzul); String formTotal2Cajas = ""; for (int i = 0; i < aColsGrue.length; i++) { formTotal2Cajas += aColsGrue[i] + (indexFinGrupo + 2) + "+"; } formTotal2Cajas += "0"; HSSFCell cellTotal2g = rowTotal2.createCell(indexCol + 35); cellTotal2g.setCellStyle(styleCantidadGris); HSSFCell cellTotal2c = rowTotal2.createCell(indexCol + 36); cellTotal2c.setCellStyle(styleCantidadGris); HSSFCell cellTotal2caja = rowTotal2.createCell(indexCol + 37); cellTotal2caja.setCellType(HSSFCell.CELL_TYPE_FORMULA); cellTotal2caja.setCellFormula(formTotal2Cajas); cellTotal2caja.setCellStyle(styleCantidadNegrita); HSSFCell cellTotal2gr = rowTotal2.createCell(indexCol + 38); cellTotal2gr.setCellStyle(styleCantidadNegrita); }