最近做一个C#项目要导入CSV文件中的数据到Oracle中,使用Aspose.Cells读取中文字段标题却乱码,表的最后多出几行null记录,而且不是免费的,后来找到了NPOI,顾名思义,就是POI的.NET版本,POI是一套用Java写成的库,能够帮助开发者在没有安装微软Office的情况下读写Office 97-2003的文件,支持的文件格式包括xls, doc,
ppt等。而且是免费的,学了半天,得出一个结论,杀鸡用牛刀。最后决定自已写一个读取及写入CSV文件的代码,效果非常好。现分享受应趣的朋友门。
using System;
using System.Data;
using System.IO; namespace DBUtility
{
public static class CsvHelper
{
/// <summary>
/// 写入CSV文件
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="fileName">文件全名</param>
/// <returns>是否写入成功</returns>
public static Boolean SaveCSV(DataTable dt, string fullFileName)
{
Boolean r = false;
FileStream fs = new FileStream(fullFileName, System.IO.FileMode.Create, System.IO.FileAccess.Write);
StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.Default);
string data = ""; //写出列名称
for (int i = ; i < dt.Columns.Count; i++)
{
data += dt.Columns[i].ColumnName.ToString();
if (i < dt.Columns.Count - )
{
data += ",";
}
}
sw.WriteLine(data); //写出各行数据
for (int i = ; i < dt.Rows.Count; i++)
{
data = "";
for (int j = ; j < dt.Columns.Count; j++)
{
data += dt.Rows[i][j].ToString();
if (j < dt.Columns.Count - )
{
data += ",";
}
}
sw.WriteLine(data);
} sw.Close();
fs.Close(); r = true;
return r;
} /// <summary>
/// 打开CSV 文件
/// </summary>
/// <param name="fileName">文件全名</param>
/// <returns>DataTable</returns>
public static DataTable OpenCSV(string fullFileName)
{
return OpenCSV(fullFileName, , , , , true);
} /// <summary>
/// 打开CSV 文件
/// </summary>
/// <param name="fileName">文件全名</param>
/// <param name="firstRow">开始行</param>
/// <param name="firstColumn">开始列</param>
/// <param name="getRows">获取多少行</param>
/// <param name="getColumns">获取多少列</param>
/// <param name="haveTitleRow">是有标题行</param>
/// <returns>DataTable</returns>
public static DataTable OpenCSV(string fullFileName, Int16 firstRow = , Int16 firstColumn = , Int16 getRows = , Int16 getColumns = , bool haveTitleRow = true)
{
DataTable dt = new DataTable();
FileStream fs = new FileStream(fullFileName, System.IO.FileMode.Open, System.IO.FileAccess.Read);
StreamReader sr = new StreamReader(fs, System.Text.Encoding.Default);
//记录每次读取的一行记录
string strLine = "";
//记录每行记录中的各字段内容
string[] aryLine;
//标示列数
int columnCount = ;
//是否已建立了表的字段
bool bCreateTableColumns = false;
//第几行
int iRow = ; //去除无用行
if (firstRow > )
{
for (int i=; i < firstRow;i++)
{
sr.ReadLine();
}
} // { ",", ".", "!", "?", ";", ":", " " };
string[] separators = { "," };
//逐行读取CSV中的数据
while ((strLine = sr.ReadLine()) != null)
{
strLine = strLine.Trim();
aryLine = strLine.Split(separators, System.StringSplitOptions.RemoveEmptyEntries); if (bCreateTableColumns == false)
{
bCreateTableColumns = true;
columnCount = aryLine.Length;
//创建列
for (int i = firstColumn; i < (getColumns == ? columnCount : firstColumn+ getColumns); i++)
{
DataColumn dc
= new DataColumn(haveTitleRow == true ? aryLine[i] : "COL" + i.ToString());
dt.Columns.Add(dc);
} bCreateTableColumns = true; if (haveTitleRow == true)
{
continue;
}
} DataRow dr = dt.NewRow();
for (int j = firstColumn; j < (getColumns == ? columnCount : firstColumn + getColumns); j++)
{
dr[j - firstColumn] = aryLine[j];
}
dt.Rows.Add(dr); iRow = iRow + ;
if (getRows > )
{
if (iRow > getRows)
{
break;
}
} } sr.Close();
fs.Close();
return dt;
}
}
}