asp.net 操作Excel-CSDN博客

本文链接：https://blog.csdn.net/myloy/article/details/4332810

当点击下载按钮时，则会出现Excel下载页面。功能源码如下:

private void Btn_DownLoad_ServerClick(object sender, System.EventArgs e)
  {
   LoadData();
   System.Data.DataTable NewTable=(System.Data.DataTable)this.DgDataAnalysis.DataSource;
   beforeTime=DateTime.Now;
   Excel.Application app = new Excel.Application();
   if(app==null)
   {
    return;
   }
   app.Visible = false;
   app.UserControl = true;
   afterTime = DateTime.Now;
   Workbooks workbooks = app.Workbooks;
   _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
   Sheets sheets = workbook.Worksheets;

   System.Data.DataTable STable=new System.Data.DataTable();
   _Worksheet ws = null;
   System.Data.DataTable MTable=MyDataAccess.SAPManageMarketArea(this.Page);
   int j=0;
   foreach(DataRow MRow in MTable.Rows)
   {
    int   rowNum=0;
    STable=ShowDgDataAnalysis(int.Parse(MRow["marketareaid"].ToString()));
    if(j!=0)
    {
     app.Worksheets.Add(Type.Missing,Type.Missing,1,Type.Missing);
    }
    ws = (_Worksheet)sheets.get_Item(1);
    ws.Cells[1,1]="办事处";
    ws.Cells[1,2]="员工";
    ws.Cells[1,3]="职务";
    ws.Cells[1,4]="工作日";
    ws.Cells[1,5]="每天SAP报告";
    ws.Cells[1,6]="每周SAP报告";
    ws.Cells[1,7]="报告与外出比";
    ws.Cells[1,8]="新建客户";
    ws.Cells[1,9]="新建项目";
    ws.Cells[1,10]="推动项目";
    ws.Cells[1,11]="外出";
    ws.Cells[1,12]="新建报告";
    ws.Cells[1,13]="日常报告";
    ws.Cells[1,14]="项目报告";
    ws.get_Range(ws.Cells[1,1],ws.Cells[1,14]).HorizontalAlignment=XlVAlign.xlVAlignCenter;
    ws.get_Range(ws.Cells[1,1],ws.Cells[1,14]).ColumnWidth=13;
    ws.get_Range(ws.Cells[1,1],ws.Cells[1,14]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
    foreach(DataRow SRow in STable.Rows)
    {
     rowNum++;
     ws.Cells[2+rowNum-1,1]=SRow["DepartmentDesc"].ToString();
     ws.Cells[2+rowNum-1,2]=SRow["Name"].ToString();
     ws.Cells[2+rowNum-1,3]=SRow["JobDesc"].ToString();
     ws.Cells[2+rowNum-1,4]=SRow["WorkDayNum"].ToString();
     ws.Cells[2+rowNum-1,5]=SRow["EveryDayReportNum"].ToString();
     ws.Cells[2+rowNum-1,6]=SRow["EveryWeekReportNum"].ToString();
     ws.Cells[2+rowNum-1,7]=SRow["OutReportP"].ToString();
     ws.Cells[2+rowNum-1,8]=SRow["ClientNum"].ToString();
     ws.Cells[2+rowNum-1,9]=SRow["ProjectNum"].ToString();
     ws.Cells[2+rowNum-1,10]=SRow["ActionNum"].ToString();
     ws.Cells[2+rowNum-1,11]=SRow["OutNum"].ToString();
     ws.Cells[2+rowNum-1,12]=SRow["ReportNum"].ToString();
     ws.Cells[2+rowNum-1,13]=SRow["DailyReportNum"].ToString();
     ws.Cells[2+rowNum-1,14]=SRow["OpporunityReportNum"].ToString();
     ws.get_Range(ws.Cells[2 + rowNum - 1, 1], ws.Cells[2 + rowNum - 1, 14]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
    }
    j++;
    ws.Name=MRow["MarketAreaDesc"].ToString();
   }
   app.Worksheets.Add(Type.Missing,Type.Missing,1,Type.Missing);
   _Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
   if(worksheet==null)
   {
    return;
   }
   worksheet.Cells[1,1]="区域";
   worksheet.Cells[1,2]="办事处";
   worksheet.Cells[1,3]="人数";
   worksheet.Cells[1,4]="Sales";
   worksheet.Cells[1,5]="FAE";
   worksheet.Cells[1,6]="工作日";
   worksheet.Cells[1,7]="人/天报告";
   worksheet.Cells[1,8]="人/周报告";
   worksheet.Cells[1,9]="报告与外出比";
   worksheet.Cells[1,10]="客户数";
   worksheet.Cells[1,11]="拜访客户数";
   worksheet.Cells[1,12]="新建客户";
   worksheet.Cells[1,13]="项目数";
   worksheet.Cells[1,14]="新建项目";
   worksheet.Cells[1,15]="推动项目";
   worksheet.Cells[1,16]="外出";
   worksheet.Cells[1,17]="新建报告";
   worksheet.Cells[1,18]="日常报告";
   worksheet.Cells[1,19]="项目报告";
   worksheet.Cells[1,20]="Sales外出";
   worksheet.Cells[1,21]="Sales报告";
   worksheet.Cells[1,22]="FAE外出";
   worksheet.Cells[1,23]="FAE报告";
   worksheet.get_Range(worksheet.Cells[1,1],worksheet.Cells[1,23]).HorizontalAlignment=XlVAlign.xlVAlignCenter;
   worksheet.get_Range(worksheet.Cells[1,1],worksheet.Cells[1,23]).ColumnWidth=10;
   worksheet.get_Range(worksheet.Cells[1,1],worksheet.Cells[1,23]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
   int i=0;
   foreach(DataRow LRow in NewTable.Rows)
   {

    worksheet.Cells[2+i,1]=LRow["MarketAreaDesc"].ToString();
    worksheet.Cells[2+i,2]=LRow["DepartmentDesc"].ToString();
    worksheet.Cells[2+i,3]=LRow["EmployeeNum"].ToString();
    worksheet.Cells[2+i,4]=LRow["SalesNum"].ToString();
    worksheet.Cells[2+i,5]=LRow["FaeNum"].ToString();
    worksheet.Cells[2+i,6]=LRow["WorkDayNum"].ToString();
    worksheet.Cells[2+i,7]=LRow["EveryDayEveryEmployeeReportNum"].ToString();
    worksheet.Cells[2+i,8]=LRow["EveryWeekEveryEmployeeReportNum"].ToString();
    worksheet.Cells[2+i,9]=LRow["OutReportP"].ToString();
    worksheet.Cells[2+i,10]=LRow["ClientNum"].ToString();
    worksheet.Cells[2+i,11]=LRow["VisitClientNum"].ToString();
    worksheet.Cells[2+i,12]=LRow["NewClientNum"].ToString();
    worksheet.Cells[2+i,13]=LRow["ProjectNum"].ToString();
    worksheet.Cells[2+i,14]=LRow["NewProjectNum"].ToString();
    worksheet.Cells[2+i,15]=LRow["ActionNum"].ToString();
    worksheet.Cells[2+i,16]=LRow["OutNum"].ToString();
    worksheet.Cells[2+i,17]=LRow["NewReportNum"].ToString();
    worksheet.Cells[2+i,18]=LRow["DailyReportNum"].ToString();
    worksheet.Cells[2+i,19]=LRow["OpporunityReportNum"].ToString();
    worksheet.Cells[2+i,20]=LRow["SalesOutNum"].ToString();
    worksheet.Cells[2+i,21]=LRow["SalesReportNum"].ToString();
    worksheet.Cells[2+i,22]=LRow["FaeOutNum"].ToString();
    worksheet.Cells[2+i,23]=LRow["FaeReportNum"].ToString();
    worksheet.get_Range(worksheet.Cells[2 + i, 1],worksheet.Cells[2 + i,23]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//设置边框颜色，不然打印预览，会非常不雅观
    i=i+1;

   }
   worksheet.Name = "General CVR";
   string tick = DateTime.Now.Ticks.ToString();
   string save_path = "~/UploadFiles"+ "//"+ tick + ".xls";
   workbook.SaveAs(Server.MapPath(save_path), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
   app.Workbooks.Close();
   app.Quit();
   System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
   System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
   System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
   System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
   System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
   System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
   sheets=null;
   workbook=null;
   worksheet=null;
   ws=null;
   workbooks=null;
   app=null;
   GC.Collect();
   string path=Server.MapPath(save_path);
   System.IO.FileInfo file = new System.IO.FileInfo(path);
   Response.Clear();
   Response.Charset="GB2312";
   Response.ContentEncoding=System.Text.Encoding.UTF8;
   Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
   Response.AddHeader("Content-Length", file.Length.ToString());
   Response.ContentType = "application/ms-excel";
   Response.WriteFile(file.FullName);
   Response.End();
  }