**
2种方式解决此问题**
我在easyexcel通过拦截器拦截 implements SheetWriteHandler
添加样式少的时候是没问题的,有一天线上环境突然不行了
在createrow报错, getrow为null也报错,因为当写入磁盘的内容与现在内容合并导致这个问题
下面出现问题的完整代码
` //获取一个workbook
Sheet sheet = writeSheetHolder.getSheet();
//设置下拉框
DataValidationHelper helper = sheet.getDataValidationHelper();
//定义sheet的名称
String hiddenName = “hidden”;
//1.创建一个隐藏的sheet 名称为 hidden
Workbook workbook= writeWorkbookHolder.getWorkbook();
Sheet hidden = workbook.createSheet(hiddenName);
Name category1Name = workbook.createName();
category1Name.setNameName(hiddenName);
for (Map.Entry<Integer,ExcelSelectedResolve> entry : map.entrySet()) {
//下拉框的起始行,结束行,起始列,结束列
CellRangeAddressList addressList = new CellRangeAddressList(1, 2000, entry.getKey(), entry.getKey());
//获取excel列名
String excelLine = getExcelLine(entry.getKey());
//2.循环赋值
String[] values = entry.getValue().getSource();
for (int i = 0, length = values.length; i < length; i++) {
// 3:表示你开始的行数 3表示 你开始的列数
Row row = hidden.getRow(i);
if (row == null) {
row = hidden.createRow(i);
}
row.createCell(entry.getKey()).setCellValue(values[i]);
}
//4. =hidden!$H:$1:$H$50 sheet为hidden的 H1列开始H50行数据获取下拉数组
String refers = "="+hiddenName + "!$"+excelLine+
"$1:$"+excelLine +"$"+ (values.length);
//5 将刚才设置的sheet引用到你的下拉列表中
DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
// 阻止输入非下拉选项的值
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.setShowErrorBox(true);
dataValidation.setSuppressDropDownArrow(true);
dataValidation.createErrorBox("提示", "请输入下拉选项中的内容");
writeSheetHolder.getSheet().addValidationData(dataValidation);
}
//设置列为隐藏
int hiddenIndex = workbook.getSheetIndex("hidden");
if (!workbook.isSheetHidden(hiddenIndex)) {
workbook.setSheetHidden(hiddenIndex, true);
}
`
废话不多说开始吧
方式一
//获取一个workbook
Sheet sheet = writeSheetHolder.getSheet();
//设置下拉框
DataValidationHelper helper = sheet.getDataValidationHelper();
for (Map.Entry<Integer,ExcelSelectedResolve> entry : map.entrySet()) {
//定义sheet的名称
String hiddenName = "hidden"+entry.getKey();
//1.创建一个隐藏的sheet 名称为 hidden
Workbook workbook= writeWorkbookHolder.getWorkbook();
Sheet hidden = workbook.createSheet(hiddenName);
Name category1Name = workbook.createName();
category1Name.setNameName(hiddenName);
//下拉框的起始行,结束行,起始列,结束列
CellRangeAddressList addressList = new CellRangeAddressList(1, 2000, entry.getKey(), entry.getKey());
//获取excel列名
String excelLine = getExcelLine(entry.getKey());
//2.循环赋值
String[] values = entry.getValue().getSource();
for (int i = 0, length = values.length; i < length; i++) {
// 3:表示你开始的行数 3表示 你开始的列数
Row row = hidden.getRow(i);
if (row == null) {
row = hidden.createRow(i);
}
row.createCell(entry.getKey()).setCellValue(values[i]);
}
//4. =hidden!$H:$1:$H$50 sheet为hidden的 H1列开始H50行数据获取下拉数组
String refers = "="+hiddenName + "!$"+excelLine+
"$1:$"+excelLine +"$"+ (values.length);
//5 将刚才设置的sheet引用到你的下拉列表中
DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
// 阻止输入非下拉选项的值
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.setShowErrorBox(true);
dataValidation.setSuppressDropDownArrow(true);
dataValidation.createErrorBox("提示", "请输入下拉选项中的内容");
writeSheetHolder.getSheet().addValidationData(dataValidation);
//设置列为隐藏
int hiddenIndex = workbook.getSheetIndex(hiddenName);
if (!workbook.isSheetHidden(hiddenIndex)) {
workbook.setSheetHidden(hiddenIndex, true);
}
}
在区别在于 之前讲所有下拉内容放入一个sheet内
导致内存与磁盘合并出现问题
修改成每一个下拉框新建一个隐藏sheet即可
方式二
通过poi不实用easyexcel 直接上代码废话不多说
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
SXSSFWorkbook workbook = new SXSSFWorkbook(xssfWorkbook, 100, false, false);
SXSSFSheet sheet = workbook.createSheet("sheet");
SXSSFRow row = sheet.createRow(0);
List<String> titleString = excelTemplateServiceImpl.listadd();
//设置标题
for (int i = 0; i < titleString.size(); i++) {
SXSSFCell cell = row.createCell(i);
cell.setCellValue(titleString.get(i));
}
Map<Integer, String[]> buffer = getBuffer();
for (Map.Entry<Integer, String[]> integerEntry : buffer.entrySet()) {
//创建下拉框
setLongHSSFValidation(workbook,integerEntry.getValue(), sheet, 1, 10000, integerEntry.getKey());
}
try {
try {
//文件名中文乱码问题
fileName = new String(fileName.getBytes("iso8859-1"), "utf-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//编码格式为UTF-8
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (Exception ex) {
ex.printStackTrace();
}
/**
* 设置下拉cell。下拉数据放在隐藏的sheet中。
* 解决下拉框过长不显示问题
*
* @param workbook HSSFWorkbook 和SXSSWorkbook 都可以
* @param dataList 下拉数据数组
* @param sheet 设置下拉框的sheet
* @param firstRow 开始行 为空的话默认数值是1
* @param endRow 结束行
* @param cellNum 下拉框所在的列
*/
public static void setLongHSSFValidation(Workbook workbook, String[] dataList, Sheet sheet, Integer firstRow, Integer endRow, Integer cellNum) {
String hiddenName = "hidden" + (cellNum + 1);
//1.创建隐藏的sheet页。存放下拉数据
Sheet hidden = workbook.createSheet(hiddenName);
//2.循环赋值
for (int i = 0, length = dataList.length; i < length; i++) {
hidden.createRow(i).createCell(cellNum).setCellValue(dataList[i]);
}
Name category1Name = workbook.createName();
category1Name.setNameName(hiddenName);
category1Name.setRefersToFormula(hiddenName + "!A$1:A$" + dataList.length);
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createFormulaListConstraint(hiddenName);
CellRangeAddressList addressList = new CellRangeAddressList(null == firstRow ? 1 : firstRow, endRow, cellNum, cellNum);
DataValidation dataValidation = helper.createValidation(constraint, addressList);
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true);
dataValidation.setShowErrorBox(true);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
// 阻止输入非下拉选项的值
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.setShowErrorBox(true);
dataValidation.setSuppressDropDownArrow(true);
dataValidation.createErrorBox("提示", "请输入下拉选项中的内容");
// 设置hiddenSheet隐藏
workbook.setSheetHidden(workbook.getSheetIndex(hidden), true);
}
解决问题