package com.zjrs.appcomm.template.excel.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.Calendar;
import java.util.Date;

public class ExcelUtils {

	public static String getStringCellValue(Cell cell) {
		if (cell == null) {
			return "";
		}
		CellType cellType = (cell.getCellType() == CellType.FORMULA ? cell.getCachedFormulaResultType() : cell.getCellType());
		if (cellType == null) {
			throw new AssertionError("异常的单元格类型: null");
		}
		switch (cellType) {
			case NUMERIC:
				if (DateUtil.isCellDateFormatted(cell)) {
					return cell.getLocalDateTimeCellValue() + "";	// TODO: 有待进一步格式化
				} else {
					return BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString();	// TODO: 有待进一步格式化
				}
			case STRING:
				return cell.getStringCellValue();
			case FORMULA:
				throw new AssertionError("异常的单元格类型: " + cellType.name());
			case BLANK:
				return "";
			case BOOLEAN:
				return Boolean.toString(cell.getBooleanCellValue()).toUpperCase();
			case ERROR:
				return FormulaError.forInt(cell.getErrorCellValue()).name();
			case _NONE:
				throw new AssertionError("异常的单元格类型: " + cellType.name());
			default:
				throw new AssertionError("异常的单元格类型: " + cellType.name());
		}
	}

	public static Object getCellValue(Cell cell) {
		if (cell == null) {
			return null;
		}
		CellType cellType = (cell.getCellType() == CellType.FORMULA ? cell.getCachedFormulaResultType() : cell.getCellType());
		if (cellType == null) {
			throw new AssertionError("异常的单元格类型: null");
		}
		switch (cellType) {
			case NUMERIC:
				if (DateUtil.isCellDateFormatted(cell)) {
					return cell.getLocalDateTimeCellValue();
				} else {
					return new BigDecimal(BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString());
				}
			case STRING:
				return cell.getStringCellValue();
			case FORMULA:
				throw new AssertionError("异常的单元格类型: " + cellType.name());
			case BLANK:
				return "";
			case BOOLEAN:
				return cell.getBooleanCellValue();
			case ERROR:
				return FormulaError.forInt(cell.getErrorCellValue());
			case _NONE:
				throw new AssertionError("异常的单元格类型: " + cellType.name());
			default:
				throw new AssertionError("异常的单元格类型: " + cellType.name());
		}
	}

	public static void setCellValue(Cell cell, Object value) {
		if (value == null) {
			cell.setBlank();
			return;
		}

		if (value instanceof CharSequence) {
			CharSequence v = (CharSequence) value;
			if (v.length() == 0) {
				cell.setBlank();
			} else {
				cell.setCellValue(v.toString());
			}
		} else if (value instanceof LocalDateTime) {
			cell.setCellValue((LocalDateTime) value);
		} else if (value instanceof LocalDate) {
			cell.setCellValue((LocalDate) value);
		} else if (value instanceof LocalTime) {
			LocalDateTime v = LocalDateTime.of(LocalDate.of(1970, 1, 1), (LocalTime) value);
			cell.setCellValue(v);
		} else if (value instanceof Date) {
			cell.setCellValue((Date) value);
		} else if (value instanceof Calendar) {
			cell.setCellValue((Calendar) value);
		} else if (value instanceof Number) {
			double v = ((Number) value).doubleValue();
			cell.setCellValue(v);
		} else if (value instanceof Boolean) {
			cell.setCellValue((boolean) value);
		} else if (value instanceof RichTextString) {
			cell.setCellValue((RichTextString) value);
		} else {
			String v = value.toString();
			if (v.length() == 0) {
				cell.setBlank();
			} else {
				cell.setCellValue(v);
			}
		}
	}

	public static Sheet getSheet(Workbook workbook, String sheetName, boolean createIfNull) {
		if (StringUtils.isEmpty(sheetName)) {
			return workbook.getSheetAt(workbook.getActiveSheetIndex());
		} else {
			Sheet sheet = workbook.getSheet(sheetName);
			if (sheet == null && createIfNull) {
				sheet = workbook.createSheet(sheetName);
			}
			return sheet;
		}
	}

	public static Row getRow(Sheet sheet, int index, boolean createIfNull) {
		Row row = sheet.getRow(index);
		if (row == null && createIfNull) {
			row = sheet.createRow(index);
		}
		return row;
	}

	public static Cell getCell(Row row, int index, boolean createIfNull) {
		Cell cell = row.getCell(index);
		if (cell == null && createIfNull) {
			cell = row.createCell(index);
		}
		return cell;
	}

	public static Cell getCellMerged(Sheet sheet, int rowIndex, int cellIndex) {
		for (CellRangeAddress cellRangeAddress: sheet.getMergedRegions()) {
			if (rowIndex >= cellRangeAddress.getFirstRow() && rowIndex <= cellRangeAddress.getLastRow()
					&& cellIndex >= cellRangeAddress.getFirstColumn() && cellIndex <= cellRangeAddress.getLastColumn()) {
				return sheet.getRow(cellRangeAddress.getFirstRow()).getCell(cellRangeAddress.getFirstColumn());
			}
		}
		return sheet.getRow(rowIndex).getCell(cellIndex);
	}

	private ExcelUtils() {}
}
