Controller.
@RequestMapping("/export")
public void test(HttpServletResponse response){
//我需要导出的list
List<NatureDisasterFactor> list = dictDataService.findByDictId(dictLevel.getId());
//下拉列表的数据
List<String> dictTypeList = new ArrayList<>();
ArrayList<String> arrayList = new ArrayList<>();
for (int i = 0; i <10000 ; i++) {
arrayList.add(String.valueOf(i));
}
ExportParams exportParams = new ExportParams("模板", "模板", ExcelType.XSSF);
Workbook workbook = ExcelExportUtil.exportExcel(exportParams,NatureDisasterFactor.class, list);
ExcelUtils.selectLargeList(workbook,2,2,3,dictTypeList .toArray(new String[dictTypeList .size()]));
ExcelUtils.downLoadExcel("模板",response,workbook);
}
Excel导出工具类
ExcelUtils.java
/**
* 用于下拉内容很多,字符超过255
* 下拉选项(先将数据放到另一个sheet页,然后下拉的数据再去sheet页读取,解决普通下拉数据量太多下拉不显示问题)
* sheetIndex 创建的sheet的idex。如果有多个下拉想放到sheet页,则需要设置不同的sheetIndex,(注意不能设置为0,0为主数据页)
* firstRow 开始行号(下标0开始)
* lastRow 结束行号,最大65535
* firstCol 区域中第一个单元格的列号 (下标0开始)
* lastCol 区域中最后一个单元格的列号
* dataArray 下拉内容
* sheetHidden 影藏的sheet编号(例如1,2,3),多个下拉数据不能使用同一个
*/
public static void selectLargeList(Workbook workbook, int firstCol, int lastCol,int sheetIndex, String[] selectList){
Sheet sheet = workbook.getSheetAt(0);
//将下拉框数据放到新的sheet里,然后excle通过新的sheet数据加载下拉框数据
String sheetName="sheetName"+String.valueOf(sheetIndex);
Sheet hidden = workbook.createSheet(sheetName);
//创建单元格对象
Cell cell =null;
//遍历我们上面的数组,将数据取出来放到新sheet的单元格中
for (int i = 0, length = selectList.length; i < length; i++){
//取出数组中的每个元素
String name = selectList[i];
//根据i创建相应的行对象(说明我们将会把每个元素单独放一行)
Row row = hidden.createRow(i);
//创建每一行中的第一个单元格
cell = row.createCell(0);
//然后将数组中的元素赋值给这个单元格
cell.setCellValue(name);
}
// 创建名称,可被其他单元格引用
String refers = sheetName+"!$A$1:$A$" + selectList.length;
//这个3代表我的下拉数据从第三行开始生效,前面1,2行不显示下拉,你们根据自己情况修改。
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(3, 90000, firstCol, lastCol);
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
//将第sheetIndex个sheet设置为隐藏
workbook.setSheetHidden(sheetIndex, true);
sheet.addValidationData(dataValidation);
}
/**
* 下载
*
* @param fileName 文件名称
* @param response
* @param workbook excel数据
*/
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
workbook.write(response.getOutputStream());
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
到这里就结束啦!