5 Easy Ways to Create Excel Sheets with C#

The integration of Excel and C# allows developers to harness the power of automation and computation within their applications, significantly enhancing data handling capabilities. Whether you're managing data, analyzing information, or generating reports, the ability to create Excel sheets programmatically can streamline your workflow. Here are five straightforward methods to accomplish this using C#.

Method 1: Using Open XML SDK

The Open XML SDK is a robust tool for working with Microsoft Office documents, including Excel. Here’s how you can use it to create an Excel sheet:

• Install the SDK: You’ll need to add the DocumentFormat.OpenXml NuGet package to your project.
• Create a Workbook:

  using DocumentFormat.OpenXml.Packaging;
  using DocumentFormat.OpenXml.Spreadsheet;
  
  public void CreateExcelFile(string filepath)
  {
      using (SpreadsheetDocument document = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook))
      {
          WorkbookPart workbookPart = document.AddWorkbookPart();
          workbookPart.Workbook = new Workbook();
  
          WorksheetPart worksheetPart = workbookPart.AddNewPart();
          worksheetPart.Worksheet = new Worksheet();
  
          Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
          
          Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };
          sheets.Append(sheet);
  
          workbookPart.Workbook.Save();
      }
  }

• Add Data: You can then manipulate the worksheet to add cells and data as needed.

💡 Note: The Open XML SDK requires knowledge of XML to fully utilize its capabilities for customizing Excel sheets.

Method 2: Excel Interop

The Excel Interop library lets you interact with Excel from C#:

• Install Excel Interop: Include Microsoft.Office.Interop.Excel in your project references.
• Create and Modify Excel File:

  using Excel = Microsoft.Office.Interop.Excel;
  
  public void CreateExcelWithInterop()
  {
      Excel.Application excel = new Excel.Application();
      Excel.Workbook workbook = excel.Workbooks.Add(Type.Missing);
      Excel.Worksheet worksheet = workbook.ActiveSheet;
  
      worksheet.Cells[1, "A"] = "Column A";
      worksheet.Cells[1, "B"] = "Column B";
  
      workbook.SaveAs("C:\\example.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange);
      workbook.Close(false, Type.Missing, Type.Missing);
      excel.Quit();
      System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
      System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
      System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
  }

Method 3: EPPlus

EPPlus is an open-source library that simplifies Excel manipulation:

• Get EPPlus: Add the EPPlus NuGet package to your project.
• Use EPPlus:

  using OfficeOpenXml;
  
  public void CreateExcelWithEPPlus()
  {
      ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
      using (var package = new ExcelPackage())
      {
          var worksheet = package.Workbook.Worksheets.Add("Sheet1");
          worksheet.Cells[1, 1].Value = "Header A";
          worksheet.Cells[1, 2].Value = "Header B";
  
          var row = worksheet.Cells["A2"];
          row.Value = "Row 1 - Cell A";
          row.Offset(0, 1).Value = "Row 1 - Cell B";
  
          package.SaveAs(new FileInfo("C:\\example.xlsx"));
      }
  }

Method 4: ClosedXML

ClosedXML is another popular library for Excel manipulation:

• Setup ClosedXML: Add ClosedXML to your project via NuGet.
• Create and Modify File:

  using ClosedXML.Excel;
  
  public void CreateExcelWithClosedXML()
  {
      using (var workbook = new XLWorkbook())
      {
          var worksheet = workbook.Worksheets.Add("Sheet 1");
          worksheet.Cell("A1").Value = "First";
          worksheet.Cell("B1").Value = "Row";
          worksheet.Cell("A2").Value = "Second";
          worksheet.Cell("B2").Value = "Row";
  
          workbook.SaveAs("C:\\example.xlsx");
      }
  }

Method 5: Using Excel-DNA

Excel-DNA provides a way to integrate C# into Excel:

• Install Excel-DNA: Add Excel-DNA to your project.
• Integrate C# with Excel:

  using ExcelDna.Integration;
  public static object AddTwo(int x, int y)
  {
      return x + y;
  }
  [ExcelFunction(Description = "Adds two numbers", Category = "Add-in")]
  public static double MyAddTwo([ExcelArgument(Description = "First value")] double a, [ExcelArgument(Description = "Second value")] double b)
  {
      return AddTwo((int)a, (int)b);
  }

Each method has its use-case:

• Open XML SDK - Best for high-level control over the Excel file structure.
• Excel Interop - Suitable for users with an Excel installation on their machine.
• EPPlus & ClosedXML - Easier to use for quick and common Excel tasks.
• Excel-DNA - Ideal for integrating C# directly into Excel functions and macros.

Ultimately, the method you choose depends on your project requirements, the level of complexity, and whether you need the Excel application installed on the end-user's system.

Which method is best for quick Excel data export?

+

For quick Excel data export without complex formatting, libraries like EPPlus or ClosedXML are recommended due to their ease of use.

Can these methods be used in a production environment?

+

Yes, all the methods described can be used in production. However, consider performance, licensing (for EPPlus), and dependencies on end-user systems (for Excel Interop).

Is there a performance difference between these methods?

+

Yes, there are performance differences. Open XML SDK and libraries like EPPlus or ClosedXML can be faster as they work without Excel installed, whereas Excel Interop can be slower due to its need for Excel runtime.