package com.aostar.ida.framework.util.excel;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
import com.aostar.ida.framework.base.exception.ServiceException;
/**
 * 处理大的excel表
 */
public class ExcelXlsxReader extends DefaultHandler {
    /**
     * 单元格中的数据可能的数据类型
     */
    enum CellDataType {
        BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
    }
    /**
     * 共享字符串表
     */
    private SharedStringsTable sst;
    /**
     * 上一次的索引值
     */
    private String lastIndex;
    /**
     * sheet名
     */
    private String sheetName = "";
    /**
     * 一行内cell集合
     */
    private List<CellVO> cellList = new ArrayList<>();
    /**
     * 判断整行是否为空行的标记
     */
    private boolean flag = false;
    /**
     * 当前行
     */
    private int curRow = 1;
    /**
     * 当前列
     */
    private int curCol = 0;
    /**
     * T元素标识
     */
    private boolean isTElement;
    /**
     * 单元格数据类型，默认为字符串类型
     */
    private CellDataType nextDataType = CellDataType.SSTINDEX;
    /**
     * 日期格式化
     */
    private final DataFormatter formatter = new DataFormatter();
    /**
     * 单元格日期格式的索引
     */
    private short formatIndex;
    /**
     * 日期格式字符串
     */
    private String formatString;
    /**
     * 定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等
     */
    private String preRef = null;
    /**
     * 当前元素位置
     */
    private String ref = null;
    /**
     * 定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格
     */
    private String maxRef = null;
    /**
     * 单元格
     */
    private StylesTable stylesTable;
    /**
     * 当前sheet对象
     */
    private SheetVO curSheetVO;
    /**
     * 当前字段的数据类型
     */
    private int curDataType;
    /**
     * 遍历工作簿中所有的电子表格
     * 并缓存在mySheetList中
     *
     * @param bytes excel字节流
     * @throws IOException  异常
     * @throws OpenXML4JException  异常
     * @throws SAXException 异常
     * @return excel对象 
     */
    public ExcelVO process(byte[] bytes)
        throws IOException, OpenXML4JException, SAXException {
        InputStream inputStream = new ByteArrayInputStream(bytes);
        OPCPackage pkg = OPCPackage.open(inputStream);
        XSSFReader xssfReader = new XSSFReader(pkg);
        stylesTable = xssfReader.getStylesTable();
        SharedStringsTable sst = xssfReader.getSharedStringsTable();
        XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
        parser.setFeature("http://apache.org/xml/features/disallow-doctype-decl", true);
        parser.setFeature("http://xml.org/sax/features/external-general-entities", false);
        parser.setFeature("http://xml.org/sax/features/external-parameter-entities", false);
        this.sst = sst;
        parser.setContentHandler(this);
        XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator)xssfReader.getSheetsData();
        ExcelVO excelVO = new ExcelVO();
        List<SheetVO> sheetList = new ArrayList<SheetVO>();
        excelVO.setSheets(sheetList);
        while (sheets.hasNext()) { // 遍历sheet
            curSheetVO = new SheetVO();
            List<RowVO> datas = new ArrayList<>();
            curSheetVO.setDatas(datas);
            curRow = 1; // 标记初始行为第一行
            InputStream sheet = sheets.next(); // sheets.next()和sheets.getSheetName()不能换位置，否则sheetName报错
            sheetName = sheets.getSheetName();
            curSheetVO.setName(sheetName);
            sheetList.add(curSheetVO);
            InputSource sheetSource = new InputSource(sheet);
            parser.parse(sheetSource); // 解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行
            sheet.close();
        }
        return excelVO; // 返回该excel文件的总行数，不包括首列和空行
    }
    /**
     * 第一个执行
     *
     */
    @Override
    public void startElement(String uri, String localName, String name, Attributes attributes)
        throws SAXException {
        // c => 单元格
        if ("c".equals(name)) {
          //开关位置不对，则证明转换后的xml只有c为没有v   <c r="XX" s="XX"></c>  这里需要自己补齐v的数据
          //由于计算实际V比较困难，这里采用有C就先占位，然后有V就替换列表最后一个元素的方式来处理
        CellVO cellVO = new CellVO();
            cellVO.setValue("");
            cellVO.setDataType(CellVO.DATA_TYPE_STRING);
            cellList.add(curCol, cellVO);
            curCol++ ;
            // 前一个单元格的位置
            if (preRef == null) {
                preRef = attributes.getValue("r");
            } else {
                preRef = ref;
            }
            // 当前单元格的位置
            ref = attributes.getValue("r");
            //检查是否有空单元格需要补齐
            int blankCount = countNullCell(ref, preRef);
            if(blankCount > 0){
              for (int i = 0; i < blankCount; i++ ) {
                CellVO blankVO = new CellVO();
                blankVO.setValue("");
                blankVO.setDataType(CellVO.DATA_TYPE_STRING);
                cellList.add(curCol, cellVO);
                curCol++ ;
              }
            }
            // 设定单元格类型
            this.setNextDataType(attributes);
        }
        // 当元素为t时
        if ("t".equals(name)) {
            isTElement = true;
        } else {
            isTElement = false;
        }
        // 置空
        lastIndex = "";
    }
    /**
     * 第二个执行
     * 得到单元格对应的索引值或是内容值
     * 如果单元格类型是字符串、INLINESTR、数字、日期，lastIndex则是索引值
     * 如果单元格类型是布尔值、错误、公式，lastIndex则是内容值
     */
    @Override
    public void characters(char[] ch, int start, int length)
        throws SAXException {
        lastIndex += new String(ch, start, length);
    }
    /**
     * 第三个执行
     *
     */
    @Override
    public void endElement(String uri, String localName, String name)
        throws SAXException {
        // t元素也包含字符串
        if (isTElement) {// 这个程序没经过
            // 将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
            String value = lastIndex.trim();
            CellVO cellVO = new CellVO();
            cellVO.setValue(value);
            cellVO.setDataType(curDataType);
            cellList.set(curCol-1, cellVO);
            isTElement = false;
            // 如果里面某个单元格含有值，则标识该行不为空行
            if (value != null && !"".equals(value)) {
                flag = true;
            }
        } else if ("v".equals(name)) {
            // v => 单元格的值，如果单元格是字符串，则v标签的值为该字符串在SST中的索引
            String value = this.getDataValue(lastIndex.trim(), "");// 根据索引值获取对应的单元格值
            if(null != value){
              value = value.trim();
            }
            CellVO cellVO = new CellVO();
            cellVO.setValue(value);
            cellVO.setDataType(curDataType);
            cellList.set(curCol-1, cellVO);
            // 如果里面某个单元格含有值，则标识该行不为空行
            if (value != null && !"".equals(value)) {
                flag = true;
            }
        } else {
            // 如果标签名称为row，这说明已到行尾，调用optRows()方法
            if ("row".equals(name)) {
                // 默认第一行为表头，以该行单元格数目为最大数目
                if (curRow == 1) {
                    maxRef = ref;
                    // 表头
                    List<String> titles = new ArrayList<>();
                    for (CellVO cell : cellList) {
                        String titleStr = cell.getValue().trim();
                        // 处理特殊字符，全部替换为下划线，避免SQL报错,只保留汉字，字母，数字
                        String patten = "[^\u4e00-\u9fa5a-zA-Z0-9]";
                        titleStr = titleStr.replaceAll(patten, "_");
                        if (StringUtils.isBlank(titleStr)) {
                            throw new ServiceException("表格的第一行必须有值");
                        }
                        // 处理表头过长
                        if (titleStr.length() > 40) {
                            titleStr = titleStr.substring(0, 40);
                        }
                        titles.add(titleStr);
                    }
                    curSheetVO.setTitles(titles);
                }
                // 补全一行尾部可能缺失的单元格
                if (maxRef != null) {
                  //检查是否有空单元格需要补齐
                    int blankCount = countNullCell(maxRef, ref);
                    if(blankCount >= 0){
                      for (int i = 0; i <= blankCount; i++ ) {
                        CellVO blankVO = new CellVO();
                        blankVO.setValue("");
                        blankVO.setDataType(CellVO.DATA_TYPE_STRING);
                        cellList.add(curCol, blankVO);
                        curCol++ ;
                      }
                    }
                }
                if (flag && curRow != 1) { // 该行不为空行且该行不是第一行，则发送（第一行为列名，不需要）
                    List<RowVO> datas = curSheetVO.getDatas();
                    RowVO rowVO = new RowVO();
                    datas.add(rowVO);
                    List<CellVO> cells = new ArrayList<>();
                    rowVO.setCells(cells);
                    for (CellVO cell : cellList) {
                        CellVO cellVO = new CellVO();
                        String value = cell.getValue();
                        if(null != value){
                          value = value.trim();
                        }
                        cellVO.setValue(value);
                        cellVO.setDataType(cell.getDataType());
                        cells.add(cellVO);
                    }
                }
                cellList.clear();
                curRow++ ;
                curCol = 0;
                preRef = null;
                ref = null;
                flag = false;
            }
        }
    }
    /**
     * 处理数据类型
     *
     * @param attributes 属性
     */
    public void setNextDataType(Attributes attributes) {
        nextDataType = CellDataType.NUMBER; // cellType为空，则表示该单元格类型为数字
        formatIndex = -1;
        formatString = null;
        String cellType = attributes.getValue("t"); // 单元格类型
        String cellStyleStr = attributes.getValue("s"); //
        if ("b".equals(cellType)) { // 处理布尔值
            nextDataType = CellDataType.BOOL;
        } else if ("e".equals(cellType)) { // 处理错误
            nextDataType = CellDataType.ERROR;
        } else if ("inlineStr".equals(cellType)) {
            nextDataType = CellDataType.INLINESTR;
        } else if ("s".equals(cellType)) { // 处理字符串
            nextDataType = CellDataType.SSTINDEX;
        } else if ("str".equals(cellType)) {
            nextDataType = CellDataType.FORMULA;
        }
        if (cellStyleStr != null) { // 处理日期
            int styleIndex = Integer.parseInt(cellStyleStr);
            XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
            formatIndex = style.getDataFormat();
            formatString = style.getDataFormatString();
            if (formatString.contains("m/d/yy")) {
                nextDataType = CellDataType.DATE;
                formatString = "yyyy-MM-dd hh:mm:ss";
            }
            if (formatString == null) {
                nextDataType = CellDataType.NULL;
                formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
            }
        }
    }
    /**
     * 对解析出来的数据进行类型处理
     * @param value   单元格的值，
     *                value代表解析：BOOL的为0或1， ERROR的为内容值，FORMULA的为内容值，INLINESTR的为索引值需转换为内容值，
     *                SSTINDEX的为索引值需转换为内容值， NUMBER为内容值，DATE为内容值
     * @param thisStr 一个空字符串
     * @return 单元格的值
     */
    public String getDataValue(String value, String thisStr) {
        curDataType = CellVO.DATA_TYPE_STRING;
        switch (nextDataType) {
            // 这几个的顺序不能随便交换，交换了很可能会导致数据错误
            case BOOL: // 布尔值
                char first = value.charAt(0);
                thisStr = first == '0' ? "FALSE" : "TRUE";
                break;
            case ERROR: // 错误
                thisStr = "\"ERROR:" + value.toString() + '"';
                break;
            case FORMULA: // 公式
                thisStr = '"' + value.toString() + '"';
                break;
            case INLINESTR:
                XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
                thisStr = rtsi.toString();
                rtsi = null;
                break;
            case SSTINDEX: // 字符串
                String sstIndex = value.toString();
                try {
                    int idx = Integer.parseInt(sstIndex);
                    XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));// 根据idx索引值获取内容值
                    thisStr = rtss.toString();
                    rtss = null;
                } catch (NumberFormatException ex) {
                    thisStr = value.toString();
                }
                break;
            case NUMBER: // 数字
                curDataType = CellVO.DATA_TYPE_NUMERIC;
                if (formatString != null) {
                    thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
                } else {
                    thisStr = value;
                }
                thisStr = thisStr.replace("_", "").trim();
                break;
            case DATE: // 日期
                curDataType = CellVO.DATA_TYPE_DATE;
                thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
                // 对日期字符串作特殊处理，去掉T
                thisStr = thisStr.replace("T", " ");
                break;
            default:
                thisStr = " ";
                break;
        }
        return thisStr;
    }
    private int countNullCell(String ref, String preRef) {
        // excel2007最大行数是1048576，最大列数是16384，最后一列列名是XFD
        String xfd = ref.replaceAll("\\d+", "");
        String preXfd = preRef.replaceAll("\\d+", "");
        xfd = fillChar(xfd, 3, '@', true);
        preXfd = fillChar(preXfd, 3, '@', true);
        char[] letter = xfd.toCharArray();
        char[] preLetter = preXfd.toCharArray();
        int res = (letter[0] - preLetter[0]) * 26 * 26 + (letter[1] - preLetter[1]) * 26 + (letter[2] - preLetter[2]);
        return res - 1;
    }
    private String fillChar(String str, int len, char let, boolean isPre) {
        int strLen = str.length();
        if (strLen < len) {
            if (isPre) {
                for (int i = 0; i < (len - strLen); i++ ) {
                    str = let + str;
                }
            } else {
                for (int i = 0; i < (len - strLen); i++ ) {
                    str = str + let;
                }
            }
        }
        return str;
    }
}

/*
 * 文件名：SheetVO.java
 * 版权：Copyright by aostarit
 * 描述：sheet实体
 */
package com.aostar.ida.framework.util.excel;
import java.util.List;
/**
 * excel的sheet实体对象
 * 
 * @version 2019年5月17日
 */
public class SheetVO {
    /**
     * 名称
     */
    private String name;
    /**
     * 表头列表--暂时只支持单表头，不支持跨行--约定为第一行
     */
    private List<String> titles;
    /**
     * 数据内容--不含表头
     */
    private List<RowVO> datas;
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public List<String> getTitles() {
        return titles;
    }
    public void setTitles(List<String> titles) {
        this.titles = titles;
    }
    public List<RowVO> getDatas() {
        return datas;
    }
    public void setDatas(List<RowVO> datas) {
        this.datas = datas;
    }
}

/*
 * 文件名：RowVO.java
 * 版权：Copyright by aostarit
 * 描述：excel行
 */
package com.aostar.ida.framework.util.excel;
import java.util.List;
/**
 * excel行对象
 * 
 * @version 2019年5月17日
 */
public class RowVO {
    /**
     * 单元格数据
     */
    private List<CellVO> cells;
    public List<CellVO> getCells() {
        return cells;
    }
    public void setCells(List<CellVO> cells) {
        this.cells = cells;
    }
}

/*
 * 文件名：CellVO.java
 * 版权：Copyright by aostarit
 * 描述：单元格对象
 */
package com.aostar.ida.framework.util.excel;
/**
 * 单元格实体
 * 
 * @version 2019年5月17日
 */
public class CellVO {
    /**
     * 数字类型
     */
    public static final int DATA_TYPE_NUMERIC = 1;
    /**
     * 字符串类型
     */
    public static final int DATA_TYPE_STRING = 2;
    /**
     * 日期类型
     */
    public static final int DATA_TYPE_DATE = 3;
    /**
     * 单元格的值
     */
    private String value;
    /**
     * 值的类型
     */
    private int dataType;
    public String getValue() {
        return value;
    }
    public void setValue(String value) {
        this.value = value;
    }
    public int getDataType() {
        return dataType;
    }
    public void setDataType(int dataType) {
        this.dataType = dataType;
    }
}

@Override
  public MsgContext exportSheetDatas(HttpServletResponse response,Long sheetId, int currentPageNo, int pageSize) {
    BizIdxSheets sheetPO = idxSheetsMapper.selectByPrimaryKey(Sutil.checkData(StringUtils.EMPTY, sheetId));
    if (null == sheetPO) {
      throw new ServiceException("未找到该指标图层信息！");
    }
    IdxSqlHander idxSqlHander = SpringUtil.getBean("idxSqlHander", IdxSqlHander.class);
    Map<String, Object> allMap=idxSqlHander.init(sheetId).queryData(currentPageNo, pageSize);
    List<Map> list = (List<Map>) allMap.get("titles");
    for (Map map : list) {
      map.get("label");
      LOGGER.info("===map=>"+map.get("label"));
    }
//    List<Map> listss1 = (List<Map>) allMap.get("datas");
//    
//    LOGGER.info("===listss=>"+listss1);
//    
//    listss1.forEach(li->{
//      li.values().forEach(v->{
//        LOGGER.info("=88=>"+v);
//      });
//      li.keySet().forEach(k->{
//        LOGGER.info("=99=>"+k);
//      });
//    });
  //  ArrayList<Map<String, Object>> rows = (ArrayList<Map<String, Object>>)allMap.get("datas");
  //  List<Map<String, Object>> compDatas = (List<Map<String, Object>>)allMap.get("datas");
    List<Map<String, String>> compTitles = (List<Map<String, String>>)MapUtils.getObject(allMap, "titles");
    List<Map<String, Object>> compDatas = (List<Map<String, Object>>)MapUtils.getObject(allMap, "datas");
    LOGGER.info("===compDatas=2>"+compDatas);
    LOGGER.info("===compTitles=2>"+compTitles);
     List<String> titles = new ArrayList<String>();
     for(Map<String, String> map : compTitles){
        titles.add(MapUtils.getString(map, "label"));
      }
      LOGGER.info("===titles=2>"+titles);
     List<List<Object>> exportDatas = new ArrayList<List<Object>>(compDatas.size());
     LOGGER.info("===exportDatas.size()==》"+exportDatas.size());
      for (int i = 0; i < compDatas.size(); i++ ) {
              List<Object> rowData = new ArrayList<Object>();
              Map<String, Object> compData = compDatas.get(i);
              for(String title : titles){
                rowData.add(MapUtils.getString(compData, title, ""));                
              }
              exportDatas.add(rowData);
          }
     LOGGER.info("===exportDatas=2>"+exportDatas);
    LOGGER.info("===json=2>"+JSON.toJSONString(allMap.get("titles")));
//    JSONArray  ac=JSON.parseArray(JSON.toJSONString(allMap.get("titles")));
//        
//    LOGGER.info("===ac=2>"+ac);
//    
//    
//    
//     for (int i = 0; i < ac.size(); i++) {
//       Map<String, Object> map = (Map<String, Object>) ac.get(i);
//       Iterator iterator = map.keySet().iterator();
//              while (iterator.hasNext()) {
//                  String string = (String) iterator.next();                
//                  LOGGER.info("===map.get(string)>"+map.get(string));
//              }
//
//     }
//     
//     JSONArray  ab=JSON.parseArray(JSON.toJSONString(allMap.get("datas")));
//        
//  
//     for (int i = 0; i < ab.size(); i++) {
//       Map<String, Object> map = (Map<String, Object>) ac.get(i);
//       Iterator iterator = map.keySet().iterator();
//              while (iterator.hasNext()) {
//                  String string = (String) iterator.next();                
//                  LOGGER.info("===map.get(string)>"+map.get(string));
//              }
//
//     }
//    
    HSSFWorkbook book=  ExcelUtils.data2Excel(exportDatas, titles, "sheet");
  //  String sceName = MapUtils.getString(allMap, "sceName");
    String sceName = "ExportXls";
      OutputStream os = null;
    try {
      os = response.getOutputStream();
      response.reset();// 清空输出流
      response.setContentType("application/x-msdownload");
      response.setCharacterEncoding("utf-8");
      response.setContentType("text/html;charset=utf-8");
      response.setHeader("Content-disposition", "attachment; filename="+URLEncoder.encode(sceName, "UTF-8")+".xls");// 设定输出文件头
      response.setContentType("application/msexcel");// 定义输出类型
      book.write(os);
      LOGGER.info("导出完成关闭流");
    } catch (IOException e) {
      return MsgContext.createErrorContext("导出失败。");
    } finally {
      IOUtils.closeQuietly(os);
    }
       return null;   
  }

allMap==>{sortColName=, datas=[{number=100, name=电工, deptID=200, ID=1}, {number=150, name=水工, deptID=300, ID=2}, {number=200, name=领导, deptID=400, ID=3}, {number=5000, name=董事长, deptID=250, ID=4}], titles=[{sign=, label=number, type=1}, {sign=, label=name, type=1}, {sign=, label=deptID, type=2}, {sign=, label=ID, type=2}], totalCount=4}