CSV文件解析到DataTable
详细代码如下:
1 public class CsvParsingHelper 2 { 3 /// <summary> 4 /// 将csv文件的数据转成datatable 5 /// </summary> 6 /// <param name="csvfilePath">csv文件路径</param> 7 /// <param name="firstIsRowHead">是否将第一行作为字段名</param> 8 /// <returns></returns> 9 public static DataTable CsvToDataTable(string csvfilePath, bool firstIsRowHead) 10 { 11 DataTable dtResult = null; 12 if (File.Exists(csvfilePath)) 13 { 14 string csvstr = File.ReadAllText(csvfilePath, Encoding.Default); 15 if (!string.IsNullOrEmpty(csvstr)) 16 { 17 dtResult = ToDataTable(csvstr, firstIsRowHead); 18 } 19 } 20 return dtResult; 21 } 22 23 /// <summary> 24 /// 将CSV数据转换为DataTable 25 /// </summary> 26 /// <param name="csv">包含以","分隔的CSV数据的字符串</param> 27 /// <param name="isRowHead">是否将第一行作为字段名</param> 28 /// <returns></returns> 29 private static DataTable ToDataTable(string csv, bool isRowHead) 30 { 31 DataTable dt = null; 32 if (!string.IsNullOrEmpty(csv)) 33 { 34 dt = new DataTable(); 35 string[] csvRows = csv.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries); 36 string[] csvColumns = null; 37 if (csvRows != null) 38 { 39 if (csvRows.Length > 0) 40 { 41 //第一行作为字段名,添加第一行记录并删除csvRows中的第一行数据 42 if (isRowHead) 43 { 44 csvColumns = FromCsvLine(csvRows[0]); 45 csvRows[0] = null; 46 for (int i = 0; i < csvColumns.Length; i++) 47 { 48 dt.Columns.Add(csvColumns[i]); 49 } 50 } 51 52 for (int i = 0; i < csvRows.Length; i++) 53 { 54 if (csvRows[i] != null) 55 { 56 csvColumns = FromCsvLine(csvRows[i]); 57 //检查列数是否足够,不足则补充 58 if (dt.Columns.Count < csvColumns.Length) 59 { 60 int columnCount = csvColumns.Length - dt.Columns.Count; 61 for (int c = 0; c < columnCount; c++) 62 { 63 dt.Columns.Add(); 64 } 65 } 66 dt.Rows.Add(csvColumns); 67 } 68 } 69 } 70 } 71 } 72 73 return dt; 74 } 75 /// <summary> 76 /// 解析一行CSV数据 77 /// </summary> 78 /// <param name="csv">csv数据行</param> 79 /// <returns></returns> 80 public static string[] FromCsvLine(string csv) 81 { 82 List<string> csvLiAsc = new List<string>(); 83 List<string> csvLiDesc = new List<string>(); 84 85 if (!string.IsNullOrEmpty(csv)) 86 { 87 //顺序查找 88 int lastIndex = 0; 89 int quotCount = 0; 90 //剩余的字符串 91 string lstr = string.Empty; 92 for (int i = 0; i < csv.Length; i++) 93 { 94 if (csv[i] == '"') 95 { 96 quotCount++; 97 } 98 else if (csv[i] == ',' && quotCount % 2 == 0) 99 { 100 csvLiAsc.Add(ReplaceQuote(csv.Substring(lastIndex, i - lastIndex))); 101 lastIndex = i + 1; 102 } 103 if (i == csv.Length - 1 && lastIndex < csv.Length) 104 { 105 lstr = csv.Substring(lastIndex, i - lastIndex + 1); 106 } 107 } 108 if (!string.IsNullOrEmpty(lstr)) 109 { 110 //倒序查找 111 lastIndex = 0; 112 quotCount = 0; 113 string revStr = Reverse(lstr); 114 for (int i = 0; i < revStr.Length; i++) 115 { 116 if (revStr[i] == '"') 117 { 118 quotCount++; 119 } 120 else if (revStr[i] == ',' && quotCount % 2 == 0) 121 { 122 csvLiDesc.Add(ReplaceQuote(Reverse(revStr.Substring(lastIndex, i - lastIndex)))); 123 lastIndex = i + 1; 124 } 125 if (i == revStr.Length - 1 && lastIndex < revStr.Length) 126 { 127 csvLiDesc.Add(ReplaceQuote(Reverse(revStr.Substring(lastIndex, i - lastIndex + 1)))); 128 lastIndex = i + 1; 129 } 130 131 } 132 string[] tmpStrs = csvLiDesc.ToArray(); 133 Array.Reverse(tmpStrs); 134 csvLiAsc.AddRange(tmpStrs); 135 } 136 } 137 138 return csvLiAsc.ToArray(); 139 } 140 /// <summary> 141 /// 反转字符串 142 /// </summary> 143 /// <param name="str"></param> 144 /// <returns></returns> 145 private static string Reverse(string str) 146 { 147 string revStr = string.Empty; 148 foreach (char chr in str) 149 { 150 revStr = chr.ToString() + revStr; 151 } 152 return revStr; 153 } 154 /// <summary> 155 /// 替换CSV中的双引号转义符为正常双引号,并去掉左右双引号 156 /// </summary> 157 /// <param name="csvValue">csv格式的数据</param> 158 /// <returns></returns> 159 private static string ReplaceQuote(string csvValue) 160 { 161 string rtnStr = csvValue; 162 if (!string.IsNullOrEmpty(csvValue)) 163 { 164 //首尾都是" 165 Match m = Regex.Match(csvValue, "^\"(.*?)\"$"); 166 if (m.Success) 167 { 168 rtnStr = m.Result("${1}").Replace("\"\"", "\""); 169 } 170 else 171 { 172 rtnStr = rtnStr.Replace("\"\"", "\""); 173 } 174 } 175 return rtnStr; 176 177 } 178 }