求导入导出cvs或xsl（excel）的类，导出时自动弹出下载。

[yingshao]

求导入导出cvs或xsl（excel）的类，导出时自动弹出下载。

[红魔大卫]

这个里面很全的

[changjian1444]

        //打开电子表
        public static OleDbConnection OpenExcel(string FileName)
        {
            try
            {
                //string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=YES\";";
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
                OleDbConnection conn = new OleDbConnection();
                conn.ConnectionString = strConn;
                conn.Open();
                return conn;
            }
            catch (Exception e)
            {
                Utilities.Logger.Warning(e.Message);
                return null;
            }
        }

        /// <summary>
        /// 返回电子表中的第一张表
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(OleDbConnection conn)
        {
            DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string TableName = dt.Rows[i][2].ToString();
                if (TableName.IndexOf("$") > 0)
                {
                    DataTable dtNew = ExecuteDataTable(conn, "Select * from [" + TableName + "]");
                    if (dtNew != null) return dtNew;
                }
            }
            return null;
        }

        //返回符合条件的记录
        public static DataTable ExecuteDataTable(OleDbConnection conn, int row, int col, string flag)
        {
            DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string TableName = dt.Rows[i][2].ToString();
                if (TableName.IndexOf("$") > 0)
                {
                    DataTable dtNew = ExecuteDataTable(conn, "Select * from [" + TableName + "]");
                    if (dtNew != null)
                    {
                        if ((dtNew.Rows.Count > row) && (dtNew.Columns.Count > col))
                        {
                            if (dtNew.Rows[row][col].ToString().IndexOf(flag) >= 0) return dtNew;
                        }
                    }
                }
            }
            return null;
        }


        //返回电子表
        public static DataTable ExecuteDataTable(OleDbConnection conn, string query)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn);
            DataSet ds = new DataSet();
            try
            {
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                return null;
            }
            if (ds.Tables.Count == 0)
            {
                return null;
            }
            if (ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }
        }

        //执行无返回指令，返回影响的行数
        public static int ExecuteNonQuery(OleDbConnection conn, string query)
        {
            int i = -1;
            OleDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = query;
            try
            {
                i = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                i = -1;
            }
            return i;
        }

        //关闭
        public static void Close(OleDbConnection conn)
        {
            try
            {
                conn.Close();
                conn.Dispose();
            }
            catch
            {
            }
        }


        #region 导出Excel

        /// <summary>
        /// 直接输出Excel
        /// </summary>
        /// <param name="dtData"></param>
        public static void ExportExcel(System.Data.DataTable dtData)
        {

            System.Web.UI.WebControls.DataGrid dgExport = null;
            
            // 当前对话
            System.Web.HttpContext curContext = System.Web.HttpContext.Current;

            // IO用于导出并返回excel文件
            System.IO.StringWriter strWriter = null;
            System.Web.UI.HtmlTextWriter htmlWriter = null;

            if (dtData != null)
            {
                // 设置编码和附件格式
                curContext.Response.ContentType = "application/vnd.ms-excel";
                curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");// System.Text.Encoding.UTF8;
                curContext.Response.Charset = "";

                // 导出excel文件
                strWriter = new System.IO.StringWriter();
                htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);

                // 为了解决dgData中可能进行了分页的情况，需要重新定义一个无分页的DataGrid
                dgExport = new System.Web.UI.WebControls.DataGrid();
                dgExport.DataSource = dtData.DefaultView;
                dgExport.AllowPaging = false;
                dgExport.DataBind();

                // 返回客户端
                dgExport.RenderControl(htmlWriter);
                curContext.Response.Write(strWriter.ToString());
                curContext.Response.End();
            }
        }
        #endregion