JAVA解析Excel工具easyexcel

easyexcel依赖和lombok依赖

<dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>easyexcel</artifactId>
          <version>2.1.5</version> 
</dependency>

<dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

ExcelController

import com.alibaba.excel.write.metadata.WriteSheet;
import com.example33.demo.Service.ExcelDemoService;
import com.example33.demo.model.ExcelPropertyIndexModel;
import com.example33.demo.utils.ExcelUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/excel")
@Slf4j
public class ExcelController {
    private final ExcelDemoService excelDemoService;

    public ExcelController(ExcelDemoService excelDemoService) {
        this.excelDemoService = excelDemoService;
    }

    /**
     * 解析EXCEL文件
     *
     * @return
     */
    @GetMapping("/writerExcel")
    public List<ExcelPropertyIndexModel> writerExcel() {
        List<ExcelPropertyIndexModel> lists = ExcelUtils.readExcel("C:\\Users\\my\\Desktop\\withHead");
        if (lists != null) {
            log.info("表数据：" + lists);
        } else {
            log.info("空异常！");
        }
        return lists;
    }


    /**
     * 导出EXCEL文件
     *
     * @param filePath 导出文件的绝对路径
     * @return
     */
    @GetMapping("/exportExcel")
    public String exportExcel(String filePath) {
        //木有数据库数据源，用xls的解析数据当作数据源
        //List<List<Object>> lists = ExcelUtils.readExcel("C:\\Users\\my\\Desktop\\withHead.xlsx");
       //查询数据库获得数据源
        List<ExcelPropertyIndexModel> lists = excelDemoService.findSome();
        log.info(lists.toString());
        //废弃写法
        //Sheet sheet1 = new Sheet(1, 0, ExcelPropertyIndexModel.class);
        WriteSheet sheet = new WriteSheet();
        sheet.setSheetNo(1);
        sheet.setClazz(ExcelPropertyIndexModel.class);
        ExcelUtils.writeSimpleBySheet(filePath, lists, sheet);
        log.info("导出成功!");
    }
}

ExcelPropertyIndexModel

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class ExcelPropertyIndexModel {

    @ExcelProperty(value = "业务编码", index = 0)
    private String businessNo;
    @ExcelProperty(value = "业务名称", index = 1)
    private String businessName;
    @ExcelProperty(value = "是否自动派工 2:是1:否", index = 2)
    private String autoDispatched;
    @ExcelProperty(value = "状态1:启用2:禁用", index = 3)
    private String status;
    @ExcelProperty(value = "创建时间", index = 4)
    private String createDate;

}

ExcelListener

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.example33.demo.model.ExcelPropertyIndexModel;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class ExcelListener extends AnalysisEventListener {

    public List<ExcelPropertyIndexModel> datas = new ArrayList<>();

    public List<ExcelPropertyIndexModel> getDatas() {
        return datas;
    }

    public void setDatas(List<ExcelPropertyIndexModel> datas) {
        this.datas = datas;
    }

    @Override
    public void invoke(Object object, AnalysisContext context) {
        ExcelPropertyIndexModel stringList = (ExcelPropertyIndexModel) object;
        datas.add(stringList);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //解析结束销毁不用的资源
        //datas.clear();
    }
}

ExcelDemoService

import com.example33.demo.mapper.ExcelDemoMapper;
import com.example33.demo.model.ExcelPropertyIndexModel;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class ExcelDemoService {
    private final ExcelDemoMapper excelDemoMapper;

    public ExcelDemoService(ExcelDemoMapper excelDemoMapper) {
        this.excelDemoMapper = excelDemoMapper;
    }

    public List<ExcelPropertyIndexModel> findSome() {
        return excelDemoMapper.findSome();
    }
}

ExcelDemoMapper

import com.example33.demo.model.ExcelPropertyIndexModel;
import org.springframework.stereotype.Component;
import java.util.List;

@Component
public interface ExcelDemoMapper {
    List<ExcelPropertyIndexModel> findSome();
}

mapper.xml

<select id="findSome" resultType="com.example33.demo.model.ExcelPropertyIndexModel">
        select business_no businessNo,
               business_name businessName,
               auto_dispatched autoDispatched,
               `status` status,
               create_date createDate
        from sys_business_type
        where company_id = 10000
    </select>

生成的excel表格

解析如上表格输出