java POI 工具类
1 导出Excel
2 导出带下拉列表联动的Excel
3 导出单元格合并的Excel
4 导出带日期校验的Excel
5 导出锁定指定列禁止修改的Excel
文章目录
java POI 工具类前言一、引用maven二、上代码1.ExcelTemplateBean 实体类2.ExcelTemplateUtil 工具类2. 使用2.1 普通导出：2.2下拉框限制，多级联动导出：2.3 合并单元格导出2.4 日期时间格式校验（禁止修改）导出2.5 锁定指定列禁止修改导出前言
需求java导出的Excel要添加指定列的下拉框限制。
提示：以下是本篇文章正文内容，下面案例可供参考
一、引用maven
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.15</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.0.1</version><scope>compile</scope></dependency>
二、上代码
1.ExcelTemplateBean 实体类
代码如下：
public class ExcelTemplateBean {// 数据页名private String dataSheetName = "sheet";// 字典页名private String dictSheetName = "dict";// 标题行private List<String> titles = new ArrayList<>();// 数据集合private List<Map<Integer, String>> dataList = new ArrayList<>();// 名称管理器-字典数据集合private Map<String, List<String>> dictMap = new HashMap<>();// 数据有效性公式-下标集合private Map<String, Integer> formulaIndex = new HashMap<>();// 默认开始日期private String startDate = "1970-01-01";// 默认结束日期private String endDate = "9999-12-31";// 日期格式验证private Set<Integer> dateFormulaIndex = new HashSet<>();// 上锁列-下标集合private Set<Integer> lockColumnIndex = new HashSet<>();// 是否上锁private Boolean lock = false;// 密码private String password = "";// 列宽private Integer columnWidth = 5000;// 隐藏字典页private Boolean dictSheetHidden = true;// 合并(4个参数，分别为起始行，结束行，起始列，结束列)// 行和列都是从0开始计数，且起始结束都会合并private List<List<Integer>> mergeRegion = new ArrayList<>();public ExcelTemplateBean() {}public ExcelTemplateBean(List<String> titles, List<Map<Integer, String>> dataList) {this.titles = titles;this.dataList = dataList;}/*** 4个参数，分别为起始行，结束行，起始列，结束列* @param firstRow* @param lastRow* @param firstCol* @param lastCol*/public void addMergeRegion(Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol) {List<Integer> region = new ArrayList<>();region.add(firstRow);region.add(lastRow);region.add(firstCol);region.add(lastCol);this.mergeRegion.add(region);}}
2.ExcelTemplateUtil 工具类
代码如下：
import mons.lang.StringUtils;import org.apache.poi.hssf.usermodel.DVConstraint;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.ss.util.CellRangeAddressList;import org.apache.poi.xssf.streaming.SXSSFSheet;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import java.io.OutputStream;import java.io.UnsupportedEncodingException;import .URLEncoder;import java.util.*;public class ExcelTemplateUtils {/*** 计算formula** @param offset 偏移量，如果给0，表示从A列开始，1，就是从B列* @param rowId 第几行* @param colCount 一共多少列* @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1*/public static String getRange(int offset, int rowId, int colCount) {if (colCount <= 0) {colCount = 1;}char start = (char) ('A' + offset);if (colCount <= 25) {char end = (char) (start + colCount - 1);return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;} else {char endPrefix = 'A';char endSuffix = 'A';if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）if ((colCount - 25) % 26 == 0) {// 边界值endSuffix = (char) ('A' + 25);} else {endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);}} else if (colCount > 51 && colCount <= 701) {// 51以上if ((colCount - 25) % 26 == 0) {endSuffix = (char) ('A' + 25);endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);} else {endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);endPrefix = (char) (endPrefix + (colCount - 25) / 26);}} else {if (colCount > 16383) {colCount = 16383;}char left = 'A';char centre = 'A';char right = 'A';if (colCount / 26 % 26 == 0) {centre = (char) ('A' + 25);// 边界值left = (char) (left + (colCount / 26 / 26 % 26 - 2));} else {left = (char) (left + (colCount / 26 / 26 % 26 - 1));centre = (char) (centre + (colCount / 26 % 26 - 1));}right = (char) (right + colCount % 26);return "$" + start + "$" + rowId + ":$" + left + centre + right + "$" + rowId;}return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;}}/*** 设置有效性，去名称管理器查询集合** @param formula 有效性：公式(名称管理器/左侧单元格)* @param dvHelper 数据约束辅助类* @param collIndex 限制列所在的下标*/public static DataValidation getDataValidation(String formula, DataValidationHelper dvHelper, Integer collIndex) {// 数据验证约束DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formula);// 公式CellRangeAddressList regions = new CellRangeAddressList(-1, -1, collIndex, collIndex);// 单元格，适用范围DataValidation dataValidation = dvHelper.createValidation(dvConstraint, regions);// 创建验证dataValidation.setEmptyCellAllowed(true); // 设置单元允许为空(不为null的画，输错了就从单元格出不来了)dataValidation.setSuppressDropDownArrow(true); // 设置下拉箭头dataValidation.setShowErrorBox(true); // 设置显示错误框;// dataValidation.setShowPromptBox(true); // 设置显示下拉框提示(一直有提示，很烦人的)dataValidation.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");// 设置输入信息提示信息return dataValidation; // 添加验证到表单}/*** 设置时间格式有效性，默认1970-01-01 到 9999-12-31* 默认格式："yyyy-MM-dd"** @param excel* @param dvHelper* @param collIndex* @return*/public static DataValidation getDateDataValidation(ExcelTemplateBean excel, DataValidationHelper dvHelper, Integer collIndex) {// 事件格式验证DataValidationConstraint dvConstraint = dvHelper.createDateConstraint(DVConstraint.ValidationType.DATE, excel.getStartDate(), excel.getEndDate(), "yyyy-MM-dd");CellRangeAddressList regions = new CellRangeAddressList(-1, -1, 8, 8);// 单元格，适用范围DataValidation dataValidation = dvHelper.createValidation(dvConstraint, regions);// 创建验证/*设置单元允许为空*/dataValidation.setEmptyCellAllowed(true);/* 设置显示错误框;*/dataValidation.setShowErrorBox(true);dataValidation.createErrorBox("格式错误", "请输入正确的日期格式！");return dataValidation; // 添加验证到表单}/*** 返回一个，有下拉，有数据的 workbook模板* 联动：/m0_37956938/article/details/78084503* 锁定：/minxl/p/10170806.html*/public static SXSSFWorkbook getSXSSFWorkbookTemplate(ExcelTemplateBean excel) {// 创建一个excelSXSSFWorkbook book = new SXSSFWorkbook();// 创建需要用户填写的sheetSXSSFSheet dataSheet = book.createSheet(excel.getDataSheetName());// 单元格，格式化样式DataFormat dataFormat = book.createDataFormat();// 锁定样式CellStyle lockStyle = book.createCellStyle();lockStyle.setLocked(true);lockStyle.setDataFormat(dataFormat.getFormat("@")); // 设置单元格文本样式为文本(长数字显示会以科学计数法展示)// 不锁定样式CellStyle unlockStyle = book.createCellStyle();unlockStyle.setLocked(false);unlockStyle.setDataFormat(dataFormat.getFormat("@")); // 设置单元格文本样式为文本// lockstyle.setWrapText(true); // 开启单元格自动换行(默认关闭，页面有点乱)// unlockstyle.setWrapText(true); // 开启单元格自动换行// 数据表，标题写入Row titlesRow = dataSheet.createRow(0);// 开启锁定（默认整张表都锁定）titlesRow.setRowStyle(lockStyle);List<String> titles = excel.getTitles();for (int i = 0; i < titles.size(); i++) {Cell cell = titlesRow.createCell(i);// 设置空白行列默认为不锁定if (excel.getLockColumnIndex().contains(i)) {dataSheet.setDefaultColumnStyle(i, lockStyle);} else {dataSheet.setDefaultColumnStyle(i, unlockStyle);}cell.setCellValue(titles.get(i));if (excel.getColumnWidth() != null) {dataSheet.setColumnWidth(i, excel.getColumnWidth());}// 填充数据时，此单元格锁定：cell.setCellStyle(lockStyle);// 填充数据时，此单元格解除锁定：cell.setCellStyle(unlockStyle);}// 填充数据List<Map<Integer, String>> dataList = excel.getDataList();for (int i = 0; i < dataList.size(); i++) {Row row = dataSheet.createRow(i + 1);Map<Integer, String> data = dataList.get(i);for (Map.Entry<Integer, String> entry : data.entrySet()) {Cell cell = row.createCell(entry.getKey());if (excel.getLockColumnIndex().contains(entry.getKey())) {cell.setCellStyle(lockStyle);} else {cell.setCellStyle(unlockStyle);}cell.setCellValue(entry.getValue());}}//创建一个专门用来存放字典信息的隐藏sheet页//因此也不能在现实页之前创建，否则无法隐藏。Sheet dictSheet = book.createSheet(excel.getDictSheetName());//这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确book.setSheetHidden(book.getSheetIndex(dictSheet), excel.getDictSheetHidden());Map<String, List<String>> dictMap = excel.getDictMap();// 往字典表，写入数据，并添加到名称管理器int rowId = 0;Set<String> keys = dictMap.keySet();Iterator<String> iterator = keys.iterator();while (iterator.hasNext()) {String key = iterator.next();List<String> childrenList = dictMap.get(key);if (childrenList == null) {childrenList = new ArrayList<>();}key = replaceValue(key);Row row = dictSheet.createRow(rowId++);row.setRowStyle(lockStyle);row.createCell(0).setCellValue(key);for (int i = 0; i < childrenList.size(); i++) {Cell cell = row.createCell(i + 1);String value = replaceValue(childrenList.get(i));cell.setCellValue(value);}// 添加名称管理器String range = getRange(1, rowId, childrenList.size());Name name = book.createName();//key不可重复name.setNameName(key);String formula = excel.getDictSheetName() + "!" + range;name.setRefersToFormula(formula);}// 单元格合并for (List<Integer> cell : excel.getMergeRegion()) {if (cell.size() == 4) {CellRangeAddress region = new CellRangeAddress(cell.get(0), cell.get(1), cell.get(2), cell.get(3));dataSheet.addMergedRegion(region);}}// 数据验证助手DataValidationHelper dvHelper = dataSheet.getDataValidationHelper();// 下拉列表验证Map<String, Integer> formulaIndex = excel.getFormulaIndex();for (Map.Entry<String, Integer> entry : formulaIndex.entrySet()) {dataSheet.addValidationData(getDataValidation(entry.getKey(), dvHelper, entry.getValue()));}// 时间格式，时间区间验证for (Integer index : excel.getDateFormulaIndex()) {dataSheet.addValidationData(getDateDataValidation(excel, dvHelper, index));}// 上锁if (excel.getLock()) {dataSheet.protectSheet(excel.getPassword());}dictSheet.protectSheet(excel.getPassword());return book;}public static String replaceValue(String value) {// 只保留，汉字，字母（大小写），数字（0-9），下划线（_）String regex = "[^\\u4e00-\\u9fa50-9a-zA-Z]+";return value.replaceAll(regex, "_");}
2. 使用
2.1 普通导出：
// 文件名String fileName = "历史" + System.currentTimeMillis() + ".xlsx";// 标题行（下标即列的位置）List<String> titles = getTitles2();// 数据集合（map为一行的数据，map的key即列的下标）List<Map<Integer, String>> dataList = getDataList2(list);ExcelTemplateBean excel = new ExcelTemplateBean(titles, dataList);SXSSFWorkbook workbook = ExcelTemplateUtils.getSXSSFWorkbookTemplate(excel);// 使用io导出workbook 就可以了
2.2下拉框限制，多级联动导出：
本质是创建了一个sheet2，为sheet1的指定列创建一个数据验证的规则，指向sheet2或者指定列
// 数据有效性字典 ，用于限制下拉框的内容Map<String, List<String>> dictMap = new HashMap<>();//得到第一级省名称，放在列表里ArrayList<String> provinceList = new ArrayList<>();provinceList.add("上海市_310100000000");dictMap.put("省市列表", provinceList);// 获取 第二列(传感器类型)，第三列(所属应用)数据Map<String, List<String>> sensorMap = this.getAppSensor();List<String> appList = sensorMap.get("app");dictMap.put("所属应用", appList);// 所属应用List<String> typeList = sensorMap.get("sensor");dictMap.put("传感器类型", typeList);// 传感器类型List<String> commisionNameString = this.getCommisionNameList();dictMap.put("委办类型", commisionNameString);// 传感器类型Map<String, Integer> formulaIndex = new HashMap<>();// 数据有效性，直接来自名称管理器（只允许中文、数字、字母、下划线）formulaIndex.put("传感器类型", 2);formulaIndex.put("所属应用", 3);formulaIndex.put("省市列表", 4);formulaIndex.put("上海市_310100000000", 5);//区// 数据有效性，直接来前一列，间接查询名称管理器// formulaIndex.put("INDIRECT($D1)",4);//区formulaIndex.put("INDIRECT($F1)", 6);//街道formulaIndex.put("INDIRECT($G1)", 7);//片区formulaIndex.put("INDIRECT($H1)", 8);//居委formulaIndex.put("INDIRECT($I1)", 9);//小区formulaIndex.put("委办类型", 10);String password = "1234567890";ExcelTemplateBean excel = new ExcelTemplateBean();// 将数据放入 excel 对象// 省略代码…………SXSSFWorkbook workbook = ExcelTemplateUtils.getSXSSFWorkbookTemplate(excel);// 使用io导出workbook 就可以了
2.3 合并单元格导出
// 文件名String fileName = "类型统计表" + System.currentTimeMillis() + ".xlsx";// 标题行List<String> titles = getTitles();// 数据集合List<Map<Integer, String>> dataList = getDataList(list);ExcelTemplateBean excel = new ExcelTemplateBean(titles, dataList);// 设置合并单元格（4个参数，分别为起始行，结束行，起始列，结束列）excel.addMergeRegion(0, 1, 0, 0);excel.addMergeRegion(0, 1, 1, 1);excel.addMergeRegion(0, 0, 2, 4);excel.addMergeRegion(0, 0, 5, 7);excel.addMergeRegion(0, 0, 8, 10);excel.addMergeRegion(0, 0, 11, 13);excel.addMergeRegion(0, 0, 14, 16);SXSSFWorkbook workbook = ExcelTemplateUtils.getSXSSFWorkbookTemplate(excel);// 设置居中CellStyle cellStyle = workbook.createCellStyle();cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setAlignment(HorizontalAlignment.CENTER);SXSSFSheet sheetAt = workbook.getSheetAt(0);sheetAt.getRow(0).getCell(0).setCellStyle(cellStyle);sheetAt.getRow(0).getCell(1).setCellStyle(cellStyle);sheetAt.getRow(0).getCell(2).setCellStyle(cellStyle);sheetAt.getRow(0).getCell(5).setCellStyle(cellStyle);sheetAt.getRow(0).getCell(8).setCellStyle(cellStyle);sheetAt.getRow(0).getCell(11).setCellStyle(cellStyle);sheetAt.getRow(0).getCell(14).setCellStyle(cellStyle);// 使用io导出workbook 就可以了
2.4 日期时间格式校验（禁止修改）导出
// 默认使用了"yyyy-MM-dd"日期格式，如有需要请自行修改:ExcelTemplateUtils.getDateDataValidation()// 文件名String fileName = "历史" + System.currentTimeMillis() + ".xlsx";// 标题行（下标即列的位置）List<String> titles = getTitles2();// 数据集合（map为一行的数据，map的key即列的下标）List<Map<Integer, String>> dataList = getDataList2(list);// 需要进行时间校验的列下标Set<Integer> dateFormulaIndex = new HashSet<>();dateFormulaIndex.add(7);dateFormulaIndex.add(8);ExcelTemplateBean excel = new ExcelTemplateBean(titles, dataList);excel.setStartDate("-01-01"); // 最小日期excel.setEndDate("2025-12-31"); // 最大日期excel.setDateFormulaIndex(dateFormulaIndex);SXSSFWorkbook workbook = ExcelTemplateUtils.getSXSSFWorkbookTemplate(excel);// 使用io导出workbook 就可以了
2.5 锁定指定列禁止修改导出
// 默认使用了"yyyy-MM-dd"日期格式，如有需要请自行修改:ExcelTemplateUtils.getDateDataValidation()// 文件名String fileName = "历史" + System.currentTimeMillis() + ".xlsx";// 标题行（下标即列的位置）List<String> titles = getTitles2();// 数据集合（map为一行的数据，map的key即列的下标）List<Map<Integer, String>> dataList = getDataList2(list);// 禁止修改列的下标Set<Integer> lockColumnIndex= new HashSet<>();lockColumnIndex.add(5);lockColumnIndex.add(6);ExcelTemplateBean excel = new ExcelTemplateBean(titles, dataList);excel.setLockColumnIndex(lockColumnIndex);SXSSFWorkbook workbook = ExcelTemplateUtils.getSXSSFWorkbookTemplate(excel);// 使用io导出workbook 就可以了
