• 免费开源数据库导入导出工具


     

    软件名:DataPie

    功能:支持SQL server、SQLite、ACCESS数据库的导入、导出、存储过程调用,支持EXCEL2007、EXCEL2003、ACCESS2007、 CSV文件导入数据库,支持EXCEL、CSV、ZIP、ACCESS文件方式导出,支持数据拆分导出及自定义SQL查询与导出。

    开发背景:作者从事财务管理工作,主要是出具集团的内部财务报表,随着公司精细化管理的需求,管理报表的数据量急速增长, 依赖EXCEL加工处理数据已经变得极为困难,因此团队全面转向关系数据库进行数据处理,为减少财务人员使用数据库的难度,因此专门针对财务报表核算需要,开发了该工具。目前,我月度报表处理的数据量超过5G,最大的单次运算量记录接近千万,该工具主要发挥的作用就是将收集到的数据, 导入SQL SERVER数据库,进行报表运算,并且输出各类财务报表,对于几十万级的数据输入、输出基本上能够轻松应付。

    源码下载地址:https://github.com/yfl8910/DataPie

    软件界面及主要代码:

    1.导入界面,支持EXCEL2007、EXCEL2003、ACCESS2007、 CSV文件导入数据库,支持CSV文件夹整体导入相应表。财务工作的数据源较多,有业务提供的excel数据,也有从系统中取出的csv、excel或者text文档,为满足多种数据源的输入,所以多种数据源导入数据库。目前SQL SERVER导入整体效率较高,ACCESS下,excel文件导入效率最高(几十万的数据量可以很快导入),其他方式效率还有待提升。

    涉及核心代码如下

    复制代码
      public static DataTable GetDataTableFromFile(string path, string tname)
            {
                string ace = "Microsoft.ACE.OLEDB.12.0";
                string jet = "Microsoft.Jet.OLEDB.4.0";
                string xl2007 = "Excel 12.0 Xml";
                string xl2003 = "Excel 8.0";
                string imex = "IMEX=1";
                string hdr = "Yes";
                string conn = "Provider={0};Data Source={1};Extended Properties="{2};HDR={3};{4}";";
                string select = "";
                string ext = Path.GetExtension(path);
                OleDbDataAdapter oda;
                DataTable dt = new DataTable(tname);
                switch (ext.ToLower())
                {
                    case ".xlsx":
                        conn = String.Format(conn, ace, Path.GetFullPath(path), xl2007, hdr, imex);
                        select = string.Format("SELECT * FROM [{0}$]", tname);
                        break;
                    case ".xls":
                        conn = String.Format(conn, jet, Path.GetFullPath(path), xl2003, hdr, imex);
                        select = string.Format("SELECT * FROM [{0}$]", tname);
                        break;
                    case ".accdb":
                        conn = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= {0};Persist Security Info=False;", path);
                        select = string.Format("SELECT * FROM [{0}]", tname);
                        break;
                    case ".csv":
                        conn = String.Format(conn, ace, path.Substring(0, path.LastIndexOf('\')), "text;Excel 12.0", hdr, imex);
                        select = string.Format("SELECT * FROM [{0}]", Path.GetFileName(path));
                        break;
                    default:
                        throw new Exception("File Not Supported!");
                }
                OleDbConnection con = new OleDbConnection(conn);
                con.Open();
                oda = new OleDbDataAdapter(select, con);
                oda.Fill(dt);
                con.Close();
                return dt;
            }
    复制代码

    2.数据导出界面,支持EXCEL2007、CSV、ACCESS、zip压缩文件、分拆导出几个功能。我目前从事的岗位,报表整体数据量接近千万级,其中单表需要导出分发到业务手中的数据量最大也超过了百万级,目前达到百万级数据量的表导出,一般采用CSV文件或者压缩包的形式,或者分多个表导出。

    涉及核心代码。excel导出主要源码 :

    复制代码
      public static void SaveExcel(string FileName, string sql, string SheetName)
            {
    
                FileInfo newFile = new FileInfo(FileName);
                if (newFile.Exists)
                {
                    newFile.Delete();
                    newFile = new FileInfo(FileName);
                }
                using (ExcelPackage package = new ExcelPackage(newFile))
                {
                    try
                    {
                        ExcelWorksheet ws = package.Workbook.Worksheets.Add(SheetName);
    
                        IDataReader reader = DBConfig.db.DBProvider.ExecuteReader(sql);
                        ws.Cells["A1"].LoadFromDataReader(reader, true);
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    package.Save();
                }
             
            }
    
            /// <summary>
            /// 工作簿中添加新的SHEET
            /// </summary>
            public static bool SaveExcel(ExcelPackage package, string sql, string SheetName)
            {
    
                try
                {
                    ExcelWorksheet ws = package.Workbook.Worksheets.Add(SheetName);
                    IDataReader reader = DBConfig.db.DBProvider.ExecuteReader(sql);
                    ws.Cells["A1"].LoadFromDataReader(reader, true);
    
                    return true;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            /// <summary>
            /// 单表格导出到一个EXCEL工作簿
            /// </summary>
            public static int ExportExcel(string FileName, string sql, string SheetName)
            {
                if (FileName != null)
                {
                    Stopwatch watch = Stopwatch.StartNew();
                    watch.Start();
    
                    SaveExcel(FileName, sql, SheetName);
                    watch.Stop();
                    return Convert.ToInt32(watch.ElapsedMilliseconds / 1000);
                }
    
                return -1;
            }
    
            public static async Task<int> ExportExcelAsync(string FileName, string sql, string SheetName)
            {
    
                return await Task.Run(
                    () => { return ExportExcel(FileName,sql,SheetName); }
                    );
    
            }
    
            /// <summary>
            /// 多表格导出到一个EXCEL工作簿
            /// </summary>
            public static int ExportExcel(string[] TabelNameArray, string filename)
            {
                if (filename != null)
                {
                    Stopwatch watch = Stopwatch.StartNew();
                    watch.Start();
    
                    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 < TabelNameArray.Length; i++)
                        {
                            string sql = "select * from  [" + TabelNameArray[i] + "]";
                            IDataReader reader = DBConfig.db.DBProvider.ExecuteReader(sql);
                            SaveExcel(package, sql, TabelNameArray[i]);
    
                        }
                        package.Save();
                    }
                    watch.Stop();
                    return Convert.ToInt32(watch.ElapsedMilliseconds / 1000);
                }
    
                return -1;
            }
    
            public static  async Task<int> ExportExcelAsync(string[] TabelNameArray, string filename) {
    
                return await Task.Run(
                    () => { return ExportExcel( TabelNameArray,filename); }      
                    );
    
            }
    
            /// <summary>
            /// 分拆导出
            /// </summary>
            public static int ExportExcel(string[] TabelNameArray, string filename, string[] whereSQLArr)
            {
                if (filename != null)
                {
                    Stopwatch watch = Stopwatch.StartNew();
                    watch.Start();
                    FileInfo file = new FileInfo(filename);
                    int wherecount = whereSQLArr.Length;
                    int count = TabelNameArray.Length;
                    for (int i = 0; i < wherecount; i++)
                    {
                        string s = filename.Substring(0, filename.LastIndexOf("\"));
                        StringBuilder newfileName = new StringBuilder(s);
                        int index = whereSQLArr[i].LastIndexOf("=");
                        string sp = whereSQLArr[i].Substring(index + 2, whereSQLArr[i].Length - index - 3);
                        newfileName.Append("\" + file.Name.Substring(0, file.Name.LastIndexOf(".")) + "_" + sp + ".xlsx");
                        FileInfo newFile = new FileInfo(newfileName.ToString());
                        if (newFile.Exists)
                        {
                            newFile.Delete();
                            newFile = new FileInfo(newfileName.ToString());
                        }
    
                        for (int j = 0; j < count; j++)
                        {
                            string sql = "select * from [" + TabelNameArray[j] + "]" + whereSQLArr[i];
                            IDataReader reader = DBConfig.db.DBProvider.ExecuteReader(sql);
                            SaveExcel(newfileName.ToString(), sql, TabelNameArray[j]);
    
                        }
    
    
                    }
    
                    watch.Stop();
                    return Convert.ToInt32(watch.ElapsedMilliseconds / 1000);
                }
    
                return -1;
            }
    
            public static async Task<int> ExportExcelAsync(string[] TabelNameArray, string filename, string[] whereSQLArr)
            {
    
                return await Task.Run(
                    () => { return ExportExcel(TabelNameArray,filename,whereSQLArr); }
                    );
    
            }
    复制代码

    csv导出主要源码 (其他代码请参github上的源码):

    复制代码
      private static void WriteHeader(IDataReader reader, StreamWriter sw)
            {
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    if (i > 0)
                        sw.Write(',');
                    sw.Write(reader.GetName(i));
                }
                sw.Write(Environment.NewLine);
            }
            private static void WriteContent(IDataReader reader, StreamWriter sw)
            {
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    if (i > 0)
                        sw.Write(',');
                    String v = reader[i].ToString();
                    if (v.Contains(',') || v.Contains('
    ') || v.Contains('
    ') || v.Contains('"'))
                    {
                        sw.Write('"');
                        sw.Write(v.Replace(""", """"));
                        sw.Write('"');
                    }
                    else
                    {
                        sw.Write(v);
                    }
                }
                sw.Write(Environment.NewLine);
            }
    
            public static int SaveCsv(IDataReader reader, string filename)
            {
                Stopwatch watch = Stopwatch.StartNew();
                watch.Start();
    
                StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding("gb2312"));
                WriteHeader(reader, sw);
    
                while (reader.Read())
                {
                    WriteContent(reader, sw);
                }
    
                sw.Flush();
                sw.Close();//释放资源
    
                watch.Stop();
                return Convert.ToInt32(watch.ElapsedMilliseconds / 1000);
            }
            public static async Task<int> ExportCsvAsync(IDataReader reader, string filename)
            {
                return await Task.Run( () => { return SaveCsv( reader,  filename);} );
            }
    
            public static StreamWriter GetStreamWriter(string filename, int outCount)
            {
                string s = filename.Substring(0, filename.LastIndexOf("."));
                StringBuilder newfileName = new StringBuilder(s);
                newfileName.Append(outCount + 1 + ".csv");
                FileInfo newFile = new FileInfo(newfileName.ToString());
                if (newFile.Exists)
                {
                    newFile.Delete();
                    newFile = new FileInfo(newfileName.ToString());
                }
                StreamWriter sw = new StreamWriter(newfileName.ToString(), false, Encoding.GetEncoding("gb2312"));
                return sw;
            }
        
            public static int SaveCsv(IDataReader reader, string filename, int pagesize)
            {
                int innerCount = 0, outCount = 0;
                Stopwatch watch = Stopwatch.StartNew();
                watch.Start();
                StreamWriter sw = GetStreamWriter(filename, outCount);
                WriteHeader(reader, sw);
                while (reader.Read())
                {
                    if (innerCount < pagesize)
                    {
                        WriteContent(reader, sw);
                        innerCount++;
                    }
                    else
                    {
                        innerCount = 0;
                        outCount++;
                        sw.Flush();
                        sw.Close();
                        sw = GetStreamWriter(filename, outCount);
                        WriteHeader(reader, sw);
                        WriteContent(reader, sw);
                        innerCount++;
                    }
                }
                sw.Flush();
                sw.Close();
                watch.Stop();
                return Convert.ToInt32(watch.ElapsedMilliseconds / 1000);
    
            }
            public static async Task<int> ExportCsvAsync(IDataReader reader, string filename, int pagesize)
            {
                return await Task.Run(() => { return SaveCsv(reader, filename,pagesize); });
            }
    复制代码

    3.设置拆分导出条件。财务工作中,涉及向业务单位分发各自的财务数据,如果采用手工筛选,然后分发效率较低,所以开发此功能,快速实现数据的拆分发送。

    4.自定义SQL代码导出。通过双击左边的表名即可自动生成导出SQL脚本,点击添加条件,还可以增加导出的条件。该工具在数据核查时,使用比较方便,虽然SQL server数据库也自带相应的功能,但是导出功能缺无法满足我的需求,所以开发一个方便自己使用的界面还是很有必要的。

    其他各项功能,及代码请参考github上的源码。

  • 相关阅读:
    STL的适配器、仿函数学习之一:accumulate和for_each的使用心得
    百度笔试题--------数字拼接,求出最小的那个
    百度面试题----依概率生成
    百度笔试题----C语言版revert
    百度笔度题-----蚂蚁爬杆问题
    Try....Catch......Finally 的执行顺序
    数据库SQL SERVER 2008R2 远程连接配置说明
    C#中的数据库的连接方式分类说明(转载)
    网络通信—udp使用领悟
    (转载)C#网络通信之TCP连接
  • 原文地址:https://www.cnblogs.com/Leo_wl/p/3815722.html
Copyright © 2020-2023  润新知