Java進行excel的匯入匯出操作

excel表格的匯出匯入在業務中經常會遇到，下面介紹hutool和easyExcel兩種操作excel的工具

測試的實體類

透過mybatis-plus生成的，用於匯出資料的實體類

@Getter
@Setter
@TableName("device_info")
@ApiModel(value = "DeviceInfo物件", description = "")
public class DeviceInfo implements Serializable {

    @ApiModelProperty("裝置ID")
    @TableId(value = "device_id",type = IdType.ASSIGN_UUID)
    private String deviceId;

    @ApiModelProperty("裝置名稱")
    @TableField("device_name")
    private String deviceName;

    @ApiModelProperty("裝置編號")
    @TableField("device_no")
    private String deviceNo;

    @ApiModelProperty("裝置型號")
    @TableField("device_model")
    private String deviceModel;

    @ApiModelProperty("新建時間")
    @TableField("create_time")
    private LocalDateTime createTime;

    @ApiModelProperty("更新時間")
    @TableField("update_time")
    private LocalDateTime updateTime;

    @ApiModelProperty("建立人")
    @TableField("created_by")
    private String createdBy;

    @ApiModelProperty("更新人")
    @TableField("updated_by")
    private String updatedBy;

    @ApiModelProperty("版本")
    @TableField("version")
    private Long version;

}

Mapper檔案

package com.example.demo.mapper;

import com.example.demo.entity.DeviceInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;


@Mapper
public interface DeviceInfoMapper extends BaseMapper<DeviceInfo> {

}

Mapper的xml檔案

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.mapper.DeviceInfoMapper">

    <!-- 通用查詢對映結果 -->
    <resultMap id="BaseResultMap" type="com.example.demo.entity.DeviceInfo">
        <id column="device_id" property="deviceId" />
        <result column="device_name" property="deviceName" />
        <result column="device_no" property="deviceNo" />
        <result column="device_model" property="deviceModel" />
        <result column="create_time" property="createTime" />
        <result column="update_time" property="updateTime" />
        <result column="created_by" property="createdBy" />
        <result column="updated_by" property="updatedBy" />
        <result column="version" property="version" />
    </resultMap>

    <!-- 通用查詢結果列 -->
    <sql id="Base_Column_List">
        device_id, device_name, device_no, device_model, create_time, update_time, created_by, updated_by, version
    </sql>

</mapper>

IService介面

package com.example.demo.service;

import com.example.demo.entity.DeviceInfo;
import com.baomidou.mybatisplus.extension.service.IService;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;

public interface IDeviceInfoService extends IService<DeviceInfo> {

    void exportExcel(HttpServletResponse response) throws IOException;

    boolean importExcel(InputStream inputStream);
}

1、使用hutool匯出

優點：hutool有很多工具類，包括ExcelWrite和ExcelReader工具類，寫程式碼也很簡潔，我覺得簡單的匯入匯出優先可以使用
缺點：匯出不支援excel模板，很難將一些特定資料放在特定的單元格中，匯入不好讀表格的特殊資訊，如批註資訊等

增加依賴

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.7.22</version>
</dependency>

匯入的表格

匯出的表格

如果要好看的格式、列寬、行距，可以透過以下程式碼設定單元格的格式，

StyleSet style = writer.getStyleSet();
style.getHeadCellStyle().setFillBackgroundColor(IndexedColors.WHITE.getIndex());
writer.getCell(0,0).getCellStyle().setFillForegroundColor(IndexedColors.WHITE.getIndex());

程式碼實現

以下是controller類

package com.example.demo.controller;


import com.example.demo.service.IDeviceInfoService;
import io.swagger.annotations.ApiOperation;
import org.springframework.web.bind.annotation.GetMapping;
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 javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;


@RestController
@RequestMapping("/device-info")
public class DeviceInfoController {

    @Resource
    private IDeviceInfoService deviceInfoService;

    @GetMapping("/exportExcel")
    @ApiOperation(value = "匯出模板表格測試")
    public void exportExcel(HttpServletResponse response) throws IOException {
        deviceInfoService.exportExcel(response);
    }

    @PostMapping("importExcel")
    @ApiOperation(value = "匯入表格測試")
    public boolean importExcel(MultipartFile file) throws IOException {
        return deviceInfoService.importExcel(file.getInputStream());
        }
}

以下是進行簡單匯入匯出操作的Service

package com.example.demo.service.impl;

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.demo.entity.DeviceInfo;
import com.example.demo.mapper.DeviceInfoMapper;
import com.example.demo.service.IDeviceInfoService;
import io.swagger.annotations.ApiModelProperty;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.util.List;


@Service
public class DeviceInfoServiceImpl extends ServiceImpl<DeviceInfoMapper, DeviceInfo> implements IDeviceInfoService {


    @Override
    public void exportExcel(HttpServletResponse response) throws IOException {

        //匯出的表格名稱
        String targetFileName = "測試匯出表格" + LocalDate.now();
        String suffix = ".xlsx";

        //設定介面響應結果格式
        response.reset();
        //response為HttpServletResponse物件
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.addHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(targetFileName + suffix, "UTF-8"));

        List<DeviceInfo> deviceInfoList = this.list();

        // 透過工具類建立writer，預設建立xls格式
        ExcelWriter writer = ExcelUtil.getWriter();

        //獲取註解的值，跟excel表的表頭對應
        Field[] fields = DeviceInfo.class.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            String header = fields[i].getDeclaredAnnotation(ApiModelProperty.class).value();
            writer.addHeaderAlias( fields[i].getName(),header);
        }
        //可以新建sheet，將資料匯出成多個sheet
        writer.setSheet("裝置資訊");
// 一次性寫出內容，使用預設樣式
        writer.write(deviceInfoList);

        //根據單元格內容自動調整列寬
        writer.autoSizeColumnAll();
//out為OutputStream，需要寫出到的目標流
        writer.flush(response.getOutputStream());
// 關閉writer，釋放記憶體
        writer.close();

    }

    @Override
    public boolean importExcel(InputStream inputStream) {
        ExcelReader reader = ExcelUtil.getReader(inputStream, 0);
        //獲取註解的值，跟excel表的表頭對應
        Field[] fields = DeviceInfo.class.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            String header = fields[i].getDeclaredAnnotation(ApiModelProperty.class).value();
            reader.addHeaderAlias(header, fields[i].getName());
        }

        List<DeviceInfo> deviceInfoList = reader.read(0, 1, DeviceInfo.class);

        return this.saveOrUpdateBatch(deviceInfoList);
    }
}

2、使用easyExcel匯出

增加依賴

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

匯入的表格

匯出的表格

預先做好的匯出模板

這是進行匯出的模板，需要在每一個單元格寫上實體的欄位名稱，程式才能精確將資料填到相應的位置
如{.deviceId}在欄位名稱前有個 . 表示資料是一個List，如{listSize}表示資料只是一個單元格的值

匯出的模板檔案放在資源下面

匯出的表格結果

程式碼實現

Controller類

package com.example.demo.controller;


import com.alibaba.excel.EasyExcel;
import com.example.demo.entity.DeviceInfo;
import com.example.demo.listener.DeviceInfoListener;
import com.example.demo.service.IDeviceInfoService;
import io.swagger.annotations.ApiOperation;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;


@RestController
@RequestMapping("/device-info")
public class DeviceInfoController {

    @Resource
    private IDeviceInfoService deviceInfoService;

    @GetMapping("/exportExcel")
    @ApiOperation(value = "匯出模板表格測試")
    public void exportExcel(HttpServletResponse response) throws IOException {
        deviceInfoService.exportExcel(response);
    }

    @PostMapping("importExcel")
    @ApiOperation(value = "匯入表格測試")
    public boolean importExcel(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(),DeviceInfo.class,new DeviceInfoListener(deviceInfoService)).sheet().doRead();
        return true;
    }
}

實現匯出邏輯的Service類

package com.example.demo.service.impl;

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.demo.entity.DeviceInfo;
import com.example.demo.mapper.DeviceInfoMapper;
import com.example.demo.service.IDeviceInfoService;
import io.swagger.annotations.ApiModelProperty;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class DeviceInfoServiceImpl extends ServiceImpl<DeviceInfoMapper, DeviceInfo> implements IDeviceInfoService {


    @Override
    public void exportExcel(HttpServletResponse response) throws IOException {
        //匯出的表格名稱
        String targetFileName = "測試匯出表格" + LocalDate.now();
        String suffix = ".xlsx";
        //根據相對路徑獲取模板表格的內容
        ClassPathResource resource = new ClassPathResource("template/測試匯出模板表格.xlsx");
        File bathFile = resource.getFile();
        //設定介面響應結果格式
        response.reset();
        //response為HttpServletResponse物件
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.addHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(targetFileName + suffix, "UTF-8"));

        List<DeviceInfo> deviceInfoList = this.list();

        Map<String,Object> map = new HashMap<>();
        map.put("listSize",deviceInfoList.size() );
        map.put("nowDate",LocalDate.now() );

        //將模板複製到目標表格中
        ExcelWriter excelWriter = EasyExcelFactory.write(response.getOutputStream()).withTemplate(bathFile).build();
        //將列表的值寫入表格中，要求實體類的欄位名稱與模板中的名稱一致
        WriteSheet sheet = EasyExcelFactory.writerSheet().build();
        excelWriter.fill(map,sheet);
        excelWriter.fill(deviceInfoList, sheet);
        excelWriter.finish();
    }
    
}

匯入需要寫一個資料監聽類，實現readListener介面

package com.example.demo.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import com.example.demo.entity.DeviceInfo;
import com.example.demo.service.IDeviceInfoService;
import lombok.extern.slf4j.Slf4j;

import java.util.List;

@Slf4j
public class DeviceInfoListener implements ReadListener<DeviceInfo> {

    /**
     * 每隔100條儲存資料庫，然後清理list ，方便記憶體回收
     */
    private static final int BATCH_COUNT = 100;
    /**
     * 快取的資料
     */
    private List<DeviceInfo> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    private IDeviceInfoService deviceInfoService;

    public DeviceInfoListener( ) {
    }

    public DeviceInfoListener(IDeviceInfoService deviceInfoService) {
        this.deviceInfoService = deviceInfoService;
    }
    @Override
    public void invoke(DeviceInfo data, AnalysisContext context) {
        log.info("解析到一條資料:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        // 達到BATCH_COUNT了，需要去儲存一次資料庫，防止資料幾萬條資料在記憶體，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 儲存完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
// 這裡也要儲存資料，確保最後遺留的資料也儲存到資料庫
        saveData();
        log.info("所有資料解析完成！");
    }

    /**
     * 加上儲存資料庫
     */
    private void saveData() {
        log.info("{}條資料，開始儲存資料庫！", cachedDataList.size());
        deviceInfoService.saveOrUpdateBatch(cachedDataList);
        log.info("儲存資料庫成功！");
    }
}