Java 设置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
}
]
}