• Home
• com.okworx.ilcd.validation
• ilcd-validation
• 2.0.10
• source code
• XLSLog.java

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

com.okworx.ilcd.validation.log.XLSLog Maven / Gradle / Ivy

package com.okworx.ilcd.validation.log;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.okworx.ilcd.validation.IDatasetsValidator;
import com.okworx.ilcd.validation.IValidator;
import com.okworx.ilcd.validation.LinkValidator;
import com.okworx.ilcd.validation.ReferenceFlowValidator;
import com.okworx.ilcd.validation.ValidatorChain;
import com.okworx.ilcd.validation.events.EventsList;
import com.okworx.ilcd.validation.events.IValidationEvent;
import com.okworx.ilcd.validation.util.Statistics;

public class XLSLog {

	private static final int MAX_ROWNUMS_FOR_AUTOSIZING = 1000;

	private static final String TABLE_HEADING_RESULT = "Result";

	private static final String FAILED = "FAILED";

	private static final String PASSED = "PASSED";

	private static final String FILE_SUFFIX_XLSX = ".xlsx";

	private static final String TABLE_HEADING_REFERENCED_DATASET_NAME = "Referenced dataset name";

	private static final String TABLE_HEADING_REFERENCED_DATASET_UUID = "Referenced dataset UUID";

	private static final String TABLE_HEADING_REFERENCED_DATASET_TYPE = "Referenced dataset type";

	private static final String TABLE_HEADING_MESSAGE = "Message";

	private static final String TABLE_HEADING_NAME = "Name";

	private static final String TABLE_HEADING_VERSION = "Version";

	private static final String TABLE_HEADING_UUID = "UUID";

	private static final String TABLE_HEADING_DATASET_TYPE = "Dataset type";

	private static final String TABLE_HEADING_FILENAME = "Filename";

	private static final String TABLE_HEADING_ASPECT = "Aspect";

	private static final String TABLE_HEADING_SEVERITY = "Severity";

	private Workbook workbook;

	private Map sheets = new HashMap();

	private Sheet summarySheet = null;

	private Sheet generalSheet = null;

	private int summaryRow = 0;

	private CellStyle boldStyle;

	private CellStyle italicStyle;

	private CellStyle redStyle;

	private CellStyle greenStyle;

	private CellStyle summaryStyle;

	private CellStyle summaryStyleBold;

	private CellStyle tableHeadingStyle;

	private CellStyle wrapStyle;

	public XLSLog(List validators, String filename, HashMap extraProperties) {

		this.workbook = new SXSSFWorkbook(100);

		setupStyles();

		createSummarySheet(filename, validators, extraProperties);

		createLogSheets(validators);

	}

	private void createLogSheets(List validators) {
		for (IValidator validator : validators) {
			Sheet sheet = this.workbook.createSheet(validator.getAspectName());
			for (int i=0; i<=11; i++) {
				((SXSSFSheet) sheet).trackColumnForAutoSizing(i);
			}
			this.sheets.put(validator, sheet);
			writeEventsHeader(validator);
			
			if (validator instanceof LinkValidator || validator instanceof ReferenceFlowValidator)
				sheet.setAutoFilter(CellRangeAddress.valueOf("A:K"));
			else
				sheet.setAutoFilter(CellRangeAddress.valueOf("A:H"));
		}
	}

	private void createGeneralSheet() {
		this.generalSheet = this.workbook.createSheet("General");
		((SXSSFSheet) this.generalSheet).trackAllColumnsForAutoSizing();
		this.workbook.setSheetOrder("General", 1);
	}
	
	private void createSummarySheet(String filename,
			List validators, HashMap extraProperties) {
		this.summarySheet = this.workbook.createSheet("Summary");

		summaryAdd(this.summaryStyleBold, "Validation summary");
		summaryAdd("");
		summaryAdd("File:", filename);
		this.summarySheet.addMergedRegion(new CellRangeAddress(
				this.summaryRow - 1, this.summaryRow - 1, 1, 20));
		summaryAdd("");
		summaryAdd("generated:", new Date().toString());
		if (extraProperties != null) {
			for (String key : extraProperties.keySet()) {
				summaryAdd(key, extraProperties.get(key));
			}
		}
		
		this.summarySheet.addMergedRegion(new CellRangeAddress(
				this.summaryRow - 1, this.summaryRow - 1, 1, 20));
		summaryAdd("");
		summaryAdd("Profile:", validators.get(0).getProfile().getName() + " " + validators.get(0).getProfile().getVersion());
		this.summarySheet.addMergedRegion(new CellRangeAddress(this.summaryRow - 1, this.summaryRow - 1, 1, 20));
		if (validators.get(0).getProfile().getDescription() != null) {
			summaryAdd("Profile description:", validators.get(0).getProfile().getDescription());
			this.summarySheet.addMergedRegion(new CellRangeAddress(this.summaryRow - 1, this.summaryRow - 1, 1, 20));
		}
		summaryAdd("");
		summaryAdd("Options:");
		for (IValidator validator : validators) {
			if (validator.getParameters().keySet().size() > 0) {
				summaryAdd(italicStyle, "    " + validator.getAspectName());
				List params = new ArrayList(validator.getParameters().keySet());
				Collections.sort(params);
				for (String paramName : params) {
					summaryAdd(italicStyle, "        " + paramName, validator.getParameters().get(paramName).toString());
				}
			}
		}
		summaryAdd("");
		summaryAdd("");
		summaryAdd(this.summaryStyleBold, TABLE_HEADING_ASPECT, TABLE_HEADING_RESULT);

		((SXSSFSheet) this.summarySheet).trackColumnForAutoSizing(0);
		((SXSSFSheet) this.summarySheet).trackColumnForAutoSizing(1);
		((SXSSFSheet) this.summarySheet).trackColumnForAutoSizing(2);
	}

	private void setupStyles() {
		this.boldStyle = workbook.createCellStyle();
		Font boldFont = workbook.createFont();
		boldFont.setBold(true);
		this.boldStyle.setFont(boldFont);

		this.italicStyle = workbook.createCellStyle();
		Font italicFont = workbook.createFont();
		italicFont.setItalic(true);
		this.italicStyle.setFont(italicFont);

		this.greenStyle = workbook.createCellStyle();
		Font greenFont = workbook.createFont();
		greenFont.setFontHeightInPoints((short) 14);
		greenFont.setColor(IndexedColors.GREEN.getIndex());
		this.greenStyle.setFont(greenFont);

		this.redStyle = workbook.createCellStyle();
		Font redFont = workbook.createFont();
		redFont.setFontHeightInPoints((short) 14);
		redFont.setColor(IndexedColors.RED.getIndex());
		this.redStyle.setFont(redFont);

		this.summaryStyle = workbook.createCellStyle();
		Font summaryFont = workbook.createFont();
		summaryFont.setFontHeightInPoints((short) 14);
		this.summaryStyle.setFont(summaryFont);

		this.summaryStyleBold = workbook.createCellStyle();
		Font bigFont = workbook.createFont();
		bigFont.setBold(true);
		bigFont.setFontHeightInPoints((short) 14);
		this.summaryStyleBold.setFont(bigFont);

		this.tableHeadingStyle = workbook.createCellStyle();
		this.tableHeadingStyle.setFont(bigFont);
		this.tableHeadingStyle.setFillBackgroundColor(IndexedColors.PALE_BLUE
				.getIndex());
		this.tableHeadingStyle.setFillPattern(FillPatternType.SPARSE_DOTS);

		this.wrapStyle = workbook.createCellStyle();
		this.wrapStyle.setWrapText(true);
	}

	public Sheet getSheet(IValidator validator) {
		return this.sheets.get(validator);
	}

	public void summaryAdd(String... strings) {
		summaryAdd(null, strings);
	}

	public void summaryAdd(CellStyle style, String... strings) {

		Row row = this.summarySheet.createRow(summaryRow);

		for (int cellnum = 0; cellnum < strings.length; cellnum++) {
			Cell cell = row.createCell(cellnum);
			cell.setCellValue(strings[cellnum]);
			if (style != null)
				cell.setCellStyle(style);
			else
				cell.setCellStyle(summaryStyle);
		}

		summaryRow++;
	}

	public void summaryAddNumbers(String string, Integer... integers) {

		Row row = this.summarySheet.createRow(summaryRow);

		Cell cell = row.createCell(0);
		cell.setCellValue(string);
		cell.setCellStyle(summaryStyle);

		for (int cellnum = 0; cellnum < integers.length; cellnum++) {
			cell = row.createCell(cellnum + 1);
			cell.setCellType(CellType.NUMERIC);
			cell.setCellValue(integers[cellnum]);
			cell.setCellStyle(summaryStyle);
		}
		
		summaryRow++;
	}

	public void summaryAddRedGreen(String string, Integer int1, Integer int2) {

		Row row = this.summarySheet.createRow(summaryRow);

		Cell cell = row.createCell(0);
		cell.setCellValue(string);
		cell.setCellStyle(summaryStyle);

		cell = row.createCell(1);
		cell.setCellType(CellType.NUMERIC);
		cell.setCellValue(int1);
		cell.setCellStyle(greenStyle);

		cell = row.createCell(2);
		cell.setCellType(CellType.NUMERIC);
		cell.setCellValue(int2);
		cell.setCellStyle(summaryStyle);
		if (int2 > 0)
			cell.setCellStyle(redStyle);

		summaryRow++;
	}

	public void summaryAddResult(boolean result, IValidator validator) {

		Row row = this.summarySheet.createRow(summaryRow);

		Cell cell = row.createCell(0);
		cell.setCellValue(validator.getAspectName());
		
//		CreationHelper factory = this.workbook.getCreationHelper();
//		Drawing drawing = this.summarySheet.getDrawingPatriarch();
//		if (drawing == null)
//			drawing = this.summarySheet.createDrawingPatriarch();
//		 
//	    // When the comment box is visible, have it show in a 1x3 space
//	    ClientAnchor anchor = factory.createClientAnchor();
//	    anchor.setCol1(cell.getColumnIndex());
//	    anchor.setCol2(cell.getColumnIndex()+1);
//	    anchor.setRow1(row.getRowNum());
//	    anchor.setRow2(row.getRowNum()+3);
//
//	    // Create the comment and set the text+author
//	    Comment comment = drawing.createCellComment(anchor);
//	    RichTextString str = factory.createRichTextString(validator.getAspectDescription());
//	    comment.setString(str);
//
//	    // Assign the comment to the cell
//	    cell.setCellComment(comment);

		cell.setCellStyle(this.summaryStyle);

		writeResult(result, row);

		summaryRow++;
	}

	public void writeEventsHeader(IValidator validator) {
		Sheet sheet = (validator instanceof ValidatorChain ? this.generalSheet : getSheet(validator));

		Row row = sheet.createRow(0);

		Cell cell = row.createCell(0);
		cell.setCellValue(TABLE_HEADING_SEVERITY);
		cell.setCellStyle(this.tableHeadingStyle);

		cell = row.createCell(1);
		cell.setCellValue(TABLE_HEADING_ASPECT);
		cell.setCellStyle(this.tableHeadingStyle);

		cell = row.createCell(2);
		cell.setCellValue(TABLE_HEADING_FILENAME);
		cell.setCellStyle(this.tableHeadingStyle);

		cell = row.createCell(3);
		cell.setCellValue(TABLE_HEADING_DATASET_TYPE);
		cell.setCellStyle(this.tableHeadingStyle);

		cell = row.createCell(4);
		cell.setCellValue(TABLE_HEADING_UUID);
		cell.setCellStyle(this.tableHeadingStyle);

		cell = row.createCell(5);
		cell.setCellValue(TABLE_HEADING_VERSION);
		cell.setCellStyle(this.tableHeadingStyle);

		cell = row.createCell(6);
		cell.setCellValue(TABLE_HEADING_NAME);
		cell.setCellStyle(this.tableHeadingStyle);

		cell = row.createCell(7);
		cell.setCellValue(TABLE_HEADING_MESSAGE);
		cell.setCellStyle(this.tableHeadingStyle);

		if (validator instanceof LinkValidator || validator instanceof ReferenceFlowValidator) {
			cell = row.createCell(8);
			cell.setCellValue(TABLE_HEADING_REFERENCED_DATASET_TYPE);
			cell.setCellStyle(this.tableHeadingStyle);
	
			cell = row.createCell(9);
			cell.setCellValue(TABLE_HEADING_REFERENCED_DATASET_UUID);
			cell.setCellStyle(this.tableHeadingStyle);
	
			cell = row.createCell(10);
			cell.setCellValue(TABLE_HEADING_REFERENCED_DATASET_NAME);
			cell.setCellStyle(this.tableHeadingStyle);
		}
	}

	public void writeEvents(IValidator validator) {
		Sheet sheet;
		
		if (validator instanceof ValidatorChain) {
			this.createGeneralSheet();
			this.writeEventsHeader(validator);
			sheet = this.generalSheet;
		} else {
			sheet = getSheet(validator);
		}

		int rowNum = 1;

		for (IValidationEvent event : validator.getEventsList().getEvents()) {
			Row row = sheet.createRow(rowNum);

			Cell cell = row.createCell(0);
			cell.setCellValue(event.getSeverity().getValue());

			cell = row.createCell(1);
			cell.setCellValue(event.getAspect());

			cell = row.createCell(2);
			cell.setCellValue(event.getReference().getShortFileName());

			cell = row.createCell(3);
			if (event.getReference().getType() != null)
				cell.setCellValue(event.getReference().getType().getValue());

			cell = row.createCell(4);
			cell.setCellValue(event.getReference().getUuid());

			cell = row.createCell(5);
			cell.setCellValue(event.getReference().getVersion());

			cell = row.createCell(6);
			cell.setCellValue(event.getReference().getName());

			if (validator instanceof LinkValidator || validator instanceof ReferenceFlowValidator) {
				cell = row.createCell(7);
				cell.setCellValue(event.getAltMessage());
				cell.setCellStyle(wrapStyle); 
	
				cell = row.createCell(8);
				try {
					cell.setCellValue(event.getMessageReference().getType().getValue());
				} catch (Exception e) {
				}
	
				cell = row.createCell(9);
				try {
					cell.setCellValue(event.getMessageReference().getUuid());
				} catch (Exception e) {
				}
	
				cell = row.createCell(10);
				try {
					cell.setCellValue(event.getMessageReference().getName());
				} catch (Exception e) {
				}
			} else {
				cell = row.createCell(7);
				cell.setCellValue(event.getMessage());				
			}
			
			rowNum++;

			// for very large numbers of rows, we're disabling column auto sizing for the remaining rows
			if (rowNum == MAX_ROWNUMS_FOR_AUTOSIZING) {
				for (int i=0; i<=11; i++) {
					sheet.autoSizeColumn(i);
					((SXSSFSheet) sheet).untrackColumnForAutoSizing(i);
				}
			}

		}

	}

	public void writeAndClose(String sourceFileName, boolean result)
			throws IOException {

		this.summarySheet.autoSizeColumn(0);
		this.summarySheet.autoSizeColumn(1);
		this.summarySheet.autoSizeColumn(2);

		if (this.generalSheet != null) {
			for (int c = 0; c < 8; c++)
				this.generalSheet.autoSizeColumn(c);
		}
		
		for (Sheet sheet : this.sheets.values()) {
			// remove empty sheets
			if (sheet.getLastRowNum() == 0) {
				workbook.removeSheetAt(workbook.getSheetIndex(sheet));
			} else {
				// autosize remaining sheets unless autosizing has been turned off
				if (sheet.getLastRowNum() < MAX_ROWNUMS_FOR_AUTOSIZING) { 
					for (int c = 0; c < 11; c++) {
						sheet.autoSizeColumn(c);
					}
				}
			}
		}

		String logFileName = sourceFileName.concat("_").concat(
				result ? PASSED : FAILED).concat(FILE_SUFFIX_XLSX);

		FileOutputStream out = new FileOutputStream(logFileName);
		this.workbook.write(out);
		out.close();

		((SXSSFWorkbook) this.workbook).dispose();
	}

	private void writeResult(boolean result, Row row) {

		Cell cell1 = row.createCell(1);

		if (result) {
			cell1.setCellValue(PASSED);
			cell1.setCellStyle(greenStyle);
		} else {
			cell1.setCellValue(FAILED);
			cell1.setCellStyle(redStyle);
		}
	}

	public void writeStats(Statistics statistics, EventsList eventsList) {
		summaryAdd("");
		summaryAddNumbers("Errors:", eventsList.getErrorCount());
		if (eventsList.getWarningCount() > 0)
			summaryAddNumbers("Warnings:", eventsList.getWarningCount());
		summaryAdd("");
		summaryAdd("");
		summaryAdd("");
		summaryAdd(this.summaryStyleBold, "Statistics");
		summaryAddNumbers("Total files processed:", statistics.getTotalCount());
		summaryAdd("");
		summaryAdd("", "valid", "invalid");
		summaryAddRedGreen("Process datasets", statistics.getValidProcessesCount(),
				statistics.getInvalidProcessesCount());
		summaryAddRedGreen("Flow datasets", statistics.getValidFlowsCount(), statistics.getInvalidFlowsCount());
		summaryAddRedGreen("Source datasets", statistics.getValidSourcesCount(),
				statistics.getInvalidSourcesCount());
		summaryAddRedGreen("Contact datasets", statistics.getValidContactsCount(),
				statistics.getInvalidContactsCount());
		summaryAddRedGreen("LCIA Method datasets", statistics.getValidLCIAMethodsCount(),
				statistics.getInvalidLCIAMethodsCount());
		summaryAddRedGreen("Flow Property datasets", statistics.getValidFlowPropertiesCount(),
				statistics.getInvalidFlowPropertiesCount());
		summaryAddRedGreen("Unit Group datasets", statistics.getValidUnitGroupsCount(),
				statistics.getInvalidUnitGroupsCount());
		summaryAddRedGreen("Life Cycle Model datasets", statistics.getValidLCModelsCount(),
				statistics.getInvalidLCModelsCount());
		summaryAddRedGreen("External files", statistics.getValidExternalFilesCount(),
				statistics.getInvalidExternalFilesCount());
	}
}