package com.ywt.core.utils;

import jxl.Cell;
import jxl.Sheet;
import jxl.format.*;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * Created by 戴怡华
 *
 * @desc excel 下载里面抽出来的一些代码
 */
public class ExcelHelper {


    /**
     * 下载excel 时 设置 httpServletResponse
     */
    public static HttpServletResponse setHttpServletResponse(HttpServletResponse httpServletResponse, String filename) throws IOException {
        httpServletResponse.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "ISO-8859-1"));
        httpServletResponse.setContentType("application/vnd.ms-excel;charset=UTF-8");
        httpServletResponse.setHeader("Pragma", "no-cache");
        httpServletResponse.setHeader("Cache-Control", "no-cache");
        httpServletResponse.setDateHeader("Expires", 0);
        return httpServletResponse;
    }


    /**
     * 得到已经设置好的excel模版(用于正文)
     *
     * @return
     */
    public static WritableCellFormat getWritableCellFormat() throws WriteException {
        //设置字体;
        WritableFont font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);

        WritableCellFormat cellFormat = new WritableCellFormat(font);
        //设置背景颜色;
        cellFormat.setBackground(Colour.WHITE);
        //设置边框;
        cellFormat.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);
        //设置自动换行;
        cellFormat.setWrap(true);
        //设置文字居中对齐方式;
        cellFormat.setAlignment(Alignment.CENTRE);
        //设置垂直居中;
        cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
        return cellFormat;
    }

    /**
     * 得到已经设置好的excel模版（细实线）
     *
     * @return
     */
    public static WritableCellFormat getThinWritableCellFormat() throws WriteException {
        //给第二行设置背景、字体颜色、对齐方式等等;
        WritableFont font2 = new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
        WritableCellFormat cellFormat2 = new WritableCellFormat(font2);
        //设置文字居中对齐方式;
        cellFormat2.setAlignment(Alignment.CENTRE);
        //设置垂直居中;
        cellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);
        cellFormat2.setBackground(Colour.WHITE);
        cellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
        cellFormat2.setWrap(true);
        return cellFormat2;
    }

    /**
     * 得到已经设置好的excel模版（用于标题）
     *
     * @return {@link WritableCellFormat}
     */
    public static WritableCellFormat getTitleWritableCellFormat() throws WriteException {
        //给第二行设置背景、字体颜色、对齐方式等等;
        WritableFont font2 = new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
        WritableCellFormat cellFormat2 = new WritableCellFormat(font2);
        //设置文字居中对齐方式;
        cellFormat2.setAlignment(Alignment.CENTRE);
        //设置垂直居中;
        cellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);
        cellFormat2.setBackground(Colour.WHITE);
        cellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
        cellFormat2.setWrap(true);
        return cellFormat2;
    }


    /**
     * 设置列的宽度
     *
     * @param sheet 表单
     * @param map   map集合，key：当前列，value：宽度
     */
    public static void settingColumnWidth(WritableSheet sheet, Map<Integer, Integer> map) {
        //给 key+1 列设置不同的宽度value;
        map.forEach(sheet::setColumnView);
        //map.forEach(sheet::setColumnView); 等同于：map.forEach((key, value)-> sheet.setColumnView(key, value));
        //给sheet电子版中所有的列设置默认的列的宽度;
        sheet.getSettings().setDefaultColumnWidth(20);
        sheet.getSettings().setDefaultRowHeight(30 * 20);
    }


    /**
     * 设置标题行
     *
     * @param sheet      表单
     * @param cellFormat 单元格
     * @param titleStr   标题数组，eg: ["第一列","第二列","第三列","第四列"]
     */
    public static void settingTitleLabelCell(WritableSheet sheet, WritableCellFormat cellFormat, String[] titleStr) throws WriteException {
        for (int i = 0, len = titleStr.length; i < len; i++) {
            Label labVal = new Label(i, 0, titleStr[i], cellFormat);
            sheet.addCell(labVal);
        }
    }

    public static void settingTitleLabelCell(WritableSheet sheet, WritableCellFormat cellFormat, int c, String[] titleStr) throws WriteException {
        for (int i = 0, len = titleStr.length; i < len; i++) {
            Label labVal = new Label(i, c, titleStr[i], cellFormat);
            sheet.addCell(labVal);
        }
    }
    /**
     * 设置正文内容
     *
     * @param sheet      表单
     * @param cellFormat 单元格
     * @param c          the column
     * @param bodyStr    标题数组，eg: ["第一列","第二列","第三列","第四列"]
     */
    public static void settingMainBodyLabelCell(WritableSheet sheet, WritableCellFormat cellFormat, int c, String[] bodyStr) throws WriteException {
        for (int i = 0, len = bodyStr.length; i < len; i++) {
            Label labVal = new Label(i, c, bodyStr[i], cellFormat);
            sheet.addCell(labVal);
        }
    }


    /**
     * @param sheet      {@link WritableSheet}
     * @param contentArr 内容数组
     * @param colOffset  列的偏移量
     * @param row        行
     * @param cellFormat {@link CellFormat} 格式化
     */
    public static WritableSheet sheetAddCell(WritableSheet sheet, String[] contentArr, int colOffset, int row, CellFormat cellFormat) throws WriteException{
        for (int i = 0, len = contentArr.length; i < len; i++) {
            Label labVal = new Label(i + colOffset, row, contentArr[i], cellFormat);
            sheet.addCell(labVal);
        }
        return sheet;
    }


    /**
     * 得到单元格里面的值
     *
     * @param sheet 表单
     * @param col   列数
     * @param row   行数
     * @return 单元格里的值
     */
    private static String getCellValue(Sheet sheet, int col, int row) {
        Cell valueCell = sheet.getCell(col, row);
        String value = "";
        if (!Checker.isNone(valueCell)) {
            value = valueCell.getContents().trim();
        }
        return value;
    }

    /**
     * 得到当前表格里面的所有数据（第一行的值不能为空），并返回
     *
     * @param sheet 表格
     * @return list数据
     */
    public static List<Object> getExcelContent(Sheet sheet) {
        // 总行数
        int rows = sheet.getRows();
        // 总列数
        int cols = sheet.getColumns();
        // 存放返回的list集合
        List<Object> lst = new LinkedList<>();
        if (rows > 0) {
            // 从第一行开始遍历，每行建立一个map对象
            for (int i = 1; i < rows; i++) {
                Map<String, String> contentMap = new HashMap<>(16);
                for (int j = 0; j < cols; j++) {
                    // 第一行的值作为key，key不能为空
                    String key = getCellValue(sheet, j, 0);
                    if (!"".equals(key)) {
                        // 得到后面的值为value
                        String value = getCellValue(sheet, j, i);
                        contentMap.put(key, value);
                    }
                }
                lst.add(contentMap);
            }
        }
        return lst;
    }

}
