Excel导入数据到数据库(Sql2005 ,Access)
1.在Sql2005创建对应的表"Roll"
2.应用以下这段代码
string execelConnectionStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
using (OleDbConnection conn = new OleDbConnection(execelConnectionStr))
{
OleDbCommand cmd = new OleDbCommand("select * FROM [Sheet1$]", conn);
conn.Open();
using (DbDataReader dr = cmd.ExecuteReader())
{
string sqlConnectionString = @"Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=True";
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "Roll";
bulkCopy.WriteToServer(dr);
}
}
}
using (OleDbConnection conn = new OleDbConnection(execelConnectionStr))
{
OleDbCommand cmd = new OleDbCommand("select * FROM [Sheet1$]", conn);
conn.Open();
using (DbDataReader dr = cmd.ExecuteReader())
{
string sqlConnectionString = @"Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=True";
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "Roll";
bulkCopy.WriteToServer(dr);
}
}
}
3.Excel导入、导出数据到access,使用Com组件
public static void ExcelImportDB()
{
OleDbConnection conExcel = new OleDbConnection();
try
{
ApplicationClass access = new ApplicationClass();
access.Visible = false;
access.OpenCurrentDatabase(Settings.Default.DBPath, true, "");
OpenFileDialog openFile = new OpenFileDialog();
openFile.Filter = ("Excel 文件(*.xls)|*.xls");
if (openFile.ShowDialog() == DialogResult.OK)
{
access.DoCmd.TransferSpreadsheet(AcDataTransferType.acImport, AcSpreadSheetType.acSpreadsheetTypeExcel12, "Intergral", openFile.FileName, true, null, null);
access.CloseCurrentDatabase();
access.DoCmd.Quit(AcQuitOption.acQuitSaveAll);
Marshal.ReleaseComObject(access);
access = null;
System.Windows.Forms.MessageBox.Show("导入数据成功", "导入数据", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.ToString());
}
finally
{
conExcel.Close();
}
}
public static void DBExportExcel()
{
try
{
ApplicationClass access = new ApplicationClass();
access.Visible = false;
access.OpenCurrentDatabase(Settings.Default.DBPath, false, "");
SaveFileDialog saveFile = new SaveFileDialog();
saveFile.Filter = ("Excel 文件(*.xls)|*.xls");
if (saveFile.ShowDialog() == DialogResult.OK)
{
access.DoCmd.TransferSpreadsheet(AcDataTransferType.acExport, AcSpreadSheetType.acSpreadsheetTypeExcel9, "Intergral", saveFile.FileName, true, null, null);
access.CloseCurrentDatabase();
access.DoCmd.Quit(AcQuitOption.acQuitSaveNone);
Marshal.ReleaseComObject(access);
access = null;
MessageBox.Show("导出数据成功", "导出数据", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
{
OleDbConnection conExcel = new OleDbConnection();
try
{
ApplicationClass access = new ApplicationClass();
access.Visible = false;
access.OpenCurrentDatabase(Settings.Default.DBPath, true, "");
OpenFileDialog openFile = new OpenFileDialog();
openFile.Filter = ("Excel 文件(*.xls)|*.xls");
if (openFile.ShowDialog() == DialogResult.OK)
{
access.DoCmd.TransferSpreadsheet(AcDataTransferType.acImport, AcSpreadSheetType.acSpreadsheetTypeExcel12, "Intergral", openFile.FileName, true, null, null);
access.CloseCurrentDatabase();
access.DoCmd.Quit(AcQuitOption.acQuitSaveAll);
Marshal.ReleaseComObject(access);
access = null;
System.Windows.Forms.MessageBox.Show("导入数据成功", "导入数据", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.ToString());
}
finally
{
conExcel.Close();
}
}
public static void DBExportExcel()
{
try
{
ApplicationClass access = new ApplicationClass();
access.Visible = false;
access.OpenCurrentDatabase(Settings.Default.DBPath, false, "");
SaveFileDialog saveFile = new SaveFileDialog();
saveFile.Filter = ("Excel 文件(*.xls)|*.xls");
if (saveFile.ShowDialog() == DialogResult.OK)
{
access.DoCmd.TransferSpreadsheet(AcDataTransferType.acExport, AcSpreadSheetType.acSpreadsheetTypeExcel9, "Intergral", saveFile.FileName, true, null, null);
access.CloseCurrentDatabase();
access.DoCmd.Quit(AcQuitOption.acQuitSaveNone);
Marshal.ReleaseComObject(access);
access = null;
MessageBox.Show("导出数据成功", "导出数据", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}