excel的匯入與匯出---通用版

李小龍6881644發表於2021-08-16
Excel

excel的匯入與匯出---通用版

web專案關於匯入匯出的業務場景很常見，最近我就又遇到了這個業務場景。這次將最近半個月做的匯入匯出總結一下

使用的pom如下,主要還是阿里巴巴的easyexcel依賴。

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>cn.lxiaol</groupId>
    <artifactId>excel_demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>excel_demo</name>
    <description>excel_demo</description>
    <properties>
        <java.version>11</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--easyExcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.67</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

專案結構如下，一個很簡單的小demo

image-20210815145241500

匯入

因為我的業務不只是一個模組用到匯入，所以定義了一個泛型類。CommonExcelListener具體程式碼如下

package cn.lxiaol.excel_demo.common.listeners;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * T 是讀取到的一行資料轉換為的目標類
 * @author lxiaol
 * @date 2021年08月15日 14:14
 */
@Slf4j
public class CommonExcelListener<T> extends AnalysisEventListener<T> {

    /**存放解析到的資料，給了一個初始容量，為了避免list的頻繁擴容帶來的效能問題*/
    private final List<T> list = new ArrayList<>(1000);

    /**解析每一行都會執行該方法*/
    @Override
    public void invoke(T data, AnalysisContext analysisContext) {
        log.info("解析到一條資料:" + data.toString());
        this.list.add(data);
    }

    /**解析完成*/
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("解析完成:共解析到{}資料", this.list.size());
    }

    /**解析表頭*/
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        log.info("解析到一條頭資料：{}, currentRowHolder: {}", headMap.toString(), context.readRowHolder().getRowIndex());
        headMap.entrySet().removeIf((h) -> Objects.isNull(h.getValue()) || "".equals(h.getValue()));
        log.info("表頭列總數：{},列頭為：{}", headMap.size(), headMap.values());
    }

    public List<T> getList() {
        return this.list;
    }

}

假設我這裡是使用者的賬號和手機號的匯入，對應的實體類如下：

package cn.lxiaol.excel_demo.dto;

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

/**
 * @author lxiaol
 * @date 2021年08月15日 14:16
 */
@Data
public class UserExcelDto {
    @ExcelProperty(value = "使用者名稱", index = 0)
    private String username;

    @ExcelProperty(value = "手機號", index = 1)
    private String phone;

    @ExcelIgnore
    private String password;
}

那麼該如何使用呢？下面來簡單講一下，so easy~

首先建立匯入所需要的controller

package cn.lxiaol.excel_demo.controller;

import cn.lxiaol.excel_demo.common.listeners.CommonExcelListener;
import cn.lxiaol.excel_demo.dto.UserExcelDto;
import cn.lxiaol.excel_demo.dto.UserExcelParamDto;
import cn.lxiaol.excel_demo.service.ExcelService;
import com.alibaba.excel.EasyExcel;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;

/**
 * @author lxiaol
 * @date 2021年08月15日 14:07
 */
@RestController
@RequestMapping("/api/excel")
@Slf4j
public class ExcelController {


    /**匯入*/
    @PostMapping(value = "/dataImport", headers = "content-type=multipart/form-data")
    public String dataImport(@RequestParam("file") MultipartFile file) {
        String result = "success";
        try {
            // 例項化物件並傳入泛型型別
            CommonExcelListener<UserExcelDto> listener = new CommonExcelListener<>();
            // 呼叫easyexcel的方法，傳入檔案流，目標型別，和read監聽器,
            // 設定表頭所在行，自動去除空字元，設定讀取第幾個sheet頁，並開始讀取
            EasyExcel.read(file.getInputStream(), UserExcelDto.class, listener)
                    .headRowNumber(1).autoTrim(true).sheet(0).doRead();
            //讀取結束，得到讀取到的資料
            List<UserExcelDto> list = listener.getList();
            if (!list.isEmpty()) {
                //.....具體業務邏輯
                System.out.println("讀取到資料，進行具體的後續操作");
            } else {
                result = "excel內容不能為空";
            }

        } catch (Exception e) {
            log.error("xxxx匯入 報錯：", e);
            result = "excel匯入報錯，請檢查資料是否合規";
        }
        return result;
    }

}

簡單兩行程式碼，就讀取完了excel的內容，相比原生的poi操作簡單多了，果然是easyexcel

拿到讀取來的資料，就可以做具體的業務了。

匯出

好，下面再看一下匯出，因為匯出也是多個模組都有，所以也寫了公用的工具類

package cn.lxiaol.excel_demo.common.utils;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author lxiaol
 * @date 2021年08月15日 14:35
 */
@Data
public class ExcelExportUtil {

    //表頭
    private String title;
    //各個列的表頭
    private String[] heardList;
    //各個列的元素key值
    private String[] heardKey;
    //需要填充的資料資訊
    private List<Map> data;
    //字型大小
    private int fontSize = 14;
    //行高
    private int rowHeight = 30;
    //列寬
    private int columWidth = 200;
    //工作表
    private String sheetName = "sheet1";

    /**
     * 開始匯出資料資訊
     */
    public void exportExport(HttpServletResponse response) throws IOException {
        //建立工作簿
        Workbook wb = new XSSFWorkbook();
        //建立工作表
        Sheet sheet = wb.createSheet(this.sheetName);
        //設定預設行寬
        sheet.setDefaultColumnWidth(20);

        //設定表頭樣式，表頭居中
        CellStyle titleStyle = wb.createCellStyle();
        //設定字型
        Font titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short) this.fontSize);
        titleStyle.setFont(titleFont);

        //在第1行建立rows
        Row titleRow = sheet.createRow(0);
        //設定列頭元素
        Cell cellHead;

        for (int i = 0; i < heardList.length; i++) {
            //背景填充色
            titleStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.index);
            titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            //邊框
            titleStyle.setBorderLeft(BorderStyle.THIN);//左邊框
            titleStyle.setBorderRight(BorderStyle.THIN);//右邊框
            cellHead = titleRow.createCell(i);
            cellHead.setCellValue(heardList[i]);
            cellHead.setCellStyle(titleStyle);
        }

        //開始寫入實體資料資訊
        //設定資料樣式
        CellStyle dataStyle = wb.createCellStyle();
        //設定字型
        Font dataFont = wb.createFont();
//        font.setFontHeightInPoints((short) this.fontSize);
        dataFont.setBold(false);
        dataStyle.setFont(dataFont);
        int count = 1;
        for (Map datum : data) {
            Row row = sheet.createRow(count);
            Cell cell;
            int len = heardKey.length;
            for (int j = 0; j < len; j++) {
                cell = row.createCell(j);
                cell.setCellStyle(dataStyle);
                Object valueObject = datum.get(heardKey[j]);
                String value;
                if (valueObject == null) {
                    valueObject = "";
                }
                if (valueObject instanceof String) {
                    //取出的資料是字串直接賦值
                    value = (String) datum.get(heardKey[j]);
                } else if (valueObject instanceof Integer) {
                    //取出的資料是Integer
                    value = String.valueOf(((Integer) (valueObject)).floatValue());
                } else if (valueObject instanceof BigDecimal) {
                    //取出的資料是BigDecimal
                    value = String.valueOf(((BigDecimal) (valueObject)).floatValue());
                } else {
                    value = valueObject.toString();
                }
                cell.setCellValue(Objects.isNull(value) ? "" : value);
            }
            count++;
        }
        data.clear();

        //匯出資料
        try (OutputStream os = response.getOutputStream()) {
            String fileName = URLEncoder.encode(this.title, StandardCharsets.UTF_8);
            //設定Http響應頭告訴瀏覽器下載這個附件
            response.setHeader("Content-Disposition", "attachment;Filename=" + fileName + ".xlsx");
            wb.write(os);
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new IOException("匯出Excel出現嚴重異常，異常資訊：" + ex.getMessage());
        } finally {
            wb.close();
        }
    }

    /**
     * 設定匯出excel 的資訊
     * 主要用到了反射，獲取類中標註的ExcelProperty註解的欄位，
     * 然後根據註解的index進行排序
     * @param maps
     * @return
     */
    public static ExcelExportUtil getExcelExportUtil(List<Map> maps, Field[] fields) {
        List<Field> fieldList = Arrays.stream(fields)
                .filter(field -> {
                    ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
                    if (annotation != null && annotation.index() > -1) {
                        field.setAccessible(true);
                        return true;
                    }
                    return false;
                }).sorted(Comparator.comparing(field -> {
                    int index = -1;
                    ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
                    if (annotation != null) {
                        index = annotation.index();
                    }
                    return index;
                })).collect(Collectors.toList());

        List<String> title = new ArrayList<>();
        List<String> properties = new ArrayList<>();
        fieldList.forEach(field -> {
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                final String[] value = annotation.value();
                String tit = value[0];
                title.add(tit);
                final String name = field.getName();
                properties.add(name);
            }
        });

        ExcelExportUtil excelExport = new ExcelExportUtil();
        excelExport.setData(maps);
        excelExport.setHeardKey(properties.toArray(new String[0]));
        excelExport.setFontSize(14);
        excelExport.setHeardList(title.toArray(new String[0]));
        return excelExport;
    }
}

再來看一下controller的匯出程式碼

    @Resource
    private ExcelService excelService;

    /**匯出*/
    @PostMapping("/dataExport")
    public String dataExport(@RequestBody UserExcelParamDto dto, HttpServletRequest request, HttpServletResponse response) {
        String result = "success";
        try {
            // .....呼叫具體的業務方法
            excelService.export(dto, request, response);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("安裝上線匯出 介面報錯：", e);
            result = "excel匯出報錯，請檢查資料是否合規";
        }
        return result;
    }

根據前臺傳過來的篩選條件，呼叫service層的業務方法

package cn.lxiaol.excel_demo.service;

import cn.lxiaol.excel_demo.common.utils.ExcelExportUtil;
import cn.lxiaol.excel_demo.dto.UserExcelDto;
import cn.lxiaol.excel_demo.dto.UserExcelParamDto;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.DateUtil;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author lxiaol
 * @date 2021年08月15日 14:31
 */
@Service
@Slf4j
public class ExcelService {

    /**
     * 匯出根據篩選條件查詢到的資料
     *
     * @param dto      篩選條件
     * @param request
     * @param response
     * @throws IOException
     */
    public void export(UserExcelParamDto dto, HttpServletRequest request,
                       HttpServletResponse response) throws IOException {

        // 模擬從資料庫查詢到10條資料，真是業務可將這段改為查詢資料庫獲取到list
        List<UserExcelDto> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            UserExcelDto userExcelDto = new UserExcelDto();
            userExcelDto.setUsername("使用者：" + i);
            userExcelDto.setUsername("手機號：188****8888");
            list.add(userExcelDto);
        }
        // 將 list 資料存放到maps中，主要是為了匯出時根據表頭填寫對應的值
        List<Map> maps = new ArrayList<>();
        list.forEach(po -> maps.add(JSONObject.parseObject(JSONObject.toJSONString(po), Map.class)));

        // 獲取到excel匯出工具類，並根據傳入class設定了表頭資訊
        ExcelExportUtil excelExportUtil =
                ExcelExportUtil.getExcelExportUtil(maps, UserExcelDto.class.getDeclaredFields());

        excelExportUtil.setTitle("Excel匯出_" + DateTimeFormatter.ofPattern("yyyyMMddHHmmss").format(LocalDateTime.now()));
        excelExportUtil.exportExport(response);
    }
}

重點是理解excelExportUtil.exportExport(response)方法。

總的來說 有了easyexcel的加持，excel 的匯入到處還是很簡單的。

示例程式碼在碼雲上，傳送門--->ゝ李大龍

相關文章