机关网络 网站 建设,免费货源网站免费版权,WordPress缩略图太模糊,wordpress知乎由于最近做的功能#xff0c;需要将接口返回的数据列表#xff0c;输出到excel中#xff0c;以供后续导入#xff0c;且网上现有的封装#xff0c;使用起来都较为麻烦#xff0c;故参考已有做法封装了工具类。
使用apache poi实现excel联动下拉框思路
创建隐藏单元格需要将接口返回的数据列表输出到excel中以供后续导入且网上现有的封装使用起来都较为麻烦故参考已有做法封装了工具类。
使用apache poi实现excel联动下拉框思路
创建隐藏单元格存储下拉数据创建名称管理器使用indirect表达式进行联动
添加依赖
!--Java程序对Microsoft Office格式档案读和写的功能--
dependencygroupIdorg.apache.poi/groupIdartifactIdpoi/artifactIdversion5.2.2/version
/dependency
dependencygroupIdorg.apache.poi/groupIdartifactIdpoi-ooxml/artifactIdversion5.2.2/version
/dependency直接上代码
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;import java.util.List;
import java.util.Map;
import java.util.Set;/*** excel验证工具类** author chenchuancheng github.com/meethigher* since 2023/08/20 23:55*/
public class ExcelValidationUtils {private static final int minRow 1;private static final int maxRow 100;private static final boolean debugHideSheet true;/*** 创建一个xlsx** return {link XSSFWorkbook}*/public static XSSFWorkbook createOneXLSX() {return new XSSFWorkbook();}/*** 为xlsx添加一个sheet** param wb xlsx* param sheetName sheet名* param headers 首行标题头* return sheet*/public static XSSFSheet addOneSheet(XSSFWorkbook wb, String sheetName, String[] headers) {XSSFSheet st wb.createSheet(sheetName);//表头样式CellStyle style wb.createCellStyle();style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式//字体样式Font fontStyle wb.createFont();fontStyle.setFontName(微软雅黑);fontStyle.setFontHeightInPoints((short) 12);style.setFont(fontStyle);//单元格格式为文本XSSFDataFormat format wb.createDataFormat();style.setDataFormat(format.getFormat());//写标题XSSFRow row st.createRow(0);st.createFreezePane(0, 1, 0, 1);for (int i 0; i headers.length; i) {String value headers[i];XSSFCell cell row.createCell(i);st.setColumnWidth(i, value.length() * 1000);cell.setCellStyle(style);st.setDefaultColumnStyle(i, style);cell.setCellValue(value);}return st;}/*** 添加两层级联数据** param wb xlsx* param targetSheet 目标sheet* param linkageData 两层级联数据* param parentCol 父列* param childCol 孩子列* param parentColIdentifier 父列标识符* return {link XSSFSheet}*/public static XSSFSheet addLinkageDataValidation(XSSFWorkbook wb, XSSFSheet targetSheet, MapString, ListString linkageData,int parentCol, int childCol, String parentColIdentifier) {XSSFSheet hideSt wb.createSheet();wb.setSheetHidden(wb.getSheetIndex(hideSt), !debugHideSheet);int rowId 0;SetString keySet linkageData.keySet();for (String parent : keySet) {ListString sonList linkageData.get(parent);XSSFRow row hideSt.createRow(rowId);row.createCell(0).setCellValue(parent);for (int i 0; i sonList.size(); i) {XSSFCell cell row.createCell(i 1);cell.setCellValue(sonList.get(i));}// 添加名称管理器,1表示b列,从b列开始往后都是子级String range getRange(1, rowId, sonList.size());Name name wb.createName();name.setNameName(parent);String formula hideSt.getSheetName() ! range;name.setRefersToFormula(formula);}//创建表达式校验XSSFDataValidationHelper helper new XSSFDataValidationHelper(targetSheet);// //父级校验如需生成更多用户手动拖拽下拉即可。此操作会导致数组内容总长度超过255时报错
// DataValidation parentValidation helper.createValidation(helper.createExplicitListConstraint(keySet.toArray(new String[0])),
// new CellRangeAddressList(minRow, maxRow, parentCol, parentCol));
// parentValidation.createErrorBox(错误, 请选择正确的父级类型);
// parentValidation.setShowErrorBox(true);
// parentValidation.setSuppressDropDownArrow(true);
// targetSheet.addValidationData(parentValidation);//解决长度为255的问题Name name wb.createName();name.setNameName(hideSt.getSheetName());name.setRefersToFormula(hideSt.getSheetName() !$A$1:$A$ keySet.size());DataValidation parentValidation helper.createValidation(helper.createFormulaListConstraint(hideSt.getSheetName()), new CellRangeAddressList(minRow, maxRow, parentCol, parentCol));parentValidation.createErrorBox(错误, 请选择正确的父级类型);parentValidation.setShowErrorBox(true);targetSheet.addValidationData(parentValidation);//子级校验如需生成更多用户手动拖拽下拉即可for (int i minRow; i maxRow; i) {DataValidation childValidation helper.createValidation(helper.createFormulaListConstraint(INDIRECT( parentColIdentifier (i 1) )),new CellRangeAddressList(i, i, childCol, childCol));childValidation.createErrorBox(错误, 请选择正确的子级类型);childValidation.setShowErrorBox(true);childValidation.setSuppressDropDownArrow(true);targetSheet.addValidationData(childValidation);}return hideSt;}/*** 添加简单下拉列表验证-下拉列表总内容不超过255字符** param st sheet* param dropDownList 下拉列表数据* param firstCol 开始列从0开始* param lastCol 结束列从0开始*/public static void addSimpleDropDownListValidation(XSSFSheet st, String[] dropDownList, int firstCol, int lastCol) {XSSFDataValidationHelper helper new XSSFDataValidationHelper(st);XSSFDataValidationConstraint constraint (XSSFDataValidationConstraint) helper.createExplicitListConstraint(dropDownList);CellRangeAddressList addressList new CellRangeAddressList(minRow, maxRow, firstCol, lastCol);XSSFDataValidation validation (XSSFDataValidation) helper.createValidation(constraint, addressList);validation.setSuppressDropDownArrow(true);validation.setShowErrorBox(true);st.addValidationData(validation);}/*** 添加复杂下拉列表验证-下拉列表总内容允许超过255字符** param wb xlsx* param dropDownList 下拉列表数据* param firstCol 开始列从0开始* param lastCol 结束列从0开始*/public static void addComplexDropDownListValidation(XSSFWorkbook wb, XSSFSheet st, String[] dropDownList, int firstCol, int lastCol) {XSSFSheet hideSt wb.createSheet();wb.setSheetHidden(wb.getSheetIndex(hideSt), !debugHideSheet);XSSFDataValidationHelper helper new XSSFDataValidationHelper(st);for (int i 0, length dropDownList.length; i length; i) {String value dropDownList[i];XSSFRow row hideSt.createRow(i);XSSFCell cell row.createCell(0);cell.setCellValue(value);}//解决长度为255的问题Name name wb.createName();name.setNameName(hideSt.getSheetName());name.setRefersToFormula(hideSt.getSheetName() !$A$1:$A$ dropDownList.length);DataValidation parentValidation helper.createValidation(helper.createFormulaListConstraint(hideSt.getSheetName()), new CellRangeAddressList(minRow, maxRow, firstCol, lastCol));parentValidation.createErrorBox(错误, 请选择正确的类型);parentValidation.setShowErrorBox(true);st.addValidationData(parentValidation);}/*** 计算formula** param offset 偏移量如果给0表示从A列开始1就是从B列* param rowId 第几行* param colCount 一共多少列* return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1*/private static String getRange(int offset, int rowId, int colCount) {char start (char) (A offset);if (colCount 25) {char end (char) (start colCount - 1);return $ start $ rowId :$ end $ rowId;} else {char endPrefix A, endSuffix;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 {// 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);}}return $ start $ rowId :$ endPrefix endSuffix $ rowId;}}
}使用示例
public class TestExportExcelWithValidation {private final static String[] headers new String[]{性别,省,市,区,};private static MapString, ListString 省级() {MapString, ListString map new HashMap();map.put(湖北省, Arrays.asList(武汉市, 襄阳市));map.put(吉林省, Arrays.asList(长春市, 吉林市));return map;}private static MapString, ListString 市级() {MapString, ListString map new HashMap();map.put(武汉市, Arrays.asList(洪山区, 江夏区));map.put(长春市, Arrays.asList(宽城区, 南关区));return map;}public static void main(String[] args) throws Exception {XSSFWorkbook wb createOneXLSX();XSSFSheet st addOneSheet(wb, data, headers);addSimpleDropDownListValidation(st, new String[]{男, 女}, 0, 0);addLinkageDataValidation(wb, st, 省级(), 1, 2, B);addLinkageDataValidation(wb, st, 市级(), 2, 3, C);wb.write(new FileOutputStream(aaa.xlsx));}
}最终结果展示如图
