package com.bstek.bdf2.export.excel;

import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
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.ss.util.CellUtil;
import org.apache.poi.ss.util.RegionUtil;
import org.springframework.stereotype.Component;

import com.bstek.bdf2.export.excel.style.FormStyleBuilder;
import com.bstek.bdf2.export.excel.style.GridStyleBuilder;
import com.bstek.bdf2.export.excel.style.GridStyleType;
import com.bstek.bdf2.export.excel.style.TitleStyleBuilder;
import com.bstek.bdf2.export.model.ReportFormData;
import com.bstek.bdf2.export.model.ReportForm;
import com.bstek.bdf2.export.model.ReportGridHeader;
import com.bstek.bdf2.export.model.ReportGrid;
import com.bstek.bdf2.export.model.ReportTitle;
import com.bstek.bdf2.export.utils.ExportUtils;

/**
 * @author matt.yao@bstek.com
 * @since 2.0
 */
@Component(ExcelReportBuilder.BEAN_ID)
public class ExcelReportBuilder extends AbstractExcelReportBuilder {
	
	
	private short rowHeitht = 2*200;
	
	private short titleRowHeitht = 2*300;

    public static final String BEAN_ID = "bdf2.ExcelReportBuilder";

    public int addTitleToSheet(ReportTitle reportTitle, Sheet sheet, int lastCol) {
        return this.addTitleToSheet(reportTitle, sheet, 0, 0, lastCol);
    }

    /**
     * 二〇二〇年七月八日 调整，如果标题中包含制表人等信息，创建两行数据
     *
     * @param reportTitle
     * @param sheet
     * @param row
     * @param firstCol
     * @param lastCol
     * @return
     */
    public int addTitleToSheet(ReportTitle reportTitle, Sheet sheet, int row,
                               int firstCol, int lastCol) {

        if (!reportTitle.isShowTitle()) {
            return row;
        }
        Row titleRow = sheet.createRow(row);
        Cell titleCell = titleRow.createCell(firstCol);
        titleCell.setCellType(Cell.CELL_TYPE_STRING);
        titleCell.setCellValue(reportTitle.getTitle());

        CellStyle titleStyle = new TitleStyleBuilder().builder(reportTitle,
                sheet.getWorkbook());
        titleCell.setCellStyle(titleStyle);

        CellRangeAddress rangle = new CellRangeAddress(row, row, firstCol,
                lastCol);
        sheet.addMergedRegion(rangle);

        // System.out.println(titleRow.getHeight());
        titleRow.setHeight(titleRowHeitht);

        // 2020年7月8日 取消标题的边框
        // this.setCellRangeAddressBorder(rangle, sheet);

        // 创建第二行信息
        if (null != reportTitle.getCreateUser()
                || null != reportTitle.getTimeDesc()) {

            row++;
            Row descRow = sheet.createRow(row);
            descRow.setHeight(rowHeitht);
            Cell cell;
            int col = firstCol;
            // 第一个位置是时间：timeDesc
            if (null != reportTitle.getTimeDesc()) {
                cell = descRow.createCell(col);
                cell.setCellType(Cell.CELL_TYPE_STRING);

                if (null == reportTitle.getTimeDescLabel()) {
                    cell.setCellValue("时间：" + reportTitle.getTimeDesc());
                } else {
                    cell.setCellValue(reportTitle.getTimeDescLabel() +"："+ reportTitle.getTimeDesc());
                }
            }
            // 第二个位置是仓库名称/说明
            if (null != reportTitle.getDepotName()) {
                col = col + lastCol / 4;
                cell = descRow.createCell(col);
                cell.setCellType(Cell.CELL_TYPE_STRING);

				if (null == reportTitle.getDepotNameLabel()) {
					cell.setCellValue("仓库：" + reportTitle.getDepotName());
				} else {
					cell.setCellValue(reportTitle.getDepotNameLabel() +"："+ reportTitle.getDepotName());
				}
            }
            // 第三个位置是创建人
            if (null != reportTitle.getCreateUser()) {
                col = col + lastCol / 2;
                cell = descRow.createCell(col);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue("制表人：" + reportTitle.getCreateUser());
            }
            // 第四个是单位
            if (null != reportTitle.getUnit()) {
                col = lastCol - 1;
                cell = descRow.createCell(col);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue("单位：" + reportTitle.getUnit());
            }

            // 第四个是单位
            if (null != reportTitle.getCreateTime()) {
                col = lastCol - 2;
                cell = descRow.createCell(col);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue("制表时间：" + reportTitle.getCreateTime());
            }
        }

        return row + 1;
    }

    public int addFormToSheet(ReportForm reportFormModel, Sheet sheet, int rowNo)
            throws Exception {
        rowNo = buildFormExcelData(reportFormModel, sheet, rowNo);
        return rowNo;
    }

    public int addGridToSheet(ReportGrid reportGridModel, Sheet sheet,
                              int initRowNo) {
    	
    	
        Map<String, CellStyle> styles = new GridStyleBuilder()
                .builderGridStyles(sheet.getWorkbook(), reportGridModel);
        int nextRow = this.buildGridExcelHeader(reportGridModel, sheet,
                initRowNo, styles);
        nextRow = this.buildGridExcelData(reportGridModel, sheet, nextRow,
                styles);
        return nextRow;
    }

    private int buildGridExcelHeader(ReportGrid gridModel, Sheet sheet,
                                     int starHeaderRow, Map<String, CellStyle> styles) {
        Map<Integer, Object> rowMap = new HashMap<Integer, Object>();
        this.calculateMaxHeaderLevel(gridModel,
                gridModel.getGridHeaderModelList());
        int maxHeaderLevel = gridModel.getMaxHeaderLevel();
        for (int i = 0; i < maxHeaderLevel; i++) {
            Row row = sheet.createRow((short) i + starHeaderRow);
            rowMap.put(i + starHeaderRow, row);
        }
        List<ReportGridHeader> topHeaders = new ArrayList<ReportGridHeader>();
        calculateGridHeadersByLevel(gridModel.getGridHeaderModelList(), 1,
                topHeaders);
        this.buildGridExcelHeader(sheet, rowMap, maxHeaderLevel, 1,
                starHeaderRow, 0, topHeaders, styles);
        return starHeaderRow + maxHeaderLevel;

    }

    private void buildGridExcelHeader(Sheet sheet, Map<Integer, Object> rowMap,
                                      int maxHeaderLevel, int currentLevel, int startHeaderRow,
                                      int startHeaderCol, List<ReportGridHeader> topHeaders,
                                      Map<String, CellStyle> styles) {
        CellStyle headerStyle = styles.get(GridStyleType.headerStyle.name());
        int currentRow = startHeaderRow + currentLevel - 1;
        Cell cell;
        Row row = (Row) rowMap.get(currentRow);
        row.setHeight(rowHeitht);
        int currentCol = startHeaderCol;
        CellRangeAddress cellRangeAddress;
        for (ReportGridHeader headerModel : topHeaders) {
            cell = row.createCell((short) currentCol);
            cell.setCellValue(headerModel.getLabel());
            int firstRow = currentRow;
            int lastRow = currentRow;
            int firstCol = currentCol;
            int lastCol = currentCol;
            int colspan = calculateGridHeaderColspan(headerModel);
            cell.setCellStyle(headerStyle);
            if (headerModel.getHeaders().size() == 0) {
                int rowspan = maxHeaderLevel - headerModel.getLevel();
                cellRangeAddress = new CellRangeAddress(firstRow, lastRow
                        + rowspan, firstCol, lastCol + colspan - 1);
                sheet.addMergedRegion(cellRangeAddress);
                this.setCellRangeAddressBorder(cellRangeAddress, sheet);
            } else {
                cellRangeAddress = new CellRangeAddress(firstRow, lastRow,
                        firstCol, lastCol + colspan - 1);
                sheet.addMergedRegion(cellRangeAddress);
                this.setCellRangeAddressBorder(cellRangeAddress, sheet);
                this.buildGridExcelHeader(sheet, rowMap, maxHeaderLevel,
                        headerModel.getLevel() + 1, startHeaderRow, firstCol,
                        headerModel.getHeaders(), styles);
            }
            currentCol = currentCol + colspan;
        }

    }

    private int buildFormExcelData(ReportForm reportFormModel, Sheet sheet,
                                   int initDataRow) throws Exception {
        SimpleDateFormat sdf = ExportUtils.getSimpleDateFormat();
        List<ReportFormData> formExcelDataList = reportFormModel
                .getListReportFormDataModel();

        FormStyleBuilder formStyleBuilder = new FormStyleBuilder();
        CellStyle labelCellStyle = formStyleBuilder.builderLabelCellStyle(
                reportFormModel, sheet.getWorkbook());
        CellStyle dataCellStyle = formStyleBuilder.builderValueCellStyle(
                reportFormModel, sheet.getWorkbook());

        Map<Integer, List<ReportFormData>> group = new HashMap<Integer, List<ReportFormData>>();
        List<ReportFormData> currentReportFormDataModels = new ArrayList<ReportFormData>();
        int rowIndx = 0;
        int colCount = reportFormModel.getColumnCount() * 2;
        int currentRowColumnSize = 0;
        int i = 1;
        for (ReportFormData reportFormDataModel : formExcelDataList) {
            int colSpan = reportFormDataModel.getColSpan();
            int formDataColumnSize = colSpan * 2 - 1 + 1;
            if (currentRowColumnSize + formDataColumnSize <= colCount) {
                currentRowColumnSize = currentRowColumnSize
                        + formDataColumnSize;
            } else {
                group.put(rowIndx, currentReportFormDataModels);
                currentRowColumnSize = formDataColumnSize;
                currentReportFormDataModels = new ArrayList<ReportFormData>();
                rowIndx++;
            }
            currentReportFormDataModels.add(reportFormDataModel);
            if (i == formExcelDataList.size()) {
                group.put(rowIndx, currentReportFormDataModels);
            }
            i++;
        }
        CellRangeAddress cellRangeAddress;
        Row row;
        Cell cellLabel;
        Cell cellData;
        for (Map.Entry<Integer, List<ReportFormData>> entry : group.entrySet()) {
            int key = entry.getKey();
            int currentRow = initDataRow + key;
            row = sheet.createRow(currentRow);
            List<ReportFormData> value = entry.getValue();
            int firstCol = 0;
            for (ReportFormData reportFormDataModel : value) {
                String label = reportFormDataModel.getLabel();
                Object data = reportFormDataModel.getData();
                int colSpan = reportFormDataModel.getColSpan();

                cellLabel = row.createCell(firstCol);
                cellLabel.setCellValue(label);
                cellLabel.setCellStyle(labelCellStyle);
                sheet.setColumnWidth(cellLabel.getColumnIndex(), 30 * 256);

                firstCol = firstCol + 1;
                cellData = row.createCell(firstCol);
                cellData.setCellStyle(dataCellStyle);
                this.fillCellValue(cellData, data, sdf);
                sheet.setColumnWidth(cellData.getColumnIndex(), 30 * 256);

                cellRangeAddress = new CellRangeAddress(currentRow, currentRow,
                        firstCol, firstCol + colSpan * 2 - 2);
                sheet.addMergedRegion(cellRangeAddress);
                this.setFormRegionStyle(sheet, cellRangeAddress, dataCellStyle);

                firstCol = firstCol + colSpan * 2 - 1;
            }
        }
        return group.keySet().size() + initDataRow;
    }

    private int buildGridExcelData(ReportGrid gridModel, Sheet sheet,
                                   int starDataRow, Map<String, CellStyle> styles) {
    	
        CellStyle dataAlignLeftStyle = styles
                .get(GridStyleType.dataAlignLeftStyle.name());
        CellStyle dataAlignCenterStyle = styles
                .get(GridStyleType.dataAlignCenterStyle.name());
        CellStyle dataAlignRightStyle = styles
                .get(GridStyleType.dataAlignRightStyle.name());

        SimpleDateFormat sdf = ExportUtils.getSimpleDateFormat();

        List<ReportGridHeader> bottomGridExcelHeader = new ArrayList<ReportGridHeader>();
        this.calculateBottomColumnHeader(gridModel.getGridHeaderModelList(),
                bottomGridExcelHeader);

        List<Map<String, Object>> excelDatas = gridModel.getGridDataModel()
                .getDatas();
        String treeColumn = gridModel.getGridDataModel().getTreeColumn();
        int excelDataIndex = 0;
        int rowSize = excelDatas.size();
        Cell cell;
        Row row;
        for (int rowNum = starDataRow; rowNum <= starDataRow + rowSize - 1; rowNum++) {
            row = sheet.createRow(rowNum);
            
            //2022年9月28日 23:50:18 手动设置行高
            row.setHeight(rowHeitht);
            
            Map<String, Object> map = excelDatas.get(excelDataIndex);
            int j = 0;
            for (ReportGridHeader header : bottomGridExcelHeader) {
                Object value = map.get(header.getColumnName());
                int dataAlign = header.getDataAlign();
                cell = row.createCell(j);
                if (dataAlign == 1) {
                    cell.setCellStyle(dataAlignCenterStyle);
                } else if (dataAlign == 2) {
                    cell.setCellStyle(dataAlignRightStyle);
                } else {
                    cell.setCellStyle(dataAlignLeftStyle);
                }
                if (value != null) {
                    if (header.getColumnName().equalsIgnoreCase(treeColumn)) {
                        int level = this.calculateIndentationCount(value
                                .toString());
                        cell.setCellStyle(new GridStyleBuilder()
                                .createIndentationCellStyle(
                                        sheet.getWorkbook(), level == 0 ? 0
                                                : level * 2));
                        cell.setCellValue(value.toString());
                    } else {
                        if (value instanceof BigDecimal
                                || value instanceof Float
                                || value instanceof Double) {
                            CellStyle style = cell.getCellStyle();
                            DataFormat format = sheet.getWorkbook()
                                    .createDataFormat();
                            String displayFormat = header.getDisplayFormat();
                            if (StringUtils.isEmpty(displayFormat))
                                style.setDataFormat(format.getFormat("#"));
                            else
                                style.setDataFormat(format
                                        .getFormat(displayFormat));
                            cell.setCellStyle(style);
                        }
                        this.fillCellValue(cell, value, sdf);
                    }

                } else {
                    cell.setCellValue("");
                }

                // 2020年7月8日 17:11:03 注释掉原来的宽度计算
                int columnWidth = sheet.getColumnWidth(cell.getColumnIndex()) / 256;
                int length;
                try {
                    length = cell.toString().getBytes("GBK").length;
                    if (columnWidth < length + 1) {
                        columnWidth = length + 1;
                    }
                } catch (UnsupportedEncodingException e) {
                    System.out.println("Excel计算宽度异常:" + e.getMessage());
                }
                sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);

                // sheet.setColumnWidth(
                // cell.getColumnIndex(),
                // header.getWidth() / 6 > 255 ? 254 * 256 : header
                // .getWidth() / 6 * 256);
                j++;
            }
            excelDataIndex++;
        }
        return starDataRow + rowSize;

    }

    private void setCellRangeAddressBorder(CellRangeAddress rangle, Sheet sheet) {
        int border = 1;
        Workbook wb = sheet.getWorkbook();
        RegionUtil.setBorderBottom(border, rangle, sheet, wb);
        RegionUtil.setBorderLeft(border, rangle, sheet, wb);
        RegionUtil.setBorderRight(border, rangle, sheet, wb);
        RegionUtil.setBorderTop(border, rangle, sheet, wb);
    }

    private void setFormRegionStyle(Sheet sheet, CellRangeAddress ca,
                                    CellStyle cs) {
        for (int i = ca.getFirstRow(); i <= ca.getLastRow(); i++) {
            Row row = CellUtil.getRow(i, sheet);
            for (int j = ca.getFirstColumn(); j <= ca.getLastColumn(); j++) {
                Cell cell = CellUtil.getCell(row, j);
                cell.setCellStyle(cs);
            }
        }
    }

    private int calculateIndentationCount(String s) {
        int count = 0;
        for (int i = 0; i < s.length(); i++) {
            char temp = s.charAt(i);
            if (temp == '\u0009') {
                count++;
            }
        }
        return count;
    }

}