Since a few days, I'm working in Visual Studio 2008 and VSTO to update an Excel application.
As I have a mighty hard time to understand everything and find some information in the very lacunar documentation (See in MSDN), I have decided to so a serie of post, related to what I learn and discover in this domain.
How to access to your sheets ?
- By simply using the "this" keyword when working in a sheet
- By using the "Globals" class
- Globals.Sheet1
- Globals.Sheet2
How to set a value in a single cell ?
- Select the cell you want and set a value
Globals.Sheet1.Cells[10, 10] = "Hello world";
How to set the same value in mutliple cells ?
-
Select a range...
- Example 1
Excel.Range range = Globals.Sheet1.Range["D10", "E11"];
- Example 2
Excel.Range cell1 = (Excel.Range)Globals.Sheet1.Cells[10, 10];
Excel.Range cell2 = (Excel.Range)Globals.Sheet1.Cells[12, 12];
Excel.Range range = Globals.Sheet1.Range[cell1, cell2];
- ...and set
range.Value2 = "Same value";
How to set a formula in a cell ?
Note that to set it in mutliple cell, you can first get a range as specified above.
Globals.Sheet1.Range["A15", "A15"].Formula = "=15";
Globals.Sheet1.Range["A16", "A16"].Formula = "=15*2";
Globals.Sheet1.Range["A17", "A17"].Formula = "=R[-2]C+R[-1]C";
How works the last example ? It's pretty simple in fact :
-
R means "the same row"
-
R[3] means the current row + 3 (if we are at row 10, so it will be row 13)
-
R[-2] means the current row - 2 (if we are at row 10, so it will be row 8)
-
C means "the current column"
-
C[3] means the current column + 3 (if we are at column 10, so it will be column 13)
-
C[-2] means the current column - 2 (if we are at column 10, so it will be column 8)