Java 使用poi自定义下载Excel模板

poi自定义Excel模板

设置下拉框以及悬浮提示信息

首先导入maven依赖

		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>

设置下载模板的样式

 @RequestMapping("download")
    public void download(HttpServletRequest request, HttpServletResponse response) throws Exception{
        //excel第一行标题内容
        String title[] = new String[]{"姓名", "姓名全拼", "性别","身份证证件类型","身份证证件号码","出生日期","手机号码","电子邮箱"};
        //工作薄名称
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("用户信息表");
        //列宽
        sheet.setDefaultColumnWidth(20);
        //设置单元格下拉框
        String [] sex = {"男","女"};
        String [] identity = {"居民身份证","士官证","学生证","驾驶证","护照","港澳通行证"};
        // 设置多少列为下拉框并给赋值
        createDropDownList(sheet,sex,0,10000,2,2);
        createDropDownList(sheet,identity,0,10000,3,3);
        setHSSFPrompt(sheet,"提示","填写样式：yyyy-MM-dd",0,10000,5,5);
        // 樣式
        HSSFCellStyle headStyle = workbook.createCellStyle();
        headStyle.setFillForegroundColor((short) 70);
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        HSSFDataFormat dataFormat = workbook.createDataFormat();
        headStyle.setDataFormat(dataFormat.getFormat("@"));
        // 居中
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        HSSFDataFormat format = workbook.createDataFormat();
        //设置单元格格式为常规
        headStyle.setDataFormat(format.getFormat("@"));
        //设置头格式(第一行)
        HSSFCellStyle columnHeadStyle = workbook.createCellStyle();
        // 居中
        columnHeadStyle.setAlignment(HorizontalAlignment.CENTER);

        int rowNO = 0;
        HSSFRow row = null;
        HSSFCell cell = null;
        row = sheet.createRow(rowNO++);
        row.setHeight((short) 400);
        //两个for循环，生成execl的标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(headStyle);
        }
        //excel文件名
        String fileName = "用户上传模板.xls";
        //告诉浏览器返回文件下载
        response.setContentType("application/download;charset=UTF-8");
        //**文件下载保存框
        response.setHeader("Content-disposition", "attachment;filename=\""
                + new String(fileName.getBytes("UTF-8"), "ISO8859_1") + "\"");
        OutputStream out = response.getOutputStream();
        workbook.write(out);
        out.close();
    }

    /**
     * 设置下拉框的方法
     * @param sheet : 传参 哪一sheet的表
     * @param values : 下拉框的内容
     * @param firstRow : 下拉框从哪一行开始
     * @param lastRow : 下拉框到哪一行结束
     * @param firstCol : 下拉框从哪一列开始
     * @param lastCol : 下拉框到哪一列结束
     */
    public void createDropDownList(Sheet sheet, String[] values, int firstRow, int lastRow, int firstCol, int lastCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();

        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        // 设置下拉框数据
        DataValidationConstraint constraint = helper.createExplicitListConstraint(values);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);

        // Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }

        sheet.addValidationData(dataValidation);
    }
    /**
     * 设置单元格上提示
     *
     * @param sheet
     *            要设置的sheet.
     * @param promptTitle
     *            标题
     * @param promptContent
     *            内容
     * @param firstRow
     *            开始行
     * @param endRow
     *            结束行
     * @param firstCol
     *            开始列
     * @param endCol
     *            结束列
     * @return 设置好的sheet.
     */
    public static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle,
                                          String promptContent, int firstRow, int endRow, int firstCol,
                                          int endCol) {
        // 构造constraint对象
        DVConstraint constraint = DVConstraint
                .createCustomFormulaConstraint("BB1");
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow,
                endRow, firstCol, endCol);
        // 数据有效性对象
        HSSFDataValidation data_validation_view = new HSSFDataValidation(
                regions, constraint);
        data_validation_view.createPromptBox(promptTitle, promptContent);
        sheet.addValidationData(data_validation_view);
        return sheet;
    }
}