/*******************************************************************************
 * Copyright 2Fi Business Solutions Ltd.
 *
 * This code is copyrighted. Under no circumstances should any party, people,
 * or organization should redistribute any portions of this code in any form,
 * either verbatim or through electronic media, to any third parties, unless
 * under explicit written permission by 2Fi Business Solutions Ltd.
 ******************************************************************************/
package com.ffii.core.utils;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.security.GeneralSecurityException;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.crypt.EncryptionMode;
import org.apache.poi.poifs.crypt.Encryptor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.RichTextString;
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.usermodel.XSSFWorkbook;
import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;

/**
 * Excel Utils (for Apache POI 3.15 to 3.17)
 *
 * @author Patrick
 * @version 2018-04-06
 */
public abstract class ExcelUtils {

	/**
	 * static A to Z char array
	 */
	private static final char[] A2Z = { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P',
			'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };

	private static final DataFormatter DATA_FORMATTER = new DataFormatter();

	/** max rows limit of .xls **/
	public static final int MAX_ROWS = 65536;
	/** max columns limit of .xls **/
	public static final int MAX_COLS = 256;

	/**
	 * Column reference to index (0-based) map, support up to 256 columns
	 * (compatible with .xls format)
	 */
	public static final Map<String, Integer> COL_IDX = new HashMap<String, Integer>(MAX_COLS, 1.0f);

	static {
		for (int columnIndex = 0; columnIndex < MAX_COLS; columnIndex++) {
			int tempColumnCount = columnIndex;
			StringBuilder sb = new StringBuilder(2);
			do {
				sb.insert(0, A2Z[tempColumnCount % 26]);
				tempColumnCount = (tempColumnCount / 26) - 1;
			} while (tempColumnCount >= 0);
			COL_IDX.put(sb.toString(), Integer.valueOf(columnIndex));
		}
	}

	/**
	 * Load XSSF workbook (xlsx file) from template source.
	 *
	 * @param url the relative path to the template source, e.g.
	 *            "WEB-INF/excel/exampleReportTemplate.xlsx"
	 *
	 * @return the workbook, or null if the template file cannot be loaded
	 */
	public static final Workbook loadXSSFWorkbookFromTemplateSource(ResourceLoader resourceLoader, String url) {
		Resource resource = resourceLoader.getResource(url);
		try {
			return new XSSFWorkbook(resource.getInputStream());
		} catch (IOException e) {
			return null;
		}
	}

	/**
	 * Write the workbook to byte array.
	 *
	 * @param workbook The Excel workbook (cannot be null)
	 *
	 * @return the byte[], or null if IO exception occurred
	 */
	public static final byte[] toByteArray(Workbook workbook) {
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		try {
			workbook.write(baos);
		} catch (IOException e) {
			return null;
		}
		return baos.toByteArray();
	}

	/**
	 * Check if the cell exists in the given sheet, row and column.
	 *
	 * @param sheet    the Sheet (cannot be null)
	 * @param rowIndex 0-based row index
	 * @param colIndex 0-based column index
	 *
	 * @return {@code true} if cell exists, else {@code false}
	 */
	public static final boolean isCellExists(Sheet sheet, int rowIndex, int colIndex) {
		Row row = sheet.getRow(rowIndex);
		if (row != null) {
			Cell cell = row.getCell(colIndex);
			return cell != null;
		}
		return false;
	}

	/**
	 * Convenient method to obtain the cell in the given sheet, row and column.
	 * <p>
	 * Creates the row and the cell if not already exist.
	 *
	 * @param sheet    the Sheet (cannot be null)
	 * @param rowIndex 0-based row index
	 * @param colIndex 0-based column index
	 *
	 * @return the Cell (never null)
	 */
	public static final Cell getCell(Sheet sheet, int rowIndex, int colIndex) {
		Row row = sheet.getRow(rowIndex);
		if (row == null) {
			row = sheet.createRow(rowIndex);
		}
		Cell cell = row.getCell(colIndex);
		if (cell == null) {
			cell = row.createCell(colIndex);
		}
		return cell;
	}

	/**
	 * Get column index by column reference (support up to 256 columns)
	 *
	 * @param columnRef column reference such as "A", "B", "AA", "AB"...
	 *
	 * @return the column index
	 *
	 * @throws NullPointerException if column reference is invalid or the index
	 *                              exceeds 256
	 */
	public static final int getColumnIndex(String columnRef) {
		return COL_IDX.get(columnRef);
	}

	/**
	 * Get column reference by column index
	 *
	 * @param columnIndex 0-based column index
	 *
	 * @return the column reference such as "A", "B", "AA", "AB"...
	 */
	public static final String getColumnRef(int columnIndex) {
		StringBuilder sb = new StringBuilder();
		int tempColumnCount = columnIndex;
		do {
			sb.insert(0, A2Z[tempColumnCount % 26]);
			tempColumnCount = (tempColumnCount / 26) - 1;
		} while (tempColumnCount >= 0);
		return sb.toString();
	}

	/**
	 * Get the Excel Cell Ref String by columnIndex and rowIndex
	 *
	 * @param columnIndex 0-based column index
	 * @param rowIndex    0-based row index
	 */
	public static final String getCellRefString(int columnIndex, int rowIndex) {
		StringBuilder sb = new StringBuilder();
		int tempColumnCount = columnIndex;
		do {
			sb.insert(0, A2Z[tempColumnCount % 26]);
			tempColumnCount = (tempColumnCount / 26) - 1;
		} while (tempColumnCount >= 0);
		sb.append(rowIndex + 1);
		return sb.toString();
	}

	/**
	 * Get Cell value as <code>String</code>
	 */
	public static String getStringValue(Cell cell) {
		if (cell != null && cell.getCellTypeEnum() == CellType.FORMULA) {
			try {
				return cell.getStringCellValue();
			} catch (Exception e) {
				return "";
			}
		}
		return DATA_FORMATTER.formatCellValue(cell);
	}

	/**
	 * Get Cell value as <code>BigDecimal</code>, with a fallback value
	 * <p>
	 * Only support {@link Cell#CELL_TYPE_NUMERIC} and {@link Cell#CELL_TYPE_STRING}
	 *
	 * @return the <code>BigDecimal</code> value, or the default value if cell is
	 *         <code>null</code> or cell type is {@link Cell#CELL_TYPE_BLANK}
	 */
	public static BigDecimal getDecimalValue(Cell cell, BigDecimal defaultValue) {
		if (cell == null || cell.getCellTypeEnum() == CellType.BLANK)
			return defaultValue;
		if (cell.getCellTypeEnum() == CellType.STRING) {
			return NumberUtils.toDecimal(cell.getStringCellValue());
		} else {
			return BigDecimal.valueOf(cell.getNumericCellValue());
		}
	}

	/**
	 * Get Cell value as <code>BigDecimal</code>
	 * <p>
	 * Only support {@link Cell#CELL_TYPE_NUMERIC} and {@link Cell#CELL_TYPE_STRING}
	 *
	 * @return the <code>BigDecimal</code> value, or <code>BigDecimal.ZERO</code> if
	 *         cell is <code>null</code> or cell type is
	 *         {@link Cell#CELL_TYPE_BLANK}
	 */
	public static BigDecimal getDecimalValue(Cell cell) {
		return getDecimalValue(cell, BigDecimal.ZERO);
	}

	/**
	 * Get Cell value as <code>double</code>
	 * <p>
	 * Only support {@link Cell#CELL_TYPE_NUMERIC} and {@link Cell#CELL_TYPE_STRING}
	 */
	public static double getDoubleValue(Cell cell) {
		if (cell == null)
			return 0.0;
		if (cell.getCellTypeEnum() == CellType.STRING) {
			return NumberUtils.toDouble(cell.getStringCellValue());
		} else {
			return cell.getNumericCellValue();
		}
	}

	/**
	 * Get Cell value as <code>int</code> (rounded half-up to the nearest integer)
	 * <p>
	 * Only support {@link Cell#CELL_TYPE_NUMERIC} and {@link Cell#CELL_TYPE_STRING}
	 */
	public static int getIntValue(Cell cell) {
		return (int) NumberUtils.round(getDoubleValue(cell), 0);
	}

	/**
	 * Get Cell Integer value (truncated)
	 */
	public static Integer getIntValue(Cell cell, Integer defaultValue) {
		if (cell == null)
			return defaultValue;
		if (cell.getCellTypeEnum() == CellType.STRING) {
			return NumberUtils.toInt(cell.getStringCellValue(), defaultValue);
		} else {
			return (int) cell.getNumericCellValue();
		}
	}

	/**
	 * Get Cell Date value
	 */
	public static Date getDateValue(Cell cell) {
		if (cell == null)
			return null;
		if (cell.getCellTypeEnum() == CellType.STRING) {
			return DateUtils.parseDateStrictly(cell.getStringCellValue(), DateUtils.PARSE_PATTERNS, null);
		}
		if (DateUtil.isCellDateFormatted(cell)) {
			try {
				return DateUtil.getJavaDate(cell.getNumericCellValue());
			} catch (NumberFormatException e) {
				return null;
			}
		} else {
			return null;
		}
	}

	/**
	 * Convenient method to set Cell value
	 *
	 * @param cell  the Cell (cannot be null)
	 * @param value the value to set
	 */
	public static void setCellValue(Cell cell, Object value) {
		if (value instanceof String)
			cell.setCellValue((String) value);
		else if (value instanceof RichTextString)
			cell.setCellValue((RichTextString) value);
		else if (value instanceof Number)
			cell.setCellValue(((Number) value).doubleValue());
		else if (value instanceof Boolean)
			cell.setCellValue(((Boolean) value).booleanValue());
		else if (value instanceof Calendar)
			cell.setCellValue((Calendar) value);
		else if (value instanceof Date)
			cell.setCellValue((Date) value);
		else if (value == null)
			cell.setCellValue("");
		else
			throw new IllegalArgumentException(value.getClass().toString() + " is not supported");
	}

	/**
	 * Convenient method to set Cell value by Sheet, row index, and column index
	 *
	 * @param sheet    the Sheet (cannot be null)
	 * @param rowIndex 0-based row index
	 * @param colIndex 0-based column index
	 * @param value    the value to set
	 */
	public static void setCellValue(Sheet sheet, int rowIndex, int colIndex, Object value) {
		setCellValue(getCell(sheet, rowIndex, colIndex), value);
	}

	/**
	 * Increase Row Height (if necessary, but never decrease it) by counting the no.
	 * of lines in a String value
	 *
	 * @param sheet          The Excel worksheet
	 * @param row            The row index (0-based)
	 * @param value          The (multi-line) String value to count for the no. of
	 *                       lines
	 * @param heightInPoints The height (in points) for 1 line of text
	 */
	public static void increaseRowHeight(Sheet sheet, int row, String value, int heightInPoints) {
		int lines = StringUtils.countMatches(value, "\n") + 1; // count no. of lines
		float newHeight = heightInPoints * lines;

		Row r = sheet.getRow(row);
		if (r == null)
			r = sheet.createRow(row);

		// increase the row height if necessary, but never decrease it
		if (r.getHeightInPoints() < newHeight) {
			r.setHeightInPoints(newHeight);
		}
	}

	/**
	 * Add merged region (i.e. merge cells)
	 *
	 * @param sheet       The Excel worksheet
	 * @param firstRowIdx The first row index (0-based)
	 * @param lastRowIdx  The last row index (0-based)
	 * @param firstColIdx The first column index (0-based)
	 * @param lastColIdx  The last column index (0-based)
	 */
	public static void addMergedRegion(Sheet sheet, int firstRowIdx, int lastRowIdx, int firstColIdx, int lastColIdx) {
		CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRowIdx, lastRowIdx, firstColIdx, lastColIdx);
		sheet.addMergedRegion(cellRangeAddress);
	}

	/**
	 * Copy and Insert Row
	 *
	 * @param workbook          The Excel workbook
	 * @param sourceSheet       The source Excel worksheet
	 * @param destinationSheet  The destination Excel worksheet
	 * @param sourceRowNum      The source row index (0-based) to copy from
	 * @param destinationRowNum The destination row index (0-based) to insert into
	 *                          (from the copied row)
	 */
	public static void copyAndInsertRow(Workbook workbook, Sheet sourceSheet, Sheet destinationSheet, int sourceRowNum,
			int destinationRowNum) {
		// get the source / destination row
		Row sourceRow = sourceSheet.getRow(sourceRowNum);
		Row destRow = destinationSheet.getRow(destinationRowNum);

		// if the row exist in destination, push down all rows by 1
		if (destRow != null) {
			destinationSheet.shiftRows(destinationRowNum, destinationSheet.getLastRowNum(), 1, true, false);
		}
		// create a new row
		destRow = destinationSheet.createRow(destinationRowNum);

		// loop through source columns to add to new row
		for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
			// grab a copy of the old cell
			Cell oldCell = sourceRow.getCell(i);

			// if the old cell is null jump to next cell
			if (oldCell == null)
				continue;

			// create a new cell in destination row
			Cell newCell = destRow.createCell(i);

			// apply cell style to new cell from old cell
			newCell.setCellStyle(oldCell.getCellStyle());

			// if there is a cell comment, copy
			if (oldCell.getCellComment() != null) {
				newCell.setCellComment(oldCell.getCellComment());
			}

			// if there is a cell hyperlink, copy
			if (oldCell.getHyperlink() != null) {
				newCell.setHyperlink(oldCell.getHyperlink());
			}

			// copy the cell data type
			newCell.setCellType(oldCell.getCellTypeEnum());

			// copy the cell data value
			switch (oldCell.getCellTypeEnum()) {
			case NUMERIC:
				newCell.setCellValue(oldCell.getNumericCellValue());
				break;
			case STRING:
				newCell.setCellValue(oldCell.getRichStringCellValue());
				break;
			case FORMULA:
				newCell.setCellFormula(oldCell.getCellFormula());
				break;
			case BLANK:
				newCell.setCellValue(oldCell.getStringCellValue());
				break;
			case BOOLEAN:
				newCell.setCellValue(oldCell.getBooleanCellValue());
				break;
			case ERROR:
				newCell.setCellErrorValue(oldCell.getErrorCellValue());
				break;
			default:
				break;
			}
		}

		// if there are any merged regions in the source row, copy to new row
		for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
			CellRangeAddress cellRangeAddress = sourceSheet.getMergedRegion(i);
			if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
				addMergedRegion(destinationSheet, destRow.getRowNum(),
						(destRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
						cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
			}
		}

		// copy row height
		destRow.setHeight(sourceRow.getHeight());
	}

	/**
	 * Copy and Insert Row
	 *
	 * @param workbook          The Excel workbook
	 * @param sheet             The Excel worksheet
	 * @param sourceRowNum      The source row index (0-based) to copy from
	 * @param destinationRowNum The destination row index (0-based) to insert into
	 *                          (from the copied row)
	 */
	public static void copyAndInsertRow(Workbook workbook, Sheet sheet, int sourceRowNum, int destinationRowNum) {
		copyAndInsertRow(workbook, sheet, sheet, sourceRowNum, destinationRowNum);
	}

	/**
	 * Copy Column
	 * 
	 * @param workbook             The Excel workbook
	 * @param sourceSheet          The source Excel worksheet
	 * @param destinationSheet     The destination Excel worksheet
	 * @param rowStart             The source row start index (0-based) to copy from
	 * @param rowEnd               The source row end index (0-based) to copy from
	 * @param sourceColumnNum      The source column index (0-based) to copy from
	 * @param destinationColumnNum The destination column index (0-based) to copy
	 *                             into (from the copied row)
	 */
	public static void copyColumn(Workbook workbook, Sheet sourceSheet, Sheet destinationSheet, int rowStart,
			int rowEnd, int sourceColumnNum, int destinationColumnNum) {
		for (int i = rowStart; i <= rowEnd; i++) {
			Row sourceRow = sourceSheet.getRow(i);
			if (sourceRow == null)
				continue;

			Row destinationRow = destinationSheet.getRow(i);
			if (destinationRow == null)
				destinationRow = destinationSheet.createRow(i);

			Cell oldCell = sourceRow.getCell(sourceColumnNum);
			if (oldCell == null)
				continue;

			Cell newCell = destinationRow.createCell(destinationColumnNum);

			newCell.setCellStyle(oldCell.getCellStyle());

			if (oldCell.getCellComment() != null) {
				newCell.setCellComment(oldCell.getCellComment());
			}

			if (oldCell.getHyperlink() != null) {
				newCell.setHyperlink(oldCell.getHyperlink());
			}

			newCell.setCellType(oldCell.getCellTypeEnum());

			switch (oldCell.getCellTypeEnum()) {
			case NUMERIC:
				newCell.setCellValue(oldCell.getNumericCellValue());
				break;
			case STRING:
				newCell.setCellValue(oldCell.getRichStringCellValue());
				break;
			case FORMULA:
				newCell.setCellFormula(oldCell.getCellFormula());
				break;
			case BLANK:
				newCell.setCellValue(oldCell.getStringCellValue());
				break;
			case BOOLEAN:
				newCell.setCellValue(oldCell.getBooleanCellValue());
				break;
			case ERROR:
				newCell.setCellErrorValue(oldCell.getErrorCellValue());
				break;
			default:
				break;
			}

			for (int ii = 0; ii < sourceSheet.getNumMergedRegions(); ii++) {
				CellRangeAddress cellRangeAddress = sourceSheet.getMergedRegion(ii);
				if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
					addMergedRegion(destinationSheet, cellRangeAddress.getFirstRow(), cellRangeAddress.getLastRow(),
							destinationColumnNum, (destinationColumnNum
									+ (cellRangeAddress.getLastColumn() - cellRangeAddress.getFirstColumn())));
				}
			}
		}

		destinationSheet.setColumnWidth(destinationColumnNum, sourceSheet.getColumnWidth(sourceColumnNum));
	}

	/**
	 * Copy Column
	 * 
	 * @param workbook             The Excel workbook
	 * @param sheet                The Excel worksheet
	 * @param rowStart             The source row start index (0-based) to copy from
	 * @param rowEnd               The source row end index (0-based) to copy from
	 * @param sourceColumnNum      The source column index (0-based) to copy from
	 * @param destinationColumnNum The destination column index (0-based) to copy
	 *                             into (from the copied row)
	 */
	public static void copyColumn(Workbook workbook, Sheet sheet, int rowStart, int rowEnd, int sourceColumnNum,
			int destinationColumnNum) {
		copyColumn(workbook, sheet, sheet, rowStart, rowEnd, sourceColumnNum, destinationColumnNum);
	}

	public static void shiftColumns(Row row, int startingIndex, int shiftCount) {
		for (int i = row.getPhysicalNumberOfCells() - 1; i >= startingIndex; i--) {
			Cell oldCell = row.getCell(i);
			Cell newCell = row.createCell(i + shiftCount);

			// apply cell style to new cell from old cell
			newCell.setCellStyle(oldCell.getCellStyle());

			// if there is a cell comment, copy
			if (oldCell.getCellComment() != null) {
				newCell.setCellComment(oldCell.getCellComment());
			}

			// if there is a cell hyperlink, copy
			if (oldCell.getHyperlink() != null) {
				newCell.setHyperlink(oldCell.getHyperlink());
			}

			// copy the cell data type
			newCell.setCellType(oldCell.getCellTypeEnum());

			// copy the cell data value
			switch (oldCell.getCellTypeEnum()) {
			case NUMERIC:
				newCell.setCellValue(oldCell.getNumericCellValue());
				break;
			case STRING:
				newCell.setCellValue(oldCell.getRichStringCellValue());
				break;
			case FORMULA:
				newCell.setCellFormula(oldCell.getCellFormula());
				break;
			case BLANK:
				newCell.setCellValue(oldCell.getStringCellValue());
				break;
			case BOOLEAN:
				newCell.setCellValue(oldCell.getBooleanCellValue());
				break;
			case ERROR:
				newCell.setCellErrorValue(oldCell.getErrorCellValue());
				break;
			default:
				break;
			}
		}
	}

	/** handle some invalid char included ( /\*[]:? ) */
	public static void setSheetName(Workbook workbook, Sheet sheet, String name) {
		if (workbook != null && sheet != null && StringUtils.isNotBlank(name))
			workbook.setSheetName(workbook.getSheetIndex(sheet), name.replaceAll("[/\\\\*\\[\\]:\\?]", "_"));
	}

	/** delete row */
	public static void deleteRow(Sheet sheet, int rowIndex) {
		if (sheet != null) {
			sheet.removeRow(sheet.getRow(rowIndex));
			if (rowIndex < sheet.getLastRowNum())
				sheet.shiftRows(rowIndex, sheet.getLastRowNum(), -1);
		}
	}

	public static byte[] encrypt(Workbook workbook, String password) {
		return encrypt(toByteArray(workbook), password);
	}

	public static byte[] encrypt(byte[] bytes, String password) {

		POIFSFileSystem fs = new POIFSFileSystem();
		EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile);
		// EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile,
		// CipherAlgorithm.aes192, HashAlgorithm.sha384, -1, -1, null);

		Encryptor enc = info.getEncryptor();
		enc.confirmPassword(password);

		// Read in an existing OOXML file and write to encrypted output stream
		// don't forget to close the output stream otherwise the padding bytes aren't
		// added
		try {
			OPCPackage opc = OPCPackage.open(new ByteArrayInputStream(bytes));
			OutputStream os = enc.getDataStream(fs);
			opc.save(os);

			ByteArrayOutputStream bos = new ByteArrayOutputStream();
			fs.writeFilesystem(bos);

			return bos.toByteArray();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (GeneralSecurityException e) {
			e.printStackTrace();
		}

		return bytes;
	}

}
