如何将Excel数据填充至已创建的二维数组？（C#环境）

It sounds like you've already got your Excel data loaded (using your System.Data references), which is half the battle! Converting that data into a 2D array is straightforward and efficient—even for 2000+ rows, this process will be quick. Here's how to do it:

Step 1: Confirm Your Data is in a DataTable #

First, make sure your Excel data is stored in a DataTable—this is the standard output when using tools like OleDbDataAdapter to read Excel files with System.Data. Let's call this table excelData for our examples.

Step 2: Create and Populate the 2D Array #

You’ll loop through each row and column of the DataTable to fill the array. This approach is lightweight and handles large datasets easily.

Generic Object Array (For Mixed Data Types) #

If your Excel has a mix of numbers, text, or other data types, use an object[,] array to preserve all values:

// Initialize array with matching dimensions to your DataTable
object[,] excelArray = new object[excelData.Rows.Count, excelData.Columns.Count];

// Populate the array
for (int row = 0; row < excelData.Rows.Count; row++)
{
    for (int col = 0; col < excelData.Columns.Count; col++)
    {
        excelArray[row, col] = excelData.Rows[row][col];
    }
}

Strongly-Typed Array (For Numeric Formula Data) #

Since you’re using this array for formulas, chances are your data is numerical. A strongly-typed array like double[,] will give you better performance and type safety:

double[,] numericExcelArray = new double[excelData.Rows.Count, excelData.Columns.Count];

for (int row = 0; row < excelData.Rows.Count; row++)
{
    for (int col = 0; col < excelData.Columns.Count; col++)
    {
        // Add error handling for non-numeric values
        if (double.TryParse(excelData.Rows[row][col]?.ToString(), out double value))
        {
            numericExcelArray[row, col] = value;
        }
        else
        {
            // Handle invalid entries (e.g., set to 0, log an error)
            numericExcelArray[row, col] = 0;
        }
    }
}

Quick Bonus: If You Haven’t Loaded the Excel Data Yet #

In case you’re still refining the data loading step, here’s a compatible snippet using OleDb:

private DataTable LoadExcelData(string filePath)
{
    // Connection string for .xlsx files (requires Microsoft Access Database Engine)
    string connString = $@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Extended Properties=""Excel 12.0 Xml;HDR=YES;""";
    
    using (OleDbConnection conn = new OleDbConnection(connString))
    {
        conn.Open();
        // Get the first sheet name from the Excel file
        DataTable schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string sheetName = schema.Rows[0]["TABLE_NAME"].ToString();
        
        // Load all sheet data into a DataTable
        string query = $"SELECT * FROM [{sheetName}]";
        using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn))
        {
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            return dataTable;
        }
    }
}

Using the Array in Your Formulas #

Once you have the array, accessing elements is simple with array[rowIndex, columnIndex]. For example, to calculate the average of the second column:

double columnSum = 0;
int targetColumn = 1; // Adjust to your desired column index

for (int row = 0; row < numericExcelArray.GetLength(0); row++)
{
    columnSum += numericExcelArray[row, targetColumn];
}

double average = columnSum / numericExcelArray.GetLength(0);

内容的提问来源于stack exchange，提问作者Dan King