C#: How to access an Excel cell?

Question

I am trying to open an Excel file and populate its cells with data? I have done the following coding so far.

Currently I am at this stage with the following code but still I am getting errors:

Microsoft.Office.Interop.Excel.ApplicationClass appExcel =
                new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
    // is there already such a file ?
    if (System.IO.File.Exists("C:\\csharp\\errorreport1.xls"))
    {
        // then go and load this into excel
        Microsoft.Office.Interop.Excel.Workbooks.Open(
            "C:\\csharp\\errorreport1.xls", true, false, 
            Missing.Value, Missing.Value, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value, Missing.Value, Missing.Value, 
            Missing.Value, Missing.Value, Missing.Value, Missing.Value);
    }
    else
    {
        // if not go and create a workbook:
        newWorkbook = appExcel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
        Microsoft.Office.Interop.Excel._Worksheet excelWorksheet = 
            (Microsoft.Office.Interop.Excel._Worksheet)
                newWorkBook.Worksheets.get_Item(1);
    } 
i++;
j = 1;

j++;
objsheet.Cells(i, j).Value = "Tabelle: " + rs.Fields["Table_Name"];
j++;
objsheet.Cells(i, j).Value = "kombinationsschluessel:FALL " 
                                + rs3.Fields[1].Value;
j++;
objsheet.Cells(i, j).Value = "Null Value: ";
j++;
objsheet.Cells(i, j).Value = "Updated with 888";

These are the top 2 errors I am getting:

Error 1 An object reference is required for the nonstatic field, method, or
        property 'Microsoft.Office.Interop.Excel.Workbooks.Open(string, object,
        object, object, object, object, object, object, object, object, object,
        object, object, object, object)'

Error 2 The name 'newWorkbook' does not exist in the current context

Answer 1

If you are trying to automate Excel, you probably shouldn't be opening a Word document and using the Word automation ;)

Check this out, it should get you started,

http://www.codeproject.com/KB/office/package.aspx

And here is some code. It is taken from some of my code and has a lot of stuff deleted, so it doesn't do anything and may not compile or work exactly, but it should get you going. It is oriented toward reading, but should point you in the right direction.

Microsoft.Office.Interop.Excel.Worksheet sheet = newWorkbook.ActiveSheet;

if ( sheet != null )
{
    Microsoft.Office.Interop.Excel.Range range = sheet.UsedRange;
    if ( range != null )
    {
        int nRows = usedRange.Rows.Count;
        int nCols = usedRange.Columns.Count;
        foreach ( Microsoft.Office.Interop.Excel.Range row in usedRange.Rows )
        {
            string value = row.Cells[0].FormattedValue as string;
        }
    }
 }

You can also do

Microsoft.Office.Interop.Excel.Sheets sheets = newWorkbook.ExcelSheets;

if ( sheets != null )
{
     foreach ( Microsoft.Office.Interop.Excel.Worksheet sheet in sheets )
     {
          // Do Stuff
     }
}

And if you need to insert rows/columns

// Inserts a new row at the beginning of the sheet
Microsoft.Office.Interop.Excel.Range a1 = sheet.get_Range( "A1", Type.Missing );
a1.EntireRow.Insert( Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Type.Missing );

Answer 2

I think, that you have to declare the associated sheet!

Try something like this

objsheet(1).Cells(i,j).Value ...

Answer 3

MSDN help has a good article on this. Search for "Creating Excel file programmatically in ASP.NET".

Answer 4

"Office Open XML Formats: Retrieving Excel 2007 Cell Values" explains how to retrieve Excel cell values.

Answer 5

How I work to automate Office / Excel:

1. Record a macro, this will generate a VBA template
2. Edit the VBA template so it will match my needs
3. Convert to VB.Net (A small step for men)
4. Leave it in VB.Net, Much more easy as doing it using C#

Answer 6

I've been using this guy's library for my Excel interop with pretty good results:

Yogesh Jagota (A Very Easy to Use Excel XML Import-Export Library)

Answer 7

Try:

Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRng;

oXL = new Excel.Application();
oXL.Visible = true;
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));

oSheet = (Excel._Worksheet)oWB.Worksheets;
oSheet.Activate();

oSheet.Cells[3, 9] = "Some Text"

Answer 8

Simple.

To open a workbook. Use xlapp.workbooks.Open()

where you have previously declared and instanitated xlapp as so.. Excel.Application xlapp = new Excel.Applicaton();

parameters are correct.

Next make sure you use the property Value2 when assigning a value to the cell using either the cells property or the range object.

Answer 9

This works fine for me

       Excel.Application oXL = null;
        Excel._Workbook oWB = null;
        Excel._Worksheet oSheet = null;

        try
        {
            oXL = new Excel.Application();
            string path = @"C:\Templates\NCRepTemplate.xlt";
            oWB = oXL.Workbooks.Open(path, 0, false, 5, "", "",
                false, Excel.XlPlatform.xlWindows, "", true, false,
                0, true, false, false);

            oSheet = (Excel._Worksheet)oWB.ActiveSheet;
            oSheet.Cells[2, 2] = "Text";