1.csv相关
public static class CsvHelper
{
/// <summary>
/// 根据csv路径获取datatable
/// </summary>
/// <param name="csvPath"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public static DataTable GetDataTable(string csvPath, out string errMsg)
{
var result = GetDt(csvPath, out errMsg, true);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
return result;
}
private static DataTable GetDt(string csvPath, out string errMsg, bool hasTitle = false)
{
var dt = new DataTable();
try
{
//将数据读入到DataTable中
if (!File.Exists(csvPath))
{
errMsg = "找不到csv文件" + csvPath;
return null;
}
using (StreamReader sr = new StreamReader(csvPath))
{
string line = sr.ReadLine();
if (!string.IsNullOrEmpty(line))
{
var columes = line.Split(',');
//生成列头
for (var i = ; i < columes.Length; i++)
{
var name = "column" + i;
if (hasTitle)
{
var txt = columes[i];
if (!string.IsNullOrWhiteSpace(txt))
{
name = txt;
}
}
while (dt.Columns.Contains(name)) name = name + "_1"; //重复行名称会报错。
dt.Columns.Add(new DataColumn(name, typeof(string)));
} if (!hasTitle)
{
var dr = dt.NewRow();
for (var iCol = ; iCol < columes.Length; iCol++)
{
var range = columes[iCol];
dr[iCol] = range;
}
dt.Rows.Add(dr);
}
line = sr.ReadLine();
//生成行数据
while (!string.IsNullOrWhiteSpace(line))
{
columes = line.Split(',');
var dr = dt.NewRow();
for (var iCol = ; iCol < columes.Length; iCol++)
{
var range = columes[iCol];
dr[iCol] = range;
}
dt.Rows.Add(dr);
line = sr.ReadLine();
}
}
}
errMsg = "";
return dt;
}
catch (Exception ex)
{
LogHelper.Log(ex);
errMsg = ex.Message;
return null;
}
} /// <summary>
/// 将dataTable保存到csv文件
/// </summary>
/// <param name="dt"></param>
/// <param name="csvPath"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public static bool SaveDataTable(DataTable dt, string csvPath, out string errMsg)
{
var result = SaveDt(dt, csvPath, out errMsg);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
return result;
} private static bool SaveDt(DataTable dt, string csvPath, out string errMsg)
{
try
{
//将数据读入到DataTable中
using (StreamWriter sr = new StreamWriter(csvPath, false, Encoding.Default))
{
var iRowCount = dt.Rows.Count;
var iColCount = dt.Columns.Count;
//生成列头
StringBuilder firstRow = new StringBuilder();
for (var i = ; i < iColCount; i++)
{
firstRow.Append(dt.Columns[i].ColumnName + ",");
}
sr.WriteLine(firstRow.ToString().TrimEnd(','));
for (var iRow = ; iRow < iRowCount; iRow++)
{
StringBuilder otherRow = new StringBuilder();
for (var iCol = ; iCol < iColCount; iCol++)
{
otherRow.Append(dt.Rows[iRow][iCol] + ",");
}
sr.WriteLine(otherRow.ToString().TrimEnd(','));
}
errMsg = "";
return true;
}
}
catch (Exception ex)
{
LogHelper.Log(ex);
errMsg = ex.Message;
return false;
}
}
}
2.datatable相关
public class DataTableHelper
{ /// <summary>
/// DataTable转化为List集合
/// </summary>
/// <typeparam name="T">实体对象</typeparam>
/// <param name="dt">datatable表</param>
/// <returns>返回list集合</returns>
public static List<T> TableToList<T>(DataTable dt )
{
List<T> list = new List<T>();
Type type = typeof(T);
foreach (DataRow row in dt.Rows)
{
PropertyInfo[] pArray = type.GetProperties(); //集合属性数组
T entity = Activator.CreateInstance<T>(); //新建对象实例
foreach (PropertyInfo p in pArray)
{
if (!dt.Columns.Contains(p.Name) || row[p.Name] == null || row[p.Name] == DBNull.Value)
{
continue; //DataTable列中不存在集合属性或者字段内容为空则，跳出循环，进行下个循环
}
try
{
var obj = Convert.ChangeType(row[p.Name], p.PropertyType);//类型强转，将table字段类型转为集合字段类型
p.SetValue(entity, obj, null);
}
catch (Exception)
{ }
}
list.Add(entity);
}
return list;
} /// <summary>
/// List集合转DataTable
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="list">传入集合</param>
/// <returns>返回datatable结果</returns>
public static DataTable ListToTable<T>(List<T> list )
{
Type tp = typeof(T);
PropertyInfo[] proInfos = tp.GetProperties();
DataTable dt = new DataTable();
foreach (var item in proInfos)
{
dt.Columns.Add(item.Name, typeof(string)); //添加列明及对应类型
}
foreach (var item in list)
{
DataRow dr = dt.NewRow();
foreach (var proInfo in proInfos)
{
object obj = proInfo.GetValue(item,null);
if (obj == null)
{
continue;
}
if ( proInfo.PropertyType == typeof(DateTime) && Convert.ToDateTime(obj) < Convert.ToDateTime("1753-01-01"))
{
continue;
}
dr[proInfo.Name] = obj;
}
dt.Rows.Add(dr);
}
return dt;
}
}
3.excel相关
public static class ExcelHelper
{
/// <summary>
/// 根据excel路径获取datatable
/// </summary>
/// <param name="excelPath"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public static DataTable GetDataTable(string excelPath, out string errMsg)
{
var result = GetDt(excelPath, out errMsg, true);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
return result;
}
private static DataTable GetDt(string excelFilePath, out string errMsg, bool hasTitle = false)
{
Microsoft.Office.Interop.Excel.Application app = null;
Microsoft.Office.Interop.Excel._Workbook workbook = null;
var dt = new DataTable();
object oMissiong = System.Reflection.Missing.Value;
try
{
app = new Microsoft.Office.Interop.Excel.Application(); workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
var sheets = workbook.Worksheets; //将数据读入到DataTable中
var worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.Item[]; //读取第一张表
if (worksheet == null)
{
errMsg = "打开excel失败，请确保excel安装环境正确";
return null;
} var iRowCount = worksheet.UsedRange.Rows.Count;
var iColCount = worksheet.UsedRange.Columns.Count;
//生成列头
for (var i = ; i < iColCount; i++)
{
var name = "column" + i;
if (hasTitle)
{
var txt = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[, i + ]).Text.ToString();
if (!string.IsNullOrWhiteSpace(txt)) name = txt;
}
while (dt.Columns.Contains(name)) name = name + "_1"; //重复行名称会报错。
dt.Columns.Add(new DataColumn(name, typeof(string)));
}
//生成行数据
var rowIdx = hasTitle ? : ;
for (var iRow = rowIdx; iRow <= iRowCount; iRow++)
{
var dr = dt.NewRow();
for (var iCol = ; iCol <= iColCount; iCol++)
{
var range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
dr[iCol - ] = (range.Value2 == null) ? "" : range.Text.ToString();
}
dt.Rows.Add(dr);
}
errMsg = "";
return dt;
}
catch (Exception ex)
{
LogHelper.Log(ex);
errMsg = ex.Message;
return null;
}
finally
{
if (workbook != null)
{
workbook.Close(false, oMissiong, oMissiong);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
}
if (app != null)
{
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
}
}
} /// <summary>
/// 将excel保存到文件
/// </summary>
/// <param name="dt"></param>
/// <param name="excelPath"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public static bool SaveDataTable(DataTable dt, string excelPath, out string errMsg)
{
var result = SaveDt(dt, excelPath, out errMsg);
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
return result;
} private static bool SaveDt(DataTable dt, string excelFilePath, out string errMsg)
{
Microsoft.Office.Interop.Excel.Application app = null;
Microsoft.Office.Interop.Excel._Workbook workbook = null;
object oMissiong = System.Reflection.Missing.Value;
try
{
app = new Microsoft.Office.Interop.Excel.Application
{
DisplayAlerts = false
};
workbook = app.Workbooks.Add(true); //将数据读入到DataTable中
var worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Item[]; //读取第一张表
if (worksheet == null)
{
errMsg = "打开excel失败，请确保excel安装环境正确";
return false;
}
var iRowCount = dt.Rows.Count;
var iColCount = dt.Columns.Count;
//生成列头
for (var i = ; i < iColCount; i++)
{
worksheet.Cells[, i + ] = dt.Columns[i].ColumnName;
}
for (var iRow = ; iRow <= iRowCount; iRow++)
{
for (var iCol = ; iCol <= iColCount; iCol++)
{
worksheet.Cells[iRow, iCol] = dt.Rows[iRow - ][iCol - ].ToString();
}
}
errMsg = "";
return true;
}
catch (Exception ex)
{
LogHelper.Log(ex);
errMsg = ex.Message;
return false;
}
finally
{
if (workbook != null)
{
workbook.Close(true, excelFilePath, oMissiong);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
}
if (app != null)
{
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
}
}
}
}
感谢阅读