How to read an Excel file 1

[Henk1]

I need to open an excel file from a specific folder, and read a value from the A1 field.

Does anyone know what I need to do to do this? I have never worked with office objects before, and just need a starting point.

I am using Office 2003 and MS Visual studio .Net Enterprise developer edition.

I have the help files, but do not really know where to start, as they are all like greek :-(

Thanks for the replies.

 

[SHelton]

The following works for Excel 97 (and XP with a couple of extra parameters in the Workbook.Open method).

First add a reference to the Microsoft Excel 8.0 Object Library (or equivalent version).

Then try the following:

private void button1_Click(object sender, System.EventArgs e)
{
	object oNull = Type.Missing;

	Excel.Application xl = new Excel.Application();
			
	Excel._Workbook wb = (Excel._Workbook)xl.Workbooks.Open(@"C:\Test.xls", oNull, oNull, oNull, oNull, oNull, oNull, oNull, oNull, oNull, oNull, oNull, oNull);

	Excel._Worksheet ws = (Excel._Worksheet)wb.Worksheets.get_Item(1);

    Excel.Range r = (Excel.Range)ws.get_Range("A1", oNull);

	MessageBox.Show(r.Value.ToString());

	wb.Close(false, oNull, oNull);

	wb = null;

	xl.Quit();

	xl = null;

}

I found it difficult to find information on this, so it's mainly trial and error.

Note that I haven't put any error handling in - if the procedure fails halfway through you are left with an instance of Excel running in the background.

 

[Henk1]

Thanks!

Will try this just now. Can you maybe tell me what the Type.Missing does?

Thanks again for the help.

 

[SHelton]

Parameters in Excel methods must have a value, so if you are not explicitly setting a value you have to pass in something. Passing null results in an exception, so Type.Missing tells Excel to ignore the parameter.

 

[SHelton]

If using Excel 2003, you may need to change the

r.Value

to

r.get_Value

.

Check out this Knowledge Base article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;824004&Product=vcSnet

 

[Henk1]

Ahhh, thanks. I sort of figured out the r.value thing. I used r.value2.tostring(), which worked.

Another question. When reading from merged cells, do you still only use the on cell value, eg. B18, or must you specify the range of the merge, ie. ("B18","F19&quot?

I am getting an error when just referencing the main cell when I have a merged cell that is empty. Any thoughts on how I can fix this?

When adding in the end of the merge cell, I get a system.object[,] returned, in stead of an empty string.

When, having ("B19", oNull), I get an error saying:

"An unhandled exception of type 'System.NullReferenceException' occurred in Import Excel Surveys.exe

Additional information: Object reference not set to an instance of an object."

Another problem is that it is not ending the new Excel.exe threads that it is creating when I loop through my folder with files in. This would not be a problem, did I have only 10 files, but I will be importing probably 500 files. And my desktop would not really cope with that.

 

[Henk1]

Sorry, ANOTHER question...

When using the r.get_value, in stead of the r.value, what must I pass it?

r.get_Value(Object RangeValueDataType)

What would this be?

 

[SHelton]

Merged cells only need the first cell when getting the Range (e.g. B18).

Empty cells can be compared to null to see if they are empty:

object o = r.Value;

if (o != null) MessageBox.Show(o.ToString());

I'm not sure why the Excel instance isn't ending, it may be an Excel 2003 issue. I don't have any problems with Excel 97 (except being 3 versions behind the times!).

The RangeValueDataType is an internal Excel type. Use:

r.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault);

 

[SHelton]

Re the Excel instance not ending, have a look at:

http://support.microsoft.com/default.aspx?scid=kb;en-us;317109&Product=vcSnet

 

[obislavu]

Another way:
int r=1;
string vCellID="A" + r.ToString();
object m_MissingValue = System.Reflection.Missing.Value;
Excel.Range m_Range = m_WorkSheet.get_Range(vCellID,m_MissingValue);
object vCellValue=m_Range.get_Value ((m_MissingValue);
// iterate through Range object
m_Range = m_WorkSheet.get_Range(vCellID,m_MissingValue);
while (vCellValue!=null)
{
// process vCellValue here
m_Range=m_Range.Next;
vCellValue=m_Range.get_Value(m_MissingValue);
}
-obislavu-

 

[JCruz063]

There's something called Microsoft Visual Studio Tools for Office (VSTO) which allows you to manipulate the the Microsoft Office programs from Visual Studio .NET. You can view a live presentation on it at:

http://support.microsoft.com/default.aspx?kbid=834499

If you have problems viewing the presentation (perhaps because of a slow connection), you can download the transcript for it, which is word by word what they say on the presentation. The transcript for this one, however, will be availale in about two weeks from now...

JC

We don't see things as they are; we see them as we are. - Anais Nin

 

[Henk1]

Wow, thanks for all the answers.

However, I still do not get the comments right. I still get problems, even though I do check for null values. I do not get an error any more, but I get "system.object[,]" as the value, where there should in fact be a comment in.

When I define oComment as object with value r.Value2, I get oComment as a System.Array object.

When I add a watch to oComment, I get an array with the first instance [1,1], as having the value that I want. However, I cannot use this index on oComment, as it complains that oComment does not have any indexes. How can I get this value on the [1,1] index?

 

[Henk1]

no matter. I got it working.

Thanks again for everyone helping with this.