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

最新推荐文章于 2023-08-25 16:36:03 发布

weixin_39609407

最新推荐文章于 2023-08-25 16:36:03 发布

阅读量634

点赞数

文章标签： java 枚举下拉框

本文链接：https://blog.csdn.net/weixin_39609407/article/details/114597956

版权

该博客介绍了如何使用Java的Apache POI库处理Excel文件，并通过创建隐藏的工作表和公式列表约束，解决在导出Excel时下拉框枚举项过多导致的问题。示例代码展示了如何创建带有长列表下拉框的Excel工作表，并优化了内存使用，避免了内存溢出。

摘要由CSDN通过智能技术生成

1 packagelogic.bsc.excelTemplateExport;2

3 importjava.io.IOException;4 importjava.io.PrintWriter;5

6 importjavax.servlet.ServletException;7 importjavax.servlet.ServletOutputStream;8 importjavax.servlet.http.HttpServlet;9 importjavax.servlet.http.HttpServletRequest;10 importjavax.servlet.http.HttpServletResponse;11 importjavax.servlet.http.HttpSession;12

13 importlogic.oec.OecToExcel;14 importlogic.pxks.ExcelOut;15

16 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;17 importorg.apache.poi.ss.usermodel.DataValidation;18 importorg.apache.poi.ss.usermodel.DataValidationConstraint;19 importorg.apache.poi.ss.usermodel.DataValidationHelper;20 importorg.apache.poi.ss.util.CellRangeAddressList;21 importorg.apache.poi.xssf.usermodel.XSSFDataValidation;22 importorg.apache.poi.xssf.usermodel.XSSFName;23 importorg.apache.poi.xssf.usermodel.XSSFRow;24 importorg.apache.poi.xssf.usermodel.XSSFSheet;25 importorg.apache.poi.xssf.usermodel.XSSFWorkbook;26

27 importcom.usrObj.User;28 importcom.yunhe.tools.Dates;29 importcom.yunhe.tools.Excels;30 importcom.yunhe.tools.Htmls;31

33 public class ExcelTDSExport extendsHttpServlet {34

35 /**转码成 UTF-836 * Constructor of the object.37 */

38 publicExcelTDSExport() {39 super();40 }41

42 /**转码成 UTF-843 * Destruction of the servlet.
44 */

45 public voiddestroy() {46 super.destroy(); //Just puts "destroy" string in log47 //Put your code here

48 }49

50 /**转码成 UTF-851 * The doGet method of the servlet.
52 *53 * This method is called when a form has its tag value method equals to get.54 *55 *@paramrequest the request send by the client to the server56 *@paramresponse the response send by the server to the client57 *@throwsServletException if an error occurred58 *@throwsIOException if an error occurred59 */

60 public voiddoGet(HttpServletRequest request, HttpServletResponse response)61 throwsServletException, IOException {62

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

70 XSSFWorkbook wb = newXSSFWorkbook();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 instanceofXSSFDataValidation) {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 // //创建第一个sheet113 // //生成第一行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-8195 * The doPost method of the servlet.
196 *197 * This method is called when a form has its tag value method equals to post.198 *199 *@paramrequest the request send by the client to the server200 *@paramresponse the response send by the server to the client201 *@throwsServletException if an error occurred202 *@throwsIOException if an error occurred203 */

204 public voiddoPost(HttpServletRequest request, HttpServletResponse response)205 throwsServletException, IOException {206

207 doGet(request, response);208 }209

210 /**转码成 UTF-8211 * The doPut method of the servlet.
212 *213 * This method is called when a HTTP put request is received.214 *215 *@paramrequest the request send by the client to the server216 *@paramresponse the response send by the server to the client217 *@throwsServletException if an error occurred218 *@throwsIOException if an error occurred219 */

220 public voiddoPut(HttpServletRequest request, HttpServletResponse response)221 throwsServletException, IOException {222

223 //Put your code here

224 }225

226 /**转码成 UTF-8227 * Returns information about the servlet, such as228 * author, version, and copyright.229 *230 *@returnString information about this servlet231 */

232 publicString getServletInfo() {233 return "This is my default servlet created by Eclipse";234 }235

236 /**转码成 UTF-8237 * Initialization of the servlet.
238 *239 *@throwsServletException if an error occurs240 */

241 public void init() throwsServletException {242 //Put your code here

243 }244

245 }