List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetIndex
@Override public int getSheetIndex(Sheet sheet)
From source file:com.anritsu.mcrepositorymanager.utils.GenerateRSS.java
public String getRSS() { FileInputStream file = null;/* ww w. j av a 2 s . c om*/ String rssFileName = rssTemplateFileName.replaceAll("template", mcVersion); try { file = new FileInputStream( new File(Configuration.getInstance().getRssTemplatePath() + rssTemplateFileName)); XSSFWorkbook workbook = new XSSFWorkbook(file); workbook.setSheetName(workbook.getSheetIndex("MC X.X.X"), "MC " + mcVersion); XSSFSheet sheet = workbook.getSheet("MC " + mcVersion); CreationHelper createHelper = workbook.getCreationHelper(); Cell cell = null; // Update the sheet title cell = sheet.getRow(0).getCell(0); cell.setCellValue(cell.getStringCellValue().replaceAll("template", mcVersion)); XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); XSSFCellStyle hlinkstyle = workbook.createCellStyle(); XSSFFont hlinkfont = workbook.createFont(); hlinkfont.setUnderline(XSSFFont.U_SINGLE); hlinkfont.setColor(HSSFColor.BLUE.index); hlinkstyle.setFont(hlinkfont); hlinkstyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); hlinkstyle.setBorderTop(XSSFCellStyle.BORDER_THIN); hlinkstyle.setBorderRight(XSSFCellStyle.BORDER_THIN); hlinkstyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); XSSFCellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd-MMMM-yyyy")); dateCellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); dateCellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); dateCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); dateCellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); // Populate the table int rowCount = 1; for (RecommendedMcPackage rmcp : sortedMcPackages) { if (rmcp.getRecommendedVersion() != null && rmcp.isShowInTable()) { Row row = sheet.createRow(rowCount + 1); rowCount++; cell = row.createCell(0); cell.setCellValue(rmcp.getTier().replaceAll("Anritsu/MasterClaw/", "")); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(rmcp.getGroup()); cell.setCellStyle(cellStyle); cell = row.createCell(2); cell.setCellValue(rmcp.getPackageName()); UrlValidator defaultValidator = new UrlValidator(UrlValidator.ALLOW_LOCAL_URLS); if (rmcp.getRecommendedVersion().getReleaseNote() != null && defaultValidator.isValid(rmcp.getRecommendedVersion().getReleaseNote())) { XSSFHyperlink releaseNotelink = (XSSFHyperlink) createHelper .createHyperlink(Hyperlink.LINK_URL); releaseNotelink.setAddress(rmcp.getRecommendedVersion().getReleaseNote()); //System.out.println("Inside(if) RN: " + rmcp.getRecommendedVersion().getReleaseNote() + " Valid: " + defaultValidator.isValid(rmcp.getRecommendedVersion().getReleaseNote())); cell.setHyperlink(releaseNotelink); } cell.setCellStyle(hlinkstyle); cell = row.createCell(3); cell.setCellValue(rmcp.getRecommendedVersion().getPackageVersion()); cell.setCellStyle(cellStyle); cell = row.createCell(4); cell.setCellValue(rmcp.getAvailability()); cell.setCellStyle(cellStyle); cell = row.createCell(5); String customers = Arrays.asList(rmcp.getRecommendedVersion().getCustomerList().toArray()) .toString(); if (customers.equalsIgnoreCase("[All]")) { customers = ""; } cell.setCellValue(customers); cell.setCellStyle(cellStyle); cell = row.createCell(6); cell.setCellValue(rmcp.getRecommendedVersion().getRisk()); cell.setCellStyle(cellStyle); cell = row.createCell(7); cell.setCellValue(rmcp.getPackageName()); XSSFHyperlink link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL); link.setAddress(rmcp.getRecommendedVersion().getDownloadLinks().iterator().next()); cell.setHyperlink((XSSFHyperlink) link); cell.setCellStyle(hlinkstyle); cell = row.createCell(8); cell.setCellValue((rmcp.getRecommendedVersion() != null && rmcp.getRecommendedVersion().isLessRecommended()) ? "#" : ""); cell.setCellStyle(cellStyle); cell = row.createCell(9); cell.setCellValue(rmcp.getRecommendedVersion().getNotes()); cell.setCellStyle(cellStyle); StringBuilder newFeatures = new StringBuilder(); for (MCPackageActivities mcpa : rmcp.getRecommendedVersion().getActivities()) { if (!mcpa.getActivityType().equalsIgnoreCase("epr")) { newFeatures.append(mcpa.getActivityType() + " " + mcpa.getActivityId() + "; "); } } cell = row.createCell(10); cell.setCellValue(newFeatures.toString()); cell.setCellStyle(cellStyle); cell = row.createCell(11); cell.setCellValue(rmcp.getRecommendedVersion().getReleaseDate()); cell.setCellStyle(dateCellStyle); } sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4); sheet.autoSizeColumn(6); sheet.autoSizeColumn(7); sheet.autoSizeColumn(8); sheet.autoSizeColumn(11); } FileOutputStream outFile = new FileOutputStream( new File(Configuration.getInstance().getRssTemplatePath() + rssFileName)); workbook.write(outFile); outFile.close(); return Configuration.getInstance().getRssTemplatePath() + rssFileName; } catch (FileNotFoundException ex) { Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex); } finally { try { file.close(); } catch (IOException ex) { Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex); } } return ""; }
From source file:in.expertsoftware.colorcheck.FormatvarificationErrorList.java
public void dumpFormatErrorToExcelFile(ArrayList<ErrorModel> get_errormodelList) throws FileNotFoundException, IOException { /***/*from ww w . ja va 2 s . c o m*/ Dump the error list into Excel file.***/ XSSFWorkbook ErrorWorkbook = new XSSFWorkbook(); XSSFSheet ErrorSheet; for (int i = 0; i < get_errormodelList.size(); i++) { int index = ErrorWorkbook.getSheetIndex(get_errormodelList.get(i).sheet_name); if (index == -1) { ErrorSheet = ErrorWorkbook.createSheet(get_errormodelList.get(i).sheet_name); XSSFRow totalError = ErrorSheet.createRow(0); XSSFRow totalWarning = ErrorSheet.createRow(1); CreaateHeaderOfErrorList(ErrorWorkbook, totalError.createCell(0), "Total Errors"); CreaateHeaderOfErrorList(ErrorWorkbook, totalWarning.createCell(0), "Total Warnings"); if ((get_errormodelList.get(i).error_level).equals("Warning")) { totalWarning.createCell(1).setCellValue(1); } else totalWarning.createCell(1).setCellValue(0); if ((get_errormodelList.get(i).error_level).equals("Error")) { totalError.createCell(1).setCellValue(1); } else totalError.createCell(1).setCellValue(0); ErrorSheet.createRow(2); XSSFRow headerrow = ErrorSheet.createRow(3); //create header of every sheet Cell Header_referenceCell = headerrow.createCell(0); CreaateHeaderOfErrorList(ErrorWorkbook, Header_referenceCell, "Cell Ref"); Cell Header_sheetname = headerrow.createCell(1); CreaateHeaderOfErrorList(ErrorWorkbook, Header_sheetname, "Sheet Name"); Cell Header_ErrorDesc = headerrow.createCell(2); CreaateHeaderOfErrorList(ErrorWorkbook, Header_ErrorDesc, "Error Description"); Cell Header_ErrorLevel = headerrow.createCell(3); CreaateHeaderOfErrorList(ErrorWorkbook, Header_ErrorLevel, "Error Level"); XSSFRow row = ErrorSheet.createRow(4); row = ErrorSheet.createRow(5); CreaateStyleOfErrorList(ErrorWorkbook, row, get_errormodelList.get(i).cell_ref, get_errormodelList.get(i).sheet_name, get_errormodelList.get(i).error_desc, get_errormodelList.get(i).error_level); ErrorSheet.autoSizeColumn(0); ErrorSheet.autoSizeColumn(1); ErrorSheet.autoSizeColumn(2); ErrorSheet.autoSizeColumn(3); } else { printinfo(ErrorWorkbook, get_errormodelList.get(i).cell_ref, get_errormodelList.get(i).sheet_name, get_errormodelList.get(i).error_desc, get_errormodelList.get(i).error_level); } } setColorInfoMetaData(ErrorWorkbook); try (FileOutputStream ErrorOutputStream = new FileOutputStream( "C:/Users/Dharam/Desktop/DIMT_NEW_CODE/ErrorSheet.xlsx")) { ErrorWorkbook.write(ErrorOutputStream); } }
From source file:in.expertsoftware.colorcheck.VerifyWorkbook.java
/** * @param args the command line arguments */// w w w . j a va2s . c o m public static void main(String[] args) throws FileNotFoundException, IOException { XSSFWorkbook workbook = null; FileInputStream DIMT_Sheet = new FileInputStream(new File(args[0])); try { workbook = new XSSFWorkbook(DIMT_Sheet); } catch (IOException ex) { ex.printStackTrace(); } VerifyTokens verifytokens = new VerifyTokens(); ArrayList get_List = verifytokens.start(workbook); ArrayList<ErrorModel> get_errormodelList = (ArrayList) get_List.get(0); ArrayList<TokenModel> get_tokenmodelList = (ArrayList) get_List.get(1); ///////////////// /*verify the metadata poaition of Reporting_Qtr*/////////////////// Metadata_Position_Of_Reporting_Qtr metadataposition_qtr = new Metadata_Position_Of_Reporting_Qtr(); int Reporting_Qtr_index = workbook.getSheetIndex("Reporting_Qtr"); if (!(metadataposition_qtr.verify_SORQ_metadata_posiition(workbook.getSheetAt(Reporting_Qtr_index), get_tokenmodelList))) { ErrorModel errmodel = new ErrorModel(); errmodel.setError_desc("SORQ Token location is not correct"); errmodel.setSheet_name("Reporting_Qtr"); errmodel.setError_level("Error"); get_errormodelList.add(errmodel); } if (!(metadataposition_qtr.verify_EORQ_metadata_posiition(workbook.getSheetAt(Reporting_Qtr_index), get_tokenmodelList))) { ErrorModel errmodel = new ErrorModel(); errmodel.setError_desc("EORQ Token location is not correct"); errmodel.setSheet_name("Reporting_Qtr"); errmodel.setError_level("Error"); get_errormodelList.add(errmodel); } /////////////////////// /*verify the metadata poaition of Reporting_Year*/////////////////// Metadata_Position_Of_Reporting_Year metadataposition_year = new Metadata_Position_Of_Reporting_Year(); int Reporting_Year_index = workbook.getSheetIndex("Reporting_Year"); if (!(metadataposition_year.verify_SORY_metadata_posiition(workbook.getSheetAt(Reporting_Year_index), get_tokenmodelList))) { ErrorModel errmodel = new ErrorModel(); errmodel.setError_desc("SORY Token location is not correct"); errmodel.setSheet_name("Reporting_Year"); errmodel.setError_level("Error"); get_errormodelList.add(errmodel); } if (!(metadataposition_year.verify_EORY_metadata_posiition(workbook.getSheetAt(Reporting_Year_index), get_tokenmodelList))) { ErrorModel errmodel = new ErrorModel(); errmodel.setError_desc("EORY Token location is not correct"); errmodel.setSheet_name("Reporting_Year"); errmodel.setError_level("Error"); get_errormodelList.add(errmodel); } int SORQtokenRow = 0; int EORQtokenRow = 0; int SORYtokenRow = 0; int EORYtokenRow = 0; int SOOWDtokenRow = 0; int EOOWDtokenRow = 0; int SOFWDtokenRow = 0; int EOFWDtokenRow = 0; for (int i = 0; i < get_tokenmodelList.size(); i++) { System.out.print( get_tokenmodelList.get(i).token_name + "row index=" + get_tokenmodelList.get(i).row_no + "\n"); if (get_tokenmodelList.get(i).token_name.equals("SORQ")) SORQtokenRow = (get_tokenmodelList.get(i).row_no); else if (get_tokenmodelList.get(i).token_name.equals("EORQ")) EORQtokenRow = (get_tokenmodelList.get(i).row_no); else if (get_tokenmodelList.get(i).token_name.equals("SORY")) SORYtokenRow = (get_tokenmodelList.get(i).row_no); else if (get_tokenmodelList.get(i).token_name.equals("EORY")) EORYtokenRow = (get_tokenmodelList.get(i).row_no); else if (get_tokenmodelList.get(i).token_name.equals("SOOWD")) SOOWDtokenRow = (get_tokenmodelList.get(i).row_no); else if (get_tokenmodelList.get(i).token_name.equals("EOOWD")) EOOWDtokenRow = (get_tokenmodelList.get(i).row_no); else if (get_tokenmodelList.get(i).token_name.equals("SOFWD")) SOFWDtokenRow = (get_tokenmodelList.get(i).row_no); else if (get_tokenmodelList.get(i).token_name.equals("EOFWD")) EOFWDtokenRow = (get_tokenmodelList.get(i).row_no); } ArrayList<String> opeartion_standard_workingSectionList = new Operation_Standard_WorkingSection_count() .working_Section(SOOWDtokenRow, EOOWDtokenRow, workbook); ArrayList<String> financial_standard_workingSectionList = new Financial_Standard_WorkingSection_count() .working_Section(SOFWDtokenRow, EOFWDtokenRow, workbook); /*ArrayList<ErrorModel> ls=(new Reporting_Qtr_Verification()).startReporting_QtrVerification(SORQtokenRow,EORQtokenRow,opeartion_standard_workingSectionList,financial_standard_workingSectionList ,workbook); ls.stream().forEach((errormodel) -> { System.out.println("ccelref "+errormodel.cell_ref+" sheetname "+errormodel.sheet_name+" dis "+errormodel.error_desc); }); */ ArrayList<ErrorModel> ls = (new Reporting_Year_Verification()).startReporting_YearVerification(SORYtokenRow, EORYtokenRow, opeartion_standard_workingSectionList, financial_standard_workingSectionList, workbook); ls.stream().forEach((errormodel) -> { System.out.println("ccelref " + errormodel.cell_ref + " sheetname " + errormodel.sheet_name + " dis " + errormodel.error_desc); }); ///error infomation on log get_errormodelList.stream().forEach((errormodel) -> { if (errormodel.row == -1) { System.out.println(errormodel.error_desc + " On sheet " + errormodel.sheet_name); } else if (errormodel.row == -2) { System.out.println(errormodel.error_desc + errormodel.sheet_name); } else { System.out.println("In " + errormodel.sheet_name + errormodel.error_desc + " at row" + errormodel.row + " and at colum" + errormodel.col); } }); //finally dump the error report to the Exxcel file //new FormatvarificationErrorList().dumpFormatErrorToExcelFile(ls); }
From source file:nl.detoren.ijsco.io.ExcelExport.java
License:Open Source License
public void exportGroepen(Groepen groepen) { String password = "abcd"; try {/* ww w.j av a2 s . c o m*/ if (groepen == null) return; // sheetindx geeft index in Excel template op basis van groepsgrootte. -1: geen sheet voor groepsgrootte int[] sheetindx = new int[] { -1, -1, -1, -1, 5, -1, 4, -1, 3, -1, 2, -1, 1, -1, 0, -1, -1, -1 }; // columnsize geeft lengte in Excel template op basis van groepsgrootte. -1: geen sheet voor groepsgrootte int[] columnsize = new int[] { -1, -1, -1, -1, 20, -1, 35, -1, 54, -1, 77, -1, 100, -1, 127, -1, -1, -1 }; // pagelngth geeft lengte in Excel template op basis van groepsgrootte. -1: geen sheet voor groepsgrootte int[] pagelngth = new int[] { -1, -1, -1, -1, 20, -1, 35, -1, 54, -1, 77, -1, 100, -1, 127, -1, -1, -1 }; int sheet2row = 2; int sheet3row = 2; FileInputStream file = new FileInputStream("Indeling.xlsm"); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFCellStyle style1 = workbook.createCellStyle(); style1.setFillPattern(FillPatternType.SOLID_FOREGROUND); style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(180, 180, 180))); XSSFCellStyle my_style = workbook.createCellStyle(); XSSFColor my_foreground = new XSSFColor(Color.ORANGE); XSSFColor my_background = new XSSFColor(Color.RED); my_style.setFillPattern(FillPatternType.SOLID_FOREGROUND); my_style.setFillForegroundColor(my_foreground); my_style.setFillBackgroundColor(my_background); XSSFSheet sheet2 = workbook.getSheet("Groepsindeling"); XSSFSheet sheet3 = workbook.getSheet("Deelnemerslijst"); updateCell(sheet3, sheet3row, 0, "Naam", style1); updateCell(sheet3, sheet3row, 1, "KNSB nr", style1); updateCell(sheet3, sheet3row, 2, "rating", style1); updateCell(sheet3, sheet3row, 3, "groep", style1); sheet3row++; for (Groep groep : groepen) { logger.log(Level.INFO, "Exporteer groep : " + groep.getNaam()); XSSFSheet sheet = workbook.cloneSheet(sheetindx[groep.getGrootte()], groep.getNaam()); updateCell(sheet, 0, 6, groep.getNaam()); updateCell(sheet2, sheet2row, 1, groep.getNaam()); sheet2row++; updateCell(sheet2, sheet2row, 0, "nr", style1); updateCell(sheet2, sheet2row, 1, "Naam", style1); updateCell(sheet2, sheet2row, 2, "KNSB nr", style1); updateCell(sheet2, sheet2row, 3, "rating", style1); sheet2row++; for (int i = 0; i < groep.getGrootte(); i++) { updateCell(sheet, 3 + i, 2, groep.getSpeler(i).getNaam()); updateCell(sheet, 3 + i, 3, groep.getSpeler(i).getKnsbnummer()); updateCell(sheet, 3 + i, 5, groep.getSpeler(i).getRating()); updateCell(sheet2, sheet2row, 0, i + 1); updateCell(sheet2, sheet2row, 1, "'" + sheet.getSheetName() + "'!" + org.apache.poi.ss.util.CellReference.convertNumToColString(2) + (4 + i), true); updateCell(sheet2, sheet2row, 2, "'" + sheet.getSheetName() + "'!" + org.apache.poi.ss.util.CellReference.convertNumToColString(3) + (4 + i), true); updateCell(sheet2, sheet2row, 3, "'" + sheet.getSheetName() + "'!" + org.apache.poi.ss.util.CellReference.convertNumToColString(5) + (4 + i), true); if (groep.getSpeler(i).getNaam() != "Bye") { updateCell(sheet3, sheet3row, 0, "'" + sheet.getSheetName() + "'!" + org.apache.poi.ss.util.CellReference.convertNumToColString(2) + (4 + i), true); updateCell(sheet3, sheet3row, 1, "'" + sheet.getSheetName() + "'!" + org.apache.poi.ss.util.CellReference.convertNumToColString(3) + (4 + i), true); updateCell(sheet3, sheet3row, 2, "'" + sheet.getSheetName() + "'!" + org.apache.poi.ss.util.CellReference.convertNumToColString(5) + (4 + i), true); updateCell(sheet3, sheet3row, 3, groep.getNaam()); } sheet2row++; sheet3row++; } sheet2row++; sheet.setForceFormulaRecalculation(true); // Set print margins XSSFPrintSetup ps = sheet.getPrintSetup(); ps.setLandscape(true); ps.setFitWidth((short) 1); sheet.setFitToPage(true); sheet.setAutobreaks(false); workbook.setPrintArea(workbook.getSheetIndex(sheet), 1, 26, 0, pagelngth[groep.getGrootte()]); sheet.setColumnBreak(18); sheet.protectSheet(password); sheet.enableLocking(); } XSSFSheet sheet4 = workbook.cloneSheet(workbook.getSheetIndex(sheet3), "Deelnemerslijst (naam)"); sortSheet(sheet4, 1, 3, 62); //XSSFSheet sheet5 = workbook.cloneSheet(workbook.getSheetIndex(sheet3), "Deelnemerslijst (rating)"); //sortSheet(sheet5, 1,4); sheet2.protectSheet(password); sheet3.protectSheet(password); sheet4.protectSheet(password); //sheet5.protectSheet(password); // Remove template sheets for (int i = 0; i < 6; i++) { workbook.removeSheetAt(0); } // Close input file file.close(); // Store Excel to new file String filename = "Indeling resultaat.xlsm"; File outputFile = new File(filename); FileOutputStream outFile = new FileOutputStream(outputFile); workbook.write(outFile); // Close output file workbook.close(); outFile.close(); // And open it in the system editor Desktop.getDesktop().open(outputFile); } catch (IOException e) { logger.log(Level.SEVERE, "Fout bij maken indeling excel : " + e.getMessage()); } }
From source file:org.apache.ofbiz.pricat.AbstractPricatParser.java
License:Apache License
public void writeCommentsToFile(XSSFWorkbook workbook, XSSFSheet sheet) { report.println();//w w w . java2 s . com report.print(UtilProperties.getMessage(resource, "WriteCommentsBackToExcel", locale), InterfaceReport.FORMAT_NOTE); FileOutputStream fos = null; XSSFCreationHelper factory = workbook.getCreationHelper(); XSSFFont boldFont = workbook.createFont(); boldFont.setFontName("Arial"); boldFont.setBold(true); boldFont.setCharSet(134); boldFont.setFontHeightInPoints((short) 9); XSSFFont plainFont = workbook.createFont(); plainFont.setFontName("Arial"); plainFont.setCharSet(134); plainFont.setFontHeightInPoints((short) 9); XSSFSheet errorSheet = null; if (errorMessages.keySet().size() > 0) { String errorSheetName = UtilDateTime.nowDateString("yyyy-MM-dd HHmm") + " Errors"; errorSheetName = WorkbookUtil.createSafeSheetName(errorSheetName); errorSheet = workbook.createSheet(errorSheetName); workbook.setSheetOrder(errorSheetName, 0); workbook.setActiveSheet(workbook.getSheetIndex(errorSheetName)); XSSFDrawing drawingPatriarch = errorSheet.getDrawingPatriarch(); if (drawingPatriarch == null) { drawingPatriarch = errorSheet.createDrawingPatriarch(); } for (int i = 0; i <= getHeaderRowNo(); i++) { XSSFRow newRow = errorSheet.createRow(i); XSSFRow row = sheet.getRow(i); newRow.setHeight(row.getHeight()); copyRow(row, newRow, factory, drawingPatriarch); } // copy merged regions for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.getFirstRow() < getHeaderRowNo()) { errorSheet.addMergedRegion(mergedRegion); } } // copy images List<XSSFPictureData> pics = workbook.getAllPictures(); List<XSSFShape> shapes = sheet.getDrawingPatriarch().getShapes(); for (int i = 0; i < shapes.size(); i++) { XSSFShape shape = shapes.get(i); XSSFAnchor anchor = shape.getAnchor(); if (shape instanceof XSSFPicture && anchor instanceof XSSFClientAnchor) { XSSFPicture pic = (XSSFPicture) shape; XSSFClientAnchor clientAnchor = (XSSFClientAnchor) anchor; if (clientAnchor.getRow1() < getHeaderRowNo()) { for (int j = 0; j < pics.size(); j++) { XSSFPictureData picture = pics.get(j); if (picture.getPackagePart().getPartName() .equals(pic.getPictureData().getPackagePart().getPartName())) { drawingPatriarch.createPicture(clientAnchor, j); } } } } } } try { // set comments in the original sheet XSSFDrawing patriarch = sheet.getDrawingPatriarch(); for (CellReference cell : errorMessages.keySet()) { if (cell != null && errorMessages.get(cell) != null) { XSSFComment comment = sheet.getCellComment(new CellAddress(cell.getRow(), cell.getCol())); boolean isNewComment = false; if (comment == null) { XSSFClientAnchor anchor = factory.createClientAnchor(); anchor.setDx1(100); anchor.setDx2(100); anchor.setDy1(100); anchor.setDy2(100); anchor.setCol1(cell.getCol()); anchor.setCol2(cell.getCol() + 4); anchor.setRow1(cell.getRow()); anchor.setRow2(cell.getRow() + 4); anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE); comment = patriarch.createCellComment(anchor); isNewComment = true; } XSSFRichTextString rts = factory.createRichTextString("OFBiz PriCat:\n"); rts.applyFont(boldFont); rts.append(errorMessages.get(cell), plainFont); comment.setString(rts); comment.setAuthor("Apache OFBiz PriCat"); if (isNewComment) { sheet.getRow(cell.getRow()).getCell(cell.getCol()).setCellComment(comment); OFBizPricatUtil.formatCommentShape(sheet, cell); } } } // set comments in the new error sheet XSSFDrawing errorPatriarch = errorSheet.getDrawingPatriarch(); int newRowNum = getHeaderRowNo() + 1; Map<Integer, Integer> rowMapping = new HashMap<Integer, Integer>(); for (CellReference cell : errorMessages.keySet()) { if (cell != null && errorMessages.get(cell) != null) { XSSFRow row = sheet.getRow(cell.getRow()); Integer rowNum = Integer.valueOf(row.getRowNum()); int errorRow = newRowNum; if (rowMapping.containsKey(rowNum)) { errorRow = rowMapping.get(rowNum).intValue(); } else { XSSFRow newRow = errorSheet.getRow(errorRow); if (newRow == null) { newRow = errorSheet.createRow(errorRow); } rowMapping.put(rowNum, Integer.valueOf(errorRow)); newRow.setHeight(row.getHeight()); copyRow(row, newRow, factory, errorPatriarch); newRowNum++; } } } // write to file if (sequenceNum > 0L) { File commentedExcel = FileUtil.getFile(tempFilesFolder + userLoginId + "/" + sequenceNum + ".xlsx"); fos = new FileOutputStream(commentedExcel); workbook.write(fos); } else { fos = new FileOutputStream(pricatFile); workbook.write(fos); } fos.flush(); fos.close(); workbook.close(); } catch (FileNotFoundException e) { report.println(e); Debug.logError(e, module); } catch (IOException e) { report.println(e); Debug.logError(e, module); } finally { if (fos != null) { try { fos.close(); } catch (IOException e) { Debug.logError(e, module); } } if (workbook != null) { try { workbook.close(); } catch (IOException e) { Debug.logError(e, module); } } } report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); report.println(); }