使用XSSFWorkbook导出xlsx表格

直接上代码，代码基本一行一注释，有看不懂的可以问我
首先Controller

	@Autowired
    XlsxService xlsxService;

    @RequestMapping(value = "/export")
    public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {

        xlsxService.exportExcel(request, response);
    }

Service

public interface XlsxService {
    void exportExcel(HttpServletRequest request, HttpServletResponse response) throws Exception;
}

Impl

@Override
    public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
        XSSFWorkbook xk = new XSSFWorkbook();

        //创建sheetname第一页页名
        XSSFSheet sheet = xk.createSheet("学生信息");
        XSSFSheet sheet1 = xk.createSheet("ibs");
        //字体样式
        XSSFFont xssfFont = xk.createFont();
        xssfFont.setBold(true);
        xssfFont.setFontName("黑体");
        xssfFont.setFontHeight(11);
        //表头样式
        XSSFCellStyle headStyle = xk.createCellStyle();
        //继承字体样式
        headStyle.setFont(xssfFont);
        //竖向居中，横向居中
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        //设置标题一行的单元格的填充颜色为灰色
        //headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headStyle.setWrapText(true);//设置自动换行
        headStyle.setHidden(true);//高度自动

        //todo 设置时间格式

        String[] toti = {"学生姓名", "班级id", "学生性别", "出生年月", "入读时间", "学生入读状态", "备注", "家长手机号", "家长姓名", "是否业主", "地址"};

        //创建第一行标题
        XSSFRow row1 = sheet.createRow(0);
        //设置宽度
        sheet.setDefaultColumnWidth(30);
        //设置标题
        for (int i = 0; i < toti.length; i++) {
            //第一行第一列
            XSSFCell Cell1 = row1.createCell(i);
            Cell1.setCellValue(toti[i]);
            Cell1.setCellStyle(headStyle);
        }

        //设置班级名称下拉
        String[] strings = {"1班","2班"};
        addValidation(sheet,strings,0,100000,1,1);

        //设置性别下拉
        String[] sex = {"男-1", "女-2"};
        addValidation(sheet, sex, 0, 100000, 2, 2);
        //0:未入读 1:在读 2:已休学  3:已退学-状态下拉
        String[] start = {"未入读-0", "在读-1", "已休学-2", "已退学-3"};
        addValidation(sheet, start, 0, 100000, 5, 5);
        //设置是否业主下拉
        String[] is = {"不是-0", "是-1"};
        addValidation(sheet, is, 0, 100000, 9, 9);

        setDateQx(sheet,1,100000,3,4);

        xk.setSheetHidden(1,true);
        //每次创建一行一列都需要调用方法可自己加工成工具类使用时只需要赋值即可
        try {
            //附件名称和格式
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment; filename=" + new String("学生信息".getBytes("gbk"), "iso8859-1") + ".xlsx");
            //转为二进制流进行吐出
            ServletOutputStream out = response.getOutputStream();
            xk.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            //log.error("Excel error", e);
        }
    }

校验单元格时间格式

/**
     * 校验时间格式
     * @param sheet
     * @param initialLine 开始行
     * @param overLint 结束行
     * @param initialColumn 开始列
     * @param overColumn 结束列
     */
    public void setDateQx(XSSFSheet sheet,int initialLine,int overLint,int initialColumn,int overColumn){
        //创建数据验证类
        DataValidationHelper helper = sheet.getDataValidationHelper();
        //设置验证生效的范围 四个参数 开始行,结束行,开始列,结束列
        CellRangeAddressList addressList = new CellRangeAddressList(initialLine, overLint, initialColumn,overColumn);
        //设置验证方式 四个参数,第一个参数常量,第二,第三是时间范
        //围 字符串格式要写成Date(2100, 1, 1)这种,第四时间格式
        DataValidationConstraint constraint = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN,"Date(2000, 1, 1)","Date(2100, 1, 1)","yyyy-MM-dd");
        //创建验证对象
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        //错误提示信息
        dataValidation.createErrorBox("提示","请输入[yyyy-MM-dd]格式日期,范围2000-1-1,2100-1-1");
        dataValidation.setShowErrorBox(true);
        //验证和工作簿绑定
        sheet.addValidationData(dataValidation);
    }

设置下拉框选项

/**
     * @param sheet
     * @param subjects 下拉选项
     * @param firstRow 第一个开始加下拉选项的行，从0开始，即第一行开始传入0
     * @param endRow   最后一个开始加下拉选项的行
     * @param firstCol 第一个开始加下拉选项的列，从0开始，即第一列开始传入0
     * @param endCol   最后一个开始加下拉选项的列
     * @throws Exception void
     * @author 杨超强
     * @date
     * @description :
     */
    public static void addValidation(XSSFSheet sheet, String[] subjects, int firstRow, int endRow, int firstCol,
                                     int endCol) throws Exception {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(subjects);
        CellRangeAddressList addressList = null;
        DataValidation dataValidation = null;
        // 设置数据有效性(下拉列)加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列
        addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        dataValidation = helper.createValidation(constraint, addressList);
        dataValidation.setShowErrorBox(true);
        sheet.addValidationData(dataValidation);
        addressList = null;
        dataValidation = null;

    }

上面这种下拉框比较简单，但是有个问题，下拉框里面的字节数有限制，从而产生了下面这种，采用两个表格，把下拉内容可以填充在第二个表里

 /**
     *
     * @param sheet
     */
    public void setShee(XSSFSheet sheet) {
        String strFormula = "ibs!$A$1:$A$100";
        XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, strFormula);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(0, 100000, 1, 1);
        // 数据有效性对象
        DataValidationHelper help = new XSSFDataValidationHelper((XSSFSheet) sheet);
        DataValidation validation = help.createValidation(constraint, regions);
        sheet.addValidationData(validation);
    }