How to Efficiently Read and Manipulate Excel Data Using C#?

C# and Excel Data: A Practical Guide

This article details effective methods for reading and manipulating Excel data using C#. The example provided illustrates opening an Excel file, copying its contents, and searching for email addresses. However, directly accessing and processing cell values requires understanding Excel's unique data structure.

Excel's Data Structure: Named Ranges

Unlike typical arrays, Excel uses named ranges to store data. Accessing a specific cell necessitates referencing its range name (e.g., "A1" for the top-left cell).

Leveraging the Range Object

The C# Excel.Range object represents a cell or a group of cells within an Excel worksheet. The get_Range method of the Excel.Worksheet object retrieves a specific Range object. For example:

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

Accessing Cell Values: Text vs. Value2

The Text property returns the user-visible text, while Value2 provides the underlying value (without formatting or rounding):

string userVisibleText = range.Text;
string rawValue = range.Value2;

Iterating Through Cell Ranges

The foreach loop efficiently iterates through a range of cells:

foreach (Excel.Range cell in range1)
{
    string text = cell.Text;
    string value = cell.Value2;
}

Resource Management: Proper Cleanup

For efficient resource management, release Excel objects in reverse order of creation. Since Excel.Application and Excel.Workbook don't implement IDisposable, use a custom release function (like ReleaseRCM in the example):

if (wkb != null)
    ExcelTools.OfficeUtil.ReleaseRCM(wkb);

if (excel != null)
    ExcelTools.OfficeUtil.ReleaseRCM(excel);

Complete Code Example

This C# code demonstrates accessing and displaying the value of cell A1:

using System;
using ExcelTools = Ms.Office;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            string filePath = @"C:\Users\Chris\Desktop\TestSheet.xls";
            Console.WriteLine(filePath);

            Excel.Application excelApp = null;
            Excel.Workbook workbook = null;

            try
            {
                excelApp = new Excel.Application();
                workbook = ExcelTools.OfficeUtil.OpenBook(excelApp, filePath, false, false, true);

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

                if (sheet != null)
                {
                    Excel.Range cell = sheet.get_Range("A1", Missing.Value);
                    string a1Value = cell != null ? cell.Text.ToString() : "";
                    Console.WriteLine("A1 value: {0}", a1Value);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (workbook != null)
                    ExcelTools.OfficeUtil.ReleaseRCM(workbook);

                if (excelApp != null)
                    ExcelTools.OfficeUtil.ReleaseRCM(excelApp);
            }
        }
    }
}

Conclusion

Successfully working with Excel data in C# hinges on understanding Excel's range-based structure. Using the Excel.Range object and its properties, along with careful resource management, enables efficient and reliable data access and manipulation.

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