ASP.NET操作Excel实例

 

public void ExportToExcel()
    {
        string path = Request.PhysicalApplicationPath + "txtfile//" + getFileName() + ".xls";
        string sql = Session["queryStr"].ToString();
        DataSet ds = Maticsoft.DBUtility.DbHelperSQL.Query(sql);
        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] = convertColumnName(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);
                        }
                    }
                    totalCount = totalCount + row + 4;
                }
                try
                {
                    myExcel.ActiveWorkbook._SaveAs(filename, Nothing, Nothing, Nothing, Nothing, Nothing, XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing);
                }
                catch
                {
                    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才可以