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

来源：互联网发布：java 高性能框架编辑：程序博客网时间：2024/05/21 17:44

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;            }        }