package com.fst.attachment.controller;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class TestPOIDropDown {
public static void main(String[] args) throws Exception {
// 创建枚举项
int len = 200;
String[] datas = new String[len];
for (int i = 0; i < len; i++) {
datas[i] = i + "我是下拉框枚举项---";
}
// ----------------生成--------------------------------
// 方法一:使用createExplicitListConstraint实现,缺陷为:
// 只能满足较少枚举项的下拉框,最好不要超过20个,具体个数根据枚举字段长度而定。
// Workbook workbook = HSSFSetDropDown(datas);
// Workbook workbook = XSSFSetDropDown(datas);
// Workbook workbook = SXSSFSetDropDown(datas);
/*
* 简单比较HSSF、XSSF、SXSSF: - 由于新的XSSF支持Excel 2007 OOXML(.xlsx)文件是基于XML的,
* 因此处理它们的内存占用量高于旧版HSSF支持的(.xls)二进制文件。 -
* SXSSF(3.8-beta3之后支持)在生成非常大的电子表格时使用, 相较于XSSF,其在某个时间点只能访问有限数量的行。
* http://poi.apache.org/components/spreadsheet/
*/
// 方法二:使用createFormulaListConstraint实现,其适用于较多枚举项的下拉框,
// 实现步骤大致为:创建一个隐藏的sheet,并往里放入枚举项,然后在第一个sheet内增加关联关系
Workbook workbook = XSSFSetDropDownAndHidden(datas);
// 输出
FileOutputStream stream = new FileOutputStream("d:\\testDropDown.xlsx");
workbook.write(stream);
stream.close();
}
/**
* 使用createFormulaListConstraint实现下拉框
* @param formulaString
* @return
*/
public static Workbook XSSFSetDropDownAndHidden(String[] formulaString) {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("下拉列表测试");
// 创建sheet,写入枚举项
Sheet hideSheet = workbook.createSheet("hiddenSheet");
for (int i = 0; i < formulaString.length; i++) {
hideSheet.createRow(i).createCell(0).setCellValue(formulaString[i]);
}
// 创建名称,可被其他单元格引用
Name category1Name = workbook.createName();
category1Name.setNameName("hidden");
// 设置名称引用的公式
// 使用像'A1:B1'这样的相对值会导致在Microsoft Excel中使用工作簿时名称所指向的单元格的意外移动,
// 通常使用绝对引用,例如'$A$1:$B$1'可以避免这种情况。
// 参考: http://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Name.html
category1Name.setRefersToFormula("hiddenSheet!" + "$A$1:$A$" + formulaString.length);
// 获取上文名称内数据
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createFormulaListConstraint("hidden");
// 设置下拉框位置
CellRangeAddressList addressList = new CellRangeAddressList(0, 200, 0, 0);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
// 处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
// 数据校验
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
// 作用在目标sheet上
sheet.addValidationData(dataValidation);
// 设置hiddenSheet隐藏
workbook.setSheetHidden(1, true);
return workbook;
}
/**
* 使用较早版本的 HSSF用户模型设置表格下拉框 缺陷:下拉框数据量超过一定数量时,系统抛异常。
*
* @param formulaString
*
*/
public static Workbook HSSFSetDropDown(String[] formulaString) {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("下拉列表测试");
// 加载下拉列表内容
DVConstraint constraint = DVConstraint.createExplicitListConstraint(formulaString);
// 设置数据有效性加载在哪个单元格上。
// 四个参数分别是:起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(0, 200, 0, 0);
// 数据有效性对象
DataValidation dataValidation = new HSSFDataValidation(regions, constraint);
sheet.addValidationData(dataValidation);
return workbook;
}
/**
* 使用 XSSF用户模型设置表格下拉框,多用来处理xlsx后缀的excel 缺陷:下拉框数据量超过一定数量时,文件打不开。
*
* @param formulaString
*
*/
public static Workbook XSSFSetDropDown(String[] formulaString) {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("下拉列表测试");
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
.createExplicitListConstraint(formulaString);
CellRangeAddressList addressList = null;
XSSFDataValidation validation = null;
for (int i = 0; i < 500; i++) {
addressList = new CellRangeAddressList(i, i, 0, 0);
validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
// 07默认setSuppressDropDownArrow(true);
// validation.setSuppressDropDownArrow(true);
// validation.setShowErrorBox(true);
sheet.addValidationData(validation);
}
return workbook;
}
/**
* 使用 SXSSF用户模型设置表格下拉框 缺陷:下拉框数据量超过一定数量时,文件打不开。
*
* @param formulaString
*
*/
public static Workbook SXSSFSetDropDown(String[] formulaString) {
SXSSFWorkbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet("下拉列表测试");
// 加载下拉列表内容
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(formulaString);
// 设置下拉框位置
CellRangeAddressList addressList = null;
addressList = new CellRangeAddressList(0, 500, 0, 0);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
// 处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
// 数据校验
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
return workbook;
}
}
package logic.bsc.excelTemplateExport;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import logic.oec.OecToExcel;
import logic.pxks.ExcelOut;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFName;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.usrObj.User;
import com.yunhe.tools.Dates;
import com.yunhe.tools.Excels;
import com.yunhe.tools.Htmls;
public class ExcelTDSExport extends HttpServlet {
/** 转码成 UTF-8
* Constructor of the object.
*/
public ExcelTDSExport() {
super();
}
/** 转码成 UTF-8
* Destruction of the servlet. <br>
*/
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
/** 转码成 UTF-8
* The doGet method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to get.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String[] formulaString = new String[] {"维持","恢复","调整","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111"};
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("下拉列表测试");
// 创建sheet,写入枚举项
XSSFSheet hideSheet = wb.createSheet("hiddenSheet");
for (int i = 0; i < formulaString.length; i++) {
hideSheet.createRow(i).createCell(0).setCellValue(formulaString[i]);
}
// 创建名称,可被其他单元格引用
XSSFName category1Name = wb.createName();
category1Name.setNameName("hidden");
// 设置名称引用的公式
// 使用像'A1:B1'这样的相对值会导致在Microsoft Excel中使用工作簿时名称所指向的单元格的意外移动,
// 通常使用绝对引用,例如'$A$1:$B$1'可以避免这种情况。
// 参考: http://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Name.html
category1Name.setRefersToFormula("hiddenSheet!" + "$A$1:$A$" + formulaString.length);
// 获取上文名称内数据
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createFormulaListConstraint("hidden");
// 设置下拉框位置
CellRangeAddressList addressList = new CellRangeAddressList(0, 200, 0, 0);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
// 处理Excel兼容性问题
if (dataValidation instanceof XSSFDataValidation) {
// 数据校验
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
// 作用在目标sheet上
sheet.addValidationData(dataValidation);
// 设置hiddenSheet隐藏
wb.setSheetHidden(1, true);
// //工作区
// XSSFWorkbook wb = new XSSFWorkbook();
// XSSFSheet sheet= wb.createSheet("test");
// //如果循环超过10172次,则报内存溢出,有谁循环超过10万次不报错,麻烦请告诉我,这样是因为可以一次性导出大量数据
// for(int i=0;i<2;i++){
// //创建第一个sheet
// //生成第一行
// XSSFRow row = sheet.createRow(i);
// //给这一行的第一列赋值
// row.createCell(0).setCellValue("column1");
// //给这一行的第一列赋值
// row.createCell(1).setCellValue("column2");
// }
//
//
//
// //String[] formulaString = new String[] {"维持","恢复","调整","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111","调整111111111111111111111111111111111111111111111"};
// XSSFSheet category1Hidden = wb.createSheet("hideSheet"); // 创建隐藏域
// for (int i = 0, length = formulaString.length; i < length; i++) { // 循环赋值(为了防止下拉框的行数与隐藏域的行数相对应来获取>=选中行数的数组,将隐藏域加到结束行之后)
// category1Hidden.createRow(i).createCell(0).setCellValue(formulaString[i]);
// }
//
// //加载下拉列表内容
// DataValidationHelper helper = sheet.getDataValidationHelper();
// DataValidationConstraint constraint = helper.createExplicitListConstraint(formulaString);
// //设置下拉框位置
// CellRangeAddressList addressList = null;
// addressList = new CellRangeAddressList(0, 10, 1, 1);
// DataValidation dataValidation = helper.createValidation(constraint, addressList);
// //处理Excel兼容性问题
// if(dataValidation instanceof XSSFDataValidation){
// //数据校验
// dataValidation.setSuppressDropDownArrow(true);
// dataValidation.setShowErrorBox(true);
// }else{
// dataValidation.setSuppressDropDownArrow(false);
// }
// sheet.addValidationData(dataValidation);
String fileHeader = "abc";
if(fileHeader==null||fileHeader.length()==0){
fileHeader = "";
}
String fullname = fileHeader + System.currentTimeMillis() + ".xlsx";// 生成文件名
if (request.getHeader("User-Agent").indexOf("MSIE 5.5") != -1) {
response.setHeader("Content-Disposition", "filename=" + fullname);
} else {
response.addHeader("Content-Disposition", "attachment;filename="
+ fullname);
}
response.setHeader("Content-Type", "application/msexcel");
ServletOutputStream streamOut = null;
try {
streamOut = response.getOutputStream();
wb.write(streamOut);// 将数据写入输出流
} catch (Exception e) {
} finally {
if (streamOut != null) {
try {
streamOut.close();
} catch (Exception e1) {
}
}
}
response.setStatus(response.SC_OK);
try {
response.flushBuffer();// 推送
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/** 转码成 UTF-8
* The doPost method of the servlet. <br>
*
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
/** 转码成 UTF-8
* The doPut method of the servlet. <br>
*
* This method is called when a HTTP put request is received.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPut(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// Put your code here
}
/** 转码成 UTF-8
* Returns information about the servlet, such as
* author, version, and copyright.
*
* @return String information about this servlet
*/
public String getServletInfo() {
return "This is my default servlet created by Eclipse";
}
/** 转码成 UTF-8
* Initialization of the servlet. <br>
*
* @throws ServletException if an error occurs
*/
public void init() throws ServletException {
// Put your code here
}
}
转自:使用poi解决导出excel内下拉框枚举项较多的问题 - *(00)* - 博客园