using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
using System.Data;
|
using System.IO;
|
using XImagingXhandler.XDAL;
|
using NPOI.SS.Formula.Eval;
|
using NPOI.SS.UserModel;
|
using NPOI.SS.Util;
|
using System.Text.RegularExpressions;
|
using NPOI.HSSF.UserModel;
|
using NPOI.XSSF.UserModel;
|
using System.Reflection;
|
using XCommon.Log;
|
using System.ComponentModel;
|
using System.Collections.ObjectModel;
|
using System.Windows;
|
using DataEntity;
|
|
namespace XHandler.Class
|
{
|
public class ExcelAndCsvHelper
|
{
|
/// <summary>
|
/// 将excel导入到datatable
|
/// </summary>
|
/// <param name="filePath">excel路径</param>
|
/// <param name="isColumnName">第一行是否是列名</param>
|
/// <returns>返回datatable</returns>
|
public static DataTable GetDataTableFromExcelFile(string filePath, bool isColumnName)
|
{
|
DataTable dataTable = null;
|
FileStream fs = null;
|
DataColumn column = null;
|
DataRow dataRow = null;
|
IWorkbook workbook = null;
|
ISheet sheet = null;
|
IRow row = null;
|
ICell cell = null;
|
int startRow = 0;
|
try
|
{
|
using (fs = File.OpenRead(filePath))
|
{
|
//workbook = WorkbookFactory.Create(fs);
|
// 2007版本
|
if (filePath.IndexOf(".xlsx") > 0)
|
workbook = new XSSFWorkbook(fs);
|
// 2003版本
|
else if (filePath.IndexOf(".xls") > 0)
|
workbook = new HSSFWorkbook(fs);
|
|
if (workbook != null)
|
{
|
sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet
|
dataTable = new DataTable();
|
if (sheet != null)
|
{
|
int rowCount = sheet.LastRowNum;//总行数
|
if (rowCount > 0)
|
{
|
IRow firstRow = sheet.GetRow(0);//第一行
|
int cellCount = firstRow.LastCellNum;//列数
|
|
//构建datatable的列
|
if (isColumnName)
|
{
|
startRow = 1;//如果第一行是列名，则从第二行开始读取
|
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
|
{
|
cell = firstRow.GetCell(i);
|
if (cell != null)
|
{
|
if (cell.StringCellValue != null)
|
{
|
column = new DataColumn(cell.StringCellValue);
|
dataTable.Columns.Add(column);
|
}
|
}
|
}
|
}
|
else
|
{
|
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
|
{
|
column = new DataColumn("column" + (i + 1));
|
dataTable.Columns.Add(column);
|
}
|
}
|
|
//填充行
|
for (int i = startRow; i <= rowCount; ++i)
|
{
|
row = sheet.GetRow(i);
|
if (row == null) continue;
|
|
dataRow = dataTable.NewRow();
|
for (int j = row.FirstCellNum; j < cellCount; ++j)
|
{
|
cell = row.GetCell(j);
|
if (cell == null)
|
{
|
dataRow[j] = "";
|
}
|
else
|
{
|
//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
|
switch (cell.CellType)
|
{
|
case CellType.Blank:
|
dataRow[j] = "";
|
break;
|
case CellType.Numeric:
|
short format = cell.CellStyle.DataFormat;
|
//对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
|
if (format == 14 || format == 31 || format == 57 || format == 58)
|
dataRow[j] = cell.DateCellValue;
|
else
|
dataRow[j] = cell.NumericCellValue;
|
break;
|
case CellType.String:
|
dataRow[j] = cell.StringCellValue;
|
break;
|
}
|
}
|
}
|
dataTable.Rows.Add(dataRow);
|
}
|
}
|
}
|
}
|
}
|
return dataTable;
|
}
|
catch (Exception ex)
|
{
|
if (fs != null)
|
{
|
fs.Close();
|
}
|
LoggerHelper.ErrorLog("ERROR:", ex);
|
return null;
|
}
|
}
|
|
/// <summary>
|
/// 获取当前单元格所在的合并单元格的位置
|
/// </summary>
|
/// <param name="sheet">sheet表单</param>
|
/// <param name="rowIndex">行索引 0开始</param>
|
/// <param name="colIndex">列索引 0开始</param>
|
/// <param name="start">合并单元格左上角坐标</param>
|
/// <param name="end">合并单元格右下角坐标</param>
|
/// <returns>返回false表示非合并单元格</returns>
|
private static bool IsMergeCell(ISheet sheet, int rowIndex, int colIndex, out Point start, out Point end)
|
{
|
bool result = false;
|
start = new Point(0, 0);
|
end = new Point(0, 0);
|
if ((rowIndex < 0) || (colIndex < 0)) return result;
|
int regionsCount = sheet.NumMergedRegions;
|
for (int i = 0; i < regionsCount; i++)
|
{
|
CellRangeAddress range = sheet.GetMergedRegion(i);
|
//sheet.IsMergedRegion(range);
|
if (rowIndex >= range.FirstRow && rowIndex <= range.LastRow && colIndex >= range.FirstColumn && colIndex <= range.LastColumn)
|
{
|
start = new Point(range.FirstRow, range.FirstColumn);
|
end = new Point(range.LastRow, range.LastColumn);
|
result = true;
|
break;
|
}
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 将datatable导出到excel
|
/// </summary>
|
/// <param name="dt">要导出的数据</param>
|
/// <param name="filePath">文件路径 默认为 D:/myxls.xls</param>
|
/// <returns></returns>
|
public static bool WriteDataTableToExcelFile(DataTable dt, string filePath = "c:/myxls.xls",string sheetName= "Sheet1")
|
{
|
bool result = false;
|
IWorkbook workbook = null;
|
FileStream fs = null;
|
IRow row = null;
|
ISheet sheet = null;
|
ICell cell = null;
|
try
|
{
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
workbook = new HSSFWorkbook();
|
sheet = workbook.CreateSheet(sheetName);//创建一个名称为Sheet1的表
|
int rowCount = dt.Rows.Count;//行数
|
int columnCount = dt.Columns.Count;//列数
|
|
//设置列头
|
row = sheet.CreateRow(0);//excel第一行设为列头
|
for (int c = 0; c < columnCount; c++)
|
{
|
cell = row.CreateCell(c);
|
cell.SetCellValue(dt.Columns[c].ColumnName);
|
}
|
|
//设置每行每列的单元格,
|
for (int i = 0; i < rowCount; i++)
|
{
|
row = sheet.CreateRow(i + 1);
|
for (int j = 0; j < columnCount; j++)
|
{
|
cell = row.CreateCell(j);//excel第二行开始写入数据
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
}
|
using (fs = File.OpenWrite(filePath))
|
{
|
workbook.Write(fs);//向打开的这个xls文件中写入数据
|
result = true;
|
}
|
}
|
return result;
|
}
|
catch (Exception ex)
|
{
|
LoggerHelper.ErrorLog("ERROR:", ex);
|
if (fs != null)
|
{
|
fs.Close();
|
}
|
return false;
|
}
|
}
|
|
|
/// <summary>
|
/// 将DataTable中数据写入到CSV文件中
|
/// </summary>
|
/// <param name="dt">提供保存数据的DataTable</param>
|
/// <param name="fileName">CSV的文件路径</param>
|
public static void WriteDataTableToCSV(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.Default);
|
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="fileName">CSV文件路径</param>
|
/// <param name="cansort">是否排序</param>
|
/// <returns>返回读取了CSV数据的DataTable</returns>
|
public static DataTable GetDataTableFromCsvFile(string filePath,bool cansort=true)
|
{
|
try
|
{
|
|
Encoding encoding = Encoding.UTF8;// Common.GetType(filePath); //Encoding.ASCII;//
|
DataTable dt = new DataTable();
|
FileStream fs = new FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
|
|
//StreamReader sr = new StreamReader(fs, Encoding.UTF8);
|
StreamReader sr = new StreamReader(fs, encoding);
|
//string fileContent = sr.ReadToEnd();
|
//encoding = sr.CurrentEncoding;
|
//记录每次读取的一行记录
|
string strLine = "";
|
//记录每行记录中的各字段内容
|
string[] aryLine = null;
|
string[] tableHead = null;
|
//标示列数
|
int columnCount = 0;
|
//标示是否是读取的第一行
|
bool IsFirst = true;
|
//逐行读取CSV中的数据
|
while ((strLine = sr.ReadLine()) != null)
|
{
|
//strLine = Common.ConvertStringUTF8(strLine, encoding);
|
//strLine = Common.ConvertStringUTF8(strLine);
|
|
if (IsFirst == true)
|
{
|
tableHead = CSVstrToArry(strLine);
|
IsFirst = false;
|
columnCount = tableHead.Length;
|
//创建列
|
for (int i = 0; i < columnCount; i++)
|
{
|
DataColumn dc = new DataColumn(tableHead[i]);
|
dt.Columns.Add(dc);
|
}
|
}
|
else
|
{
|
aryLine = CSVstrToArry(strLine);
|
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)
|
{
|
if (cansort)
|
{
|
dt.DefaultView.Sort = tableHead[0] + " " + "asc";
|
}
|
}
|
|
sr.Close();
|
fs.Close();
|
return dt;
|
}
|
catch (Exception ex)
|
{
|
LoggerHelper.ErrorLog("ERROR:", ex);
|
return null;
|
}
|
}
|
|
/// <summary>
|
/// 跳过引号中的逗号,进行逗号分隔(字段内容中的逗号不参与分隔)
|
/// </summary>
|
/// <param name="strLine"></param>
|
/// <returns></returns>
|
public static string[] CSVstrToArry(string splitStr)
|
{
|
var newstr = string.Empty;
|
List<string> sList = new List<string>();
|
|
bool isSplice = false;
|
string[] array = splitStr.Split(new char[] { ',' });
|
foreach (var str in array)
|
{
|
if (!string.IsNullOrEmpty(str) && str.IndexOf('"') > -1)
|
{
|
var firstchar = str.Substring(0, 1);
|
var lastchar = string.Empty;
|
if (str.Length > 0)
|
{
|
lastchar = str.Substring(str.Length - 1, 1);
|
}
|
if (firstchar.Equals("\"") && !lastchar.Equals("\""))
|
{
|
isSplice = true;
|
}
|
if (lastchar.Equals("\""))
|
{
|
if (!isSplice)
|
newstr += str;
|
else
|
newstr = newstr + "," + str;
|
|
isSplice = false;
|
}
|
}
|
else
|
{
|
if (string.IsNullOrEmpty(newstr))
|
newstr += str;
|
}
|
|
if (isSplice)
|
{
|
//添加因拆分时丢失的逗号
|
if (string.IsNullOrEmpty(newstr))
|
newstr += str;
|
else
|
newstr = newstr + "," + str;
|
}
|
else
|
{
|
sList.Add(newstr.Replace("\"", "").Trim());//去除字符中的双引号和首尾空格
|
newstr = string.Empty;
|
}
|
}
|
return sList.ToArray();
|
}
|
|
/// <summary>
|
/// 把list 转化为DataTable
|
/// </summary>
|
/// <param name="newObj">List集合</param>
|
/// <returns>DataTable数据集</returns>
|
public static DataTable ListFormatToDataTable(ObservableCollection<Object> newObj)
|
{
|
|
//= new ObservableCollection<Object>();
|
DataTable dataTable= new DataTable();
|
if (newObj!=null&&newObj.Count > 0)
|
{
|
var newObjClazz = newObj[0].GetType();
|
PropertyInfo[] fields = newObjClazz.GetProperties(BindingFlags.Public | BindingFlags.Instance);
|
|
//创建表头
|
foreach (PropertyInfo field in fields)
|
{
|
Object newValue = field.GetValue(newObj[0]);
|
if (newValue != null)
|
{
|
DescriptionAttribute descriptionAttribute = field.GetCustomAttribute(typeof(DescriptionAttribute)) as DescriptionAttribute;
|
string keyName = descriptionAttribute?.Description ?? field.Name;
|
//diffMap.Add(keyName, " 值为 " + newValue);
|
DataColumn dataColumn = new DataColumn();
|
dataColumn.ColumnName = keyName;
|
if (keyName.Equals("源执行结果") || keyName.Equals("目标执行结果"))
|
{
|
dataColumn.DataType = typeof(string);
|
}
|
else
|
{
|
dataColumn.DataType = newValue.GetType();
|
}
|
dataTable.Columns.Add(dataColumn);
|
}
|
}
|
|
//创建表数据
|
foreach (var item in newObj)
|
{
|
DataRow dataRow = dataTable.NewRow();
|
newObjClazz = item.GetType();
|
fields = newObjClazz.GetProperties(BindingFlags.Public | BindingFlags.Instance);
|
foreach (PropertyInfo field in fields)
|
{
|
DescriptionAttribute descriptionAttribute = field.GetCustomAttribute(typeof(DescriptionAttribute)) as DescriptionAttribute;
|
string keyName = descriptionAttribute?.Description ?? field.Name;
|
|
Object newValue = field.GetValue(item);
|
|
if (newValue != null)
|
{
|
if (keyName.Equals("源执行结果") || keyName.Equals("目标执行结果"))
|
{
|
if((int)newValue==0)
|
{
|
dataRow[keyName] = "失败";
|
}
|
else
|
{
|
dataRow[keyName] = "成功";
|
}
|
}
|
else
|
{
|
dataRow[keyName] = newValue;
|
}
|
}
|
}
|
|
dataTable.Rows.Add(dataRow);
|
}
|
}
|
|
return dataTable;
|
}
|
|
public static ObservableCollection<Object> ExampleFormatToObject(ObservableCollection<ExperimentRunDetailModel> experimentRunDetailModels)
|
{
|
ObservableCollection<Object> observableCollection= new ObservableCollection<Object>();
|
foreach(var item in experimentRunDetailModels)
|
{
|
Object obj= item;
|
observableCollection.Add(obj);
|
}
|
return observableCollection;
|
}
|
}
|
|
}
|
