Read open Excel sheet into DataSet?

[ThienZ]

Hi,

 

how can I read an open Excelsheet into a dataset? Usually I'm using this :

String sConnectionString =  
"Provider=Microsoft.Jet.OLEDB.4.0;" + 
@"Data Source=c:\xlfile.xls;Extended Properties=Excel 8.0;"; 


OleDbConnection objConn = new OleDbConnection(sConnectionString); 

objConn.Open(); 

OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [sheet1$]", objConn); 

OleDbDataAdapter myda = new OleDbDataAdapter();
myda.SelectCommand = objCmdSelect;

myda.Fill(ds);

 

I can't use this way because i can't close the file...

 

Thx

[cikaPero]

Hi,

 

there is a much more cleaner, faster and easier way to import Excel to DataSet.

 

With GemBox.Spreadsheet Excel C# component this is all the code you need:

 

var ef = new ExcelFile();
ef.LoadXls(@"c:\xlfile.xls");

// DataSet schema has to be defined before this.
for(int i = 0; i < ef.Worksheets.Count; ++i)
{
var ws = ef.Worksheets[i];
ws.ExtractToDataTable(dataSet.Tables[i], ws.Rows.Count, ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows[0], ws.Columns[0]);
}