CODEWITHSUNDEEP
c#excel
tksy
tksy

Reputation: 3529

C#: How to access an Excel cell?

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

Upvotes: 20

Views: 116994

Answers (7)

cordellcp3
cordellcp3

Reputation: 3623

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

Try something like this 

objsheet(1).Cells[i,j].Value;

Upvotes: 3

Reddy
Reddy

Reputation: 11

You can use the below code; it's working fine for me:

newWorkbook = appExcel.Workbooks.Add();

Upvotes: 1

Andy
Andy

Reputation: 11

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";

Upvotes: 1

Anonymous Type
Anonymous Type

Reputation: 3061

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.

Upvotes: 1

Jimbo
Jimbo

Reputation: 113

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"

Upvotes: 1

GvS
GvS

Reputation: 52528

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#

Upvotes: 1

Rob Prouse
Rob Prouse

Reputation: 22657

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 );

Upvotes: 20

Related Questions