主要解决excel动态模板下拉联动问题:
如图:
下拉联动处理代码:
下拉长度在255以内:
private static DataValidation setDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) {
DataValidationHelper helper = sheet.getDataValidationHelper();
//加载下拉列表内容
DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
constraint.setExplicitListValues(textList);
//设置数据有效性加载在哪个单元格上。四个参数分别是:起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol);
//数据有效性对象
DataValidation data_validation = helper.createValidation(constraint, regions);
return data_validation;
}
长度在255以上:
private static DataValidation setDataValidations(Sheet sheet, String[] textList, int firstRow, int endRows, int firstCol, int endCol, HSSFWorkbook book) {
String hiddenSheet = "dd123";
DVConstraint constraint = null;
CellRangeAddressList addressList = null;
DataValidation validation = null; // 数据验证
HSSFSheet category1Hidden = book.createSheet(hiddenSheet); // 创建隐藏域
for (int i = 0, length = textList.length; i < length; i++) { // 循环赋值(为了防止下拉框的行数与隐藏域的行数相对应来获取>=选中行数的数组,将隐藏域加到结束行之后)
sheet.createRow(endRows + i).createCell(8).setCellValue(textList[i]);
}
Name category1Name = book.createName();
category1Name.setNameName(hiddenSheet);
category1Name.setRefersToFormula(hiddenSheet + "!I1:A" + (textList.length + endRows));
constraint = DVConstraint.createFormulaListConstraint(hiddenSheet);
addressList = new CellRangeAddressList((short) firstRow, (short) endRows, (short) firstCol, (short) endCol);
validation = new HSSFDataValidation(addressList, constraint);
book.setSheetHidden(1, true); // 1隐藏、0显示
return validation;
}
调用全例:
参数分别为:fileName--导出的文件名称 titleList--标题行 downData--下拉数据 downRows--下拉行 dateRows--日期格式行 textRows文本格式行
public static boolean createExcelTemp(String fileName, String[] titleList, List<String[]> downData, String[] downRows,
String[] dateRows, String textRows[], HttpServletResponse response) {
try {
//创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//创建sheet表
HSSFSheet sheet = workbook.createSheet("sheet1");
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth(15);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
setStyle(style,workbook);
// 生成并设置另一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
setStyle2(style2,workbook);
// 声明一个画图的顶级管理器
// HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
//生成标题行
HSSFRow row = sheet.createRow(0);
int Cell = 0;
for(int i=0;i<titleList.length;i++) {
HSSFCell cell = row.createCell(Cell);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(titleList[i]);
cell.setCellValue(text);
Cell++;
}
//添加下拉列表
for(int i=0;i<downRows.length;i++) {
int num = Integer.parseInt(downRows[i]);
if(downData.get(i).length>=255){
sheet.addValidationData(setDataValidations(sheet,downData.get(i),1,10000,num,num,workbook));
}else{
sheet.addValidationData(setDataValidation(sheet,downData.get(i),1,10000,num,num));
}
}
//添加日期格式校验
for(int i=0;i<dateRows.length;i++) {
int num = Integer.parseInt(dateRows[i]);
sheet.addValidationData(setDate(1,10000,num,num));
}
//设置文本格式
CellStyle textStyle = workbook.createCellStyle();
DataFormat format = workbook.createDataFormat();
textStyle.setDataFormat(format.getFormat("@"));
for (int j = 1; j < 10000; j++) {
Row rowID = sheet.createRow(j);
for(int i=0;i<textRows.length;i++) {
int num = Integer.parseInt(textRows[i]);
Cell cell1 = rowID.createCell((short) num);
cell1.setCellStyle(textStyle);
}
}
System.out.println("导出文件名称:"+fileName);
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("gbk"), "iso8859-1")+".xls");
OutputStream ouputStream = response.getOutputStream();
workbook.write(ouputStream);
ouputStream.flush();
ouputStream.close();
return true;
} catch (IOException e) {
e.printStackTrace();
}
return false;
}