Java++：POI 导入|导出 工具类

本文主要是介绍Java++：POI 导入|导出 工具类，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

没有十全十美的程序

 1、封装工具类

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，导出样例：

感谢楼主的封装

引文：https://www.cnblogs.com/zhulei2/p/15907855.html

这篇关于Java++：POI 导入|导出 工具类的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！