作为财务数据核算人员，面对大量的业务与财务数据，借助于传统的excel表格，已经力不从心。最近几个月，利用周末及下班的空闲时间，写了一个数据库 导入 导出 工具 ，以方便业务逻辑密集型的数据处理。目前，datapie 支持 百万级别的数据 导出 ，对于几十万
作为财务数据核算人员，面对大量的业务与财务数据，借助于传统的excel表格，已经力不从心。最近几个月，利用周末及下班的空闲时间，写了一个数据库导入导出工具，以方便业务逻辑密集型的数据处理。目前，datapie支持百万级别的数据导出，对于几十万的数据导入，也轻松应付。
源码及安装包下载地址：https://github.com/yfl8910/datapie
先看看界面，登录界面：
主界面：
主要代码：
1.把excel文件读到datatable
///
///根据excel路径和sheet名称，返回excel的datatable
///
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(iliststring> 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(iliststring> 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(iliststring> 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
{
dt.rows[n].setadded();
}
//adapter.updatebatchsize = 1000;
adapter.update(dt);
}
return true;
}
catch (exception e)
{
throw e;
}
}
3．导出excel文件
///
///保存excel文件，覆盖相同文件名的文件
///
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;
}
}
///
///多个表格导出到一个excel工作簿
///
public static void export(iliststring> sheetnames, string filename, dbconfig db, iliststring> 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
{
dt = db.db.returndatatable(sqls[i]);
saveexcel(sheetnames[i], dt, package);
}
package.save();
}
}
///
///单个表格导出到一个excel工作簿
///
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();
}
}
///
///单个表导出到多个excel工作簿（分页）
///
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
{
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();
}
}
}
4.datapie下载地址
https://github.com/yfl8910/datapie