[Java知识库]Spring boot 实现EXCEL文件的上传与下载

EXCEL文件的上传：
第一步：准备相关表格读取类文件
ExcelReadUtil

import lombok.Data;
import org.apache.commons.beanutils.BeanUtilsBean;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Data
public class ExcelReadUtil<T> {

    private static BeanUtilsBean beanUtilsBean = new BeanUtilsBean();
    static {
        beanUtilsBean.getConvertUtils().register(new org.apache.commons.beanutils.converters.DateConverter(null), java.util.Date.class);
    }

    /**
     * 表中列名和列索引的对应关系
     */
    private final Map<String, Integer> title_to_index = new HashMap<>();

    /**
     * 所有带ExcelIn注解的属性
     */
    private final List<Field> fields = new ArrayList<>();
    /**
     * 统计表格的行和列数量用来遍历表格
     */
    private int firstCellNum = 0;
    private int lastCellNum = 0;
    private int firstRowNum = 0;
    private int lastRowNum = 0;
    private String sheetName;
    private Sheet sheet;

    public List<T> read(InputStream in, Class clazz) throws Exception {
        gatherAnnotationFields(clazz);
        configSheet(in);
        configHeader();
        List contentList = null;
        try {
            contentList = readContent(clazz);
        } catch (IllegalAccessException e) {
            throw new Exception(e);
        } catch (InstantiationException e) {
            throw new Exception(e);
        } catch (InvocationTargetException e) {
            throw new Exception(e);
        }
        return contentList;
    }

    private List readContent(Class clazz) throws IllegalAccessException, InstantiationException, InvocationTargetException {
        Object object = null;
        Row row = null;
        List<Object> rsList = new ArrayList<>();
        Object value = null;
        for (int i = (firstRowNum + 1); i <= lastRowNum; i++) {
            object = clazz.newInstance();
            row = sheet.getRow(i);
            for (Field field : fields) {
                //根据注解中的title，取到表格中该列所对应的的值
                Integer column = title_to_index.get(field.getAnnotation(ExcelIn.class).title());
                if (column == null) {
                    continue;
                }
                value = getCellValue(row.getCell(column));
                if (null != value && StringUtils.isNotBlank(value.toString())) {
                    beanUtilsBean.setProperty(object, field.getName(), value);
                }
            }
            rsList.add(object);
        }
        return rsList;
    }

    private void configSheet(InputStream in) throws Exception {
        try (Workbook wb = WorkbookFactory.create(in)) {
            getSheetByName(wb);
        } catch (FileNotFoundException e) {
            throw new Exception(e);
        } catch (IOException e) {
            throw new Exception(e);
        }
    }

    private void configHeader() {
        this.firstRowNum = sheet.getFirstRowNum();
        this.lastRowNum = sheet.getLastRowNum();
        Row row = sheet.getRow(firstRowNum);
        this.firstCellNum = row.getFirstCellNum();
        this.lastCellNum = row.getLastCellNum();
        for (int i = firstCellNum; i < lastCellNum; i++) {
            title_to_index.put(row.getCell(i).getStringCellValue(), i);
        }
    }
    /**
     * 根据sheet名称获取sheet
     *
     * @param workbook
     * @return
     * @throws Exception
     */
    private void getSheetByName(Workbook workbook) throws Exception {
        int sheetNumber = workbook.getNumberOfSheets();
        for (int i = 0; i < sheetNumber; i++) {
            String name = workbook.getSheetName(i);
            if (StringUtils.equals(this.sheetName, name)) {
                this.sheet = workbook.getSheetAt(i);
                return;
            }
        }
        throw new Exception("excel中未找到名称为" + this.sheetName + "的sheet");
    }

    private void gatherAnnotationFields(Class clazz) throws Exception {
        if (!clazz.isAnnotationPresent(ExcelIn.class)) {
            throw new Exception(clazz.getName() + "类上没有ExcelIn注解");
        }
        ExcelIn excelIn = (ExcelIn) clazz.getAnnotation(ExcelIn.class);
        this.sheetName = excelIn.sheetName();
        Field[] allFields = clazz.getDeclaredFields();
        for (Field field : allFields) {
            if (field.isAnnotationPresent(ExcelIn.class)) {
                fields.add(field);
            }
        }
        if (fields.isEmpty()) {
            throw new Exception(clazz.getName() + "中没有ExcelIn注解字段");
        }
    }

    private Object getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        Object obj = null;
        switch (cell.getCellType()) {
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case ERROR:
                obj = cell.getErrorCellValue();
                break;
            case FORMULA:
                try {
                    obj = String.valueOf(cell.getStringCellValue());
                } catch (IllegalStateException e) {
                    obj = numericToBigDecimal(cell);
                }
                break;
            case NUMERIC:
                obj = getNumericValue(cell);
                break;
            case STRING:
                String value = String.valueOf(cell.getStringCellValue()).trim();
                obj = value;
                break;
            default:
                break;
        }
        return obj;
    }
    private Object getNumericValue(Cell cell) {
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            return cell.getDateCellValue();
        }else {
            return numericToBigDecimal(cell);
        }
    }
    private Object numericToBigDecimal(Cell cell) {
        String valueOf = String.valueOf(cell.getNumericCellValue());
        BigDecimal value = new BigDecimal(valueOf);
        return value;
    }
}

第二步：实现文件上传函数

import org.springframework.web.multipart.MultipartFile;

//从文件里面读取数据
public List<XxxUnit> readDataFromFile(MultipartFile unitListFile) {
        ExcelReadUtil<XxxUnit> reader = new ExcelReadUtil<>();
        List<XxxUnit> xxxUnitList = null;
        try {
            xxxUnitList = reader.read(unitListFile.getInputStream(), Xxx.class);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return xxxUnitList;
    }

EXCEL文件的下载

第一步：添加maven依赖

<dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>5.2.0</version>
</dependency>
<dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi-ooxml</artifactId>
     <version>5.2.0</version>
</dependency>
<dependency>
     <groupId>commons-beanutils</groupId>
     <artifactId>commons-beanutils</artifactId>
     <version>1.9.3</version>
</dependency>

第二步：实现EXCEL文件下载函数

import java.io.OutputStream;
import org.apache.poi.hssf.usermodel.*;

public void exportFile(HttpServletResponse response){
    //创建表格
    HSSFWorkbook wb = new HSSFWorkbook();
    //创建sheet1
    HSSFSheet sheet1 =wb.createSheet("sheet名称");
    HSSFRow row = sheet1.createRow(0);
    //创建单元格
    HSSFCellStyle style =wb.createCellStyle();
    HSSFCell cell = row.createCell(0);
    cell.setCellValue("第一列");
    cell.setCellStyle(style);

    cell = row.createCell((short)1);
    cell.setCellValue("第二列");
    cell.setCellStyle(style);

    cell = row.createCell((short)2);
    cell.setCellValue("第三列");
    cell.setCellStyle(style);

    //数据库获取数据
    List<DataDTO> dataDTOList= findDatas();

    //数据值给到表格
    for(int i=0; i<dataDTOList.size(); i++){
        row = sheet1.createRow(i+1);
        DataDTO dataDTO = dataDTOList.get(i);
        row.createCell((short)0).setCellValue(dataDTO.getColumn1());
        row.createCell((short)1).setCellValue(dataDTO.getColumn2());
        row.createCell((short)2).setCellValue(dataDTO.getColumn3());
    }

//下载EXCEL
    OutputStream out = null;
    try{
         out = response.getOutputStream();
         String fileName= "结果表格";
         response.setContentType("application/vnd.ms-excel; charset=utf-8");
         response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
             wb.write(out);
             wb.close();
        } catch (Exception e){
            e.printStackTrace();
        }
    }