使用poi解决导出excel内下拉框枚举项较多的问题

废话少说，直接上代码：

  1 package com.fst.attachment.controller;
  2 
  3 import java.io.FileOutputStream;
  4 
  5 import org.apache.poi.hssf.usermodel.DVConstraint;
  6 import org.apache.poi.hssf.usermodel.HSSFDataValidation;
  7 import org.apache.poi.hssf.usermodel.HSSFSheet;
  8 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  9 import org.apache.poi.ss.usermodel.DataValidation;
 10 import org.apache.poi.ss.usermodel.DataValidationConstraint;
 11 import org.apache.poi.ss.usermodel.DataValidationHelper;
 12 import org.apache.poi.ss.usermodel.Name;
 13 import org.apache.poi.ss.usermodel.Sheet;
 14 import org.apache.poi.ss.usermodel.Workbook;
 15 import org.apache.poi.ss.util.CellRangeAddressList;
 16 import org.apache.poi.xssf.streaming.SXSSFWorkbook;
 17 import org.apache.poi.xssf.usermodel.XSSFDataValidation;
 18 import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
 19 import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
 20 import org.apache.poi.xssf.usermodel.XSSFSheet;
 21 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 22 
 23 public class TestPOIDropDown {
 24 
 25     public static void main(String[] args) throws Exception {
 26         // 创建枚举项
 27         int len = 200;
 28         String[] datas = new String[len];
 29         for (int i = 0; i < len; i++) {
 30             datas[i] = i + "我是下拉框枚举项---";
 31         }
 32 
 33         // ----------------生成--------------------------------
 34         // 方法一：使用createExplicitListConstraint实现，缺陷为：
 35         // 只能满足较少枚举项的下拉框，最好不要超过20个，具体个数根据枚举字段长度而定。
 36         // Workbook workbook = HSSFSetDropDown(datas);
 37         // Workbook workbook = XSSFSetDropDown(datas);
 38         // Workbook workbook = SXSSFSetDropDown(datas);
 39         /*
 40          * 简单比较HSSF、XSSF、SXSSF： - 由于新的XSSF支持Excel 2007 OOXML（.xlsx）文件是基于XML的，
 41          * 因此处理它们的内存占用量高于旧版HSSF支持的（.xls）二进制文件。 -
 42          * SXSSF（3.8-beta3之后支持）在生成非常大的电子表格时使用， 相较于XSSF,其在某个时间点只能访问有限数量的行。
 43          * http://poi.apache.org/components/spreadsheet/
 44          */
 45 
 46         // 方法二：使用createFormulaListConstraint实现，其适用于较多枚举项的下拉框，
 47         // 实现步骤大致为：创建一个隐藏的sheet，并往里放入枚举项，然后在第一个sheet内增加关联关系
 48         Workbook workbook = XSSFSetDropDownAndHidden(datas);
 49 
 50         // 输出
 51         FileOutputStream stream = new FileOutputStream("d:\testDropDown.xlsx");
 52         workbook.write(stream);
 53         stream.close();
 54     }
 55     
 56 
 57     /**
 58      * 使用createFormulaListConstraint实现下拉框
 59      * @param formulaString
 60      * @return
 61      */
 62     public static Workbook XSSFSetDropDownAndHidden(String[] formulaString) {
 63         Workbook workbook = new XSSFWorkbook();
 64         Sheet sheet = workbook.createSheet("下拉列表测试");
 65         // 创建sheet，写入枚举项
 66         Sheet hideSheet = workbook.createSheet("hiddenSheet");
 67         for (int i = 0; i < formulaString.length; i++) {
 68             hideSheet.createRow(i).createCell(0).setCellValue(formulaString[i]);
 69         }
 70         // 创建名称，可被其他单元格引用
 71         Name category1Name = workbook.createName();
 72         category1Name.setNameName("hidden");
 73         // 设置名称引用的公式
 74         // 使用像'A1：B1'这样的相对值会导致在Microsoft Excel中使用工作簿时名称所指向的单元格的意外移动，
 75         // 通常使用绝对引用，例如'$A$1:$B$1'可以避免这种情况。
 76         // 参考： http://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Name.html
 77         category1Name.setRefersToFormula("hiddenSheet!" + "$A$1:$A$" + formulaString.length);
 78         // 获取上文名称内数据
 79         DataValidationHelper helper = sheet.getDataValidationHelper();
 80         DataValidationConstraint constraint = helper.createFormulaListConstraint("hidden");
 81         // 设置下拉框位置
 82         CellRangeAddressList addressList = new CellRangeAddressList(0, 200, 0, 0);
 83         DataValidation dataValidation = helper.createValidation(constraint, addressList);
 84         // 处理Excel兼容性问题
 85         if (dataValidation instanceof XSSFDataValidation) {
 86             // 数据校验
 87             dataValidation.setSuppressDropDownArrow(true);
 88             dataValidation.setShowErrorBox(true);
 89         } else {
 90             dataValidation.setSuppressDropDownArrow(false);
 91         }
 92         // 作用在目标sheet上
 93         sheet.addValidationData(dataValidation);
 94         // 设置hiddenSheet隐藏
 95         workbook.setSheetHidden(1, true);
 96         return workbook;
 97     }
 98     
 99 
100     /**
101      * 使用较早版本的 HSSF用户模型设置表格下拉框 缺陷：下拉框数据量超过一定数量时，系统抛异常。
102      * 
103      * @param formulaString
104      * 
105      */
106     public static Workbook HSSFSetDropDown(String[] formulaString) {
107         HSSFWorkbook workbook = new HSSFWorkbook();
108         HSSFSheet sheet = workbook.createSheet("下拉列表测试");
109         // 加载下拉列表内容
110         DVConstraint constraint = DVConstraint.createExplicitListConstraint(formulaString);
111         // 设置数据有效性加载在哪个单元格上。
112         // 四个参数分别是：起始行、终止行、起始列、终止列
113         CellRangeAddressList regions = new CellRangeAddressList(0, 200, 0, 0);
114         // 数据有效性对象
115         DataValidation dataValidation = new HSSFDataValidation(regions, constraint);
116         sheet.addValidationData(dataValidation);
117         return workbook;
118     }
119 
120     /**
121      * 使用 XSSF用户模型设置表格下拉框，多用来处理xlsx后缀的excel 缺陷：下拉框数据量超过一定数量时，文件打不开。
122      * 
123      * @param formulaString
124      * 
125      */
126     public static Workbook XSSFSetDropDown(String[] formulaString) {
127         XSSFWorkbook workbook = new XSSFWorkbook();
128         XSSFSheet sheet = workbook.createSheet("下拉列表测试");
129         XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
130         XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
131                 .createExplicitListConstraint(formulaString);
132         CellRangeAddressList addressList = null;
133         XSSFDataValidation validation = null;
134         for (int i = 0; i < 500; i++) {
135             addressList = new CellRangeAddressList(i, i, 0, 0);
136             validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
137             // 07默认setSuppressDropDownArrow(true);
138             // validation.setSuppressDropDownArrow(true);
139             // validation.setShowErrorBox(true);
140             sheet.addValidationData(validation);
141         }
142         return workbook;
143     }
144 
145     /**
146      * 使用 SXSSF用户模型设置表格下拉框 缺陷：下拉框数据量超过一定数量时，文件打不开。
147      * 
148      * @param formulaString
149      * 
150      */
151     public static Workbook SXSSFSetDropDown(String[] formulaString) {
152         SXSSFWorkbook workbook = new SXSSFWorkbook();
153         Sheet sheet = workbook.createSheet("下拉列表测试");
154         // 加载下拉列表内容
155         DataValidationHelper helper = sheet.getDataValidationHelper();
156         DataValidationConstraint constraint = helper.createExplicitListConstraint(formulaString);
157         // 设置下拉框位置
158         CellRangeAddressList addressList = null;
159         addressList = new CellRangeAddressList(0, 500, 0, 0);
160         DataValidation dataValidation = helper.createValidation(constraint, addressList);
161         // 处理Excel兼容性问题
162         if (dataValidation instanceof XSSFDataValidation) {
163             // 数据校验
164             dataValidation.setSuppressDropDownArrow(true);
165             dataValidation.setShowErrorBox(true);
166         } else {
167             dataValidation.setSuppressDropDownArrow(false);
168         }
169         sheet.addValidationData(dataValidation);
170         return workbook;
171     }
172 
173 }

demo：

  1 package logic.bsc.excelTemplateExport;
  2 
  3 import java.io.IOException;
  4 import java.io.PrintWriter;
  5 
  6 import javax.servlet.ServletException;
  7 import javax.servlet.ServletOutputStream;
  8 import javax.servlet.http.HttpServlet;
  9 import javax.servlet.http.HttpServletRequest;
 10 import javax.servlet.http.HttpServletResponse;
 11 import javax.servlet.http.HttpSession;
 12 
 13 import logic.oec.OecToExcel;
 14 import logic.pxks.ExcelOut;
 15 
 16 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 17 import org.apache.poi.ss.usermodel.DataValidation;
 18 import org.apache.poi.ss.usermodel.DataValidationConstraint;
 19 import org.apache.poi.ss.usermodel.DataValidationHelper;
 20 import org.apache.poi.ss.util.CellRangeAddressList;
 21 import org.apache.poi.xssf.usermodel.XSSFDataValidation;
 22 import org.apache.poi.xssf.usermodel.XSSFName;
 23 import org.apache.poi.xssf.usermodel.XSSFRow;
 24 import org.apache.poi.xssf.usermodel.XSSFSheet;
 25 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 26 
 27 import com.usrObj.User;
 28 import com.yunhe.tools.Dates;
 29 import com.yunhe.tools.Excels;
 30 import com.yunhe.tools.Htmls;
 31 
 32 
 33 public class ExcelTDSExport extends HttpServlet {
 34 
 35     /** 转码成 UTF-8
 36      * Constructor of the object.
 37      */
 38     public ExcelTDSExport() {
 39         super();
 40     }
 41 
 42     /** 转码成 UTF-8
 43      * Destruction of the servlet. <br>
 44      */
 45     public void destroy() {
 46         super.destroy(); // Just puts "destroy" string in log
 47         // Put your code here
 48     }
 49 
 50     /** 转码成 UTF-8
 51      * The doGet method of the servlet. <br>
 52      *
 53      * This method is called when a form has its tag value method equals to get.
 54      * 
 55      * @param request the request send by the client to the server
 56      * @param response the response send by the server to the client
 57      * @throws ServletException if an error occurred
 58      * @throws IOException if an error occurred
 59      */
 60     public void doGet(HttpServletRequest request, HttpServletResponse response)
 61             throws ServletException, IOException {
 62            
 63         
 64         
 65         
 66         
 67          String[] formulaString = new String[] {"维持","恢复","调整","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111"};
 68         
 69          
 70          XSSFWorkbook wb = new XSSFWorkbook();
 71          XSSFSheet sheet = wb.createSheet("下拉列表测试");
 72             // 创建sheet，写入枚举项
 73          XSSFSheet hideSheet = wb.createSheet("hiddenSheet");
 74             for (int i = 0; i < formulaString.length; i++) {
 75                 hideSheet.createRow(i).createCell(0).setCellValue(formulaString[i]);
 76             }
 77             // 创建名称，可被其他单元格引用
 78             XSSFName  category1Name = wb.createName();
 79             category1Name.setNameName("hidden");
 80             // 设置名称引用的公式
 81             // 使用像'A1：B1'这样的相对值会导致在Microsoft Excel中使用工作簿时名称所指向的单元格的意外移动，
 82             // 通常使用绝对引用，例如'$A$1:$B$1'可以避免这种情况。
 83             // 参考： http://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Name.html
 84             category1Name.setRefersToFormula("hiddenSheet!" + "$A$1:$A$" + formulaString.length);
 85             // 获取上文名称内数据
 86             DataValidationHelper helper = sheet.getDataValidationHelper();
 87             DataValidationConstraint constraint = helper.createFormulaListConstraint("hidden");
 88             // 设置下拉框位置
 89             CellRangeAddressList addressList = new CellRangeAddressList(0, 200, 0, 0);
 90             DataValidation dataValidation = helper.createValidation(constraint, addressList);
 91             // 处理Excel兼容性问题
 92             if (dataValidation instanceof XSSFDataValidation) {
 93                 // 数据校验
 94                 dataValidation.setSuppressDropDownArrow(true);
 95                 dataValidation.setShowErrorBox(true);
 96             } else {
 97                 dataValidation.setSuppressDropDownArrow(false);
 98             }
 99             // 作用在目标sheet上
100             sheet.addValidationData(dataValidation);
101             // 设置hiddenSheet隐藏
102             wb.setSheetHidden(1, true);
103 
104          
105          
106         
107 //        //工作区     
108 //        XSSFWorkbook wb = new XSSFWorkbook();     
109 //        XSSFSheet sheet= wb.createSheet("test");    
110 //        //如果循环超过10172次，则报内存溢出，有谁循环超过10万次不报错，麻烦请告诉我，这样是因为可以一次性导出大量数据  
111 //        for(int i=0;i<2;i++){  
112 //            //创建第一个sheet     
113 //            //生成第一行     
114 //            XSSFRow row = sheet.createRow(i);     
115 //            //给这一行的第一列赋值     
116 //            row.createCell(0).setCellValue("column1");     
117 //            //给这一行的第一列赋值     
118 //            row.createCell(1).setCellValue("column2");
119 //        } 
120 //
121 //        
122 //      
123 //        //String[] formulaString = new String[] {"维持","恢复","调整","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111"};
124 //        XSSFSheet category1Hidden = wb.createSheet("hideSheet"); // 创建隐藏域
125 //        for (int i = 0, length = formulaString.length; i < length; i++) { // 循环赋值（为了防止下拉框的行数与隐藏域的行数相对应来获取>=选中行数的数组，将隐藏域加到结束行之后）
126 //            category1Hidden.createRow(i).createCell(0).setCellValue(formulaString[i]);
127 //        } 
128 //        
129         
130         
131         
132 //      //加载下拉列表内容
133 //        DataValidationHelper helper = sheet.getDataValidationHelper();
134 //        DataValidationConstraint constraint = helper.createExplicitListConstraint(formulaString);
135 //        //设置下拉框位置
136 //        CellRangeAddressList addressList = null;
137 //        addressList = new CellRangeAddressList(0, 10, 1, 1);
138 //        DataValidation dataValidation = helper.createValidation(constraint, addressList);
139 //        //处理Excel兼容性问题
140 //        if(dataValidation instanceof XSSFDataValidation){
141 //            //数据校验
142 //            dataValidation.setSuppressDropDownArrow(true);
143 //            dataValidation.setShowErrorBox(true);
144 //        }else{
145 //            dataValidation.setSuppressDropDownArrow(false);
146 //        }
147 //        sheet.addValidationData(dataValidation);
148 
149         
150         
151         
152         
153         
154         String fileHeader = "abc";
155         if(fileHeader==null||fileHeader.length()==0){
156             fileHeader = "";
157         }
158         String fullname = fileHeader + System.currentTimeMillis() + ".xlsx";// 生成文件名
159         if (request.getHeader("User-Agent").indexOf("MSIE 5.5") != -1) {
160             response.setHeader("Content-Disposition", "filename=" + fullname);
161         } else {
162             response.addHeader("Content-Disposition", "attachment;filename="
163                     + fullname);
164         }
165 
166         response.setHeader("Content-Type", "application/msexcel");
167         ServletOutputStream streamOut = null;
168 
169         try {
170             streamOut = response.getOutputStream();
171             wb.write(streamOut);// 将数据写入输出流
172         } catch (Exception e) {
173         } finally {
174             if (streamOut != null) {
175                 try {
176                     streamOut.close();
177                 } catch (Exception e1) {
178                 }
179             }
180         }
181 
182         response.setStatus(response.SC_OK);
183 
184         try {
185             response.flushBuffer();// 推送
186         } catch (IOException e) {
187             // TODO Auto-generated catch block
188             e.printStackTrace();
189         }
190        
191         
192     }
193 
194     /** 转码成 UTF-8
195      * The doPost method of the servlet. <br>
196      *
197      * This method is called when a form has its tag value method equals to post.
198      * 
199      * @param request the request send by the client to the server
200      * @param response the response send by the server to the client
201      * @throws ServletException if an error occurred
202      * @throws IOException if an error occurred
203      */
204     public void doPost(HttpServletRequest request, HttpServletResponse response)
205             throws ServletException, IOException {
206 
207         doGet(request, response);
208     }
209 
210     /** 转码成 UTF-8
211      * The doPut method of the servlet. <br>
212      *
213      * This method is called when a HTTP put request is received.
214      * 
215      * @param request the request send by the client to the server
216      * @param response the response send by the server to the client
217      * @throws ServletException if an error occurred
218      * @throws IOException if an error occurred
219      */
220     public void doPut(HttpServletRequest request, HttpServletResponse response)
221             throws ServletException, IOException {
222 
223         // Put your code here
224     }
225 
226     /** 转码成 UTF-8
227      * Returns information about the servlet, such as 
228      * author, version, and copyright. 
229      *
230      * @return String information about this servlet
231      */
232     public String getServletInfo() {
233         return "This is my default servlet created by Eclipse";
234     }
235 
236     /** 转码成 UTF-8
237      * Initialization of the servlet. <br>
238      *
239      * @throws ServletException if an error occurs
240      */
241     public void init() throws ServletException {
242         // Put your code here
243     }
244 
245 }

参考
http://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Name.html
http://poi.apache.org/components/spreadsheet/
最后
转帖请注明出处，谢谢
---------------------
版权声明：本文为CSDN博主「Fei___」的原创文章，遵循CC 4.0 by-sa版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/fei565789229/article/details/85016091