easyUtils

easyUtils

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Arrays;
import java.util.List;
import java.util.NoSuchElementException;
import java.util.Objects;

/**
 * easyUtils
 */
@Slf4j
public class EasyPoiUtils {

    /**
     * 功能描述：导入Excel,并封装成实体类
     *
     * @param file       上传的文件
     * @param titleRows  表标题的行数
     * @param headerRows 表头行数
     * @param pojoClass  Excel实体类
     * @param needVerify 是否需要校验
     */
    public static <T> ExcelImportResult<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerify, Class<T> pojoClass) {
        if (file == null) {
            log.info("导入文件为空");
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        if (needVerify) {
            params.setNeedVerify(needVerify);
        }
        try {
            return ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new RuntimeException("excel文件不能为空");
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
    }

    /**
     * 导出excel
     */
    public static void downloadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws Exception {
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));

        OutputStream output = response.getOutputStream();
        BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
        workbook.write(bufferedOutPut);
        bufferedOutPut.flush();
        bufferedOutPut.close();
        output.close();
    }

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws Exception {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws Exception {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) {
            downloadExcel(fileName, response, workbook);
        }
    }

    /**
     * 功能描述：导入Excel,并封装成实体类
     *
     * @param file       上传的文件
     * @param titleRows  表标题的行数
     * @param headerRows 表头行数
     * @param needVerify Excel实体类
     * @param pojoClass  是否需要校验
     * @param handler    前置处理器
     * @param <T>        实体类
     * @return
     */
    public static <T> ExcelImportResult<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerify, Class<T> pojoClass, IExcelVerifyHandler handler) {
        if (file == null) {
            log.info("导入文件为空");
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        if (needVerify) {
            params.setNeedVerify(needVerify);
            params.setVerifyHandler(handler);
        }
        try {
            return ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new RuntimeException("excel文件不能为空");
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
    }

    /**
     * 导出设置下拉框
     * firstRow 开始行号(下标0开始)
     * lastRow  结束行号，最大65535
     * firstCol 区域中第一个单元格的列号 (下标0开始)
     * lastCol 区域中最后一个单元格的列号
     * dataArray 下拉内容
     */
    public static void selectList(Workbook workbook, int firstRow, int lastRow, int firstCol, int lastCol, String[] dataArray) {
        Sheet sheet = workbook.getSheetAt(0);
        //生成下拉列表
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        //生成下拉框内容
        DataValidationHelper dvHelper = sheet.getDataValidationHelper();
        DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(dataArray);
        DataValidation validation = dvHelper.createValidation(dvConstraint, cellRangeAddressList);
        //设置错误信息提示
        validation.setShowErrorBox(true);
        //对sheet页生效
        sheet.addValidationData(validation);
    }
    /**
     * @param workbook workbook
     * @param data     下拉项数据
     * @param firstRow 需要添加下拉项的起始行下标, 默认添加到  firstRow-500行
     * @param column   需要添加下拉项的列下标
     * @param valid    是否禁止修改下拉项数据
     */
    public static void setValid(Workbook workbook, String[] data, Integer firstRow, Integer column, Boolean valid) {
        Sheet dataSheet = workbook.getSheetAt(0);
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, 500, column, column);
        DataValidationHelper helper = dataSheet.getDataValidationHelper();
        DataValidationConstraint constraint = buildConstraint(workbook, data);
        // 关联上面的Name的名称
        DataValidation validation = helper.createValidation(constraint, addressList);
        // 是否显示下拉框箭头
        validation.setSuppressDropDownArrow(true);
        if (valid) {
            // 单元格值和下拉项不符合时提示的值
            validation.setShowErrorBox(true);
            validation.createErrorBox("错误提示", "非下拉项中的字典值无法导入,请勿修改");
            validation.setEmptyCellAllowed(true);
            /**
             * 	DataValidation.ErrorStyle.STOP 提示框为红色,(重试,取消,帮助)选择重试继续编辑,选择取消恢复为选择的下拉项值
             * 	DataValidation.ErrorStyle.WARNING 提示框为黄色,(是,否,取消,帮助)选择是可保存非下拉框值,选择否继续编辑,选择取消恢复为选择的下拉项值
             * 	DataValidation.ErrorStyle.INFO 提示框为蓝色,(确定,取消,帮助)选择确定可保存非下拉框值
             */
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        }
        // 选择单元格时候显示的提示语
        validation.setShowPromptBox(false);
//        validation.createPromptBox(null,"请选择下拉项中的字典值");
        // 选择下拉项后是否允许清除单元格
        validation.setEmptyCellAllowed(false);
        dataSheet.addValidationData(validation);
    }

    /**
     * 构建 DataValidationConstraint
     * 数据总字符小于255时用官方api构建下拉项
     * 大于255时使用隐藏sheet存储下拉项数据
     *
     * @param workbook workbook
     * @param data     下拉项数据
     * @return
     */
    public static DataValidationConstraint buildConstraint(Workbook workbook, String[] data) {
        Sheet dataSheet = workbook.getSheetAt(0);
        DataValidationHelper dateSheetHelper = dataSheet.getDataValidationHelper();
        Arrays.stream(data).peek(po-> System.out.println(po.length()));
        int sum = Arrays.stream(data).mapToInt(String::length).sum();
        if (sum < 255) {
            // 下拉项总字符 < 255, 使用原生方式添加下拉框
            return dataSheet.getDataValidationHelper().createExplicitListConstraint(data);
        }
        // 通过隐藏sheet方式保存下拉项数据
        int total = workbook.getNumberOfSheets();
        Sheet validSheet;
        if (total < 2) {
            // 默认导出的Excel只有一个sheet,小于2表示未创建隐藏sheet,此时手动创建隐藏sheet
            validSheet = workbook.createSheet("参考值");
            workbook.setSheetHidden(1, true);
        } else {
            // 隐藏sheet已创建,直接获取
            validSheet = workbook.getSheetAt(1);
        }
        // 本次在哪一列写入下拉项数据
        Integer validIndex = 0;
        boolean notFind = true;
        for (int i = 0, length = data.length; i < length; i++) {
            Row row = validSheet.getRow(i);
            if (Objects.isNull(row)){
                row = validSheet.createRow(i);
            }
            if (notFind){
                notFind = false;
                while (Objects.nonNull(row.getCell(validIndex))){
                    validIndex++;
                }
            }
            row.createCell(validIndex).setCellValue(data[i]);
        }
        String validSheetName = validSheet.getSheetName();
        String nameName = validSheetName + validIndex;
        Name name = workbook.createName();
        // 设置Name的名称
        name.setNameName(nameName);
        //4 $A$1:$A$N代表 以A列1行开始获取N行下拉数据
        String reg = "!$%s$1:$%s$%d";
        char a = 'A';
        char c = (char) (a + validIndex);
        String format = String.format(reg, c, c, data.length);
        String formatName = validSheetName + format;
        // 设置引用哪个sheet的哪一列多少行的数据
        name.setRefersToFormula(formatName);
        // helper 由 dataSheet 创建, nameName 中包含了隐藏sheet的引用的下拉项信息, 以此关联二者
        return dateSheetHelper.createFormulaListConstraint(nameName);
    }
}