csv公共类
public class CommonReadCVSFile
{
private string m_fileName; //文件名
private Encoding m_encoding; //编码
public CommonReadCVSFile()
{
SliptChar = ',';
}
public Char SliptChar { get; set; }
private byte[] m_fileContent;
public void OpenCSVFile(byte[] fileContent, Encoding encoding)
{
this.m_fileName = string.Empty;
this.m_encoding = encoding;
m_fileContent = fileContent;
}
private List<List<string>> m_AllCsvLines = new List<List<string>>();
public int CsvFile_LoadAnalyze()
{
m_AllCsvLines.Clear();
string[] lines = null;
if (string.IsNullOrEmpty(m_fileName) == false)
{
File.ReadAllLines(m_fileName, m_encoding);
}
else
{
int startIndex = 0;
//0xef, 0xbb, 0xbf UF8 bom头
if (m_fileContent.Length >= 3 && m_fileContent[0] == 0xef && m_fileContent[1] == 0xbb && m_fileContent[2] == 0xbf)
{
startIndex = 3;
}
string str = this.m_encoding.GetString(m_fileContent, startIndex, m_fileContent.Length - startIndex);
lines = str.Split('\n').Select(s => s.Trim()).ToArray();
}
int state = 0; //未读取,未开始
List<string> cells = null;
StringBuilder sb_buffer = new StringBuilder();
for (int i = 0; i < lines.Length; i++)
{
cells = new List<string>(32);
state = Line_Analyze(lines[i], sb_buffer, state, cells, this.SliptChar);
if (string.IsNullOrEmpty(lines[i]) == true)
continue;
if (state == 0)
{//完成一行的分析
m_AllCsvLines.Add(cells);
}
else if (state < 0)
{
throw new Exception("无法分析该CSV文件.");
}
}
if (state != 0)
{
cells.Add(sb_buffer.ToString());
m_AllCsvLines.Add(cells);
}
return m_AllCsvLines.Count;
}
public int SimpleTable_LoadAnalyze()
{
m_AllCsvLines.Clear();
string[] lines = null;
if (string.IsNullOrEmpty(m_fileName) == false)
{
File.ReadAllLines(m_fileName, m_encoding);
}
else
{
string str = this.m_encoding.GetString(m_fileContent);
lines = str.Split('\n').Select(s => s.Trim()).ToArray();
}
List<string> cells = null;
int state = 0;
for (int i = 0; i < lines.Length; i++)
{
cells = new List<string>(32);
state = Line_AnalyzeSimpleTableText(lines[i], this.SliptChar, cells);
if (string.IsNullOrEmpty(lines[i]) == true)
continue;
if (state > 0)
{//完成一行的分析
m_AllCsvLines.Add(cells);
}
else if (state < 0)
{
throw new Exception("无法分析该文件.");
}
}
return m_AllCsvLines.Count;
}
/// <summary>
/// 0按首行标题命名列表的列，否则按标题列自动命名,源方法
/// </summary>
/// <param name="tableTitleRowNumber"></param>
/// <returns></returns>
public DataTable ToDataTable(int tableTitleRowNumber = -1)
{
int colcount = m_AllCsvLines.Max(s => s.Count);
DataTable dt = new DataTable();
if (tableTitleRowNumber < 0)
{
for (int i = 0; i < colcount; i++)
{
dt.Columns.Add("Col" + i.ToString(), typeof(string));
}
foreach (var row in m_AllCsvLines)
{
var dr = dt.NewRow();
for (int i = 0; i < colcount; i++)
{
if (i < row.Count)
{
dr[i] = row[i];
}
else
{
dr[i] = null;
}
}
dt.Rows.Add(dr);
}
}
else
{
if (tableTitleRowNumber >= m_AllCsvLines.Count)
return null;
var li = m_AllCsvLines[tableTitleRowNumber];
int colCount = li.Count;
foreach (var item in li)
{
dt.Columns.Add(item, typeof(string));
}
int index = 0;
foreach (var row in m_AllCsvLines)
{
if (index == tableTitleRowNumber)
{
index++;
continue;
}
var dr = dt.NewRow();
for (int i = 0; i < colcount; i++)
{
if (i < row.Count)
{
dr[i] = row[i];
}
else
{
dr[i] = null;
}
}
dt.Rows.Add(dr);
index++;
}
}
return dt;
}
public DataTable ToDataTables(int tableTitleRowNumber = -1)
{
int colcount = m_AllCsvLines.Max(s => s.Count);
DataTable dt = new DataTable();
if (tableTitleRowNumber < 0)
{
for (int i = 0; i < colcount; i++)
{
dt.Columns.Add("Col" + i.ToString(), typeof(string));
}
foreach (var row in m_AllCsvLines)
{
var dr = dt.NewRow();
for (int i = 0; i < colcount; i++)
{
if (i < row.Count)
{
dr[i] = row[i];
}
else
{
dr[i] = null;
}
}
dt.Rows.Add(dr);
}
}
else
{
if (tableTitleRowNumber >= m_AllCsvLines.Count)
return null;
string[] aryLine = null;
string[] tableHead = null;
string str = "";
int columnCount = 0;
var li = m_AllCsvLines[tableTitleRowNumber];
str = li[0];
tableHead = str.Split(',');
columnCount = tableHead.Length;
//创建列
for (int i = 0; i < columnCount; i++)
{
DataColumn dc = new DataColumn(tableHead[i]);
dt.Columns.Add(dc);
}
int index = 0;
foreach (var row in m_AllCsvLines)
{
if (index == tableTitleRowNumber)
{
index++;
continue;
}
str = row[0];
aryLine = str.Split(',');
var dr = dt.NewRow();
for (int j = 0; j < columnCount; j++)
{
dr[j] = aryLine[j];
}
dt.Rows.Add(dr);
index++;
}
}
return dt;
}
/// <summary>
/// 将json数据转换为string字符串
/// </summary>
/// <param name="tableTitleRowNumber"></param>
/// <returns></returns>
public string ToDataString(int tableTitleRowNumber = -1)
{
int colcount = m_AllCsvLines.Max(s => s.Count);
DataTable dt = new DataTable();
if (tableTitleRowNumber >= m_AllCsvLines.Count)
return null;
string[] aryLine = null;
string[] tableHead = null;
string str = "";
int columnCount = 0;
var li = m_AllCsvLines[tableTitleRowNumber];
str = li[0];
str = str.Replace("\\", "");
return str;
}
public DataTable ToDataTable(int tableTitleRowNumber, Func<List<string>, bool> filerHandle)
{
int colcount = m_AllCsvLines.Max(s => s.Count);
DataTable dt = new DataTable();
if (tableTitleRowNumber < 0)
{
for (int i = 0; i < colcount; i++)
{
dt.Columns.Add("Col" + i.ToString(), typeof(string));
}
foreach (var row in m_AllCsvLines)
{
if (filerHandle != null)
{
if (filerHandle(row) == false)
continue;
}
var dr = dt.NewRow();
for (int i = 0; i < colcount; i++)
{
if (i < row.Count)
{
dr[i] = row[i];
}
else
{
dr[i] = null;
}
}
dt.Rows.Add(dr);
}
}
else
{
if (tableTitleRowNumber >= m_AllCsvLines.Count)
return null;
var li = m_AllCsvLines[tableTitleRowNumber];
int colCount = li.Count;
if (filerHandle != null)
{
if (filerHandle(li) == false)
return null;
}
foreach (var item in li)
{
dt.Columns.Add(item, typeof(string));
}
int index = 0;
foreach (var row in m_AllCsvLines)
{
if (filerHandle != null)
{
if (filerHandle(li) == false)
continue;
}
if (index == tableTitleRowNumber)
{
index++;
continue;
}
var dr = dt.NewRow();
for (int i = 0; i < colcount; i++)
{
if (i < row.Count)
{
dr[i] = row[i];
}
else
{
dr[i] = null;
}
}
dt.Rows.Add(dr);
index++;
}
}
return dt;
}
public void DataTableToCsvString(DataTable dt, bool isShowHeader, StringBuilder stringBuilder)
{
string strTemp = string.Empty;
StringBuilder temp_stringBuilder = new StringBuilder();
if (isShowHeader == true)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
var a = dt.Columns[i].ColumnName;
strTemp = a == null ? string.Empty : a;
if (strTemp.Contains(this.SliptChar) == true)
{
strTemp = "\"" + strTemp.Replace("\"", "\"\"") + "\"";
}
if (temp_stringBuilder.Length > 0)
{
temp_stringBuilder.Append(this.SliptChar);
}
temp_stringBuilder.Append(strTemp);
}
stringBuilder.AppendLine(temp_stringBuilder.ToString());
}
strTemp = string.Empty;
foreach (DataRow row in dt.Rows)
{
temp_stringBuilder.Clear();
for (int i = 0; i < dt.Columns.Count; i++)
{
var a = row[i];
strTemp = a == null ? string.Empty : a.ToString();
if (strTemp.Contains(this.SliptChar) == true)
{
strTemp = "\"" + strTemp.Replace("\"", "\"\"") + "\"";
}
if (temp_stringBuilder.Length > 0)
{
temp_stringBuilder.Append(this.SliptChar);
}
temp_stringBuilder.Append(strTemp);
}
stringBuilder.AppendLine(temp_stringBuilder.ToString());
}
}
public List<string> GetRowCells(int row)
{
if (row < m_AllCsvLines.Count)
return m_AllCsvLines[row];
return null;
}
/// <summary>
/// 行输入分析,对CSV格式严格分析
/// </summary>
/// <param name="line"></param>
/// <param name="sList"></param>
/// <returns></returns>
private static int Line_Analyze(string line, StringBuilder sb_buffer, int state, List<string> sList, char splitChar = ',')
{//分析
//规定CSV
//1.转义字符 " 由 "" 表示
//2.字符串格式字符由"XXXXXXXX"表示, XXXXXX可以包含空格( )或换行(\r\n)，以及逗号(,)
//3.其他格式由字符直接表示 例如纯数字可以不是等，可以不由""包裹
//4.每行数据结束由\r\n分隔，每个Cell数据结束由,分隔
/*分析过程*/
//1.读取1个字符，
//2.验证字符
//2.1 是否是, 如果直接是逗号表示cell值为null
//2.2 是否是" 如果是应进入 字符串匹配模式
//2.2 字符是\r\n 则数据是一个空行
int index = 0;
if (state == 0)
{
state = 0; //=0未开始 ;= 1 普通Cell; =2 首字符" Cell; =3 在字符串Cell里遇见"
sb_buffer.Length = 0;
}
char ch;
while (index < line.Length)
{
ch = line[index];
if (state == 0)
{//行， 首字符
if (char.IsWhiteSpace(ch) == true)
{//空白字符跳过
}
else if (ch == splitChar)
{//找到一个Cell
sList.Add("");
}
else if (ch == '"')
{//字符串Cell状态
state = 2;
}
else
{//普通Cell状态
state = 1;
sb_buffer.Append(ch);
}
index++;
continue;
}
else if (state == 1)
{//普通Cell状态
if (ch == splitChar)
{//Cell结束符
state = 0;
sList.Add(sb_buffer.ToString());
sb_buffer.Length = 0;
}
else
{
sb_buffer.Append(ch);
}
index++;
continue;
}
else if (state == 2)
{//字符串Cell状态
if (ch == '"')
{//字符串Cell状态中的，Cell状态
state = 3; //状态3为转义状态。。
}
else
{
sb_buffer.Append(ch);
}
index++;
continue;
}
else if (state == 3)
{//字符串Cell 里遇到"
int nSpaceCount = 0;
if (ch == splitChar)
{//Cell结束符号,形如 "XXXXXXX",
sb_buffer.Length = sb_buffer.Length - nSpaceCount;//空白符号的截至位
sList.Add(sb_buffer.ToString());
sb_buffer.Length = 0;
state = 0;
}
else if (char.IsWhiteSpace(ch) == true)
{//空白符号则跳过,形如 "XXXXXXX"空白, 也是合法的
nSpaceCount++;
sb_buffer.Append(ch);
}
else if (ch == '"')
{//形如 "XXXX"" 需转义" ，则转义结束
sb_buffer.Append('"');
state = 2; //当前转义结束
}
else
{//形如 "XXXXX"XX，则前转义"无效.字符加入到sb, 状态回复到 字符串Cell状态,
sb_buffer.Append(ch);
state = 2;
}
index++;
continue;
}
}
if (state == 0)
{
sList.Add(null);
return 0; //正常结束
}
else if (state == 1)
{//强制结束
sList.Add(sb_buffer.ToString());
return 0;
}
else if (state == 2)
{//字符串未完结
return 2;//需要从下一行接续
}
else if (state == 3)
{//强制完成 形如"XXXXXXXX"
sList.Add(sb_buffer.ToString());
return 0;
}
return -1; //错误的状态
}
/// <summary>
/// 行输入分析,只对每行数据做分割字符的分析
/// </summary>
/// <param name="line"></param>
/// <param name="sList"></param>
/// <returns></returns>
private static int Line_AnalyzeSimpleTableText(string line, char splitChar, List<string> sList)
{//分析
//简易文本表格
//每一行由\t进行数据列分割
if (string.IsNullOrEmpty(line) == true)
{
sList.Add(string.Empty);
return 1;
}
var a = line.Split(splitChar).Select(s => s.Trim()).ToList();
sList.AddRange(a);
return sList.Count;
}
}
调用
byte[] bytes = null;
using (FileStream fs = new FileStream("csv文件路径", FileMode.Open, FileAccess.Read, FileShare.Read))
{
// 读取文件的 byte[]
bytes = new byte[fs.Length];
fs.Read(bytes, 0, bytes.Length);
}
CommonReadCVSFile CVSFileReader = new CommonReadCVSFile();
DataTable csvDt;
CVSFileReader.SliptChar = ',';
CVSFileReader.OpenCSVFile(bytes, System.Text.Encoding.UTF8);
CVSFileReader.CsvFile_LoadAnalyze();
var c = CVSFileReader.CsvFile_LoadAnalyze();
if (c == 0)
{
throw new Exception("csv no data.");
}
csvDt = CVSFileReader.ToDataTable(0);
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
