EasyPoi实现excel多sheet导入

不念不忘少年蓝@ 2021-09-25 10:46 1220阅读 0赞

# EXCEL #

[导入模板][Link 1]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1cGVybG92ZXJf_size_16_color_FFFFFF_t_70_pic_center]

# MAVEN #

<dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
        <version>4.1.0</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-web</artifactId>
        <version>4.1.0</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-annotation</artifactId>
        <version>4.1.0</version>
    </dependency>

# Controller #

package io.btm.modules.importexcel;
    
    import cn.afterturn.easypoi.excel.ExcelImportUtil;
    import cn.afterturn.easypoi.excel.entity.ImportParams;
    import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
    import io.btm.common.utils.R;
    import io.swagger.annotations.ApiOperation;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.web.bind.annotation.PostMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    import org.springframework.web.multipart.MultipartFile;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    
    @RestController
    @RequestMapping("importExcel")
    public class ExcelController {
    
        /**
         * 导入
         */
        @PostMapping("import")
        @ApiOperation(value = "导入")
        public R ordergeneratexml(MultipartFile file) throws Exception {
            //根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页
            Workbook hssfWorkbook = getWorkBook(file);
            //获取sheet数量
            int sheetNum = hssfWorkbook.getNumberOfSheets();
            ImportParams params = new ImportParams();
            //表头在第几行
            params.setTitleRows(3);
            //是否需要通过key-value导入方法，获取特定字段
            params.setReadSingleCell(true);
            //判断一个cell是key的规则,可以自定义,默认就是 “:”
            params.setKeyMark("：");
            List<ExcelVo> listAll = new ArrayList<>();
            for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
                //第几个sheet页
                params.setStartSheetIndex(sheetIndex);
                //获取表头下的数据
                ExcelImportResult<ExcelVo> result = ExcelImportUtil.importExcelMore(file.getInputStream(), ExcelVo.class, params);
                List<ExcelVo> list = result.getList();
                //获取特定字段的map
                Map<String, Object> map = result.getMap();
                list.forEach(entity -> {
                    entity.setRukuriqi(map.get("日期：").toString());
                    entity.setDianpumingchen(map.get("店铺名：").toString());
                });
                listAll.addAll(list);
            }
            return R.ok().put("listAll", listAll);
        }
    
        public static Workbook getWorkBook(MultipartFile file) throws IOException {
            //这样写excel能兼容03和07
            InputStream is = file.getInputStream();
            Workbook hssfWorkbook = null;
            try {
                hssfWorkbook = new HSSFWorkbook(is);
            } catch (Exception ex) {
                is =file.getInputStream();
                hssfWorkbook = new XSSFWorkbook(is);
            }
            return hssfWorkbook;
        }
    
    }

# 实体类VO #

package io.btm.modules.importexcel;
    
    import cn.afterturn.easypoi.excel.annotation.Excel;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public class ExcelVo {
        @Excel(name = "序号")
        private String indexNum;
        @Excel(name = "品名")
        private String name;
        @Excel(name = "订货数（斤）")
        private String dinghuoNum;
        @Excel(name = "去皮斤数")
        private String qupiNum;
        @Excel(name = "去皮斤数")
        private String sunhaoNum;
        @Excel(name = "供货单价")
        private String gonghuoNum;
        @Excel(name = "订单金额")
        private String dingnanNum;
        @Excel(name = "订单金额")
        private String qupijine;
        @Excel(name = "订单金额")
        private String sunhaojine;
        @Excel(name = "产品差价")
        private String chanpindanjia;
        @Excel(name = "实际结账")
        private String shijijiezhang;
        @Excel(name = "是否结款")
        private String shifoujiekuan;
    	@Excel(name = "是否结款")
        private String beizhu;
        private String rukuriqi;
        private String dianpumingchen;
    }

[源码地址][Link 2]  
[开发指南][Link 3]

[Link 1]: https://download.csdn.net/download/superlover_/19562321
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1cGVybG92ZXJf_size_16_color_FFFFFF_t_70_pic_center]: /images/20210923/79d781bdaf8b47aaa25402fb46a33e52.png
[Link 2]: https://gitee.com/lemur/easypoi
[Link 3]: http://doc.wupaas.com/docs/easypoi/easypoi-1c0u4mo8p4ro8