Java execl导出工具

Java execl导出

package com.ruoyi.common.utils.poi;

import com.ruoyi.common.annotation.Excel;
import com.ruoyi.common.annotation.Excels;
import com.ruoyi.common.annotation.Excel.Type;
import com.ruoyi.common.core.text.Convert;
import com.ruoyi.common.utils.DateUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.*;
import java.util.stream.Collectors;

public class MyExeclUtil<T> {

    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);


    /**
     * 工作薄对象
     */
    private HSSFWorkbook wb;

    /**
     * 工作表对象
     */
    private HSSFSheet sheet;

    /**
     * 工作表名称
     */
    private String sheetName;

    /**
     * 最大高度
     */
    private short maxHeight;

    /**
     * 样式列表
     */
    private Map<String, CellStyle> styles;

    /**
     * 导入导出数据列表
     */
    private List<T> list;

    /**
     * 注解列表
     */
    private List<Object[]> fields;

    /**
     * 导出类型（EXPORT:导出数据；IMPORT：导入模板）
     */
    private Excel.Type type;

    /**
     * 样式
     */
    private HSSFCellStyle colStyle;

    /**
     * 用于多行标题
     */
    private Map<String, Object> map;

    private List<String> indexs;
    /**
     * 实体对象
     */
    public Class<T> clazz;

    public MyExeclUtil(Class<T> clazz) {
        this.clazz = clazz;
    }

    /**
     * 初始化方法
     *
     * @param list
     * @param sheetName
     * @param type
     */
    public void init(List<T> list, String sheetName, Type type) {
        if (list == null) {
            list = new ArrayList<T>();
        }
        this.list = list;
        this.sheetName = sheetName;
        this.type = type;
        // 创建HSSFWorkbook对象
        this.wb = new HSSFWorkbook();
        // 创建HSSFSheet对象
        this.sheet = wb.createSheet(sheetName);
        // 初始化样式
        this.colStyle = createCellStyle(wb, (short) 10);
        // 得到所有定义字段
        createExcelField();
    }


    /**
     * 导出execl--正常格式的execl
     *
     * @param list
     * @param request
     * @param fileName
     * @param response
     * @throws IOException
     */
    public void exportExcel(List<T> list, String fileName, HttpServletRequest request, HttpServletResponse response) throws IOException, IllegalAccessException {
        // 1.初始化
        this.init(list, fileName, Type.EXPORT);
        // 2.创建标题---只有一层的标题
        int rowNum = createTtile();
        // 3.填充内容---正常的内容
        createContent(rowNum);
        // 4.下载
        downloadExecl(request, fileName, response);
    }

    /**
     * 导出execl--2行标题
     *
     * @param list
     * @param request
     * @param fileName
     * @param response
     * @throws IOException
     */
    public void exportExcelMultiRow(List<T> list, String fileName, List<String> indexs, HttpServletRequest request, HttpServletResponse response) throws IOException, IllegalAccessException {
        // 1.初始化
        this.init(list, fileName, Type.EXPORT);
        // 2.初始化map
//        this.map = map;
        this.indexs = indexs;
        // 3.创建标题---多行标题（2行）
        int rowNum = createTitleMultiRow();
        // 5.填充内容---正常的内容
        createContent(rowNum);
        // 6.下载
        downloadExecl(request, fileName, response);
    }

    /**
     * 只有一行的标题
     */
    public int createTtile() {
        // 行
        HSSFRow row = sheet.createRow(0);
        // 列
        int column = 0;
        for (Object[] os : fields) {
            Excel excel = (Excel) os[1];
            this.createCell(excel, row, column++);
        }
        return 1;
    }

    /**
     * 多行的标题（2行）
     *
     * @return
     */
    public int createTitleMultiRow() {
        for(int i = 0; i < 2; i++) {
            // 行
            HSSFRow row = sheet.createRow(i);
            // 列
            int column = 0;
            for(int j = 0; j < fields.size(); j++){
                Object[] os = fields.get(j);
                Excel excel = (Excel) os[1];
                // 值
                String value = indexs.get(j);
                if(value.contains(":")) { // 和并第一行
                     // 计算所要合并的列数
                    String[] split = value.split(":");
                    // 第一行数据
                    String startStr = split[0];
                    // 第二行数据
                    String endStr = split[1];
                    int col = countColnum(startStr);
                    if(i < 1) {
                        if(j > 0) {
                            String value1 = indexs.get(j - 1);
                            if(value.contains(":")) {
                                // 计算所要合并的列数
                                String[] split1 = value1.split(":");
                                String startStr1 = split1[0];
                                if(!startStr.equals(startStr1)) {
                                    // 合并行
                                    sheet.addMergedRegion(new CellRangeAddress(0, 0, column, column + col - 1));
                                    //
                                    for(int k = column; k < column + col; k++) {
                                        // 创建列
                                        Cell cell = row.createCell(k);
                                        sheet.setColumnWidth(k, 4000);
                                        // 写入列信息
                                        cell.setCellValue(startStr);
                                        cell.setCellStyle(colStyle);
                                    }
                                    column = column + col;
                                }
                            }
                        }
                    }else {
                        // 创建列
                        Cell cell = row.createCell(column);
                        sheet.setColumnWidth(column, 4000);
                        // 写入列信息
                        cell.setCellValue(endStr);
                        cell.setCellStyle(colStyle);
                        column++;
                    }
                }else { // 合并第一行和第二行
                    if(i < 1) {
                        // 合并行
                        sheet.addMergedRegion(new CellRangeAddress(0, 1, column, column));
                    }
                    this.createCell(excel, row, column++);
                }

            }
        }
        return 2;
    }

    public int countColnum(String startStr) {
        startStr = startStr+":";
        int col = 0;
        for(String str : indexs) {
            if(str.contains(startStr)) {
                col++;
            }
        }
        return col;
    }

    /**
     * 生产内容
     */
    public void createContent(int rowNum) throws IllegalAccessException {
        for (int i = 0; i < list.size(); i++) {
            // 行
            HSSFRow row = sheet.createRow(rowNum);
            rowNum++;
            // 得到导出对象.
            T vo = (T) list.get(i);
            // 列
            int colnum1 = 0;
            for (Object[] os : fields) {
                Field field = (Field) os[0];
                // 设置实体类私有属性可访问
                field.setAccessible(true);
                // 创建单元格
                HSSFCell cell = row.createCell(colnum1++);
                // 设置单元格内容
                Object o = field.get(vo);
                String value = "";
                if (field.getName().contains("Time")) {
                    DateUtils.parseDateToStr(DateUtils.YYYY_MM_DD_HH_MM_SS, (Date) o);
                } else {
                    value = Convert.toStr(o);
                }
                cell.setCellValue(value);
                // 加载单元格样式
                cell.setCellStyle(colStyle);
            }
        }
    }

    /**
     * 创建单元格
     */
    public Cell createCell(Excel attr, Row row, int column) {
        // 创建列
        Cell cell = row.createCell(column);
        // 写入列信息
        cell.setCellValue(attr.name());
        setDataValidation(attr, row, column);
        cell.setCellStyle(colStyle);
        return cell;
    }

    /**
     * 创建表格样式
     */
    public void setDataValidation(Excel attr, Row row, int column) {
        if (attr.name().indexOf("注：") >= 0) {
            sheet.setColumnWidth(column, 6000);
        } else {
            // 设置列宽
            sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
        }
    }

    /**
     * 得到所有定义字段
     */
    private void createExcelField() {
        this.fields = new ArrayList<Object[]>();
        List<Field> tempFields = new ArrayList<>();
        tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
        tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
        for (Field field : tempFields) {
            // 单注解
            if (field.isAnnotationPresent(Excel.class)) {
                putToField(field, field.getAnnotation(Excel.class));
            }

            // 多注解
            if (field.isAnnotationPresent(Excels.class)) {
                Excels attrs = field.getAnnotation(Excels.class);
                Excel[] excels = attrs.value();
                for (Excel excel : excels) {
                    putToField(field, excel);
                }
            }
        }
        this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort())).collect(Collectors.toList());
        this.maxHeight = getRowHeight();
    }

    /**
     * 放到字段集合中
     */
    private void putToField(Field field, Excel attr) {
        if (attr != null && (attr.type() == Excel.Type.ALL || attr.type() == type)) {
            this.fields.add(new Object[]{field, attr});
        }
    }

    /**
     * 根据注解获取最大行高
     */
    public short getRowHeight() {
        double maxHeight = 0;
        for (Object[] os : this.fields) {
            Excel excel = (Excel) os[1];
            maxHeight = maxHeight > excel.height() ? maxHeight : excel.height();
        }
        return (short) (maxHeight * 20);
    }

    /**
     * 表格样式
     *
     * @param workbook
     * @param fontsize
     * @return
     */
    private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize) {
        // TODO Auto-generated method stub
        HSSFCellStyle style = workbook.createCellStyle();
        // 水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        // 创建字体
        HSSFFont font = workbook.createFont();
        // 是否加粗字体
//		font.setBold(true);
        font.setFontHeightInPoints(fontsize);
        // 加载字体
        style.setFont(font);
        // 设置边框样式
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        // 文字自动换行
        style.setWrapText(true);
        return style;
    }

    /**
     * 下载execl
     *
     * @param request
     * @param fileName
     * @param response
     * @throws IOException
     */
    public void downloadExecl(HttpServletRequest request, String fileName, HttpServletResponse response) throws IOException {
        //输出Excel文件
        OutputStream output = response.getOutputStream();
        response.reset();

        //设置响应头，
        String agent = request.getHeader("USER-AGENT").toLowerCase();
        response.setContentType("application/vnd.ms-excel");
        String 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");
        }
        wb.write(output);
        output.close();
    }
}