使用 C# 将 Excel 文件导入 Microsoft SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10750803/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Import Excel file into Microsoft SQL Server using C#
提问by sean
I have a C# program that I want to import an Excel file into Microsoft SQL Server 2008 R2.
我有一个 C# 程序，我想将 Excel 文件导入 Microsoft SQL Server 2008 R2。
Can someone give me a link or tutorial where I can fully understand on how to this?
有人可以给我一个链接或教程，让我可以完全理解如何做到这一点？
I've been doing this for a long time and I really don't have an idea on how to implement this.
我已经这样做了很长时间，我真的不知道如何实现这一点。
Please help. Thanks.
请帮忙。谢谢。
回答by Pranay Rana
回答by JayOnDotNet
string excelConnectionString = @"Provider=Microsoft
.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended
Properties=""Excel 8.0;HDR=YES;""";
// Create Connection to Excel Workbook
// Create Connection to Excel Workbook
We can Import excel to sql server like this
我们可以像这样将excel导入到sql server
using (OleDbConnection connection =
new OleDbConnection(excelConnectionString)){
using (OleDbConnection connection =
new OleDbConnection(excelConnectionString)){
OleDbCommand command = new OleDbCommand
("Select ID,Data FROM [Data$]", connection);
OleDbCommand command = new OleDbCommand
("Select ID,Data FROM [Data$]", connection);
connection.Open();
`// Create DbDataReader to Data Worksheet `
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "Data Source=.;
Initial Catalog=Test;Integrated Security=True";
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "ExcelData";
bulkCopy.WriteToServer(dr);
}
回答by Andrew
I think that most useful answers already have been given. I am not going to describe the C# way, but rathher give you an easy way of doing it without C#:
我认为最有用的答案已经给出。我不打算描述 C# 方式，而是提供一种无需 C# 的简单方法：
Open your MS Access, link your MS SQL database via ODBC, open your table in Access and paste your Excel records there via copy-paste (preformatted to match the table schema.)
打开您的 MS Access，通过 ODBC 链接您的 MS SQL 数据库，在 Access 中打开您的表格并通过复制粘贴（预先格式化以匹配表格架构）将您的 Excel 记录粘贴到那里。
This way is very fast and useful when you do some one-time data import.
当您执行一些一次性数据导入时，这种方式非常快速且有用。
回答by Mackie
This code may also help you.
此代码也可能对您有所帮助。
private void processExcel(string filename)
{
filename = Server.MapPath("~/Files/WM-0b23-productsBook.xlsx");
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
var missing = System.Reflection.Missing.Value;
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(filename, false, true, missing, missing, missing, true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, '\t', false, false, 0, false, true, 0);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Microsoft.Office.Interop.Excel.Range xlRange = xlWorkSheet.UsedRange;
Array myValues = (Array)xlRange.Cells.Value2;
int vertical = myValues.GetLength(0);
int horizontal = myValues.GetLength(1);
System.Data.DataTable dt = new System.Data.DataTable();
// must start with index = 1
// get header information
for (int i = 1; i <= horizontal; i++)
{
dt.Columns.Add(new DataColumn(myValues.GetValue(1, i).ToString()));
}
// Get the row information
for (int a = 2; a <= vertical; a++)
{
object[] poop = new object[horizontal];
for (int b = 1; b <= horizontal; b++)
{
poop[b - 1] = myValues.GetValue(a, b);
}
DataRow row = dt.NewRow();
row.ItemArray = poop;
dt.Rows.Add(row);
}
// assign table to default data grid view
GridView1.DataSource = dt;
GridView1.DataBind();
xlWorkBook.Close(true, missing, missing);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Unable to release the Object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
