Excel导入数据到数据库(Sql2005 ,Access)

最新推荐文章于 2021-11-15 14:00:59 发布

weixin_33766168

最新推荐文章于 2021-11-15 14:00:59 发布

阅读量137

点赞数 1

文章标签：数据库

本文介绍如何使用C#实现Excel数据导入到SQL Server数据库的方法，包括通过OLE DB连接读取Excel表格数据，并批量写入SQL Server数据库的具体步骤。此外，还提供了使用Com组件将数据在Access和Excel间进行导入导出的示例代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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);

}

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());

}