.NET C# 读写CSV及转换DataTable

.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;
}