Example #1
0
        public static Workbook AddSheet <T>(this Workbook workbook, IList <T> data = null) where T : class, new()
        {
            string sheetName = null;
            IExcelTypeFormater <Worksheet> defaultExcelTypeFormater = null;
            var excelAttribute = typeof(T).GetCustomAttribute <ExcelAttribute>();

            if (excelAttribute == null)
            {
                sheetName = typeof(T).Name;
                defaultExcelTypeFormater = new SpireExcelTypeFormater();
            }
            else
            {
                if (excelAttribute.IsIncrease)
                {
                    if (workbook.Worksheets.Count == 0)
                    {
                        sheetName = $"{excelAttribute.SheetName}";
                    }
                    else
                    {
                        sheetName = $"{excelAttribute.SheetName}{ workbook.Worksheets.Count}";
                    }
                }
                else
                {
                    sheetName = excelAttribute.SheetName;
                }
                if (excelAttribute.ExportExcelType != null)
                {
                    defaultExcelTypeFormater = Activator.CreateInstance(excelAttribute.ExportExcelType) as IExcelTypeFormater <Worksheet>;
                }
                if (defaultExcelTypeFormater == null)
                {
                    defaultExcelTypeFormater = new SpireExcelTypeFormater();
                }
            }
            Worksheet sheet = workbook.Worksheets[sheetName];

            if (sheet == null)
            {
                sheet = workbook.Worksheets.Add(sheetName);
            }
            defaultExcelTypeFormater.SetExcelWorksheet()?.Invoke(sheet);

            var mainPropertieList = typeof(T).ToColumnDic();


            IList <IExcelExportFormater <CellRange> > excelTypes = new List <IExcelExportFormater <CellRange> >();
            IExcelExportFormater <CellRange>          defaultExcelExportFormater = new SpireExcelExportFormater();
            int row    = (sheet.CellRecords.LastRow == -1 ? 0 : sheet.CellRecords.LastRow) + 1;
            int column = 1;

            //表头行
            foreach (var item in mainPropertieList)
            {
                IExcelExportFormater <CellRange> excelType = null;
                if (item.Value.ExportExcelType != null)
                {
                    excelType = excelTypes.Where(o => o.GetType().FullName == item.Value.ExportExcelType.FullName).FirstOrDefault();
                    if (excelType == null)
                    {
                        excelType = Activator.CreateInstance(item.Value.ExportExcelType) as IExcelExportFormater <CellRange>;
                        excelTypes.Add(excelType);
                    }
                }
                else
                {
                    excelType = defaultExcelExportFormater;
                }
                excelType.SetHeaderCell()?.Invoke(sheet[row, column], item.Value.Name);
                column++;
            }

            row++;

            //数据行
            if (data != null && data.Any())
            {
                foreach (var item in data)
                {
                    column = 1;
                    foreach (var mainPropertie in mainPropertieList)
                    {
                        IExcelExportFormater <CellRange> excelType = null;
                        var mainValue = mainPropertie.Key.GetValue(item);
                        if (mainPropertie.Value.ExportExcelType != null)
                        {
                            excelType = excelTypes.Where(o => o.GetType().FullName == mainPropertie.Value.ExportExcelType.FullName).FirstOrDefault();
                            if (excelType == null)
                            {
                                excelType = Activator.CreateInstance(mainPropertie.Value.ExportExcelType) as IExcelExportFormater <CellRange>;
                                excelTypes.Add(excelType);
                            }
                        }
                        else
                        {
                            excelType = defaultExcelExportFormater;
                        }
                        excelType.SetBodyCell()?.Invoke(sheet[row, column], mainValue);
                        column++;
                    }
                    row++;
                }
            }
            return(workbook);
        }
Example #2
0
        public static IWorkbook AddSheet <T>(this IWorkbook workbook, IList <T> data = null) where T : class, new()
        {
            string sheetName = null;
            IExcelTypeFormater <ISheet> defaultExcelTypeFormater = null;
            var excelAttribute = typeof(T).GetCustomAttribute <ExcelAttribute>();

            if (excelAttribute == null)
            {
                sheetName = typeof(T).Name;
                defaultExcelTypeFormater = new NpoiExcelTypeFormater();
            }
            else
            {
                if (excelAttribute.IsIncrease)
                {
                    if (workbook.NumberOfSheets == 0)
                    {
                        sheetName = $"{excelAttribute.SheetName}";
                    }
                    else
                    {
                        sheetName = $"{excelAttribute.SheetName}{workbook.NumberOfSheets}";
                    }
                }
                else
                {
                    sheetName = excelAttribute.SheetName;
                }
                if (excelAttribute.ExportExcelType != null && typeof(IExcelTypeFormater <ISheet>).IsAssignableFrom(excelAttribute.ExportExcelType))
                {
                    defaultExcelTypeFormater = Activator.CreateInstance(excelAttribute.ExportExcelType) as IExcelTypeFormater <ISheet>;
                }
                if (defaultExcelTypeFormater == null)
                {
                    defaultExcelTypeFormater = new NpoiExcelTypeFormater();
                }
            }
            ISheet sheet = workbook.GetSheet(sheetName);

            if (sheet == null)
            {
                sheet = workbook.CreateSheet(sheetName);
            }
            defaultExcelTypeFormater.SetExcelWorksheet()?.Invoke(sheet);

            var mainPropertieList = typeof(T).ToColumnDic();

            IList <IExcelExportFormater <ICell> > excelTypes = new List <IExcelExportFormater <ICell> >();
            IExcelExportFormater <ICell>          defaultExcelExportFormater = new NpoiExcelExportFormater();
            int row    = (sheet?.PhysicalNumberOfRows ?? 0);
            int column = 0;

            //表头行
            var headerRowCell = sheet.CreateRow(row);

            foreach (var item in mainPropertieList)
            {
                IExcelExportFormater <ICell> excelType = null;
                if (item.Value.ExportExcelType != null)
                {
                    excelType = excelTypes.Where(o => o.GetType().FullName == item.Value.ExportExcelType.FullName).FirstOrDefault();
                    if (excelType == null)
                    {
                        excelType = Activator.CreateInstance(item.Value.ExportExcelType) as IExcelExportFormater <ICell>;
                        excelTypes.Add(excelType);
                    }
                }
                else
                {
                    excelType = defaultExcelExportFormater;
                }
                excelType.SetHeaderCell()?.Invoke(headerRowCell.CreateCell(column), item.Value.Name);
                column++;
            }

            row++;

            //数据行
            if (data != null && data.Any())
            {
                foreach (var item in data)
                {
                    var rowCell = sheet.CreateRow(row);
                    column = 0;
                    foreach (var mainPropertie in mainPropertieList)
                    {
                        IExcelExportFormater <ICell> excelType = null;
                        var mainValue = mainPropertie.Key.GetValue(item);
                        if (mainPropertie.Value.ExportExcelType != null)
                        {
                            excelType = excelTypes.Where(o => o.GetType().FullName == mainPropertie.Value.ExportExcelType.FullName).FirstOrDefault();
                            if (excelType == null)
                            {
                                excelType = Activator.CreateInstance(mainPropertie.Value.ExportExcelType) as IExcelExportFormater <ICell>;
                                excelTypes.Add(excelType);
                            }
                        }
                        else
                        {
                            excelType = defaultExcelExportFormater;
                        }
                        excelType.SetBodyCell()?.Invoke(rowCell.CreateCell(column), mainValue);
                        column++;
                    }
                    row++;
                }
            }
            return(workbook);
        }