×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: C#
Posted by: Arturo Bardelli
Added: Jan 20, 2016 10:40 AM
Modified: Jan 20, 2016 10:45 AM
Views: 27
call .ToCsv on any datatable to get a csv representation of that datatable, good for importing it to excel or to a spreadsheet program
  1.   public static class Extensions
  2.   {
  3.       private const string CSV_SEPARATOR = ",";
  4.      
  5.       private const string QUOTE = "\"";
  6.       private const string ESCAPED_QUOTE = "\"\"";
  7.       private static char[] CHARACTERS_THAT_MUST_BE_QUOTED = { ',', '"', '\n' };
  8.  
  9.     public static string ToCSV(this DataTable table, List<String> columnsExcluded, bool includeSeparatorSpecificationOnFirstRow)
  10.     {
  11.         var result = new StringBuilder();
  12.  
  13.         // Aggiunta riga di specifica del separatore per excel        
  14.         if (includeSeparatorSpecificationOnFirstRow)
  15.         {
  16.             result.Append(string.Concat("sep=", CSV_SEPARATOR));
  17.             result.Append(Environment.NewLine);
  18.         }
  19.  
  20.         var columnsIncluded = table.Columns.Cast<DataColumn>().ToArray();// table.Columns.Cast<DataColumn>().Where(col => columnsExcluded.Contains(col.ColumnName) == false).ToArray();
  21.         var columnsIncluded2 = table.Columns.Cast<DataColumn>().Where(col => columnsExcluded.Contains(col.ColumnName) == false).ToArray();
  22.  
  23.         for (int i = 0; i < columnsIncluded.Length; i++)
  24.         {
  25.             string toAdd = columnsIncluded[i].ColumnName;
  26.             string toAddEscaped = Escape(toAdd);
  27.        
  28.             result.Append(toAddEscaped);
  29.             result.Append(i == columnsIncluded.Length - 1 ? Environment.NewLine : CSV_SEPARATOR);
  30.         }
  31.  
  32.         foreach (DataRow row in table.Rows)
  33.         {
  34.             for (int i = 0; i < columnsIncluded.Length; i++)
  35.             {
  36.             string toAdd = row[columnsIncluded[i].ColumnName].ToString();
  37.             string toAddEscaped = Escape(toAdd);
  38.  
  39.             result.Append(toAddEscaped);
  40.             result.Append(i == columnsIncluded.Length - 1 ? Environment.NewLine : CSV_SEPARATOR);
  41.             }
  42.         }
  43.  
  44.         return result.ToString();
  45.     }
  46.  
  47.     private static string Escape(string s)
  48.     {
  49.         if (s.Contains(QUOTE))
  50.             s = s.Replace(QUOTE, ESCAPED_QUOTE);
  51.  
  52.         if (s.IndexOfAny(CHARACTERS_THAT_MUST_BE_QUOTED) > -1)
  53.             s = QUOTE + s + QUOTE;
  54.  
  55.         return s;
  56.     }
  57.  
  58.     //private static string Unescape(string s)
  59.     //{
  60.     //    if (s.StartsWith(QUOTE) && s.EndsWith(QUOTE))
  61.     //    {
  62.     //        s = s.Substring(1, s.Length - 2);
  63.  
  64.     //        if (s.Contains(ESCAPED_QUOTE))
  65.     //            s = s.Replace(ESCAPED_QUOTE, QUOTE);
  66.     //    }
  67.  
  68.     //    return s;
  69.     //}
  70.  
  71.   }