List of usage examples for org.apache.poi.ss.usermodel Cell setCellFormula
void setCellFormula(String formula) throws FormulaParseException, IllegalStateException;
From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.ReviewerVendorPoXlsReportHelper.java
License:Apache License
/** Adds totals */ private void addTotalsForDellMatches(Workbook p_workbook, IntHolder p_row) throws Exception { Sheet theSheet = data.dellSheet;/*from ww w .ja va 2s .c om*/ // Totals int totalsRow = p_row.getValue() + 1; // skip a row Row totalRow = getRow(theSheet, totalsRow); Cell cell_A = getCell(totalRow, 0); cell_A.setCellValue(bundle.getString("lb_totals")); cell_A.setCellStyle(getSubTotalStyle(p_workbook)); theSheet.addMergedRegion(new CellRangeAddress(totalsRow, totalsRow, 0, 7)); setRegionStyle(theSheet, new CellRangeAddress(totalsRow, totalsRow, 0, 7), getSubTotalStyle(p_workbook)); int lastRow = p_row.getValue() - 2; int c = 8; // Word Count Cell cell_I = getCell(totalRow, c++); cell_I.setCellFormula("SUM(I5" + ":I" + lastRow + ")"); cell_I.setCellStyle(getSubTotalStyle(p_workbook)); // Invoice Cell cell_J = getCell(totalRow, c++); cell_J.setCellFormula("SUM(J5" + ":J" + lastRow + ")"); cell_J.setCellStyle(getTotalMoneyStyle(p_workbook)); // add an extra column for Dell Tracking Use Cell cell_K = getCell(totalRow, c++); cell_K.setCellValue(""); cell_K.setCellStyle(getTotalMoneyStyle(p_workbook)); }
From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.ReviewerVendorPoXlsReportHelper.java
License:Apache License
/** Adds the totals and sub-total formulas */ private void addTotalsForTradosMatches(Workbook p_workbook, IntHolder p_row) throws Exception { Sheet theSheet = data.tradosSheet;/* w ww.ja v a2s .c om*/ int row = p_row.getValue() + 1; // skip a row String title = bundle.getString("lb_totals"); Row totalRow = getRow(theSheet, row); Cell cell_A = getCell(totalRow, 0); cell_A.setCellValue(title); cell_A.setCellStyle(getSubTotalStyle(p_workbook)); theSheet.addMergedRegion(new CellRangeAddress(row, row, 0, 5)); setRegionStyle(theSheet, new CellRangeAddress(row, row, 0, 5), getSubTotalStyle(p_workbook)); int lastRow = p_row.getValue() - 2; // add in word count totals int c = 6; // word counts if (data.headers[0] != null) { Cell cell_G = getCell(totalRow, c++); cell_G.setCellFormula("SUM(G5:G" + lastRow + ")"); cell_G.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_H = getCell(totalRow, c++); cell_H.setCellFormula("SUM(H5:H" + lastRow + ")"); cell_H.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_I = getCell(totalRow, c++); cell_I.setCellFormula("SUM(I5:I" + lastRow + ")"); cell_I.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_J = getCell(totalRow, c++); cell_J.setCellFormula("SUM(J5:J" + lastRow + ")"); cell_J.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_K = getCell(totalRow, c++); cell_K.setCellFormula("SUM(K5:K" + lastRow + ")"); cell_K.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_L = getCell(totalRow, c++); cell_L.setCellFormula("SUM(L5:L" + lastRow + ")"); cell_L.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_M = getCell(totalRow, c++); cell_M.setCellFormula("SUM(M5:M" + lastRow + ")"); cell_M.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_N = getCell(totalRow, c++); cell_N.setCellFormula("SUM(N5:N" + lastRow + ")"); cell_N.setCellStyle(getSubTotalStyle(p_workbook)); // word count costs Cell cell_O = getCell(totalRow, c++); cell_O.setCellFormula("SUM(O5:O" + lastRow + ")"); cell_O.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_P = getCell(totalRow, c++); cell_P.setCellFormula("SUM(P5:P" + lastRow + ")"); cell_P.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_Q = getCell(totalRow, c++); cell_Q.setCellFormula("SUM(Q5:Q" + lastRow + ")"); cell_Q.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_R = getCell(totalRow, c++); cell_R.setCellFormula("SUM(R5:R" + lastRow + ")"); cell_R.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_S = getCell(totalRow, c++); cell_S.setCellFormula("SUM(S5:S" + lastRow + ")"); cell_S.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_T = getCell(totalRow, c++); cell_T.setCellFormula("SUM(T5:T" + lastRow + ")"); cell_T.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_U = getCell(totalRow, c++); cell_U.setCellFormula("SUM(U5:U" + lastRow + ")"); cell_U.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_V = getCell(totalRow, c++); cell_V.setCellFormula("SUM(V5:V" + lastRow + ")"); cell_V.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_W = getCell(totalRow, c++); cell_W.setCellFormula("SUM(W5:W" + lastRow + ")"); cell_W.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_X = getCell(totalRow, c++); cell_X.setCellFormula("SUM(X5:X" + lastRow + ")"); cell_X.setCellStyle(getTotalMoneyStyle(p_workbook)); } else { Cell cell_G = getCell(totalRow, c++); cell_G.setCellFormula("SUM(G5:G" + lastRow + ")"); cell_G.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_H = getCell(totalRow, c++); cell_H.setCellFormula("SUM(H5:H" + lastRow + ")"); cell_H.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_I = getCell(totalRow, c++); cell_I.setCellFormula("SUM(I5:I" + lastRow + ")"); cell_I.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_J = getCell(totalRow, c++); cell_J.setCellFormula("SUM(J5:J" + lastRow + ")"); cell_J.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_K = getCell(totalRow, c++); cell_K.setCellFormula("SUM(K5:K" + lastRow + ")"); cell_K.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_L = getCell(totalRow, c++); cell_L.setCellFormula("SUM(L5:L" + lastRow + ")"); cell_L.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_M = getCell(totalRow, c++); cell_M.setCellFormula("SUM(M5:M" + lastRow + ")"); cell_M.setCellStyle(getSubTotalStyle(p_workbook)); // word count costs Cell cell_N = getCell(totalRow, c++); cell_N.setCellFormula("SUM(N5:N" + lastRow + ")"); cell_N.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_O = getCell(totalRow, c++); cell_O.setCellFormula("SUM(O5:O" + lastRow + ")"); cell_O.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_P = getCell(totalRow, c++); cell_P.setCellFormula("SUM(P5:P" + lastRow + ")"); cell_P.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_Q = getCell(totalRow, c++); cell_Q.setCellFormula("SUM(Q5:Q" + lastRow + ")"); cell_Q.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_R = getCell(totalRow, c++); cell_R.setCellFormula("SUM(R5:R" + lastRow + ")"); cell_R.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_S = getCell(totalRow, c++); cell_S.setCellFormula("SUM(S5:S" + lastRow + ")"); cell_S.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_T = getCell(totalRow, c++); cell_T.setCellFormula("SUM(T5:T" + lastRow + ")"); cell_T.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_U = getCell(totalRow, c++); cell_U.setCellFormula("SUM(U5:U" + lastRow + ")"); cell_U.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_V = getCell(totalRow, c++); cell_V.setCellFormula("SUM(V5:V" + lastRow + ")"); cell_V.setCellStyle(getTotalMoneyStyle(p_workbook)); } // add an extra column for Dell Tracking Use Cell cell_Last = getCell(totalRow, c++); cell_Last.setCellValue(""); cell_Last.setCellStyle(getTotalMoneyStyle(p_workbook)); }
From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.VendorPOXlsReport.java
License:Apache License
/** Adds the totals and sub-total formulas */ private void addTotalsForDellMatches(Workbook p_workbook, MyData p_data, IntHolder p_row, ResourceBundle bundle) throws Exception { Sheet theSheet = p_data.dellSheet;/*from w w w . j a va2 s .co m*/ int row = p_row.getValue() + 1; // skip a row String title = bundle.getString("lb_totals"); Row theRow = getRow(theSheet, row); Cell cell_A = getCell(theRow, 0); cell_A.setCellValue(title); cell_A.setCellStyle(getSubTotalStyle(p_workbook)); theSheet.addMergedRegion(new CellRangeAddress(row, row, 0, 5)); setRegionStyle(theSheet, new CellRangeAddress(row, row, 0, 5), getSubTotalStyle(p_workbook)); int lastRow = p_row.getValue() - 2; // add in word count totals int c = 6; if (p_data.headers[0] != null) { // word counts Cell cell_G = getCell(theRow, c++); cell_G.setCellFormula("SUM(G5:G" + lastRow + ")"); cell_G.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_H = getCell(theRow, c++); cell_H.setCellFormula("SUM(H5:H" + lastRow + ")"); cell_H.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_I = getCell(theRow, c++); cell_I.setCellFormula("SUM(I5:I" + lastRow + ")"); cell_I.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_J = getCell(theRow, c++); cell_J.setCellFormula("SUM(J5:J" + lastRow + ")"); cell_J.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_K = getCell(theRow, c++); cell_K.setCellFormula("SUM(K5:K" + lastRow + ")"); cell_K.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_L = getCell(theRow, c++); cell_L.setCellFormula("SUM(L5:L" + lastRow + ")"); cell_L.setCellStyle(getSubTotalStyle(p_workbook)); // word count costs Cell cell_M = getCell(theRow, c++); cell_M.setCellFormula("SUM(M5:M" + lastRow + ")"); cell_M.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_N = getCell(theRow, c++); cell_N.setCellFormula("SUM(N5:N" + lastRow + ")"); cell_N.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_O = getCell(theRow, c++); cell_O.setCellFormula("SUM(O5:O" + lastRow + ")"); cell_O.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_P = getCell(theRow, c++); cell_P.setCellFormula("SUM(P5:P" + lastRow + ")"); cell_P.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_Q = getCell(theRow, c++); cell_Q.setCellFormula("SUM(Q5:Q" + lastRow + ")"); cell_Q.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_R = getCell(theRow, c++); cell_R.setCellFormula("SUM(R5:R" + lastRow + ")"); cell_R.setCellStyle(getTotalMoneyStyle(p_workbook)); } else { // word counts Cell cell_G = getCell(theRow, c++); cell_G.setCellFormula("SUM(G5:G" + lastRow + ")"); cell_G.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_H = getCell(theRow, c++); cell_H.setCellFormula("SUM(H5:H" + lastRow + ")"); cell_H.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_I = getCell(theRow, c++); cell_I.setCellFormula("SUM(I5:I" + lastRow + ")"); cell_I.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_J = getCell(theRow, c++); cell_J.setCellFormula("SUM(J5:J" + lastRow + ")"); cell_J.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_K = getCell(theRow, c++); cell_K.setCellFormula("SUM(K5:K" + lastRow + ")"); cell_K.setCellStyle(getSubTotalStyle(p_workbook)); // word count costs Cell cell_L = getCell(theRow, c++); cell_L.setCellFormula("SUM(L5:L" + lastRow + ")"); cell_L.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_M = getCell(theRow, c++); cell_M.setCellFormula("SUM(M5:M" + lastRow + ")"); cell_M.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_N = getCell(theRow, c++); cell_N.setCellFormula("SUM(N5:N" + lastRow + ")"); cell_N.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_O = getCell(theRow, c++); cell_O.setCellFormula("SUM(O5:O" + lastRow + ")"); cell_O.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_P = getCell(theRow, c++); cell_P.setCellFormula("SUM(P5:P" + lastRow + ")"); cell_P.setCellStyle(getTotalMoneyStyle(p_workbook)); } }
From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.VendorPOXlsReport.java
License:Apache License
/** Adds the totals and sub-total formulas */ private void addTotalsForTradosMatches(Workbook p_workbook, MyData p_data, IntHolder p_row, ResourceBundle bundle) throws Exception { Sheet theSheet = p_data.tradosSheet; int row = p_row.getValue() + 1; // skip a row String title = bundle.getString("lb_totals"); Row theRow = getRow(theSheet, row);/* w w w.ja v a 2 s.co m*/ Cell cell_A = getCell(theRow, 0); cell_A.setCellValue(title); cell_A.setCellStyle(getSubTotalStyle(p_workbook)); theSheet.addMergedRegion(new CellRangeAddress(row, row, 0, 5)); setRegionStyle(theSheet, new CellRangeAddress(row, row, 0, 5), getSubTotalStyle(p_workbook)); int lastRow = p_row.getValue() - 2; // add in word count totals int c = 6; if (p_data.headers[0] != null) { // word counts Cell cell_G = getCell(theRow, c++); cell_G.setCellFormula("SUM(G5:G" + lastRow + ")"); cell_G.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_H = getCell(theRow, c++); cell_H.setCellFormula("SUM(H5:H" + lastRow + ")"); cell_H.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_I = getCell(theRow, c++); cell_I.setCellFormula("SUM(I5:I" + lastRow + ")"); cell_I.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_J = getCell(theRow, c++); cell_J.setCellFormula("SUM(J5:J" + lastRow + ")"); cell_J.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_K = getCell(theRow, c++); cell_K.setCellFormula("SUM(K5:K" + lastRow + ")"); cell_K.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_L = getCell(theRow, c++); cell_L.setCellFormula("SUM(L5:L" + lastRow + ")"); cell_L.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_M = getCell(theRow, c++); cell_M.setCellFormula("SUM(M5:M" + lastRow + ")"); cell_M.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_N = getCell(theRow, c++); cell_N.setCellFormula("SUM(N5:N" + lastRow + ")"); cell_N.setCellStyle(getSubTotalStyle(p_workbook)); // word count costs Cell cell_O = getCell(theRow, c++); cell_O.setCellFormula("SUM(O5:O" + lastRow + ")"); cell_O.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_P = getCell(theRow, c++); cell_P.setCellFormula("SUM(P5:P" + lastRow + ")"); cell_P.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_Q = getCell(theRow, c++); cell_Q.setCellFormula("SUM(Q5:Q" + lastRow + ")"); cell_Q.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_R = getCell(theRow, c++); cell_R.setCellFormula("SUM(R5:R" + lastRow + ")"); cell_R.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_S = getCell(theRow, c++); cell_S.setCellFormula("SUM(S5:S" + lastRow + ")"); cell_S.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_T = getCell(theRow, c++); cell_T.setCellFormula("SUM(T5:T" + lastRow + ")"); cell_T.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_U = getCell(theRow, c++); cell_U.setCellFormula("SUM(U5:U" + lastRow + ")"); cell_U.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_V = getCell(theRow, c++); cell_V.setCellFormula("SUM(V5:V" + lastRow + ")"); cell_V.setCellStyle(getTotalMoneyStyle(p_workbook)); } else { // word counts Cell cell_G = getCell(theRow, c++); cell_G.setCellFormula("SUM(G5:G" + lastRow + ")"); cell_G.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_H = getCell(theRow, c++); cell_H.setCellFormula("SUM(H5:H" + lastRow + ")"); cell_H.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_I = getCell(theRow, c++); cell_I.setCellFormula("SUM(I5:I" + lastRow + ")"); cell_I.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_J = getCell(theRow, c++); cell_J.setCellFormula("SUM(J5:J" + lastRow + ")"); cell_J.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_K = getCell(theRow, c++); cell_K.setCellFormula("SUM(K5:K" + lastRow + ")"); cell_K.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_L = getCell(theRow, c++); cell_L.setCellFormula("SUM(L5:L" + lastRow + ")"); cell_L.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_M = getCell(theRow, c++); cell_M.setCellFormula("SUM(M5:M" + lastRow + ")"); cell_M.setCellStyle(getSubTotalStyle(p_workbook)); // word count costs Cell cell_N = getCell(theRow, c++); cell_N.setCellFormula("SUM(N5:N" + lastRow + ")"); cell_N.setCellStyle(getSubTotalStyle(p_workbook)); Cell cell_O = getCell(theRow, c++); cell_O.setCellFormula("SUM(O5:O" + lastRow + ")"); cell_O.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_P = getCell(theRow, c++); cell_P.setCellFormula("SUM(P5:P" + lastRow + ")"); cell_P.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_Q = getCell(theRow, c++); cell_Q.setCellFormula("SUM(Q5:Q" + lastRow + ")"); cell_Q.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_R = getCell(theRow, c++); cell_R.setCellFormula("SUM(R5:R" + lastRow + ")"); cell_R.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_S = getCell(theRow, c++); cell_S.setCellFormula("SUM(S5:S" + lastRow + ")"); cell_S.setCellStyle(getTotalMoneyStyle(p_workbook)); Cell cell_T = getCell(theRow, c++); cell_T.setCellFormula("SUM(T5:T" + lastRow + ")"); cell_T.setCellStyle(getTotalMoneyStyle(p_workbook)); } }
From source file:com.griffinslogistics.document.excel.BDLGenerator.java
/** * * @param sheet//from ww w . jav a 2 s . com * @param leftStyle * @param middleStyle * @param rightStyle * @param footerStyle * @return index of the last table row created */ private static int insertTableBody(Sheet sheet, CellStyle leftStyle, CellStyle middleStyle, CellStyle rightStyle, CellStyle footerStyle, List<BookBoxModel> bookBoxModels) { Integer index = 18; Integer currentBookTitleIndex = 1; Set<Integer> rowsToSum = new HashSet<Integer>(); Set<Integer> totalsToSum = new HashSet<Integer>(); String cellMergeString; String cellFormula; try { int currentBookNumber = bookBoxModels.get(0).getBookNumber(); for (int i = 0; i < bookBoxModels.size(); i++) { index++; BookBoxModel currentModel = bookBoxModels.get(i); Row row = sheet.createRow(index); if (currentBookNumber != currentModel.getBookNumber()) { for (int j = 2; j <= 4; j++) { row.createCell(j).setCellStyle(footerStyle); } addTotalTitleRow(row, index, sheet, footerStyle, currentBookTitleIndex, rowsToSum, totalsToSum); index++; currentBookTitleIndex++; row = sheet.createRow(index); } for (int j = 2; j <= 4; j++) { row.createCell(j).setCellStyle(middleStyle); } //Book Title Row Cell titleCell = row.createCell(1); cellMergeString = String.format("$B$%s:$E$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeString)); titleCell.setCellStyle(leftStyle); String isbn = currentModel.getISBN() != null ? currentModel.getISBN() : ""; String cellString = String.format("%s %s", currentModel.getTitle(), isbn); titleCell.setCellValue(cellString); Cell quantityCell = row.createCell(5); quantityCell.setCellStyle(middleStyle); quantityCell.setCellValue(Double.parseDouble(currentModel.getBooksCount().toString())); Cell boxCountCell = row.createCell(6); boxCountCell.setCellStyle(middleStyle); boxCountCell.setCellValue(Double.parseDouble(currentModel.getBoxesCount().toString())); Cell totalQuantityCell = row.createCell(7); totalQuantityCell.setCellStyle(rightStyle); cellFormula = String.format("F%s*G%s", index + 1, index + 1); totalQuantityCell.setCellFormula(cellFormula); Cell palettesCountCell = row.createCell(8); palettesCountCell.setCellStyle(rightStyle); // excel is not 0-based! rowsToSum.add(index + 1); currentBookNumber = currentModel.getBookNumber(); } index++; Row row = sheet.createRow(index); for (int i = 2; i <= 4; i++) { row.createCell(i).setCellStyle(footerStyle); } addTotalTitleRow(row, index, sheet, footerStyle, currentBookTitleIndex, rowsToSum, totalsToSum); //Total row index++; Row footerTotalRow = sheet.createRow(index); Cell footerTotalRowTitleCell = footerTotalRow.createCell(1); for (int i = 2; i <= 4; i++) { footerTotalRow.createCell(i).setCellStyle(footerStyle); } cellMergeString = String.format("$B$%s:$E$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeString)); footerTotalRowTitleCell.setCellStyle(footerStyle); footerTotalRowTitleCell.setCellValue("Total"); Cell footerTotalRowQuantityCell = footerTotalRow.createCell(5); footerTotalRowQuantityCell.setCellStyle(footerStyle); Cell footerTotalRowBoxCountCell = footerTotalRow.createCell(6); footerTotalRowBoxCountCell.setCellStyle(footerStyle); Cell footerTotalRowTotalQuantityCell = footerTotalRow.createCell(7); footerTotalRowTotalQuantityCell.setCellStyle(footerStyle); Cell footerTotalRowPalettesCountCell = footerTotalRow.createCell(8); footerTotalRowPalettesCountCell.setCellStyle(footerStyle); //build cell formulas StringBuilder totalBoxesCountformulaBuilder = new StringBuilder(); StringBuilder totalBooksCountformulaBuilder = new StringBuilder(); StringBuilder totalPaletsCountformulaBuilder = new StringBuilder(); // Example: SUM(H22;H25;H28;H31;H34) totalBoxesCountformulaBuilder.append("SUM("); totalBooksCountformulaBuilder.append("SUM("); totalPaletsCountformulaBuilder.append("SUM("); for (Integer integer : totalsToSum) { totalBoxesCountformulaBuilder.append("G").append(integer).append(","); totalBooksCountformulaBuilder.append("H").append(integer).append(","); totalPaletsCountformulaBuilder.append("I").append(integer).append(","); } totalBoxesCountformulaBuilder.deleteCharAt(totalBoxesCountformulaBuilder.length() - 1); totalBooksCountformulaBuilder.deleteCharAt(totalBooksCountformulaBuilder.length() - 1); totalPaletsCountformulaBuilder.deleteCharAt(totalPaletsCountformulaBuilder.length() - 1); totalBoxesCountformulaBuilder.append(")"); totalBooksCountformulaBuilder.append(")"); totalPaletsCountformulaBuilder.append(")"); footerTotalRowBoxCountCell.setCellFormula(totalBoxesCountformulaBuilder.toString()); footerTotalRowTotalQuantityCell.setCellFormula(totalBooksCountformulaBuilder.toString()); footerTotalRowPalettesCountCell.setCellFormula(totalPaletsCountformulaBuilder.toString()); } catch (Exception e) { e.printStackTrace(); } return index; }
From source file:com.griffinslogistics.document.excel.BDLGenerator.java
private static void addTotalTitleRow(Row row, Integer index, Sheet sheet, CellStyle footerStyle, Integer currentBookTitleIndex, Set<Integer> rowsToSum, Set<Integer> totalsToSum) { //Total Book Title Row Cell totalRowTitleCell = row.createCell(1); String cellMergeString = String.format("$B$%s:$E$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeString)); totalRowTitleCell.setCellStyle(footerStyle); totalRowTitleCell.setCellValue("Total Title " + currentBookTitleIndex++); Cell totalRowQuantityCell = row.createCell(5); totalRowQuantityCell.setCellStyle(footerStyle); Cell totalRowBoxCountCell = row.createCell(6); totalRowBoxCountCell.setCellStyle(footerStyle); Cell totalRowTotalQuantityCell = row.createCell(7); totalRowTotalQuantityCell.setCellStyle(footerStyle); Cell totalRowPalettesCountCell = row.createCell(8); totalRowPalettesCountCell.setCellStyle(footerStyle); //build cell formulas StringBuilder totalBoxesCountformulaBuilder = new StringBuilder(); StringBuilder totalBooksCountformulaBuilder = new StringBuilder(); StringBuilder totalPaletsCountformulaBuilder = new StringBuilder(); // Example: SUM(H22;H25;H28;H31;H34) totalBoxesCountformulaBuilder.append("SUM("); totalBooksCountformulaBuilder.append("SUM("); totalPaletsCountformulaBuilder.append("SUM("); for (Integer integer : rowsToSum) { totalBoxesCountformulaBuilder.append("G").append(integer).append(","); totalBooksCountformulaBuilder.append("H").append(integer).append(","); totalPaletsCountformulaBuilder.append("I").append(integer).append(","); }/* w ww . ja v a2 s . c om*/ totalBoxesCountformulaBuilder.deleteCharAt(totalBoxesCountformulaBuilder.length() - 1); totalBooksCountformulaBuilder.deleteCharAt(totalBooksCountformulaBuilder.length() - 1); totalPaletsCountformulaBuilder.deleteCharAt(totalPaletsCountformulaBuilder.length() - 1); totalBoxesCountformulaBuilder.append(")"); totalBooksCountformulaBuilder.append(")"); totalPaletsCountformulaBuilder.append(")"); totalRowBoxCountCell.setCellFormula(totalBoxesCountformulaBuilder.toString()); totalRowTotalQuantityCell.setCellFormula(totalBooksCountformulaBuilder.toString()); totalRowPalettesCountCell.setCellFormula(totalPaletsCountformulaBuilder.toString()); // excel is not 0-based totalsToSum.add(index + 1); rowsToSum.clear(); }
From source file:com.griffinslogistics.document.excel.CMRGenerator.java
private static int generatePoints10Till15(Long totalBoxesCount, double weight, XSSFSheet sheet, Map<String, CellStyle> styles, int currentRow) { currentRow++;// ww w . j a v a 2 s .co m Row row31 = sheet.createRow(currentRow); sheet.addMergedRegion(CellRangeAddress.valueOf("$B$31:$E$31")); sheet.addMergedRegion(CellRangeAddress.valueOf("$F$31:$G$31")); sheet.addMergedRegion(CellRangeAddress.valueOf("$H$31:$I$31")); sheet.addMergedRegion(CellRangeAddress.valueOf("$B$32:$E$32")); sheet.addMergedRegion(CellRangeAddress.valueOf("$F$32:$G$32")); sheet.addMergedRegion(CellRangeAddress.valueOf("$H$32:$I$32")); Cell labels10Till13Cell = row31.createCell(1); labels10Till13Cell.setCellValue(LABELS_10_TILL_13_BULGARIAN); labels10Till13Cell.setCellStyle(styles.get(LABEL_TOP_STYLE)); Cell weightLabelCell1 = row31.createCell(5); weightLabelCell1.setCellValue(LABEL_WEIGHT_BULGARIAN); weightLabelCell1.setCellStyle(styles.get(LABEL_TOP_STYLE)); Cell volumeLabelCell1 = row31.createCell(7); volumeLabelCell1.setCellValue(LABEL_VOLUME); volumeLabelCell1.setCellStyle(styles.get(LABEL_TOP_STYLE)); row31.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); currentRow++; Row row32 = sheet.createRow(currentRow); Cell labels10Till13Cel2 = row32.createCell(1); labels10Till13Cel2.setCellValue(LABEL_10_TILL_13_ENGLISH); labels10Till13Cel2.setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); Cell weightLabelCell2 = row32.createCell(5); weightLabelCell2.setCellValue(LABEL_WEIGHT_ENGLISH); weightLabelCell2.setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); Cell volumeLabelCell2 = row32.createCell(7); volumeLabelCell2.setCellValue(LABEL_VOLUME_ENGLISH); volumeLabelCell2.setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row32.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); // DYNAMIC CONTENT FOR BOOKS sheet.addMergedRegion(CellRangeAddress.valueOf("$D$33:$E$33")); sheet.addMergedRegion(CellRangeAddress.valueOf("$F$33:$G$33")); sheet.addMergedRegion(CellRangeAddress.valueOf("$H$33:$I$33")); currentRow++; Row row33 = sheet.createRow(currentRow); Cell packagesCountCell = row33.createCell(1); packagesCountCell.setCellValue(totalBoxesCount); packagesCountCell.setCellStyle(styles.get(CONTENT_MIDDLE_ALLIGN_RIGHT_STYLE)); Cell packagingMethodCell = row33.createCell(2); packagingMethodCell.setCellValue("CARTONS"); packagingMethodCell.setCellStyle(styles.get(CONTENT_MIDDLE_NO_BORDERS_STYLE)); Cell goodsNatureCell = row33.createCell(3); goodsNatureCell.setCellValue("BOOKS"); goodsNatureCell.setCellStyle(styles.get(CONTENT_MIDDLE_ALLIGN_CENTER_STYLE)); Cell grossWeightCell = row33.createCell(5); try { BigDecimal bd = new BigDecimal(weight); bd = bd.setScale(2, RoundingMode.HALF_UP); weight = bd.doubleValue(); } catch (Exception e) { System.out.println(e.getMessage()); } grossWeightCell.setCellValue(weight); grossWeightCell.setCellStyle(styles.get(CONTENT_MIDDLE_ALLIGN_CENTER_STYLE)); for (int j = 6; j <= 8; j++) { row33.createCell(j).setCellStyle(styles.get(CONTENT_MIDDLE_ALLIGN_CENTER_STYLE)); } currentRow++; Row totalsRow = sheet.createRow(currentRow); String cellMergeString = String.format("$B$%s:$E$%s", currentRow + 1, currentRow + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeString)); cellMergeString = String.format("$F$%s:$G$%s", currentRow + 1, currentRow + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeString)); cellMergeString = String.format("$H$%s:$I$%s", currentRow + 1, currentRow + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeString)); Cell totalsLabelCell = totalsRow.createCell(1); totalsLabelCell.setCellValue(LABEL_TOTALS_10_TILL_15); totalsLabelCell.setCellStyle(styles.get(LABEL_BOTTOM_STYLE)); for (int i = 2; i <= 4; i++) { totalsRow.createCell(i).setCellStyle(styles.get(LABEL_BOTTOM_STYLE)); } Cell totalWeightCell = totalsRow.createCell(5); totalWeightCell.setCellStyle(styles.get(CONTENT_BOTTOM_ALLIGN_CENTER_STYLE)); totalWeightCell.setCellFormula("SUM(F33)"); totalsRow.createCell(6).setCellStyle(styles.get(CONTENT_BOTTOM_ALLIGN_CENTER_STYLE)); Cell totalVolumeCell = totalsRow.createCell(7); totalVolumeCell.setCellStyle(styles.get(CONTENT_BOTTOM_ALLIGN_CENTER_STYLE)); totalVolumeCell.setCellFormula("SUM(H33)"); totalsRow.createCell(8).setCellStyle(styles.get(CONTENT_BOTTOM_ALLIGN_CENTER_STYLE)); return currentRow; }
From source file:com.griffinslogistics.excel.BDLGenerator.java
/** * * @param sheet/*from w w w. j a va 2 s .c o m*/ * @param leftStyle * @param middleStyle * @param rightStyle * @param footerStyle * @return index of the last table row created */ private static int insertTableBody(Sheet sheet, CellStyle leftStyle, CellStyle middleStyle, CellStyle rightStyle, CellStyle footerStyle, List<BookBoxModel> bookBoxModels) { Integer index = 18; Integer currentBookTitleIndex = 1; Set<Integer> rowsToSum = new HashSet<Integer>(); Set<Integer> totalsToSum = new HashSet<Integer>(); String cellMergeString; String cellFormula; try { int currentBookNumber = bookBoxModels.get(0).getBookNumber(); for (int i = 0; i < bookBoxModels.size(); i++) { index++; BookBoxModel currentModel = bookBoxModels.get(i); Row row = sheet.createRow(index); if (currentBookNumber != currentModel.getBookNumber()) { for (int j = 2; j <= 4; j++) { row.createCell(j).setCellStyle(footerStyle); } addTotalTitleRow(row, index, sheet, footerStyle, currentBookTitleIndex, rowsToSum, totalsToSum); index++; currentBookTitleIndex++; row = sheet.createRow(index); } for (int j = 2; j <= 4; j++) { row.createCell(j).setCellStyle(middleStyle); } //Book Title Row Cell titleCell = row.createCell(1); cellMergeString = String.format("$B$%s:$E$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeString)); titleCell.setCellStyle(leftStyle); titleCell.setCellValue(currentModel.getTitle()); Cell quantityCell = row.createCell(5); quantityCell.setCellStyle(middleStyle); quantityCell.setCellValue(Double.parseDouble(currentModel.getBooksCount().toString())); Cell boxCountCell = row.createCell(6); boxCountCell.setCellStyle(middleStyle); boxCountCell.setCellValue(Double.parseDouble(currentModel.getBoxesCount().toString())); Cell totalQuantityCell = row.createCell(7); totalQuantityCell.setCellStyle(rightStyle); cellFormula = String.format("F%s*G%s", index + 1, index + 1); totalQuantityCell.setCellFormula(cellFormula); Cell palettesCountCell = row.createCell(8); palettesCountCell.setCellStyle(rightStyle); // excel is not 0-based! rowsToSum.add(index + 1); currentBookNumber = currentModel.getBookNumber(); } index++; Row row = sheet.createRow(index); for (int i = 2; i <= 4; i++) { row.createCell(i).setCellStyle(footerStyle); } addTotalTitleRow(row, index, sheet, footerStyle, currentBookTitleIndex, rowsToSum, totalsToSum); //Total row index++; Row footerTotalRow = sheet.createRow(index); Cell footerTotalRowTitleCell = footerTotalRow.createCell(1); for (int i = 2; i <= 4; i++) { footerTotalRow.createCell(i).setCellStyle(footerStyle); } cellMergeString = String.format("$B$%s:$E$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(cellMergeString)); footerTotalRowTitleCell.setCellStyle(footerStyle); footerTotalRowTitleCell.setCellValue("Total"); Cell footerTotalRowQuantityCell = footerTotalRow.createCell(5); footerTotalRowQuantityCell.setCellStyle(footerStyle); Cell footerTotalRowBoxCountCell = footerTotalRow.createCell(6); footerTotalRowBoxCountCell.setCellStyle(footerStyle); Cell footerTotalRowTotalQuantityCell = footerTotalRow.createCell(7); footerTotalRowTotalQuantityCell.setCellStyle(footerStyle); Cell footerTotalRowPalettesCountCell = footerTotalRow.createCell(8); footerTotalRowPalettesCountCell.setCellStyle(footerStyle); //build cell formulas StringBuilder totalBoxesCountformulaBuilder = new StringBuilder(); StringBuilder totalBooksCountformulaBuilder = new StringBuilder(); StringBuilder totalPaletsCountformulaBuilder = new StringBuilder(); // Example: SUM(H22;H25;H28;H31;H34) totalBoxesCountformulaBuilder.append("SUM("); totalBooksCountformulaBuilder.append("SUM("); totalPaletsCountformulaBuilder.append("SUM("); for (Integer integer : totalsToSum) { totalBoxesCountformulaBuilder.append("G").append(integer).append(","); totalBooksCountformulaBuilder.append("H").append(integer).append(","); totalPaletsCountformulaBuilder.append("I").append(integer).append(","); } totalBoxesCountformulaBuilder.deleteCharAt(totalBoxesCountformulaBuilder.length() - 1); totalBooksCountformulaBuilder.deleteCharAt(totalBooksCountformulaBuilder.length() - 1); totalPaletsCountformulaBuilder.deleteCharAt(totalPaletsCountformulaBuilder.length() - 1); totalBoxesCountformulaBuilder.append(")"); totalBooksCountformulaBuilder.append(")"); totalPaletsCountformulaBuilder.append(")"); footerTotalRowBoxCountCell.setCellFormula(totalBoxesCountformulaBuilder.toString()); footerTotalRowTotalQuantityCell.setCellFormula(totalBooksCountformulaBuilder.toString()); footerTotalRowPalettesCountCell.setCellFormula(totalPaletsCountformulaBuilder.toString()); } catch (Exception e) { e.printStackTrace(); } return index; }
From source file:com.helger.poi.excel.WorkbookCreationHelper.java
License:Apache License
/** * @param sFormula//w w w.ja v a 2 s . com * The formula to be set. May be <code>null</code> to set no formula. * @return A new cell in the current row of the current sheet with the passed * formula */ @Nonnull public Cell addCellFormula(@Nullable final String sFormula) { final Cell aCell = addCell(); aCell.setCellType(Cell.CELL_TYPE_FORMULA); aCell.setCellFormula(sFormula); return aCell; }
From source file:com.kafeidev.test.BusinessPlan.java
License:Apache License
@Test public static void main(String[] args) throws Exception { Workbook wb;/*from www . java 2 s .c o m*/ 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("Business Plan"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } //columns for 11 weeks starting from 9-Nov Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); // { // String inputDate = "2010-Nov-04 01:32:27"; // Date date = new SimpleDateFormat("yyyy-MMM-dd HH:mm:ss", new Locale("en,EN")).parse(inputDate); // String str= new SimpleDateFormat("dd.MMM.yyyy HH:mm:ss").format(date); // System.out.println("data:"+str); // // } calendar.setTime(fmt.parse("19-Nov")); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1; for (int i = 0; i < data.length; i++, rownum++) { row = sheet.createRow(rownum); if (data[i] == null) continue; for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { calendar.setTime(fmt.parse(data[i][j])); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } //group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(3, 4); // Write the output to a file String file = "businessplan.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }