ASP.NET操作Excel实例

 protected void Page_Load(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
            SqlDataAdapter ap = new SqlDataAdapter("SELECT [CustomerID], [CompanyName], [ContactName], [Country] FROM [Customers]", con);
            DataSet ds = new DataSet();
            ap.Fill(ds);
            string path = Request.PhysicalApplicationPath + "doc//" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss") + ".xls";
            ExportToExcel(path, ds);

        }
        public void ExportToExcel(string path, DataSet ds)
        {
           
            if (ds.Tables.Count != 0)
            {
                //生成.xls文件完整路径名
                // string tempFileName = GetTempFileName();
                object filename = path;
                object Nothing = System.Reflection.Missing.Value;

                //创建excel文件，文件名用系统时间生成精确到毫秒
                Excel.Application myExcel = new Excel.ApplicationClass();
                myExcel.Application.Workbooks.Add(Nothing);
                try
                {
                    //把Dataset中的数据插入excel文件中
                    int totalCount = 0;
                    for (int k = 0; k < ds.Tables.Count; k++)
                    {
                        int row = ds.Tables[k].Rows.Count;
                        int column = ds.Tables[k].Columns.Count;

                        for (int i = 0; i < column; i++)
                        {
                            myExcel.Cells[totalCount + 1, 1 + i] = ds.Tables[k].Columns[i].ColumnName;
                        }

                        for (int i = 0; i < row; i++)
                        {
                            for (int j = 0; j < column; j++)
                            {
                               // myExcel.Cells[totalCount + 2 + i, 1 + j] = "'" + getRowValue(ds.Tables[k].Rows[i], ds.Tables[k].Columns[j].ColumnName);
                                myExcel.Cells[totalCount + 2 + i, 1 + j] = "'" + ds.Tables[k].Rows[i].ItemArray[j].ToString() ;
                            }
                        }
                        totalCount = totalCount + row + 4;
                    }
                    try
                    {
                        myExcel.ActiveWorkbook._SaveAs(filename, Nothing, Nothing, Nothing, Nothing, Nothing, XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing);
                    }
                    catch(Exception ex)
                    {
                        return;
                    }
                    //让生成的excel文件可见
                    //myExcel.Visible = true;
                }
                catch (Exception e)
                {
                }
                foreach (Excel.WorkbookClass w in myExcel.Workbooks)
                {
                    w.Close(false, Nothing, Nothing);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(w);
                }
                myExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
                myExcel = null;
                System.GC.Collect();
                Response.Clear();
                Response.Buffer = true;
                Response.ContentType = "application/ms-excel";
                Response.AppendHeader("Content-Disposition", "attachment;filename=todayDownList.xls");
                System.IO.FileInfo fileInfo = new FileInfo(path);
                Response.AddHeader("Content-Length", fileInfo.Length.ToString());
                Response.WriteFile(path);
                Response.End();
            }
            else
            {
                //System.Windows.Forms.MessageBox.Show("No Data");
            }
        }

注意:Excel命名空间在COM里,添加此命名空间时,在机器上应该装有EXCEL才可以