.NET C# 读写CSV及转换DataTable

目录

• .NET C# 读写CSV及转换DataTable
• • 1. 依赖库
  • 2. CSVUtil
  • • 2.1 CSV 转 DataTable
    • 2.2 DataTable 转 CSV 文本
    • 2.3 DataTable 转 CSV
    • 2.4 私有方法

.NET C# 读写CSV及转换DataTable

1. 依赖库

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

2. CSVUtil

2.1 CSV 转 DataTable

/// 

/// CSV 转 DataTable
/// 
/// CSV文件路径
/// 间隔符，默认为","
/// 结果DataTable
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 文本

/// 

/// DataTable 转 CSV 文本
/// 
/// DataTable实例
/// 间隔符，默认为","
/// CSV 文本
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

/// 

/// DataTable 转 CSV
/// 
/// DataTable实例
/// CSV文件路径
/// 间隔符，默认为","
/// 覆盖CSV文件，默认为True，若仅追加，则传False
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;
}