JAVA POI通用Excel导入模板

Excel导入模板类

package com.golte.dataform.analysis.controller;

import com.alibaba.fastjson.JSON;
import com.golte.common.GlobalResponse;
import com.golte.dataform.analysis.vo.response.project.ProjectBaseInfoResp;
import com.golte.utils.DateUtils;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;

public class ProjectBasicInfoExcel {

    private static String getValue(XSSFCell xssfCell) {
        if (Objects.isNull(xssfCell)) {
            return null;
        }
        if (xssfCell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        }

        if (xssfCell.getCellType() == CellType.NUMERIC) {
            return String.valueOf(xssfCell.getNumericCellValue());
        }

        return String.valueOf(xssfCell);
    }

    @GetMapping("/downTemplate")
    @ApiOperation("下载模板")
    public void downTemplate(HttpServletResponse response) throws IOException {
        String time = DateUtils.dateTime2Str(DateUtils.DATE_PATTERN_DEFAULT, new Date());
        String fileUrl = "test.xlsx";
        ServletOutputStream out = null;
        InputStream iputstream = null;
        try {
            //读取文件服务器模板
            URL url = new URL(fileUrl);
            HttpURLConnection uc = (HttpURLConnection) url.openConnection();
            uc.setDoInput(true); //设置是否要从 URL 连接读取数据,默认为true
            uc.connect();
            iputstream = uc.getInputStream();

            String fileName = time + "基础信息导入模板.xls";
            String[] array = fileName.split("[.]");
            String fileType = array[array.length - 1].toLowerCase();
            //设置文件ContentType类型
            if ("jpg,jepg,gif,png".contains(fileType)) {//图片类型
                response.setContentType("image/" + fileType);
            } else if ("pdf".contains(fileType)) {//pdf类型
                response.setContentType("application/pdf");
            } else {//自动判断下载文件类型
                response.setContentType("multipart/form-data");
            }
            response.setContentType("application/octet-stream;charset=utf-8");
            //设置文件头：最后一个参数是设置下载文件名
            response.setHeader("Content-Disposition",
                    "attachment;fileName=" + URLEncoder.encode(fileName, "UTF-8"));

            out = response.getOutputStream();
            // 读取文件流
            int len;
            byte[] buffer = new byte[1024 * 10];
            while ((len = iputstream.read(buffer)) != -1) {
                out.write(buffer, 0, len);
            }
            if (Objects.nonNull(out)) {
                out.flush();
            }
        } catch (IOException e) {
        } finally {
            try {
                if (Objects.nonNull(out)) {
                    out.close();
                }
                if (Objects.nonNull(iputstream)) {
                    iputstream.close();
                }
            } catch (IOException e) {
            }
        }
    }

    @PostMapping("/excelImport")
    @ApiOperation("项目基础信息")
    public GlobalResponse<List<String>> excelImport(@RequestParam("file") MultipartFile file) {
        //查询数据库中是否存在数据
        Integer count = 0; //mapper.selectCount()
        String projectId;
        List<String> msg = new ArrayList<>();
        ProjectBaseInfoResp projectBaseInfo;
        XSSFWorkbook xssfWorkbook = null;
        XSSFCell cell = null;
        try {
            xssfWorkbook = new XSSFWorkbook(file.getInputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
        //获取工作簿中的第一个Sheet
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
        //获取第一个Sheet,从第二行开始遍历获取每一行数据
        for (int rowNum = 2; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
            //获取第 rowNum 行对象
            XSSFRow xssfRow = xssfSheet.getRow(rowNum);
            Class<ProjectBaseInfoResp> projectBaseInfoRespClass = ProjectBaseInfoResp.class;
            try {
                //对象字段必须与excel表格字段顺序一致
                projectBaseInfo = projectBaseInfoRespClass.newInstance();
                Field[] fields = projectBaseInfoRespClass.getDeclaredFields();
                for (int i = 0; i < fields.length; i++) {
                    cell = xssfSheet.getRow(1).getCell(i);
                    Field field = fields[i];
                    field.setAccessible(true);
                    field.set(projectBaseInfo, getValue(xssfRow.getCell(i)));
                    log.info("字段名称：{}", cell);
                }
                log.info("解析对象属性值：{}", JSON.toJSONString(projectBaseInfo));
            } catch (Exception e) {
                msg.add("项目ID: [ " + xssfRow.getCell(0).toString() + " ] 解析失败! 请检查字段 [ " + cell + " ] 数据是否正确");
                e.printStackTrace();
                continue;
            }
            //取第一行第一个格的值
            projectId = getValue(xssfRow.getCell(0));
            //数据库表中没有数据的时候直接插入所有excel不做校验
            if (Objects.isNull(count) || count.intValue() == 0) {
                log.info("插入：{}",projectId);
                continue;
            }
            //根据 projectId 去数据库查询项目
            //mapper.selectById(projectId);
            //查询为空插入数据
            if (Objects.isNull(projectId)) {
                log.info("插入：{}",projectId);
                continue;
            }
            //如果不为空并且字段有变化则更新数据
            String oldObject = JSON.toJSONString(projectId);
            String newObject = JSON.toJSONString(projectBaseInfo);
            if (Objects.nonNull(projectId) && !oldObject.equals(newObject)) {
                log.info("更新：{}\n更新前: {}\n更新后: {}",projectId,oldObject,newObject);
            }
        }
        if (msg.size() == 0) {
            msg.add("已完全同步Excel");
        }
        return GlobalResponse.ok(msg);
    }
}

##通用方法体

    private static String getValue(XSSFCell xssfCell) {
        if (Objects.isNull(xssfCell)) {
            return null;
        }
        if (xssfCell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        }

        if (xssfCell.getCellType() == CellType.NUMERIC) {
            return String.valueOf(xssfCell.getNumericCellValue());
        }

        return String.valueOf(xssfCell);
    }

    public static <T> List<T> excelTemplate(Integer rowNum,Integer sheetIndex,Class clz, XSSFWorkbook xssfWorkbook) {
        List<T> excelList = new ArrayList();
        //获取第一个Sheet
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(sheetIndex);
        //第 rowNum 开始遍历获取每一行数据
        for (; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
            //获取第 rowNum 行对象
            XSSFRow xssfRow = xssfSheet.getRow(rowNum);
            try {
                //对象字段必须与excel表格字段顺序一致
                T obj = (T) clz.newInstance();
                Field[] fields = clz.getDeclaredFields();
                for (int i = 0; i < fields.length; i++) {
                    Field field = fields[i];
                    field.setAccessible(true);
                    field.set(obj, getValue(xssfRow.getCell(i)));
                }
                excelList.add(obj);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        //返回Excel解析后的对象集合
        return excelList;
    }

    public static <T> List<T> excelTemplate(Integer rowNum,Integer sheetIndex,Class clz, XSSFWorkbook xssfWorkbook) {
        List<T> excelList = new ArrayList();
        List<String> msg = new ArrayList();
        XSSFCell cell = null;
        //获取第一个Sheet
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(sheetIndex);
        //第 rowNum 开始遍历获取每一行数据
        for (; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
            //获取第 rowNum 行对象
            XSSFRow xssfRow = xssfSheet.getRow(rowNum);
            try {
                //对象字段必须与excel表格字段顺序一致
                T obj = (T) clz.newInstance();
                Field[] fields = clz.getDeclaredFields();
                for (int i = 0; i < fields.length; i++) {
                    cell = xssfSheet.getRow(1).getCell(i);
                    Field field = fields[i];
                    field.setAccessible(true);
                    field.set(obj, getValue(xssfRow.getCell(i)));
                    log.info("字段名称：{}", cell);
                }
                log.info("解析对象属性值：{}", JSON.toJSONString(obj));
                excelList.add(obj);
            } catch (Exception e) {
                e.printStackTrace();
                msg.add("项目ID: [ " + xssfRow.getCell(0).toString() + " ] 解析失败! 请检查字段 [ " + cell + " ] 数据是否正确");
            }
        }
        log.info("{}", Arrays.toString(msg.toArray()));
        return excelList;
    }