package com.zjrs.appcomm.template.excel;

import com.zjrs.appcomm.template.excel.builder.ExportDefinition;
import com.zjrs.appcomm.template.excel.builder.ImportDefinition;
import com.zjrs.appcomm.template.excel.mapper.*;
import com.zjrs.appcomm.template.excel.util.ExcelUtils;
import com.zjrs.appcomm.template.excel.util.MapperUtils;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;

import java.io.ByteArrayOutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 操作Excel的工具，目前包括导出和导入excel功能
 * 
 * <p>
 * 导出功能范例(无离散数据)：
 * <blockquote><pre>
 * List<Example> datas = new ArrayList<>();
 * datas.add(new Example(...));
 * datas.add(new Example(...));
 * ExportDefinition<?, Example> exportDefinition = ExportDefinition.builder(null, Example.class)
 * 		.listData(datas) // 注入列表数据
 * 		.build();
 * byte[] b = excelTemplate.exportFile(exportDefinition);
 * </pre></blockquote>
 * 
 * <p>
 * 导出功能范例(有离散数据)：
 * <blockquote><pre>
 * List<Example> datas = new ArrayList<>(...);
 * datas.add(new Example(...));
 * datas.add(new Example(...));
 * DExample dData = new DExample();
 * ExportDefinition<DExample, Example> exportDefinition = ExportDefinition.builder(DExample.class, Example.class)
 * 		.putDiscreteFieldCoordinate("d1", ?, ?)
 * 		.putDiscreteFieldCoordinate("d2", ?, ?)
 * 		.listFields(new String[] { "l1", "l2", "l3", "l4", "l5" })
 * 		.listData(datas) // 注入列表数据
 * 		.build();
 * byte[] b = excelTemplate.exportFile(exportDefinition);
 * </pre></blockquote><p>
 * 
 * <p>
 * 导入功能范例(无离散数据)：
 * <blockquote><pre>
 * </pre></blockquote>
 * 
 * <p>
 * 导入功能范例(有离散数据)：
 * <blockquote><pre>
 * </pre></blockquote>
 * @author froms
 *
 * 其他:<br />
 * 1.针对导出时列需要进行相同值合并的需求，可以使用ExportBuilder的addListDataCellStyler(field, new FieldMergeCellStyler(relatedField))方法进行配置
 */

public class ExcelTemplate {

	private static final Logger LOG = LoggerFactory.getLogger(ExcelTemplate.class);
	
	private ObjectMapper objectMapper;
	
	public ExcelTemplate() {
		this.objectMapper = new ObjectMapper();
	}
	
	public ExcelTemplate(ObjectMapper objectMapper) {
		this.objectMapper = objectMapper;
	}
	
	/**
	 * 导出Excel数据
	 * @param <D> 离散数据的定义
	 * @param <L> 列表数据行的定义
	 * @param exportDefinition 导出Excel数据定义
	 * @throws Exception 
	 */
	public <D, L> byte[] exportFile(ExportDefinition<D, L> exportDefinition) throws Exception {
		/* 开始处理离散数据 */
		if (exportDefinition.getDiscreteData() != null) {
			exportDiscreteData(exportDefinition.getSheet(), exportDefinition.getDiscreteFieldCoordinates(), exportDefinition.getDiscreteDefaultDataStyler(), exportDefinition.getDiscreteData());
		}
		
		/* 开始处理列表表头数据 */
		if (!ObjectUtils.isEmpty(exportDefinition.getListHeaders())) {
			exportHeaderData(exportDefinition.getSheet(), exportDefinition.getListHeaderRowIndex(), exportDefinition.getListHeaders(), exportDefinition.getListFields(), exportDefinition.getListHeaderRowStyler(), exportDefinition.getListHeaderCellStyler());
		}
		/* 开始处理列表数据 */
		if (exportDefinition.getListData() != null) {
			moveListTemplate(exportDefinition.getSheet(), exportDefinition.getListDataRowIndex(), exportDefinition.getListData());
			copyListTemplate(exportDefinition.getSheet(), exportDefinition.getListDataRowIndex(), exportDefinition.getListData());
			
			exportListRowData(exportDefinition.getSheet(), exportDefinition.getListDataRowIndex(), exportDefinition.getListFields(), exportDefinition.getListDataRowStyler(), exportDefinition.getListDataCellStyler(), exportDefinition.getListData());
		}
		
		try (ByteArrayOutputStream os = new ByteArrayOutputStream()) {
			exportDefinition.getSheet().getWorkbook().write(os);
			return os.toByteArray();
		}
	}
	
	private <D> void exportDiscreteData(Sheet sheet, Map<String, SheetCoordinate> discreteFieldCoordinates, CellStyler discreteDefaultDataStyler, D discreteData) throws Exception {
		Map<String, Object> discreteMap = MapperUtils.object2Map(discreteData, objectMapper);
		for (Map.Entry<String, SheetCoordinate> discreteFieldCoordinate: discreteFieldCoordinates.entrySet()) {
			String name = discreteFieldCoordinate.getKey();
			SheetCoordinate coordinate = discreteFieldCoordinate.getValue();
			Object discreteValue = discreteMap.get(name);
			
			Row row = ExcelUtils.getRow(sheet, coordinate.getRow(), true);
			Cell cell = ExcelUtils.getCell(row, coordinate.getCol(), true);
			boolean doValue = true;
			if (coordinate.getStyler() == null) {
				if (discreteDefaultDataStyler != null) {
					doValue = discreteDefaultDataStyler.style(cell, discreteValue, name, -1, coordinate.getRow(), coordinate.getCol(), discreteMap);
				}
			} else {
				doValue = coordinate.getStyler().style(cell, discreteValue, name, -1, coordinate.getRow(), coordinate.getCol(), discreteMap);
			}
			if (doValue) {
				ExcelUtils.setCellValue(cell, discreteValue);
			}
		}
	}
	private void exportHeaderData(Sheet sheet, int listHeaderRowIndex, String[][] listHeaders, String[] listFields, RowStyler listHeaderRowStyler, CellStyler listHeaderCellStyler) {
		for (int i = 0; i < listHeaders.length; i++) {
			String[] listOneHeaders = listHeaders[i];
			int rowIndex = listHeaderRowIndex + i;
			Row headerRow = ExcelUtils.getRow(sheet, rowIndex, true);
			boolean doCell = listHeaderRowStyler.style(headerRow, 0);
			if (doCell) {
				for (int j = 0; j < listOneHeaders.length; j++) {
					String header = listOneHeaders[j];
					Cell headerCell = ExcelUtils.getCell(headerRow, j, true);
					boolean doValue = listHeaderCellStyler.style(headerCell, header, j < listFields.length ? listFields[j] : null, i, rowIndex, j, null);
					if (doValue) {
						ExcelUtils.setCellValue(headerCell, header);
					}
				}
			}
		}
	}
	private <L> void moveListTemplate(Sheet sheet, int listDataRowIndex, List<L> listData) {
		/* 下移模板故有内容，为数据腾出空间 */
		if (listData.size() > 1 && listDataRowIndex + 1 <= sheet.getLastRowNum()) {
			sheet.shiftRows(listDataRowIndex + 1, sheet.getLastRowNum(), listData.size() - 1, true, true);
		}
	}
	private <L> void copyListTemplate(Sheet sheet, int listDataRowIndex, List<L> listData) {
		/* 复制模板行 */
		Row templateRow = ExcelUtils.getRow(sheet, listDataRowIndex, true);
		for (int i = 1; i < listData.size(); i++) {
			Row dataRow = ExcelUtils.getRow(sheet, listDataRowIndex + i, true);
			dataRow.setHeightInPoints(templateRow.getHeightInPoints());
			for (int j = 0; j < templateRow.getLastCellNum(); j++) {
				Cell templateCell = templateRow.getCell(j);
				Cell dataCell = dataRow.getCell(j);
				if (templateCell == null) {
					if (dataCell != null) {
						dataRow.removeCell(dataCell);
					}
				} else {
					if (dataCell == null) {
						dataCell = dataRow.createCell(j);
					}
					dataCell.setCellStyle(templateCell.getCellStyle());
				}
			}
		}
	}
	private <L> void exportListRowData(Sheet sheet, int listDataRowIndex, String[] listFields, RowStyler listDataRowStyler, CellStyler listDataCellStyler, List<L> listData) throws Exception {
		for (int i = 0; i < listData.size(); i++) {
			Map<String, Object> listMap = MapperUtils.object2Map(listData.get(i), objectMapper);
			int rowIndex = listDataRowIndex + i;
			Row dataRow = ExcelUtils.getRow(sheet, rowIndex, true);
			boolean doCell = true;
			if (listDataRowStyler != null) {
				doCell = listDataRowStyler.style(dataRow, i);
			}
			if (doCell) {
				exportListCellData(dataRow, i, rowIndex, listFields, listDataCellStyler, listMap);
			}
		}
	}
	private void exportListCellData(Row dataRow, int dataIndex, int rowIndex, String[] listFields, CellStyler listDataCellStyler, Map<?, ?> listMap) {
		Cell cell = dataRow.createCell(dataIndex);
		CellStyle oldCellStyle = cell.getCellStyle();
		CellStyle newCellStyle = cell.getSheet().getWorkbook().createCellStyle();
		newCellStyle.cloneStyleFrom(oldCellStyle);
		newCellStyle.setBorderTop(BorderStyle.THIN);
		newCellStyle.setBorderBottom(BorderStyle.THIN);
		newCellStyle.setBorderLeft(BorderStyle.THIN);
		newCellStyle.setBorderRight(BorderStyle.THIN);
		for (int j = 0; j < listFields.length; j++) {
			String field = listFields[j];
			Cell dataCell = ExcelUtils.getCell(dataRow, j, true);
			Object value = listMap.get(field);
			boolean doValue = true;
			if (listDataCellStyler != null) {
				if (listDataCellStyler instanceof SimpleDataCellStyler){
					dataCell.setCellStyle(newCellStyle);
					doValue = true;
				}else{
					doValue = listDataCellStyler.style(dataCell, value, field, dataIndex, rowIndex, j, listMap);
				}
			}
			if (doValue) {
				ExcelUtils.setCellValue(dataCell, value);
			}
		}
	}
	
	/**
	 * 导入Excel数据
	 * @param <D> 离散数据的定义
	 * @param <L> 行数据的定义
	 * @param importDefinition 导入Excel数据定义
	 * @return 成功返回true，失败返回false
	 */
	public <D, L> void importFile(ImportDefinition<D, L> importDefinition) {
		if (importDefinition.getDiscreteClass() != null) {
			importDiscreteData(importDefinition.getSheet(), importDefinition.getDiscreteFieldCoordinates(), importDefinition.getDiscreteMapper(), importDefinition.getDiscreteClass());
		}
		
		if (importDefinition.getListClass() != null) {
			Row[] rows = new Row[importDefinition.getHeaderRowCount()];
			for (int i = 0; i < importDefinition.getHeaderRowCount(); i++) {
				rows[i] = ExcelUtils.getRow(importDefinition.getSheet(), importDefinition.getHeaderRowIndex() + i, false);
			}
			String[] names = importDefinition.getHeaderMapper().mapper(rows);
			
			importListData(importDefinition.getSheet(), names, importDefinition.isCalculateMergedCell(), importDefinition.getDataRowIndex(), importDefinition.getListFilter(), importDefinition.getListfinishedJudge(), importDefinition.getRowMapper(), importDefinition.getListClass());
		}
	}
	
	private <D> void importDiscreteData(Sheet sheet, Map<String, SheetCoordinate> discreteFieldCoordinates, DiscreteMapper<D> discreteMapper, Class<D> discreteType) {
		Map<String, Cell> discreteCellMap = new HashMap<>();
		Map<String, Object> discreteMap = new HashMap<>();
		for (Map.Entry<String, SheetCoordinate> discreteFieldCoordinate: discreteFieldCoordinates.entrySet()) {
			String name = discreteFieldCoordinate.getKey();
			SheetCoordinate coordinate = discreteFieldCoordinate.getValue();
			
			Cell cell = null;
			Row row = ExcelUtils.getRow(sheet, coordinate.getRow(), false);
			if (row != null) {
				cell = ExcelUtils.getCell(row, coordinate.getCol(), false);
			}
			discreteCellMap.put(name, cell);
			discreteMap.put(name, ExcelUtils.getCellValue(cell));
		}
		D d = null;
		try {
			d = objectMapper.convertValue(discreteMap, discreteType);
		} catch (Exception e) {
			LOG.debug("导入离散数据转换异常", e);
		}
		discreteMapper.mapper(d, discreteMap, discreteCellMap);
	}
	
	private <L> void importListData(Sheet sheet, String[] names, boolean calculateMergedCell, int dataRowIndex, RowJudge listFilter, RowJudge listfinishedJudge, RowMapper<L> rowMapper, Class<L> listType) {
		int dataIndex = 0;
		for (int i = dataRowIndex; i <= sheet.getLastRowNum(); i++) {
			Row dataRow = ExcelUtils.getRow(sheet, i, false);
			if (dataRow == null) {
				LOG.debug("跳过空行(行空)");
				continue;
			}
			if (listfinishedJudge != null) {
				boolean judge = listfinishedJudge.judge(dataRow, i);
				if (judge) {
					break;
				}
			}
			if (listFilter != null) {
				boolean judge = listFilter.judge(dataRow, i);
				if (judge) {
					continue;
				}
			}
			
			int num = importListCellData(dataRow, calculateMergedCell, dataIndex, i, names, rowMapper, listType);
			dataIndex += num;
		}
	}
	private <L> int importListCellData(Row dataRow, boolean calculateMergedCell, int dataIndex, int rowIndex, String[] names, RowMapper<L> rowMapper, Class<L> listType) {
		boolean isDataEmpty = true;
		Map<String, Cell> cellMap = new HashMap<>();
		Map<String, Object> valuesMap = new HashMap<>();
		for (int j = 0; j < dataRow.getLastCellNum(); j++) {
			if (j >= names.length) {
				LOG.debug("跳过无用列");
				break;
			}
			String name = names[j];
			if (StringUtils.isEmpty(name)) {
				continue;
			}
			Cell cell = null;
			if (calculateMergedCell) {
				cell = ExcelUtils.getCellMerged(dataRow.getSheet(), rowIndex, j);
			} else {
				cell = ExcelUtils.getCell(dataRow, j, false);
			}
			Object value = ExcelUtils.getCellValue(cell);
			if (!StringUtils.isEmpty(value)) {
				isDataEmpty = false;
			}
			cellMap.put(name, cell);
			valuesMap.put(name, value);
		}
		if (isDataEmpty) {
			LOG.debug("跳过空行(数据空)");
			return 0;
		}
		
		L t = null;
		try {
			t = objectMapper.convertValue(valuesMap, listType);
		} catch (Exception e) {
			LOG.debug("导入列表数据转换异常", e);
		}
		rowMapper.mapper(t, valuesMap, cellMap, dataIndex, rowIndex);
		return 1;
	}
}
