List of usage examples for org.apache.poi.ss.usermodel Sheet createRow
Row createRow(int rownum);
From source file:com.globalsight.everest.qachecks.QAChecker.java
License:Apache License
private Row getRow(Sheet p_sheet, int p_row) { Row row = p_sheet.getRow(p_row);// ww w . j av a2 s .co m if (row == null) { row = p_sheet.createRow(p_row); } return row; }
From source file:com.griffinslogistics.document.excel.BDLGenerator.java
private static void insertPulsioImage(Workbook workbook, Sheet sheet, Pulsiodetails pulsiodetails) { Row imageRow = sheet.createRow(0); imageRow.setHeightInPoints(55);/*from ww w . j a v a2s. com*/ byte[] imageBytes = pulsiodetails.getLogo(); int pictureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG); CreationHelper helper = workbook.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = helper.createClientAnchor(); //set top-left corner for the image anchor.setCol1(7); anchor.setRow1(0); Picture pict = drawing.createPicture(anchor, pictureIdx); pict.resize(); }
From source file:com.griffinslogistics.document.excel.BDLGenerator.java
private static void insertDate(Sheet sheet, CellStyle style) { Row dateRow = sheet.createRow(6); 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);// ww w. java2s . com dateCell.setCellValue(dateString); }
From source file:com.griffinslogistics.document.excel.BDLGenerator.java
private static void insertContacts(Sheet sheet, CellStyle pulsioNameStyle, CellStyle contactsStyle, Pulsiodetails pulsiodetails) {//from ww w. j a va 2 s. c om 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); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Bon de livraison"); titleCell.setCellStyle(style);/* w w w . j ava 2 s. c o m*/ 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 ww . j a v a 2 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/* w ww. ja v a 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); 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 insertFooter(Sheet sheet, CellStyle footerStyle, int index, String packageNumber, String deliveryAddress, String client) { String mergeString;//from w ww . j a v a2 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 generate(XSSFWorkbook workbook, BookLabelModel bookLabelModel) { Map<String, CellStyle> styles = createStyles(workbook); String title = bookLabelModel.getTitle().replace("/", "-"); bookLabelModel.setTitle(title);//from ww w .j av a 2 s.c o m // Sheet sheet = workbook.createSheet(bookLabelModel.getBookNumber() + " " + bookLabelModel.getTitle()); Sheet sheet = workbook.createSheet(); for (int i = 0; i < 20; i++) { sheet.createRow(i); } generateHeaders(sheet, styles); generateAddress(sheet, styles, bookLabelModel); generateClient(sheet, styles, bookLabelModel); generateTransportation(sheet, styles, bookLabelModel); generateTitle(sheet, styles, bookLabelModel); generateCountPerBox(sheet, styles, bookLabelModel); generateCountPerAddress(sheet, styles, bookLabelModel); sheet.setColumnWidth(1, 20000); sheet.setColumnWidth(4, 20000); sheet.autoSizeColumn(0, false); sheet.autoSizeColumn(3, false); sheet.getPrintSetup().setLandscape(true); sheet.getPrintSetup().setPaperSize(XSSFPrintSetup.A4_PAPERSIZE); sheet.setFitToPage(true); }
From source file:com.griffinslogistics.excel.BDLGenerator.java
private static void insertPulsioImage(Workbook workbook, Sheet sheet, Pulsiodetails pulsiodetails) { Row imageRow = sheet.createRow(0); imageRow.setHeightInPoints(55);/*from w ww . j a v a 2s . co m*/ byte[] imageBytes = pulsiodetails.getLogo(); int pictureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG); CreationHelper helper = workbook.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = helper.createClientAnchor(); //set top-left corner for the image anchor.setCol1(8); anchor.setRow1(0); Picture pict = drawing.createPicture(anchor, pictureIdx); pict.resize(); }