easypoi导入

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>${easypoi-web.version}</version>
        </dependency>

    <easypoi-web.version>3.2.0</easypoi-web.version>

文件转 Base64、文件转字符串

package com.yirui.supervisor.util;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.util.Base64;

public class FileBase64 {


    public static void main(String[] args) {
        String path="D:\a.pdf";
    //    decryptByBase64(s,path);

    }
    public static Boolean decryptByBase64(String base64, String filePath) {
        if (base64 == null && filePath == null) {
            return false;
        }
        try {
            Files.write(Paths.get(filePath), Base64.getMimeDecoder().decode(base64), StandardOpenOption.CREATE);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return true;
    }

    public  static String encryptToBase64(String filePath) {
        if (filePath == null) {
            return null;
        }
        try {
            byte[] b = Files.readAllBytes(Paths.get(filePath));
            return Base64.getMimeEncoder().encodeToString(b);
        } catch (IOException e) {
            e.printStackTrace();
        }

        return null;
    }

}

文件转base64第二种

       import org.apache.commons.codec.binary.Base64;
import org.apache.commons.io.FileUtils;

String filebyteString = Base64.encodeBase64String(FileUtils.readFileToByteArray(file));

导入获取数据的工具类

package com.yirui.supervisor.util;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;


import java.io.File;

import java.io.IOException;
import java.util.List;

public class ExlsxFileUtil {


    public static  <T> List<T> getData(String fileBase64, Class<T> clazz) throws IOException {
        String filePath = System.getProperty("user.dir") +".xlsx";
        Boolean aBoolean = FileBase64.decryptByBase64(fileBase64, filePath);
        if(aBoolean){
            ImportParams params = new ImportParams();
            params.setHeadRows(1);
            File file = new File(filePath);
            List<T> list = ExcelImportUtil.importExcel(file,clazz, params);
            deleteFile(filePath);
            return list;

        }else{
            return null;
        }

    }

    public static void deleteFile(String filePath){
        File file = new File(filePath);
        if(file.exists()){
            file.delete();
        }
    }

}

// replace格式为 "替换前的值_替换后的值"
@Excel(name = "性别*", replace = {"男_0", "女_1"})
private Integer gender;

easypoi导出

    @ApiOperation(value = "项目_导入模板下载", notes = "项目_导入模板下载")
    @PostMapping("/mould")
    public void mould(HttpServletResponse response) throws Exception {
        ExportParams params = new ExportParams();
        Workbook workbook = ExcelExportUtil.exportExcel(params, ProjectImport.class, new ArrayList<>());
        OutputStream out = response.getOutputStream();
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment;filename="+new String("项目_导入模板.xlsx".getBytes("utf-8"),"ISO-8859-1"));
        workbook.write(out);
        out.close();
        workbook.close();
    }

生成下拉

        Sheet sheet = workbook.getSheetAt(0);
        // 只对(0，0)单元格有效
        CellRangeAddressList regions = new CellRangeAddressList(1, 1000, 3, 3);
        // 生成下拉框内容
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(new String[] {"建设单位", "监理单位", "施工单位(总包)", "施工单位(分包)", "设计单位", "勘察单位", "检测单位", "监测单位", "商混供应单位"});
        // 绑定下拉框和作用区域
        HSSFDataValidation data_validation = new HSSFDataValidation(regions,constraint);
        // 对sheet页生效
        sheet.addValidationData(data_validation);

处理不能超过255的异常

    public static HSSFDataValidation getDataValidationList4Col( int firstRow,
                                                                int endRow,int firstCol,
                                                                int endCol, List<String> colName,
                                                                Workbook wbCreat)
    {
        String[] dataArray = colName.toArray(new String[0]);
        Sheet hidden = wbCreat.createSheet("hidden");
        Cell cell = null;
        for (int i = 0, length = dataArray.length; i < length; i++)
        {
            String name = dataArray[i];
            Row row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
        }

        Name namedCell = wbCreat.createName();
        namedCell.setNameName("hidden");
        namedCell.setRefersToFormula("hidden!$A$1:$A$" + dataArray.length);
        //加载数据,将名称为hidden的
        DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");

        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,
            endCol);
        HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);

        //将第二个sheet设置为隐藏
        wbCreat.setSheetHidden(1, true);

        return validation;
    }