using NPOI.HSSF.UserModel;
|
using NPOI.SS.UserModel;
|
using NPOI.XSSF.UserModel;
|
using OfficeOpenXml;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.IO;
|
|
namespace JiepeiWMS.Common.Helper
|
{
|
public class ExcelHelper
|
{
|
#region 导出Excel文件
|
/// <summary>
|
/// 导出Excel文件
|
/// </summary>
|
/// <typeparam name="T"></typeparam>
|
/// <param name="dataList">数据</param>
|
/// <param name="headers">表头</param>
|
/// <returns></returns>
|
public static string CreateExcelFromList<T>(List<T> dataList, List<string> headers)
|
{
|
string sWebRootFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "tempExcel");
|
if (!Directory.Exists(sWebRootFolder))
|
{
|
Directory.CreateDirectory(sWebRootFolder);
|
}
|
string sFileName = $@"tempExcel_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
|
var path = Path.Combine(sWebRootFolder, sFileName);
|
FileInfo file = new FileInfo(path);
|
if (file.Exists)
|
{
|
file.Delete();
|
file = new FileInfo(path);
|
}
|
using (ExcelPackage package = new ExcelPackage(file))
|
{
|
//创建sheet
|
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("sheet1");
|
worksheet.Cells.LoadFromCollection(dataList, true);
|
//表头字段
|
for (int i = 0; i < headers.Count; i++)
|
{
|
worksheet.Cells[1, i + 1].Value = headers[i];
|
}
|
package.Save();
|
}
|
return path;
|
}
|
|
/// <summary>
|
/// 生成Excle 内存流
|
/// </summary>
|
/// <typeparam name="T"></typeparam>
|
/// <param name="dataList"></param>
|
/// <param name="headers"></param>
|
/// <returns></returns>
|
public static Stream CreateExcelStreamFromList<T>(List<T> dataList, List<string> headers)
|
{
|
|
string sWebRootFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "tempExcel");
|
if (!Directory.Exists(sWebRootFolder))
|
{
|
Directory.CreateDirectory(sWebRootFolder);
|
}
|
|
|
string sFileName = $@"tempExcel_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
|
var path = Path.Combine(sWebRootFolder, sFileName);
|
FileInfo file = new FileInfo(path);
|
if (file.Exists)
|
{
|
file.Delete();
|
file = new FileInfo(path);
|
}
|
|
MemoryStream ms = new MemoryStream();
|
|
using (ExcelPackage package = new ExcelPackage(file))
|
{
|
//创建sheet
|
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("sheet1");
|
worksheet.Cells.LoadFromCollection(dataList, true);
|
//表头字段
|
for (int i = 0; i < headers.Count; i++)
|
{
|
worksheet.Cells[1, i + 1].Value = headers[i];
|
}
|
package.SaveAs(ms);
|
ms.Flush();
|
ms.Position = 0;//指定当前流的位置从0开始
|
return ms;
|
}
|
|
}
|
|
|
#endregion
|
|
#region 导入Excel文件
|
|
/// <summary>
|
/// Aspose.Cells 读取Excel文件 只读第一个sheet
|
/// </summary>
|
/// <param name="FileName"></param>
|
public static DataTable ImportExcel(string FileName)
|
{
|
|
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(FileName);
|
|
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(FileName);
|
Aspose.Cells.Cells cells = workbook.Worksheets[0].Cells;
|
System.Data.DataTable dt = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1,true);
|
return dt;
|
}
|
|
|
/// <summary>
|
/// NPOI读取Excel文件
|
/// </summary>
|
/// <param name="fileName"></param>
|
/// <param name="sheetname"></param>
|
public static List<DataTable> Read(string fileName)
|
{
|
try
|
{
|
List<DataTable> tables = new List<DataTable>();
|
if (!File.Exists(fileName))
|
return tables;
|
var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
|
IWorkbook workbook = null;
|
if (fileName.IndexOf(".xlsx") > 0)
|
{
|
// 2007版本
|
workbook = new XSSFWorkbook(fs);
|
}
|
else if (fileName.IndexOf(".xls") > 0)
|
{
|
// 2003版本
|
workbook = new HSSFWorkbook(fs);
|
}
|
|
var workbooks = workbook.GetEnumerator();
|
while (workbooks.MoveNext())
|
{
|
ISheet sheet = workbooks.Current as ISheet;
|
DataTable dt = new DataTable(sheet.SheetName);
|
var rows = sheet.GetRowEnumerator();
|
while (rows.MoveNext())
|
{
|
IRow row = rows.Current as IRow;
|
if (row.RowNum == 0)
|
{
|
row.Cells.ForEach(cell =>
|
{
|
dt.Columns.Add(cell.StringCellValue);
|
});
|
}
|
else
|
{
|
var dr = dt.NewRow();
|
for (int i = 0; i < row.Cells.Count; i++)
|
{
|
dr[i] = row.Cells[i].ToString();
|
}
|
dt.Rows.Add(dr);
|
}
|
}
|
|
tables.Add(dt);
|
}
|
return tables;
|
}
|
catch (Exception ex)
|
{
|
|
throw;
|
}
|
}
|
|
public static int GradeForSort(string classGrade)
|
{
|
var Level = classGrade switch
|
{
|
"一级" => 1,
|
"二级" => 2,
|
"三级" => 3,
|
"四级" => 4,
|
"五级" => 5,
|
"六级" => 6,
|
"七级" => 7,
|
"八级" => 8,
|
"九级" => 9,
|
"十级" => 10,
|
_ => 0,
|
};
|
return Level;
|
}
|
#endregion
|
|
|
|
}
|
}
|
