public void exportExcel(List<List<String>> list,HttpServletResponse response,String fileName) throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheetCs = wb.createSheet("sheet1");
// 为首行赋值
for(int i=0;i<list.size();i++){XSSFRow row = sheetCs.createRow(i);
for(int j=0;j<list.get(i).size();j++){sheetCs.setColumnWidth(j, 3500);// 设置列宽
XSSFCell cell = row.createCell(j);
cell.setCellValue(list.get(i).get(j));
}
}
int sheetTotal=wb.getNumberOfSheets();
XSSFRow row;
// 第一个下拉选
// 新建一个 sheet 页
String hiddenSheetName = "hiddenSheet" + sheetTotal;
XSSFSheet hiddenSheet = wb.createSheet(hiddenSheetName);
// 数据
String[] selectList =new String[100]; // 对应列下拉框数据
for(int i=0;i<100;i++){selectList[i]="测试数据"+i;
}
// 写入下拉数据到新的 sheet 页中
for (int i = 0; i < selectList.length; i++) {row = hiddenSheet.createRow(i);
XSSFCell cell = row.createCell(0);
cell.setCellValue(selectList[i]);
}
// 获取新 sheet 页内容
String strFormula = hiddenSheetName + "!$A$1:$A$65535";
XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST,strFormula);
// 设置数据有效性加载在哪个单元格上, 四个参数分别是:起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(1,65535, 0, 0);
// 数据有效性对象
DataValidationHelper help = new XSSFDataValidationHelper((XSSFSheet) sheetCs);
DataValidation validation = help.createValidation(constraint, regions);
sheetCs.addValidationData(validation);
// 将新建的 sheet 页隐藏掉
wb.setSheetHidden(sheetTotal, true);
sheetTotal++;
// 第二个下拉选
hiddenSheetName = "hiddenSheet" + sheetTotal;
XSSFSheet hiddenSheet2 = wb.createSheet(hiddenSheetName);
String[] selectList2 =new String[100]; // 对应列下拉框数据
for(int i=0;i<100;i++){selectList2[i]="测试数据 2"+i;
}
for (int i = 0; i < selectList2.length; i++) {row = hiddenSheet2.createRow(i);
XSSFCell cell = row.createCell(0);
cell.setCellValue(selectList2[i]);
}
String strFormula2 = hiddenSheetName + "!$A$1:$A$65535";
XSSFDataValidationConstraint constraint2 = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST,strFormula2);
CellRangeAddressList regions2 = new CellRangeAddressList(1,65535, 1, 1);
DataValidationHelper help2 = new XSSFDataValidationHelper((XSSFSheet) sheetCs);
DataValidation validation2 = help2.createValidation(constraint2, regions2);
sheetCs.addValidationData(validation2);
wb.setSheetHidden(sheetTotal, true);
sheetTotal++;
try {OutputStream output = response.getOutputStream();
fileName = URLEncoder.encode(fileName,"UTF-8");
response.addHeader("Content-Disposition", "inline;filename="+fileName+".xls");
response.setContentType("application/msexcel");
wb.write(output);
output.flush();
output.close();} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();}
// 结束
System.out.println("Over");
}