ADO/ADO.NET - Reading calculated values from excel files

Asked By Nagaraj muthuchamy on 20-Apr-09 01:55 PM
Hi,

I have been trying this for two days.

Is it possible to load the calculated Excel fields in to dataset.

I am using Ado.Net and Oledb to read excel.

Thanks,
Nagaraj
Santhosh N replied to Nagaraj muthuchamy on 20-Apr-09 02:10 PM
I suppose you can read the calculated fields as normal columns and here is an example on how to read excel data into dataset...

http://www.codeproject.com/KB/vb/Excel_Connectivity.aspx
Nagaraj muthuchamy replied to Santhosh N on 20-Apr-09 02:29 PM
Thanks for your response.

I tried this method.

It's loading normal columns , but not loading the calculated fields .

Thanks,
Nagaraj

look sample code - Ravenet Rasaiyah replied to Nagaraj muthuchamy on 20-Apr-09 09:51 PM

re - Web Star replied to Nagaraj muthuchamy on 21-Apr-09 12:35 AM

u simply use this code if any column calculated in excel that also read and show in datagrid

private DataSet GetExcelWorkSheet(string pathName,string fileName,int workSheetNumber)
{  
  OleDbConnection ExcelConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+pathName+@"\"+fileName+";Extended Properties=Excel 8.0;");
  OleDbCommand ExcelCommand = new OleDbCommand();
  ExcelCommand.Connection = ExcelConnection;
  OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);

  ExcelConnection.Open();
  DataTable ExcelSheets = ExcelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,new object[] {null, null, null, "TABLE"});
 string SpreadSheetName = "["+ExcelSheets.Rows[workSheetNumber]["TABLE_NAME"].ToString()+"]";

  DataSet ExcelDataSet = new DataSet();
  ExcelCommand.CommandText = @"SELECT * FROM "+SpreadSheetName;
  ExcelAdapter.Fill(ExcelDataSet);
 
  ExcelConnection.Close();   
  return ExcelDataSet;
}