.NET C# 读写CSV及转换DataTable

1. 依赖库

using System.Data;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;

2. CSVUtil

2.1 CSV 转 DataTable

/// <summary>
/// CSV 转 DataTable
/// </summary>
/// <param name="csvFilePath">CSV文件路径</param>
/// <param name="separator">间隔符，默认为","</param>
/// <returns>结果DataTable</returns>
public static DataTable? FromCSV(string csvFilePath, string separator = ",")
{DataTable dt = new DataTable();string separatorTemp = Guid.NewGuid().ToString().Replace("-", "");List<string[]?> lineArrayList = ReadCsvLines(csvFilePath, separator, separatorTemp);if (lineArrayList.Count < 1){return null;}int maxColumnCount = 0;lineArrayList.ForEach(l =>{if(l != null && l.Length > maxColumnCount){maxColumnCount = l.Length;}});string[]? headerLineArray = lineArrayList[0];for (int columnIdx = 0; columnIdx < maxColumnCount; columnIdx++){string? columnName = null;if (headerLineArray != null && headerLineArray.Length > columnIdx){columnName = headerLineArray[columnIdx]?.Trim('"')?.Replace(separatorTemp, separator);}if (string.IsNullOrEmpty(columnName)){columnName = $"column_{columnIdx + 1}";}string columnNameTemp = columnName;int tag = 0;while (dt.Columns.Contains(columnNameTemp)){columnNameTemp = $"{columnName}_{++tag}";}dt.Columns.Add(columnNameTemp);}for (int rowIdx = 1; rowIdx < lineArrayList.Count; rowIdx++){string[]? lineArray = lineArrayList[rowIdx];DataRow dataRow = dt.NewRow();for (int columnIdx = 0; columnIdx < maxColumnCount; columnIdx++){if (lineArray != null && lineArray.Length > columnIdx){dataRow[columnIdx] = lineArray[columnIdx]?.Trim('\"')?.Replace(separatorTemp, separator);}}dt.Rows.Add(dataRow);}return dt;
}

2.2 DataTable 转 CSV 文本

/// <summary>
/// DataTable 转 CSV 文本
/// </summary>
/// <param name="dt">DataTable实例</param>
/// <param name="separator">间隔符，默认为","</param>
/// <returns>CSV 文本</returns>
public static string ToCsvText(DataTable dt, string separator = ",")
{string csvText = string.Empty;string currentLine = string.Empty;for (int columnIdx = 0; columnIdx < dt.Columns.Count; columnIdx++){currentLine += $"\"{dt.Columns[columnIdx].ColumnName}\"{separator}";}currentLine = currentLine[..^separator.Length];csvText += currentLine;for (int rowIdx = 0; rowIdx < dt.Rows.Count; rowIdx++){currentLine = string.Empty;for (int columnIdx = 0; columnIdx < dt.Columns.Count; columnIdx++){currentLine += $"\"{dt.Rows[rowIdx][columnIdx]}\"{separator}";}currentLine = currentLine[..^separator.Length];csvText += Environment.NewLine + currentLine;}return csvText;
}

2.3 DataTable 转 CSV

/// <summary>
/// DataTable 转 CSV
/// </summary>
/// <param name="dt">DataTable实例</param>
/// <param name="csvFilePath">CSV文件路径</param>
/// <param name="separator">间隔符，默认为","</param>
/// <param name="cover">覆盖CSV文件，默认为True，若仅追加，则传False</param>
public static void ToCSV(DataTable dt, string csvFilePath, string separator = ",", bool cover = true)
{FileStream? fs = null;StreamWriter? sw = null;string csvText = ToCsvText(dt, separator);try{if (!File.Exists(csvFilePath) || cover){fs = new FileStream(csvFilePath, FileMode.Create, FileAccess.Write);sw = new StreamWriter(fs, Encoding.UTF8);}else{fs = new FileStream(csvFilePath, FileMode.Append, FileAccess.Write);sw = new StreamWriter(fs, Encoding.UTF8);sw.WriteLine();}sw.Write(csvText);}catch (Exception ex){// 异常处理...}finally{try { sw?.Close(); } catch { }try { fs?.Close(); } catch { }}
}

2.4 私有方法

private static List<string[]?> ReadCsvLines(string csvFilePath, string separator, string separatorTemp)
{FileStream? fs = null;StreamReader? sr = null;List<string[]?> lineArrayList = new List<string[]?>();try{fs = new FileStream(csvFilePath, FileMode.Open, FileAccess.Read);sr = new StreamReader(fs, Encoding.UTF8);string? currentLine = string.Empty;string[]? lineArray = null;while (!string.IsNullOrEmpty(currentLine = sr.ReadLine())){currentLine = currentLine.Trim();if (currentLine.Contains('"')){Regex regex = new Regex("\"(.*?)\"");MatchCollection matches = regex.Matches(currentLine);int offset = 0;foreach (Match match in matches.Cast<Match>()){Group group = match.Groups[1];if (group.Value.Contains(separator)){string replaceText = group.Value.Replace(separator, separatorTemp);currentLine = currentLine.Remove(group.Index + offset, group.Length);currentLine = currentLine.Insert(group.Index + offset, replaceText);offset = offset + replaceText.Length - group.Length;}}}lineArray = currentLine.Split(separator);lineArrayList.Add(lineArray);}}catch (Exception ex){// 异常处理...}finally{try { sr?.Close(); } catch { }try { fs?.Close(); } catch { }}return lineArrayList;
}