Get Value in Excel Spreadsheet From C#

[Guest]

I am opening an Excel Workbook from C# (VS2005) and I need to get a value
from a Spreadsheet before closing the C# application. Does anyone know if
this can be done? Here is the code to open the Workbook:
//Open the Excel Spreadsheet
Microsoft.Office.Interop.Excel.Application excelApp = new
Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = true;
string workbookPath = @"\\server\path$\AssignmentTest.xls";
Microsoft.Office.Interop.Excel.Workbook excelWorkbook =
excelApp.Workbooks.Open(workbookPath,
0, false, 5, "password", "", true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);

 

[DeveloperX]

I am opening an Excel Workbook from C# (VS2005) and I need to get a value
from a Spreadsheet before closing the C# application. Does anyone know if
this can be done? Here is the code to open the Workbook:
//Open the Excel Spreadsheet
Microsoft.Office.Interop.Excel.Application excelApp = new
Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = true;
string workbookPath = @"\\server\path$\AssignmentTest.xls";
Microsoft.Office.Interop.Excel.Workbook excelWorkbook =
excelApp.Workbooks.Open(workbookPath,
0, false, 5, "password", "", true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);

Worksheet w = (Worksheet)excelApp.ActiveSheet;
Range r = (Range)w.Cells[Row, Column];
string s = (string) r.Text

where Row and Column are int's

Worksheet w = (Worksheet)excelApp.ActiveSheet;
Range r = (Range)w.Cells.get_Range(aRange, _missing);
string s = (string) r.Text

where aRange could be "A1" for example.

you've also got
r.Value2 which returns the range's value
r.Formula for the formula obviously

and less obvious r.set_Value to set the value.

 

[Guest]

Thanks for the response. My Workbook has numerous sheets and I need to get
the values in column A of the "Security" sheet. I can set a worksheet
variable like this:

Worksheet w = (Worksheet)excelApp.Sheets["Security"];

Now that I have 'w' I can't seem to get the right syntax to read any values
in column A.
--
Robert Hill

I am opening an Excel Workbook from C# (VS2005) and I need to get a value
from a Spreadsheet before closing the C# application. Does anyone know if
this can be done? Here is the code to open the Workbook:
//Open the Excel Spreadsheet
Microsoft.Office.Interop.Excel.Application excelApp = new
Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = true;
string workbookPath = @"\\server\path$\AssignmentTest.xls";
Microsoft.Office.Interop.Excel.Workbook excelWorkbook =
excelApp.Workbooks.Open(workbookPath,
0, false, 5, "password", "", true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);

Worksheet w = (Worksheet)excelApp.ActiveSheet;
Range r = (Range)w.Cells[Row, Column];
string s = (string) r.Text

where Row and Column are int's

Worksheet w = (Worksheet)excelApp.ActiveSheet;
Range r = (Range)w.Cells.get_Range(aRange, _missing);
string s = (string) r.Text

where aRange could be "A1" for example.

you've also got
r.Value2 which returns the range's value
r.Formula for the formula obviously

and less obvious r.set_Value to set the value.

 

[DeveloperX]

Thanks for the response. My Workbook has numerous sheets and I need to get
the values in column A of the "Security" sheet. I can set a worksheet
variable like this:

Worksheet w = (Worksheet)excelApp.Sheets["Security"];

Now that I have 'w' I can't seem to get the right syntax to read any values
in column A.
--
Robert Hill

Worksheet w = (Worksheet)excelApp.ActiveSheet;
Range r = (Range)w.Cells[Row, Column];
string s = (string) r.Text

where Row and Column are int's

Worksheet w = (Worksheet)excelApp.ActiveSheet;
Range r = (Range)w.Cells.get_Range(aRange, _missing);
string s = (string) r.Text

where aRange could be "A1" for example.

you've also got
r.Value2 which returns the range's value
r.Formula for the formula obviously

and less obvious r.set_Value to set the value.- Hide quoted text -

- Show quoted text -

Well column A is column one, so the easiest way would be to do:

for(row = 1; row< 10; row++) // loop through first 10 rows excel is
mainly 1 based.
{
Range r = (Range)w.Cells[row, 1];
string s = (string) r.Text;
}

Alternatively you can

do the Range r = (Range)w.Cells.get_Range("A:A", _missing);

method where _missing is
private static object _missing = System.Reflection.Missing.Value;
in my code.
You can then iterate through all the cells in the returned range.

The easiest way to get your head around it is to go into excel and
record a macro. 9 times out of 10 it's pretty much a case of using the
same methods, although take care with some of the setters and getters
as mentioned above.

Hope that helps.

 

[Guest]

Thanks so much. This is exactly what I needed.
--
Robert Hill

Thanks for the response. My Workbook has numerous sheets and I need to get
the values in column A of the "Security" sheet. I can set a worksheet
variable like this:

Worksheet w = (Worksheet)excelApp.Sheets["Security"];

Now that I have 'w' I can't seem to get the right syntax to read any values
in column A.
--
Robert Hill

I am opening an Excel Workbook from C# (VS2005) and I need to get a value
from a Spreadsheet before closing the C# application. Does anyone know if
this can be done? Here is the code to open the Workbook:
//Open the Excel Spreadsheet
Microsoft.Office.Interop.Excel.Application excelApp = new
Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = true;
string workbookPath = @"\\server\path$\AssignmentTest.xls";
Microsoft.Office.Interop.Excel.Workbook excelWorkbook =
excelApp.Workbooks.Open(workbookPath,
0, false, 5, "password", "", true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);

Worksheet w = (Worksheet)excelApp.ActiveSheet;
Range r = (Range)w.Cells[Row, Column];
string s = (string) r.Text

where Row and Column are int's

Worksheet w = (Worksheet)excelApp.ActiveSheet;
Range r = (Range)w.Cells.get_Range(aRange, _missing);
string s = (string) r.Text

where aRange could be "A1" for example.

you've also got
r.Value2 which returns the range's value
r.Formula for the formula obviously

and less obvious r.set_Value to set the value.- Hide quoted text -

- Show quoted text -

Well column A is column one, so the easiest way would be to do:

for(row = 1; row< 10; row++) // loop through first 10 rows excel is
mainly 1 based.
{
Range r = (Range)w.Cells[row, 1];
string s = (string) r.Text;
}

Alternatively you can

do the Range r = (Range)w.Cells.get_Range("A:A", _missing);

method where _missing is
private static object _missing = System.Reflection.Missing.Value;
in my code.
You can then iterate through all the cells in the returned range.

The easiest way to get your head around it is to go into excel and
record a macro. 9 times out of 10 it's pretty much a case of using the
same methods, although take care with some of the setters and getters
as mentioned above.

Hope that helps.