List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet createRow
@Override public XSSFRow createRow(int rownum)
From source file:com.griffinslogistics.document.excel.CMRGenerator.java
private static int generatePoint20Till24(XSSFSheet sheet, Map<String, CellStyle> styles, int currentRow, Pulsiodetails pulsiodetails) {//from w w w . ja v a2 s. c o m currentRow++; String mergeString; for (int i = currentRow; i < currentRow + 2; i++) { Row row = sheet.createRow(i); mergeString = String.format("$B$%s:$I$%s", i + 1, i + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); for (int j = 1; j < 9; j++) { row.createCell(j) .setCellStyle(styles.get(i == currentRow ? LABEL_MIDDLE_STYLE : LABEL_BOTTOM_STYLE)); } } for (int i = currentRow + 2; i < currentRow + 16; i++) { Row row = sheet.createRow(i); mergeString = String.format("$D$%s:$I$%s", i + 1, i + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); for (int j = 1; j < 9; j++) { row.createCell(j).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); } } currentRow++; Row row45 = sheet.getRow(currentRow); row45.setHeightInPoints(30); row45.getCell(1).setCellValue(LABEL_POINT_20); currentRow++; Row row46 = sheet.getRow(currentRow); Cell establishedInCell = row46.getCell(1); establishedInCell.setCellValue(LABEL_ESTABLISHED_IN); Cell establishedOnCell = row46.getCell(2); establishedOnCell.setCellValue(LABEL_ESTABLISHED_ON); Cell goodsDeliveredCell = row46.getCell(3); goodsDeliveredCell.setCellValue(LABEL_GOODS_RECEIVED); currentRow++; Row row47 = sheet.getRow(currentRow); row47.getCell(3).setCellValue(LABEL_TIME_OF_ARRIVAL); currentRow++; Row row48 = sheet.getRow(currentRow); Cell cityCell = row48.getCell(1); cityCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); cityCell.setCellValue("Sofia, Bulgaria"); Cell dateCell = row48.getCell(2); dateCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); dateCell.setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(new Date())); currentRow += 2; sheet.getRow(currentRow).getCell(3).setCellValue(LABEL_PLACE_20); currentRow++; Row row51 = sheet.getRow(currentRow); currentRow++; row51.getCell(1).setCellValue(TWENTY_TWO); row51.getCell(2).setCellValue(TWENTY_THREE); // Insert signature picture Workbook workbook = sheet.getWorkbook(); byte[] imageBytes = pulsiodetails.getSignature(); int pictureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_PNG); CreationHelper helper = workbook.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = helper.createClientAnchor(); //set top-left corner for the image anchor.setCol1(1); anchor.setRow1(currentRow); XSSFPicture pict = (XSSFPicture) drawing.createPicture(anchor, pictureIdx); pict.resize(1.01, 5); currentRow += 4; Row row56 = sheet.getRow(currentRow); row56.getCell(3).setCellValue(LABEL_SIGNATURE_STAMP); currentRow += 2; Row row58 = sheet.getRow(currentRow); Cell signatureLabelCell1 = row58.getCell(1); signatureLabelCell1.setCellValue(LABEL_SENDER_SIGNATURE_BULGARIAN); Cell carrierSignatureCell = row58.getCell(2); carrierSignatureCell.setCellValue(LABEL_CARRIER_SIGNATURE_BULGARIAN); Cell receiverSignatureCell = row58.getCell(3); receiverSignatureCell.setCellValue(LABEL_RECEIVER_SIGNATURE_BULGARIAN); currentRow++; Row row59 = sheet.getRow(currentRow); Cell signatureLabelCell2 = row59.getCell(1); signatureLabelCell2.setCellValue(LABEL_SENDER_SIGNATURE_ENGLISH); Cell carrierSignatureCel2 = row59.getCell(2); carrierSignatureCel2.setCellValue(LABEL_CARRIER_SIGNATURE_BULGARIAN); Cell receiverSignatureCel2 = row59.getCell(3); receiverSignatureCel2.setCellValue(LABEL_RECEIVER_SIGNATURE_ENGLISH); currentRow++; Row row60 = sheet.createRow(currentRow); mergeString = String.format("$B$%s:$I$%s", currentRow + 1, currentRow + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); for (int i = 1; i < 9; i++) { row60.createCell(i).setCellStyle(styles.get(LABEL_WHOLE_STYLE)); } Cell additionalSpaceCell = row60.getCell(1); additionalSpaceCell.setCellValue(LABEL_ADDITIONAL_SPACE); return currentRow; }
From source file:com.griffinslogistics.excel.CMRGenerator.java
public static void generateAll(OutputStream outputStream, List<BookspackageCMRModel> bookspackageCMRModels, Pulsiodetails pulsioDetails) {/*from w w w .j a v a 2 s .co m*/ try { XSSFWorkbook workbook = new XSSFWorkbook(); for (BookspackageCMRModel bookspackageCMRModel : bookspackageCMRModels) { XSSFSheet sheet = workbook.createSheet(bookspackageCMRModel.getPackageNumber()); setDefaultSheetStyles(sheet); Map<String, CellStyle> styles = createStyles(workbook); int currentRow = 2; Row headerRow = sheet.createRow(currentRow); generateHeaderRow(headerRow, styles, bookspackageCMRModel.getPackageNumber()); currentRow = generateSenderAndDriver(sheet, styles, currentRow); currentRow = generatePoint2Till9(sheet, styles, currentRow, bookspackageCMRModel.getDeliveryAddress()); double weight = bookspackageCMRModel.getTotalWeight(); Long totalBoxesCount = bookspackageCMRModel.getTotalBoxesCount(); currentRow = generatePoints10Till15(totalBoxesCount, weight, sheet, styles, currentRow); currentRow = generatePoint15Till19(sheet, styles, currentRow); currentRow = generatePoint20Till24(sheet, styles, currentRow, pulsioDetails); sheet.autoSizeColumn(1, true); sheet.autoSizeColumn(2, true); } workbook.write(outputStream); } catch (FileNotFoundException ex) { Logger.getLogger(CMRGenerator.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(CMRGenerator.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.griffinslogistics.excel.CMRGenerator.java
public static void generateSingle(OutputStream outputStream, BookspackageCMRModel bookspackageCMRModel, Pulsiodetails pulsioDetails) {/*from ww w . j av a 2s . com*/ try { XSSFWorkbook workbook = new XSSFWorkbook(); //Get first sheet from the workbook XSSFSheet sheet = workbook.createSheet(bookspackageCMRModel.getPackageNumber()); setDefaultSheetStyles(sheet); Map<String, CellStyle> styles = createStyles(workbook); int currentRow = 2; Row headerRow = sheet.createRow(currentRow); generateHeaderRow(headerRow, styles, bookspackageCMRModel.getPackageNumber()); currentRow = generateSenderAndDriver(sheet, styles, currentRow); currentRow = generatePoint2Till9(sheet, styles, currentRow, bookspackageCMRModel.getDeliveryAddress()); double weight = bookspackageCMRModel.getTotalWeight(); Long totalBoxesCount = bookspackageCMRModel.getTotalBoxesCount(); currentRow = generatePoints10Till15(totalBoxesCount, weight, sheet, styles, currentRow); currentRow = generatePoint15Till19(sheet, styles, currentRow); currentRow = generatePoint20Till24(sheet, styles, currentRow, pulsioDetails); workbook.write(outputStream); } catch (FileNotFoundException ex) { Logger.getLogger(CMRGenerator.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(CMRGenerator.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.griffinslogistics.excel.CMRGenerator.java
private static int generatePoint2Till9(XSSFSheet sheet, Map<String, CellStyle> styles, int currentRow, String deliveryAddress) { for (int i = 11; i < 27; i++) { sheet.addMergedRegion(CellRangeAddress.valueOf("$B$" + i + ":$C$" + i)); sheet.addMergedRegion(CellRangeAddress.valueOf("$D$" + i + ":$I$" + i)); }/*from w ww . jav a2 s . co m*/ currentRow++; Row row11 = sheet.createRow(currentRow); Cell receiverLabelCell = row11.createCell(1); receiverLabelCell.setCellValue(LABEL_RECEIVER); receiverLabelCell.setCellStyle(styles.get(LABEL_TOP_STYLE)); row11.createCell(2).setCellStyle(styles.get(LABEL_TOP_STYLE)); currentRow++; Row row12 = sheet.createRow(currentRow); row12.createCell(1).setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); row12.createCell(2).setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); currentRow++; Row row13 = sheet.createRow(currentRow); Cell receiverCell1 = row13.createCell(1); receiverCell1.setCellValue(RECEIVER_PART_1); receiverCell1.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); row13.createCell(2).setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); currentRow++; Row row14 = sheet.createRow(currentRow); Cell receiverCell2 = row14.createCell(1); receiverCell2.setCellValue(RECEIVER_PART_2); receiverCell2.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); row14.createCell(2).setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); row14.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); currentRow++; Row row15 = sheet.createRow(currentRow); Cell receiverCell3 = row15.createCell(1); receiverCell3.setCellValue(RECEIVER_PART_3); receiverCell3.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); row15.createCell(2).setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); Cell successiveCarrierCell1 = row11.createCell(3); successiveCarrierCell1.setCellValue(LABEL_SUCCESSIVE_CARRIERS_PART_1); successiveCarrierCell1.setCellStyle(styles.get(LABEL_TOP_STYLE)); row11.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); Cell successiveCarrierCell2 = row12.createCell(3); successiveCarrierCell2.setCellValue(LABEL_SUCCESSIVE_CARRIERS_PART_2); successiveCarrierCell2.setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row12.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); Cell successiveCarrierCell3 = row13.createCell(3); successiveCarrierCell3.setCellValue(LABEL_SUCCESSIVE_CARRIERS_PART_3); successiveCarrierCell3.setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row13.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); Cell reservationsCell1 = row15.createCell(3); reservationsCell1.setCellValue(RESERVATIONS_PART_1); reservationsCell1.setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row15.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); currentRow++; Row row16 = sheet.createRow(currentRow); Cell reservationsCell2 = row16.createCell(3); reservationsCell2.setCellValue(RESERVATIONS_PART_2); reservationsCell2.setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row16.createCell(1).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row16.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row16.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); currentRow++; Row row17 = sheet.createRow(currentRow); row17.createCell(1).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row17.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row17.createCell(3).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row17.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); currentRow++; Row row18 = sheet.createRow(currentRow); Cell takingOverCellLabel = row18.createCell(1); takingOverCellLabel.setCellValue(LABEL_TAKING_OVER); takingOverCellLabel.setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row18.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row18.createCell(3).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row18.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); currentRow++; Row row19 = sheet.createRow(currentRow); Cell takingOverContentCell = row19.createCell(1); takingOverContentCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); takingOverContentCell.setCellValue(TAKING_OVER_CONTENT); row19.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row19.createCell(3).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row19.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); currentRow++; Row row20 = sheet.createRow(currentRow); Cell countryLabelCell = row20.createCell(1); countryLabelCell.setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); countryLabelCell.setCellValue(LABEL_COUNTRY); row20.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row20.createCell(3).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row20.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); currentRow++; Row row21 = sheet.createRow(currentRow); Cell countryContentCell = row21.createCell(1); countryContentCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); countryContentCell.setCellValue(ADDRESS_PART_4.trim()); row21.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row21.createCell(3).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row21.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); currentRow++; Row row22 = sheet.createRow(currentRow); Cell dateCell = row22.createCell(1); dateCell.setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); dateCell.setCellValue(LABEL_DATE); row22.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row22.createCell(3).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row22.createCell(8).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); currentRow++; Row row23 = sheet.createRow(currentRow); Cell arrivalTime = row23.createCell(1); arrivalTime.setCellStyle(styles.get(LABEL_BOTTOM_STYLE)); arrivalTime.setCellValue(LABEL_ARRIVAL_TIME); row23.createCell(2).setCellStyle(styles.get(LABEL_BOTTOM_STYLE)); for (int i = 3; i < 9; i++) { row23.createCell(i).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); } currentRow++; Row row24 = sheet.createRow(currentRow); Cell goodsDeliveryLabelCell = row24.createCell(1); goodsDeliveryLabelCell.setCellValue(LABEL_DELIVERY_OF_GOODS); goodsDeliveryLabelCell.setCellStyle(styles.get(LABEL_TOP_STYLE)); row24.createCell(2).setCellStyle(styles.get(LABEL_TOP_STYLE)); currentRow++; Row row25 = sheet.createRow(currentRow); Cell placeLabelCell = row25.createCell(1); placeLabelCell.setCellValue(LABEL_PLACE); placeLabelCell.setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); row25.createCell(2).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); currentRow++; Row row26 = sheet.createRow(currentRow); Cell placeContentCell = row26.createCell(1); placeContentCell.setCellValue(deliveryAddress); placeContentCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); row26.createCell(2).setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); currentRow++; Row row27 = sheet.createRow(currentRow); Cell deliveryCountryLabelCell = row27.createCell(1); deliveryCountryLabelCell.setCellValue(LABEL_COUNTRY); deliveryCountryLabelCell.setCellStyle(styles.get(LABEL_LEFT_STYLE)); Cell deliveryCountryContentCell = row27.createCell(2); deliveryCountryContentCell.setCellValue(FRANCE); deliveryCountryContentCell.setCellStyle(styles.get(CONTENT_RIGHT_STYLE)); currentRow++; Row row28 = sheet.createRow(currentRow); sheet.addMergedRegion(CellRangeAddress.valueOf("$B$28:$C$28")); Cell openingHoursCell = row28.createCell(1); openingHoursCell.setCellValue(LABEL_OPENING_HOURS); openingHoursCell.setCellStyle(styles.get(LABEL_BOTTOM_STYLE)); row28.createCell(2).setCellStyle(styles.get(LABEL_BOTTOM_STYLE)); currentRow++; Row row29 = sheet.createRow(currentRow); sheet.addMergedRegion(CellRangeAddress.valueOf("$B$29:$C$29")); sheet.addMergedRegion(CellRangeAddress.valueOf("$B$30:$C$30")); Cell senderInstructionsCell1 = row29.createCell(1); senderInstructionsCell1.setCellValue(SENDER_INSTRUCTIONS_BULGARIAN); senderInstructionsCell1.setCellStyle(styles.get(LABEL_TOP_STYLE)); row29.createCell(2).setCellStyle(styles.get(LABEL_TOP_STYLE)); sheet.addMergedRegion(CellRangeAddress.valueOf("$D$29:$I$29")); sheet.addMergedRegion(CellRangeAddress.valueOf("$D$30:$I$30")); Cell carrierDocumentsCell1 = row29.createCell(3); carrierDocumentsCell1.setCellValue(CARRIER_DOCUMENTS_BULGARIAN); carrierDocumentsCell1.setCellStyle(styles.get(LABEL_TOP_STYLE)); for (int i = 4; i < 9; i++) { row29.createCell(i).setCellStyle(styles.get(LABEL_TOP_STYLE)); } currentRow++; Row row30 = sheet.createRow(currentRow); Cell senderInstructionsCell2 = row30.createCell(1); senderInstructionsCell2.setCellValue(SENDERS_INSTRUCTIONS_ENGLISH); senderInstructionsCell2.setCellStyle(styles.get(LABEL_BOTTOM_STYLE)); row30.createCell(2).setCellStyle(styles.get(LABEL_BOTTOM_STYLE)); Cell carrierDocumentsCell2 = row30.createCell(3); carrierDocumentsCell2.setCellValue(CARRIER_DOCUMENTS_ENGLISH); carrierDocumentsCell2.setCellStyle(styles.get(LABEL_BOTTOM_STYLE)); for (int i = 4; i < 9; i++) { row30.createCell(i).setCellStyle(styles.get(LABEL_BOTTOM_STYLE)); } for (int i = 23; i < 28; i++) { sheet.getRow(i).createCell(8).setCellStyle(styles.get(LABEL_RIGHT_STYLE)); } return currentRow; }
From source file:com.griffinslogistics.excel.CMRGenerator.java
private static int generatePoint20Till24(XSSFSheet sheet, Map<String, CellStyle> styles, int currentRow, Pulsiodetails pulsiodetails) {/*w ww . jav a 2s.co m*/ currentRow++; String mergeString; for (int i = currentRow; i < currentRow + 2; i++) { Row row = sheet.createRow(i); mergeString = String.format("$B$%s:$I$%s", i + 1, i + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); for (int j = 1; j < 9; j++) { row.createCell(j) .setCellStyle(styles.get(i == currentRow ? LABEL_MIDDLE_STYLE : LABEL_BOTTOM_STYLE)); } } for (int i = currentRow + 2; i < currentRow + 16; i++) { Row row = sheet.createRow(i); mergeString = String.format("$D$%s:$I$%s", i + 1, i + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); for (int j = 1; j < 9; j++) { row.createCell(j).setCellStyle(styles.get(LABEL_MIDDLE_STYLE)); } } currentRow++; Row row45 = sheet.getRow(currentRow); row45.setHeightInPoints(30); row45.getCell(1).setCellValue(LABEL_POINT_20); currentRow++; Row row46 = sheet.getRow(currentRow); Cell establishedInCell = row46.getCell(1); establishedInCell.setCellValue(LABEL_ESTABLISHED_IN); Cell establishedOnCell = row46.getCell(2); establishedOnCell.setCellValue(LABEL_ESTABLISHED_ON); Cell goodsDeliveredCell = row46.getCell(3); goodsDeliveredCell.setCellValue(LABEL_GOODS_RECEIVED); currentRow++; Row row47 = sheet.getRow(currentRow); row47.getCell(3).setCellValue(LABEL_TIME_OF_ARRIVAL); currentRow++; Row row48 = sheet.getRow(currentRow); Cell cityCell = row48.getCell(1); cityCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); cityCell.setCellValue("Sofia, Bulgaria"); Cell dateCell = row48.getCell(2); dateCell.setCellStyle(styles.get(CONTENT_MIDDLE_STYLE)); dateCell.setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(new Date())); currentRow += 2; sheet.getRow(currentRow).getCell(3).setCellValue(LABEL_PLACE_20); currentRow++; Row row51 = sheet.getRow(currentRow); currentRow++; row51.getCell(1).setCellValue(TWENTY_TWO); row51.getCell(2).setCellValue(TWENTY_THREE); // Insert signature picture Workbook workbook = sheet.getWorkbook(); byte[] imageBytes = pulsiodetails.getSignature(); 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(1); anchor.setRow1(currentRow); Picture pict = drawing.createPicture(anchor, pictureIdx); pict.resize(); currentRow += 4; Row row56 = sheet.getRow(currentRow); row56.getCell(3).setCellValue(LABEL_SIGNATURE_STAMP); currentRow += 2; Row row58 = sheet.getRow(currentRow); Cell signatureLabelCell1 = row58.getCell(1); signatureLabelCell1.setCellValue(LABEL_SENDER_SIGNATURE_BULGARIAN); Cell carrierSignatureCell = row58.getCell(2); carrierSignatureCell.setCellValue(LABEL_CARRIER_SIGNATURE_BULGARIAN); Cell receiverSignatureCell = row58.getCell(3); receiverSignatureCell.setCellValue(LABEL_RECEIVER_SIGNATURE_BULGARIAN); currentRow++; Row row59 = sheet.getRow(currentRow); Cell signatureLabelCell2 = row59.getCell(1); signatureLabelCell2.setCellValue(LABEL_SENDER_SIGNATURE_ENGLISH); Cell carrierSignatureCel2 = row59.getCell(2); carrierSignatureCel2.setCellValue(LABEL_CARRIER_SIGNATURE_BULGARIAN); Cell receiverSignatureCel2 = row59.getCell(3); receiverSignatureCel2.setCellValue(LABEL_RECEIVER_SIGNATURE_ENGLISH); currentRow++; Row row60 = sheet.createRow(currentRow); mergeString = String.format("$B$%s:$I$%s", currentRow + 1, currentRow + 1); sheet.addMergedRegion(CellRangeAddress.valueOf(mergeString)); for (int i = 1; i < 9; i++) { row60.createCell(i).setCellStyle(styles.get(LABEL_WHOLE_STYLE)); } Cell additionalSpaceCell = row60.getCell(1); additionalSpaceCell.setCellValue(LABEL_ADDITIONAL_SPACE); return currentRow; }
From source file:com.heimaide.server.common.utils.excel.ExportExcel.java
License:Open Source License
private void exportFile(List<String> list, String rootPath, HttpServletResponse response, List<List<Object>> bodyList) throws FileNotFoundException, IOException { String importFileName = rootPath + "WEB-INF" + File.separator + "xlsx" + File.separator + "order.xlsx"; String tmpDir = rootPath + "tmpDir" + File.separator; XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(importFileName)); this.styles = createStyles(wb); //?excel? /*from ww w. j av a 2 s . c o m*/ //??sheet XSSFSheet sheet = wb.getSheetAt(0); XSSFCell cell1 = sheet.getRow(1).getCell(0); String cell1str = cell1.getStringCellValue(); //???? cell1str = cell1str.replace("X", list.get(0)); cell1.setCellValue(cell1str); XSSFCell cell2 = sheet.getRow(2).getCell(0); String cell2str = cell2.getStringCellValue(); //?? cell2str = cell2str.replace("X", list.get(1)); //? cell2str = cell2str.replace("Y", list.get(2)); //??? cell2str = cell2str.replace("Z", list.get(3)); cell2.setCellValue(cell2str); XSSFCell cell3 = sheet.getRow(3).getCell(12); String cell3str = cell3.getStringCellValue(); cell3str = cell3str.replace("XX", list.get(4)); cell3.setCellValue(cell3str); XSSFCell cell4 = sheet.getRow(sheet.getLastRowNum() - 1).getCell(0); String cell4str = cell4.getStringCellValue(); cell4str = cell4str.replace("X", list.get(5)); cell4.setCellValue(cell4str); XSSFCell cell5 = sheet.getRow(sheet.getLastRowNum() - 4).getCell(12); XSSFCell cell6 = sheet.getRow(sheet.getLastRowNum() - 4).getCell(13); String str1 = String.valueOf(list.get(6)); String str2 = String.valueOf(list.get(7)); str1 = str1.substring(0, str1.indexOf(".")); str2 = str2.substring(0, str2.indexOf(".")); cell5.setCellValue(str1); cell6.setCellValue(str2); XSSFCell cell7 = sheet.getRow(sheet.getLastRowNum() - 3).getCell(12); XSSFCell cell8 = sheet.getRow(sheet.getLastRowNum() - 3).getCell(13); cell7.setCellValue(Double.parseDouble(list.get(6)) / 10); cell8.setCellValue(Double.parseDouble(list.get(7)) / 10); // int columnNum =sheet.getRow(3).getLastCellNum(); // int column =0; // for(int i=0;i<columnNum;i++) // { // System.out.println("==="+sheet.getRow(3).getCell(i).getStringCellValue()); // column =i; // } int num = 5; for (int i = 0; i < bodyList.size(); i++) { List<Object> strList = bodyList.get(i); sheet.shiftRows(num, bodyList.size() + num, 1, true, false); sheet.createRow(num); for (int j = 0; j < strList.size(); j++) { Row row = sheet.getRow(num); addCell(row, j, strList.get(j)); // Cell cell = row.createCell(j); // cell.setCellValue(strList.get(j)); } num++; } //?? String tmpName = tmpDir + "tmp_hmd.xlsx"; File dirFile = new File(tmpDir); if (!dirFile.exists()) { dirFile.mkdir(); } File tmpFile = new File(tmpName); FileOutputStream fos = new FileOutputStream(tmpFile); wb.write(fos); XSSFWorkbook wb1 = new XSSFWorkbook(new FileInputStream(tmpName)); response.reset(); response.setContentType("application/octet-stream; charset=utf-8"); response.setHeader("Content-Disposition", "attachment; filename=" + Encodes.urlEncode("?" + list.get(0) + "?.xlsx")); wb1.write(response.getOutputStream()); tmpFile.delete(); }
From source file:com.hp.autonomy.frontend.reports.powerpoint.PowerPointServiceImpl.java
License:MIT License
/** * Internal implementation to add a sunburst chart (actually a doughnut chart) to a slide, based on a template. * @param template the parsed template information. * @param slide the slide to add to.// www . j a v a2 s .c om * @param anchor optional bounding rectangle to draw onto, in PowerPoint coordinates. * If null, we'll use the bounds from the original template chart. * @param data the sunburst data. * @param shapeId the slide shape ID, should be unique within the slide. * @param relId the relation ID to the chart data. * @throws TemplateLoadException if we can't create the sunburst; most likely due to an invalid template. */ private static void addSunburst(final SlideShowTemplate template, final XSLFSlide slide, final Rectangle2D.Double anchor, final SunburstData data, final int shapeId, final String relId) throws TemplateLoadException { final String[] categories = data.getCategories(); final double[] values = data.getValues(); final String title = data.getTitle(); slide.getXmlObject().getCSld().getSpTree().addNewGraphicFrame() .set(template.getDoughnutChartShapeXML(relId, shapeId, "chart" + shapeId, anchor)); final XSSFWorkbook workbook = new XSSFWorkbook(); final XSSFSheet sheet = workbook.createSheet(); final XSLFChart baseChart = template.getDoughnutChart(); final CTChartSpace chartSpace = (CTChartSpace) baseChart.getCTChartSpace().copy(); final CTChart ctChart = chartSpace.getChart(); final CTPlotArea plotArea = ctChart.getPlotArea(); if (StringUtils.isEmpty(title)) { if (ctChart.getAutoTitleDeleted() != null) { ctChart.getAutoTitleDeleted().setVal(true); } ctChart.unsetTitle(); } final CTDoughnutChart donutChart = plotArea.getDoughnutChartArray(0); final CTPieSer series = donutChart.getSerArray(0); final CTStrRef strRef = series.getTx().getStrRef(); strRef.getStrCache().getPtArray(0).setV(title); sheet.createRow(0).createCell(1).setCellValue(title); strRef.setF(new CellReference(sheet.getSheetName(), 0, 1, true, true).formatAsString()); final CTStrRef categoryRef = series.getCat().getStrRef(); final CTStrData categoryData = categoryRef.getStrCache(); final CTNumRef numRef = series.getVal().getNumRef(); final CTNumData numericData = numRef.getNumCache(); final String[] fillColors = data.getColors(); final String[] strokeColors = data.getStrokeColors(); final boolean overrideFill = ArrayUtils.isNotEmpty(fillColors); final boolean overrideStroke = ArrayUtils.isNotEmpty(strokeColors); final boolean overrideColors = overrideFill || overrideStroke; final List<CTDPt> dPtList = series.getDPtList(); final CTDPt templatePt = (CTDPt) dPtList.get(0).copy(); if (overrideColors) { dPtList.clear(); final CTShapeProperties spPr = templatePt.getSpPr(); final CTLineProperties ln = spPr.getLn(); // We need to unset any styles on the existing template if (overrideFill) { unsetSpPrFills(spPr); } if (overrideStroke) { unsetLineFills(ln); } } categoryData.setPtArray(null); numericData.setPtArray(null); CTLegend legend = null; final int[] showInLegend = data.getShowInLegend(); int nextLegendToShow = 0, nextLegendToShowIdx = -1; if (showInLegend != null) { // We need to write legendEntry elements to hide the legend for chart series we don't want. // Note this only works in PowerPoint, and not OpenOffice. legend = ctChart.isSetLegend() ? ctChart.getLegend() : ctChart.addNewLegend(); Arrays.sort(showInLegend); nextLegendToShow = showInLegend[++nextLegendToShowIdx]; } for (int idx = 0; idx < values.length; ++idx) { final CTStrVal categoryPoint = categoryData.addNewPt(); categoryPoint.setIdx(idx); categoryPoint.setV(categories[idx]); final CTNumVal numericPoint = numericData.addNewPt(); numericPoint.setIdx(idx); numericPoint.setV(Double.toString(values[idx])); if (overrideColors) { final CTDPt copiedPt = (CTDPt) templatePt.copy(); copiedPt.getIdx().setVal(idx); if (overrideFill) { final Color color = Color.decode(fillColors[idx % fillColors.length]); final CTSolidColorFillProperties fillClr = copiedPt.getSpPr().addNewSolidFill(); fillClr.addNewSrgbClr().setVal( new byte[] { (byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue() }); } if (overrideStroke) { final Color strokeColor = Color.decode(strokeColors[idx % strokeColors.length]); final CTSolidColorFillProperties strokeClr = copiedPt.getSpPr().getLn().addNewSolidFill(); strokeClr.addNewSrgbClr().setVal(new byte[] { (byte) strokeColor.getRed(), (byte) strokeColor.getGreen(), (byte) strokeColor.getBlue() }); } dPtList.add(copiedPt); } if (legend != null) { // We're hiding some legend elements. Should we show this index? if (nextLegendToShow == idx) { // We show this index, find the next one to show. ++nextLegendToShowIdx; if (nextLegendToShowIdx < showInLegend.length) { nextLegendToShow = showInLegend[nextLegendToShowIdx]; } } else { // We hide this index. If there's already a matching legend entry in the XML, update it, // otherwise we create a new legend entry. boolean found = false; for (int ii = 0, max = legend.sizeOfLegendEntryArray(); ii < max; ++ii) { final CTLegendEntry legendEntry = legend.getLegendEntryArray(ii); final CTUnsignedInt idxLegend = legendEntry.getIdx(); if (idxLegend != null && idxLegend.getVal() == idx) { found = true; if (legendEntry.isSetDelete()) { legendEntry.getDelete().setVal(true); } else { legendEntry.addNewDelete().setVal(true); } } } if (!found) { final CTLegendEntry idxLegend = legend.addNewLegendEntry(); idxLegend.addNewIdx().setVal(idx); idxLegend.addNewDelete().setVal(true); } } } XSSFRow row = sheet.createRow(idx + 1); row.createCell(0).setCellValue(categories[idx]); row.createCell(1).setCellValue(values[idx]); } categoryData.getPtCount().setVal(categories.length); numericData.getPtCount().setVal(values.length); categoryRef.setF(new CellRangeAddress(1, values.length, 0, 0).formatAsString(sheet.getSheetName(), true)); numRef.setF(new CellRangeAddress(1, values.length, 1, 1).formatAsString(sheet.getSheetName(), true)); try { writeChart(template.getSlideShow(), slide, baseChart, chartSpace, workbook, relId); } catch (IOException | InvalidFormatException e) { throw new TemplateLoadException("Error writing chart in loaded template", e); } }
From source file:com.hp.autonomy.frontend.reports.powerpoint.PowerPointServiceImpl.java
License:MIT License
/** * Utility function to write the date graph data as a Excel workbook; required since PowerPoint charts actually * embed an Excel file with corresponding data. If invalid, it'll open in OpenOffice fine, but PowerPoint will * complain that the presentation is corrupted. * @param data the date graph data./*w w w . jav a 2s . com*/ * @return a new Excel workbook with specified data on a new sheet. */ private static XSSFWorkbook writeChart(final DategraphData data) { final XSSFWorkbook wb = new XSSFWorkbook(); final XSSFSheet sheet = wb.createSheet("Sheet1"); final CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat((short) 14); final List<DategraphData.Row> rows = data.getRows(); final long[] timestamps = data.getTimestamps(); final XSSFRow header = sheet.createRow(0); header.createCell(0).setCellValue("Timestamp"); for (int ii = 0; ii < rows.size(); ++ii) { header.createCell(ii + 1).setCellValue(rows.get(ii).getLabel()); } for (int rowIdx = 0; rowIdx < timestamps.length; ++rowIdx) { final XSSFRow row = sheet.createRow(rowIdx + 1); final XSSFCell cell = row.createCell(0); cell.setCellStyle(cellStyle); cell.setCellValue(new Date(timestamps[rowIdx] * 1000)); for (int ii = 0; ii < rows.size(); ++ii) { row.createCell(ii + 1).setCellValue(rows.get(ii).getValues()[rowIdx]); } } return wb; }
From source file:com.khartec.waltz.web.endpoints.extracts.BaseDataExtractor.java
License:Open Source License
private void writeExcelBody(Select<?> qry, XSSFSheet sheet) { AtomicInteger rowNum = new AtomicInteger(1); qry.fetch().forEach(r -> {//from w w w. j a v a 2 s . c o m Row row = sheet.createRow(rowNum.getAndIncrement()); AtomicInteger colNum = new AtomicInteger(0); colNum.set(0); for (Field<?> field : r.fields()) { Cell cell = row.createCell(colNum.getAndIncrement()); cell.setCellValue(Optional.ofNullable(r.get(field)).map(Objects::toString).orElse("")); } }); }
From source file:com.khartec.waltz.web.endpoints.extracts.BaseDataExtractor.java
License:Open Source License
private void writeExcelHeader(Select<?> qry, XSSFSheet sheet) { Row headerRow = sheet.createRow(0); AtomicInteger colNum = new AtomicInteger(); qry.fieldStream().forEach(f -> {/*from www .j av a 2 s . co m*/ Cell cell = headerRow.createCell(colNum.getAndIncrement()); cell.setCellValue(Objects.toString(f.getName())); }); }