作为财务数据核算人员，面对大量的业务与财务数据，借助于传统的EXCEL表格，已经力不从心。最近几个月，利用周末及下班的空闲时间，写了一个数据库导入导出工具，以方便业务逻辑密集型的数据处理。目前，DataPie支持百万级别的数据导出，对于几十万的数据导入，也轻松应付。

源码及安装包下载地址：https://github.com/yfl8910/DataPie

 .NET 6 版本：https://github.com/yfl8910/DataPie2

先看看界面，登录界面：

主界面：

主要代码：

1.把excel文件读到DataTable

        ///<summary>

        ///根据excel路径和sheet名称，返回excel的DataTable

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

                    }

                }

            }

        }

3）ACCESS版本

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)

            {

鲜花

握手

雷人

路过

鸡蛋

• 关于我们

  创业团队 加入我们 媒体报道 合作伙伴 公益事业

• 产品与服务

  寻求合作 项目投资 干货视频 经理人培训 招聘代理

• 解决方案

  一站式 制造业 教育科研 行业案例

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服（服务时间 9:00～18:00）

在线QQ客服

地址：深圳市南山区西丽大学城创智工业园

电邮：jeky_zhao#qq.com

移动电话：139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap