如何通过C＃从Excel数据表导入数据到SQL [英] How to import data to SQL from excel data table by C#
问题描述
问题
如何将代码导入数据表写入SQL Server 2012.
详细信息
我有Excel工作表名称InvoiceData.xlsx有两列
Problem
How to write code import data table to SQL server 2012 .
Details
I have excel sheet name InvoiceData.xlsx have two columns
UnitCode CurrentMeterReading
21544 2900
22152 9000
19822 9200
我从excel表到datatable的成功数据如下：
I get success data from excel sheet to datatable as following :
public System.Data. DataTable Showdataprint()
{
OleDbConnection con = new OleDbConnection(connectionString);
con.Open();
string str = @"SELECT [UnitCode],[CurrentMeterReading] FROM [Sheet1$]";
OleDbCommand com = new OleDbCommand();
com = new OleDbCommand(str, con);
OleDbDataAdapter oledbda = new OleDbDataAdapter();
oledbda = new OleDbDataAdapter(com);
DataSet ds = new DataSet();
ds = new DataSet();
oledbda.Fill(ds,"[Sheet1$]");
con.Close();
System.Data.DataTable dt = new System.Data.DataTable();
dt = ds.Tables["[Sheet1$]"];
return dt;
}
我从excel成功获取数据现在我需要将数据从datatable导入到sql server 2012 TableName：WahInvoice PK：Serial
I get successfully data from excel to Datatable Now I need to Import data from datatable to sql server 2012 TableName : WahInvoice PK : Serial
CREATE TABLE [dbo].[WAHInvoice](
[Serial] [int] NOT NULL,
[Year] [int] NULL,
[Month] [int] NULL,
[UnitCode] [int] NULL,
[CurrentReadingDate] [date] NULL,
[CurrentMeterReading] [decimal](18, 2) NULL,
CONSTRAINT [PK_WAHInvoice_1] PRIMARY KEY CLUSTERED
(
[Serial] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
我需要知道如何将数据从excel导入到sql。
假设我有关于表中sql的数据WahInvoice
，最大序列2000这个含义数据如下：
Serial UnitCode CurrentMeterReading
2001 21544 2900
2002 22152 9000
2003 19822 9200
剩余列的所有值都将为空。
实际上我为导入数据编写的内容导入按钮下的sql在导入按钮下单击我写如下：
I need to know how to Import data from excel to sql .
suppose i have data on sql in table WahInvoice
with max serial 2000 this meaning data will be as following :
Serial UnitCode CurrentMeterReading
2001 21544 2900
2002 22152 9000
2003 19822 9200
and all values for columns remaining will be null .
Actually what i write for Import data to sql under import button Under Import button click i write as following :
System.Data.DataTable dt = new System.Data.DataTable();
dt = Showdataprint();
//How to import datatable to sql server 2012 < br $> b $ b
我尝试过：
What I have tried:
How to Import data to sql from Excel data table by c#
推荐答案
;
OleDbCommand com = new OleDbCommand（）;
com = new OleDbCommand（str，con）;
OleDbDataAdapter oledbda = new OleDbDataAdapter（）;
oledbda = new OleDbDataAdapter（com）;
DataSet ds = new DataSet（）;
ds = new DataSet（）;
oledbda.Fill（ds， [Sheet1
"; OleDbCommand com = new OleDbCommand(); com = new OleDbCommand(str, con); OleDbDataAdapter oledbda = new OleDbDataAdapter(); oledbda = new OleDbDataAdapter(com); DataSet ds = new DataSet(); ds = new DataSet(); oledbda.Fill(ds,"[Sheet1
）;
co n.Close（）;
System.Data.DataTable dt = new System.Data.DataTable（）;
dt = ds.Tables [ [Sheet1
"); con.Close(); System.Data.DataTable dt = new System.Data.DataTable(); dt = ds.Tables["[Sheet1
< /跨度>];
return dt;
}
"]; return dt; }
我从excel成功获取数据现在我需要将数据从datatable导入到sql server 2012 TableName：WahInvoice PK：Serial
I get successfully data from excel to Datatable Now I need to Import data from datatable to sql server 2012 TableName : WahInvoice PK : Serial
CREATE TABLE [dbo].[WAHInvoice](
[Serial] [int] NOT NULL,
[Year] [int] NULL,
[Month] [int] NULL,
[UnitCode] [int] NULL,
[CurrentReadingDate] [date] NULL,
[CurrentMeterReading] [decimal](18, 2) NULL,
CONSTRAINT [PK_WAHInvoice_1] PRIMARY KEY CLUSTERED
(
[Serial] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
我需要知道如何将数据从excel导入到sql。
假设我有关于表中sql的数据WahInvoice
，最大序列2000这个含义数据如下：
Serial UnitCode CurrentMeterReading
2001 21544 2900
2002 22152 9000
2003 19822 9200
剩余列的所有值都将为空。
实际上我为导入数据编写的内容导入按钮下的sql在导入按钮下单击我写如下：
I need to know how to Import data from excel to sql .
suppose i have data on sql in table WahInvoice
with max serial 2000 this meaning data will be as following :
Serial UnitCode CurrentMeterReading
2001 21544 2900
2002 22152 9000
2003 19822 9200
and all values for columns remaining will be null .
Actually what i write for Import data to sql under import button Under Import button click i write as following :
System.Data.DataTable dt = new System.Data.DataTable();
dt = Showdataprint();
//How to import datatable to sql server 2012 < br $> b $ b
我尝试过：
What I have tried:
How to Import data to sql from Excel data table by c#
这篇关于如何通过C＃从Excel数据表导入数据到SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
