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); }