使用easypoi导出

1
2
3
4
5
6
7
8
9
10
11
12
13
ExportParams exportParams = new ExportParams("title", "sheetName", ExcelType.XSSF);
exportParams.setCreateHeadRows(true);
String[] strings = new String[]{"李四", "王五", "小明"};

int firstCol = 2;
int lastCol = 2;
ArrayList<User> list = new ArrayList<>();

Workbook workbook = ExcelExportUtil.exportExcel(exportParams, User.class, list);

selectList(workbook, 2, 65535, 3, 3, strings, 1);

EasyExcelUtils.downLoadExcel("测试数据", response, workbook);

工具类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
/**
 * @param firstRow    开始行号(下标0开始)
 * @param lastRow     结束行号，最大65535
 * @param firstCol    区域中第一个单元格的列号 (下标0开始)
 * @param lastCol     区域中最后一个单元格的列号
 * @param dataArray   下拉内容
 * @param sheetHidden 影藏的sheet编号（例如1,2,3），多个下拉数据不能使用同一个
 * @see <a href="http://poi.apache.org/components/spreadsheet/quick-guide.html#Validation"> POI官网</a>
 */
public static void selectList(Workbook workbook, int firstRow, int lastRow, int firstCol, int lastCol, String[] dataArray, int sheetHidden) {

    // 创建一个隐藏的sheet,用来存放下拉框数据
    String hiddenName = "hidden_" + (int) ((Math.random() * 9 + 1) * 100);
    Sheet sheet = workbook.getSheetAt(0);
    Sheet hidden = workbook.createSheet(hiddenName);
    Cell cell = null;
    for (int i = 0, length = dataArray.length; i < length; i++) {
        String name = dataArray[i];
        Row row = hidden.createRow(i);
        cell = row.createCell(0);
        cell.setCellValue(name);
    }

    Name namedCell = workbook.createName();
    namedCell.setNameName(hiddenName);

    // Excel下拉框引用公式
    namedCell.setRefersToFormula(hiddenName + "!$A$1:$A$" + dataArray.length);
    //加载数据,将名称为hidden的
    DVConstraint constraint = DVConstraint.createFormulaListConstraint(hiddenName);

    //  导出XLS
    // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
    HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);


    // XLSX
    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
    DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(hiddenName);
    dvConstraint.setOperator(0);
    CellRangeAddressList xssfAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
    XSSFDataValidation xssfDataValidation = (XSSFDataValidation) dvHelper.createValidation(
            dvConstraint, xssfAddressList);
    // 是否显示错误提示框(XSSFDataValidation默认为false,HSSFDataValidation默认为true)
    xssfDataValidation.setShowErrorBox(true);
    // 错误提示框的提示内容
    xssfDataValidation.createErrorBox("Hello", "World");
    // 将sheet设置为隐藏
    workbook.setSheetHidden(sheetHidden, true);
    sheet.addValidationData(xssfDataValidation);
}

导出工具类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/**
 * 下载
 *
 * @param fileName 文件名称
 * @param response
 * @param workbook excel数据
 */
public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
    try {
        response.reset();
        response.setCharacterEncoding("ISO8859-1");
        response.setContentType("application/vnd.ms-excel");
        String downloadFileName = new String(fileName.getBytes(), "ISO8859-1") + "." + ExcelTypeEnum.XLSX.getValue();
        response.setHeader("Content-Disposition", "attachment;filename=" + downloadFileName);
        response.addHeader("Pargam", "no-cache");
        response.addHeader("Cache-Control", "no-cache");
        workbook.write(response.getOutputStream());
    } catch (Exception e) {
        throw new IOException(e.getMessage());
    }
}