How to Efficiently Read Data from Excel Files Using C#?

Mastering Excel Data Extraction with C#

Efficiently handling Excel files in C# requires a clear understanding of named ranges. Instead of relying on array indexing, leverage the get_Range() method for precise cell selection.

For example, to access cell A1:

<code class="language-csharp">Excel.Worksheet sheet = workbook.Sheets["Sheet1"] as Excel.Worksheet;
Excel.Range range = sheet.get_Range("A1", Missing.Value);</code>

range now represents cell A1. Access its contents using:

• range.Text: Displays the cell's formatted text.
• range.Value2: Retrieves the underlying cell value (unformatted).

Iterating through a cell range:

<code class="language-csharp">Excel.Range range = sheet.get_Range("A1:A5", Missing.Value);

if (range != null)
{
    foreach (Excel.Range r in range)
    {
        string userText = r.Text;
        object cellValue = r.Value2; //Note: object type for flexibility
    }
}</code>

Crucially, use Value2 instead of Value to avoid potential C# limitations.

Essential Cleanup

Prevent memory leaks by releasing COM objects after use:

<code class="language-csharp">if (wkb != null)
    ExcelTools.OfficeUtil.ReleaseRCM(wkb);

if (excel != null)
    ExcelTools.OfficeUtil.ReleaseRCM(excel);</code>

ReleaseRCM is a custom function for releasing COM resources.

Illustrative Code Snippet

<code class="language-csharp">using ExcelTools = Ms.Office; // Assuming a custom namespace
using Excel = Microsoft.Office.Interop.Excel;

...

Excel.Workbook wkb = ExcelTools.OfficeUtil.OpenBook(excel, file);

...

Excel.Worksheet sheet = wkb.Sheets["Data"] as Excel.Worksheet;

Excel.Range range = sheet.get_Range("A1", Missing.Value);

string cellA1Text = range.Text.ToString();

...</code>

Key Considerations

• Hide the Excel application during processing by setting object visibility to false.
• Explore OleDb as a viable alternative for Excel data access.
• This example uses C#'s Type.Missing for optional parameters.

This enhanced approach ensures efficient and reliable Excel data handling within your C# applications.

The above is the detailed content of How to Efficiently Read Data from Excel Files Using C#?. For more information, please follow other related articles on the PHP Chinese website!