3 Simple Ways to Convert Excel to CSV in C

In today's data-driven environment, efficiently converting Excel spreadsheets into Comma-Separated Values (CSV) files is essential for many applications, including data analysis, database management, and sharing information. Whether you are a developer or a user aiming to streamline workflows, automating this process in C# can enhance productivity significantly. Here, we will explore three straightforward methods to convert Excel to CSV in C#, catering to both newcomers and seasoned programmers.

Method 1: Using Excel Interop

Excel Interop is part of the Microsoft Office suite, which allows applications to interact with Excel directly. This method requires Excel installed on the machine running the script.

• Step 1: Add the Microsoft.Office.Interop.Excel NuGet package to your project.
• Step 2: Import the necessary namespaces:

  using Excel = Microsoft.Office.Interop.Excel;

• Step 3: Write the conversion code:

  public static void ConvertExcelToCSV(string excelFilePath, string csvOutputPath)
  {
      Excel.Application excelApp = new Excel.Application();
      Excel.Workbook workbook = null;
      Excel.Worksheet worksheet = null;
      try
      {
          workbook = excelApp.Workbooks.Open(excelFilePath);
          worksheet = workbook.Sheets[1];
          worksheet.SaveAs(csvOutputPath, Excel.XlFileFormat.xlCSV);
      }
      finally
      {
          workbook?.Close(false);
          excelApp?.Quit();
          System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
          System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
          System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
          worksheet = null;
          workbook = null;
          excelApp = null;
          GC.Collect();
      }
  }

🚨 Note: This method requires Excel installed on the server, which can cause issues if your server environment lacks the necessary software. Make sure to manage COM object references carefully to prevent memory leaks.

Method 2: Using OpenXML SDK

OpenXML SDK provides a set of classes to manipulate Office Open XML documents, including Excel spreadsheets, without requiring Office to be installed. This approach is very efficient for servers or environments where Excel is not available.

• Step 1: Add DocumentFormat.OpenXml NuGet package to your project.
• Step 2: Use the following code to convert Excel to CSV:

  using DocumentFormat.OpenXml.Packaging;
  using DocumentFormat.OpenXml.Spreadsheet;
  using System.Text;
  
  public static void ConvertExcelToCSVUsingOpenXML(string excelFilePath, string csvOutputPath)
  {
      StringBuilder csvContent = new StringBuilder();
      using (SpreadsheetDocument document = SpreadsheetDocument.Open(excelFilePath, false))
      {
          WorkbookPart workbookPart = document.WorkbookPart;
          WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
          SheetData sheetData = worksheetPart.Worksheet.Elements().First();
          foreach (Row r in sheetData.Elements())
          {
              foreach (Cell c in r.Elements())
              {
                  csvContent.Append(CellValueToString(c, workbookPart) + ",");
              }
              csvContent.AppendLine();
          }
      }
      File.WriteAllText(csvOutputPath, csvContent.ToString());
  }
  
  private static string CellValueToString(Cell cell, WorkbookPart workbookPart)
  {
      if (cell.CellValue == null)
          return string.Empty;
  
      string value = cell.InnerText;
      if (cell.DataType != null && cell.DataType == CellValues.SharedString)
      {
          return workbookPart.SharedStringTablePart.SharedStringTable.Elements().ElementAt(int.Parse(value)).InnerText;
      }
      return value;
  }

Method 3: Using EPPlus

EPPlus is a popular .NET library for managing Office Open XML spreadsheets. It does not require Excel to be installed and provides a clean API for handling spreadsheets.

• Step 1: Install the EPPlus NuGet package to your project.
• Step 2: Utilize EPPlus to perform the conversion:

  using OfficeOpenXml;
  using System.IO;
  
  public static void ConvertExcelToCSVUsingEPPlus(string excelFilePath, string csvOutputPath)
  {
      FileInfo existingFile = new FileInfo(excelFilePath);
      using (ExcelPackage package = new ExcelPackage(existingFile))
      {
          ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
          StringBuilder sb = new StringBuilder();
          for (int rowNum = 1; rowNum <= worksheet.Dimension.End.Row; rowNum++)
          {
              for (int colNum = 1; colNum <= worksheet.Dimension.End.Column; colNum++)
              {
                  sb.Append(worksheet.Cells[rowNum, colNum].Value?.ToString() ?? string.Empty);
                  if (colNum < worksheet.Dimension.End.Column)
                      sb.Append(",");
              }
              sb.AppendLine();
          }
          File.WriteAllText(csvOutputPath, sb.ToString());
      }
  }

💡 Note: EPPlus is a robust library but be cautious with its licensing, as commercial use requires a paid license.

This comprehensive guide has outlined three distinct methods for converting Excel files to CSV in C#. Each method has its unique advantages:

• Excel Interop: Best when you have Excel installed and need advanced Excel operations.
• OpenXML SDK: Ideal for server environments where Excel isn't installed, offering high performance and full document access.
• EPPlus: Provides ease of use with its clean API, suitable for both desktop and server applications.

The choice of method depends on your specific needs, server setup, and licensing considerations. Ensure you understand the implications of each approach in terms of resource requirements, licensing, and performance to select the most suitable for your project.

What are the benefits of converting Excel to CSV?

+

CSV files are lighter and easier to read across different platforms. They support simpler data exchange, require less memory to process, and are universally supported by database systems and data analysis tools.

Can these methods handle Excel files with multiple worksheets?

+

The code snippets provided focus on converting the first sheet. However, all three libraries can be adapted to handle multiple worksheets by iterating through each sheet in the workbook.

What should I do if my Excel file contains special characters or formatting?

+

CSV doesn’t inherently support formatting. Special characters need to be handled with care, often through escape sequences or double-quoting. Libraries like EPPlus or OpenXML SDK can help manage complex data types and formatting during conversion.