一、工具类
1.ExcelUtil2.Pom 二、生成文件
1.下拉框2.数据校验 三、Excel 命令
1.获取活动单元格：=INDIRECT("R"&ROW()&"C"&CELL(),0)2.获取指定单元格：=F13.获取前一列对应行的值：=INDIRECT("R"&ROW()&"C"&COLUMN()-1,0)4.获取指定单元格数据字长：=LEN(F2)5.获取指定单元格数据字节长：=LENB(F2)6.判断：=IF(LENB(F2)>2,"大于","小于或等于")
一、工具类 1.ExcelUtilpackage 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;
public class ExcelUtil {
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 COM_BOX_LIST = new ArrayList<>(1);
public static void creat() {
SXSSFWorkbook workbook = new SXSSFWorkbook(-1);
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("守护");
setDataLengthLimit(sheet,"=((LENB(INDIRECT("F"&ROW())) >= 1) * (LENB(INDIRECT("F"&ROW())) <= 10))");
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);
}
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();
}
}
}
}
private static void setComBox(SXSSFSheet sheet, List 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++;
}
}
private static void setPullBox(SXSSFSheet sheet, List 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));
}
}
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;
}
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
二、生成文件 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,“大于”,“小于或等于”)4.0.0 org.springframework.boot spring-boot-starter-parent 2.6.1 com.demo exceldemo 1.0-SNAPSHOT 17 17 org.springframework.boot spring-boot-starter-web org.projectlombok lombok org.apache.poi poi 5.2.0 org.apache.poi poi-ooxml 5.2.0
