package cn.wise.sc.acquisition.business.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.FontFormatting;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
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.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.DecimalFormat;
import java.util.*;

/**
 * excel工具类
 *
 * @author zhutianwei
 */
public class ExcelUtil {
    /**
     * 导出excel
     *
     * @param fileName
     * @param headers
     * @param datas
     * @param response
     */
    public static void excelExport(String fileName, String[] headers, List<Object[]> datas,
                                   HttpServletResponse response) {
        Workbook workbook = getWorkbook(headers, datas);
        if (workbook != null) {
            ByteArrayOutputStream byteArrayOutputStream = null;
            try {
                byteArrayOutputStream = new ByteArrayOutputStream();
                workbook.write(byteArrayOutputStream);

                String suffix = ".xlsx";
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                response.setHeader("Content-Disposition",
                        "attachment;filename=" + new String((fileName + suffix).getBytes(), "iso-8859-1"));

                OutputStream outputStream = response.getOutputStream();
                outputStream.write(byteArrayOutputStream.toByteArray());
                outputStream.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (byteArrayOutputStream != null) {
                        byteArrayOutputStream.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }

                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void excelExportNew(String fileName, String[] headers, List<Object[]> datas,
                                      HttpServletResponse response) {
        Workbook workbook = getWorkbookNew(headers, datas);
        if (workbook != null) {
            ByteArrayOutputStream byteArrayOutputStream = null;
            try {
                byteArrayOutputStream = new ByteArrayOutputStream();
                workbook.write(byteArrayOutputStream);

                String suffix = ".xlsx";
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                response.setHeader("Content-Disposition",
                        "attachment;filename=" + new String((fileName + suffix).getBytes(), "iso-8859-1"));

                OutputStream outputStream = response.getOutputStream();
                outputStream.write(byteArrayOutputStream.toByteArray());
                outputStream.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (byteArrayOutputStream != null) {
                        byteArrayOutputStream.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }

                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void excelExportNew(String fileName, Workbook workbook,
                                      HttpServletResponse response) {
        if (workbook != null) {
            ByteArrayOutputStream byteArrayOutputStream = null;
            try {
                byteArrayOutputStream = new ByteArrayOutputStream();
                workbook.write(byteArrayOutputStream);

                String suffix = ".xlsx";
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                response.setHeader("Content-Disposition",
                        "attachment;filename=" + new String((fileName + suffix).getBytes(), "iso-8859-1"));

                OutputStream outputStream = response.getOutputStream();
                outputStream.write(byteArrayOutputStream.toByteArray());
                outputStream.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (byteArrayOutputStream != null) {
                        byteArrayOutputStream.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }

                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * @param headers 列头
     * @param datas   数据
     * @return
     */
    public static Workbook getWorkbook(String[] headers, List<Object[]> datas) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        Row row = null;
        Cell cell = null;
        XSSFFont font = workbook.createFont();
        int line = 0, maxColumn = 0;
        if (headers != null && headers.length > 0) { // 设置列头
            CellStyle style = workbook.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
            row = sheet.createRow(line++);
            row.setHeightInPoints(23);
            font.setBold(true);
            font.setFontHeightInPoints((short) 13);
            style.setFont(font);
            style.setWrapText(true);

            maxColumn = headers.length;
            for (int i = 0; i < maxColumn; i++) {
                cell = row.createCell(i);

                XSSFFont xxsfFont = workbook.createFont();
                //表头处理上下标
                if (headers[i] != null) {
                    //处理下标
                    if (headers[i].contains("<sup>") || headers[i].contains("</sup>")) {
                        headers[i] = headers[i].replaceAll("<sup>-</sup>", "⁻");
                    }

                    if (headers[i].contains("<sub>") || headers[i].contains("</sub>")) {
                        xxsfFont.setTypeOffset(FontFormatting.SS_SUB);
                        cell.setCellStyle(style);
                        XSSFRichTextString richString = new XSSFRichTextString( headers[i].replaceAll("</sub>", "<sub>").replaceAll("<sub>", ""));
                        //提取下标位置
                        applyRichStringFontsub( headers[i], richString, xxsfFont);
                        cell.setCellValue(richString);
//                                value = cell.getStringCellValue();
                    }else{
                        cell.setCellStyle(style);
                        cell.setCellValue( headers[i]);
                    }

                }
//                cell.setCellValue(headers[i]);
//                cell.setCellStyle(style);
            }
        }

        if (datas != null && datas.size() > 0) {// 渲染数据
            CellStyle style = workbook.createCellStyle();
            style.setWrapText(true);
            XSSFFont xxsfFont = workbook.createFont();

            for (int index = 0, size = datas.size(); index < size; index++) {
                Object[] data = datas.get(index);
                if (data != null && data.length > 0) {
                    row = sheet.createRow(line++);
                    row.setHeightInPoints(20);

                    int length = data.length;
                    if (length > maxColumn) {
                        maxColumn = length;
                    }

                    for (int i = 0; i < length; i++) {
                        String value = data[i] == null ? null : data[i].toString();
                        cell = row.createCell(i);
                        if (value != null) {
                            //处理下标
                            if (value.contains("<sup>") || value.contains("</sup>")) {
                                value = value.replaceAll("<sup>-</sup>", "⁻");
                            }

                            if (value.contains("<sub>") || value.contains("</sub>")) {
                                xxsfFont.setTypeOffset(FontFormatting.SS_SUB);
                                cell.setCellStyle(style);
                                XSSFRichTextString richString = new XSSFRichTextString(value.replaceAll("</sub>", "<sub>").replaceAll("<sub>", ""));
                                //提取下标位置
                                applyRichStringFontsub(value, richString, xxsfFont);
                                cell.setCellValue(richString);
//                                value = cell.getStringCellValue();
                            } else {
                                cell.setCellValue(value);
                                cell.setCellStyle(style);
                            }
                            /*if (value.contains("<sup>") || value.contains("</sup>")) {
                                xxsfFont.setTypeOffset(FontFormatting.SS_SUPER);
                                cell.setCellStyle(style);
                                XSSFRichTextString richString = new XSSFRichTextString(value.replaceAll("</sup>", "<sup>").replaceAll("<sup>", ""));
                                //提取下标位置
                                applyRichStringFontsup(value, richString, xxsfFont);
                                cell.setCellValue(richString);
                                value = cell.getStringCellValue();
                            }*/


                        }
                    }
                }
            }
        }

        for (int i = 0; i < maxColumn; i++) {
            sheet.autoSizeColumn(i);
            //单元格自适应宽度
            sheet.setColumnWidth(i, Math.max(15 * 256, Math.min(255 * 256, sheet.getColumnWidth(i) * 12 / 10)));
        }

        return workbook;
    }

    private static void applyRichStringFontsub(String sub, XSSFRichTextString richString, XSSFFont xxsfFont) {
        String[] split = sub.replaceAll("</sub>", "<sub>").split("<sub>");
        String it_str = "";
        for (int i = 1; i < split.length + 1; i++) {
            if (i % 2 != 0) {
                it_str = it_str + split[i - 1];
            } else {
                richString.applyFont(it_str.length(), (it_str + split[i - 1]).length(), xxsfFont);
                it_str = it_str + split[i - 1];
            }

        }
    }

    public static XSSFRichTextString applyFontsub(String sub, XSSFFont xxsfFont) {
        XSSFRichTextString richString = new XSSFRichTextString();
        if (sub.contains("<sup>") || sub.contains("</sup>")) {
            sub = sub.replaceAll("<sup>-</sup>", "⁻");
        }
        if (sub.contains("<sub>") || sub.contains("</sub>")) {
            xxsfFont.setTypeOffset(FontFormatting.SS_SUB);
            richString.setString(sub.replaceAll("</sub>", "<sub>").replaceAll("<sub>", ""));
            //提取下标位置
            applyRichStringFontsub(sub, richString, xxsfFont);
        } else {
            richString.setString(sub);
        }
        return richString;
    }

    private static void applyRichStringFontsup(String sup, XSSFRichTextString richString, XSSFFont xxsfFont) {
        String[] split = sup.replaceAll("</sup>", "<sup>").split("<sup>");
        String it_str = "";
        for (int i = 1; i < split.length + 1; i++) {
            if (i % 2 != 0) {
                it_str = it_str + split[i - 1];
            } else {
                richString.applyFont(it_str.length(), (it_str + split[i - 1]).length(), xxsfFont);
                it_str = it_str + split[i - 1];
            }

        }
    }


    public static Workbook getWorkbookNew(String[] headers, List<Object[]> datas) {
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        workbook.setSheetName(0, "样品处置记录");

        Row row = null;
        Cell cell = null;

        Font font = workbook.createFont();

        int line = 0, maxColumn = 0;
        if (headers != null && headers.length > 0) { // 设置列头
            CellStyle style = workbook.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            row = sheet.createRow(line++);
            row.setHeightInPoints(23);
            font.setFontHeightInPoints((short) 12);
            font.setFontName("宋体");
            style.setFont(font);

            maxColumn = headers.length;
            for (int i = 0; i < maxColumn; i++) {
                cell = row.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(style);
            }
        }

        if (datas != null && datas.size() > 0) {// 渲染数据
            CellStyle style = workbook.createCellStyle();
            for (int index = 0, size = datas.size(); index < size; index++) {
                Object[] data = datas.get(index);
                if (data != null && data.length > 0) {
                    row = sheet.createRow(line++);
                    row.setHeightInPoints(20);

                    int length = data.length;
                    if (length > maxColumn) {
                        maxColumn = length;
                    }

                    for (int i = 0; i < length; i++) {
                        style.setWrapText(true);  //关键
                        style.setFont(font);
                        cell = row.createCell(i);
                        cell.setCellValue(data[i] == null ? null : data[i].toString());
                        cell.setCellStyle(style);
                        style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
                    }
                }
            }
        }

        for (int i = 0; i < maxColumn; i++) {
            sheet.autoSizeColumn(i);
            //单元格自适应宽度
            sheet.setColumnWidth(i, Math.max(15 * 256, Math.min(255 * 256, sheet.getColumnWidth(i) * 12 / 10)));
        }

        return workbook;
    }

    /**
     * @param infoHeader  委托人信息表头
     * @param teamsHeader 检测项表头
     * @return
     */
    private static XSSFWorkbook generatoronsignationWorkbook(List<String> infoHeader, List<String> teamsHeader) {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("sheet1");
        XSSFCellStyle style = wb.createCellStyle();
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        int rownum = 0;
        //创建第一行
        XSSFRow row0 = sheet.createRow(rownum++);
        //处理委托人信息表头
        for (int i = 0; i < infoHeader.size(); i++) {
            XSSFCell tempCell = row0.createCell(i);
            tempCell.setCellValue(infoHeader.get(i));
            tempCell.setCellStyle(style);
        }
        //处理检测项总产值表头
        for (int i = infoHeader.size(); i < teamsHeader.size() + infoHeader.size(); i++) {
            XSSFCell tempCell = row0.createCell(i);
            tempCell.setCellValue(teamsHeader.get(i - infoHeader.size()));
            tempCell.setCellStyle(style);
        }

        //增加1空列
        {
            XSSFCell tempCell = row0.createCell(teamsHeader.size() + infoHeader.size());
            tempCell.setCellValue("");
            tempCell.setCellStyle(style);
        }
        //处理检测项信息表头
        for (int i = teamsHeader.size() + infoHeader.size() + 1; i < infoHeader.size() + 1 + teamsHeader.size() * 3; i++) {
            String header = teamsHeader.get((i - teamsHeader.size() - infoHeader.size() - 1) / 2);
            XSSFCell tempCell1 = row0.createCell(i);
            tempCell1.setCellStyle(style);
            tempCell1.setCellValue(header);
            XSSFCell tempCell2 = row0.createCell(++i);
            tempCell2.setCellValue(header);
            tempCell2.setCellStyle(style);
            //合并表头
            sheet.addMergedRegion(new CellRangeAddress(0, 0, i - 1, i));
        }
        //创建第二行
        XSSFRow row1 = sheet.createRow(rownum++);
        //处理委托人信息表头
        for (int i = 0; i < infoHeader.size(); i++) {
            XSSFCell tempCell = row1.createCell(i);
            tempCell.setCellValue("");
            tempCell.setCellStyle(style);
            //合并表头
            sheet.addMergedRegion(new CellRangeAddress(0, 1, i, i));
        }
        //处理检测项总产值表头
        for (int i = infoHeader.size(); i < teamsHeader.size() + infoHeader.size(); i++) {
            XSSFCell tempCell = row1.createCell(i);
            tempCell.setCellValue("计算产值");
            tempCell.setCellStyle(style);
        }
        //增加1空列
        {
            XSSFCell tempCell = row0.createCell(teamsHeader.size() + infoHeader.size());
            tempCell.setCellValue("");
            tempCell.setCellStyle(style);
        }
        //处理检测项信息表头
        for (int i = teamsHeader.size() + 1 + infoHeader.size(); i < infoHeader.size() + 1 + teamsHeader.size() * 3; i++) {
            XSSFCell tempCell1 = row1.createCell(i);
            tempCell1.setCellValue("单价");
            tempCell1.setCellStyle(style);
            XSSFCell tempCell2 = row1.createCell(++i);
            tempCell2.setCellValue("数量");
            tempCell2.setCellStyle(style);
        }

        return wb;
    }

    /**
     * 读取文件
     * @param fis
     * @return
     * @throws Exception
     */
    @SuppressWarnings("resource")
    public static List<Map<Integer, String>> readData(FileInputStream fis) throws Exception {
        XSSFWorkbook hwb = new XSSFWorkbook(fis);
        List<Map<Integer, String>> usermap = new ArrayList<>();
        // 获取第一个sheet页
        XSSFSheet sheetAt = hwb.getSheetAt(0);
        if (sheetAt == null) {
            return usermap;
        }

        // 遍历行里面的单元格内容.
        for (int i = 1; i <= sheetAt.getLastRowNum(); i++) {

            // User user = new User();
            Map<Integer, String> map = new HashMap<Integer, String>();

            // 得到每一行
            XSSFRow row = sheetAt.getRow(i);
            // 如果为空就跳出
            if (row == null) {
                continue;
            }
            // 遍历列
            for (int j = 0; j < row.getLastCellNum(); j++) {
                // 遍历列里面的内容
                XSSFCell cell = row.getCell(j);
                if (cell == null) {
                    map.put(j, null);
                }
                try {
                    map.put(j, cell.getStringCellValue());
                } catch (Exception e) {
                    map.put(j, new DecimalFormat("#0").format(cell.getNumericCellValue()).toString());
                }
            }
            usermap.add(map);
        }
        return usermap;
    }






    public static void main(String[] args) throws IOException {
        //指定数据存放的位置
        OutputStream outputStream = new FileOutputStream("D:\\test.xlsx");
        String[] in_fo = {
                "委托单号",
                "委托单位",
                "项目编号",
                "项目名称",
                "委托人",
                "样品数量",
                "发样日期",
                "是否加急",
                "实际产值",
        };
        List info = Arrays.asList(in_fo);
        String a = "荧光检测	L.O.I	SiO2	Al2O3+TiO2	Al2O3	TiO2	Fe2O3	CaO	MgO	K2O	Na2O	有效钾	有效钠	重量法SO3	SO3	挥发性硫	硫酸盐硫	Cl⁻	fCaO 1350	fCaO 1400	fCaO 1450	fCaO	附着水	结晶水	工业分析	St,ad	水分	密度	比表面积	45μm细度	80μm细度	200μm细度	激光粒度分析	颗粒形貌分析	塑性指数	白度	粒度筛析	易磨性	T3000	T5000	磨蚀性	可磨	辊磨	易烧性	其他-工艺性能	MnO	TiO2	五氧化二磷	铜(Cu)	铅(Pb)	锰(Mn)	镍(Ni)	锌(Zn)	钴(Co)	总铬(Cr)	镉(Cd)	汞(Hg)	砷(As)	锑(Sb)	水溶性铬(VI)	水泥强度检测1天	水泥强度检测2天	水泥强度检测3天	水泥强度检测7天	水泥强度检测28天	标准稠度用水量	流动度	凝结时间	安定性	岩相	衍射	弹筒发热量	热分析	金属铁	水化热	燃烧特性	不溶物	氟离子	游离硅	f-SiO2	f-SiO2+45	f-SiO2+80	其他	煤中硫";
        String[] te_rm = a.split("\t");
        List term = Arrays.asList(te_rm);
        XSSFWorkbook workbook = generatoronsignationWorkbook(info, term);
        workbook.write(outputStream);
        outputStream.close();
    }
}
