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

Asked

Viewed 928 times

3

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);
        }
    }
}

  • Possible duplicate of Converting word file to pdf

  • @Matheusmiranda, I don’t think it can be considered as duplicate since Word and Excel have very different structures, Excel have worksheets and cells, for example, and one of the doubts of this question was about how to save in XLSX format, while the question of your question was about how to save Word document in PDF.

  • I have the same doubt that you, when I try to run I get this error message -> 'Access to the 'C: Program Files (x86) IIS Express result.xlsx' path has been denied. ' How do I save the file to another folder other than this one ?

1 answer

5

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:


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

Browser other questions tagged

You are not signed in. Login or sign up in order to post.