How to import excel file using oledb?

Posted by Oswaldlily under ASP.NET on 2/6/2014 | Points: 10 | Views : 2032 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	@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

Latest Posts