| | | 1 | | using System; |
| | | 2 | | using System.Text; |
| | | 3 | | using System.Collections.Generic; |
| | | 4 | | using System.Linq; |
| | | 5 | | using System.Threading.Tasks; |
| | | 6 | | using ClosedXML.Excel; |
| | | 7 | | using System.IO; |
| | | 8 | | |
| | | 9 | | namespace WinSolutions.Sveta.Common |
| | | 10 | | { |
| | | 11 | | public static class CsvUtil |
| | | 12 | | { |
| | | 13 | | /// <summary> |
| | | 14 | | /// Экспортирует список массивов строк в csv-строку с разделителем ";" |
| | | 15 | | /// </summary> |
| | | 16 | | /// <param name="rows">список массивов строк</param> |
| | | 17 | | /// <returns>csv-строка</returns> |
| | | 18 | | public static string ToCsv(List<string[]> rows) |
| | 0 | 19 | | { |
| | 0 | 20 | | var result = new StringBuilder(); |
| | 0 | 21 | | if (rows.Count == 0 || rows[0] == null || rows[0].Length == 0) return result.ToString(); |
| | | 22 | | |
| | 0 | 23 | | var fieldsCount = rows[0].Length; |
| | 0 | 24 | | foreach(var row in rows) |
| | 0 | 25 | | { |
| | 0 | 26 | | for(int i = 0; i < fieldsCount; i++) |
| | 0 | 27 | | { |
| | 0 | 28 | | var row2 = (row[i] ?? string.Empty).Replace("\"", "\"\""); |
| | 0 | 29 | | if(row2.Contains(";") || row2.Contains("\t") || row2.Contains(",")) |
| | 0 | 30 | | { |
| | 0 | 31 | | row2 = $"\"{row2}\""; |
| | 0 | 32 | | } |
| | 0 | 33 | | result.Append(row2); |
| | | 34 | | |
| | 0 | 35 | | if (i + 1 != fieldsCount) |
| | 0 | 36 | | { |
| | 0 | 37 | | result.Append(";"); |
| | 0 | 38 | | } |
| | 0 | 39 | | } |
| | 0 | 40 | | result.AppendLine(); |
| | 0 | 41 | | } |
| | | 42 | | |
| | 0 | 43 | | return result.ToString(); |
| | 0 | 44 | | } |
| | | 45 | | |
| | | 46 | | /// <summary> |
| | | 47 | | /// Экспортирует набор объектов в csv-строку с разделителем ";" |
| | | 48 | | /// В качестве имен колонок используются названия паблик свойств объекта. В данные кладутся значения паблик свой |
| | | 49 | | /// </summary> |
| | | 50 | | /// <typeparam name="T">тип объекта</typeparam> |
| | | 51 | | /// <param name="data">коллекция объектов</param> |
| | | 52 | | /// <returns>csv-строка</returns> |
| | | 53 | | public static string ToCsv<T>(IEnumerable<T> data) |
| | 0 | 54 | | { |
| | 0 | 55 | | if (!data.Any()) return string.Empty; |
| | | 56 | | |
| | 0 | 57 | | List<string[]> result = new List<string[]>(); |
| | | 58 | | |
| | 0 | 59 | | var cols = new List<string>(); |
| | 0 | 60 | | var props = typeof(T).GetProperties(); |
| | 0 | 61 | | foreach (var field in props) |
| | 0 | 62 | | { |
| | 0 | 63 | | cols.Add(field.Name); |
| | 0 | 64 | | } |
| | 0 | 65 | | result.Add(cols.ToArray()); |
| | | 66 | | |
| | 0 | 67 | | foreach(var row in data) |
| | 0 | 68 | | { |
| | 0 | 69 | | cols.Clear(); |
| | 0 | 70 | | foreach (var field in props) |
| | 0 | 71 | | { |
| | 0 | 72 | | var val = field.GetValue(row)?.ToString(); |
| | 0 | 73 | | cols.Add(val ?? ""); |
| | 0 | 74 | | } |
| | 0 | 75 | | result.Add(cols.ToArray()); |
| | 0 | 76 | | } |
| | | 77 | | |
| | 0 | 78 | | return ToCsv(result); |
| | 0 | 79 | | } |
| | | 80 | | |
| | | 81 | | /// <summary> |
| | | 82 | | /// Экспортирует список массивов строк в excel |
| | | 83 | | /// </summary> |
| | | 84 | | /// <param name="rows">список массивов строк</param> |
| | | 85 | | /// <returns>XLWorkbook</returns> |
| | | 86 | | public static XLWorkbook ToExcel(List<string[]> rows) |
| | 0 | 87 | | { |
| | 0 | 88 | | var book = new XLWorkbook(); |
| | | 89 | | |
| | 0 | 90 | | var worksheet = book.Worksheets.Add("data"); |
| | 0 | 91 | | var currentRow = 1; |
| | | 92 | | |
| | 0 | 93 | | foreach (var line in rows) |
| | 0 | 94 | | { |
| | 0 | 95 | | var currentCol = 1; |
| | 0 | 96 | | foreach (var val in line) |
| | 0 | 97 | | { |
| | 0 | 98 | | worksheet.Cell(currentRow, currentCol++).Value = val; |
| | 0 | 99 | | } |
| | | 100 | | |
| | 0 | 101 | | currentRow++; |
| | 0 | 102 | | } |
| | | 103 | | |
| | 0 | 104 | | return book; |
| | 0 | 105 | | } |
| | | 106 | | |
| | | 107 | | public static MemoryStream ToExcelStream(List<string[]> rows) |
| | 0 | 108 | | { |
| | 0 | 109 | | var stream = new MemoryStream(); |
| | 0 | 110 | | using (var book = CsvUtil.ToExcel(rows)) |
| | 0 | 111 | | { |
| | 0 | 112 | | book.SaveAs(stream); |
| | 0 | 113 | | } |
| | 0 | 114 | | stream.Position = 0; |
| | 0 | 115 | | return stream; |
| | 0 | 116 | | } |
| | | 117 | | } |
| | | 118 | | } |