从 Excel API C# 获取单元格值 [英] Getting Cell Values from Excel API C#

问题描述

我有一个我为 Excel 制作的功能区和一个 cs 文件，用作加载项的核心.当在 CustomRibbon 上单击按钮时，我想解析其中一行的数据值.

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

    }

但这给了我一个错误

来自 HRESULT 的异常:0x800A03EC

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

它也给我一个错误.

推荐答案

Excel 表格中引用单元格的最低"适用索引是 [1,1]，它对应于 A 列，单元格 1.我明白了您试图引用超出表格范围的 0,0.

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.

尽管 C# 通常使用基于零的表索引，但 Excel Interop 的程序员似乎坚持了不同的方法.那或者他们想保持他们的约定一致，因为 Excel 表格中的第一行以 1 开头，而不是 0.我不是 Excel 爱好者，但这是我最好的猜测.

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.

根据 Siddharth 的要求，这里有一个将 DataGridView 控件的内容复制到 Excel 工作表的示例.请注意，这只是演示了基本功能，并不是一个完整的示例或必要的最佳实践:

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

注意索引之间的差异.此示例首先从 DataGridView 复制标题，然后偏移 Excel 工作表中的位置以复制其余数据，因为列标题不计为 DataGrid 中可索引的单元格.这可能也适用于数据表.

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.

这篇关于从 Excel API C# 获取单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！