EasyPoi实现excel多sheet导入

本文主要是介绍EasyPoi实现excel多sheet导入，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

EXCEL

导入模板

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();//获取特定字段的mapMap<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和07InputStream 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;
}

源码地址
开发指南

这篇关于EasyPoi实现excel多sheet导入的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---