Java导出Excel时生成下拉框(poi)
最近项目升级，需要在excel导出时根据列名，有候选码值的要求下拉展示，所以对这块记录一下。
使用的POI版本为3.17。导出时添加下拉框分两种情况，以下拉框字符数是否超过255区分，两种情况的参考代码如下：
一、简单类型的下拉框，码值不超过255个字符时使用。
1 package test; 2 3 import org.apache.poi.hssf.usermodel.*; 4 import org.apache.poi.ss.util.CellRangeAddressList; 5 import org.junit.Test; 6 7 import java.io.FileOutputStream; 8 import java.io.IOException; 9 import java.util.HashMap; 10 import java.util.Map; 11 12 /** 13 * @ClassName test2 14 * @Description: TODO 15 * @Author zyf 16 * @Date 2020/8/4 17 * @Version V1.0 18 **/ 19 public class test2 { 20 21 @Test 22 public void test() throws IOException { 23 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); 24 HSSFSheet sheet = hssfWorkbook.createSheet(); 25 HSSFRow row = sheet.createRow(0); 26 row.createCell(0).setCellValue("名称"); 27 row.createCell(1).setCellValue("类型"); 28 String col = "JGLX"; //机构类型 29 Map<String, String> boxMap = new HashMap<>(); 30 boxMap.put("JGLX", "1类型,2类型,3类型"); 31 //指定将下拉框添加至1-10行，0-0列。即第一列的第2到11行 32 HSSFDataValidation dataValidation = createBox(col, boxMap , 1 , 10 , 0 , 0); 33 if(dataValidation != null) { 34 sheet.addValidationData(dataValidation); 35 } 36 FileOutputStream out = new FileOutputStream("C:\\Users\\yf\\Desktop\\test1.xls"); 37 hssfWorkbook.write(out); 38 out.close(); 39 } 40 41 /** 42 * excel导出，有码值的数据使用下拉框展示。 43 * @param col 列名 44 * @param boxMap 码值集合 45 * @param firstRow 插入下拉框开始行号 46 * @param lastRow 插入下拉框结束行号 47 * @param firstCol 插入下拉框开始列号 48 * @param lastCol 插入下拉框结束行号 49 * @return 50 */ 51 public HSSFDataValidation createBox(String col, Map<String , String> boxMap , int firstRow, int lastRow, int firstCol, int lastCol) { 52 HSSFDataValidation dataValidation = null; 53 //查询码值表 54 String cols = ""; 55 if(null != boxMap.get(col)) { 56 cols = boxMap.get(col); 57 } 58 //设置下拉框 59 if(cols.length() > 0 && null != cols) { 60 String str[] = cols.split(","); 61 //指定0-9行，0-0列为下拉框 62 CellRangeAddressList cas = new CellRangeAddressList(firstRow , lastRow , firstCol , lastCol); 63 //创建下拉数据列 64 DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(str); 65 //将下拉数据放入下拉框 66 dataValidation = new HSSFDataValidation(cas, dvConstraint); 67 } 68 return dataValidation; 69 } 70 71 72 }
二、复杂类型下拉框、码值超过255个字符 1 package test;
2 3 import org.apache.poi.hssf.usermodel.*; 4 import org.apache.poi.ss.usermodel.Name; 5 import org.apache.poi.ss.util.CellRangeAddressList; 6 import org.junit.Test; 7 8 import java.io.FileOutputStream; 9 import java.io.IOException; 10 import java.util.HashMap; 11 import java.util.List; 12 import java.util.Map; 13 14 /** 15 * @ClassName test3 16 * @Description: TODO 17 * @Author zyf 18 * @Date 2020/8/4 19 * @Version V1.0 20 **/ 21 public class test3 { 22 23 @Test 24 public void test() throws IOException { 25 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); 26 HSSFSheet sheet = hssfWorkbook.createSheet(); 27 HSSFRow row = sheet.createRow(0); 28 row.createCell(0).setCellValue("名称"); 29 row.createCell(1).setCellValue("类型"); 30 String col = "JGLX"; //机构类型 31 Map<String, String> boxMap = new HashMap<>(); 32 boxMap.put("JGLX", "1类型,2类型,3类型"); 33 int i =0; 34 HSSFDataValidation dataValidation = createBox1(hssfWorkbook , col, boxMap , 10 , i , 0); 35 if(dataValidation != null) { 36 sheet.addValidationData(dataValidation); 37 } 38 FileOutputStream out = new FileOutputStream("C:\\Users\\yf\\Desktop\\test22.xls"); 39 hssfWorkbook.write(out); 40 out.close(); 41 } 42 /** 43 * excel导出，有码值的数据使用下拉框展示。解决下拉框最多255个字符的问题。 44 * 原理为新建一个隐藏状态的sheet页，用来存储下拉框的值。 45 * @param wb 工作簿 HSSFWorkbook 46 * @param col 当前列名 47 * @param boxMap 码值集合 48 * @param rows 正常sheet页数据，用来指定哪些行需要添加下拉框 49 * @param i 多个码值需要添加下拉，隐藏状态的sheet页名称不能重复，添加i值区分。 50 * @param colToIndex 用来指定哪些列需要添加下拉框 51 * @return dataValidation 52 */ 53 public HSSFDataValidation createBox1(HSSFWorkbook wb, String col, Map<String , String> boxMap, int rows, int i, int colToIndex) { 54 HSSFDataValidation dataValidation = null; 55 String cols = ""; 56 //查询码值集合,获取当前列的码值。 57 if(null != boxMap.get(col)) { 58 cols = boxMap.get(col); 59 } 60 //新建隐藏状态的sheet，用来存储码值。 61 if(cols.length() > 0 && null != cols) { 62 String str[] = cols.split(","); 63 //创建sheet页 64 HSSFSheet sheet = wb.createSheet("hidden"+i); 65 //向创建的sheet页添加码值数据。 66 for (int i1 = 0; i1 < str.length; i1++) { 67 HSSFRow row = sheet.createRow(i1); 68 HSSFCell cell = row.createCell((int) 0); 69 cell.setCellValue(str[i1]); 70 } 71 //将码值sheet页做成excel公式 72 Name namedCell = wb.createName(); 73 namedCell.setNameName("hidden"+i); 74 namedCell.setRefersToFormula("hidden"+i+"!$A$1:$A$" + str.length); 75 //确定要在哪些单元格生成下拉框 76 DVConstraint dvConstraint = DVConstraint.createFormulaListConstraint("hidden"+i); 77 CellRangeAddressList regions = new CellRangeAddressList(1 , rows , colToIndex , colToIndex); 78 dataValidation = new HSSFDataValidation(regions, dvConstraint); 79 //隐藏码值sheet页 80 int sheetNum = wb.getNumberOfSheets();
for(int n=1; n<sheetNum; n++) {
wb.setSheetHidden(n, true);
}
81 } 82 return dataValidation; 83 } 84 }
