How to create CSV Excel file C

Question

I m looking for a class for creating CSV Excel files   Expected features    Extremely simple to use Escapes commas and quotes so excel handles them fine Exports date and datetimes in timezone-proof format   Do you know any class capable of this

User · Answer

there s an open-source library for CSV which you can get using nuget  http   joshclose github io CsvHelper

User · Answer

I ve added         public void ExportToFile string path  DataTable tabela          DataColumnCollection colunas   tabela Columns        foreach  DataRow linha in tabela Rows                     this AddRow                foreach  DataColumn coluna in colunas                                this coluna ColumnName    linha coluna                                this ExportToFile path        Previous code does not work with old  NET versions  For 3 5 version of framework use this other version           public void ExportToFile string path                bool abort   false          bool exists   false          do                       exists   File Exists path               if   exists                                if   Convert ToBoolean  File CreateText path                              abort   true                          while   exists    abort            if   abort                          File OpenWrite path               using  StreamWriter w   File AppendText path                                 w WriteLine  hello                                        File WriteAllText path  Export

User · Answer

The original class have a problem  and that is if you want to add a new column  you will receive KeyNotFoundException on Export method  For example   static void Main string   args        var export   new CsvExport         export AddRow        export  Region      New York  USA       export  Sales     100000      export  Date Opened     new DateTime 2003  12  31        export AddRow        export  Region      Sydney   in   Australia       export  Sales     50000      export  Date Opened     new DateTime 2005  1  1  9  30  0       export  Balance     3 45f     Exception is throwed for this new column      export ExportToFile  Somefile csv        To solve this  and using the  KeyboardCowboy idea of   using reflection  I modified the code to allow add rows that do not have the same columns  You can use instances of anonymous classes  For example   static void Main string   args        var export   new CsvExporter         export AddRow new  A   12  B    Empty         export AddRow new  A   34 5f  D   false         export ExportToFile  File csv        You can download the source code here CsvExporter  Feel free to use and modify   Now  if all rows you want to write are of the same class  I created the generic class CsvWriter cs  which has a better performance RAM usage and ideal for writing large files Plus it lets you add formatters to the data type you want  An example of use   class Program       static void Main string   args                var writer   new CsvWriter lt Person gt   Persons csv             writer AddFormatter lt DateTime gt  d   gt  d ToString  MM dd yyyy              writer WriteHeaders            writer WriteRows GetPersons              writer Flush            writer Close               private static IEnumerable lt Person gt  GetPersons                 yield return new Person                               FirstName    Jhon                    LastName    Doe                    Sex    M                          yield return new Person                               FirstName    Jhane                    LastName    Doe                   Sex    F                   BirthDate   DateTime Now                                      class Person               public string FirstName   get  set             public string LastName   get  set             public char Sex    get  set             public DateTime BirthDate   get  set

User · Answer

Slightly different version I wrote using reflection for my needs  I had to export a list of objects to csv  In case someone wants to use it for future   public class CsvExport lt T gt  where T  class               public List lt T gt  Objects           public CsvExport List lt T gt  objects                        Objects   objects                     public string Export                         return Export true                      public string Export bool includeHeaderLine                         StringBuilder sb   new StringBuilder                  Get properties using reflection              IList lt PropertyInfo gt  propertyInfos   typeof T  GetProperties                 if  includeHeaderLine                                  add header line                  foreach  PropertyInfo propertyInfo in propertyInfos                                        sb Append propertyInfo Name  Append                                         sb Remove sb Length - 1  1  AppendLine                                 add value for each property              foreach  T obj in Objects                                               foreach  PropertyInfo propertyInfo in propertyInfos                                        sb Append MakeValueCsvFriendly propertyInfo GetValue obj  null    Append                                         sb Remove sb Length - 1  1  AppendLine                               return sb ToString                         export to a file          public void ExportToFile string path                        File WriteAllText path  Export                          export as binary data          public byte   ExportToBytes                         return Encoding UTF8 GetBytes Export                          get the csv value for field          private string MakeValueCsvFriendly object value                        if  value    null  return                 if  value is Nullable  amp  amp    INullable value  IsNull  return                  if  value is DateTime                                if    DateTime value  TimeOfDay TotalSeconds    0                      return   DateTime value  ToString  yyyy-MM-dd                    return   DateTime value  ToString  yyyy-MM-dd HH mm ss                              string output   value ToString                 if  output Contains         output Contains                        output         output Replace                                   return output                     Usage sample    updated per comment   CsvExport lt BusinessObject gt  csv  new CsvExport lt BusinessObject gt  GetBusinessObjectList     Response Write csv Export

User · Answer

I added ExportToStream so the csv didn t have to save to the hard drive first   public Stream ExportToStream         MemoryStream stream   new MemoryStream        StreamWriter writer   new StreamWriter stream       writer Write Export true        writer Flush        stream Position   0      return stream

User · Answer

Another good solution to read and write CSV-files is filehelpers  open source

User · Answer

If anyone would like I converted this to an extension method on IEnumerable   public static class ListExtensions       public static string ExportAsCSV lt T gt  this IEnumerable lt T gt  listToExport  bool includeHeaderLine  string delimeter                StringBuilder sb   new StringBuilder             IList lt PropertyInfo gt  propertyInfos   typeof T  GetProperties             if  includeHeaderLine                        foreach  PropertyInfo propertyInfo in propertyInfos                                sb Append propertyInfo Name  Append                                 sb Remove sb Length - 1  1  AppendLine                       foreach  T obj in listToExport                        T localObject   obj               var line   String Join delimeter  propertyInfos Select x   gt  SanitizeValuesForCSV x GetValue localObject  null   delimeter                  sb AppendLine line                      return sb ToString               private static string SanitizeValuesForCSV object value  string delimeter                string output           if  value    null  return              if  value is DateTime                        output     DateTime value  ToLongDateString                      else                       output   value ToString                                       if  output Contains delimeter     output Contains                    output         output Replace                               output   output Replace   n                 output   output Replace   r                 return output

User · Answer

You need only 1 function to do this  Only you have to do is to make a folder in your solution explorer and store the csv file there and then export that file to the user    As in my case I have a folder downloads  First I export all my content to that directory and then exporting it to the user  For response end handling  I used the ThreadAbortException  So it is a 100  genuine and working function in my solution   protected void lnkExport OnClick object sender  EventArgs e         string filename   strFileName    Export csv        DataTable dt   obj GetData          call the content and load it into the datatable      strFileName   Server MapPath  Downloads            strFileName      creating a file in the downloads folder in your solution explorer      TextWriter tw   new StreamWriter strFileName       using the built in class textwriter for writing your content in the exporting file      string strData    Username Password City       above line is the header for your exported file  So add headings for your coloumns in excel  csv  file and seperate them with          strData    Environment NewLine      setting the environment to the new line      foreach  DataRow dr in dt Rows               strData    dr  Username   ToString           dr  Password   ToString                dr  City   ToString           strData    Environment NewLine            everytime when loop execute  it adds a line into the file     tw Write strData       writing the contents in file     tw Close        closing the file     Response Redirect  Downloads     filename       exporting the file to the user as a popup to save as

User · Answer

Please forgive me  But I think a public open-source repository is a better way to share code and make contributions  and corrections  and additions like  I fixed this  I fixed that   So I made a simple git-repository out of the topic-starter s code and all the additions   https   github com jitbit CsvExport  I also added a couple of useful fixes myself  Everyone could add suggestions  fork it to contribute etc  etc  etc  Send me your forks so I merge them back into the repo   PS  I posted all copyright notices for Chris   Chris if you re against this idea - let me know  I ll kill it

User · Answer

How about using string Join instead of all the foreach Loops

User · Answer

Thanks a lot for that  I modified the class to    use a variable delimiter  instead of hardcoded in code  replacing all newLines   n  r  n r  in MakeValueCsvFriendly   Code   using System  using System Collections Generic  using System Linq  using System Data SqlTypes  using System IO  using System Text  using System Text RegularExpressions       public class CsvExport                public char delim                     lt summary gt              To keep the ordered list of column names              lt  summary gt          List lt string gt  fields   new List lt string gt                   lt summary gt              The list of rows              lt  summary gt          List lt Dictionary lt string  object gt  gt  rows   new List lt Dictionary lt string  object gt  gt                   lt summary gt              The current row              lt  summary gt          Dictionary lt string  object gt  currentRow   get   return rows rows Count - 1                     lt summary gt              Set a value on this column              lt  summary gt          public object this string field                        set                                  Keep track of the field names  because the dictionary loses the ordering                 if   fields Contains field   fields Add field                   currentRow field    value                                        lt summary gt              Call this before setting any fields on a row              lt  summary gt          public void AddRow                         rows Add new Dictionary lt string  object gt                              lt summary gt              Converts a value to how it should output in a csv file             If it has a comma  it needs surrounding with double quotes             Eg Sydney  Australia - gt   Sydney  Australia              Also if it contains any double quotes      then they need to be replaced with quad quotes sic                   Eg  Dangerous Dan  McGrew - gt     Dangerous Dan   McGrew               lt  summary gt          string MakeValueCsvFriendly object value                        if  value    null  return                 if  value is INullable  amp  amp    INullable value  IsNull  return                 if  value is DateTime                                if    DateTime value  TimeOfDay TotalSeconds    0                      return   DateTime value  ToString  yyyy-MM-dd                    return   DateTime value  ToString  yyyy-MM-dd HH mm ss                              string output   value ToString                if  output Contains delim     output Contains                        output         output Replace                                  if  Regex IsMatch output         r n  n  r                     output   string Join      Regex Split output        r n  n  r                  return output                          lt summary gt              Output all rows as a CSV returning a string              lt  summary gt          public string Export                         StringBuilder sb   new StringBuilder                    The header             foreach  string field in fields                  sb Append field  Append delim               sb AppendLine                    The rows             foreach  Dictionary lt string  object gt  row in rows                                foreach  string field in fields                      sb Append MakeValueCsvFriendly row field    Append delim                   sb AppendLine                               return sb ToString                            lt summary gt              Exports to a file              lt  summary gt          public void ExportToFile string path                        File WriteAllText path  Export                             lt summary gt              Exports as raw UTF8 bytes              lt  summary gt          public byte   ExportToBytes                         return Encoding UTF8 GetBytes Export

User · Answer

You can also use ADO to do this  http   weblogs asp net fmarguerie archive 2003 10 01 29964 aspx

User · Answer

great work on this class  Simple and easy to use  I modified the class to include a title in the first row of the export  figured I would share   use   CsvExport myExport   new CsvExport    myExport addTitle   String Format  Name   0   1    lastName  firstName      class   public class CsvExport       List lt string gt  fields   new List lt string gt          public string addTitle   get  set       string for the first row of the export      List lt Dictionary lt string  object gt  gt  rows   new List lt Dictionary lt string  object gt  gt         Dictionary lt string  object gt  currentRow               get                       return rows rows Count - 1                        public object this string field                set                       if   fields Contains field   fields Add field               currentRow field    value                       public void AddRow                 rows Add new Dictionary lt string  object gt                 string MakeValueCsvFriendly object value                if  value    null  return             if  value is Nullable  amp  amp    INullable value  IsNull  return             if  value is DateTime                        if    DateTime value  TimeOfDay TotalSeconds    0                  return   DateTime value  ToString  yyyy-MM-dd                return   DateTime value  ToString  yyyy-MM-dd HH mm ss                      string output   value ToString            if  output Contains         output Contains                    output         output Replace                              return output              public string Export                 StringBuilder sb   new StringBuilder                if there is a title         if   string IsNullOrEmpty addTitle                            escape chars that would otherwise break the row   export             char   csvTokens   new                  n     r                  if  addTitle IndexOfAny csvTokens   gt   0                                addTitle          addTitle Replace                                                 sb Append addTitle  Append                   sb AppendLine                           The header         foreach  string field in fields          sb Append field  Append               sb AppendLine                The rows         foreach  Dictionary lt string  object gt  row in rows                        foreach  string field in fields                  sb Append MakeValueCsvFriendly row field    Append                   sb AppendLine                       return sb ToString               public void ExportToFile string path                File WriteAllText path  Export                public byte   ExportToBytes                 return Encoding UTF8 GetBytes Export

[c#] How to create CSV Excel file C#?

Examples related to c#

Examples related to excel

Examples related to csv

Examples related to export