in pricat/src/main/java/org/apache/ofbiz/pricat/AbstractPricatParser.java [305:458]
public void writeCommentsToFile(XSSFWorkbook workbook, XSSFSheet sheet) {
if (workbook == null) {
Debug.logError("workbook war is null; Nothing can be done", MODULE);
}
report.println();
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().isEmpty()) {
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
if (errorSheet != null) {
XSSFDrawing errorPatriarch = errorSheet.getDrawingPatriarch();
int newRowNum = getHeaderRowNo() + 1;
Map<Integer, Integer> rowMapping = new HashMap<>();
for (CellReference cell : errorMessages.keySet()) {
if (cell != null && errorMessages.get(cell) != null) {
XSSFRow row = sheet.getRow(cell.getRow());
Integer rowNum = row.getRowNum();
int errorRow = newRowNum;
if (rowMapping.containsKey(rowNum)) {
errorRow = rowMapping.get(rowNum);
} else {
XSSFRow newRow = errorSheet.getRow(errorRow);
if (newRow == null) {
newRow = errorSheet.createRow(errorRow);
}
rowMapping.put(rowNum, errorRow);
newRow.setHeight(row.getHeight());
copyRow(row, newRow, factory, errorPatriarch);
newRowNum++;
}
}
}
}
// write to file
if (sequenceNum > 0L) {
File commentedExcel = FileUtil.getFile(TEMP_FILES_FOLDER + 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 (IOException e) {
report.println(e);
Debug.logError(e, MODULE);
} finally {
if (fos != null) {
try {
fos.close();
} catch (IOException e) {
Debug.logError(e, MODULE);
}
}
try {
workbook.close();
} catch (IOException e) {
Debug.logError(e, MODULE);
}
}
report.println(UtilProperties.getMessage(RESOURCE, "ok", locale), InterfaceReport.FORMAT_OK);
report.println();
}