java调用sqlserver存储过程

package com.senlany.service.impl.k3;

import java.io.IOException;
import java.io.OutputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.senlany.comm.ConnUtil;
import com.senlany.mapper.k3.FinancialReportMapper;
/**
 * @author Amos   E-mail : zhangxiaopeng@wlspjt.com
 *    @version 创建时间：2015年5月28日 下午2:36:37
 *  @description
 *  因程序未按照标准java程序结构创建
 *  现将程序逻辑放在service中
 */
@Service
public class FinancialReportService {
     private static Log log = LogFactory.getLog(FinancialReportService.class);     
    @Autowired
    private FinancialReportMapper financialReportMapper;   

    /**
     * @param map
     * @return
     */
    public List<Map> getList(Map map){
        return financialReportMapper.getList(map);
    }
   
    /**
     * 导出财务报表
     * @param begindate
     * @param enddate
     */
    public void exportExcel(HttpServletRequest request, HttpServletResponse response){
       
        Connection conn = null;
        Statement st  = null;
        ResultSet rs = null;
        conn = ConnUtil.getConn();
        WritableWorkbook workbook = null;
        WritableSheet sheet = null;
        Label label = null;
        Label label0 = null;
       
       
        try {
       
            /**
             * 此程序块必须放在此处执行
             * 因此处调用函数中有批量更新操作
             */
             conn.setAutoCommit(false);
             st = (Statement) conn.createStatement();
             st.addBatch("dbo.WL_F_MAIN");
             st.executeBatch();
             conn.commit();
             
             //查询全局临时表
             CallableStatement  cs = conn.prepareCall("SELECT * FROM ##financial_report_table");    
             rs = cs.executeQuery();  
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 
 
        // 创建Excel表
        try {
            /**
             * 将工作薄输出至前台文件输出流
             */
           String contentType = "application/vnd.ms-excel";
           response.reset();
           response.setContentType(contentType);
           response.setHeader("Content-Disposition", "attachment; filename=\"" + new String("休闲营销财务报表.xls".getBytes("gb2312"), "ISO8859-1") + "\"");
           OutputStream out = response.getOutputStream();              
           workbook = Workbook.createWorkbook(out);
            
             // 创建Excel表中的sheet
            sheet = workbook.createSheet("First Sheet", 0);
         
            // 向Excel中添加数据
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            String colName = null;
            int row = 2;
           
            int income_count = 0;//收入计数
            int inc_cat_xxd_ct = 0;//收入_休闲豆计数
            int inc_cat_rsb_ct = 0;//收入_肉松饼计数
            int inc_cat_dxm_ct = 0;//收入_点心面计数
           
            int gift_count = 0;//搭赠计数
            int gif_cat_xxd_ct = 0;//搭赠_休闲豆计数
            int gif_cat_rsb_ct = 0;//搭赠_肉松饼计数
            int gif_cat_dxm_ct = 0;//搭赠_点心面计数
           
            int fee_count = 0;//运费计数
         
            // 添加标题
            for (int i = 0; i < columnCount; i++) {
                colName = rsmd.getColumnName(i + 1);
                if("FCustID".equals(colName)){
                    colName = "客户编号";
                    label = new Label(i, 0, colName);
                }else if("BIGAREA".equals(colName)){
                    colName = "大区";
                    label = new Label(i, 0, colName);
                }else if("AREAMANAGER".equals(colName)){
                    colName = "大区经理";
                    label = new Label(i, 0, colName);
                }else if("PROVINCE".equals(colName)){
                    colName = "省区";
                    label = new Label(i, 0, colName);
                }else if("PROVINCEMANAGER".equals(colName)){
                    colName = "省区经理";
                    label = new Label(i,0, colName);
                }else if("CLIENT".equals(colName)){
                    colName = "客户";
                    label = new Label(i,0, colName);
                }else if("SALESMAN".equals(colName)){
                    colName = "业务员";
                    label = new Label(i,0, colName);
                } else if("DATE".equals(colName)){
                    colName = "日期";
                    label = new Label(i, 0, colName);
                }else{
                    if(colName.contains("QUANTITY")){
                        label = new Label(i, row+1, "数量");
                        sheet.addCell(label);
                    }else if(colName.contains("PRICE")){
                        label = new Label(i, row+1, "单价");
                        sheet.addCell(label);
                    }else if(colName.contains("AMOUNT")){
                        label = new Label(i, row+1, "金额");
                        sheet.addCell(label);
                    }
                    label = new Label(i, row, colName);
                }    
               
                if(colName.endsWith("0")){
                        income_count++;
                        if(colName.startsWith("xxd")){
                            inc_cat_xxd_ct ++;
                        }else if(colName.startsWith("rsb")){
                            inc_cat_rsb_ct ++;
                        }else{
                            inc_cat_dxm_ct ++;
                        }
                       
                }else if(colName.endsWith("1")){
                        gift_count++;
                        if(colName.startsWith("xxd")){
                            gif_cat_xxd_ct ++;
                        }else if(colName.startsWith("rsb")){
                            gif_cat_rsb_ct ++;
                        }else{
                            gif_cat_dxm_ct ++;
                        }
                }else if(colName.endsWith("3")){
                        fee_count++;
                }
                log.debug("标题:"+i+"---"+row +"---"+ colName);
                sheet.addCell(label);
            }
            /**
             * 单元格合并各参数含义
             * mergeCells(startColNum, startRowNum, endColNum, endRowNum);
             */
            sheet.mergeCells(0, 0, 0, 3);
            sheet.mergeCells(1, 0, 0, 3);
            sheet.mergeCells(2, 0, 0, 3);
            sheet.mergeCells(3, 0, 0, 3);
            sheet.mergeCells(4, 0, 0, 3);
            sheet.mergeCells(5 ,0, 0, 3);
            sheet.mergeCells(6, 0, 0, 3);
            sheet.mergeCells(7, 0, 0, 3);
           
            sheet.mergeCells(8, 0,7+income_count, 0);
            label = new Label(8, 0, "销售收入");
            sheet.addCell(label);
            sheet.mergeCells(8, 1,7+inc_cat_xxd_ct,1);
            label = new Label(8,1, "休闲豆");
            sheet.addCell(label);
            sheet.mergeCells(8+inc_cat_xxd_ct, 1,7+inc_cat_xxd_ct+inc_cat_rsb_ct,1);
            label = new Label(8+inc_cat_xxd_ct, 1, "肉松饼");
            sheet.addCell(label);
            sheet.mergeCells(8+inc_cat_xxd_ct+inc_cat_rsb_ct, 1,7+inc_cat_xxd_ct+inc_cat_rsb_ct+inc_cat_dxm_ct,1);
            label = new Label(8+inc_cat_xxd_ct+inc_cat_rsb_ct, 1, "点心面");
            sheet.addCell(label);
           
            sheet.mergeCells(8+income_count, 0,7+income_count+gift_count,0);
            label = new Label(8+income_count, 0, "搭赠");
            sheet.addCell(label);
            sheet.mergeCells(8+income_count, 1,7+income_count+gif_cat_xxd_ct, 1);
            label = new Label(8+income_count, 1, "休闲豆");
            sheet.addCell(label);
            sheet.mergeCells(8+income_count+gif_cat_xxd_ct, 1,7+income_count+gif_cat_xxd_ct+gif_cat_rsb_ct,1);
            label = new Label(8+income_count+gif_cat_xxd_ct, 1, "肉松饼");
            sheet.addCell(label);
            sheet.mergeCells(8+income_count+gif_cat_xxd_ct+gif_cat_rsb_ct, 1,7+income_count+gif_cat_xxd_ct+gif_cat_rsb_ct+gif_cat_dxm_ct,1);
            label = new Label(8+income_count+gif_cat_xxd_ct+gif_cat_rsb_ct, 1, "点心面");
            sheet.addCell(label);
           
            sheet.mergeCells(8+income_count+gift_count, 0,7+income_count+gift_count+fee_count,0);
            label = new Label(8+income_count+gift_count, 0, "运费");
            sheet.addCell(label);
            row += 2;
//               row ++;
            log.debug("写入标题成功");
            while (rs.next()) {
                for (int i = 0; i < columnCount; i++) {
                    label = new Label(i, row, rs.getString(i + 1));
                    log.debug("行:"+i+"---"+row +"---"+ rs.getString(i+1));
                    sheet.addCell(label);
                }
                row++;
            }
            log.debug("写入内容成功");
            // 关闭文件
            workbook.write();
            workbook.close();
            log.info("数据成功写入Excel");
        } catch (SQLException e) {
              log.debug(e.getMessage());
        } catch (RowsExceededException e) {
              log.debug(e.getMessage());
        } catch (WriteException e) {
              log.debug(e.getMessage());
        } catch (IOException e) {
              log.debug(e.getMessage());
        } finally {
                try {
                     workbook.close();
                        //删除全局临时表
                        st = (Statement) conn.createStatement();
                        st.addBatch("if object_id('tempdb..##financial_report_table') is not null      DROP TABLE ##financial_report_table;");
                        st.executeBatch();
                        conn.commit();
                        //释放数据库相关链接
                    ConnUtil.free(null, st, conn);
                   
                } catch (Exception e) {
                      log.debug(e.getMessage());
                }
        }
    }   
   
}