/*************************************************
* 描述：
*
* Author：ys
* Date：2023/7/31 17:45:03
* Update：
* ************************************************/
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms.VisualStyles;
using System.Windows.Forms;
using static Sunny.UI.UIDataGridView;
using Sunny.UI.Win32;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.Data;
using NPOI.SS.Formula.Eval;
using System.Text.RegularExpressions;
using System.Reflection;
using System.ComponentModel;
namespace Wd.Common
{
public class ExcelHelper
{
#region 读取excel
/// <summary>
/// 根据Excel和Sheet返回DataTable
/// </summary>
/// <param name="filePath">Excel文件地址</param>
/// <param name="sheetIndex">Sheet索引</param>
/// <returns>DataTable</returns>
public static DataTable GetDataTableByExcelPath(string filePath, int sheetIndex)
{
return GetDataSetByExcelPath(filePath, sheetIndex).Tables[0];
}
/// <summary>
/// 根据Excel返回DataSet
/// </summary>
/// <param name="filePath">Excel文件地址</param>
/// <param name="sheetIndex">Sheet索引，可选，默认返回所有Sheet</param>
/// <returns>DataSet</returns>
public static DataSet GetDataSetByExcelPath(string filePath, int? sheetIndex = null)
{
DataSet ds = new DataSet();
IWorkbook fileWorkbook;
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
if (filePath.Last() == 's')
{
try
{
fileWorkbook = new HSSFWorkbook(fs);
}
catch (Exception ex)
{
//logger.Error("打开Excel文件失败！", ex);
throw ex;
}
}
else
{
try
{
fileWorkbook = new XSSFWorkbook(fs);
}
catch
{
fileWorkbook = new HSSFWorkbook(fs);
}
}
}
for (int i = 0; i < fileWorkbook.NumberOfSheets; i++)
{
if (sheetIndex != null && sheetIndex != i)
continue;
DataTable dt = new DataTable();
ISheet sheet = fileWorkbook.GetSheetAt(i);
// 表名
dt.TableName = sheet.SheetName;
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int j = 0; j < header.LastCellNum; j++)
{
object obj = GetValueTypeForXLS(header.GetCell(j) as ICell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + j.ToString()));
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(j);
}
//数据
IEnumerator rows = sheet.GetEnumerator();
int RowIndex = sheet.FirstRowNum + 1;
while (rows.MoveNext())
{
if (sheet.GetRow(RowIndex) == null)
{
break;
}
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int K in columns)
{
dr[K] = GetValueTypeForXLS(sheet.GetRow(RowIndex).GetCell(K) as ICell);
if (dr[K] != null && dr[K].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
RowIndex++;
}
ds.Tables.Add(dt);
}
return ds;
}
/// <summary>
/// 根据单元格将内容返回为对应类型的数据
/// </summary>
/// <param name="cell">单元格</param>
/// <returns>数据</returns>
private static object GetValueTypeForXLS(ICell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
if (DateUtil.IsCellDateFormatted(cell))
{
return cell.DateCellValue.ToString("yyyy/MM/dd hh:mm:ss.fff");
}
else
{
return cell.NumericCellValue;
}
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
switch (cell.CachedFormulaResultType)
{
case CellType.Boolean:
return cell.BooleanCellValue;
case CellType.Error:
return ErrorEval.GetText(cell.ErrorCellValue);
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
{
return cell.DateCellValue.ToString("yyyy/MM/dd");
}
else
{
return cell.NumericCellValue;
}
case CellType.String:
string str = cell.StringCellValue;
if (!string.IsNullOrEmpty(str))
{
return str.ToString().Trim();
}
else
{
return string.Empty;
}
case CellType.Unknown:
case CellType.Blank:
default:
return string.Empty;
}
default:
return "=" + cell.CellFormula;
}
}
#endregion
#region 实体集合保存Excel，Excel读取到L实体集合
/// <summary>
/// 保存Excel文件
/// </summary>
/// <param name="pathExcelName">保存路径+Excel文件名</param>
/// <param name="sheetName">Sheet工作表名</param>
/// <param name="data">实体类对象</param>
public static void ExportExcelByList<T>(string pathExcelName, string sheetName, List<T> data)
{
//创建一个Excel文档
IWorkbook workBook = new HSSFWorkbook();
//创建一个工作表Sheet
ISheet sheet = workBook.CreateSheet(sheetName);
sheet.DefaultColumnWidth = 50;
int rowNum = 0;
//LastRowNum记录当前可用写入的行索引
var row = sheet.CreateRow(sheet.LastRowNum);
//获取这个实体对象的所有属性
PropertyInfo[] preInfo = typeof(T).GetProperties();
foreach (var item in preInfo)
{
//获取当前属性的自定义特性列表
object[] objPres = item.GetCustomAttributes(typeof(DescriptionAttribute), true);
if (objPres.Length > 0)
{
for (int i = 0; i < objPres.Length; i++)
{
//创建行，将当前自定义特性写入
row.CreateCell(rowNum).SetCellValue(((DescriptionAttribute)objPres[i]).Description);
rowNum++;
}
}
}
int j = sheet.LastRowNum + 1, columnNum = 0;
foreach (var item in data)
{
columnNum = 0;
row = sheet.CreateRow(j++);
//获取当前对象的属性列表
var itemProps = item.GetType().GetProperties();
foreach (var itemPropSub in itemProps)
{
//获取当前对象特性中的自定义特性[Description("自定义特性")]
var objs = itemPropSub.GetCustomAttributes(typeof(DescriptionAttribute), true);
if (objs.Length > 0)
{
//将当前对象的特性值，插入当前行的第n列单元格
row.CreateCell(columnNum).SetCellValue(itemPropSub.GetValue(item, null) == null ? "" : itemPropSub.GetValue(item, null).ToString());
columnNum++;
}
}
}
//文件流写入
using (MemoryStream ms = new MemoryStream())
{
workBook.Write(ms);
using (FileStream fs = new FileStream(pathExcelName, FileMode.Create, FileAccess.Write))
{
ms.WriteTo(fs);
}
ms.Flush();
ms.Position = 0;
workBook.Close();
}
}
#endregion 实体集合与Excel互相转换
#region DataTable导出Excel 和Excel读取到DataTabel
/// <summary>
/// DataTabel导出excel
/// </summary>
/// <param name="tabelDatas"></param>
/// <param name="excelPathAll"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public static bool ExportExcelByDataTabel(string excelPathAll, DataTable tabelDatas, out string errMsg)
{
errMsg = string.Empty;
if (string.IsNullOrEmpty(excelPathAll))
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
//设置文件标题
saveFileDialog.Title = "导出Excel文件";
//设置文件类型
saveFileDialog.Filter = "Microsoft Office Excel 工作簿(*.xls)|*.xls";
//设置默认文件类型显示顺序
saveFileDialog.FilterIndex = 1;
//是否自动在文件名中添加扩展名
saveFileDialog.AddExtension = true;
//是否记忆上次打开的目录
saveFileDialog.RestoreDirectory = true;
//设置默认文件名
//saveFileDialog.FileName = "";
//按下确定选择的按钮
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
//获得文件路径
excelPathAll = saveFileDialog.FileName.ToString();
}
else
{
errMsg = "没有获取到保存路径";
return false;
}
}
string myDateFormat = "yyyy-MM-dd HH:mm:ss";
XSSFWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IRow rowHeader = sheet.CreateRow(0);
for (int i = 0; i < tabelDatas.Columns.Count; i++)
{
DataColumn column = tabelDatas.Columns[i];
rowHeader.CreateCell(i).SetCellValue(column.Caption);
}
short decimalformat = HSSFDataFormat.GetBuiltinFormat("0.00");
short dateformat = wb.CreateDataFormat().GetFormat(myDateFormat);
ICellStyle styleDecimal = wb.CreateCellStyle();
styleDecimal.DataFormat = decimalformat;
ICellStyle styleDate = wb.CreateCellStyle();
styleDate.DataFormat = dateformat;
ICellStyle styleNormal = wb.CreateCellStyle();
for (int i = 0; i < tabelDatas.Rows.Count; i++)
{
DataRow dr = tabelDatas.Rows[i];
IRow ir = sheet.CreateRow(i + 1);
for (int j = 0; j < dr.ItemArray.Length; j++)
{
ICell icell = ir.CreateCell(j);
object cellValue = dr[j];
Type type = cellValue.GetType();
if (type == typeof(decimal) || type == typeof(double) || type == typeof(int) || type == typeof(float))
{
icell.SetCellValue(Convert.ToDouble(cellValue));
icell.CellStyle = styleDecimal;
}
else if (type == typeof(DateTime))
{
icell.SetCellValue(Convert.ToDateTime(cellValue).ToString(myDateFormat));
icell.CellStyle = styleNormal;
}
else if (type == typeof(bool))
{
icell.SetCellValue(Convert.ToBoolean(cellValue) ? "是" : "否");
icell.CellStyle = styleNormal;
}
else
{
icell.SetCellValue(cellValue.ToString());
icell.CellStyle = styleNormal;
}
}
}
using (FileStream fs = File.OpenWrite(excelPathAll))
{
wb.Write(fs);
}
return true;
}
#endregion DataTable导出Excel 和Excel读取到DataTabel
#region DataTabel导出Csv 和CSV文件读取到DataTable
/// <summary>
/// Csv文件读取到DataTable
/// </summary>
/// <param name="filePath">csv文件路径</param>
/// <param name="n">表示第n行是字段title,第n+1行是记录开始(首行是标题传0)</param>
/// <returns>可选参数表示最后K行不算记录默认0</returns>
public static DataTable GetDataTableByCsvPath(string filePath, int n)
{
DataTable dt = new DataTable();
String csvSplitBy = "(?<=^|,)(\"(?:[^\"]|\"\")*\"|[^,]*)";
StreamReader reader = new StreamReader(filePath, System.Text.Encoding.Default, false);
int i = 0, m = 0;
reader.Peek();
while (reader.Peek() > 0)
{
m = m + 1;
string str = reader.ReadLine();
if (m >= n + 1)
{
if (m == n + 1) //如果是字段行，则自动加入字段。
{
MatchCollection mcs = Regex.Matches(str, csvSplitBy);
foreach (Match mc in mcs)
{
if (dt.Columns.Contains(mc.Value))
{
dt.Columns.Add(mc.Value + "2"); //增加列标题
}
else
{
dt.Columns.Add(mc.Value);
}
}
}
else
{
MatchCollection mcs = Regex.Matches(str, "(?<=^|,)(\"(?:[^\"]|\"\")*\"|[^,]*)");
i = 0;
System.Data.DataRow dr = dt.NewRow();
foreach (Match mc in mcs)
{
dr[i] = mc.Value;
i++;
}
dt.Rows.Add(dr); //DataTable 增加一行
}
}
}
return dt;
}
/// <summary>
/// 将DataTable导出为Csv文件
/// </summary>
/// <param name="dt">数据</param>
/// <param name="savaPath">保存的路径</param>
/// <param name="strName">文件名称</param>
/// <returns></returns>
public static string ExportCsvByDataTabel(System.Data.DataTable dt, string savaPath, string strName)
{
//保存到本项目文件夹下
//string strPath = Path.GetTempPath() + strName + ".csv";
//保存到指定目录下
string strPath = savaPath + "\\" + strName + ".csv";
if (File.Exists(strPath))
{
File.Delete(strPath);
}
//先打印标头
StringBuilder strColu = new StringBuilder();
StringBuilder strValue = new StringBuilder();
int i = 0;
try
{
StreamWriter sw = new StreamWriter(new FileStream(strPath, FileMode.CreateNew), Encoding.GetEncoding("GB2312"));
for (i = 0; i <= dt.Columns.Count - 1; i++)
{
strColu.Append(dt.Columns[i].ColumnName);
strColu.Append(",");
}
//移出掉最后一个,字符
strColu.Remove(strColu.Length - 1, 1);
sw.WriteLine(strColu);
foreach (DataRow dr in dt.Rows)
{
//移出
strValue.Remove(0, strValue.Length);
for (i = 0; i <= dt.Columns.Count - 1; i++)
{
strValue.Append(dr[i].ToString());
strValue.Append(",");
}
//移出掉最后一个,字符
strValue.Remove(strValue.Length - 1, 1);
sw.WriteLine(strValue);
}
sw.Close();
//打开文件
//System.Diagnostics.Process.Start(strPath);
return strPath;
}
catch (Exception ex)
{
return "";
}
}
#endregion DataTabel导出Csv 和CSV文件读取到DataTable
#region 导出excel 【使用NPOI库】
/// <summary>
/// DataTable转存为Excel文件【使用NPOI库】
/// </summary>
/// <param name="dt"></param>
/// <param name="filePath"></param>
public static void GetExcelByDataTable(DataTable dt, string filePath)
{
// 创建一个新的Excel工作簿
IWorkbook workbook = new XSSFWorkbook();
// 创建一个新的工作表并命名为“Sheet1”
ISheet worksheet = workbook.CreateSheet("Sheet1");
// 将DataTable的列名写入工作表中
IRow headerRow = worksheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = headerRow.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
// 将DataTable的数据写入工作表中
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow dataRow = worksheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = dataRow.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
// 保存Excel文件
using (FileStream fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(fileStream);
}
}
/// <summary>
/// 将excel导入到datatable
/// </summary>
/// <param name="filePath">excel路径</param>
/// <param name="isColumnName">第一行是否是列名</param>
/// <returns>返回datatable</returns>
public static DataTable GetDataTableByExcel(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))
{
// 版本后缀控制
if (filePath.IndexOf(".xlsx") > 0)
workbook = new XSSFWorkbook(fs);
// 版本后缀控制
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)
{
if (fs != null)
{
fs.Close();
}
return null;
}
}
public static void GetExcelByDataGridView(DataGridView dgv, string filePath)
{
// 创建一个新的Excel工作簿
IWorkbook workbook = new XSSFWorkbook();
// 创建一个新的工作表并命名为“Sheet1”
ISheet worksheet = workbook.CreateSheet("Sheet1");
// 将DataTable的列名写入工作表中
IRow headerRow = worksheet.CreateRow(0);
for (int i = 0; i < dgv.Columns.Count; i++)
{
ICell cell = headerRow.CreateCell(i);
cell.SetCellValue(dgv.Columns[i].HeaderText);
}
// 将DataTable的数据写入工作表中
for (int i = 0; i < dgv.Rows.Count; i++)
{
IRow dataRow = worksheet.CreateRow(i + 1);
for (int j = 0; j < dgv.Columns.Count; j++)
{
ICell cell = dataRow.CreateCell(j);
cell.SetCellValue(dgv.Rows[i].Cells[j].ToString());
}
}
// 保存Excel文件
using (FileStream fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(fileStream);
}
}
#endregion
/// <summary>
///
/// </summary>
/// <param name="filePath"></param>
/// <param name="ht">Hashtable的key与Excel文件中的名称对应</param>
/// <param name="sheetIndex"></param>
public static void WriteHashtable(string filePath, Hashtable ht, int? sheetIndex = null)
{
if (ht == null)
{
return;
}
IWorkbook fileWorkbook;
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
if (filePath.Last() == 's')
{
try
{
fileWorkbook = new HSSFWorkbook(fs);
}
catch (Exception ex)
{
//logger.Error("打开Excel文件失败！", ex);
throw ex;
}
}
else
{
try
{
fileWorkbook = new XSSFWorkbook(fs);
}
catch
{
fileWorkbook = new HSSFWorkbook(fs);
}
}
}
foreach (DictionaryEntry dictEntry in ht)
{
string strKey = dictEntry.Key.ToString();
string strValue = dictEntry.Value as string;
List<string> lstValue = dictEntry.Value as List<string>;
if (strValue != null)
{
IName name1 = fileWorkbook.GetName(dictEntry.Key.ToString());
CellReference cr2 = new CellReference(name1.RefersToFormula);
ICell cell2 = fileWorkbook.GetSheet(cr2.SheetName).GetRow(cr2.Row).GetCell(cr2.Col);
cell2.SetCellValue(strValue);
}
else if (lstValue != null)
{
IName name1 = fileWorkbook.GetName(dictEntry.Key.ToString());
if (name1 == null)
{
//logger.Warn("名称为null:" + dictEntry.Key.ToString());
continue;
}
CellReference cr2 = new CellReference(name1.RefersToFormula);
var cellSheet = fileWorkbook.GetSheet(cr2.SheetName);
var cellRow = cellSheet.GetRow(cr2.Row);
int iRowCount = (int)Math.Ceiling((double)lstValue.Count / cellRow.Cells.Count);
int iCurRow = -1;
for (int rowIndex = 0; rowIndex < iRowCount; rowIndex++)
{
iCurRow = cr2.Row + rowIndex + 1;
var newRow = cellSheet.GetRow(iCurRow);
if (iCurRow > cellSheet.LastRowNum)
{
cellSheet.CreateRow(iCurRow);
}
else
{
cellSheet.ShiftRows(iCurRow, cellSheet.LastRowNum, 1, true, false);
}
newRow = cellSheet.GetRow(iCurRow);
newRow.Height = cellRow.Height;
for (int colIndex = 0; colIndex < cellRow.Cells.Count; colIndex++)
{
var cellsource = cellRow.GetCell(colIndex);
var cellInsert = newRow.CreateCell(colIndex);
var cellStyle = cellsource.CellStyle;
//设置单元格样式
if (cellStyle != null)
{
cellInsert.CellStyle = cellsource.CellStyle;
if (lstValue.Count > rowIndex * cellRow.Cells.Count + colIndex)
{
if (lstValue[rowIndex * cellRow.Cells.Count + colIndex] == "虚拟出库" ||
lstValue[rowIndex * cellRow.Cells.Count + colIndex] == "虚拟退库")
{
//设置单元格信息
//cellsource.CellStyle.FillPattern = FillPattern.SolidForeground;
//cellsource.CellStyle.FillForegroundColor = 10;
ICellStyle boldStyle = fileWorkbook.CreateCellStyle();
boldStyle.FillPattern = FillPattern.SolidForeground;
boldStyle.FillForegroundColor = 10;
boldStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
boldStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellInsert.CellStyle = boldStyle;
}
}
}
if (rowIndex * cellRow.Cells.Count + colIndex >= lstValue.Count)
{
break;
}
cellInsert.SetCellValue(lstValue[rowIndex * cellRow.Cells.Count + colIndex]);
}
}
}
}
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write, FileShare.ReadWrite))
{
fileWorkbook.Write(fs);
fs.Flush();
}
}
/// <summary>
/// 根据Excel和Sheet返回DataTable
/// </summary>
/// <param name="filePath">Excel文件地址</param>
/// <param name="sheetIndex">Sheet索引</param>
/// <returns>DataTable</returns>
public static DataTable GetDataTable(string filePath, int sheetIndex)
{
return GetDataSetByExcelPath(filePath, sheetIndex).Tables[0];
}
}
}
