CSV是逗号分隔值格式的文件,其文件以纯文本形式存储表格数据(数字和文本)。CSV文件由任意数目的记录组成,记录间以某种换行符分隔;每条记录由字段组成,字段间的分隔符是其它字符或字符串,最常见的是逗号或制表符。在C#中有时候需要读取和写入Csv文件,特此封装了一个工具类CsvHelper。特此说一句,C#将数据导出到CSV文件的速度比较快,有时候导出Excel文件很慢的时候可以选择先导出.csv文件,而后再本地用Excel软件打开。
CsvHelper类主要是利用C#操作Csv文件,主要包含2个方法。
dt2csv(参数略):将数据导入到csv文件中
csv2dt(参数略):将Csv读入DataTable
具体的工具类实现如下:
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Data.OleDb; namespace Core.IO
{
/// <summary>
/// CSV文件转换类
/// </summary>
public static class CsvHelper
{
/// <summary>
/// 导出报表为Csv
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="strFilePath">物理路径</param>
/// <param name="tableheader">表头</param>
/// <param name="columname">字段标题,逗号分隔</param>
public static bool dt2csv(DataTable dt, string strFilePath, string tableheader, string columname)
{
try
{
string strBufferLine = "";
StreamWriter strmWriterObj = new StreamWriter(strFilePath, false, System.Text.Encoding.UTF8);
strmWriterObj.WriteLine(tableheader);
strmWriterObj.WriteLine(columname);
for (int i = ; i < dt.Rows.Count; i++)
{
strBufferLine = "";
for (int j = ; j < dt.Columns.Count; j++)
{
if (j > )
strBufferLine += ",";
strBufferLine += dt.Rows[i][j].ToString();
}
strmWriterObj.WriteLine(strBufferLine);
}
strmWriterObj.Close();
return true;
}
catch
{
return false;
}
} /// <summary>
/// 将Csv读入DataTable
/// </summary>
/// <param name="filePath">csv文件路径</param>
/// <param name="n">表示第n行是字段title,第n+1行是记录开始</param>
public static DataTable csv2dt(string filePath, int n, DataTable dt)
{
StreamReader reader = new StreamReader(filePath, System.Text.Encoding.UTF8, false);
int i = , m = ;
reader.Peek();
while (reader.Peek() > )
{
m = m + ;
string str = reader.ReadLine();
if (m >= n + )
{
string[] split = str.Split(','); System.Data.DataRow dr = dt.NewRow();
for (i = ; i < split.Length; i++)
{
dr[i] = split[i];
}
dt.Rows.Add(dr);
}
}
return dt;
} /// <summary>
/// CSV转换成DataTable(OleDb数据库访问方式)
/// </summary>
/// <param name="csvPath">csv文件路径</param>
/// <returns></returns>
public static DataTable CSVToDataTableByOledb(string csvPath)
{
DataTable csvdt = new DataTable("csv");
if (!File.Exists(csvPath))
{
throw new FileNotFoundException("csv文件路径不存在!");
} FileInfo fileInfo = new FileInfo(csvPath);
using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileInfo.DirectoryName + ";Extended Properties='Text;'"))
{
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [" + fileInfo.Name + "]", conn);
adapter.Fill(csvdt);
}
return csvdt;
} /// <summary>
/// CSV转换成DataTable(文件流方式)
/// </summary>
/// <param name="csvPath">csv文件路径</param>
/// <returns></returns>
public static DataTable CSVToDataTableByStreamReader(string csvPath)
{
DataTable csvdt = new DataTable("csv"); int intColCount = ;
bool blnFlag = true;
DataColumn column;
DataRow row;
string strline = null;
string[] aryline; using (StreamReader reader = new StreamReader(csvPath, FileEncoding.GetEncoding(csvPath)))
{
while (!string.IsNullOrEmpty((strline = reader.ReadLine())))
{
aryline = strline.Split(new char[] { ',' }); if (blnFlag)
{
blnFlag = false;
intColCount = aryline.Length;
for (int i = ; i < aryline.Length; i++)
{
column = new DataColumn(aryline[i]);
csvdt.Columns.Add(column);
}
continue;
} row = csvdt.NewRow();
for (int i = ; i < intColCount; i++)
{
row[i] = aryline[i];
}
csvdt.Rows.Add(row);
}
} return csvdt;
} /// <summary>
/// DataTable 生成 CSV
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="csvPath">csv文件路径</param>
public static void DataTableToCSV(DataTable dt, string csvPath)
{
if (null == dt)
return; StringBuilder csvText = new StringBuilder();
StringBuilder csvrowText = new StringBuilder();
foreach (DataColumn dc in dt.Columns)
{
csvrowText.Append(",");
csvrowText.Append(dc.ColumnName);
}
csvText.AppendLine(csvrowText.ToString().Substring()); foreach (DataRow dr in dt.Rows)
{
csvrowText = new StringBuilder();
foreach (DataColumn dc in dt.Columns)
{
csvrowText.Append(",");
csvrowText.Append(dr[dc.ColumnName].ToString().Replace(',', ' '));
}
csvText.AppendLine(csvrowText.ToString().Substring());
} File.WriteAllText(csvPath, csvText.ToString(), Encoding.Default);
} }
}
CsvHelper