客服电话
APP下载
迪恩网络APP
随时随地掌握行业动态
官方微信
扫描二维码
关注迪恩网络微信公众号
|
作为财务数据核算人员，面对大量的业务与财务数据，借助于传统的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) {
全部评论
专题导读
热门推荐
热门话题
 阅读排行榜
|
请发表评论