NPOI2.4.1

EXL导入

/// <summary>
/// 获取数据 和图片
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="fileStream"></param>
/// <param name="fileName"></param>
/// <returns></returns>
public static ListAndImgs<T> GetListAndImgs<T>(Stream fileStream, string fileName) where T : ExlTem, new()
{
// 获取上传文件后缀
string ext = Path.GetExtension(fileName);
if (ext != ".xls" && ext != ".xlsx")
{
throw new Exception("只能上传xls,xlsx文件");
}
// 根据Excel版本进行处理
IWorkbook workbook = ext == "xls" ? (IWorkbook)new HSSFWorkbook(fileStream) : new XSSFWorkbook(fileStream);
// 获取Excel第一张工作簿
ISheet sheet = workbook.GetSheetAt(0);

// 获取EXL数据行数
int rowNum = sheet.LastRowNum;
if (rowNum == 0)
{
throw new Exception("EXL无数据");
}
IRow firstRow = sheet.GetRow(0);
// 获取EXL数据列数
int Cell = firstRow.LastCellNum;

Dictionary<string, int> titleRowDictionary = new Dictionary<string, int>();//列名->列号
List<string> titles = new List<string>();//所有列名
for (int i = 0; i < Cell; i++)
{
string title = firstRow.GetCell(i).ToString();
titles.Add(title);
titleRowDictionary[title] = i;
}
Type type = typeof(T);
IEnumerable<String> templates = type.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Static | BindingFlags.Instance).Select(i => i.GetPropDescription()); //EXL模板
//校验模板：
IEnumerable<string> lackTitle = templates.Where(i => !titles.Contains(i));//缺少的列
if (lackTitle.Any())
{
throw new Exception($"EXL缺少列：【{lackTitle.JoinAsString(",")}】");
}
//exl转List
List<T> datalist = new List<T>();
for (int i = 1; i <= rowNum; i++)
{
IRow currentRow = sheet.GetRow(i);
T t = new T();
t.Row = i;
//T t = (T)Activator.CreateInstance(type);
foreach (PropertyInfo prop in type.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Static | BindingFlags.Instance))
{
ICell cell = currentRow.GetCell(titleRowDictionary[prop.GetPropDescription()]);
if (cell == null) // 当此列为图片时 cell为null
{
continue;
}
switch (prop.PropertyType.Name)
{
case "String":
prop.SetValue(t, cell.ToString());
continue;
case "Int16":
case "Int32":
case "Int64":
if (int.TryParse(cell.ToString(), out int intV))
{
prop.SetValue(t, intV);
}
continue;
case "DateTime":
if (DateTime.TryParse(cell.ToString(), out DateTime dateV))
{
prop.SetValue(t, dateV);
}
continue;
case "Decimal":
if (Decimal.TryParse(cell.ToString(), out decimal decimalV))
{
prop.SetValue(t, decimalV);
}
continue;

default:
throw new Exception($"EXL暂未解析数据类型：{prop.PropertyType.Name}");
}

}
datalist.Add(t);
}
Dictionary<int, PicturesInfo> imgsDict = sheet.GetAllPictureInfos();
ListAndImgs<T> result = new ListAndImgs<T>();
result.DataList = datalist;
result.ImgsDict = imgsDict;
return result;
}

public class ListAndImgs<T> where T: ExlTem
{
public List<T> DataList { get; set; }

public Dictionary<int, PicturesInfo> ImgsDict { get; set; }
}

public class ExlTem
{
/// <summary>
/// 第几行
/// </summary>
public int Row { get; set; }
}

示例数据类：

public class GoodsTem:ExlTem
{

[Description("sku")]
public string Sku { get; set; }
/// <summary>中文名</summary>
[Description("中文品名")]
public string Cn { get; set; }
/// <summary>英文名</summary>
[Description("英文品名")]
public string En { get; set; }
/// <summary>海关编码us</summary>
[Description("海关编码")]
public string UsCode { get; set; }
/// <summary>重量（kg）</summary>
[Description("毛重(kg)")]
public decimal Weight { get; set; }
/// <summary>申报金额</summary>
[Description("申报单价(USD)")]
public decimal Declare { get; set; }

/// <summary>材质</summary>
[Description("材质")]
public string Material { get; set; }

/// <summary>品牌</summary>
[Description("品牌")]
public string Brand { get; set; }
/// <summary>图片</summary>
[Description("图片")]
public string Pic { get; set; }
}

/// <summary>
/// 获取属性别名
/// </summary>
/// <param name="propertyInfo"></param>
/// <returns></returns>
public static string GetPropDescription(this PropertyInfo propertyInfo)
{
var descAttr = propertyInfo.GetCustomAttribute<DescriptionAttribute>();
if (descAttr != null)
{
return descAttr.Description;
}
return propertyInfo.Name;
}

处理图片

public class PicturesInfo
{
public int MinRow { get; set; }
public int MaxRow { get; set; }
public int MinCol { get; set; }
public int MaxCol { get; set; }
public byte[] PictureData { get; set; }

public PicturesInfo(int minRow, int maxRow, int minCol, int maxCol, byte[] pictureData)
{
this.MinRow = minRow;
this.MaxRow = maxRow;
this.MinCol = minCol;
this.MaxCol = maxCol;
this.PictureData = pictureData;
}
}

public static class NpoiExtend
{

/// <summary>
/// key:行数，value:图片信息
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
public static Dictionary<int, PicturesInfo> GetAllPictureInfos(this ISheet sheet)
{
if (sheet is HSSFSheet)
{
return GetAllPictureInfos((HSSFSheet)sheet);
}
else if (sheet is XSSFSheet)
{
return GetAllPictureInfos((XSSFSheet)sheet);
}
else
{
throw new Exception("未处理类型，没有为该类型添加：GetAllPicturesInfos()扩展方法！");
}
}

private static Dictionary<int, PicturesInfo> GetAllPictureInfos(HSSFSheet sheet)
{

Dictionary<int, PicturesInfo> dict = new Dictionary<int, PicturesInfo>();var shapeContainer = sheet.DrawingPatriarch as HSSFShapeContainer;

if (null != shapeContainer)
{
var shapeList = shapeContainer.Children;
foreach (var shape in shapeList)
{
if (shape is HSSFPicture && shape.Anchor is HSSFClientAnchor)
{
var picture = (HSSFPicture)shape;
var anchor = picture.ClientAnchor;

dict[anchor.Row1] = new PicturesInfo(anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, picture.PictureData.Data);

}
}
}
return dict;
}

private static Dictionary<int, PicturesInfo> GetAllPictureInfos(XSSFSheet sheet)
{

Dictionary<int, PicturesInfo> dict = new Dictionary<int, PicturesInfo>();
var documentPartList = sheet.GetRelations();
foreach (var documentPart in documentPartList)
{
if (documentPart is XSSFDrawing)
{
var drawing = (XSSFDrawing)documentPart;
var shapeList = drawing.GetShapes();
foreach (var shape in shapeList)
{
if (shape is XSSFPicture)
{
XSSFPicture picture = (XSSFPicture)shape;
var anchor = picture.ClientAnchor;

dict[anchor.Row1] = new PicturesInfo(anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, picture.PictureData.Data);
}
}
}
}

return dict;
}