예제 #1
0
        public static Task <List <List <T> > > GetGroupedDTOs <T>(MemoryStream stream, string fileName, ImportConfig config, IExcelConfigurationRepo excelConfig) where T : Output, new()
        {
            return(Task.Run(() =>
            {
                stream.Position = 0;

                IWorkbook wbk;

                if (fileName.EndsWith(".xls", StringComparison.InvariantCultureIgnoreCase))
                {
                    wbk = new HSSFWorkbook(stream); //This will read 2007 Excel format
                }
                else if (fileName.EndsWith(".xlsx", StringComparison.InvariantCultureIgnoreCase))
                {
                    wbk = new XSSFWorkbook(stream);
                }
                else
                {
                    throw new Exception("This format is not supported");
                }

                var sheet = wbk.GetSheetAt(0);    //get first sheet from workbook

                IRow headerRow = sheet.GetRow(0); //Get Header Row

                var columnNames = Utils.GetColumnNames(headerRow);

                excelConfig.Save(1, typeof(T).Name, fileName, columnNames);

                var result = new List <List <T> >();

                var miniResult = new List <T>();

                var firstRow = sheet.FirstRowNum;
                var lastRow = sheet.LastRowNum;

                for (int i = (firstRow + 1); i <= lastRow; i++) //Read Excel File
                {
                    var row = sheet.GetRow(i);

                    try
                    {
                        if (miniResult.Count > 0 && (row == null || row.All(x => x.CellType == CellType.Blank)))
                        {
                            result.Add(miniResult);
                            miniResult = new List <T>();
                        }
                        else
                        {
                            if (row != null && row.Any(x => x.CellType != CellType.Blank))
                            {
                                miniResult.Add(GetDTO <T>(row, columnNames.ToList(), i));
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        System.Diagnostics.Debugger.Break();
                    }
                }

                if (miniResult.Count > 0)
                {
                    result.Add(miniResult);
                }

                return result;
            }));
        }
예제 #2
0
        public static Task <ConcurrentBag <T> > GetDTOs <T>(MemoryStream stream, string fileName, ImportConfig config, IExcelConfigurationRepo excelConfig) where T : Output, new()
        {
            return(Task.Run(() =>
            {
                var result = new ConcurrentBag <T>();

                stream.Position = 0;

                IWorkbook wbk;

                if (fileName.EndsWith(".xls", StringComparison.InvariantCultureIgnoreCase))
                {
                    wbk = new HSSFWorkbook(stream); //This will read 2007 Excel format
                }
                else if (fileName.EndsWith(".xlsx", StringComparison.InvariantCultureIgnoreCase))
                {
                    wbk = new XSSFWorkbook(stream);
                }
                else
                {
                    throw new Exception("This format is not supported");
                }

                var sheet = wbk.GetSheetAt(0);    //get first sheet from workbook

                IRow headerRow = sheet.GetRow(0); //Get Header Row

                var columnNames = Utils.GetColumnNames(headerRow);

                excelConfig.Save(1, typeof(T).Name, fileName, columnNames);

                var firstRow = sheet.FirstRowNum;
                var lastRow = sheet.LastRowNum;

                for (int i = (firstRow + 1); i <= lastRow; i++) //Read Excel File
                {
                    var row = sheet.GetRow(i);

                    if (row == null && config.IgnoreNullRows)
                    {
                        continue;
                    }
                    try
                    {
                        if (row.All(x => x == null || string.IsNullOrEmpty(x.ToString())))
                        {
                            continue;
                        }
                    }
                    catch
                    {
                        System.Diagnostics.Debugger.Break();
                    }
                    result.Add(GetDTO <T>(row, columnNames.ToList(), i));
                }

                return result;
            }));
        }