public static class CSVFileHelper
{
public static string ToHtmlTable(this DataTable target)
{
return DataTableToHtmlTable(target);
}
public static string ToRootExcelFile(this DataTable target,string _fileName)
{
return SaveDataTableToRootExcelFile(_fileName, target);
}
public static string DataTableToHtmlTable(DataTable sourceData)
{
//
var sbHtml = new StringBuilder();
sbHtml.Append("<table border='1' cellspacing='0' cellpadding='0'>");
sbHtml.Append("<tr>");
DataColumnCollection columns = sourceData.Columns;
foreach (DataColumn col in columns)
{
sbHtml.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", col.ColumnName);
}
sbHtml.Append("</tr>");
foreach (DataRow row in sourceData.Rows)
{
sbHtml.Append("<tr>");
foreach (DataColumn col in columns)
{
sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", row[col]);
}
sbHtml.Append("</tr>");
}
sbHtml.Append("</table>");
return sbHtml.ToString();
}
/// <summary>
///
/// </summary>
/// <param name="_fileName"></param>
/// <param name="sourceData"></param>
/// <returns></returns>
public static string SaveDataTableToRootExcelFile(string _fileName,DataTable sourceData)
{
string FileName = _fileName;
string dir = HttpRuntime.AppDomainAppPath.ToString() + "/SaveExcelFile/";
FileName = dir + FileName;
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
int numWriter = 1000;
int numRecord = sourceData.Rows.Count;
var sbHtml = new StringBuilder();
sbHtml.Append("<table border='1' cellspacing='0' cellpadding='0'>");
sbHtml.Append("<tr>");
DataColumnCollection columns = sourceData.Columns;
foreach (DataColumn col in columns)
{
sbHtml.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", col.ColumnName);
}
sbHtml.Append("</tr>");
using (FileStream fs = File.Open(FileName, FileMode.CreateNew, FileAccess.Write))
{
using (StreamWriter sw = new StreamWriter(fs))
{
try
{
sw.Write(sbHtml.ToString());
sbHtml.Clear();
int i = 0;
foreach (DataRow row in sourceData.Rows)
{
sbHtml.Append("<tr>");
foreach (DataColumn col in columns)
{
sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", row[col]);
}
sbHtml.Append("</tr>");
i++;
if (i == 1000)
{
i = 0;
sw.Write(sbHtml.ToString());
sbHtml.Clear();
}
}
sw.Write("</table>");
sw.Close();
fs.Close();
}
catch (Exception ex)
{
sw.Close();
fs.Close();
}
}
}
return FileName;
}
/// <summary>
/// 将DataTable中数据写入到CSV文件中
/// </summary>
/// <param name="dt">提供保存数据的DataTable</param>
/// <param name="fullPath">CSV的文件路径</param>
public static void SaveCSV(DataTable dt, string fullPath)
{
FileInfo fi = new FileInfo(fullPath);
if (!fi.Directory.Exists)
{
fi.Directory.Create();
}
FileStream fs = new FileStream(fullPath, System.IO.FileMode.Create, System.IO.FileAccess.Write);
StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.UTF8);
string data = "";
//写出列名称
for (int i = 0; i < dt.Columns.Count; i++)
{
data += dt.Columns[i].ColumnName.ToString();
if (i < dt.Columns.Count - 1)
{
data += ",";
}
}
sw.WriteLine(data);
//写出各行数据
for (int i = 0; i < dt.Rows.Count; i++)
{
data = "";
for (int j = 0; j < dt.Columns.Count; j++)
{
string 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();
}
/// <summary>
/// 将CSV文件的数据读取到DataTable中
/// </summary>
/// <param name="filePath">CSV文件路径</param>
/// <returns>返回读取了CSV数据的DataTable</returns>
public static DataTable OpenCSV(string filePath)
{
Encoding encoding = Encoding.GetEncoding("GB2312");
DataTable dt = new DataTable();
FileStream fs = new FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
StreamReader sr = new StreamReader(fs, encoding);
//记录每次读取的一行记录
string strLine = "";
//记录每行记录中的各字段内容
string[] aryLine = null;
string[] tableHead = null;
//标示列数
int columnCount = 0;
//标示是否是读取的第一行
bool IsFirst = true;
//逐行读取CSV中的数据
while ((strLine = sr.ReadLine()) != null)
{
if (IsFirst == true)
{
tableHead = strLine.Split(',');
IsFirst = false;
columnCount = tableHead.Length;
//创建列
for (int i = 0; i < columnCount; i++)
{
DataColumn dc = new DataColumn(tableHead[i]);
dt.Columns.Add(dc);
}
}
else
{
aryLine = strLine.Split(',');
DataRow dr = dt.NewRow();
for (int j = 0; j < columnCount; j++)
{
dr[j] = aryLine[j];
}
dt.Rows.Add(dr);
}
}
if (aryLine != null && aryLine.Length > 0)
{
dt.DefaultView.Sort = tableHead[0] + " " + "asc";
}
sr.Close();
fs.Close();
return dt;
}
}
