ACCESS，文件下载，DLL操作类，EXCEL导出方法备忘

1.简单Acess操作类

    /// <summary>
    /// 这个类用于Access数据库的操作
    /// </summary>
    class AccessConnHelper
    {
        //数据库存放位置
        public string FilePath { get; set; }
        
        public  bool GetConnectionState()
        {
            OleDbConnection conn =GetNewConnection();
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                return true;
            }
            else
                return false;
            
        }

        /// <summary>
        /// 获得新的数据库连接
        /// </summary>
        /// <returns>返回新连接</returns>
        private  OleDbConnection GetNewConnection()
        {
            OleDbConnection oleDbConnection = new OleDbConnection();
            oleDbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + FilePath;

            try
            {
                oleDbConnection.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return oleDbConnection;
        }

        /// <summary>
        /// 执行SQL语句，并返回一个OleDbDataReader对象。
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <returns>OleDbDataReader对象</returns>
        private  OleDbDataReader ExecuteReader(string commandText, OleDbParameter[] commandParm)
        {
            OleDbConnection oleDbConnection = GetNewConnection();
            OleDbCommand oleDbCommand = new OleDbCommand(commandText, oleDbConnection);
            PrepareCommand(oleDbCommand, commandParm);
            try
            {
                OleDbDataReader dbDataReader = oleDbCommand.ExecuteReader(CommandBehavior.CloseConnection);
                return dbDataReader;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        private  void PrepareCommand(OleDbCommand oleDbCommand, OleDbParameter[] commandParms)
        {
            if (commandParms != null)
            {
                foreach (OleDbParameter parameter in commandParms)
                {
                    if (parameter.Value == null)
                    {
                        parameter.Value = DBNull.Value;
                    }
                    oleDbCommand.Parameters.Add(parameter);
                }
            }
        }

        /// <summary>
        /// 获取表中某一列有值得个数
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        public string GetItemsNumber(string tableName, string columnName)
        {
            string sqlText = string.Format("SELECT COUNT(*) FROM [{0}] WHERE [{1}] is not null", tableName, columnName);
            OleDbConnection oleDbConnection = GetNewConnection();
            OleDbCommand oleDbCommand = new OleDbCommand(sqlText, oleDbConnection);
            string result=oleDbCommand.ExecuteScalar().ToString();
            oleDbConnection.Close();
            return result;
        }

        /// <summary>
        /// 从数据库中读取并返回所有URL列表。
        /// </summary>
        public List<string> GetAllList(string urlNumber,string tablename,string columen)
        {
            string sqlText = string.Format("SELECT TOP {0} [URL] FROM [{1}] where [{2}] is not null ORDER BY [{2}] DESC,[ID]", urlNumber, tablename,columen);
            List<string> list = new List<string>();
            OleDbDataReader oleDbDataReader =ExecuteReader(sqlText, null);
            while (oleDbDataReader.Read())
            {                        
                list.Add(oleDbDataReader["URL"].ToString());
            }
            oleDbDataReader.Close();
            return list;
        }

        /// <summary>
        /// 返回数据库中所有的表项
        /// </summary>
        public DataTable GetAllTables()
        {
            return GetNewConnection().GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "Table" });
        }

        /// <summary>
        /// 返回所有列名
        /// </summary>
        public List<string> GetALLTitle(string tableName)
        {
            List<string> columnName = new List<string>();
            DataTable dt = GetNewConnection().GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null,tableName, null });
            foreach (DataRow dr in dt.Rows)
            {
                if (dr["COLUMN_NAME"].ToString().Substring(0, 1) == "_")
                {
                    columnName.Add(dr["COLUMN_NAME"].ToString());
                }                
            }
            return columnName;
        }   
    }

2.文件下载类

    /// <summary>
    /// 文件下载通用类
    /// </summary>
    public class LoadFile : System.Web.UI.Page
    {
        /// <summary>
        /// 下载指定的路径的文件到本地
        /// </summary>
        /// <param name="filePath">文件在服务器的虚拟路径</param>
        public void DownLoadFile(string filePath)
        {
            FileInfo file = new System.IO.FileInfo(Server.MapPath(filePath));//将虚拟路径转换成物理路径，获得文件的信息
            if (File.Exists(Server.MapPath(filePath)))
            {
                string name = Path.GetFileName(Server.MapPath(filePath));
                //存在文件        
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.Buffer = false;
                //通知浏览器下载文件而不是打开 
                HttpContext.Current.Response.ContentType = "application/octet-stream";
                HttpContext.Current.Response.Charset = "GB2312";//定义输出字符集 
                HttpContext.Current.Response.ContentEncoding = Encoding.Default;//输出内容的编码为默认编码
                //下载的文件名            
                if (HttpContext.Current.Request.UserAgent.Contains("MSIE") || HttpContext.Current.Request.UserAgent.Contains("msie"))
                {
                    // 如果客户端使用 Microsoft Internet Explorer，则需要编码
                    name = ToHexString(name);      // name =Server.UrlEncode(name); 功能等价
                }

                HttpContext.Current.Response.AppendHeader("content-disposition", "attachment;filename=" + name);
                //文件的大小
                HttpContext.Current.Response.AppendHeader("content-length", file.Length.ToString());
                HttpContext.Current.Response.WriteFile(filePath);
                HttpContext.Current.Response.End();//将当前所有缓冲区的输出内容发送到客户端，并停止页面的执行
            }
        }

        /// <summary>
        /// 为字符串中的非英文字符编码
        /// </summary>
        private string ToHexString(string s)
        {
            char[] chars = s.ToCharArray();
            StringBuilder builder = new StringBuilder();
            for (int index = 0; index < chars.Length; index++)
            {
                bool needToEncode = NeedToEncode(chars[index]);
                if (needToEncode)
                {
                    string encodedString = ToHexString(chars[index]);
                    builder.Append(encodedString);
                }
                else
                {
                    builder.Append(chars[index]);
                }
            }

            return builder.ToString();
        }

        /// <summary>
        ///指定一个字符是否应该被编码
        /// </summary>
        private bool NeedToEncode(char chr)
        {
            string reservedChars = "$-_.+!*'(),@=&";

            if (chr > 127)
                return true;
            if (char.IsLetterOrDigit(chr) || reservedChars.IndexOf(chr) >= 0)
                return false;

            return true;
        }

        /// <summary>
        /// 为非英文字符串编码
        /// </summary>
        private string ToHexString(char chr)
        {
            UTF8Encoding utf8 = new UTF8Encoding();
            byte[] encodedBytes = utf8.GetBytes(chr.ToString());
            StringBuilder builder = new StringBuilder();
            for (int index = 0; index < encodedBytes.Length; index++)
            {
                builder.AppendFormat("%{0}", Convert.ToString(encodedBytes[index], 16));
            }
            return builder.ToString();
        }
    } 

3.datagridview导出excel类

    /// <summary>
    /// 将DV导出EXCEL类
    /// </summary>
    public class ExcelCommand
    {
        public static void print(DataGridViewX dataGridView)
        {
                //导出到execl   
                //没有数据的话就不往下执行   
                if (dataGridView.Rows.Count == 0)
                return;
               //实例化一个Excel.Application对象   
                Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();

               //让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写   
                Excel.Visible = false;

                //新增加一个工作簿，Workbook是直接保存，不会弹出保存对话框，加上Application会弹出保存对话框，值为false会报错   
                Excel.Application.Workbooks.Add(true);     

                //生成Excel中列头名称   
                for (int i = 0; i < dataGridView.Columns.Count; i++)
                {
                    Excel.Cells[1, i + 1] = dataGridView.Columns[i].HeaderText;
                }
                //把DataGridView当前页的数据保存在Excel中   
                for (int i = 0; i < dataGridView.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < dataGridView.Columns.Count; j++)
                    {                     
                            Excel.Cells[i + 2, j + 1] = dataGridView[j, i].Value.ToString();                      
                    }
                }

                //设置禁止弹出保存和覆盖的询问提示框   
                Excel.DisplayAlerts = false;
                Excel.AlertBeforeOverwriting = false;

                //保存工作簿   
                Excel.Application.Workbooks.Add(true).Save();
                //保存excel文件   
                Excel.Save();

                //确保Excel进程关闭   
                Excel.Quit();
                Excel = null;   
                     
        }

        public static void KillProcess()
        {
            //获得进程对象，以用来操作  
            System.Diagnostics.Process myproc = new System.Diagnostics.Process();
            //得到所有打开的进程   
            try
            {
                //获得需要杀死的进程名  
                foreach (Process thisproc in Process.GetProcessesByName("EXCEL"))
                {
                    //立即杀死进程  
                    thisproc.Kill();
                }
            }
            catch (Exception Exc)
            {
                throw new Exception("", Exc);
            }
        }  
    }

4.调用CMD方法

        /// <summary>
        /// 调用cmd程序
        /// </summary>
        /// <param name="commandText">cmd指令</param>
        /// <returns>结果</returns>
        private string ExeCommand(string commandText)
        {         
            using (Process p = new Process())
            {
                p.StartInfo.FileName = "cmd.exe";             
                //将此属性设置为 false 能够重定向输入流、输出流和错误流。
                p.StartInfo.Arguments ="/c "+ commandText;
                p.StartInfo.UseShellExecute = false;                             
                p.StartInfo.RedirectStandardInput = true;
                p.StartInfo.RedirectStandardOutput = true;
                p.StartInfo.RedirectStandardError = true;
                p.StartInfo.CreateNoWindow = true;
                p.Start();           
                p.Close();
                return null;
            }
        }