作者:手机用户彡2570437895 | 来源:互联网 | 2023-02-01 09:11
理念:
读取XLS并在Microsoft SQL上上传数据
问题:
只有第一列正在上传到数据库
我的代码:
private void button1_Click(object sender, EventArgs e) {
// string path = @"XXXX\xls_test\Book1.xlsx";
string path = @ "XXXX\xls_test\Book1.xlsx";
ImportDataFromExcel(path);
}
public void ImportDataFromExcel(string excelFilePath) {
//declare variables - edit these based on your particular situation
string ssqltable = "Table1";
// make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have different
string myexceldataquery = "select student from [Sheet1$]";
try {
//create our connection strings
//string sexcelcOnnectionstring= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath + ";" + "Extended Properties=" + "\"Excel 8.0; HDR=Yes; IMEX=1;\"";
string sexcelcOnnectionstring= @ "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= XXXX\xls_test\Book1.xlsx" + ";" + "Extended Properties=Excel 12.0";
string ssqlcOnnectionstring= "XXXX";
//execute a query to erase any previous data from our destination table
string sclearsql = "delete from " + ssqltable;
SqlConnection sqlcOnn= new SqlConnection(ssqlconnectionstring);
SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);
sqlconn.Open();
sqlcmd.ExecuteNonQuery();
sqlconn.Close();
//series of commands to bulk copy data from the excel file into our sql table
OleDbConnection oledbcOnn= new OleDbConnection(sexcelconnectionstring);
OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
oledbconn.Open();
OleDbDataReader dr = oledbcmd.ExecuteReader();
SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);
bulkcopy.DestinatiOnTableName= ssqltable;
while (dr.Read()) {
bulkcopy.WriteToServer(dr);
}
dr.Close();
oledbconn.Close();
MessageBox.Show("File imported into sql server.");
} catch (Exception ex) {
//handle exception
MessageBox.Show(ex.ToString());
MessageBox.Show("Enter exception");
}
}
数据库上的表:
CREATE TABLE [dbo].[Table1](
[student] [varchar](50) NULL,
[rollno] [int] NULL,
[course] [varchar](50) NULL
) ON [PRIMARY]
GO
如果您想从XLS和数据库示例中访问2张图片:https:
//drive.google.com/drive/folders/0B98UpTa2n4XbeHZtOHU2cVotUms?usp =sharing
最后,我从这个代码的大部分内容:https:
//code.msdn.microsoft.com/office/Import-Excel-Spreadsheet-2b7ca7cf#content
我改变了一点,但大多数想法来自这些链接.
欢迎任何帮助!谢谢.
1> oerkelens..:
有点猜测,但你有:
string myexceldataquery = "select student from [Sheet1$]";
然后你说
只有第一列正在上传到数据库
如果你只选择列,这似乎是完全可以预期的.
也许选择你想要的其他领域(我猜他们!):
string myexceldataquery = "select student, rollno, course from [Sheet1$]";