List of usage examples for org.apache.poi.ss.usermodel Sheet addMergedRegion
int addMergedRegion(CellRangeAddress region);
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; int row = p_row.getValue() + 1; // skip a row String title = bundle.getString("lb_totals"); Row theRow = getRow(theSheet, row);/* www . j a va 2 s . c om*/ 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 ww .j a va 2 s . com 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
private static void insertDate(Sheet sheet, CellStyle style) { Row dateRow = sheet.createRow(6);/*from ww w . ja va 2 s . c om*/ sheet.addMergedRegion(CellRangeAddress.valueOf("$B$7:$C$7")); Locale frenchLocale = new Locale("fr", "FR"); SimpleDateFormat dateFormat = new SimpleDateFormat("dd MMMM yyyy", frenchLocale); String dateString = dateFormat.format(new Date()); Cell dateCell = dateRow.createCell(1); dateCell.setCellStyle(style); dateCell.setCellValue(dateString); }
From source file:com.griffinslogistics.document.excel.BDLGenerator.java
private static void insertContacts(Sheet sheet, CellStyle pulsioNameStyle, CellStyle contactsStyle, Pulsiodetails pulsiodetails) {/*w w w . ja va 2s . c o m*/ Row pulsioNameRow = sheet.createRow(9); Cell pulsioNameCell = pulsioNameRow.createCell(1); pulsioNameCell.setCellValue("PULSIO"); pulsioNameCell.setCellStyle(pulsioNameStyle); Row firstContactsRow = sheet.createRow(10); Cell firstContactsCell = firstContactsRow.createCell(1); firstContactsCell.setCellValue("Contact: " + pulsiodetails.getContact1()); firstContactsCell.setCellStyle(contactsStyle); Row secondContactsRow = sheet.createRow(11); Cell secondContactsCell = secondContactsRow.createCell(1); secondContactsCell.setCellValue(pulsiodetails.getContact2()); secondContactsCell.setCellStyle(contactsStyle); sheet.addMergedRegion(CellRangeAddress.valueOf("$B$11:$C$11")); sheet.addMergedRegion(CellRangeAddress.valueOf("$B$12:$C$12")); }
From source file:com.griffinslogistics.document.excel.BDLGenerator.java
private static void insertTitle(Sheet sheet, CellStyle style) { Row titleRow = sheet.createRow(14);/* w w w. j a v a 2 s . co m*/ Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Bon de livraison"); titleCell.setCellStyle(style); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$15:$J$15")); }
From source file:com.griffinslogistics.document.excel.BDLGenerator.java
private static void insertTableHeaders(Sheet sheet, CellStyle headerRowLeftCellStyleStyle, CellStyle headerRowMiddleCellStyle, CellStyle headerRowRightCellStyle) { Row tableHeadersRow = sheet.createRow(18); tableHeadersRow.setHeightInPoints((short) 35); sheet.addMergedRegion(CellRangeAddress.valueOf("$B$19:$E$19")); Cell titleCell = tableHeadersRow.createCell(1); titleCell.setCellValue("Titre"); titleCell.setCellStyle(headerRowLeftCellStyleStyle); for (int i = 2; i <= 4; i++) { tableHeadersRow.createCell(i).setCellStyle(headerRowMiddleCellStyle); }//from w w w . ja va2 s . c o m Cell quantityCell = tableHeadersRow.createCell(5); quantityCell.setCellValue("Qunatite/carton"); quantityCell.setCellStyle(headerRowMiddleCellStyle); Cell boxesCountCell = tableHeadersRow.createCell(6); boxesCountCell.setCellValue("Nbre cartons"); boxesCountCell.setCellStyle(headerRowMiddleCellStyle); Cell totalQuantityCell = tableHeadersRow.createCell(7); totalQuantityCell.setCellValue("Quantite totale"); totalQuantityCell.setCellStyle(headerRowMiddleCellStyle); Cell palettesCountCell = tableHeadersRow.createCell(8); palettesCountCell.setCellValue("Nbre Palettes"); palettesCountCell.setCellStyle(headerRowRightCellStyle); }
From source file:com.griffinslogistics.document.excel.BDLGenerator.java
/** * * @param sheet/* ww w . ja v a 2 s. c om*/ * @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(","); }//from w w w.j a va 2 s . co m 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.BDLGenerator.java
private static void insertFooter(Sheet sheet, CellStyle footerStyle, int index, String packageNumber, String deliveryAddress, String client) { String mergeString;/*from ww w. j av a 2 s. c o m*/ index += 2; Row transportNumberRow = sheet.createRow(index); Cell transportNumberCell = transportNumberRow.createCell(1); transportNumberCell.setCellValue("Num Tpt: " + packageNumber); transportNumberCell.setCellStyle(footerStyle); mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); index += 2; Row addressLabelRow = sheet.createRow(index); Cell addressLabelCell = addressLabelRow.createCell(1); addressLabelCell.setCellValue("A livrer chez: "); addressLabelCell.setCellStyle(footerStyle); mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); index += 1; Row addressRow = sheet.createRow(index); Cell addressCell = addressRow.createCell(1); addressCell.setCellValue(deliveryAddress); addressCell.setCellStyle(footerStyle); mergeString = String.format("$B$%s:$I$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); index += 3; Row clientLabelRow = sheet.createRow(index); Cell clientLabelCell = clientLabelRow.createCell(1); clientLabelCell.setCellValue("Pour le compte des Editions: "); clientLabelCell.setCellStyle(footerStyle); mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); index += 1; Row clientRow = sheet.createRow(index); Cell clientCell = clientRow.createCell(1); clientCell.setCellValue(client); clientCell.setCellStyle(footerStyle); mergeString = String.format("$B$%s:$I$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); index += 4; Row dateRow = sheet.createRow(index); Cell dateCell = dateRow.createCell(1); dateCell.setCellValue("Date: ................"); dateCell.setCellStyle(footerStyle); mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); index += 3; Row signatureRow = sheet.createRow(index); Cell signatureCell = signatureRow.createCell(1); signatureCell.setCellValue("Signature et tampon: ..................."); signatureCell.setCellStyle(footerStyle); mergeString = String.format("$B$%s:$C$%s", index + 1, index + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); }
From source file:com.griffinslogistics.document.excel.BookLabelGenerator.java
private static void generateAddress(Sheet sheet, Map<String, CellStyle> styles, BookLabelModel bookLabelModel) { sheet.addMergedRegion(CellRangeAddress.valueOf("$A2:A3")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A12:A13")); sheet.addMergedRegion(CellRangeAddress.valueOf("$D2:D3")); sheet.addMergedRegion(CellRangeAddress.valueOf("$D12:D13")); Row row2 = sheet.getRow(1);/*ww w. jav a 2s . c o m*/ Row row3 = sheet.getRow(2); Cell a2 = row2.createCell(0); a2.setCellValue(ADDRESS); a2.setCellStyle(styles.get(STYLE_LABEL)); Cell a3 = row3.createCell(0); a3.setCellStyle(styles.get(STYLE_LABEL)); Cell d2 = row2.createCell(3); d2.setCellValue(ADDRESS); d2.setCellStyle(styles.get(STYLE_LABEL)); Cell d3 = row3.createCell(3); d3.setCellStyle(styles.get(STYLE_LABEL)); Cell b2 = row2.createCell(1); b2.setCellValue(bookLabelModel.getAddress()); b2.setCellStyle(styles.get(STYLE_CONTENT)); Cell b3 = row3.createCell(1); b3.setCellValue(bookLabelModel.getPostalCode()); b3.setCellStyle(styles.get(STYLE_CONTENT)); Cell e2 = row2.createCell(4); e2.setCellValue(bookLabelModel.getAddress()); e2.setCellStyle(styles.get(STYLE_CONTENT)); Cell e3 = row3.createCell(4); e3.setCellValue(bookLabelModel.getPostalCode()); e3.setCellStyle(styles.get(STYLE_CONTENT)); Row row12 = sheet.getRow(11); Row row13 = sheet.getRow(12); Cell a12 = row12.createCell(0); a12.setCellValue(ADDRESS); a12.setCellStyle(styles.get(STYLE_LABEL)); Cell a13 = row13.createCell(0); a13.setCellStyle(styles.get(STYLE_LABEL)); Cell d12 = row12.createCell(3); d12.setCellValue(ADDRESS); d12.setCellStyle(styles.get(STYLE_LABEL)); Cell d13 = row13.createCell(3); d13.setCellStyle(styles.get(STYLE_LABEL)); Cell b12 = row12.createCell(1); b12.setCellValue(bookLabelModel.getAddress()); b12.setCellStyle(styles.get(STYLE_CONTENT)); Cell b13 = row13.createCell(1); b13.setCellValue(bookLabelModel.getPostalCode()); b13.setCellStyle(styles.get(STYLE_CONTENT)); Cell e12 = row12.createCell(4); e12.setCellValue(bookLabelModel.getAddress()); e12.setCellStyle(styles.get(STYLE_CONTENT)); Cell e13 = row13.createCell(4); e13.setCellValue(bookLabelModel.getPostalCode()); e13.setCellStyle(styles.get(STYLE_CONTENT)); }