注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

程序员驿站

淘宝店:http://shop106888457.taobao.com

 
 
 

日志

 
 

C#开发EXCEL导入、导出支持MSSQL、ORACLE、ACCESS  

2014-10-23 16:20:15|  分类: C# |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
主要代码:
1.excel文件读到DataTable
 
        ///<summary>
        ///根据excel路径和sheet名称,返回excelDataTable
        ///</summary>
        public static DataTable GetExcelDataTable(string path, string tname)
        {
            /*Office 2007*/
            string ace = "Microsoft.ACE.OLEDB.12.0";
            /*Office 97 - 2003*/
            string jet = "Microsoft.Jet.OLEDB.4.0";
            string xl2007 = "Excel 12.0 Xml";
            string xl2003 = "Excel 8.0";
            string imex = "IMEX=1";
            /* csv */
            string text = "text";
            string fmt = "FMT=Delimited";
            string hdr = "Yes";
            string conn = "Provider={0};Data Source={1};Extended Properties=\"{2};HDR={3};{4}\";";
            string select = string.Format("SELECT * FROM [{0}$]", tname);
            //string select = sql;
            string ext = Path.GetExtension(path);
            OleDbDataAdapter oda;
            DataTable dt = new DataTable("data");
            switch (ext.ToLower())
            {
                case ".xlsx":
                    conn = String.Format(conn, ace, Path.GetFullPath(path), xl2007, hdr, imex);
                    break;
                case ".xls":
                    conn = String.Format(conn, jet, Path.GetFullPath(path), xl2003, hdr, imex);
                    break;
                case ".csv":
                    conn = String.Format(conn, jet, Path.GetDirectoryName(path), text, hdr, fmt);
                    //sheet = Path.GetFileName(path);
                    break;
                default:
                    throw new Exception("File Not Supported!");
            }
            OleDbConnection con = new OleDbConnection(conn);
            con.Open();
            //select = string.Format(select, sql);
            oda = new OleDbDataAdapter(select, con);
            oda.Fill(dt);
            con.Close();
            return dt;
        }
2.批量把数据导入到数据库
1)SQL SERVER版本
    public bool SqlBulkCopyImport(IList<string> maplist, string TableName, DataTable dt)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                {
                    bulkCopy.DestinationTableName = TableName;
                    foreach (string a in maplist)
                    {
                        bulkCopy.ColumnMappings.Add(a, a);
                    }
                    try
                    {
                        bulkCopy.WriteToServer(dt);
                        return true;
                    }
                    catch (Exception e)
                    {
                        throw e;
                    }
                }
            }
        }
2)oracle版本 
public bool SqlBulkCopyImport(IList<string> maplist, string TableName, DataTable dt)
        {
 
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
 
                connection.Open();
 
                using (OracleBulkCopy bulkCopy = new OracleBulkCopy(connection))
                {
 
                    bulkCopy.DestinationTableName = TableName;
 
                    foreach (string a in maplist)
                    {
 
                        bulkCopy.ColumnMappings.Add(a, a);
 
                    }
 
                    try
                    {
 
                        bulkCopy.WriteToServer(dt);
 
                        return true;
 
                    }
 
                    catch (Exception e)
                    {
                        throw e;
 
                    }
                }
           }
        }
 
3ACCESS版本
public bool SqlBulkCopyImport(IList<string> maplist, string TableName, DataTable dt)
        {
            try
            {
                using (OleDbConnection connection = new OleDbConnection(connectionString))
                {
                    connection.Open();
                    OleDbDataAdapter adapter = new OleDbDataAdapter("select * from " + TableName + " where 1=0", connection);
                    OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
                    int rowcount = dt.Rows.Count;
                    for (int n = 0; n < rowcount; n++)
                    {
                        dt.Rows[n].SetAdded();
                    }
                    //adapter.UpdateBatchSize = 1000;
                    adapter.Update(dt);
                }
                return true;
            }
            catch (Exception e)
            {
                throw e;
            }
       
       
        }
 
 
3.导出EXCEL文件
///<summary>
        ///保存excel文件,覆盖相同文件名的文件
        ///</summary>
        public static bool SaveExcel(string SheetName, DataTable dt, ExcelPackage package)
        {
 
            try
            {              
                ExcelWorksheet ws = package.Workbook.Worksheets.Add(SheetName);
                ws.Cells["A1"].LoadFromDataTable(dt, true);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
 
        ///<summary>
        ///多个表格导出到一个excel工作簿
        ///</summary>
        public static void export(IList<string> SheetNames, string filename, DBConfig db, IList<string> sqls)
        {
            DataTable dt = new DataTable();
            FileInfo newFile = new FileInfo(filename);
            if (newFile.Exists)
            {
                newFile.Delete();
                newFile = new FileInfo(filename);
            }
            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                for (int i = 0; i < sqls.Count; i++)
                {
                    dt = db.DB.ReturnDataTable(sqls[i]);
                    SaveExcel(SheetNames[i], dt, package);
                }
                package.Save();
            }
        }
 
        ///<summary>
        ///单个表格导出到一个excel工作簿
        ///</summary>
        public static void export(string SheetName, string filename, DBConfig db, string sql)
        {
            DataTable dt = new DataTable();
            FileInfo newFile = new FileInfo(filename);
            if (newFile.Exists)
            {
                newFile.Delete();
                newFile = new FileInfo(filename);
            }
            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                dt = db.DB.ReturnDataTable(sql);
                SaveExcel(SheetName, dt, package);
                package.Save();
            }
        }
 
        ///<summary>
        ///单个表导出到多个excel工作簿(分页)
        ///</summary>
        public static void export(string SheetName, string filename, DBConfig db, string sql, int num, int pagesize)
        {
            DataTable dt = new DataTable();
            FileInfo newFile = new FileInfo(filename);
            int numtb = num / pagesize + 1;
            for (int i = 1; i <= numtb; i++)
            {
                string s = filename.Substring(0, filename.LastIndexOf("."));
                StringBuilder newfileName = new StringBuilder(s);
                newfileName.Append(i + ".xlsx");
                newFile = new FileInfo(newfileName.ToString());
                if (newFile.Exists)
                {
                    newFile.Delete();
                    newFile = new FileInfo(newfileName.ToString());
                }
                using (ExcelPackage package = new ExcelPackage(newFile))
                {
                    dt = db.DB.ReturnDataTable(sql, pagesize * (i - 1), pagesize);
                    SaveExcel(SheetName, dt, package);
                    package.Save();
                }
            }
        }
  评论这张
 
阅读(294)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017