1.解决长度255问题
最近在项目中用到poi生成excel文件，其中需要将某一列限定为特定的值
原始的方法为：
private static HSSFDataValidation getDataValidationList(HSSFSheet sheet, short firstRow,
short firstCol, short endRow,
short endCol, List<String> strList)
{
LOGGER.info("dataList: {}.", strList);
String[] dataArray = strList.toArray(new String[0]);
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
DataValidationConstraint dvConstraint = (DataValidationConstraint)dvHelper
.createExplicitListConstraint(dataArray);
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,
endCol);
HSSFDataValidation validation = (HSSFDataValidation)dvHelper.createValidation(dvConstraint,
addressList);
if (null != validation)
{
sheet.addValidationData(validation);
}
return validation;
}
但是当一个单元格的字符数大于255时，就报如下问题：
java.lang.IllegalArgumentException: String literals in formulas can’t be bigger than 255 characters ASCII
解决方案：把他放在一个隐藏的sheet中，如果有多个下拉框的话看下面的方法
colName：列名 （Collections.addAll(headerList,"单元编号","楼层编号","房屋号","房屋名称","房屋地址","单元名称"");）
firstCol：起始列
firstRow：起始行
endRow：终止行
firstCol：起始列
endCol：终止列
private static HSSFDataValidation getDataValidationList4Col(HSSFSheet sheet, short firstRow,
short firstCol, short endRow,
short endCol, List<String> colName,
HSSFWorkbook wbCreat)
{
String[] dataArray = colName.toArray(new String[0]);
HSSFSheet hidden = wbCreat.createSheet("hidden");
HSSFCell cell = null;
for (int i = 0, length = dataArray.length; i < length; i++)
{
String name = dataArray[i];
HSSFRow row = hidden.createRow(i);
cell = row.createCell(0);
cell.setCellValue(name);
}
Name namedCell = wbCreat.createName();
namedCell.setNameName("hidden");
namedCell.setRefersToFormula("hidden!$A$1:$A$" + dataArray.length);
//加载数据,将名称为hidden的
DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,
endCol);
HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
//将第二个sheet设置为隐藏
wbCreat.setSheetHidden(1, true);
if (null != validation)
{
sheet.addValidationData(validation);
}
return validation;
}
以上为次大佬编写
原文链接：https://blog.csdn.net/wangchaox123/article/details/97787470
2.解决多个下拉框问题
只需要将其中的List改变即可调用此方法，调用的方法在下面
/**
*设置某些列的值只能输入预制的数据,显示下拉框
* @param sheet HSSFSheet sheetlist = wb.createSheet("XXXX");工作表对象
* @return
*/
private static List<HSSFDataValidation> setHSSFValidation2(HSSFSheet sheet,List<SysDictVo> sysDictVoList,HSSFWorkbook workbook) {
//这里将下拉框进行拆分存储在每一个单元格 并进行隐藏 防止下拉框数据过多存储在一个单元格的字符数大于255而报错
List<HSSFDataValidation> list =new ArrayList<>();
HSSFDataValidation dataValidation = null;
//查询码值集合,获取当前列的码值。
//房屋性质
List<String> houseAttrList = new ArrayList<>();//houseAttrList.toArray(new String[houseAttrList.size()])
for (SysDictVo dict : sysDictVoList) {
if(dict.getDictCode() == "bld_attr" || dict.getDictCode().equals("bld_attr")){
houseAttrList.add(dict.getItemValue()+"_"+dict.getItemText());
System.out.println(dict.getItemValue()+"_"+dict.getItemText());
}
}
//指定0-9行，0-0列为下拉框
CellRangeAddressList cell1 = new CellRangeAddressList(1 , 500 , 8 , 8);
//创建下拉数据列
DVConstraint dvConstraint1 = DVConstraint.createExplicitListConstraint(houseAttrList.toArray(new String[houseAttrList.size()]));
//将下拉数据放入下拉框
dataValidation = new HSSFDataValidation(cell1, dvConstraint1);
list.add(dataValidation);
//房屋类型
List<String> houseTypeList = new ArrayList<>();
for (SysDictVo dict : sysDictVoList) {
if(dict.getDictCode() == "house_type" || dict.getDictCode().equals("house_type")){
houseTypeList.add(dict.getItemValue()+"_"+dict.getItemText());
System.out.println(dict.getItemValue()+"_"+dict.getItemText());
}
}
//指定0-9行，0-0列为下拉框
CellRangeAddressList cell12 = new CellRangeAddressList(1 , 500 , 9 , 9);
//创建下拉数据列
DVConstraint dvConstraint2 = DVConstraint.createExplicitListConstraint(houseTypeList.toArray(new String[houseTypeList.size()]));
//将下拉数据放入下拉框
dataValidation = new HSSFDataValidation(cell12, dvConstraint2);
list.add(dataValidation);
// //房屋用途
//这里将下拉框进行拆分存储在每一个单元格 并进行隐藏 防止下拉框数据过多存储在一个单元格的字符数大于255而报错
List<String> houseUseTypeList = new ArrayList<>();
for (SysDictVo dict : sysDictVoList) {
if(dict.getDictCode() == "bld_use" || dict.getDictCode().equals("bld_use")){
houseUseTypeList.add(dict.getItemValue()+"_"+dict.getItemText());
System.out.println(dict.getItemValue()+"_"+dict.getItemText());
}
}
String[] houseUseTypeArray = houseUseTypeList.toArray(new String[0]);
// HSSFWorkbook workbook = sheet.getWorkbook();
// Sheet hidden = workbook.createSheet("hidden");
// Cell cell = null;
// for (int i = 0, length = houseUseTypeArray.length; i < length; i++)
// {
// String name = houseUseTypeArray[i];
// Row roww = hidden.createRow(i);
// cell = roww.createCell(0);
// cell.setCellValue(name);
// }
// 创建名称管理器
Name namedCell = workbook.createName();
namedCell.setNameName("hidden");
// 引用列，industry为上面创建的一张隐藏的sheet表，$A$2:$A$n,是此表中存放的数据
namedCell.setRefersToFormula("hidden!$A$1:$A$" + houseUseTypeArray.length);
//将第二个sheet页设置为隐藏
workbook.setSheetHidden(1, true);
//将名称为hidden的数据进行加载
// 验证约束
DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");
//设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(1 , 500 , 10 , 10);
HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
list.add(data_validation_list);
//房屋结构
List<String> engineeStructList = new ArrayList<>();
for (SysDictVo dict : sysDictVoList) {
if(dict.getDictCode() == "enginee_struct" || dict.getDictCode().equals("enginee_struct")){
engineeStructList.add(dict.getItemValue()+"_"+dict.getItemText());
System.out.println(dict.getItemValue()+"_"+dict.getItemText());
}
}
//指定0-9行，0-0列为下拉框
CellRangeAddressList cell14 = new CellRangeAddressList(1 , 500 , 11 , 11);
//创建下拉数据列
DVConstraint dvConstraint4 = DVConstraint.createExplicitListConstraint(engineeStructList.toArray(new String[engineeStructList.size()]));
//将下拉数据放入下拉框
dataValidation = new HSSFDataValidation(cell14, dvConstraint4);
list.add(dataValidation);
//
//
//户型
List<String> houseModelList = new ArrayList<>();
for (SysDictVo dict : sysDictVoList) {
if(dict.getDictCode() == "house_model" || dict.getDictCode().equals("house_model")){
houseModelList.add(dict.getItemValue()+"_"+dict.getItemText());
System.out.println(dict.getItemValue()+"_"+dict.getItemText());
}
}
//指定0-9行，0-0列为下拉框
CellRangeAddressList cell15 = new CellRangeAddressList(1 , 500 , 12 , 12);
//创建下拉数据列
DVConstraint dvConstraint5 = DVConstraint.createExplicitListConstraint(houseModelList.toArray(new String[houseModelList.size()]));
//将下拉数据放入下拉框
dataValidation = new HSSFDataValidation(cell15, dvConstraint5);
list.add(dataValidation);
return list;
}
这是调用以上方法的代码
//设置下拉框
List<HSSFDataValidation> dataValidationList = setHSSFValidation2(sheet, sysDictVoList,hssfWorkbook);
if (!CollectionUtils.isEmpty(dataValidationList)) {
for (HSSFDataValidation hssfDataValidation : dataValidationList) {
sheet.addValidationData(hssfDataValidation);
}
}
一下为完整代码，复制可用，只需要把下拉的值改为自己的 和 列名
package com.qycloud.modules.bbuilding.util;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.qycloud.modules.bbuilding.vo.SysDictVo;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import javax.servlet.http.HttpServletResponse;
/**
* @ClassName BHouseExcelUtil
* @Description TODO
* @Author zs
* @Date 2022-09-29 11:05
* @Version 1.0
*/
public class BHouseExcelUtil {
/**
* 可下载指定位置设置下拉框属性，并设置表头的Excel
* @param response
* @param headers 表头内容
* @param fileName 文件名
* @param firstRow 添加下拉框对应开始行13
* @param endRow 添加下拉框对应结束行13
*/
public static void downloadComboBoxExcel(HttpServletResponse response, String[] headers, List<SysDictVo> sysDictVoList,
String fileName, int firstRow, int endRow){
try {
FileOutputStream out = new FileOutputStream("f:\\"+fileName+".xls");
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
HSSFSheet sheet = hssfWorkbook.createSheet("房屋列表");
HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置字体·
HSSFFont font = hssfWorkbook.createFont();
HSSFFont font1 = hssfWorkbook.createFont();
font.setFontName("宋体");
font1.setFontName("宋体");
font.setFontHeightInPoints((short) 12);
font1.setFontHeightInPoints((short) 10);
font.setBold(true);
//设置表头边框
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
//设置边距
sheet.setDefaultColumnWidth(18);
sheet.setDefaultRowHeightInPoints(20);
//新增数据行，并且设置单元格数据
int rowNum = 0;
//在表中存放查询到的数据放入对应的列
//在excel表中添加表头
HSSFRow row = sheet.createRow(rowNum);//1
HSSFRow row2 = sheet.createRow(1);//2
row.setHeightInPoints(23);//1
row2.setHeightInPoints(23);//2
HSSFCell cell;
// 合并第一行 从第一行开始第一行结束，第0列开始 第cellNum列结束
CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,18);
sheet.addMergedRegion(callRangeAddress);
// 在第一行第 一个单元格
cell = row.createCell(0);
// 第一行合并内容
cell.setCellValue("房屋列表");
// 设置样式为居中
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = hssfWorkbook.createCellStyle();
// style.setAlignment(HorizontalAlignment.CENTER);
style.setAlignment(HorizontalAlignment.CENTER); //水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
cell.setCellStyle(style);
for (int j = 0; j < headers.length; j++) {
cell = row2.createCell(j);
cell.setCellValue(headers[j]);
cell.setCellStyle(cellStyle);
}
//设置下拉框
List<HSSFDataValidation> dataValidationList = setHSSFValidation2(sheet, sysDictVoList,hssfWorkbook);
if (!CollectionUtils.isEmpty(dataValidationList)) {
for (HSSFDataValidation hssfDataValidation : dataValidationList) {
sheet.addValidationData(hssfDataValidation);
}
}
hssfWorkbook.write(out);
out.flush();
out.close();
}catch(Exception e){
e.printStackTrace();
}
}
/**
*设置某些列的值只能输入预制的数据,显示下拉框
* @param sheet HSSFSheet sheetlist = wb.createSheet("XXXX");工作表对象
* @return
*/
private static List<HSSFDataValidation> setHSSFValidation2(HSSFSheet sheet,List<SysDictVo> sysDictVoList,HSSFWorkbook workbook) {
//这里将下拉框进行拆分存储在每一个单元格 并进行隐藏 防止下拉框数据过多存储在一个单元格的字符数大于255而报错
List<HSSFDataValidation> list =new ArrayList<>();
HSSFDataValidation dataValidation = null;
//查询码值集合,获取当前列的码值。
//房屋性质
List<String> houseAttrList = new ArrayList<>();//houseAttrList.toArray(new String[houseAttrList.size()])
for (SysDictVo dict : sysDictVoList) {
if(dict.getDictCode() == "bld_attr" || dict.getDictCode().equals("bld_attr")){
houseAttrList.add(dict.getItemValue()+"_"+dict.getItemText());
System.out.println(dict.getItemValue()+"_"+dict.getItemText());
}
}
//指定0-9行，0-0列为下拉框
CellRangeAddressList cell1 = new CellRangeAddressList(1 , 500 , 8 , 8);
//创建下拉数据列
DVConstraint dvConstraint1 = DVConstraint.createExplicitListConstraint(houseAttrList.toArray(new String[houseAttrList.size()]));
//将下拉数据放入下拉框
dataValidation = new HSSFDataValidation(cell1, dvConstraint1);
list.add(dataValidation);
//房屋类型
List<String> houseTypeList = new ArrayList<>();
for (SysDictVo dict : sysDictVoList) {
if(dict.getDictCode() == "house_type" || dict.getDictCode().equals("house_type")){
houseTypeList.add(dict.getItemValue()+"_"+dict.getItemText());
System.out.println(dict.getItemValue()+"_"+dict.getItemText());
}
}
//指定0-9行，0-0列为下拉框
CellRangeAddressList cell12 = new CellRangeAddressList(1 , 500 , 9 , 9);
//创建下拉数据列
DVConstraint dvConstraint2 = DVConstraint.createExplicitListConstraint(houseTypeList.toArray(new String[houseTypeList.size()]));
//将下拉数据放入下拉框
dataValidation = new HSSFDataValidation(cell12, dvConstraint2);
list.add(dataValidation);
// //房屋用途
//这里将下拉框进行拆分存储在每一个单元格 并进行隐藏 防止下拉框数据过多存储在一个单元格的字符数大于255而报错
List<String> houseUseTypeList = new ArrayList<>();
for (SysDictVo dict : sysDictVoList) {
if(dict.getDictCode() == "bld_use" || dict.getDictCode().equals("bld_use")){
houseUseTypeList.add(dict.getItemValue()+"_"+dict.getItemText());
System.out.println(dict.getItemValue()+"_"+dict.getItemText());
}
}
String[] houseUseTypeArray = houseUseTypeList.toArray(new String[0]);
HSSFWorkbook workbook = sheet.getWorkbook();
Sheet hidden = workbook.createSheet("hidden");
Cell cell = null;
for (int i = 0, length = houseUseTypeArray.length; i < length; i++)
{
String name = houseUseTypeArray[i];
Row roww = hidden.createRow(i);
cell = roww.createCell(0);
cell.setCellValue(name);
}
// 创建名称管理器
Name namedCell = workbook.createName();
namedCell.setNameName("hidden");
// 引用列，industry为上面创建的一张隐藏的sheet表，$A$2:$A$n,是此表中存放的数据
namedCell.setRefersToFormula("hidden!$A$1:$A$" + houseUseTypeArray.length);
//将第二个sheet页设置为隐藏
workbook.setSheetHidden(1, true);
//将名称为hidden的数据进行加载
// 验证约束
DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");
//设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(1 , 500 , 10 , 10);
HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
list.add(data_validation_list);
//房屋结构
List<String> engineeStructList = new ArrayList<>();
for (SysDictVo dict : sysDictVoList) {
if(dict.getDictCode() == "enginee_struct" || dict.getDictCode().equals("enginee_struct")){
engineeStructList.add(dict.getItemValue()+"_"+dict.getItemText());
System.out.println(dict.getItemValue()+"_"+dict.getItemText());
}
}
//指定0-9行，0-0列为下拉框
CellRangeAddressList cell14 = new CellRangeAddressList(1 , 500 , 11 , 11);
//创建下拉数据列
DVConstraint dvConstraint4 = DVConstraint.createExplicitListConstraint(engineeStructList.toArray(new String[engineeStructList.size()]));
//将下拉数据放入下拉框
dataValidation = new HSSFDataValidation(cell14, dvConstraint4);
list.add(dataValidation);
//
//
//户型
List<String> houseModelList = new ArrayList<>();
for (SysDictVo dict : sysDictVoList) {
if(dict.getDictCode() == "house_model" || dict.getDictCode().equals("house_model")){
houseModelList.add(dict.getItemValue()+"_"+dict.getItemText());
System.out.println(dict.getItemValue()+"_"+dict.getItemText());
}
}
//指定0-9行，0-0列为下拉框
CellRangeAddressList cell15 = new CellRangeAddressList(1 , 500 , 12 , 12);
//创建下拉数据列
DVConstraint dvConstraint5 = DVConstraint.createExplicitListConstraint(houseModelList.toArray(new String[houseModelList.size()]));
//将下拉数据放入下拉框
dataValidation = new HSSFDataValidation(cell15, dvConstraint5);
list.add(dataValidation);
return list;
}
}
这是controller代码
记得实体类要加注解：@Excel(name = “单元编号”,orderNum = “5”, width = 15)
/**
* 导出excel
*/
@RequestMapping(value = "/exportXls")
public void exportXls2(BHouse bHouse, HttpServletRequest request,HttpServletResponse response) {
List<String> headerList = new ArrayList<>();
Collections.addAll(headerList,"单元编号","楼层编号","房屋号","房屋名称","房屋地址","单元名称","楼层名称","跃层数","房屋性质","房屋类型","房屋用途","房屋结构","户型",
"房间数","实测建筑面积","实测套内面积","实测分摊面积","实测其他面积","房屋居住状态");
Map<String,Object> map = new HashMap<>();
map.put("dictCodes",new String[]{"bld_attr","house_type","bld_use","enginee_struct","house_model"});
List<SysDictVo> sysDictVoList = bHouseService.getDictNumber(map);//sysDictService.getDictItems("bld_attr");
BHouseExcelUtil.downloadComboBoxExcel(null, headerList.toArray(new String[headerList.size()]),sysDictVoList,"房屋列表", 1, 500);
}
1848
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
