// 由于 DataGridColumn 需要 WPF 的才能使用 暂时注释
///// <summary>
///// 导出Excel
///// 将表格控件的表头和内容导出到Excel文件
///// </summary>
///// <typeparam name="T"></typeparam>
///// <param name="filePath">保存路径</param>
///// <param name="dgColumns">表格控件表头</param>
///// <param name="entitys">数据源</param>
///// <param name="isAddInExistWorksheets">追加内容到Excel文件中</param>
///// <returns>导出结果</returns>
//public static bool ExportToExcel<T>(string filePath, ObservableCollection<DataGridColumn> dgColumns, List<T> entitys, bool isAddInExistWorksheets = false)
//{
// List<ExportColumn> transDgColumns = new List<ExportColumn>();
// foreach (DataGridColumn column in dgColumns)
// {
// ExportColumn ecModel = new ExportColumn();
// System.Windows.Data.Binding columnPath = column.ClipboardContentBinding as System.Windows.Data.Binding;
// ecModel.Name = columnPath.Path.Path;
// ecModel.Header = column.Header.ToString();
// transDgColumns.Add(ecModel);
// }
// return NPOIHelper.ExportToExcelForAll<T>(filePath, transDgColumns, entitys, isAddInExistWorksheets);
//}
/// <summary>
/// 导出Excel
/// 将自定义的表头和内容导出到Excel文件
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filePath">保存路径</param>
/// <param name="dgColumns">自定义表头集合</param>
/// <param name="entitys">数据源</param>
/// <param name="isAddInExistWorksheets">追加内容到Excel文件中</param>
/// <returns>导出结果</returns>
public static bool ExportToExcelForAll <T>(string filePath, IEnumerable <ExportColumn> dgColumns, List <T> entitys, bool isAddInExistWorksheets = false)
{
if (entitys == null)
{
throw new Exception("导出数据为 null。");
}
bool isSucceed = false; // 导出结果
IWorkbook iworkbook;
if (isAddInExistWorksheets == false) // 覆盖已存在的Excel文件
{
if (filePath.EndsWith("xlsx", StringComparison.InvariantCultureIgnoreCase))
{
iworkbook = new XSSFWorkbook();
}
else if (filePath.EndsWith("xls", StringComparison.InvariantCultureIgnoreCase))
{
iworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else
{
throw new Exception("必须保存为Excel标准后缀（.xls, .xlsx）。");
}
ISheet sheet = iworkbook.CreateSheet();
NPOIHelper.fillSheet <T>(sheet, dgColumns, entitys); // 填充工作表
using (FileStream fOut = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write, FileShare.ReadWrite)) // 写入流
{
fOut.Flush();
iworkbook.Write(fOut);//写入文件
isSucceed = true;
}
}
else // 向已存在的Excel文件中追加内容
{
if (!File.Exists(filePath)) // 追加的文件不存在
{
return(NPOIHelper.ExportToExcelForAll <T>(filePath, dgColumns, entitys, false));
}
using (FileStream fRead = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) // 读取流
{
if (filePath.EndsWith("xlsx", StringComparison.InvariantCultureIgnoreCase))
{
iworkbook = new XSSFWorkbook(fRead);
}
else if (filePath.EndsWith("xls", StringComparison.InvariantCultureIgnoreCase))
{
iworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fRead);
}
else
{
throw new Exception("必须保存为Excel标准后缀（.xls, .xlsx）。");
}
}
ISheet sheet = iworkbook.GetSheetAt(0); // 默认追加到第一个工作表
NPOIHelper.fillSheet <T>(sheet, dgColumns, entitys, true); // 填充工作表
using (FileStream fOut = new FileStream(filePath, FileMode.Open, FileAccess.Write, FileShare.ReadWrite)) // 写入流
{
fOut.Flush();
iworkbook.Write(fOut);//写入文件
isSucceed = true;
}
}
iworkbook = null;
return(isSucceed);
}
// IWorkbook
// HSSFWorkbook -- XLS
// XSSFWorkbook -- XLSX
/// <summary>
/// 读取Excel文件
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="fileName">文件名称</param>
/// <returns>DataSet 结果集</returns>
public static DataSet Excel2DataSet(string filePath, ExcelReaderConfig config = null)
{
DataSet result = new DataSet();
IWorkbook iWorkbook = null;
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
if (filePath.EndsWith("xlsx"))
{
iWorkbook = new XSSFWorkbook(fs);
}
else if (filePath.EndsWith("xls"))
{
iWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
}
else
{
throw new Exception("必须保存为Excel标准后缀（.xls, .xlsx）。");
}
int sheetCount = iWorkbook.NumberOfSheets; // 工作表总数
for (int sheetNo = 0; sheetNo < sheetCount; sheetNo++)
{
SheetReadConfig matchReadConfig = null;
ISheet sheet = iWorkbook.GetSheetAt(sheetNo);
if (config != null && config.Config != null)
{
matchReadConfig = config.Config.FirstOrDefault(j => j.SheetName == sheet.SheetName || (j.SheetIndex.HasValue == true && j.SheetIndex == sheetNo));
if (matchReadConfig == null)
{
continue;
}
}
DataTable dt = new DataTable();
#region 表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
if (header == null)
{
continue;
}
List <int> columns = new List <int>();
for (int i = 0; i < header.LastCellNum; i++)
{
if (header.GetCell(i) != null)
{
object obj = NPOIHelper.GetValueType(header.GetCell(i) as ICell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
{
dt.Columns.Add(new DataColumn(obj.ToString()));
}
columns.Add(i);
}
}
#endregion
#region 数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool rowHasValue = false; // TODO : 遇到空行 计数问题未解决
// TODO : 计算行号
foreach (int j in columns)
{
if (sheet.GetRow(i) != null && sheet.GetRow(i).GetCell(j) != null)
{
if (matchReadConfig != null && matchReadConfig.CellReadRule != null && matchReadConfig.CellReadRule.ContainsKey(j))
{
CellType t = CellType.Blank;
if (matchReadConfig.CellReadRule.TryGetValue(j, out t))
{
try
{
switch (t)
{
case CellType.String:
{
dr[j] = sheet.GetRow(i).GetCell(j).StringCellValue;
if (rowHasValue != true && dr[j] != null && string.IsNullOrEmpty(dr[j].ToString()) == false)
{
rowHasValue = true;
}
}
break;
case CellType.Formula:
{
dr[j] = sheet.GetRow(i).GetCell(j).CellFormula;
if (rowHasValue != true && dr[j] != null && string.IsNullOrEmpty(dr[j].ToString()) == false)
{
rowHasValue = true;
}
}
break;
case CellType.DateTime:
{
dr[j] = sheet.GetRow(i).GetCell(j).DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff");
if (rowHasValue != true && dr[j].Equals(DateTime.MinValue.ToString("yyyy-MM-dd HH:mm:ss.fff")) == false)
{
rowHasValue = true;
}
}
break;
case CellType.Blank:
dr[j] = string.Empty;
break;
default:
dr[j] = string.Empty;
break;
}
}
catch (Exception ex)
{
throw new Exception("NPOIHelperV3 - matchReadConfig.CellReadRule Swicth Throw Exception", ex);
}
}
else
{
dr[j] = NPOIHelper.GetValueType(sheet.GetRow(i).GetCell(j) as ICell);
if (rowHasValue != true && dr[j] != null && dr[j].ToString() != string.Empty)
{
rowHasValue = true;
}
}
}
else
{
// TODO : 读取日期Cell问题仍需解决
dr[j] = NPOIHelper.GetValueType(sheet.GetRow(i).GetCell(j) as ICell);
if (rowHasValue != true && dr[j] != null && dr[j].ToString() != string.Empty)
{
rowHasValue = true;
}
}
}
}
if (rowHasValue)
{
dt.Rows.Add(dr);
}
}
#endregion
result.Tables.Add(dt);
}
}
return(result);
}
