C#导出excel并下载

  

  public void SaveAsExcel(DataTable dt1,DataTable dt2,string filename)
        {
            Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application();

           //SaveFileDialog sfd = new SaveFileDialog();
           // filename += DateTime.Now.ToString("yyyyMMdd") + "-" + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString();
           // sfd.FileName = filename;
           // sfd.Filter = "Excel files (*xls) | *.xls";
           // sfd.RestoreDirectory = true;
       //     if (sfd.ShowDialog() == DialogResult.OK && sfd.FileName.Trim() != null)
            {
                System.Reflection.Missing miss = System.Reflection.Missing.Value;

                appexcel = new Microsoft.Office.Interop.Excel.Application();

                Microsoft.Office.Interop.Excel.Workbook workbookdata;

                Microsoft.Office.Interop.Excel.Worksheet worksheetdata1;
                Microsoft.Office.Interop.Excel.Worksheet worksheetdata2;

                Microsoft.Office.Interop.Excel.Range rangedata1;
                Microsoft.Office.Interop.Excel.Range rangedata2;
             
                //设置对象不可见

                appexcel.Visible = false;

                System.Globalization.CultureInfo currentci = System.Threading.Thread.CurrentThread.CurrentCulture;

                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-us");

                workbookdata = appexcel.Workbooks.Add(miss);

                worksheetdata1 = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss, miss, miss, miss);
                worksheetdata2 = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss, miss, miss, miss);

                //给工作表赋名称

                worksheetdata1.Name = "Statistics";
                worksheetdata2.Name = "Detail";

                for (int i = 0; i < dt1.Columns.Count; i++)

                {

                    worksheetdata1.Cells[1, i + 1] = dt1.Columns[i].ColumnName.ToString();

                }
                for (int i = 0; i < dt2.Columns.Count; i++)

                {

                    worksheetdata2.Cells[1, i + 1] = dt2.Columns[i].ColumnName.ToString();

                }

                //因为第一行已经写了表头，所以所有数据都应该从a2开始
                rangedata1 = worksheetdata1.get_Range("a2", miss);
                rangedata2 = worksheetdata2.get_Range("a2", miss);
                //列自适应宽度
                rangedata1.Columns.AutoFit();
                rangedata2.Columns.AutoFit();


                //第一个表begin****************************************************
                //irowcount为实际行数，最大行
                Microsoft.Office.Interop.Excel.Range xlrang1 = null;
                int irowcount1 = dt1.Rows.Count;

                int iparstedrow1 = 0, icurrsize1 = 0;

                //ieachsize为每次写行的数值，可以自己设置

                int ieachsize1 = 1000;

                //icolumnaccount为实际列数，最大列数

                int icolumnaccount1 = dt1.Columns.Count;

                //在内存中声明一个ieachsize×icolumnaccount的数组，ieachsize是每次最大存储的行数，icolumnaccount就是存储的实际列数

                object[,] objval1 = new object[ieachsize1, icolumnaccount1];

                icurrsize1 = ieachsize1;

                while (iparstedrow1 < irowcount1)

                {

                    if ((irowcount1 - iparstedrow1) < ieachsize1)

                        icurrsize1 = irowcount1 - iparstedrow1;

                    //用for循环给数组赋值

                    for (int i = 0; i < icurrsize1; i++)

                    {

                        for (int j = 0; j < icolumnaccount1; j++)

                            objval1[i, j] = dt1.Rows[i + iparstedrow1][j].ToString();

                        System.Windows.Forms.Application.DoEvents();

                    }

                    string X = "A" + ((int)(iparstedrow1 + 2)).ToString();

                    string col = "";

                    if (icolumnaccount1 <= 26)

                    {

                        col = ((char)('A' + icolumnaccount1 - 1)).ToString() + ((int)(iparstedrow1 + icurrsize1 + 1)).ToString();

                    }

                    else

                    {

                        col = ((char)('A' + (icolumnaccount1 / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount1 % 26 - 1))).ToString() + ((int)(iparstedrow1 + icurrsize1 + 1)).ToString();

                    }

                    xlrang1 = worksheetdata1.get_Range(X, col);

                    // 调用range的value2属性，把内存中的值赋给excel

                    xlrang1.Value2 = objval1;

                    iparstedrow1 = iparstedrow1 + icurrsize1;

                }


                //保存工作表

                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlrang1);

                xlrang1 = null;

                //第一个表end****************************************************

                //调用方法关闭excel进程            
                appexcel.Visible = true;
                workbookdata.Saved = true;
                filename = "../upload/" + filename;
                workbookdata.SaveCopyAs(Server.MapPath(filename));
                workbookdata.Close(true, Type.Missing, Type.Missing);
                workbookdata = null;
                appexcel.Quit();
                appexcel = null;

            }
           
        }

 

     public void DownloadFile(string filePath)
        {
            string saveFileName = Path.GetFileName(filePath);
             string  FullFileName = Server.MapPath(filePath);
            FileInfo DownloadFile = new FileInfo(FullFileName);
            //byte[] buffer;

            if (DownloadFile.Exists)
            {

                try
                {
                    //WriteFile实现下载(word)
                
                    Response.Clear();
                    Response.ClearContent();
                    Response.ClearHeaders();
                    Response.AddHeader("Content-Disposition", "attachment;filename=" + saveFileName);
                    Response.AddHeader("Content-Length", DownloadFile.Length.ToString());
                    Response.AddHeader("Content-Transfer-Encoding", "binary");
                    Response.ContentType = "application/vnd.ms-excel";
                    Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
                    Response.WriteFile(DownloadFile.FullName);
                    Response.Flush();
                    Response.End();
                    System.IO.File.Delete(FullFileName);
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message);
                }




            }

        }

 

 

调用方法后台：

     string filename = Request.QueryString["tablename"] + "_" + Guid.NewGuid().ToString()+".xlsx";
                SaveAsExcel(dt1,dt2, filename);
                DownloadFile("/upload/" + filename);

调用方法前台：

    self.location = '/download/predication?type=0&tablename=' + tablename + '&learnedId=' + learnedId;