5 Ways to Programmatically Fill Excel Sheets in C

Excel is one of the most widely used tools for data analysis, reporting, and general office work. In many business environments, the need to automate repetitive tasks like data entry, analysis, and reporting becomes a necessity. C# is an excellent language for creating robust and scalable applications for Excel automation, thanks to its integration with Office Interop libraries and third-party libraries like EPPlus and OpenXML SDK. Here are five detailed methods to programmatically fill Excel sheets using C#:

1. Using Microsoft Office Interop Excel

The Microsoft Office Interop Excel library allows you to control Excel through COM automation. Here's how you can get started:

• Add Reference: Add the Microsoft.Office.Interop.Excel reference to your project.
• Create Application Object: Use Application excelApp = new Application();
• Open or Create Workbook: You can either open an existing workbook or create a new one.
• Manipulate Data: Use methods like Worksheet.Cells[row, column].Value to fill data.
• Save and Close: Don't forget to save and close the workbook when you're done.

```csharp Application excelApp = new Application(); Workbook workbook = excelApp.Workbooks.Add(); Worksheet worksheet = workbook.Worksheets[1]; worksheet.Cells[1, 1].Value = "Header 1"; worksheet.Cells[1, 2].Value = "Header 2"; for(int i = 2; i <= 10; i++) { worksheet.Cells[i, 1].Value = i * 100; worksheet.Cells[i, 2].Value = "Data " + i; } workbook.SaveAs("C:\\Example.xlsx"); workbook.Close(); excelApp.Quit(); ```

⚠️ Note: Interop requires Excel to be installed on the machine where the application is running. This can be problematic in server environments or when deploying applications to users who might not have Excel.

2. Using EPPlus

EPPlus is an open-source .NET library that reads and writes Excel files using the Office Open XML format. It doesn't require Excel to be installed on the machine, making it suitable for server-side operations:

• Download and Install: Get EPPlus via NuGet Package Manager.
• Create Package: Instantiate an ExcelPackage.
• Add Worksheets: Add worksheets to the workbook.
• Fill Data: Use the various methods provided by EPPlus to fill cells.
• Save File: Save the workbook.

```csharp using (ExcelPackage excelPackage = new ExcelPackage(new FileInfo("C:\\Example.xlsx"))) { ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1"); worksheet.Cells["A1"].Value = "Header 1"; worksheet.Cells["B1"].Value = "Header 2"; for (int row = 2; row <= 10; row++) { worksheet.Cells[row, 1].Value = row * 100; worksheet.Cells[row, 2].Value = "Data " + row; } excelPackage.Save(); } ```

3. Using OpenXML SDK

The Open XML SDK provides a set of classes that enable .NET developers to work directly with Open XML documents like Excel files:

• Include Reference: Reference the DocumentFormat.OpenXml.dll.
• Create Spreadsheet Document: Instantiate a SpreadsheetDocument.
• Work with Worksheet: Define and manipulate worksheet parts.
• Add Cells: Insert cells with data into the worksheet.
• Save Document: Close and save the document.

```csharp using (SpreadsheetDocument document = SpreadsheetDocument.Create("C:\\Example.xlsx", DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets()); WorksheetPart worksheetPart = workbookPart.AddNewPart(); SheetData sheetData = new SheetData(); worksheetPart.Worksheet = new Worksheet(sheetData); for (int row = 1; row <= 10; row++) { Row r = new Row() { RowIndex = (UInt32)row }; for (int col = 1; col <= 2; col++) { Cell c = new Cell() { CellReference = GetColumnName(col) + row.ToString() }; c.DataType = CellValues.String; c.CellValue = new CellValue("Data"); r.Append(c); } sheetData.Append(r); } document.Close(); } ```

4. Using ExcelDataReader

While primarily used for reading Excel files, ExcelDataReader can also be used for filling Excel sheets, particularly in conjunction with libraries like EPPlus:

• Read Existing Data: ExcelDataReader can extract data from an existing Excel file.
• Create or Modify Workbook: Use EPPlus or OpenXML SDK to modify or create new workbooks.
• Fill New Data: Insert new data alongside or replacing the old data.

```csharp using (var stream = File.Open("C:\\OldData.xlsx", FileMode.Open, FileAccess.Read)) { using (var reader = ExcelReaderFactory.CreateReader(stream)) { var dataset = reader.AsDataSet(); foreach (DataTable table in dataset.Tables) { // Use EPPlus or OpenXML to write this data back into a new or existing workbook. } } } ```

5. Using VBA in a Managed Environment

VBA (Visual Basic for Applications) can be used within Excel and called by a C# application through COM Interop:

• Add VBA Module: Within Excel, add a VBA module with macros to fill data.
• Call VBA Macros: Use C# to open the Excel file and run VBA macros.

```csharp Application excelApp = new Application(); Workbook workbook = excelApp.Workbooks.Open("C:\\VBAExample.xlsx"); excelApp.Run("MyMacroName"); workbook.Save(); workbook.Close(); excelApp.Quit(); ```

In this approach, the data manipulation is handled in VBA, which might be more familiar to users who already use Excel extensively, but it's less optimal for server-side automation or when looking for robust, scalable solutions.

Each of these methods offers different advantages:

• Office Interop: Ideal for in-depth Excel manipulation and when Excel is installed.
• EPPlus and OpenXML: Suitable for server-side applications where Excel isn't required.
• ExcelDataReader with Others: Useful for transforming and re-importing existing data.
• VBA in Managed Environment: For legacy systems or environments where VBA is already in use.

In conclusion, filling Excel sheets programmatically in C# can be achieved through various methods, each tailored to different scenarios and requirements. Whether you need to automate repetitive tasks, process large data sets, or integrate with existing systems, there's a solution that will fit your needs. Choosing the right approach depends on your deployment environment, the scale of the task, and the level of Excel interaction needed. Remember to consider server-side limitations, performance implications, and the ease of maintenance when making your decision.

What is the difference between Interop and OpenXML SDK for Excel Automation?

+

Interop requires Excel to be installed on the machine running the application, making it suitable for client-side applications. It has full Excel functionality but can be slower due to COM inter-process communication. OpenXML SDK, on the other hand, works directly with the XML data behind Excel files, providing high performance for file operations without needing Excel installation, making it ideal for server-side automation.

Is EPPlus free for commercial use?

+

Yes, EPPlus is now MIT-licensed, making it free for both personal and commercial use since version 5.5.0.

Can I automate Excel without having Excel installed?

+

Absolutely. Libraries like EPPlus and OpenXML SDK allow you to work with Excel files without the need for Excel to be installed. They work directly with the XML structure of Excel files.

What are the performance implications of using Interop versus EPPlus?

+

Interop has a performance overhead due to COM inter-process communication and can be slower, especially when dealing with large datasets or frequent Excel operations. EPPlus, by contrast, is typically faster because it operates directly on the file, avoiding the need for COM interaction.