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 void CreateTitle(ISheet sheet, Object item)
{
Type type = item.GetType();
List <AssitCellAttribute> titles = ExcelAssit.GetCellAttributes(type);
IRow row = sheet.CreateRow(0);
int cellIndex = 0;
titles.ForEach(a => {
ExcelAssit.CreateCellTitle(a, row, cellIndex);
cellIndex++;
});
}
public static bool AppendExcel(string path, string sheetName, List <Object> datas)
{
if (datas == null || datas.Count <= 0)
{
return(false);
}
IWorkbook workbook = NPOIOpenExcel(path);
ISheet sheet = workbook.GetSheet(sheetName);
int rowIndex = sheet.LastRowNum + 1;
datas.ForEach(a => {
ExcelAssit.WriteRow(a, workbook, sheet, rowIndex);
rowIndex++;
});
return(WriteToDisk(path, workbook));
}
public static bool WriteExcel(string path, string sheetName, List <Object> datas)
{
if (datas == null || datas.Count <= 0)
{
return(false);
}
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet(sheetName);
ExcelAssit.CreateTitle(sheet1, datas[0]);
int rowIndex = 1;
datas.ForEach(a => {
ExcelAssit.WriteRow(a, workbook, sheet1, rowIndex);
rowIndex++;
});
return(WriteToDisk(path, workbook));
}
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;
}
});
}
