Problem with exporting to Excel using C#

Question

I am exporting a list to excel, it occurs that in one of the fields, "fiscal note number content" and very large, excel tries to format it, generating special characters.

Is there any way to select the note field and format it?

I’m doing like this:

      var data = new[]{ 
                           new{ Name="OS", Email="[email protected]", ChaveNF="8715421643464674616464646476464646464646761322031313787" },
                           new{ Name="Shyam", Email="[email protected]", ChaveNF="8715421643464674616464646476464646464646761455322031313322031313" },
                           new{ Name="Mohan", Email="[email protected]", ChaveNF="8715421643464674616464646476464646464646761322031313" },
                           new{ Name="Sohan", Email="[email protected]", ChaveNF="8715421643464674616464646476464646464646761322031313" },
                           new{ Name="Karan", Email="[email protected]", ChaveNF="8715421643464674616464646476464646464646761322031313" },
                           new{ Name="Brij", Email="[email protected]", ChaveNF="8715421643464674616464646476464646464646761322031313" }                       
     };





 private static void CovertToExcel<T>(List<T> data)
    {
        using (TextWriter output = File.CreateText(@"C:\Users\Public\RelDCL.xls"))
        {
            PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
            foreach (PropertyDescriptor prop in props)
            {
                output.Write(prop.DisplayName); // header
                output.Write("\t");
            }
            output.WriteLine();
            foreach (T item in data)
            {
                foreach (PropertyDescriptor prop in props)
                {


                    output.Write(prop.Converter.ConvertToString(prop.GetValue(item)));
                    output.Write("\t");
                }
                output.WriteLine();
            }
        }
    }

Answer 1

Itasouza, please see the example:

using System.Collections.Generic;
using Excel = Microsoft.Office.Interop.Excel;
namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            var bankAccounts = new List<Account> {
                new Account { ID = 345678, Balance = 541.27},
                new Account {ID = 1230221,Balance = -1237.44},
                new Account {ID = 346777,Balance = 3532574},
                new Account {ID = 235788,Balance = 1500.033333}};
            DisplayInExcel(bankAccounts);
        }
        static void DisplayInExcel(IEnumerable<Account> accounts)
        {
            var excelApp = new Excel.Application { Visible = true };
            excelApp.Workbooks.Add();
            Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
            workSheet.Cells[1, "A"] = "ID Number";
            workSheet.Cells[1, "B"] = "Current Balance";
            var row = 1;
            foreach (var acct in accounts)
            {
                row++;
                workSheet.Cells[row, "A"] = acct.ID;
                workSheet.Cells[row, "B"] = acct.Balance;

            }
            workSheet.Range["B2", "B" + row].NumberFormat = "#,###.00 €";
            workSheet.Columns[1].AutoFit();
            workSheet.Columns[2].AutoFit();
        }
    }
    public class Account
    {
        public int ID { get; set; }
        public double Balance { get; set; }
    }
}

Another example:

oSheet.get_Range("B", "B").NumberFormat = "000.000.000/0000-00";
excelWorksheet.Range["S1", "S" + linhas].NumberFormat = "R$ #.###,00"

See more in:

Formatting Excel Cells (currency)

Format columns of an Excel file generated by C#

C# excel format column