java poi导入导出excel

1，Utils：

package com.osrmt.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel处理工具类
 *
 */
public class ExcelUtil {

    /**
     * 导出excel
     * @param headNameList 文件字段头显示名字
     * @param headField 文件字段头数据字段
     * @param listData 数据集合
     * @param stream 流
     * @throws Exception 异常
     */
    public static void exportExcel(List<String> headNameList,List<String> headField,List<Map<String,String>> listData,OutputStream stream) throws Exception{
        try (HSSFWorkbook wb = new HSSFWorkbook()) {
            HSSFSheet sheet = wb.createSheet("Sheet1");
            HSSFCellStyle titleStyle = wb.createCellStyle(); // 标题样式
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            HSSFFont titleFont = wb.createFont(); // 创建字体样式
            titleFont.setBold(true); // 字体加粗
            titleFont.setFontName("Times New Roman"); // 设置字体类型
            titleFont.setFontHeightInPoints((short) 18); // 设置字体大小
            titleStyle.setFont(titleFont); // 为标题样式设置字体样式

            HSSFCellStyle headerXStyle = wb.createCellStyle();
            headerXStyle.setBorderTop(BorderStyle.THIN);
            headerXStyle.setBorderBottom(BorderStyle.THIN);
            headerXStyle.setBorderLeft(BorderStyle.THIN);
            headerXStyle.setBorderRight(BorderStyle.THIN);
            HSSFFont headerFont = wb.createFont(); // 创建字体样式
            headerFont.setBold(true); // 字体加粗
            headerXStyle.setFont(headerFont);
            headerXStyle.setAlignment(HorizontalAlignment.CENTER);

            //获取表头显示名称
            String[] titles = new String[headNameList.size()];
            for (int i = 0; i < headNameList.size(); i++) {
                titles[i] = headNameList.get(i);
            }

            int rowInd = 0;
            HSSFRow headerRow = sheet.createRow(rowInd);
            headerRow.setHeightInPoints(16);
            for (int i = 0; i < titles.length; i++) {
                HSSFCell cell = headerRow.createCell(i);
                cell.setCellValue(titles[i]);
                cell.setCellStyle(headerXStyle);
            }

            HSSFCellStyle headerCStyle = wb.createCellStyle();
            headerCStyle.setBorderTop(BorderStyle.THIN);
            headerCStyle.setBorderBottom(BorderStyle.THIN);
            headerCStyle.setBorderLeft(BorderStyle.THIN);
            headerCStyle.setBorderRight(BorderStyle.THIN);

            titles = new String[headField.size()];
            for (int i = 0; i < headField.size(); i++) {

                titles[i] = headField.get(i).toLowerCase();
            }
            if (listData != null) {
                for (Map<String, String> map : listData) {
                    rowInd++;
                    HSSFRow _row = sheet.createRow(rowInd);
                    _row.setHeightInPoints(16);

                    for (int j = 0; j < titles.length; j++) {
                        HSSFCell cell = _row.createCell(j);
                        cell.setCellStyle(headerCStyle);

                        cell.setCellValue(StringUtil.toNotNullString(map.get(titles[j])));
                    }
                }
            }
            wb.write(stream);

            /*OutputStream out = new FileOutputStream("F:/" + "xls.xlsx");
            wb.write(out);*/
        }
    }
    
    /**
     * 检查导入excel字段头格式是否正确
     * @param wb 工作簿
     * @param colName 列名列表
     * @return 是：一致
     */
    public static boolean checkFormat(HSSFWorkbook wb,List<String> colName) {
        HSSFSheet sheet=wb.getSheetAt(0);
        int lastRow = sheet.getLastRowNum();
        if (lastRow <1) {
            return false;
        }
        //检查格式，表头是否一致
        Row row = sheet.getRow(0);
        int cellF = row.getFirstCellNum();
        int cellL = row.getLastCellNum();
        if (cellF >= 0 && cellL >= colName.size()) {
            for(int i=0;i<cellL;i++){
                String xlsColString=row.getCell(i).getStringCellValue();
                String colString=colName.get(i);
                if (!xlsColString.equals(colString)) {
                    return false;
                }
            }
            return true;
        }
        return false;
    }
    
    /**
     * 解析excel数据
     * @param wb excel文件对象
     * @param headNameList 文件字段头显示名字
     * @param headFieldList 文件字段头数据字段
     * @return 数据集合
     */
    public static List<Map<String,Object>> analysisExcel(HSSFWorkbook wb,List<String> headNameList,List<String> headFieldList) {
        HSSFSheet sheet=wb.getSheetAt(0);
        int lastRow = sheet.getLastRowNum();
        List<Map<String, Object>> list = new ArrayList<>();
        for (int i = lastRow; i >= 1; i--) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            int firstCell = row.getFirstCellNum();
            int lastCell = row.getLastCellNum();
            if (firstCell != 0 &&lastCell > headNameList.size()) {
                continue;
            }
            Map<String, Object> map = new HashMap<>();
            for (int j = firstCell; j < lastCell; j++) {
                Cell cell = row.getCell(j);
                    if (cell == null) {
                    continue;
                }
                CellType style = cell.getCellTypeEnum();
                String cellString = "";
                if (style == CellType.BOOLEAN) {
                    cellString = String.valueOf(row.getCell(j).getBooleanCellValue());
                } else if (style == CellType.NUMERIC) {
                    short format = cell.getCellStyle().getDataFormat();
                    SimpleDateFormat sdf = null;
                    if (format == 14 || format == 31 || format == 57 || format == 58) {
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }else if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    }else if (format == 21) {
                        sdf = new SimpleDateFormat("HH:mm:ss");
                    }
                    if (sdf != null) {
                        //日期
                        double value = cell.getNumericCellValue();
                        Date date = DateUtil.getJavaDate(value);
                        cellString = StringUtil.toNotNullString(sdf.format(date));
                    }else {
                        String value = StringUtil.toNotNullString(row.getCell(j).getNumericCellValue());
                        if (value.contains(".")) {
                            //判断是否是整形
                            String[] aa = value.split("\\.");
                            if (aa.length == 2 && aa[1].equals("0")) {
                                cellString = aa[0];
                            }else {
                                cellString = String.valueOf(row.getCell(j).getNumericCellValue());
                            }
                        }
                    }
                } else  {
                    cellString = row.getCell(j).getStringCellValue();
                }
                map.put(headFieldList.get(j), cellString);
            }
            list.add(map);
        }
        return list;
    }
}

2，实体类：

package com.osrmt.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

@Data
@TableName(value = "xq_nenglishuxing")
public class XqNengliShuxing {
    @TableId(type= IdType.INPUT)
    @ApiModelProperty("主键id")
    private Long id;

    @TableField(value="code")
    @ApiModelProperty("目标编号")
    private String code;

    @TableField(value="description")
    @ApiModelProperty("能力属性描述信息")
    private String description;

    @TableField(value="priority")
    @ApiModelProperty("优先级(0低 1中 2高)")
    private Integer priority;

    @TableField(value="is_disable")
    @ApiModelProperty("是否禁用(0启用 1禁用)")
    private Integer isDisable;

    @TableField(value="creator")
    @ApiModelProperty("创建人")
    private String creator;

    @TableField(value = "createtime")
    @ApiModelProperty("创建时间")
    private Long createTime;

    @TableField(value="nlqd_id")
    @ApiModelProperty("所属能力清单的id")
    private Long nlqdId;

    @TableField(value = "nengli_mubiao_value")
    @ApiModelProperty("能力目标值")
    private String nengliMubiaoValue;
}

3，接口实现

    @GetMapping("/exportExcel")
    @ApiOperation("导出能力清单能力属性excel")
    public Response exportExcel(@RequestParam("nlqdId") Long nlqdId,
                       HttpServletResponse response) throws Exception {

        QueryWrapper<XqNengliShuxing> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda().eq(XqNengliShuxing::getNlqdId, nlqdId);

        List<XqNengliShuxing> list = nengliShuxingService.list(queryWrapper);


        List<Map<String, String>> data = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Map<String, String> map = JSON.parseObject(JSON.toJSONString(list.get(i)), Map.class);
            data.add(map);
        }

        Properties properties = getApiModelProperty("com.osrmt.entity.XqNengliShuxing");
        List<String> headNameList = properties.values().stream().map(String::valueOf).collect(Collectors.toList());

        List<String> headFieldList = properties.keySet().stream().map(String::valueOf).collect(Collectors.toList());
        System.out.println(properties);

        try {
            ExcelUtil.exportExcel(headNameList, headFieldList, data, response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("导出excel失败");
        }
        return Response.success();
    }

    /** 功能描述:
     * 获取类字段ApiModelProperty注解value值（中文）
     * @param classPath: 类路径
     * @author: zl
     * @date: 2022/2/17 17:10
     */
    private Properties getApiModelProperty(String classPath){
        Properties p = new Properties();
        try {
            // 1.根据类路径获取类
            Class<?> c = Class.forName(classPath);
            // 2.获取类的属性
            Field[] declaredFields = c.getDeclaredFields();
            // 3.遍历属性，获取属性上ApiModelProperty的值，属性的名，存入Properties
            if (declaredFields.length != 0) {
                for (Field field : declaredFields) {
                    if (field.getAnnotation(ApiModelProperty.class) != null) {
                        // key和value可根据需求存
                        // 这存的key为类属性名，value为注解的值
                        p.put(field.getName(), field.getAnnotation(ApiModelProperty.class).value());
                    }
                }
                return p;
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return p;
    }



    @PostMapping("/importExcel")
    @ApiOperation("导入能力清单能力属性excel")
    public Response importExcel(MultipartFile file, Long nlqdId) {
        Properties properties = getApiModelProperty("com.osrmt.entity.XqNengliShuxing");
        List<String> headNameList = properties.values().stream().map(String::valueOf).collect(Collectors.toList());
        List<String> headFieldList = properties.keySet().stream().map(String::valueOf).collect(Collectors.toList());
        try {
            HSSFWorkbook workbook =new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
            List<Map<String, Object>> maps = ExcelUtil.analysisExcel(workbook, headNameList, headFieldList);
            for (int i = 0; i < maps.size(); i++) {
                XqNengliShuxing xqNengliShuxing = JSON.parseObject(JSON.toJSONString(maps.get(i)), XqNengliShuxing.class);
                NengliShuxingProperty nengliShuxingProperty = new NengliShuxingProperty();
                BeanUtils.copyProperties(xqNengliShuxing, nengliShuxingProperty);
                nengliShuxingProperty.setNlqdId(nlqdId);
                addNengliShuxing(nengliShuxingProperty);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return Response.success();
    }

4，导出样例：