How to import excel file using oledb?

Posted by Oswaldlily under ASP.NET on 2/6/2014 | Points: 10 | Views : 913 | Status : [Member] | Replies : 2
How to read empty rows and columns using oledb connection?
conn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; ;data source='" & pathname & " '; Extended Properties=""Excel 12.0;IMEX=1;""")


Problem:
Above code import oly tables..But excluding above rows&sides of exceltable.

I need to include as it is from excel sheet..

how to solve this




Responses

Posted by: kgovindarao523-21772 on: 2/6/2014 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi,
Try this code
private void button1_Click(object sender, EventArgs e)
{
string fileName = @"C:\test.xlsx";
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=YES;\"";
OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString);
con.Open();
OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter("select * from [SHEET1$]", con);
DataSet excelDataSet = new DataSet();
cmd.Fill(excelDataSet);
xl.DataSource = excelDataSet.Tables[0];
con.Close();
}



Thank you,
Govind

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Oswaldlily on: 2/6/2014 [Member] Starter | Points: 25

Up
0
Down
@govind

getting this error in connection string "Format of the initialization string does not conform to specification starting at index 124".

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response