Updated getting data from an Excel file in C#

Ok I posted how to get data from an excel file a bit ago. I have some update code that reads all files into a dataset, or reads from a specific file. You could easily modify this code to read from a specific sheet as well.

public static DataSet GetAllSheetsFromExcelFile( string filename )
{
DataSet ds;

try
{
ds = new DataSet();

DataTable dtSheets = new DataTable();

// get a datatable with the worksheet name(s)
OleDbConnection con = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
filename + ";Extended Properties=Excel 8.0" );

con.Open();

// get a datatable of the sheetnames in this file
DataTable dtNames = con.GetOleDbSchemaTable( System.Data.OleDb.OleDbSchemaGuid.Tables, new Object[]
{ null, null, null, "TABLE" } );

// add each sheet as a new table to our dataset
foreach( DataRow row in dtNames.Rows )
{
DataTable dtTemp = new DataTable();

string tableName = row["TABLE_NAME"].ToString();

OleDbDataAdapter da = new OleDbDataAdapter( "select * from [" + tableName + "]", con );

da.Fill( dtTemp );

ds.Tables.Add( dtTemp );

}//end foreach

con.Close();

return ds;

}//end try
catch( Exception ex )
{
return new DataSet();
}//end catch
}

public static DataTable GetSheetDataFromExcelFile( string filename )
{
try
{
// get a datatable with the worksheet name(s)
OleDbConnection con = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
filename + ";Extended Properties=Excel 8.0" );

con.Open();

// get a datable of sheet names
DataTable dtNames = con.GetOleDbSchemaTable( System.Data.OleDb.OleDbSchemaGuid.Tables, new Object[]
{ null, null, null, "TABLE" } );

DataTable dtSheets = new DataTable();

// use the first sheet to get our data
if( dtNames.Rows.Count > 0 )
{
string tableName = dtNames.Rows[0]["TABLE_NAME"].ToString();
OleDbDataAdapter da = new OleDbDataAdapter( "select * from [" + tableName + "]", con );
da.Fill( dtSheets );
}

con.Close();

return dtSheets;

}//end try
catch( Exception ex )
{
return new DataTable();
}//end catch
}

Comments

Popular Posts