private static ICell CreateCellTitle(AssitCellAttribute attr, IRow row, int index)
{
ICell cell = ExcelAssit.CreateCell(attr, row, index);
cell.SetCellValue(attr.CellTitle);
return(cell);
}
private static ICell CreateCell(AssitCellAttribute attr, IRow row, int index)
{
ICell cell = row.CreateCell(index);
if (attr.CellType == CellType.String)
{
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
}
if (attr.CellType == CellType.Int)
{
cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
}
if (attr.CellType == CellType.Float)
{
cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
}
if (attr.CellType == CellType.DateTime)
{
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
}
if (attr.CellType == CellType.Image)
{
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
}
return(cell);
}
private static void WriteRow(Object item, IWorkbook book, ISheet sheet, int rowIndex)
{
Type type = item.GetType();
IRow row = sheet.CreateRow(rowIndex);
int cellIndex = 0;
type.GetProperties().ToList().ForEach(a => {
AssitCellAttribute attr = ExcelAssit.GetCellAttribute(a);
if (attr == null)
{
return;
}
ICell cell = ExcelAssit.CreateCell(attr, row, cellIndex);
cellIndex++;
if (attr.CellType == CellType.Image)
{
if (a.GetValue(item) == null)
{
return;
}
byte[] bytes = null;
if (a.PropertyType == typeof(string))
{
bytes = System.IO.File.ReadAllBytes(a.GetValue(item).ToString());
}
if (a.PropertyType == typeof(byte[]))
{
bytes = (byte[])a.GetValue(item);
}
int pictureIdx = book.AddPicture(bytes, PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, cellIndex - 1, rowIndex, cellIndex, rowIndex + 1);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
return;
}
if (attr.CellType == CellType.Int)
{
if (a.GetValue(item) == null)
{
return;
}
cell.SetCellValue(int.Parse(a.GetValue(item).ToString()));
return;
}
if (attr.CellType == CellType.Float)
{
if (a.GetValue(item) == null)
{
return;
}
cell.SetCellValue(float.Parse(a.GetValue(item).ToString()));
return;
}
if (attr.CellType == CellType.DateTime)
{
if (a.GetValue(item) == null)
{
return;
}
if (a.PropertyType == typeof(string))
{
cell.SetCellValue(int.Parse(a.GetValue(item).ToString()));
}
if (a.PropertyType == typeof(DateTime))
{
cell.SetCellValue(DateTime.Parse(a.GetValue(item).ToString()).ToString("yyyy-MM-dd hh:mm:ss"));
}
}
if (attr.CellType == CellType.String)
{
if (a.GetValue(item) == null)
{
return;
}
cell.SetCellValue(a.GetValue(item).ToString());
return;
}
});
}
public static List <T> ReadExcel <T>(string path, string sheetName)
{
Type type = typeof(T);
List <T> list = new List <T>();
IWorkbook workbook = NPOIOpenExcel(path);
ISheet sheet = workbook.GetSheet(sheetName);
IRow row = sheet.GetRow(0);
Dictionary <string, PropertyInfo> propertys = new Dictionary <string, PropertyInfo>();
var propertiess = type.GetProperties().ToList();
int cellIndex = 0;
row.Cells.ForEach(a => {
propertiess.ForEach(a1 => {
AssitCellAttribute attr = a1.GetCustomAttribute <AssitCellAttribute>();
if (attr == null)
{
return;
}
if (attr.CellTitle == a.StringCellValue.Trim())
{
propertys.Add(cellIndex.ToString(), a1);
}
cellIndex++;
});
});
for (int i = 1; i < sheet.LastRowNum; i++)
{
IRow irow = sheet.GetRow(i);
var createfn = typeof(T).GetConstructors()[0];
Object t = createfn.Invoke(null);
for (int j = 0; j < irow.Cells.Count; j++)
{
PropertyInfo pi = propertys[j.ToString()];
if (pi == null)
{
continue;
}
AssitCellAttribute attr = GetCellAttribute(pi);
if (attr == null)
{
continue;
}
if (attr.CellType == CellType.Image)
{
continue;
}
ICell a = irow.GetCell(j);
if (attr.CellType == CellType.String)
{
if (a != null && a.StringCellValue != null)
{
pi.SetValue(t, a.StringCellValue);
}
}
if (attr.CellType == CellType.Int)
{
if (a != null && a.StringCellValue != null)
{
pi.SetValue(t, int.Parse(a.StringCellValue));
}
}
if (attr.CellType == CellType.Float)
{
if (a != null && a.StringCellValue != null)
{
pi.SetValue(t, float.Parse(a.StringCellValue));
}
}
if (attr.CellType == CellType.DateTime)
{
if (a != null && a.StringCellValue != null)
{
if (pi.PropertyType == typeof(string))
{
pi.SetValue(t, a.StringCellValue);
}
if (pi.PropertyType == typeof(DateTime))
{
pi.SetValue(t, DateTime.Parse(a.StringCellValue));
}
}
}
}
list.Add((T)t);
}
return(null);
}
