reading excel files from c#

[Juan Ramon Martinez]

Hello

Im trying to read an excel file from a c# program. I need to read the cells
and do some processing with the values.
As far as I've learned the best way to go is using the Offic XP Interop
Assemblies. Ive downloaded them, but I have not found any reference
documentation. Any pointers will be greatly appreciated

Ive already succeeded in reading the file and get the appropiate sheet,.
When I read the cells however always get a System.__ComObject thats not very
useful. I dont have a way to know the type of the object or how to get any
meaningufl data from it.

here's a code snippet

//<snippet>

ApplicationClass xlApp=new ApplicationClass();
if (xlApp == null){
throw new ApplicationException("No se pudo ejecutar excel");
}
object noVal = System.Reflection.Missing.Value;
Workbook
xlBook=xlApp.Workbooks.Open(pFileName,noVal,noVal,noVal,noVal,noVal,noVal,no
Val,noVal,noVal,noVal,noVal,noVal,noVal,noVal);
Worksheet sheet=(Worksheet)xlBook.Worksheets.get_Item(pWorkSheetName);
if (result == null){
throw new ApplicationException("No se pudo encontrar hoja");
}
xlApp.Visible=false;
Range rows=sheet.Cells;
int rowCount=1;
int colCount=1;
Object cellValue=null;
while (rows.get_Item(rowCount,colCount)!=null && rowCount <=10){
for (colCount=1;colCount <=10;colCount++){
cellValue=rows.get_Item(rowCount,colCount);
}
rowCount++;
}
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

//</snippet>

Thanks!

JR

[Nicholas Paldino [.NET/C# MVP]]

Juan,

You might want to try the Microsoft Excel Object Model reference,
located at (watch for line wrap):

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl10/htm
l/xltocObjectModelApplication.asp?frame=true

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- casp...@caspershouse.com

"Juan Ramon Martinez" <jrmar...@agroasemex.gob.mx> wrote in message
news:OMc1MRj$CHA....@TK2MSFTNGP11.phx.gbl...

[Ryan Trudelle-Schwarz]

"Juan Ramon Martinez" <jrmar...@agroasemex.gob.mx> wrote
>

> Im trying to read an excel file from a c# program. I need to read the
cells
> and do some processing with the values.
> As far as I've learned the best way to go is using the Offic XP Interop
> Assemblies. Ive downloaded them, but I have not found any reference
> documentation. Any pointers will be greatly appreciated

Personally I find it much easier to just query the excel file via the OLEDB
provider built into ado.
I posted some sample code in another thread here earlier if you need it.

[Gordon Clark]

Ryan:

I have tried searching for your message on reading Excel files via OLEDB
provider built in ado. I could not locate it. Could you give me a more
definitive reference?

Gordon

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ryan Trudelle-Schwarz]

"Gordon Clark" <anon...@devdex.com> wrote

>
> I have tried searching for your message on reading Excel files via OLEDB
> provider built in ado. I could not locate it. Could you give me a more
> definitive reference?

Well, here's what i posted on the other topic (I was mistaken, it was in
another ng):

You can query the names of the sheets using something like:

~~~~~~~~~~~~~~~~~~~~~
System.Data.OleDb.OleDbConnection connection = new
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" + Server.MapPath("/temp/" + filename.Value) + ";Extended
Properties=Excel 8.0;");
System.Data.DataTable lookup;
connection.Open();
lookup =
connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,
null);
connection.Close();
connection.Dispose();

System.String table;
for(System.Int32 i = 0; i < lookup.Rows.Count; i++)
{
table = (System.String)lookup.Rows[i]["TABLE_NAME"];
if(table.Length > 2 && table.StartsWith("$") && table.EndsWith("$"))
table = table.Substring(1, table.Length - 2);
tables.Items.Add(table);
}
~~~~~~~~~~~~~~~~~~~~~

Then knowing the sheet name (table below), you can query the data from the
sheet directly:

~~~~~~~~~~~~~~~~~~~~~
System.Data.OleDb.OleDbConnection connection = new
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" + Server.MapPath("/temp/" + filename.Value) + ";Extended
Properties=Excel 8.0;");
System.Data.OleDb.OleDbCommand command = new
System.Data.OleDb.OleDbCommand();
System.Data.OleDb.OleDbDataReader reader;
command.Connection = connection;
command.CommandText = "Select * From [" + table + "] Where false = true";
connection.Open();
reader = command.ExecuteReader();
~~~~~~~~~~~~~~~~~~~~~