ExcelExportUtiler excel工具类

@Slf4j
public class ExcelExportUtil {

    /*工作薄*/
    private HSSFWorkbook workbook;

    /*默认格子宽度*/
    private int defaultColumnWidth = 20;

    /*sheet页的名称*/
    private String sheetName;

    /*默认标题字体*/
    private HSSFFont defaultTitleFont;

    /*默认内容字体*/
    private HSSFFont defaultContentFont;

    /*默认标题风格*/
    private HSSFCellStyle defaultTitleStyle;

    /*默认内容风格*/
    private HSSFCellStyle defaultContentStyle;

    /*最后一个sheet页索引*/
    private int lastSheetIndex = 0;

    public HSSFWorkbook getWorkbook() {
        return workbook;
    }

    public int getDefaultColumnWidth() {
        return defaultColumnWidth;
    }

    public String getSheetName() {
        return sheetName;
    }

    public HSSFFont getDefaultTitleFont() {
        return defaultTitleFont;
    }

    public HSSFFont getDefaultContentFont() {
        return defaultContentFont;
    }

    public HSSFCellStyle getDefaultTitleStyle() {
        return defaultTitleStyle;
    }

    public HSSFCellStyle getDefaultContentStyle() {
        return defaultContentStyle;
    }


    public ExcelExportUtil() {
        this(null, 0, null, null, null, null, null);
    }

    public ExcelExportUtil(int defaultColumnWidth) {
        this(defaultColumnWidth, null);
    }

    public ExcelExportUtil(String sheetName) {
        this(0, sheetName);
    }

    public ExcelExportUtil(int defaultColumnWidth, String sheetName) {
        this(null, defaultColumnWidth, sheetName);
    }

    public ExcelExportUtil(HSSFWorkbook workbook, int defaultColumnWidth, String sheetName) {
        this(workbook, defaultColumnWidth, sheetName, null, null, null, null);
    }

    public ExcelExportUtil(HSSFWorkbook workbook, int defaultColumnWidth, String sheetName, HSSFFont defaultTitleFont, HSSFFont defaultContentFont, HSSFCellStyle defaultTitleStyle, HSSFCellStyle defaultContentStyle) {

        if (workbook == null) {
            this.workbook = new HSSFWorkbook();
        } else {
            this.workbook = workbook;
        }

        if (defaultColumnWidth == 0) {
            this.defaultColumnWidth = 20;
        } else {
            this.defaultColumnWidth = defaultColumnWidth;
        }

        if (sheetName == null || "".equals(sheetName.trim())) {
            this.sheetName = "sheet";
        } else {
            this.sheetName = sheetName;
        }

        if (defaultTitleFont == null) {
            this.defaultTitleFont = getDefaultTitleFont(this.workbook);
        } else {
            this.defaultTitleFont = defaultTitleFont;
        }

        if (defaultContentFont == null) {
            this.defaultContentFont = getDefaultContentFont(this.workbook);
        } else {
            this.defaultContentFont = defaultContentFont;
        }

        if (defaultTitleStyle == null) {
            this.defaultTitleStyle = getDefaultTitleStyle(this.workbook);
        } else {
            this.defaultTitleStyle = defaultTitleStyle;
        }

        if (defaultContentStyle == null) {
            this.defaultContentStyle = getDefaultContentStyle(this.workbook);
        } else {
            this.defaultContentStyle = defaultContentStyle;
        }
    }

    /*获取默认标题字体*/
    public static HSSFFont getDefaultTitleFont(HSSFWorkbook workbook) {
        HSSFFont titleFont = workbook.createFont();
        titleFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());                  //字体颜色
        titleFont.setFontHeightInPoints((short) 9);                 //字号
        titleFont.setBold(true);          //粗体
        titleFont.setFontName("微软雅黑");                          //微软雅黑
        return titleFont;
    }

    /*获取默认内容字体*/
    public static HSSFFont getDefaultContentFont(HSSFWorkbook workbook) {
        HSSFFont contentFont = workbook.createFont();                 //定义内容字体样式
        contentFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());                  //字体颜色
        contentFont.setFontHeightInPoints((short) 9);                 //字号
        contentFont.setBold(true);        //普通粗细
        contentFont.setFontName("微软雅黑");                          //微软雅黑
        return contentFont;
    }

    /*获取默认标题样式*/
    public static HSSFCellStyle getDefaultTitleStyle(HSSFWorkbook workbook) {
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setBorderBottom(BorderStyle.THIN);                 //设置底部边线
        titleStyle.setBorderLeft(BorderStyle.THIN);                   //设置左部边线
        titleStyle.setBorderRight(BorderStyle.THIN);                  //设置右部边线
        titleStyle.setBorderTop(BorderStyle.THIN);                    //设置顶部边线
        titleStyle.setAlignment(HorizontalAlignment.CENTER);                   //表头内容水平居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);        //表头内容垂直居中
        titleStyle.setFont(getDefaultTitleFont(workbook));                     //设置该字体样式
        titleStyle.setWrapText(true);
        return titleStyle;
    }

    /*获取默认内容样式*/
    public static HSSFCellStyle getDefaultContentStyle(HSSFWorkbook workbook) {
        HSSFCellStyle contentStyle = workbook.createCellStyle();
        contentStyle.setBorderBottom(BorderStyle.THIN);                 //设置底部边线
        contentStyle.setBorderLeft(BorderStyle.THIN);                   //设置左部边线
        contentStyle.setBorderRight(BorderStyle.THIN);                  //设置右部边线
        contentStyle.setBorderTop(BorderStyle.THIN);                    //设置顶部边线
        contentStyle.setAlignment(HorizontalAlignment.CENTER);                   //表头内容水平居中
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);        //表头内容垂直居中
        contentStyle.setFont(getDefaultContentFont(workbook));                    //设置该字体样式
        contentStyle.setWrapText(true);
        return contentStyle;
    }

    /**
     * 提示用户下载excel
     *
     * @param response 响应对象
     * @param fileName excel文件名
     *                 create by sunlihuo @2020-12-12
     */
    public static void downloadExcel(HSSFWorkbook workbook, HttpServletResponse response, String fileName) {
        try (OutputStream outputStream = response.getOutputStream()){
            if (StringUtils.isNotBlank(fileName)) {
                fileName = new String(fileName.getBytes("gb2312"), "iso8859-1");                      //给文件名重新编码
            } else {
                fileName = "excel";
            }
            response.setContentType("text/html;charset=utf-8");                                  //设置响应编码
            response.setContentType("application/x-msdownload");                                 //设置为文件下载
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");   //设置响应头信息
            workbook.write(outputStream);                                                        //把工作薄写进流中
        } catch (IOException e) {
            log.error("提示用户下载excel错误", e);
        }
    }

    /**
     * 提示用户下载excel
     *
     * @param response 响应对象
     * @param fileName excel文件名
     *                 create by sunlihuo @2020-12-12
     */
    public void downloadExcel(HttpServletResponse response, String fileName) {
        //创建输出流
        try (OutputStream outputStream = response.getOutputStream()){
            if (StringUtils.isNotBlank(fileName)) {
                fileName = new String(fileName.getBytes("gb2312"), "iso8859-1");                 //给文件名重新编码
            } else {
                fileName = "excel";
            }
            response.setContentType("text/html;charset=utf-8");                                  //设置响应编码
            response.setContentType("application/x-msdownload");                                 //设置为文件下载
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");   //设置响应头信息
            workbook.write(outputStream);                                                        //把工作薄写进流中
        } catch (IOException e) {
            log.error("提示用户下载excel错误", e);
        }
    }

    /**
     * 提示用户下载excel
     *
     * @function 对ie浏览器和firefox进行了兼容，不会出现乱码问题浏
     * @author junqiang.qiu
     * @date 2016年12月8日
     */
    public void downloadExcel(HttpServletRequest request, HttpServletResponse response, String fileName) {
        String agent = request.getHeader("USER-AGENT").toLowerCase();
        String codedFileName;
        try (OutputStream outputStream = response.getOutputStream()){
            codedFileName = java.net.URLEncoder.encode(fileName, "UTF-8");                        //给文件名重新编码
            /*这里对火狐浏览器做了设置*/
            if (agent.contains("firefox")) {
                response.setCharacterEncoding("utf-8");
                response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xls");
            } else {
                /*其他浏览器*/
                response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");

            }
            response.setContentType("text/html;charset=utf-8");                                  //设置响应编码
            response.setContentType("application/x-msdownload");                                 //设置为文件下载
            workbook.write(outputStream);                                                        //把工作薄写进流中
        } catch (Exception e) {
            log.error("提示用户下载excel错误", e);
        }

    }

    /**
     * @function 创建Excel，在服务器端或者是本地
     * @author junqiang.qiu
     * @date 2017年1月12日
     */
    public void createExcel(String path, String fileName) {
        /*这里使用File.separator是Java定义的一个枚举，这样就可以跨平台，对应Windows和linux是不一样的*/
        try {
            String param = null;
            param = path + File.separator + fileName + ".xls";
            FileOutputStream fos = new FileOutputStream(param);
            workbook.write(fos);
            fos.close();
        } catch (Exception e) {
            log.info("创建excel失败" + e);
        }
    }

    /**
     * 添加标题行
     *
     * @param titles    标题集合
     * @param rowHeight 行高
     */
    public void addTitlesRow(Collection<String> titles, int rowHeight) {

        try {

            if (titles != null && rowHeight > 0) {

                int rowIndex = 0;
                HSSFSheet sheet = workbook.getSheet(sheetName);

                /*总是获取最后一页,第一次则创建sheet页*/
                if (sheet == null) {
                    sheet = workbook.createSheet(sheetName);
                } else {
                    sheet = workbook.getSheetAt(lastSheetIndex);
                    rowIndex = sheet.getLastRowNum() + 1;
                }

                //若当前sheet页超过最大条数65536,则再创建一个sheet页
                if (rowIndex > 65535) {
                    lastSheetIndex++;
                    sheet = workbook.createSheet(sheetName + lastSheetIndex);
                    rowIndex = 0;
                }

                sheet.setDefaultColumnWidth(defaultColumnWidth);

                /*创建标题行*/
                HSSFRow row = sheet.createRow(rowIndex);
                row.setHeightInPoints(rowHeight);
                Iterator<String> iterator = titles.iterator();
                int index = 0;
                while (iterator.hasNext()) {
                    String title = iterator.next();
                    HSSFCell cell = row.createCell(index);                        //给该行创建单元格
                    cell.setCellValue(title);                                    //给单元格放入标题
                    cell.setCellStyle(defaultTitleStyle);
                    index++;
                }
            }
        } catch (Exception e) {
            log.error("添加标题行发生错误==>", e);
        }

    }

    /**
     * 添加内容行
     *
     * @param titleKeyMap 标题和mapList中key的对应
     * @param mapList     内容集合
     * @param rowHeight   内容行行高
     */
    public void addContentRow(Map<String, String> titleKeyMap, List<Map<String, Object>> mapList, int rowHeight) {

        try {

            if (titleKeyMap != null && rowHeight > 0 && mapList != null) {

                int rowIndex = 0;

                HSSFSheet sheet = workbook.getSheet(sheetName);

                /*总是获取最后一页,第一次则创建sheet页*/
                if (sheet == null) {
                    sheet = workbook.createSheet(sheetName);
                    sheet.setDefaultColumnWidth(defaultColumnWidth);
                } else {
                    sheet = workbook.getSheetAt(lastSheetIndex);
                    rowIndex = sheet.getLastRowNum() + 1;
                }

                /*取出标题*/
                List<String> titles = new ArrayList<String>(titleKeyMap.keySet());

                /*根据数据的条数来创建表格行*/
                for (Map<String, Object> map : mapList) {

                    //若当前sheet页超过最大条数65536,则再创建一个sheet页
                    if (rowIndex > 65535) {
                        lastSheetIndex++;
                        sheet = workbook.createSheet(sheetName + lastSheetIndex);
                        sheet.setDefaultColumnWidth(defaultColumnWidth);
                        rowIndex = 0;
                    }

                    HSSFRow row = sheet.createRow(rowIndex++);
                    row.setHeightInPoints(rowHeight);
                    for (int k = 0; k < titleKeyMap.size(); k++) {
                        HSSFCell cell = row.createCell(k);
                        String key = titleKeyMap.get(titles.get(k));
                        String value = (map.get(key) != null) ? map.get(key).toString() : "";
                        cell.setCellValue(value);
                        cell.setCellStyle(defaultContentStyle);
                    }
                }
            }
        } catch (Exception e) {
            log.error("添加内容行发生错误==>", e);
        }

    }

    /**
     * 新增横向的标题-值的行
     *
     * @param titleValueRow
     */
    public void addTitleValueRow(TitleValueRow titleValueRow) {

        try {
            int rowIndex = 0;

            HSSFSheet sheet = workbook.getSheet(sheetName);

            /*总是获取最后一页,第一次则创建sheet页*/
            if (sheet == null) {
                sheet = workbook.createSheet(sheetName);
            } else {
                sheet = workbook.getSheetAt(lastSheetIndex);
                rowIndex = sheet.getLastRowNum() + 1;
            }

            //若当前sheet页超过最大条数65536,则再创建一个sheet页
            if (rowIndex > 65535) {
                lastSheetIndex++;
                sheet = workbook.createSheet(sheetName + lastSheetIndex);
                rowIndex = 0;
            }

            sheet.setDefaultColumnWidth(defaultColumnWidth);

            /*创建该行*/
            HSSFRow row = sheet.createRow(rowIndex);
            row.setHeightInPoints(titleValueRow.getRowHeight());

            List<TitleValue> titleValueList = titleValueRow.getTitleValueList();

            for (int i = 0; i < titleValueRow.getCellsSize(); i++) {
                row.createCell(i).setCellStyle(defaultContentStyle);
            }

            int curCellIndex = 0;
            for (TitleValue titleValue : titleValueList) {
                /*给单元格赋值*/
                HSSFCell cell = row.getCell(curCellIndex);
                cell.setCellValue(titleValue.getTitle());
                if (titleValue.getTitleCells() > 1) {
                    sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, curCellIndex, (curCellIndex + titleValue.getTitleCells() - 1)));
                }
                curCellIndex += titleValue.getTitleCells();
                cell.setCellStyle(defaultTitleStyle);

                if (titleValue.getValueCells() >= 1) {
                    cell = row.getCell(curCellIndex);
                    cell.setCellValue(titleValue.getValue());
                }
                if (titleValue.getValueCells() > 1) {
                    sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, curCellIndex, curCellIndex + titleValue.getValueCells() - 1));
                }
                curCellIndex += titleValue.getValueCells();
            }
        } catch (Exception e) {
            log.error("新增横向的标题-值的行==>", e);
        }
    }

    /**
     * 标题和值行的类
     */
    public static class TitleValueRow {

        /*标题和值的集合*/
        private List<TitleValue> titleValueList;

        /*该行所占用的总格子数*/
        private int cellsSize = 0;

        /*该行所占用的高度*/
        private float rowHeight = 20;

        public TitleValueRow() {
            this.titleValueList = new ArrayList<TitleValue>();
        }

        public TitleValueRow(List<TitleValue> titleValueList, int rowHeight) {
            if (titleValueList != null && titleValueList.size() > 0) {
                this.titleValueList = titleValueList;
                for (TitleValue titleValue : titleValueList) {
                    cellsSize += titleValue.getTitleCells() + titleValue.getValueCells();
                }
            } else {
                this.titleValueList = new ArrayList<TitleValue>();
            }
            if (rowHeight > 0) {
                this.rowHeight = rowHeight;
            }
        }

        public void addTitleValue(TitleValue titleValue) {
            titleValueList.add(titleValue);
            cellsSize++;
        }

        public float getRowHeight() {
            return rowHeight;
        }

        public void setRowHeight(float rowHeight) {
            this.rowHeight = rowHeight;
        }

        public List<TitleValue> getTitleValueList() {
            return titleValueList;
        }

        public void setTitleValueList(List<TitleValue> titleValueList) {
            this.titleValueList = titleValueList;
        }

        public int getCellsSize() {
            return cellsSize;
        }

        public void setCellsSize(int cellsSize) {
            this.cellsSize = cellsSize;
        }
    }

    /**
     * 横向的标题和行:
     * 格式为 ==> xxxx标题 : 值
     */
    public static class TitleValue {

        /*标题*/
        private String title;

        /*值*/
        private String value;

        /*标题所占用的格子数*/
        private int titleCells = 1;

        /*内容所占用的格子数*/
        private int valueCells = 1;

        /*构造方法*/
        public TitleValue() {

        }

        public TitleValue(String title, String value) {
            this.title = title;
            this.value = value;
        }

        public TitleValue(String title, String value, int titleCells, int valueCells) {
            this.title = title;
            this.value = value;
            this.titleCells = titleCells;
            this.valueCells = valueCells;

        }

        public TitleValue(String title, String value, int titleCells) {
            this.title = title;
            this.value = value;
            this.titleCells = titleCells;
        }


        public String getTitle() {
            return title;
        }

        public void setTitle(String title) {
            this.title = title;
        }

        public String getValue() {
            return value;
        }

        public void setValue(String value) {
            this.value = value;
        }

        public int getTitleCells() {
            return titleCells;
        }

        public void setTitleCells(int titleCells) {
            this.titleCells = titleCells;
        }

        public int getValueCells() {
            return valueCells;
        }

        public void setValueCells(int valueCells) {
            this.valueCells = valueCells;
        }
    }

    /**
     * @param fileName 导出文件名
     * @param msg      错误信息
     * @function 导出一个具有错误信息的excel
     * @author sunlihuo
     * @date 2017年3月10日
     */
    public static void exportErrorMsg(HttpServletResponse response, String fileName, String msg) {
        ExcelExportUtil excelExportUtil = new ExcelExportUtil("错误信息");
        TitleValue titleValue = new TitleValue(msg, "", 10, 0);
        TitleValueRow row = new TitleValueRow(Arrays.asList(titleValue), 50);
        excelExportUtil.addTitleValueRow(row);
        excelExportUtil.downloadExcel(response, fileName);
    }

    /**
     * @param response http响应对象
     * @function 传入一个http响应对象, 模拟导出一个excel
     * @author sunlihuo
     * @date 2015年8月5日
     * update at 2017年4月5日 by sunlihuo
     */
    public static void testExcelPort(HttpServletResponse response) {

        //创建一个实例,可以根据需要传入不同的参数,默认sheetName为"sheet"
        ExcelExportUtil excelExportUtil = new ExcelExportUtil();

        Map<String, String> titleKeyMap = new LinkedHashMap<String, String>();
        titleKeyMap.put("用户名", "userName");
        titleKeyMap.put("密码", "password");
        titleKeyMap.put("性别", "sex");
        titleKeyMap.put("年龄", "age");
        titleKeyMap.put("爱好", "hobby");
        titleKeyMap.put("公司", "company");

        //模拟10万条数据(本工具类支持任意数量和任意excel版本的导出,超过65536条将会分页)
        List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
        for (int i = 0; i < 100000; i++) {
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("userName", "小明" + i);
            map.put("age", i);
            map.put("company", "蜀国" + i);
            map.put("hobby", "做作业");
            map.put("password", "1232323" + i);
            map.put("sex", "男");
            mapList.add(map);
        }

        /*添加横向的行*/
        List<TitleValue> titleValues = new ArrayList<TitleValue>();
        titleValues.add(new TitleValue("测试excel工具类", "", 6, 0));
        TitleValueRow row = new TitleValueRow(titleValues, 50);
        excelExportUtil.addTitleValueRow(row);

        /*添加标题行*/
        excelExportUtil.addTitlesRow(titleKeyMap.keySet(), 30);

        /*添加内容行*/
        excelExportUtil.addContentRow(titleKeyMap, mapList, 20);

        /*添加横向的行*/
        List<TitleValue> titleValues1 = new ArrayList<TitleValue>();
        titleValues1.add(new TitleValue("末尾也可以添加", "哈哈哈哈", 3, 3));
        TitleValueRow row2 = new TitleValueRow(titleValues1, 50);
        excelExportUtil.addTitleValueRow(row2);

        /*下载*/
        excelExportUtil.downloadExcel(response, "用户信息表");

    }

    public static void ExcelPort(HttpServletResponse response, List<String> keys, List<String> titles, String fileName, List<Map<String, Object>> dataList) {

        //创建一个实例,可以根据需要传入不同的参数,默认sheetName为"sheet"
        ExcelExportUtil excelExportUtil = new ExcelExportUtil();

        Map<String, String> titleKeyMap = new LinkedHashMap<String, String>();

        //一般keys和titles大小一定要相等，或者keys长度>titles长度
        for (int i = 0; i < titles.size(); i++) {
            titleKeyMap.put(titles.get(i), keys.get(i));
        }



        /*添加横向的行*/
        List<TitleValue> titleValues = new ArrayList<TitleValue>();
        titleValues.add(new TitleValue(fileName, "", titles.size(), 0));
        TitleValueRow row = new TitleValueRow(titleValues, 30);
        excelExportUtil.addTitleValueRow(row);

        /*添加标题行*/
        excelExportUtil.addTitlesRow(titleKeyMap.keySet(), 30);

        /*添加内容行*/
        excelExportUtil.addContentRow(titleKeyMap, dataList, 20);

        /*添加横向的行*/
        List<TitleValue> titleValues1 = new ArrayList<TitleValue>();
//        titleValues1.add(new TitleValue("末尾也可以添加", "哈哈哈哈", 3, 3));
        TitleValueRow row2 = new TitleValueRow(titleValues1, 50);
        excelExportUtil.addTitleValueRow(row2);

        /*下载*/
        excelExportUtil.downloadExcel(response, fileName);

    }

    public static List<Map<String, Object>> list2Map(List list) {
        if (CollectionUtils.isEmpty(list)) {
            return new ArrayList<>();
        }
        List<Map<String, Object>> resultList = new ArrayList<>();
        list.stream().forEach(o -> {
            resultList.add(object2Map(o));
        });
        return resultList;
    }

    public static Map<String, Object> object2Map(Object o) {
        Map<String, Object> parameters = new HashMap<>();
        try {
            Field[] fields = o.getClass().getDeclaredFields();
            for (Field field : fields) {
                parameters.put(field.getName(), getFieldValueByName(field.getName(), o));
            }
        } catch (Exception e) {
            log.debug("对象toMap错误", e);
        }
        return parameters;
    }

    private static Object getFieldValueByName(String fieldName, Object o) {
        try {
            String firstLetter = fieldName.substring(0, 1).toUpperCase();
            String getter = "get" + firstLetter + fieldName.substring(1);
            Method method = o.getClass().getMethod(getter, new Class[]{});
            Object value = method.invoke(o, new Object[]{});
            return value;
        } catch (Exception e) {
            return null;
        }
    }


}