CODEWITHSUNDEEP
c#apiexcelvba
Ramie
Ramie

Reputation: 1201

Getting Cell Values from Excel API C#

I have a ribbon that i made for Excel along with a cs File to perform as the core of the add-in. When a button is clicked on the CustomRibbon I want to parse the data values for one of the rows. 

    private void button1_Click(object sender, RibbonControlEventArgs e)
    {
        Console.WriteLine(Globals.ThisAddIn.Application.Cells[0, 0]);

    }

But that gives me an error

Exception from HRESULT: 0x800A03EC

All i want to do is to be able to parse the data from cells on my worksheet and write data to the worksheet. Even on the startup of my program when i do:

        sheet.Cells[0,0]= "hello";

It gives me an error as well.

Upvotes: 2

Views: 6521

Answers (2)

B L
B L

Reputation: 1592

The "lowest" applicable index for referring to a cell in an Excel sheet is [1,1], which corresponds to Column A, Cell 1. I see you're trying to refer to 0,0 which is out of the bounds of the table.

Though C# typically utilizes zero based table indexing, it appears the programmers of Excel Interop adhered to a different methodology. That or they wanted to keep their conventions uniform, since the first row in an Excel table starts with 1, and not 0. I'm not an Excel buff, but that's my best guess.

Edit: Per Siddharth's request, here's an example that copies the contents of a DataGridView control to an Excel sheet. Note that this just demonstrates basic functionality, and isn't a complete example or necessarily best practice:

#using Microsoft.Office.Interop;

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;

xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(1);
xlWorkSheet = Excel.Worksheet xlWorkBook.ActiveSheet;

// Notice the index starting at [1,1] (row,column)
for (int i=1; i<DataGridView.Columns.Count+1; i++)
    xlWorkSheet.Cells[1, i] = DataGridView.Columns[i - 1].HeaderText;

for each(DataGridViewRow row in DataGridView.Rows)
{
    for each (DataGridViewColumn column in DataGridView.Columns)
    {
        xlWorkSheet.Cells[row.Index+2,column.Index+1] = DataGridView.Rows[row.Index].Cells[column.Index].Value;
    }
}

Notice the disparities between the indexing. This example first copies the headers from the DataGridView, then offsets the position in the Excel sheet to copy the rest of the data since the column headers don't count as index-able cells in the DataGrid. This would probably also work for DataTables.

Upvotes: 6

Porkbutts
Porkbutts

Reputation: 964

Try using the Value2 field of the cell.

sheet.Cells[1,1].Value2 = "hello";

Also in this line

Console.WriteLine(Globals.ThisAddIn.Application.Cells[1, 1]);

The "Cells" field is a property of a worksheet. You can't use it on the Excel application; you need to first create or open a Workbook and then create or use an existing Worksheet.

EDIT: Forgot that Excel ranges are 1-index and not 0-index.

Upvotes: -1

Related Questions