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表格

表格样式

解析如上表格输出

解析数据格式

接收文件和多个参数

• 如果需要接收文件和多个参数的情况下,我们可以把文件接收格式定义为MultipartFile,并且写入需要的其他参数的定义.
• controller改动如下,主要是改动了访问方式和参数列表.

/**
     * 解析EXCEL文件
     *
     * @return
     */
    @PostMapping("/writerExcel")
    public List<ExcelPropertyIndexModel> writerExcel(@RequestBody MultipartFile file, String name, int age) {
        //List<ExcelPropertyIndexModel> lists = ExcelUtils.readExcel("f:\\withHead");
        List<ExcelPropertyIndexModel> lists = ExcelUtils.readExcel(file);
        log.info("name {}",name);
        log.info("age {}", age);
        if (lists != null) {
            log.info("表数据：" + lists);
        } else {
            log.info("空异常！");
        }
        return lists;
    }

• 相应的ExcelUtils的解析方法需要改动,把文件的输入流直接转化得到.

/**
     * 解析excel文件内容
     *
     * @param file
     * @return
     */
    public static List<ExcelPropertyIndexModel> readExcel(MultipartFile file) {

        //File file = new File(fileName);
        InputStream inputStream = null;
        try {
            //inputStream = new FileInputStream(file);
            inputStream = file.getInputStream();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        // 解析每行结果在listener中处理
        ExcelListener listener = new ExcelListener();

        ExcelReader excelReader1 = EasyExcelFactory.read(inputStream)
                .registerReadListener(listener)
                .head(ExcelPropertyIndexModel.class)
                .excelType(ExcelTypeEnum.XLS).build();

        excelReader1.readAll();
        List<ExcelPropertyIndexModel> datas = listener.getDatas();
        return datas;
    }

导出excel包含多个sheet

String filePath = "f:/withHead";
ExcelWriter excelWriter = EasyExcel.write(filePath).build();
WriteSheet writeSheet = EasyExcel.writerSheet(0, "业务信息1").head(ExcelPropertyIndexModel.class).build();
excelWriter.write(lists, writeSheet);
WriteSheet writeSheet1 = EasyExcel.writerSheet(1, "业务信息2").head(ExcelPropertyIndexModel.class).build();
excelWriter.write(lists, writeSheet1);
excelWriter.finish();

表格样式

导出excel并实现页面下载

@Controller
public class ExportExcelController {
    @GetMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response) throws Exception{
            String fileName="业务类型";
                response.setContentType("application/vnd.ms-excel");
                response.setCharacterEncoding("utf-8");
                // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
                fileName = URLEncoder.encode(fileName, "UTF-8");
                response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
                List<ExcelPropertyIndexModel> lists = excelDemoService.findSome();
                ExcelWriter excelWriter = EasyExcel.write(filePath).build();
                WriteSheet writeSheet = EasyExcel.writerSheet(0, "业务信息1").head(ExcelPropertyIndexModel.class).build();
                excelWriter.write(lists, writeSheet);
                WriteSheet writeSheet1 = EasyExcel.writerSheet(1, "业务信息2").head(ExcelPropertyIndexModel.class).build();
                excelWriter.write(lists, writeSheet1);
                excelWriter.finish();
    }
}

浏览器测试

阿里云easyexcel

https://github.com/alibaba/easyexcel/blob/master/src/test/java/com/alibaba/easyexcel/test/demo/web/WebTest.java