Stored Procedure Temp Table Excel Template Export

public void ExecuteSP_MonthPreIncome(string year, string month)
  {
   try
   {
    using(System.Data.OleDb.OleDbConnection conn = SqlServerHelper.getConnection())
    {
     OleDbParameter spParms1 = new OleDbParameter("begDate", SqlDbType.VarChar);
     OleDbParameter spParms2 = new OleDbParameter("endDate", SqlDbType.VarChar);

     string nextmonth = System.String.Format("{0:00}",Convert.ToInt32(month)+1);
     spParms1.Value = year+"-"+month+"-01";    
     spParms2.Value = year+"-"+nextmonth+"-01";
     
     conn.Open();
     OleDbCommand cmd = new OleDbCommand();
     cmd.Connection = conn;
     cmd.CommandType = CommandType.StoredProcedure;
     cmd.Parameters.Add(spParms1);
     cmd.Parameters.Add(spParms2);

     cmd.CommandText = "sp_Month_PreIncome";

     cmd.ExecuteNonQuery();
    }
   }
   catch(Exception ex)
   {
    throw new Exception("统计 预收费用户当月扣款收入 失败！",ex);
   }
  }
  /// <summary>
  /// 预收费用户当月扣款收入
  /// </summary>
  /// <param name="year">year</param>
  /// <param name="month">month</param>
  public void ExportMonthPreIncome(string year, string month)
  {
   try
   {
    string SheetName = Common.EXCELRPT_TYPE.预收费收入.ToString()+"_"+year+month;
    string filename = Common.Common.AppPath+"\\Reports\\"+SheetName+".xls";
  
    //复制刚才创建的模板文件为新文件，建议将模板文件放入数据库中，使用时将文件下载到本地
    FileInfo mode=new FileInfo(Common.Common.AppPath+"\\预收费用户当月扣款收入.xls");
    try
    {
     mode.CopyTo(filename,true);
    }
    catch(Exception ee)
    {
     MessageBox.Show(ee.Message);
     return;
    } 

    //打开复制后的文件
    object missing=Missing.Value;
    Excel.Application myExcel=new Excel.Application ( );
    //打开新文件
    myExcel.Application.Workbooks.Open(filename,
     missing,
     missing,
     missing,
     missing,
     missing,
     missing,
     missing,
     missing,
     missing,
     missing,
     missing,
     missing);
    //将Excel显示出来
    myExcel.Visible=true;

    //=====================================================================//
    //读入第一个单元格的内容，从该单元格中得到实际内容应该从什么地方开始写入
    //=====================================================================//
    //得到当前的工作簿
    Excel.Workbook myBook=myExcel.Workbooks[1];
    Excel.Worksheet mySheet=(Excel.Worksheet)myBook.Worksheets[1];
    Excel.Range r=mySheet.get_Range(mySheet.Cells[6,1],mySheet.Cells[6,1]); 
    string strValue=r.Value.ToString();
    mySheet.Cells[6,1]="";//清空存放起始位置数据的单元格
    mySheet.Name = SheetName;

    //分离出起始位置和列数
    int startx,starty,len;
    string[] strInfo=strValue.Split(',');
    starty=int.Parse(strInfo[0]);
    startx=int.Parse(strInfo[1]);
    len=int.Parse(strInfo[2]);

    int i = 0;
    string sql = "select * from tmp_month_preincome";
    using(System.Data.OleDb.OleDbConnection conn = SqlServerHelper.getConnection())
    {
     conn.Open();
     //
     OleDbCommand cmd = new OleDbCommand(sql, conn,null);
     OleDbDataReader myReader = cmd.ExecuteReader();
     while(myReader.Read())
     {  
      if(!myReader.IsDBNull(0))      
       myExcel.Cells[starty+i,startx+0]="'"+myReader.GetString(0);
      if(!myReader.IsDBNull(1))      
       myExcel.Cells[starty+i,startx+1]= myReader.GetDecimal(1);
      if(!myReader.IsDBNull(2))      
       myExcel.Cells[starty+i,startx+2]= myReader.GetInt32(2);
      if(!myReader.IsDBNull(3))      
       myExcel.Cells[starty+i,startx+3]= myReader.GetInt32(3);
      if(!myReader.IsDBNull(4))      
       myExcel.Cells[starty+i,startx+4]= myReader.GetInt32(4);
      if(!myReader.IsDBNull(5))      
       myExcel.Cells[starty+i,startx+5]= myReader.GetInt32(5);
      if(!myReader.IsDBNull(6))      
       myExcel.Cells[starty+i,startx+6]= myReader.GetDecimal(6);
      if(!myReader.IsDBNull(7))      
       myExcel.Cells[starty+i,startx+7]= myReader.GetDecimal(7);
      if(!myReader.IsDBNull(8))      
       myExcel.Cells[starty+i,startx+8]= myReader.GetDecimal(8);
      if(!myReader.IsDBNull(9))      
       myExcel.Cells[starty+i,startx+9]= myReader.GetDecimal(9);
      if(!myReader.IsDBNull(10))      
       myExcel.Cells[starty+i,startx+10]= myReader.GetDecimal(10);
      if(!myReader.IsDBNull(11))      
       myExcel.Cells[starty+i,startx+11]= myReader.GetDecimal(11);
      //
      i++;
     }
       
    }
    #region
    //
    //   //,本例中共有10行数据（第一行不是数据），实际时可以用MyDataSet.Tables[myTableName].Rows.Count之类来判断
    //   int nCount=10;
    //   //逐列写入数据，数组中第一行我列标题，忽略
    //   for(int j=0;j<len;j++)
    //   {
    //    //得到当前列的字段名称，本例中不使用
    //    //r=mySheet.get_Range(mySheet.Cells[starty,startx+j],mySheet.Cells[starty,startx+j]);
    //    //string strFieldName=r.Value.ToString();
    //    //逐行写入本列数据
    //    for(int i=0;i<nCount;i++)
    //    {
    //     //以单引号开头，表示该单元格为纯文本
    //     myExcel.Cells[starty+i,startx+j]="'"+myData[i+1,j];
    //     //实际使用时用myExcel.Cells[starty+i,startx+j]="'"+row[strFieldName].ToString();，这也就是为什么
    //     //要提取字段名的原因
    //    }
    //   }
   
    #endregion

    
    r=mySheet.get_Range(mySheet.Cells[starty+i,startx],mySheet.Cells[starty+i,len]);
    r.Select();
    r.Cells.Interior.ColorIndex = 40;

    //求和
    int rowsum = starty+i-6;
    r.Cells.FormulaR1C1 = "=SUM(R[-"+rowsum.ToString()+"]C:R[-1]C)";
   
    //
    myExcel.Cells[starty+i,1]="'本月合计";
    myExcel.Cells[starty+i,startx+12]="'";
  
    //核查事项
    int addrows = 7;
    for(int k=1;k<=addrows;k++)
    {
     myExcel.Cells[starty+i+k,1]="'";
    }   
    addrows += 1;
    myExcel.Cells[starty+i+addrows,1]="'需核查事项小计";
    r=mySheet.get_Range(mySheet.Cells[starty+i+addrows,startx],mySheet.Cells[starty+i+addrows,len]);
    r.Select();
    r.Cells.Interior.ColorIndex = 40;

    //将列标题和实际内容选中
    myBook=myExcel.Workbooks[1];
    mySheet=(Excel.Worksheet)myBook.Worksheets[1];
    r=mySheet.get_Range(mySheet.Cells[starty-1,startx],mySheet.Cells[starty+i+addrows,len]);
    r.Select();
    //=====通过执行宏来格表格加边框=======//
    try
    {
     myExcel.Run("加边框",missing,missing,

      missing,missing,missing,missing,missing,missing,missing,

      missing,missing,missing,missing,missing,missing,missing,

      missing,missing,missing,missing,missing,missing,missing,

      missing,missing,missing,missing,missing,missing,missing);
    }
    catch
    {
    }

    //保存修改
    myBook.Save();      
   }    
   catch(Exception )
   {
   }
  
  }

CREATE PROCEDURE [sp_Month_PreIncome]
 @begDate varchar(20),
 @endDate varchar(20)
AS

declare @cpChargeId varchar(20)
declare @orgId varchar(20) --
declare @amount decimal(8,2)

--申明变量
declare @orgName varchar(200) --单位
declare @preamount decimal(8,2) --预收金额
declare @corpcard int --法人卡
declare @opercard int --操作员卡
declare @corpcardopen int --开户
declare @opercardopen int
declare @corpcardupdt int --更新
declare @opercardupdt int
declare @yearfee decimal(8,2)--年费
declare @funcfee1 decimal(8,2)--年费之增值功能费
declare @flowfee decimal(8,2)--流量费
declare @funcfee2 decimal(8,2)--流量费之增值功能费
declare @sumamout decimal(8,2)--收款总额
declare @remain decimal(8,2)--预付费余额
 
--申明汇总变量
declare @sumpreamount decimal(8,2) --预收金额
declare @sumcorpcardopen int --开户
declare @sumopercardopen int
declare @sumcorpcardupdt int --更新
declare @sumopercardupdt int
declare @sumyearfee decimal(8,2)--年费
declare @sumfuncfee1 decimal(8,2)--年费之增值功能费
declare @sumflowfee decimal(8,2)--流量费
declare @sumfuncfee2 decimal(8,2)--流量费之增值功能费
declare @sumsumamout decimal(8,2)--收款总额
declare @sumremain decimal(8,2)--预付费余额

set @sumpreamount = 0
set @sumcorpcardopen = 0
set @sumopercardopen = 0
set @sumcorpcardupdt = 0
set @sumopercardupdt = 0
set @sumyearfee = 0
set @sumfuncfee1 = 0
set @sumflowfee = 0
set @sumfuncfee2 = 0
set @sumsumamout = 0
set @sumremain = 0

--删除数据
delete from tmp_month_preincome

--遍历T_Bill, 查找OrgID及其OrgName、余额
declare cur1 cursor for
select DISTINCT b.OrgID, o.OrgName, r.Remain
from T_Bill b
left join T_OrgInfo o on b.OrgID = o.OrgCode
left join T_OrgRemain r on r.OrgId = b.OrgId
where b.Recondate >= @begDate and b.Recondate < @endDate and b.Status = 2 and b.AutoFlag = 1

open cur1
fetch next from cur1 into @orgId,@orgName,@remain
while @@fetch_status = 0
 begin
   --本月充值总额
   declare cur3 cursor for
   select sum(Amount) as summnt from T_CashFlow
   where OperationTime >= @begDate and OperationTime < @endDate and OrgID = @orgId and OperationType = 1
   open cur3
   fetch from cur3 into @preamount
   close cur3
   deallocate cur3

   --根据OrgID查找其所有账单
   declare cur2 cursor for
   select b.ReconAmt,b.cpChargeID,
   c.CorpCardCount, c.OperCardCount
 from T_Bill b
 left join T_Card c on c.BillID = b.BillId
 where b.OrgID = @orgId and b.Recondate >= @begDate and b.Recondate < @endDate
  and b.Status = 2 and b.AutoFlag = 1
 open cur2

 set @yearfee = 0
 set @funcfee1 = 0
 set @flowfee = 0
 set @funcfee2 = 0
 declare @isyearfee int, @addvalue decimal(8,2)
 set @isyearfee = 0
 set @addvalue = 0
 set @sumamout = 0

 fetch next from cur2 into @amount,@cpChargeId, @corpcard, @opercard
 while @@fetch_status = 0
         begin
    --汇总账单  
  if(@cpChargeId='01010000')   --如果计费类型是开户费，查找法人卡和操作员卡
   begin
    set @corpcardopen = @corpcardopen + @corpcard * 380
    set @opercardopen = @opercardopen + @opercard * 250
    set @sumamout = @sumamout + @corpcardopen + @opercardopen
   end
   else if(@cpChargeId = '01020000')--如果是证书更新费，查找法人卡和操作员卡
   begin
    set @corpcardupdt = @corpcardupdt + @corpcard * 380
    set @opercardupdt = @opercardupdt + @opercard * 250
    set @sumamout = @sumamout + @corpcardupdt + @opercardupdt
   end
   else if(@cpChargeId = '01030101')--如果是年费   
   begin
    set @yearfee = @yearfee + @amount
    set @sumamout = @sumamout + @yearfee
    set @isyearfee = 1
   end
    else if(@cpChargeId = '01030400')--如果是流量费  
   begin
    set @flowfee = @flowfee + @amount
    set @sumamout = @sumamout + @flowfee   
   end
   else if(@cpChargeId = '01040000')--如果是增值功能费 
   begin
    set @addvalue = @addvalue + @amount
    set @sumamout = @sumamout + @addvalue
   end
    
   --下一个账单
   fetch next from cur2 into @amount,@cpChargeId, @corpcard, @opercard
  end

 close cur2
 deallocate cur2

 --是否是年费用户
 if  @isyearfee = 1
  begin
  set @funcfee1 = @addvalue
         end
 else
  begin
  set @funcfee2 = @addvalue
         end
 
 --写入临时表
 --to do
 insert into tmp_month_preincome
 (
  orgName,
  preamount ,
  corpcardopen,
  opercardopen,
  corpcardupdt,
  opercardupdt,
  yearfee ,
  funcfee1 ,
  flowfee ,
  funcfee2 ,
  sumamout ,
  remain
 )values
 (@orgName,@preamount,@corpcardopen, @opercardopen, @corpcardupdt, @opercardupdt, @yearfee, @funcfee1, @flowfee, @funcfee2, @sumamout, @remain)
 
 --汇总 
 set @sumpreamount = @sumpreamount + @preamount
 set @sumcorpcardopen = @sumcorpcardopen + @corpcardopen
 set @sumopercardopen = @sumopercardopen + @opercardopen
 set @sumcorpcardupdt = @sumcorpcardupdt + @corpcardupdt
 set @sumopercardupdt = @sumopercardupdt + @opercardupdt
 set @sumyearfee = @sumyearfee + @yearfee
 set @sumfuncfee1 = @sumfuncfee1 + @funcfee1
 set @sumflowfee = @sumflowfee + @flowfee
 set @sumfuncfee2 = @sumfuncfee2 + @funcfee2
 set @sumsumamout = @sumsumamout + @sumamout
 set @sumremain = @sumremain + @remain

  --下一个企业
  fetch next from cur1 into @orgId, @orgName, @remain
 end

close cur1
deallocate cur1--关闭游标，释放资源
GO