Home >Backend Development >C++ >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:
<code class="language-csharp">Excel.Range range = worksheet.get_Range("A1", Missing.Value);</code>
Accessing Cell Values: Text
vs. Value2
The Text
property returns the user-visible text, while Value2
provides the underlying value (without formatting or rounding):
<code class="language-csharp">string userVisibleText = range.Text; string rawValue = range.Value2;</code>
Iterating Through Cell Ranges
The foreach
loop efficiently iterates through a range of cells:
<code class="language-csharp">foreach (Excel.Range cell in range1) { string text = cell.Text; string value = cell.Value2; }</code>
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):
<code class="language-csharp">if (wkb != null) ExcelTools.OfficeUtil.ReleaseRCM(wkb); if (excel != null) ExcelTools.OfficeUtil.ReleaseRCM(excel);</code>
Complete Code Example
This C# code demonstrates accessing and displaying the value of cell A1:
<code class="language-csharp">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); } } } }</code>
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!