如何用Open XML结合Selenium C#按列名和行号获取Excel单元格值

Got it, let's fix this for you. You're dealing with slow Interop performance and annoying dependency errors from Excel Data Reader—Open XML is definitely the right call here. Below is a complete, optimized implementation of the ReadExcelVal function you need, plus setup steps to get it working smoothly with your Selenium C# scripts.

Step 1: Install the Open XML SDK #

First, add the Open XML NuGet package to your project. Run this in the Package Manager Console:

Install-Package DocumentFormat.OpenXml

Or use the NuGet Package Manager UI to search for "DocumentFormat.OpenXml" and install the latest stable version.

Step 2: Implement the Excel Reader Class #

Here's a ready-to-use static class that includes your requested ReadExcelVal function. It handles column name lookup, different cell types (strings, numbers, dates), and avoids the slow Interop overhead:

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Linq;

public static class ExcelDataReader
{
    // Update this path to your actual Excel file location
    private static readonly string _excelFilePath = @"C:\TestData\YourTestData.xlsx";
    private static Dictionary<string, int> _columnIndexMap;
    private static SheetData _cachedSheetData;
    private static WorkbookPart _cachedWorkbookPart;

    // Initialize once to cache column mappings and sheet data for faster reads
    static ExcelDataReader()
    {
        InitializeReader();
    }

    private static void InitializeReader()
    {
        using (var document = SpreadsheetDocument.Open(_excelFilePath, false))
        {
            _cachedWorkbookPart = document.WorkbookPart;
            var sheet = _cachedWorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
            var worksheetPart = _cachedWorkbookPart.GetPartById(sheet.Id) as WorksheetPart;
            _cachedSheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
            
            // Build map of column names to their index (uses first row as header)
            _columnIndexMap = BuildColumnIndexMap(_cachedSheetData);
        }
    }

    private static Dictionary<string, int> BuildColumnIndexMap(SheetData sheetData)
    {
        var columnMap = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
        var headerRow = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex == 1);

        if (headerRow == null)
            throw new InvalidOperationException("Excel file has no header row (expected row 1)");

        foreach (var cell in headerRow.Elements<Cell>())
        {
            var columnName = GetCellValue(cell);
            var columnIndex = GetColumnNumberFromCellReference(cell.CellReference);
            columnMap[columnName] = columnIndex;
        }

        return columnMap;
    }

    private static int GetColumnNumberFromCellReference(string cellReference)
    {
        // Convert cell reference like "A1" to column number (A=1, B=2, etc.)
        var columnLetters = new string(cellReference.TakeWhile(char.IsLetter).ToArray());
        int columnNumber = 0;

        foreach (var c in columnLetters)
        {
            columnNumber = columnNumber * 26 + (c - 'A' + 1);
        }

        return columnNumber;
    }

    private static string GetCellValue(Cell cell)
    {
        if (cell == null) return string.Empty;

        var rawValue = cell.CellValue?.InnerText ?? string.Empty;

        // Handle shared strings (common for text values)
        if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
        {
            var stringTable = _cachedWorkbookPart.SharedStringTablePart?.SharedStringTable;
            if (stringTable != null && int.TryParse(rawValue, out int stringIndex))
            {
                rawValue = stringTable.Elements<SharedStringItem>().ElementAt(stringIndex).InnerText;
            }
        }

        // Handle dates (Excel stores dates as numeric values)
        if (cell.DataType != null && cell.DataType.Value == CellValues.Number)
        {
            if (IsDateCell(cell))
            {
                if (double.TryParse(rawValue, out double dateValue))
                {
                    return DateTime.FromOADate(dateValue).ToString("yyyy-MM-dd"); // Adjust format as needed
                }
            }
        }

        return rawValue;
    }

    private static bool IsDateCell(Cell cell)
    {
        var stylesPart = _cachedWorkbookPart.WorkbookStylesPart;
        if (stylesPart == null || cell.StyleIndex == null) return false;

        var cellFormat = stylesPart.Stylesheet.CellFormats.Elements<CellFormat>().ElementAt((int)cell.StyleIndex.Value);
        var numberFormat = stylesPart.Stylesheet.NumberingFormats.Elements<NumberingFormat>()
            .FirstOrDefault(nf => nf.NumberFormatId == cellFormat.NumberFormatId);

        return numberFormat != null && (numberFormat.FormatCode.Contains("yyyy") || numberFormat.FormatCode.Contains("mm") || numberFormat.FormatCode.Contains("dd"));
    }

    // Your requested function - returns value by row number and column name
    public static string ReadExcelVal(int rowNum, string colName)
    {
        // Validate column name exists
        if (!_columnIndexMap.TryGetValue(colName, out int columnIndex))
        {
            throw new ArgumentException($"Column '{colName}' not found in Excel header");
        }

        // Get the target row
        var targetRow = _cachedSheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex == rowNum);
        if (targetRow == null)
        {
            throw new ArgumentException($"Row {rowNum} does not exist in the Excel file");
        }

        // Find the cell in the target row and column
        var targetCell = targetRow.Elements<Cell>()
            .FirstOrDefault(c => GetColumnNumberFromCellReference(c.CellReference) == columnIndex);

        return GetCellValue(targetCell);
    }
}

Key Features & Notes #

• Performance: Caches the sheet data and column mappings on initialization, so repeated reads are fast (no re-parsing the entire Excel file every time).
• Case Insensitive: Column name lookup ignores case (so "Username" and "username" both work).
• Handles Multiple Data Types: Automatically converts shared strings, numbers, and dates to readable strings.
• Error Handling: Throws clear exceptions if the column name or row number doesn't exist, making debugging easier.

How to Use It in Your Selenium Script #

Call the function directly when you need to populate a web element:

// Example: Read username from row 2, column "Username"
string username = ExcelDataReader.ReadExcelVal(2, "Username");
driver.FindElement(By.Id("username-input")).SendKeys(username);

// Example: Read password from row 2, column "Password"
string password = ExcelDataReader.ReadExcelVal(2, "Password");
driver.FindElement(By.Id("password-input")).SendKeys(password);

Customization Tips #

• Change Header Row: If your header isn't in row 1, update the BuildColumnIndexMap method where it checks r.RowIndex == 1.
• Use Specific Worksheet: To target a sheet by name instead of the first sheet, replace GetFirstChild<Sheet>() with:

  var sheet = _cachedWorkbookPart.Workbook.Sheets.Cast<Sheet>().First(s => s.Name == "TestDataSheet");
  

• Adjust Date Format: Modify the ToString("yyyy-MM-dd") in the GetCellValue method to match your preferred date format.

This implementation avoids the slow Interop calls and the dependency conflicts you had with Excel Data Reader, making it perfect for your data-driven Selenium framework.

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