spring boot中Excel檔案的解析

一、抽象基類，傳入Excel檔案，自動解析出實體類對象或者相應的Map對象

package com.taoche.im.utility.excel;

import java.util.List;
import java.util.Map;

//F為Excel檔案對象，本例中為spring MultipartFile
//T為實體類，實體類的屬性應該和Excel檔案第一行的标題頭相同
public abstract class AbstractExcelBeanWrapper<F, T> {
    protected F excelFile;

    protected List<T> beanWrapper;

    protected T t;

    protected List<Map<String,String>> mapWrapper;

    protected int indexOfSheet;

//indexOfSheet為sheet的索引編号，從0開始，訓示解析哪一個sheet
    public AbstractExcelBeanWrapper(F file, T t, int indexOfSheet) {
        this.excelFile = file;
        this.t = t;
        this.indexOfSheet = indexOfSheet;
    }

//将Excel檔案解析為具體的Map對象
    protected abstract void resolve(int indexOfSheet);

//将Excel檔案解析為具體的實體類對象
    protected abstract void resolveT(int indexOfSheet) throws IllegalAccessException, InstantiationException;

    public List<T> getBeanWrapper() throws InstantiationException, IllegalAccessException {
        resolveT(indexOfSheet);
        return beanWrapper;
    }

    public List<Map<String, String>> getMapWrapper() {
        resolve(indexOfSheet);
        return mapWrapper;
    }
}      

二、抽象類實作，采用具體的解析Excel對象的技術

package com.taoche.im.utility.excel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class HssfExcelBeanWrapper<F, T> extends AbstractExcelBeanWrapper<F, T> {

    public HssfExcelBeanWrapper(F f, T t, int indexOfSheet) {
        super(f, t, indexOfSheet);
    }

    @Override
    protected void resolve(int indexOfSheet) {
        List<Map<String, String>> list = null;
        Workbook wb = null;
        Sheet sheet = null;
        Row row = null;
        String cellData = null;
        List<String> keys = null;
        if (this.excelFile instanceof MultipartFile) {
            MultipartFile file = (MultipartFile) excelFile;
            wb = readExcel(file);
            if (wb != null) {
                list = new ArrayList<>();
                sheet = wb.getSheetAt(1);
                int rownum = sheet.getPhysicalNumberOfRows();
                row = sheet.getRow(0);
                int column = row.getPhysicalNumberOfCells();
                keys = new ArrayList<>();
                for (int i = 0; i < column; i++) {
                    keys.add((String) getCellFormatValue(row.getCell(i)));
                }
                for (int i = 1; i < rownum; i++) {
                    Map<String, String> map = new LinkedHashMap<>();
                    row = sheet.getRow(i);
                    if (row != null) {
                        for (int j = 0; j < column; j++) {
                            cellData = (String) getCellFormatValue(row.getCell(j));
                            map.put(keys.get(j), cellData);
                        }
                    } else {
                        break;
                    }
                    list.add(map);
                }
            }
        }
        this.mapWrapper = list;
    }

    @Override
    protected void resolveT(int indexOfSheet) throws IllegalAccessException, InstantiationException {
        List<T> list = null;
        Workbook wb = null;
        Sheet sheet = null;
        Row row = null;
        Object cellData = null;
        List<Field> keys = null;
        if (this.excelFile instanceof MultipartFile) {
            MultipartFile file = (MultipartFile) excelFile;
            wb = readExcel(file);
            if (wb != null) {
                list = new ArrayList<>();
                sheet = wb.getSheetAt(indexOfSheet);
                int rownum = sheet.getPhysicalNumberOfRows();
                row = sheet.getRow(0);
                int column = row.getPhysicalNumberOfCells();
                keys = resolveKeys(row, column);
                for (int i = 1; i < rownum; i++) {
                    T bean = (T) this.t.getClass().newInstance();
                    row = sheet.getRow(i);
                    if (row != null) {
                        for (int j = 0; j < column; j++) {
                            cellData = getCellFormatValue(row.getCell(j));
                            keys.get(j).setAccessible(true);
                            setValues(keys.get(j), bean, cellData);
                        }
                    } else {
                        break;
                    }
                    list.add(bean);
                }
            }
        }
        this.beanWrapper = list;
    }

    private void setValues(Field field, T bean, Object cellData) throws IllegalAccessException {
        switch (field.getGenericType().getTypeName()) {
            case "java.lang.String":
                field.set(bean, cellData);
                break;
            case "java.lang.Boolean":
            case "boolean":
                field.set(bean, Boolean.valueOf(cellData.toString()));
                break;
            case "java.lang.Byte":
            case "byte":
                field.set(bean, Byte.valueOf((byte) (double) cellData));
                break;
            case "java.lang.Long":
            case "long":
                field.set(bean, Long.valueOf((long) ((double) cellData)));
                break;
            case "java.lang.Short":
            case "short":
                field.set(bean, Short.valueOf((short) ((double) cellData)));
                break;
            case "java.lang.Integer":
            case "int":
                field.set(bean, Integer.valueOf((int) ((double) cellData)));
                break;
            case "java.lang.Double":
            case "double":
                field.set(bean, Double.valueOf(cellData.toString()));
                break;
            case "java.lang.Float":
            case "floag":
                field.set(bean, Float.valueOf(cellData.toString()));
                break;
            case "java.lang.Character":
            case "char":
                field.set(bean, Character.valueOf(cellData.toString().charAt(0)));
                break;
            default:
                break;
        }
    }

    private List<Field> resolveKeys(Row row, int column) {
        List<Field> keys = new ArrayList<>();
        Class tClass = this.t.getClass();
        Field[] fields = tClass.getDeclaredFields();
        for (int i = 0; i < column; i++) {
            for (int j = 0; j < fields.length; j++) {
                if (fields[j].getName().equalsIgnoreCase((String) getCellFormatValue(row.getCell(i)))) {
                    keys.add(fields[j]);
                    break;
                }
            }
        }
        return keys;
    }

// 讀取Excel檔案，根據字尾名不同，選擇相應的技術
    private static Workbook readExcel(MultipartFile excelFile) {
        Workbook wb = null;
        String fileName = excelFile.getOriginalFilename();
        String extString = fileName.substring(fileName.lastIndexOf("."));
        try {
            if (".xls".equals(extString)) {
                return wb = new HSSFWorkbook(excelFile.getInputStream());
            } else if (".xlsx".equals(extString)) {
                return wb = new XSSFWorkbook(excelFile.getInputStream());
            } else {
                return wb = null;
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    private static Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            switch (cell.getCellType()) {
                case NUMERIC: {
                    cellValue = cell.getNumericCellValue();
                    break;
                }
                case FORMULA: {
                    if (DateUtil.isCellDateFormatted(cell)) {
                        cellValue = cell.getDateCellValue();
                    } else {
                        cellValue = cell.getNumericCellValue();
                    }
                    break;
                }
                case STRING: {
                    cellValue = cell.getStringCellValue();
                    break;
                }
                default:
                    cellValue = "";
                    break;
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }
}      

三、Service類，根據傳入的Excel檔案，解析為具體的實體類，儲存資料庫

@Override
public int batchImport(MultipartFile file, String otherParameters) throws IllegalAccessException, InstantiationException {
    XXXModel model = new XXXModel();
    AbstractExcelBeanWrapper<MultipartFile, ImMessageModel> excelBeanWrapper = new HssfExcelBeanWrapper<>(file, model, 0);
    List<ImMessageModel> list = excelBeanWrapper.getBeanWrapper();
    return this.XXXDao.batchInsert(list, otherParameters);
}      

四、controller層，判斷是否為Excel檔案，調用service層解析存儲資料，傳回插入成功的條數

@PostMapping("importer")
public ResponseResult batchInsert(@RequestParam("file") MultipartFile file, String otherParameters) throws InstantiationException, IllegalAccessException {
    if (file == null ||
            (file.getOriginalFilename().endsWith(".xls") && file.getOriginalFilename().endsWith(".xlsx"))) {
        return new ResponseResult().setCode(5000).setMessage("不是Excel檔案");
    }
    int cnt = this.messageModelService.batchImport(file, otherParameters);
    return new ResponseResult().setCode(200).setMessage("插入：" + cnt + "條");
}