java导出excel

需求：导出页面报表中的指定数据（勾选的数据）

HTML：

<form id="sale_form" "return false" method="post" action="">
           <tr>
               <td><input type="checkbox" class="checkboxes" name="id" value=""/></td>
               <td></td>
               <td></td>
               <td></td>
               <td></td>
           </tr>
</form>

jq设置：
//导出数据到excel

 var excelExport=function () {

        $("#add",_currentTab).on('click',function () {
            if($("#table_list tbody tr .checkboxes:checked",_currentTab).length<1){
                bootbox.alert("请先选择需要操作的流水！");
                return;
            }
            //工具类中的导出excel不能使用ajax提交数据只能使用action进行数据提交
            //但是这里有一个问题，直接设置action的url会让网页一加载就提交，因此
            //要给form设置 "return false"属性，点击导出按钮进行下面操作
            $("#sale_form",_currentTab).removeAttr("onsubmit");//移除onsubmit属性，让表单可以提交
            $("#sale_form",_currentTab).attr("action","/promotionFlow/excelExport");//设置action属性
            $("#sale_form",_currentTab).submit();//提交事务
        })
    };

后台代码：

def excelExport(){
        def ids= params.id
        String orgs=String.join(",", ids);

        String fileName="促销档期销售报表";
        String sql="SELECT  d.ID, p.BILL_NO AS "促销单编码",i.ITEMS_NAME AS "促销商品",d.ORIGINAL_PRICE AS "原销售
        价",d.DISCOUNT_PRICE AS "优惠价",d.SALES_VOLUMES AS "销售数量",d.DISCOUNT_TYPE AS "优惠类型",d.DISCOUNT_AMOUNT AS 
        "优惠金额",d.CREATE_DATE AS "销售日期" FROM XXXX d LEFT JOIN  XXXX o ON o.ID = d.ORG_ID LEFT JOIN 
        PROMOTION p ON p.ID = d.PROMOTION_ID LEFT JOIN ITEMS i ON i.ID = d.ITEM_ID WHERE d.ID IN (${orgs})"

        println("response："+response)

        toolService.exportExcel(fileName, sql, response);

        render(status: 200)

    }

toolService.exportExcel(fileName, sql, response)调用的工具类：
这里的导出思路是将需要的数据通过sql查出来，然后再导出excel报表

 /**
     * 将信息导出到excel 中
     * @param fileName 导出后的文件名
     * @param sql 要导出的内容
     * @param response 以response 的方式反应
     * @return
     */

    def exportExcel(String fileName, String sql, HttpServletResponse response) {
        Connection connection = dataSource.getConnection()  //先获取connection
        PreparedStatement preparedStatement = connection.prepareStatement(sql)
        ResultSet resultSet = preparedStatement.executeQuery()
        // 获取结果集表头
        ResultSetMetaData md = resultSet.getMetaData()  //自动将获取的字段名设置为表头
        int columnCount = md.getColumnCount()
        // 读取工作薄
        Workbook wb = new SXSSFWorkbook()
        SXSSFSheet sheet1 = wb.createSheet("sheet1")
        //写表头
        Row row = (Row) sheet1.createRow(0)
        for (int j = 0; j < columnCount; j++) {
            Cell cell = row.createCell(j)
            cell.setCellValue(md.getColumnName(j + 1))
        }
        //写表
        int i = 0
        while (resultSet.next()) {
            i++
            //创建行
            row = (Row) sheet1.createRow(i)
            //创建列
            for (int j = 0; j < columnCount; j++) {
                Cell cell = row.createCell(j)
                cell.setCellValue(resultSet.getString(j + 1))
            }
        }
        //输出
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        response.setHeader("Content-Disposition",
                "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO-8859-1") + ".xlsx")
        //只有这样才能让文件名是中文的
        wb.write(response.outputStream)
        response.outputStream.flush();
    }