package froala.editor.utils;

import java.awt.font.FontRenderContext;
import java.awt.font.LineBreakMeasurer;
import java.awt.font.TextAttribute;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.AttributedString;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.ShapeTypes;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFSimpleShape;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ExcelUtil {
	private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);

	private XSSFSheet sheet;
	private int rowIndex = 0;
	private int maxCols = 0;
	private XSSFWorkbook workbook;
	private int offsetCol = 0;

	public ExcelUtil(String sheetName) {
		workbook = new XSSFWorkbook();
		sheet = workbook.createSheet(sheetName);
	}

	public void setOffset(int col, int row) {
		offsetCol = col;
		rowIndex = row;
	}

	public void addRow(List<String> rows) {
		addRow(null, (short) 0, rows);
	}

	public void addRow(String backgroundColor, List<String> rows) {
		addRow(backgroundColor, (short) 0, rows);
	}

	public int getRowIndex() {
		return rowIndex;
	}

	public void setCellWidth(int cellnum, int width) {
		sheet.setColumnWidth(cellnum, width);
	}

	public void setCell(int cellnum, int rownum, XSSFCellStyle cellStyle, String cellValue) {
		XSSFRow row = sheet.getRow(rownum);
		if (row == null) {
			row = sheet.createRow(rownum);
		}

		XSSFCell cell = row.getCell(cellnum);
		if (cell == null) {
			cell = row.createCell(cellnum, XSSFCell.CELL_TYPE_STRING);
		}
		cell.setCellStyle(cellStyle);
		cell.setCellValue(cellValue);
	}

	public void addRow(String backgroundColor, short boldweight, List<String> cellStrings) {
		XSSFRow header = sheet.createRow(rowIndex++);
		int cellIndex = offsetCol;
		for (String value : cellStrings) {
			XSSFCell cell = header.createCell(cellIndex++, XSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(value);
			if( backgroundColor != null ) {
				cell.setCellStyle(createCellStyle(backgroundColor, boldweight));
			}
		}
		if (maxCols < cellIndex) {
			maxCols = cellIndex;
		}
	}

	public void appendRow(int cellnum, int rownum, String backgroundColor, short boldweight, List<String> cellStrings) {
		XSSFRow header = sheet.getRow(rownum);
		if(header == null) {
			rowIndex = rownum;
			header = sheet.createRow(rowIndex++);
		}
		int cellIndex = cellnum;
		for (String value : cellStrings) {
			XSSFCell cell = header.createCell(cellIndex++, XSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(value);
			cell.setCellStyle(createCellStyle(backgroundColor, boldweight));
		}
		if (maxCols < cellIndex) {
			maxCols = cellIndex;
		}
	}

	public void addRow(List<Map<String, String>> style, List<String> cellStrings) {
		XSSFRow header = sheet.createRow(rowIndex++);
		int cellIndex = offsetCol;
		for (String value : cellStrings) {
			int index = cellIndex - offsetCol;
			XSSFCell cell = header.createCell(cellIndex++, XSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(value);
			String backgroundColor = null;
			short boldweight = 0;
			if (style.size() > index) {
				Map<String, String> styleMap = style.get(index);
				backgroundColor = styleMap.get("backgroundColor");
				if (styleMap.containsKey("boldweight")) {
					boldweight = Short.parseShort(styleMap.get("boldweight"));
				}
			}
	      cell.setCellStyle(createCellStyle(backgroundColor, boldweight));
		}

		if (maxCols < cellIndex) {
			maxCols = cellIndex;
		}
	}

	public XSSFCellStyle createCellStyle(String backgroundColor, short boldweight) {
		XSSFCellStyle cellStyle = sheet.getWorkbook().createCellStyle();
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		if (backgroundColor != null) {
			cellStyle.setFillForegroundColor(new XSSFColor(hexToByteArray(backgroundColor)));
			cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
		}
		setSolidBorder(cellStyle);

		if (boldweight != 0) {
			Font headerFont = this.sheet.getWorkbook().createFont();
			headerFont.setBoldweight(boldweight);
			cellStyle.setFont(headerFont);
		}

		return cellStyle;
	}

	public void setFontHeight(int cellnum, int rownum, int height) {
		sheet.getRow(rownum).getCell(cellnum).getCellStyle().getFont().setFontHeight(height);
	}

	public void setCellAlignment(int cellnum, int rownum, HorizontalAlignment align) {
		sheet.getRow(rownum).getCell(cellnum).getCellStyle().setAlignment(align);
	}

	public void setCellVerticalAlignment(int cellnum, int rownum, VerticalAlignment align) {
		sheet.getRow(rownum).getCell(cellnum).getCellStyle().setVerticalAlignment(align);
	}

	public void setCellWrapText(int cellnum, int rownum, boolean b) {
		XSSFRow row = sheet.getRow(rownum);
		XSSFCellStyle rowStyle = row.getRowStyle();
		if (rowStyle == null) {
			XSSFCellStyle cellStyle = sheet.getWorkbook().createCellStyle();
			cellStyle.setWrapText(b);
			row.setRowStyle(cellStyle);
		} else {
			rowStyle.setWrapText(b);
		}
		row.getCell(cellnum).getCellStyle().setWrapText(b);
	}

	// hex to byte[]
	public byte[] hexToByteArray(String hex) {
		if (hex == null || hex.length() == 0) {
			return null;
		}

		byte[] ba = new byte[hex.length() / 2];
		for (int i = 0; i < ba.length; i++) {
			ba[i] = (byte) Integer.parseInt(hex.substring(2 * i, 2 * i + 2), 16);
		}
		return ba;
	}

	public void setSolidBorder(XSSFCellStyle cellStyle) {
		cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
		cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
		cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
		cellStyle.setBorderTop(CellStyle.BORDER_THIN);
		cellStyle.setBorderRight(CellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
	}

	public void write(OutputStream outputStream) throws IOException {
		// adjust column width to fit the content
		for (int i = 0; i < maxCols; i++) {
			sheet.autoSizeColumn(i);
			sheet.setColumnWidth(i, Math.min(255 * 256, sheet.getColumnWidth(i) + 1500));
		}

		for (int i = 0; i < offsetCol; i++) {
			setCellWidth(i, 900);
		}
		this.sheet.getWorkbook().write(outputStream);
//		this.workbook.close();
	}

	public void merge(int firstCol, int firstRow, int lastCol, int lastRow) {
		// 셀 병합
		sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol)); // 병합
	}

	public void drawRect(int rownum, int scolnum, int ecolnum, int dx1, int dx2) {
		XSSFDrawing patriarch = sheet.createDrawingPatriarch();
		XSSFClientAnchor a = new XSSFClientAnchor();
		a.setCol1(scolnum);
		a.setRow1(rownum);
		a.setDx1(pxToEmu(dx1));
		a.setDy1(pxToEmu(5));
		a.setDx2(pxToEmu(dx2));
		a.setDy2(pxToEmu(-5));
		a.setRow2(rownum + 1);
		a.setCol2(ecolnum);

		XSSFSimpleShape shape1 = patriarch.createSimpleShape(a);
		shape1.setShapeType(ShapeTypes.RECT);
		int red = 0, green = 0, blue = 0;
	    red = Integer.parseInt("f0", 16);
	    green = Integer.parseInt("ad", 16);
	    blue = Integer.parseInt("4e", 16);
	    shape1.setLineStyleColor(red, green, blue);
	    shape1.setFillColor(red, green, blue);
	}

	public static int pxToEmu(int px) {
		return (int) Math.round(((double) px) * 72 * 20 * 635 / 96); // assume
                                                                 // 96dpi
	}

	public static int emuToPx(int emu) {
		return (int) Math.round(((double) emu) * 96 / 72 / 20 / 635); // assume
                                                                  // 96dpi
	}

	public float getDefaultRowHeightInPoints() {
		return this.sheet.getDefaultRowHeightInPoints();
	}

	public void setRowHeightInPoints(int rownum, float height) {
		sheet.getRow(rownum).setHeightInPoints(height);
	}

	public float getRowHeightInPoints(int rownum) {
		return sheet.getRow(rownum).getHeightInPoints();
	}

	/**
	 * ROW 높이 자동 조절
	 *
	 * @param rownum
	 * @param cellValue
	 */
	public void setAutoRowFit(int cellnum, int rownum) {
		XSSFRow row = sheet.getRow(rownum);
		XSSFCell cell = row.getCell(cellnum);
		XSSFFont cellFont = cell.getCellStyle().getFont();
		int fontStyle = java.awt.Font.PLAIN;
		if (cellFont.getBold())
			fontStyle = java.awt.Font.BOLD;
		if (cellFont.getItalic())
			fontStyle = java.awt.Font.ITALIC;

		java.awt.Font currFont = new java.awt.Font(cellFont.getFontName(), fontStyle, cellFont.getFontHeightInPoints());

		String cellText = cell.getStringCellValue();
		AttributedString attrStr = new AttributedString(cellText);
		attrStr.addAttribute(TextAttribute.FONT, currFont);

		// Use LineBreakMeasurer to count number of lines needed for the text
		//
		FontRenderContext frc = new FontRenderContext(null, true, true);
		LineBreakMeasurer measurer = new LineBreakMeasurer(attrStr.getIterator(), frc);
		int nextPos = 0;
		int lineCnt = 1;
		float columnWidthInPx = sheet.getColumnWidth(cellnum);
		while (measurer.getPosition() < cellText.length()) {
			nextPos = measurer.nextOffset(columnWidthInPx);
			lineCnt++;
			measurer.setPosition(nextPos);
		}
		int fromIndex = -1;
		while ((fromIndex = cellText.indexOf("\n", fromIndex + 1)) >= 0) {
			lineCnt++;
		}
		if (lineCnt > 1) {
			row.setHeightInPoints(sheet.getDefaultRowHeightInPoints() * lineCnt * /* fudge factor */ 1.1f);
		}
	}

	public static List<List<String>> readExcel(File file) throws IOException, InvalidFormatException {
		List<List<String>> resultList = null;
		FileInputStream fis = null;
		try {
			fis = new FileInputStream(file);
			resultList = readExcel(fis, file.getName(), 0, null);
		} catch (IOException | InvalidFormatException e) {
			LOGGER.error("ERROR! excel read error!!");
			return null;
		} finally {
			fis.close();
		}
		return resultList;
	}

	public static List<List<String>> readExcel(File file, int sheetAt) throws IOException, InvalidFormatException {
		List<List<String>> resultList = null;
		FileInputStream fis = null;
		try {
			fis = new FileInputStream(file);
			resultList = readExcel(fis, file.getName(), sheetAt, null);
		} catch (IOException | InvalidFormatException e) {
			LOGGER.error("ERROR! excel read error!!");
			return null;
		} finally {
			fis.close();
		}
		return resultList;
	}

	public static List<List<String>> readExcel(File file, int sheetAt, Integer requiredIndex) throws IOException, InvalidFormatException {
		List<List<String>> resultList = null;
		FileInputStream fis = null;
		try {
			fis = new FileInputStream(file);
			resultList = readExcel(fis, file.getName(), sheetAt, requiredIndex);
		} catch (IOException | InvalidFormatException e) {
			LOGGER.error("ERROR! excel read error!!");
			return null;
		} finally {
			fis.close();
		}
		return resultList;
	}

	public static List<List<String>> readExcel(InputStream is) throws IOException, InvalidFormatException {
		List<List<String>> resultList = null;
		try {
			resultList = readExcel(is, "xlsx", 0, null);
		} catch (IOException | InvalidFormatException e) {
			LOGGER.error("ERROR! excel read error!!");
			return null;
		}
		return resultList;
	}

	private static Workbook getWorkbook(InputStream inputStream, String fileName) throws IOException {
		Workbook workbook = null;

		if (fileName.endsWith("xlsx")) {
			workbook = new XSSFWorkbook(inputStream);
		} else if (fileName.endsWith("xls")) {
			workbook = new HSSFWorkbook(inputStream);
		} else {
			throw new IllegalArgumentException("The specified file is not Excel file");
		}

		return workbook;
	}

	public static List<List<String>> readExcel(InputStream is, String fileName, int sheetAt, Integer requiredIndex)
			throws IOException, InvalidFormatException {
		List<List<String>> resultList = new ArrayList<>();
		// 파일을 읽기위해 엑셀파일을 가져온다
		Workbook workbook = getWorkbook(is, fileName);
		int rowindex = 0;
		int columnindex = 0;
		// 시트 수 (첫번째에만 존재하므로 0을 준다)
		// 만약 각 시트를 읽기위해서는 FOR문을 한번더 돌려준다
		Sheet sheet = workbook.getSheetAt(sheetAt);
		// 병합 셀 조회 후 병합해제
		String cellVal="";

		for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
			CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
			int colIndex = mergedRegion.getFirstColumn();
			int firstRowIndex = mergedRegion.getFirstRow();
			int lastRowIndex = mergedRegion.getLastRow();
			if(colIndex==0){
				cellVal = sheet.getRow(firstRowIndex).getCell(colIndex).getStringCellValue();
			}
			for (int j = firstRowIndex; j < lastRowIndex+1; j++) {
				Cell inputCell = sheet.getRow(j).getCell(0);
				inputCell.setCellValue(cellVal);
			}
		}
		// 행의 수
		int rows = sheet.getPhysicalNumberOfRows();
//		int rows = sheet.getLastRowNum();
		int rowCnt = 0;
		for (rowindex = 0; rowindex < rows; rowindex++) {
			// 행을 읽는다
			Row row = sheet.getRow(rowindex);
			if (row != null) {
				// 필수로 값이 있어야 하는 Cell 체크.
				if (requiredIndex != null) {
					Cell requiredCell = row.getCell(requiredIndex);
					String requiredValue = "";
					switch (requiredCell.getCellType()) {
						case Cell.CELL_TYPE_BLANK:
							requiredValue = requiredCell.getBooleanCellValue() + "";
							break;
						default:
							requiredValue = "true";
							break;
					}

					if ("false".equals(requiredValue)) {
						break;
//						continue;
					}

//					Cell requiredCell = row.getCell((short)0x1);
//					if (requiredCell == null) {
//						continue;
//					}
				}

				resultList.add(new ArrayList<String>());

				// 셀의 수
//				int cells = row.getPhysicalNumberOfCells();
				int cells = row.getLastCellNum();
				for (columnindex = 0; columnindex <= cells; columnindex++) {
					// 셀값을 읽는다
					Cell cell = row.getCell(columnindex);
					String value = "";
					// 셀이 빈값일경우를 위한 널체크
					if (rowindex == 0 && cell == null) {
						continue;
					}

					if (cell != null) {
						// 타입별로 내용 읽기
						switch (cell.getCellType()) {
							case Cell.CELL_TYPE_FORMULA:
								value = cell.getCellFormula();
								break;
							case Cell.CELL_TYPE_NUMERIC:
								value = String.format("%1$,.0f", cell.getNumericCellValue());
								break;
							case Cell.CELL_TYPE_STRING:
								value = cell.getStringCellValue() + "";
								break;
							case Cell.CELL_TYPE_BLANK:
								value = cell.getBooleanCellValue() + "";
								break;
							case Cell.CELL_TYPE_ERROR:
								value = cell.getErrorCellValue() + "";
								break;
						}
					}

					if ("false".equals(value)) {
						value = "";
					}

					resultList.get(rowCnt).add(value);
				}
			}
			rowCnt++;
		}
//		workbook.;
		return resultList;
	}

	public static List<List<String>> readFormulaExcel(File file, int sheetAt)
			throws IOException, InvalidFormatException {
		List<List<String>> resultList = null;
		FileInputStream fis = null;
		OPCPackage opcPackage = null;
		Workbook workbook = null;
		try {
			resultList = new ArrayList<List<String>>();
			// 파일을 읽기위해 엑셀파일을 가져온다
			if (file.getName().endsWith("xlsx")) {
				opcPackage = OPCPackage.open(file);
				workbook = new XSSFWorkbook(opcPackage);
			} else if (file.getName().endsWith("xls")) {
				fis = new FileInputStream(file);
				workbook = new HSSFWorkbook(fis);
			}

			int rowindex = 0;
			int columnindex = 0;
			// 시트 수 (첫번째에만 존재하므로 0을 준다)
			// 만약 각 시트를 읽기위해서는 FOR문을 한번더 돌려준다
			Sheet sheet = workbook.getSheetAt(sheetAt);
			// 행의 수
			int rows = sheet.getPhysicalNumberOfRows();
			int rowCnt = 0;
			for (rowindex = 0; rowindex < rows; rowindex++) {
				// 행을 읽는다
				Row row = sheet.getRow(rowindex);
				if (row != null) {
					resultList.add(new ArrayList<String>());
					// 셀의 수
					int cells = row.getLastCellNum();
					for (columnindex = 0; columnindex < cells; columnindex++) {
						// 셀값을 읽는다
						Cell cell = row.getCell(columnindex);
						String value = "";
						// 셀이 빈값일경우를 위한 널체크
						if (rowindex == 0 && cell == null) {
							continue;
						}
						if (cell != null) {
							// 타입별로 내용 읽기
							switch (cell.getCellType()) {
								case Cell.CELL_TYPE_FORMULA:
									// 수식결과형태만 조회
									// 값 처리 과정은 속도가 너무 느림
									int cellType = cell.getCachedFormulaResultType();
									switch(cellType) {
										case Cell.CELL_TYPE_NUMERIC:
											//날짜인지 아닌지 구분
											if( DateUtil.isCellDateFormatted( cell)) {
												Date date = cell.getDateCellValue();
												value = new SimpleDateFormat("yyyy-MM-dd").format(date);
											}else {
												if(cell.getNumericCellValue() == (long) cell.getNumericCellValue()) {
													value = String.format("%d",(long)cell.getNumericCellValue());
												}else {
													//CP별 가중치는 서식 백분율 값 들어가있음
													if(sheetAt == 4) {
														value = String.format("%.3f",cell.getNumericCellValue()*100);
													}else {
														value = String.format("%.3f",cell.getNumericCellValue());
													}
												}
											}
											break;
										case Cell.CELL_TYPE_STRING:
											value = cell.getStringCellValue() + "";
											break;
										case Cell.CELL_TYPE_BLANK:
											value = cell.getBooleanCellValue() + "";
											break;
										case Cell.CELL_TYPE_ERROR:
											value = cell.getErrorCellValue() + "";
											break;
									}
									break;
								case Cell.CELL_TYPE_NUMERIC:
									//날짜인지 아닌지 구분
									if( DateUtil.isCellDateFormatted( cell)) {
										Date date = cell.getDateCellValue();
										value = new SimpleDateFormat("yyyy-MM-dd").format(date);
									}else {
										if(cell.getNumericCellValue() == (long) cell.getNumericCellValue()) {
											value = String.format("%d",(long)cell.getNumericCellValue());
										}else {
											//CP별 가중치는 서식 백분율 값 들어가있음
											if(sheetAt == 4) {
												value = String.format("%.3f",cell.getNumericCellValue()*100);
											}else {
												value = String.format("%.3f",cell.getNumericCellValue());
											}
										}
									}
									break;
								case Cell.CELL_TYPE_STRING:
									value = cell.getStringCellValue() + "";
									break;
								case Cell.CELL_TYPE_BLANK:
									value = cell.getBooleanCellValue() + "";
									break;
								case Cell.CELL_TYPE_ERROR:
									value = cell.getErrorCellValue() + "";
									break;
							}
						}
						if ("false".equals(value)) {
							value = "";
						}
						resultList.get(rowCnt).add(value);
					}
				}
				rowCnt++;
			}
		} catch (IOException e) {
			LOGGER.error("ERROR! excel read error!!");
			return null;
		} finally {
			if (file.getName().endsWith("xlsx")) {
				opcPackage.close();
			} else if (file.getName().endsWith("xls")) {
				fis.close();
			}
		}
		return resultList;
	}

	/**
	 * 엑셀 출력 시, valueof null 일 때, 빈값으로 값 치환
	 * @Author : 이다솜
	 * @Date   : 2021. 10. 6.
	 * @Method Name : stingValueOf
	 * @return : String
	 */
	public String stringValueOf(Object object) {
		return object == null ? "" : String.valueOf(object);
	}

}