public List <PolicyFromRegion> GetPolicy(DataPreLoad dataPreLoad) { HSSFWorkbook templateWorkbook = null; XSSFWorkbook xssf = null; ISheet table; string extention = System.IO.Path.GetExtension(Path); using (FileStream fs = new FileStream(Path, FileMode.Open, FileAccess.Read)) { if (extention != ".xls") { xssf = new XSSFWorkbook(fs); table = xssf[0]; } else { templateWorkbook = new HSSFWorkbook(fs, true); table = templateWorkbook[0]; } } return(Process(table, dataPreLoad)); }
/// <summary> /// Функция предварительной обработки файла /// </summary> /// <returns></returns> public static LoadRegion.DataPreLoad PreLoadExcel(string Path) { LoadRegion.DataPreLoad preLoadRegion = new DataPreLoad(); HSSFWorkbook templateWorkbook = null; XSSFWorkbook xssf = null; ISheet table; long countField = 0; long countRow = 0; long countEmpty = 0; string extention = System.IO.Path.GetExtension(Path); using (FileStream fs = new FileStream(Path, FileMode.Open, FileAccess.Read)) { if (extention != ".xls") { xssf = new XSSFWorkbook(fs); table = xssf[0]; } else { templateWorkbook = new HSSFWorkbook(fs, true); table = templateWorkbook[0]; } } for (int i = 0; ; i++) { if (table.GetRow(0).GetCell(i) == null) { countEmpty++; if (countEmpty > 10) { break; } } else { countField++; countEmpty = 0; } } countEmpty = 0; for (int i = 0; ; i++) { if (table.GetRow(i) == null) { countEmpty++; if (countEmpty > 10) { break; } } else { countRow++; countEmpty = 0; } } preLoadRegion.CountRow = countRow - 1; preLoadRegion.CountField = countField; //делаем автоопределение региона if (preLoadRegion.CountField == Constants.DefaultCountFieldInRegionExcel) { preLoadRegion.AutoRegionId = (long)ListRegionId.Default; } if (preLoadRegion.CountField == Constants.CountFieldInUfa) { preLoadRegion.AutoRegionId = (long)ListRegionId.Ufa; } if (preLoadRegion.CountField == Constants.CountFieldInMoscowObl) { preLoadRegion.AutoRegionId = (long)ListRegionId.MoscowObl; } return(preLoadRegion); }
protected abstract List <PolicyFromRegion> Process(ISheet table, DataPreLoad dataPreLoad);
protected override List <PolicyFromRegion> Process(ISheet table, DataPreLoad dataPreLoad) { List <PolicyFromRegion> listPolicy = new List <PolicyFromRegion>(); for (int i = 1; i <= dataPreLoad.CountRow; i++) { IRow row = table.GetRow(i); if (row != null) { PolicyFromRegion policy = new PolicyFromRegion(); policy.RegionId = this.RegionId; policy.SaveDate = DateTime.Now; ICell[] cells = new ICell[dataPreLoad.CountField]; for (int j = 0; j < cells.Count(); j++) { cells[j] = row.GetCell(j); } if (cells[0] != null) { policy.TemporaryPolicyNumber = cells[0].ToString(); } if (cells[1] != null) { policy.UnifiedPolicyNumber = cells[1].ToString(); } ////if (cells[2] != null) policy.UnifiedPolicyNumber = cells[2].StringCellValue; if (cells[3] != null) { policy.PolicyStatus.Id = GetLong(cells[3]); } if (cells[4] != null) { policy.PolicyStatus.Name = cells[4].ToString(); } if (cells[5] != null) { policy.ClientVisitDate = GetDateTime(cells[5]); } //if (cells[6] != null) policy.ApplicationMethod = cells[6].ToString(); if (cells[7] != null) { policy.DeliveryCenter.Code = cells[7].ToString().Trim(); } if (cells[8] != null) { policy.Lastname = cells[8].ToString(); } if (cells[9] != null) { policy.Firstname = cells[9].ToString(); } if (cells[10] != null) { policy.Secondname = cells[10].ToString(); } if (cells[11] != null) { policy.Sex = GetSex(cells[11].ToString()); } if (cells[12] != null) { policy.Birthday = GetDateTime(cells[12]); } if (cells[13] != null) { policy.Category = cells[13].ToString(); } ////if (cells[14] != null) policy.Citizenship = cells[14].ToString(); if (cells[15] != null) { policy.IssueDate = GetDateTime(cells[15]); } if (cells[16] != null) { policy.StatusDate = GetDateTime(cells[16]); } if (cells[17] != null) { policy.Phone = GetPhone(cells[17].ToString()); } if (cells[18] != null) { policy.BlankNumber = cells[18].ToString(); } listPolicy.Add(policy); } else { break; } } return(listPolicy); }