Java17 POI5.2.0 Excel 下拉框数据校验

猪悟道

已于 2023-02-04 00:03:53 修改

阅读量2.7k

点赞数 2

分类专栏： JavaWeb 服务框架文章标签： apache java poi excel 数据校验

于 2022-01-21 22:33:37 首次发布

本文链接：https://blog.csdn.net/weixin_42176639/article/details/122630158

版权

JavaWeb 服务框架专栏收录该内容

39 篇文章 9 订阅

订阅专栏

Java 设置Excel 下拉框、自定义数据校验

一、工具类
- 1.ExcelUtil
- 2.Pom
二、生成文件
- 1.下拉框
- 2.数据校验
三、Excel 命令
四、Excel 表头通用生成方法

一、工具类

1.ExcelUtil

package com.demo.util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.ObjectUtils;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * Description: Excel 工具类
 *
 * @Author: zhx & moon hongxu_1234@163.com
 * @Date: 2022-01-21 19:56
 * @version: V1.0.0
 */
public class ExcelUtil {

    /**
     * OUT_PATH         保存位置
     * SHEET_NAME       工作表名称
     * FIRST_ROW_NUM    起始行
     * LAST_ROW_NUM     结束行
     * LENGTH_LIMIT_COL 增加数据校验的列
     */
    private static String OUT_PATH = "D:\\01.xlsx";
    private static String SHEET_NAME = "sheet1";
    private static int FIRST_ROW_NUM = 0;
    private static int LAST_ROW_NUM = 10;
    private static int LENGTH_LIMIT_COL = 5;
    private static List<String[]> COM_BOX_LIST = new ArrayList<>(1);

    public static void creat() {

        /**
         * 1.创建 workbook
         */
        SXSSFWorkbook workbook = new SXSSFWorkbook(-1);

        /**
         * 2.创建 sheet
         */
        SXSSFSheet sheet = workbook.createSheet(SHEET_NAME);

        //TODO 创建行、写入数据、增加样式
        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue("编号");
        row.createCell(1).setCellValue("地点");
        row.createCell(2).setCellValue("节气");
        row.createCell(3).setCellValue("守护");

        /**
         * 3.增加数据校验（自定义）
         *   说明："=(ISTEXT(INDIRECT(\"F\"&ROW())) * (LENB(INDIRECT(\"F\"&ROW())) >= 1) * (LENB(INDIRECT(\"F\"&ROW())) <= 10))"
         *   是否为文本          ISTEXT(INDIRECT(\"F\"&ROW()))
         *   字节数大于等于0     (LENB(INDIRECT(\"F\"&ROW())) >= 0)
         *   字节数小于等于200   (LENB(INDIRECT(\"F\"&ROW())) <= 10))
         *   如果是字符长度，就是LEN() 取F列当前行值 INDIRECT("F"&ROW())
         */
        setDataLengthLimit(sheet,"=((LENB(INDIRECT(\"F\"&ROW())) >= 1) * (LENB(INDIRECT(\"F\"&ROW())) <= 10))");

        /**
         * 4.设置下拉值 创建下拉值保存表立春、雨水、惊蛰、春分、清明、谷雨、立夏、小满、芒种、夏至、小暑、大暑、立秋、处暑、白露、秋分、寒露、霜降、立冬、小雪、大雪、冬至、小寒、大寒
         */
        SXSSFSheet comBox = workbook.createSheet("comBox");
        workbook.setSheetHidden(workbook.getSheetIndex(comBox),true);
        COM_BOX_LIST.add(new String[]{"洛阳","南京","西安","北京","开封","杭州"});
        COM_BOX_LIST.add(new String[]{"立春","雨水","惊蛰","清明","谷雨","立夏","小满","芒种","夏至","小暑","大暑","立秋","处暑","白露","秋分","寒露","霜降","立冬","小雪","大雪","冬至","小寒","大寒"});
        COM_BOX_LIST.add(new String[]{"青龙","白虎","玄武","朱雀"});
        setComBox(comBox,COM_BOX_LIST);
        setPullBox(sheet,COM_BOX_LIST);

        /**
         * 增加
         */
        save(workbook,OUT_PATH);
    }

    /**
     * 保存Excel到本地
     * @param workbook
     * @param outPath
     */
    public static void save(Workbook workbook,String outPath){
        FileOutputStream foss = null;
        try {
            FileOutputStream fos = new FileOutputStream(outPath);
            workbook.write(fos);
            foss = fos;
            fos.close();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (!ObjectUtils.isEmpty(foss)){
                try {
                    foss.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }


    }


    /**
     * 按列写入下拉值
     * @param sheet
     * @param list
     * @return
     */
    private static void setComBox(SXSSFSheet sheet, List<String[]> list){
        int rowCount,sellCount=0;
        SXSSFRow row;
        for (String[] arr:list) {
            rowCount = 0;
            for (String str:arr) {
                if(rowCount>sheet.getLastRowNum()){
                    row = sheet.createRow(rowCount);
                }
                else{
                    row = sheet.getRow(rowCount);
                }
                row.createCell(sellCount).setCellValue(str);
                rowCount++;
            }
            sellCount++;
        }
    }

    /**
     * 在Excel中设置下拉列表
     * +1 从第二列开始设置下拉
     *
     * @param sheet
     * @param list
     * @return
     */
    private static void setPullBox(SXSSFSheet sheet, List<String[]> list) {

        //存放枚举值信息的列 在第二个sheet表 即comBox
        String[] arr = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};

        for (int i = 0; i < list.size(); i++) {
            //即comBox第A1到A5000作为下拉列表来源数据
            StringBuffer stringBuffer = new StringBuffer();
            stringBuffer.append("comBox!$").append(arr[i]).append("$1:$").append(arr[i]).append("$").append(list.get(i).length);
            sheet.addValidationData(getPullDataValidation(stringBuffer.toString(), i+1, sheet));
        }
    }

    /**
     * @param @param  strFormula  枚举值在第二个sheet中行数和列数无需修改
     * @param @param  firstRow   起始行
     * @param @param  endRow     终止行
     * @param @param  firstCol   起始列
     * @param @param  endCol     终止列
     * @param @return
     * @return HSSFDataValidation
     * @throws
     * @Title: SetDataValidation
     * @Description: 下拉列表元素很多的情况 (255以上的下拉)
     */
    private static XSSFDataValidation getPullDataValidation(String strFormula,
                                                        int firstCol, SXSSFSheet sheet) {
        sheet.setSelected(true);
        // 创建下拉列表数据
        DataValidationHelper dvHelper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint2 = dvHelper.createFormulaListConstraint(strFormula);
        // 设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(FIRST_ROW_NUM, LAST_ROW_NUM, firstCol, firstCol);
        XSSFDataValidation dataValidation2 = (XSSFDataValidation) dvHelper.createValidation(constraint2, regions);

        dataValidation2.createErrorBox("输入不合法", "请键入下拉列表中的值！");
        dataValidation2.createPromptBox("", null);
        dataValidation2.setShowErrorBox(Boolean.TRUE);
        return dataValidation2;
    }

    /**
     * 添加数据长度校验
     * @param sheet
     */
    private static void setDataLengthLimit(SXSSFSheet sheet,String cellCheck){
        DataValidationHelper helper = sheet.getDataValidationHelper();
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(FIRST_ROW_NUM,LAST_ROW_NUM,LENGTH_LIMIT_COL,LENGTH_LIMIT_COL);
        DataValidationConstraint customConstraint = helper.createCustomConstraint(cellCheck);
        DataValidation dataValidation = helper.createValidation(customConstraint, cellRangeAddressList);
        dataValidation.createErrorBox("输入不合法", "描述过长(可输入0-200个字节)");
        dataValidation.setEmptyCellAllowed(true);
        dataValidation.setShowErrorBox(true);
        sheet.addValidationData(dataValidation);
    }

    public static void main(String[] args) {
        creat();
    }
}

2.Pom

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.1</version>
        <relativePath/>
    </parent>
    <groupId>com.demo</groupId>
    <artifactId>exceldemo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
    </properties>
    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <!-- xls https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.0</version>
        </dependency>

        <!-- xlsx https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.0</version>
        </dependency>


    </dependencies>
</project>

二、生成文件

1.下拉框

在这里插入图片描述

2.数据校验

在这里插入图片描述

三、Excel 命令

1.获取活动单元格：=INDIRECT(“R”&ROW()&“C”&CELL(),0)

2.获取指定单元格：=F1

在这里插入图片描述

3.获取前一列对应行的值：=INDIRECT(“R”&ROW()&“C”&COLUMN()-1,0)

在这里插入图片描述

4.获取指定单元格数据字长：=LEN(F2)

在这里插入图片描述

5.获取指定单元格数据字节长：=LENB(F2)

在这里插入图片描述

6.判断：=IF(LENB(F2)>2,“大于”,“小于或等于”)

在这里插入图片描述

四、Excel 表头通用生成方法

通过表头配置信息，生成任意格式 Excel 表头，当前演示代码通过Json文件配置的头信息，还可以存在数据库、缓存、接口传参的形式获取

1.Excel 文件和头信息描述类

POI 依赖包

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>

WorkBook 描述类

package com.demo.entity.export;

import com.demo.entity.export.SheetDataHead;
import lombok.Data;

import java.util.List;

/**
 * @author 
 * @date 2023-02-02 17:36
 * @since 1.8
 */
@Data
public class WorkBookConfig {
    private String fileName;
    private String sheetName;
    private int dataStartRow;
    private List<SheetDataHead> headList;
}

头信息描述类

package com.demo.entity.export;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * @author 
 * @date 2023-02-02 17:37
 * @since 1.8
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class SheetDataHead {

    /**
     * 头名称
     */
    private String name;
    /**
     * 起始行
     */
    private int firstRow;
    /**
     * 结束行
     */
    private int lastRow;
    /**
     * 起始列
     */
    private int firstCol;
    /**
     * 结束列
     */
    private int lastCol;

    /**
     * 任何一个不相等即合并
     * @return
     */
    public boolean isCellRange(){
        return firstRow != lastRow || firstCol != lastCol;
    }

    /**
     * 获取合并范围
     * @return
     */
    public CellRangeAddress getCellRangeAddress(){
        return new CellRangeAddress(firstRow,lastRow,firstCol,lastCol);
    }
}

2.表头生成方法

package com.demo.util;

import com.alibaba.fastjson.JSONObject;
import com.demo.entity.export.SheetDataHead;
import com.demo.entity.export.WorkBookConfig;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author 
 * @date 2023-02-02 17:33
 * @since 1.8
 */
public class ExcelUtil {

    /***
     * buffer 缓冲区
     */
    public static final int BYTE_BUFFER_ALLOCATE = 2048;

    /**
     * 创建 WorkBook 并设置表头
     * @param sheetName
     * @param heads
     * @return
     */
    public static XSSFWorkbook buildWorkBookAndWriteHead(String sheetName, List<SheetDataHead> heads){
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet(sheetName);
        if (!CollectionUtils.isEmpty(heads)){
            CellStyle cellStyle = getCellStyle(workbook);
            Map<Integer,List<SheetDataHead>> rowMap = heads.stream().collect(Collectors.groupingBy(SheetDataHead::getFirstRow));
            rowMap.forEach((k,v)->{
                XSSFRow row = sheet.createRow(k);
                XSSFCell cell;
                for (SheetDataHead head:v){
                    cell = row.createCell(head.getFirstCol());
                    cell.setCellValue(head.getName());
                    cell.setCellStyle(cellStyle);
                    if (head.isCellRange()){
                        sheet.addMergedRegion(head.getCellRangeAddress());
                    }
                }
            });
        }
        return workbook;
    }

    /**
     * 获取单元格样式
     * @return
     */
    public static CellStyle getCellStyle(XSSFWorkbook workbook){
        //创建样式对象
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        //垂直和水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return cellStyle;
    }

    /**
     * 数据转换
     * @param filePath
     */
    public static WorkBookConfig getDataHeadList(String filePath){

        String json = resourceRead(filePath);
        WorkBookConfig config = new WorkBookConfig();
        config.setHeadList(new ArrayList<>(0));
        if (StringUtils.hasLength(json)){
            try {
                config = JSONObject.parseObject(json,WorkBookConfig.class);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
        return config;
    }

    /**
     * 读取配置的JSON文件
     * @param filePath
     * @return
     */
    public static String resourceRead(String filePath){

        if (StringUtils.hasLength(filePath)){
            ClassPathResource classPathResource = new ClassPathResource(filePath);
            InputStream inputStream = null;
            StringBuilder builder = new StringBuilder();
            try {
                inputStream = classPathResource.getInputStream();
                byte[] temp = new byte[BYTE_BUFFER_ALLOCATE];
                int read;
                while ((read = inputStream.read(temp)) != -1){
                    if (read < BYTE_BUFFER_ALLOCATE){
                        byte[] tail = Arrays.copyOf(temp,read);
                        builder.append(new String(tail));
                    } else {
                        builder.append(new String(temp));
                    }
                }
                return builder.toString();
            } catch (IOException e) {
                return "";
            } finally {
                if (null != inputStream){
                    try {
                        inputStream.close();
                    } catch (IOException e) {
                        throw new RuntimeException(e);
                    }
                }
            }

        }
        return "";
    }
}

3.测试类，用于导出Excel文件

package com.demo.controller;

import com.demo.entity.export.WorkBookConfig;
import com.demo.util.ExcelUtil;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

/**
 * @author 
 * @date 2023-02-02 17:38
 * @since 1.8
 */
@RestController
@RequestMapping("/export")
public class ExportController {

    static Map<Integer,String> mapConfig = new HashMap<>(2);

    static {
        mapConfig.put(0,"data_excel_json/demo_1.json");
        mapConfig.put(1,"data_excel_json/demo_2.json");
    }

    @GetMapping("/{type}")
    public void export(@PathVariable("type") Integer type, HttpServletResponse response){

        WorkBookConfig config = ExcelUtil.getDataHeadList(mapConfig.get(type));

        XSSFWorkbook workbook = ExcelUtil.buildWorkBookAndWriteHead(config.getSheetName(),config.getHeadList());

        //写到输出流
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = config.getFileName();
            response.setHeader("Content-Disposition", "attachment; filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            if (null!=workbook){
                try {
                    workbook.close();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }
}

测试用 Json 配置信息

在这里插入图片描述

{
  "fileName": "demo_1",
  "sheetName": "sheet_t",
  "dataStartRow": 1,
  "headList": [
    {
      "name": "河南",
      "firstRow": 0,
      "firstCol": 0,
      "lastRow": 0,
      "lastCol": 3
    },
    {
      "name": "河北",
      "firstRow": 0,
      "firstCol": 4,
      "lastRow": 0,
      "lastCol": 7
    },
    {
      "name": "南阳",
      "firstRow": 1,
      "firstCol": 0,
      "lastRow": 1,
      "lastCol": 0
    },
    {
      "name": "洛阳",
      "firstRow": 1,
      "firstCol": 1,
      "lastRow": 1,
      "lastCol": 1
    },
    {
      "name": "开封",
      "firstRow": 1,
      "firstCol": 2,
      "lastRow": 1,
      "lastCol": 3
    },
    {
      "name": "唐山",
      "firstRow": 1,
      "firstCol": 4,
      "lastRow": 2,
      "lastCol": 5
    },
    {
      "name": "石家庄",
      "firstRow": 1,
      "firstCol": 6,
      "lastRow": 1,
      "lastCol": 6
    },
    {
      "name": "保定",
      "firstRow": 1,
      "firstCol": 7,
      "lastRow": 1,
      "lastCol": 7
    },
    {
      "name": "古都",
      "firstRow": 2,
      "firstCol": 0,
      "lastRow": 2,
      "lastCol": 2
    },
    {
      "name": "事件",
      "firstRow": 2,
      "firstCol": 3,
      "lastRow": 2,
      "lastCol": 3
    },
    {
      "name": "秦皇岛",
      "firstRow": 2,
      "firstCol": 6,
      "lastRow": 2,
      "lastCol": 6
    },
    {
      "name": "河间",
      "firstRow": 2,
      "firstCol": 7,
      "lastRow": 2,
      "lastCol": 7
    }

  ]
}

5.导出的 Excel 表头如下：

在这里插入图片描述

猪悟道

关注

2
点赞
踩
7

收藏

觉得还不错? 一键收藏
打赏
0
评论
Java17 POI5.2.0 Excel 下拉框数据校验

Java POI 设置 Excel 下拉框、数据校验类和方法：SXSSFWorkbook、DataValidationHelper、DataValidationConstraint、CellRangeAddressList、XSSFDataValidation、createCustomConstraintExcel Head 通用表头自动生成方法
复制链接

扫一扫