C#关于CSV得一些操作
一、DataTable转CSV:
/// <summary>
/// DataTable转CSV
/// </summary>
/// <param name="dt"></param>
/// <param name="fullPath"></param>
public static void DataTableToCSV(DataTable dt, string fullPath)
{
FileInfo fi = new FileInfo(fullPath);
if (!fi.Directory.Exists)
{
fi.Directory.Create();
}
FileStream fs = new FileStream(fullPath, FileMode.Create, FileAccess.Write);
StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
var header = string.Empty;
for (int i = 0; i < dt.Columns.Count; i++)//写入列名
{
header += dt.Columns[i].ColumnName.ToString();
if (i < dt.Columns.Count - 1)
{
header += ",";
}
}
sw.WriteLine(header);
//写出各行数据
string data = "";
for (var i = 0; i < dt.Rows.Count; i++)
{
data = "";
for (var j = 0; j < dt.Columns.Count; j++)
{
var str = dt.Rows[i][j].ToString();
str = str.Replace("\"", "\"\""); //替换英文冒号 英文冒号需要换成两个冒号
if (str.Contains(',') || str.Contains('"')
|| str.Contains('\r') || str.Contains('\n')) //含逗号 冒号 换行符的需要放到引号中
{
str = string.Format("\"{0}\"", str);
}
data += str;
if (j < dt.Columns.Count - 1)
{
data += ",";
}
}
sw.WriteLine(data);
}
sw.Close();
fs.Close();
}
二、合并多个CSV:
/// <summary>
/// 合并CSV
/// </summary>
/// <param name="sourcePath"></param>
/// <param name="targetPath"></param>
public static void MergeCSV(string sourcePath, string targetPath)
{
//读取CVS
FileInfo sourceFI = new FileInfo(sourcePath);
if (!sourceFI.Directory.Exists)
{
return;
}
FileStream sourceFS = new FileStream(sourcePath, FileMode.Open, FileAccess.Read);
StreamReader sourceSR = new StreamReader(sourceFS, Encoding.UTF8);
//写入
FileInfo targetFI = new FileInfo(targetPath);
if (!targetFI.Directory.Exists)
{
targetFI.Directory.Create();
}
FileStream targetFS = new FileStream(targetPath, FileMode.Create, FileAccess.Write);
StreamWriter targetSW = new StreamWriter(targetFS, Encoding.UTF8);
//记录每次读取的一行记录
string strLine = "";
//逐行读取CSV中的数据
while ((strLine = sourceSR.ReadLine()) != null)
{
targetSW.WriteLine(strLine);
}
sourceFS.Close();
sourceSR.Close();
targetFS.Close();
targetSW.Close();
sourceFI.Delete();
}
三、SqlDataReader 写入CSV:
public static void SaveCSV2(string fullPath)
{
var cmd = dba.CreateCommand(sqlPara.Sql, CommandType.Text, sqlPara.Parameters);
var dr= cmd.ExecuteReader();
FileInfo fi = new FileInfo(fullPath);
if (!fi.Directory.Exists)
{
fi.Directory.Create();
}
FileStream fs = new FileStream(fullPath, FileMode.Create, FileAccess.Write);
StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
var headers = new List<string>();
for (int i = 0; i < dr.FieldCount; i++)//写入列名
{
headers.Add(dr.GetName(i));
}
var header = string.Join(",", headers);
sw.WriteLine(header);
//写出各行数据
string line = "";
while (dr.Read())
{
var row = new List<string>();
foreach (var col in headers)
{
row.Add(col);
}
line = string.Join(",", row);
sw.WriteLine(line);
}
sw.Close();
fs.Close();
}