springboot项目下载和导入excel模板

下载模板

@ApiOperation(value = "下载excel导入模板")
@GetMapping("/selfCollectionDtl/collectionDtlExcelTmp")
public void collectionDtlExcelTmp(HttpServletResponse response) throws Exception {
    try {
        String message = "template/collectionDtl.xls";
        String fileName = "收缴导入模板";
        InputStream resourceAsStream = Thread.currentThread().getContextClassLoader().getResourceAsStream(message);
        HSSFWorkbook wb = new HSSFWorkbook(resourceAsStream);
        //返回数据流
        POIExcelUtils.buildExcelDocument(fileName, wb, response);
    } catch (Exception e) {
        throw new Exception("收缴导入模板下载失败，请联系管理员。");
    }
}

导入模板

controller层和service层

@ApiOperation(value = "数据导入")
@PostMapping(value = "/selfCollectionDtl/importExcel",consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
public ResponseData importExcel(@RequestParam("file") MultipartFile file, String orgCode) throws Exception {
    if(StringUtils.isBlank(orgCode)){
        throw new Exception("所属组织编码不能为空");
    }
    return objSelfCollectionDtlService.importExcel(file,orgCode);
}

service层

@Override
public ResponseData importExcel(MultipartFile file, String orgCode) throws Exception {
    InputStream inputStream = file.getInputStream();
    ImportParams params = new ImportParams();
    params.setTitleRows(1);
    //获取输入对象
    List<NgPayingUnitVo> list = ExcelImportUtil.importExcel(inputStream, NgPayingUnitVo.class, params);
    if (CollectionUtils.isEmpty(list) ) {
        throw new ServiceException(1,"导入数据为空");
    }
    //数据处理
    return null;
}

读取excel的工具类ExcelImportUtil类

package cn.afterturn.easypoi.excel;

import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.imports.ExcelImportService;
import cn.afterturn.easypoi.excel.imports.sax.SaxReadExcel;
import cn.afterturn.easypoi.exception.excel.ExcelImportException;
import cn.afterturn.easypoi.handler.inter.IReadHandler;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.List;
import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ExcelImportUtil {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelImportUtil.class);

    private ExcelImportUtil() {
    }

    public static <T> List<T> importExcel(File file, Class<?> pojoClass, ImportParams params) {
        FileInputStream in = null;

        List var4;
        try {
            in = new FileInputStream(file);
            var4 = (new ExcelImportService()).importExcelByIs(in, pojoClass, params, false).getList();
        } catch (ExcelImportException var9) {
            throw new ExcelImportException(var9.getType(), var9);
        } catch (Exception var10) {
            LOGGER.error(var10.getMessage(), var10);
            throw new ExcelImportException(var10.getMessage(), var10);
        } finally {
            IOUtils.closeQuietly(in);
        }

        return var4;
    }

    public static <T> List<T> importExcel(InputStream inputstream, Class<?> pojoClass, ImportParams params) throws Exception {
        return (new ExcelImportService()).importExcelByIs(inputstream, pojoClass, params, false).getList();
    }

    public static <T> ExcelImportResult<T> importExcelMore(InputStream inputstream, Class<?> pojoClass, ImportParams params) throws Exception {
        return (new ExcelImportService()).importExcelByIs(inputstream, pojoClass, params, true);
    }

    public static <T> ExcelImportResult<T> importExcelMore(File file, Class<?> pojoClass, ImportParams params) {
        FileInputStream in = null;

        ExcelImportResult var4;
        try {
            in = new FileInputStream(file);
            var4 = (new ExcelImportService()).importExcelByIs(in, pojoClass, params, true);
        } catch (ExcelImportException var9) {
            throw new ExcelImportException(var9.getType(), var9);
        } catch (Exception var10) {
            LOGGER.error(var10.getMessage(), var10);
            throw new ExcelImportException(var10.getMessage(), var10);
        } finally {
            IOUtils.closeQuietly(in);
        }

        return var4;
    }

    public static void importExcelBySax(InputStream inputstream, Class<?> pojoClass, ImportParams params, IReadHandler handler) {
        (new SaxReadExcel()).readExcel(inputstream, pojoClass, params, handler);
    }
}