How to save an XLSX (Excel) file instead of CSV?

Question

Today I have the code below, which saves a file in CSV format.
What I need to change to save in XLSX format (Excel)?

public static void buildCSV(string filename = "resultado.xlsx")
        {
            Console.WriteLine("Escrevendo no arquivo.");

            StringBuilder csvBuilder = new StringBuilder();

            //cria o header
            csvBuilder.AppendLine("Nome;Cpf;Cep;ID Cliente");

            foreach (var item in BAG)
            {
                csvBuilder.AppendLine(item);
            }

            File.WriteAllText(filename, csvBuilder.ToString(), Encoding.UTF8);
        }
    }
}

Answer 1

You can do this using the Excel interoperability library (Microsoft.Office.Interop.Excel.dll), but for this Excel will have to be installed on the computer that will run its application.

To use this code you first need to reference the namespace Microsoft.Office.Interop.Excel. An easy way to do this is by using Nuget. In Visual Studio go to Tools > Nuget Package Manager > Manage Nuget Packages for Solution > Browse, search for Microsoft.Office.Interop.Excel and install the package. Or do thus.

The code to create the XLSX file is this:

using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

   public static void CriarArquivoXlsx(string filename = "resultado.xlsx")
   {
      _CriarArquivoXlsx(filename);

      // Libera os objetos COM do Excel.
      GC.Collect();
      GC.WaitForPendingFinalizers();
   }

   private static void _CriarArquivoXlsx(string filename)
   {
      Excel.Application xlApp = new Excel.Application();
      if (xlApp == null)
      {
         MessageBox.Show("Excel não está instalado!");
         return;
      }

      Excel.Workbook xlWorkbook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
      Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1];

      // Cabeçalho.
      // A indicação da coluna pode ser por números (1, 2, 3, etc.)
      // ou por texto ("A", "B", "C", etc.).
      int row = 1;
      xlWorksheet.Cells[row, 1] = "Nome";
      xlWorksheet.Cells[row, 2] = "Cpf";
      xlWorksheet.Cells[row, 3] = "Cep";
      xlWorksheet.Cells[row, 4] = "ID Cliente";

      foreach (string item in BAG)
      {
         row++;
         int col = 0;
         foreach (string campo in item.Split(';'))
         {
            col++;
            xlWorksheet.Cells[row, col] = campo;
         }
      }

      xlWorkbook.SaveAs(filename, XlFileFormat.xlOpenXMLWorkbook);

      xlWorkbook.Close();
      xlApp.Quit();
   }

I created the private function _CriarArquivoXlsx(), that does the real work, and the public service CriarArquivoXlsx(), calling the private function and then firing the GC (Garbagecollector) to release references of COM objects created in the private function.

This is important because, as the interoperability library with Office creates COM objects, they are managed by . NET, and therefore by the Garbage Collector (Garbage Collector) of . NET, the Excel process can be "hung" indefinitely while the application is running. That is why it is necessary to force an immediate garbage collection, thus releasing the references of the Excel COM objects.

The fact that you have put the garbage collection outside the function that creates the COM objects is merely a ruse for it to work properly also in the application’s debug mode, see here the explanation of why to do so.

References used for code creation:

• I want to create xlsx (Excel) file from c# - Stack Overflow
• Microsoft.Office.Interop.Excel.Worksheet.Cells[x,y]. Style? - Stack Overflow
• Clean up Excel Interop Objects with Idisposable - Stack Overflow (same link quoted above)
• How to: Access Office Interop Objects by Using Visual C# Features (C# Programming Guide) | Microsoft Docs

---

There is also an open source library that allows you to create XLSX files without needing Excel installed, called Epplus. More details here:

• Create Excel (.XLS and .XLSX) file from C# - Stack Overflow