Excel导入导出简易工具类

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.List;

/**
 * @author
 * @version 1.0
 * @date 2021/8/8 11:46
 * excel工具类
 */
public class ExcelUtils {


    /**
     * 导出数据到excel
     * @param fileName 文件名
     * @param sheetName sheet名
     * @param data 数据集
     * @param clas 数据的类名
     * @param response
     * @throws IOException
     */
    public static void download(String fileName, String sheetName, List data, Class clas, HttpServletResponse response) throws IOException {
        ServletOutputStream out = response.getOutputStream();

        //通知浏览器以附件的形式下载处理，设置返回头要注意文件名有中文
        response.setHeader("Content-disposition", "attachment;filename=" + new String( fileName.getBytes("UTF-8"), "ISO8859-1" ) + ".xlsx");
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");

        EasyExcel.write(out, clas)
                .registerWriteHandler(getHorizontalCellStyleStrategy())
                .sheet(sheetName)
                .doWrite(data);

    }


    /**
     * 设置导出的头部策略
     */
    private static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置头不自动换行
        headWriteCellStyle.setWrapped(false);
        // 背景色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        headWriteFont.setFontName("Arial");
        headWriteFont.setBold(false);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontName("Arial");
        contentWriteFont.setFontHeightInPoints((short)12);
        // 字体样式
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle){};

        return horizontalCellStyleStrategy;
    }


    @Slf4j
    public static class CustomSheetWriteHandler implements SheetWriteHandler {
        //列宽集合
        private List<Integer> columnWidths;
        //构造
        public CustomSheetWriteHandler(List<Integer> columnWidths) {
            this.columnWidths = columnWidths;
        }


        @Override
        public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

        }

        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
            log.info("第{}个Sheet写入成功。", writeSheetHolder.getSheetNo());

            // 区间设置 第一列第一行和第二行的数据。由于第一行是头，所以第一、二行的数据实际上是第二三行
       /* CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 2, 0, 0);
        DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[] {"测试1", "测试2"});
        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
        writeSheetHolder.getSheet().addValidationData(dataValidation);*/
            log.info("普通策略设置setColumnWidth开始~");
            if(CollectionUtils.isNotEmpty(columnWidths)){
                for (int i = 0; i < columnWidths.size(); i++) {
                    writeSheetHolder.getSheet().setColumnWidth(i, columnWidths.get(i));
                }
            }
            log.info("普通策略设置setColumnWidth结束~");
        }
    }


    /**
     * 动态返回cell的值
     */
    public static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        String value;
        switch (cell.getCellTypeEnum()) {
            case FORMULA:
                // 公式
                //公式里面可能是数字/boolean/字符串的情况，需要二次辨别，以免取出空值
                FormulaEvaluator evaluator=cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
                CellValue evaluate = evaluator.evaluate(cell);
                CellType cellTypeEnum = evaluate.getCellTypeEnum();
                if (cellTypeEnum.equals(CellType.NUMERIC)) {
                    value = String.valueOf(evaluate.getNumberValue());
                } else if (cellTypeEnum.equals(CellType.BOOLEAN)) {
                    value = cell.getBooleanCellValue() + "";
                }else {
                    value = evaluate.getStringValue();
                }
                break;
            case NUMERIC:
                // 数字
                //如果为时间格式的内容
                if (DateUtil.isCellDateFormatted(cell)) {
                    //注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                    break;
                } else {
                    value = new DecimalFormat("#.##########").format(cell.getNumericCellValue());
                }
                break;
            case STRING:
                // 字符串
                value = cell.getStringCellValue().trim();
                break;
            case BOOLEAN:
                // Boolean
                value = cell.getBooleanCellValue() + "";
                break;
            case BLANK:
                // 空值
                value = "";
                break;
            case ERROR:
                // 故障
                value = "";
                break;
            default:
                value = "";
                break;
        }
        return value;

    }

}