利用OleDb方式對DataSet 和 Excel 數(shù)據(jù)快速導(dǎo)入導(dǎo)出
一：DataSet 導(dǎo)入到Excel
/// <summary>
/// 導(dǎo)出數(shù)據(jù)到Excel
/// </summary>
/// <param name="Path">需要導(dǎo)入的Excel地址</param>
/// <param name="oldds">需要導(dǎo)入的數(shù)據(jù)</param>
/// <param name="TableName">表名</param>
public static void DSToExcel2003(string Path, DataSet oldds, string TableName)
{
//Excel2003的連接字符串
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Path + ";Extended Properties=Excel 8.0";
//執(zhí)行導(dǎo)入
ExcuteSQL(oldds, TableName, strCon);
}
/// <summary>
/// 導(dǎo)出數(shù)據(jù)到Excel
/// </summary>
/// <param name="Path">需要導(dǎo)入的Excel地址</param>
/// <param name="oldds">需要導(dǎo)入的數(shù)據(jù)</param>
/// <param name="TableName">表名</param>
public static void DSToExcel2007(string Path, DataSet oldds, string TableName)
{
//Excel2007的連接字符串
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Path + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
//執(zhí)行導(dǎo)入
ExcuteSQL(oldds, TableName, strConn);
}
/// <summary>
/// 執(zhí)行
/// </summary>
/// <param name="oldds">需要導(dǎo)入的數(shù)據(jù)</param>
/// <param name="TableName">表名</param>
/// <param name="strCon">連接字符串</param>
private static void ExcuteSQL(DataSet oldds, string TableName, string strCon)
{
//連接
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = "select * from [" + TableName + "$]";
try
{
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
System.Data.OleDb.OleDbCommandBuilder builder = new OleDbCommandBuilder(myCommand);
//QuotePrefix和QuoteSuffix主要是對builder生成InsertComment命令時使用。
//獲取insert語句中保留字符（起始位置）
builder.QuotePrefix = "[";
//獲取insert語句中保留字符（結(jié)束位置）
builder.QuoteSuffix = "]";
DataSet newds = new DataSet();
//獲得表結(jié)構(gòu)
DataTable ndt = oldds.Tables[0].Clone();
//清空數(shù)據(jù)
//ndt.Rows.Clear();
ndt.TableName = TableName;
newds.Tables.Add(ndt);
//myCommand.Fill(newds, TableName);
for (int i = 0; i < oldds.Tables[0].Rows.Count; i++)
{
//在這里不能使用ImportRow方法將一行導(dǎo)入到news中，
//因為ImportRow將保留原來DataRow的所有設(shè)置(DataRowState狀態(tài)不變)。
//在使用ImportRow后newds內(nèi)有值，但不能更新到Excel中因為所有導(dǎo)入行的DataRowState!=Added
DataRow nrow = newds.Tables[0].NewRow();
for (int j = 0; j < oldds.Tables[0].Columns.Count; j++)
{
nrow[j] = oldds.Tables[0].Rows[i][j];
}
newds.Tables[0].Rows.Add(nrow);
}
//插入數(shù)據(jù)
myCommand.Update(newds, TableName);
}
finally
{
myConn.Close();
}
}
/// 導(dǎo)出數(shù)據(jù)到Excel
/// </summary>
/// <param name="Path">需要導(dǎo)入的Excel地址</param>
/// <param name="oldds">需要導(dǎo)入的數(shù)據(jù)</param>
/// <param name="TableName">表名</param>
public static void DSToExcel2003(string Path, DataSet oldds, string TableName)
{
//Excel2003的連接字符串
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Path + ";Extended Properties=Excel 8.0";
//執(zhí)行導(dǎo)入
ExcuteSQL(oldds, TableName, strCon);
}
/// <summary>
/// 導(dǎo)出數(shù)據(jù)到Excel
/// </summary>
/// <param name="Path">需要導(dǎo)入的Excel地址</param>
/// <param name="oldds">需要導(dǎo)入的數(shù)據(jù)</param>
/// <param name="TableName">表名</param>
public static void DSToExcel2007(string Path, DataSet oldds, string TableName)
{
//Excel2007的連接字符串
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Path + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
//執(zhí)行導(dǎo)入
ExcuteSQL(oldds, TableName, strConn);
}
/// <summary>
/// 執(zhí)行
/// </summary>
/// <param name="oldds">需要導(dǎo)入的數(shù)據(jù)</param>
/// <param name="TableName">表名</param>
/// <param name="strCon">連接字符串</param>
private static void ExcuteSQL(DataSet oldds, string TableName, string strCon)
{
//連接
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = "select * from [" + TableName + "$]";
try
{
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
System.Data.OleDb.OleDbCommandBuilder builder = new OleDbCommandBuilder(myCommand);
//QuotePrefix和QuoteSuffix主要是對builder生成InsertComment命令時使用。
//獲取insert語句中保留字符（起始位置）
builder.QuotePrefix = "[";
//獲取insert語句中保留字符（結(jié)束位置）
builder.QuoteSuffix = "]";
DataSet newds = new DataSet();
//獲得表結(jié)構(gòu)
DataTable ndt = oldds.Tables[0].Clone();
//清空數(shù)據(jù)
//ndt.Rows.Clear();
ndt.TableName = TableName;
newds.Tables.Add(ndt);
//myCommand.Fill(newds, TableName);
for (int i = 0; i < oldds.Tables[0].Rows.Count; i++)
{
//在這里不能使用ImportRow方法將一行導(dǎo)入到news中，
//因為ImportRow將保留原來DataRow的所有設(shè)置(DataRowState狀態(tài)不變)。
//在使用ImportRow后newds內(nèi)有值，但不能更新到Excel中因為所有導(dǎo)入行的DataRowState!=Added
DataRow nrow = newds.Tables[0].NewRow();
for (int j = 0; j < oldds.Tables[0].Columns.Count; j++)
{
nrow[j] = oldds.Tables[0].Rows[i][j];
}
newds.Tables[0].Rows.Add(nrow);
}
//插入數(shù)據(jù)
myCommand.Update(newds, TableName);
}
finally
{
myConn.Close();
}
}
使用實例
/// <summary>
/// 導(dǎo)出數(shù)據(jù)
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExport_Click(object sender, EventArgs e)
{
//string connString = "server=localhost;uid=sa;pwd=123456;database=MYDB";
string excelModlePath = @"G:\Study\ExcelOper\Model\學(xué)籍表.xls";
//文件保存對話框
System.Windows.Forms.SaveFileDialog fd = new SaveFileDialog();
fd.DefaultExt = ".xls";
//string savefilepath = @"G:\Study\ExcelOper\SaveFolder\study.xls";
if (fd.ShowDialog() == DialogResult.OK)
{
using (ExcelHelper excel = new ExcelHelper())
{
excel.Open(excelModlePath);
//保存數(shù)據(jù)到用戶指定的文件夾
excel.SaveFile(fd.FileName);
}
string connString = "server=localhost;uid=sa;pwd=123456;database=MYDB";
DataSet ds = SqlHelper.ExecuteDataSet(connString, CommandType.Text, "select * from study");
ds.Tables[0].TableName = "study";
ExcelHelper.DSToExcel2003(fd.FileName, ds, "study");
}
}
/// 導(dǎo)出數(shù)據(jù)
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExport_Click(object sender, EventArgs e)
{
//string connString = "server=localhost;uid=sa;pwd=123456;database=MYDB";
string excelModlePath = @"G:\Study\ExcelOper\Model\學(xué)籍表.xls";
//文件保存對話框
System.Windows.Forms.SaveFileDialog fd = new SaveFileDialog();
fd.DefaultExt = ".xls";
//string savefilepath = @"G:\Study\ExcelOper\SaveFolder\study.xls";
if (fd.ShowDialog() == DialogResult.OK)
{
using (ExcelHelper excel = new ExcelHelper())
{
excel.Open(excelModlePath);
//保存數(shù)據(jù)到用戶指定的文件夾
excel.SaveFile(fd.FileName);
}
string connString = "server=localhost;uid=sa;pwd=123456;database=MYDB";
DataSet ds = SqlHelper.ExecuteDataSet(connString, CommandType.Text, "select * from study");
ds.Tables[0].TableName = "study";
ExcelHelper.DSToExcel2003(fd.FileName, ds, "study");
}
}
二 Excel導(dǎo)出到DataSet
/// <summary>
/// 查詢Excle2007的數(shù)據(jù)
/// </summary>
/// <param name="Path"></param>
/// <returns></returns>
public static DataSet Excel2007ToDS(string Path, string SheetName)
{
//Excel2007鏈接字符串
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Path + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
return ExcuteQuery(strConn, SheetName);
}
/// <summary>
/// 查詢Excle2003的數(shù)據(jù)
/// </summary>
/// <param name="Path"></param>
/// <returns></returns>
public static DataSet Excel2003ToDS(string Path, string SheetName)
{
//Excel2003鏈接字符串
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
return ExcuteQuery(strConn, SheetName);
}
/// <summary>
/// 執(zhí)行查詢
/// </summary>
/// <param name="strConn"></param>
/// <returns></returns>
private static DataSet ExcuteQuery(string strConn, string SheetName)
{
//默認(rèn)sheet名
if (string.IsNullOrEmpty(SheetName))
{
SheetName = "Sheet1";
}
//鏈接
OleDbConnection conn = new OleDbConnection(strConn);
DataSet ds = null;
try
{
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = "select * from [" + SheetName + "$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
//查詢數(shù)據(jù)
myCommand.Fill(ds, SheetName);
}
finally
{
conn.Close();
}
//
return ds;
}
/// 查詢Excle2007的數(shù)據(jù)
/// </summary>
/// <param name="Path"></param>
/// <returns></returns>
public static DataSet Excel2007ToDS(string Path, string SheetName)
{
//Excel2007鏈接字符串
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Path + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
return ExcuteQuery(strConn, SheetName);
}
/// <summary>
/// 查詢Excle2003的數(shù)據(jù)
/// </summary>
/// <param name="Path"></param>
/// <returns></returns>
public static DataSet Excel2003ToDS(string Path, string SheetName)
{
//Excel2003鏈接字符串
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
return ExcuteQuery(strConn, SheetName);
}
/// <summary>
/// 執(zhí)行查詢
/// </summary>
/// <param name="strConn"></param>
/// <returns></returns>
private static DataSet ExcuteQuery(string strConn, string SheetName)
{
//默認(rèn)sheet名
if (string.IsNullOrEmpty(SheetName))
{
SheetName = "Sheet1";
}
//鏈接
OleDbConnection conn = new OleDbConnection(strConn);
DataSet ds = null;
try
{
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = "select * from [" + SheetName + "$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
//查詢數(shù)據(jù)
myCommand.Fill(ds, SheetName);
}
finally
{
conn.Close();
}
//
return ds;
}
使用實例
private void btnImport_Click(object sender, EventArgs e)
{
//openFileDialog1.ShowDialog();
//if (openFileDialog1.RestoreDirectory)
//{
//}
string connString = "server=localhost;uid=sa;pwd=123456;database=MYDB";
System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
if (fd.ShowDialog() == DialogResult.OK)
{
TransferData(fd.FileName, "study", connString);
DataSet ds = SqlHelper.ExecuteDataSet(connString, CommandType.Text, "select * from study");
dataGridView1.DataSource = ds.Tables[0];
}
}
{
//openFileDialog1.ShowDialog();
//if (openFileDialog1.RestoreDirectory)
//{
//}
string connString = "server=localhost;uid=sa;pwd=123456;database=MYDB";
System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
if (fd.ShowDialog() == DialogResult.OK)
{
TransferData(fd.FileName, "study", connString);
DataSet ds = SqlHelper.ExecuteDataSet(connString, CommandType.Text, "select * from study");
dataGridView1.DataSource = ds.Tables[0];
}
}
注意事項：
一：DataSet導(dǎo)入到Excel時 導(dǎo)入的Excel的文件的Sheet名要和要導(dǎo)入的DataTable名一致，
Sheet的數(shù)據(jù)列名也要和DataTable列名一致放在Sheet的第一排
如下圖:
二：Excel導(dǎo)出數(shù)據(jù)到DataSet的SheetName 需要和實際的Sheet名一致
總結(jié)：和直接使用office內(nèi)核訪問Excel數(shù)據(jù)的方式相比相信這種方式的讀寫速度上會占有很大優(yōu)勢
并且不用擔(dān)心非托管資源的釋放
幽夜底衣角，那一片清風(fēng)。
