public static ExelSheet[] LoadSheets(Workbook workbook, int count = 0, Action <int> progressReport = null, bool deleteEmptyRows = true) { var result = new List <ExelSheet>(); //var totalRowsCount = 0; //var loaded = 0; //totalRowsCount = workbook.Worksheets.Cast<Worksheet>().Select(i => i.Cells.Rows.Count).Sum(); Helpers.PercentageProgress prg = new Helpers.PercentageProgress(); prg.Change += (s, e) => { if (progressReport != null) { progressReport((int)e.Value); } }; foreach (var sheetItem in workbook .Worksheets .Cast <Worksheet>() .Where(s => s.Cells.Rows.Count > 1) .Select(s => new { Sheet = s, ProgressInfo = prg.GetChild() }) .ToArray() ) { ExelSheet sht = new ExelSheet() { Name = sheetItem.Sheet.Name }; sht.Rows.AddRange( LoadRows(sheetItem.Sheet, count, new Action <int>((i) => { sheetItem.ProgressInfo.Value = i; }), deleteEmptyRows) ); if (sht.Rows.Count > 0) { result.Add(sht); } } return(result.ToArray()); }
/// <summary> /// Поиск в соответствии со входными параметрами (Адрес и город) улиц и прочей информации /// </summary> /// <param name="incomingData">Входные параметры (Адрес и город)</param> /// <returns>Соответствующие выходные параметры (Найденные и созданные улицы и прочее)</returns> public IDictionary<AddressParserIncomingParameter, AddressParserResult> Parse(IEnumerable<AddressParserIncomingParameter> incomingData, bool doNotAddAnyDataToDictionary = false, Action<decimal> reportProgress = null, Action<string> verboseLog = null) { if (incomingData == null) throw new ArgumentNullException(nameof(incomingData)); var log = new Action<string>((str) => { if (verboseLog != null) verboseLog($"{GetType().Name}.{nameof(Parse)}() {str}"); }); log($"Incoming data length: {incomingData.Count()}"); var pp = new Helpers.PercentageProgress(); pp.Change += (s, e) => { if (reportProgress != null) reportProgress(e.Value); }; var res = incomingData .GroupBy(i => i.City) .Select(g => new { City = g.Key, Items = g.ToArray(), Progress = pp.GetChild() }) .ToArray() //.AsParallel() .Select(g => new { g.City, Streets = GetStreets(g.Items.Select(i => i.Address), g.City, doNotAddAnyDataToDictionary, (progress) => g.Progress.Value = progress, str => log(str)), g.Items, }) .SelectMany(g => g.Items.Join(g.Streets, r => r.Address, i => i.Key, (r, i) => new { IncomingParameters = r, Data = new { Address = i.Key, Street = i.Value } })) .ToDictionary(item => item.IncomingParameters, item => new AddressParserResult() { Address = item.Data.Address, Street = item.Data.Street }); return res; }
/// <summary> /// Получение списка улиц в соответствии с адресами /// </summary> /// <param name="incomingAddresses">Адреса, для которых нужно определить улицы</param> /// <param name="city">Город</param> /// <param name="doNotAddAnyDataToDictionary">Флаг для отмены добавления данных в словарь</param> /// <param name="reportProgress">Action для отслеживания процесса выполнения</param> /// <returns>Список соответствия входящему адресу найденной улицы</returns> internal IDictionary<Address, RoyaltyRepository.Models.Street> GetStreets( IEnumerable<Address> incomingAddresses, RoyaltyRepository.Models.City city, bool doNotAddAnyDataToDictionary = false, Action<decimal> reportProgress = null, Action<string> verboseLog = null) { var pp = new Helpers.PercentageProgress(); var ppLoad = pp.GetChild(weight: 0.5m); var ppSubitem = pp.GetChild(weight: 9m); var ppEnd = pp.GetChild(weight: 0.5m); pp.Change += (s, e) => { if (reportProgress != null) reportProgress(e.Value); }; verboseLog = verboseLog ?? new Action<string>(s => { }); using (var logSession = Helpers.Log.Session($"{GetType().Name}.{nameof(GetStreets)}()")) try { logSession.Output = new Action<IEnumerable<string>>( (strs) => strs.ToList().ForEach(s => verboseLog(s)) ); var findStreet = incomingAddresses .Distinct() .OrderByDescending(a => a.Area) .ThenByDescending(a => a.Street) .ThenByDescending(a => a.House.ToString()) .LeftOuterJoin(Account.Dictionary.Records, //.Where(r => r.Street != null && r.Street.Area != null), s => new { StreetName = s.Street, AreaName = s.Area }, d => new { StreetName = d.Street.Name, AreaName = d.Street.Area.Name }, (s, d) => new { IncomingAddress = s, Street = (d != null ? d.ChangeStreetTo ?? d.Street : null), ConditionsScore = GetConditionsScore(s.House.Number, d == null ? null : d.Conditions, doNotAddAnyDataToDictionary), }) .ToArray(); var count = findStreet.Length; int current = 0; ppLoad.Value = 90; var res = findStreet .Select(i => { var subRes = new { i.IncomingAddress, Street = i.Street ?? GetStreetByDictionary(i.IncomingAddress, city, doNotAddAnyDataToDictionary, (s) => logSession.Add(s)) ?? GetNewStreet(i.IncomingAddress, city, doNotAddAnyDataToDictionary, (s) => logSession.Add(s)) }; current++; ppSubitem.Value = (decimal)current / (decimal)count * 100m; return subRes; } ) .GroupBy(i => i.IncomingAddress) .Select(g => new { IncomingAddress = g.Key, Items = g }) //Исключаем случаи, когда в словаре более одной записи на входящий адрес (что-то пошло не так, и мы генерируем ошибку) .Select(g => { if (g.Items.Count() > 1) { var ex = new Exception($"For incoming address '{g.IncomingAddress.ToString()}' found more then 1 street. See data for details"); int index = 0; foreach (var i in g.Items.Select(n => n.Street)) ex.Data.Add(index++, i.ToString()); throw ex; } return new { g.IncomingAddress, g.Items.FirstOrDefault().Street }; }) .ToArray(); ppLoad.Value = 100; logSession.Add($"Array constructed with {res.Length} elements for city '{city}'. Try to create dictionary."); return res.ToDictionary(i => i.IncomingAddress, i => i.Street); } catch(Exception ex) { logSession.Add(ex); logSession.Enabled = true; throw; } finally { ppEnd.Value = 100; } }
private void Load() { if (loadWorker != null) try { loadWorker.CancelAsync(); } catch { } LoadingProgress = 0; IsDocumentLoaded = false; Error = string.Empty; if (string.IsNullOrWhiteSpace(Path)) return; IsBusy = true; loadWorker = new BackgroundWorker() { WorkerReportsProgress = true, WorkerSupportsCancellation = true }; var init = new AsyncLoadInit() { DeleteEmptyRows = this.DeleteEmptyRows, WorkBookFilePath = this.Path, PreloadCount = Settings.SettingsProvider.CurrentSettings.PreloadedRowsCount }; var applyRes = new Action<Workbook, ExelSheet[]>((w, s) => { WorkBook = w; DocumentSheets.Clear(); var oldSheetName = (SelectedSheet == null ? string.Empty : SelectedSheet.Name) ?? string.Empty; foreach (var s2 in s) DocumentSheets.Add(s2); SelectedSheet = DocumentSheets.FirstOrDefault(ss => ss.Name == oldSheetName) ?? DocumentSheets.FirstOrDefault(); }); loadWorker.DoWork += (s, e) => { Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("ru-RU"); Thread.CurrentThread.CurrentUICulture = Thread.CurrentThread.CurrentCulture; var bw = (BackgroundWorker)s; var prms = (AsyncLoadInit)e.Argument; var res = new AsyncLoadResult() { StartSettings = prms }; var pp = new Helpers.PercentageProgress(); pp.Change += (sP, eP) => { bw.ReportProgress((int)eP.Value); }; var pp0 = pp.GetChild(); pp0.Weight = 1; var pp1 = pp.GetChild(); pp1.Weight = 3; var pp2 = pp.GetChild(); pp2.Weight = 6; //var pp3 = pp.GetChild(); //pp3.Weight = 3; bw.ReportProgress((int)pp.Value, "Открытие документа..."); res.WorkBook = new Workbook(prms.WorkBookFilePath); pp0.Value = 100; bw.ReportProgress((int)pp.Value, "Чтение первых записей на страницах..."); var subRes = AsyncDocumentLoader.LoadSheets(res.WorkBook, prms.PreloadCount, (i) => { pp1.Value = i; }, prms.DeleteEmptyRows).ToArray(); bw.ReportProgress((int)pp.Value, new AsyncLoadResult() { WorkBook = res.WorkBook, WorkSheets = subRes }); bw.ReportProgress((int)pp.Value, "Чтение всех записей на страницах..."); res.WorkSheets = AsyncDocumentLoader.LoadSheets(res.WorkBook, 0, (i) => { pp2.Value = i; }, prms.DeleteEmptyRows).ToArray(); //foreach (var ws in res.WorkSheets.Select(w => new { WorkSheet = w, Progress = pp3.GetChild() }).ToArray()) //{ // ws.WorkSheet.UpdateHeaders(); //} bw.ReportProgress((int)pp.Value, "Применение результата..."); e.Result = res; }; loadWorker.ProgressChanged += (s, e) => { if (loadWorker != s) return; LoadingProgress = e.ProgressPercentage; var res = e.UserState as AsyncLoadResult; if (res != null) applyRes(res.WorkBook, res.WorkSheets); else { var status = e.UserState as string; if (status != null) Status = status; } }; loadWorker.RunWorkerCompleted += (s, e) => { if (loadWorker == s) { if (e.Cancelled) Error = "Задание отменено пользователем"; if (e.Error != null) { Error = e.Error.GetExceptionText(); } else { var res = e.Result as AsyncLoadResult; if (res != null) { if (res.WorkSheets.Count() == 0) Error = "В документе не найдено ни одного листа"; Status = "Применение результатов (подсчёт заголовков и прочее)..."; applyRes(res.WorkBook, res.WorkSheets); } else Error = "Внутренняя ошибка приложения"; } Status = "Загрузка документа завершена" + (string.IsNullOrWhiteSpace(Error) ? string.Empty : " с ошибками"); LoadingProgress = 100; IsDocumentLoaded = true; IsBusy = false; loadWorker = null; } ((BackgroundWorker)s).Dispose(); }; loadWorker.RunWorkerAsync(init); }
public static ExelRow[] LoadRows(Worksheet sheet, int count = 0, Action <int> progressReport = null, bool deleteEmptyRows = true) { var result = new List <ExelRow>(); var totalRowsCount = 0; var loaded = 0; var pp = new Helpers.PercentageProgress(); if (progressReport != null) { pp.Change += (s, e) => { progressReport((int)e.Value); } } ; var pp0 = pp.GetChild(weight: 9); var pp1 = pp.GetChild(weight: 1); try { totalRowsCount = Math.Max(sheet.Cells.MaxRow + 1, sheet.Cells.Rows.Count); } catch { totalRowsCount = sheet.Cells.Rows.Count; } if (count <= 0) { count = totalRowsCount; } #region Select max column index for sheet int maxColumnIndex = sheet.Cells.Rows.Cast <Row>().Take(count).Select(c => { if (c.LastCell != null) { for (int i = c.LastCell.Column; i >= 0; i--) { var cell = c.GetCellOrNull(i); if (!string.IsNullOrWhiteSpace( cell == null || cell.Value == null ? null : cell.Value.ToString().Trim() ) ) { return(cell.IsMerged ? cell.GetMergedRange().FirstColumn + cell.GetMergedRange().ColumnCount - 1 : i); } } } return(0); } ) .Union(new int[] { 0 }) .Max(); #endregion #region Read rows if (sheet.Cells.Count > 0) { var lockObj = new Object(); var lockCell = new Object(); sheet.Cells.MultiThreadReading = true; var items = sheet.Cells.Rows.Cast <Row>() .Where(r => r.Index < count) .AsParallel() .Select(row => { Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("ru-RU"); Thread.CurrentThread.CurrentUICulture = Thread.CurrentThread.CurrentCulture; var r = new ExelRow() { Index = row.Index }; var currMaxColumnsIndex = row.LastCell == null ? 0 : row.LastCell.Column; int lastFilledColumnIndex = Math.Min(currMaxColumnsIndex, maxColumnIndex); #region Read data from cells var cells = Enumerable.Range(0, lastFilledColumnIndex + 1) .Select(cellIndex => new { OriginalCell = row.GetCellOrNull(cellIndex), Index = cellIndex }) .Select(c => new { OriginalCell = c.OriginalCell, ResultCell = new ExelCell() { Value = string.Empty, FormatedValue = string.Empty, CellStyle = new Style(), OriginalIndex = c.Index } }) .Select(i => { if (i.OriginalCell != null) { lock (lockCell) i.ResultCell.FormatedValue = i.OriginalCell.StringValue; var link = GetHyperlinkForCell(i.OriginalCell, sheet); if (link != null) { i.ResultCell.HyperLink = link.Address; } else { var formula = string.Empty; lock (lockCell) formula = i.OriginalCell.Formula; if (!string.IsNullOrWhiteSpace(formula)) { i.ResultCell.HyperLink = formula.Split(new char[] { '\"' }).FirstOrDefault(str => str.Contains("http")); } } if (i.OriginalCell.IsMerged) { i.ResultCell.IsMerged = true; var content = string.Empty; var values = (IEnumerable)i.OriginalCell.GetMergedRange().Value; if (values != null) { foreach (var value in values) { content += value; } } i.ResultCell.Value = content; } else if (i.OriginalCell.Value != null) { i.ResultCell.Value = i.OriginalCell.Value.ToString(); } else { i.ResultCell.Value = string.Empty; } try { var comment = sheet.Comments[i.OriginalCell.Row, i.ResultCell.OriginalIndex]; if (comment != null) { i.ResultCell.Comment = comment.Note; } } catch { } var style = i.OriginalCell.GetStyle(); i.ResultCell.CellStyle = style; i.ResultCell.Color = (style != null) ? (DefColors.Any(clr => ColorsEqual(clr, style.BackgroundColor)) ? style.ForegroundColor : style.BackgroundColor) : System.Drawing.Color.White; i.ResultCell.Color = System.Drawing.Color.FromArgb((i.ResultCell.Color.R > byte.MinValue || i.ResultCell.Color.G > byte.MinValue || i.ResultCell.Color.B > byte.MinValue) && i.ResultCell.Color.A == byte.MinValue ? byte.MaxValue : i.ResultCell.Color.A, i.ResultCell.Color.R, i.ResultCell.Color.G, i.ResultCell.Color.B); } return(new { ResultCell = i.ResultCell, Index = i.ResultCell.OriginalIndex }); }) .OrderBy(i => i.Index) .Select(i => i.ResultCell) .ToArray(); r.Cells.AddRange(cells); #endregion var lastStyle = (maxColumnIndex > 0) ? r.Cells[lastFilledColumnIndex].CellStyle : new Style(); for (int i = r.Cells.Count; i <= maxColumnIndex; i++) { r.Cells.Add(new ExelCell() { Value = string.Empty, CellStyle = lastStyle }); } lock (lockObj) { loaded++; pp0.Value = (decimal)loaded * 100m / (decimal)count; } return(r); }) .OrderBy(r => r.Index) .Where(r => !deleteEmptyRows || !r.IsEmpty) // delete empty rows .ToArray(); result.AddRange(items); } #endregion if (result.Count > 0) { #region Delete bottom //#### Try to delete bottom info #### //get last empty index var lastEmptyRow = result.LastOrDefault(row => row.IsEmpty); int lastEmptyRowsInDataIndex = lastEmptyRow == null ? 0 : result.IndexOf(lastEmptyRow); //int lastEmptyRowsInDataIndex = // result // .Where(row => row.IsEmpty) // .Select(row => result.IndexOf(row)) // .OrderByDescending(i => i) // .FirstOrDefault(); //get last non empty index before last empty to check similarity var lastNotEmptyRow = result.Take(lastEmptyRowsInDataIndex == 0 ? result.Count : lastEmptyRowsInDataIndex).LastOrDefault(row => !row.IsEmpty); int lastNotEmptyRowsInDataIndexBeforeEmpty = lastNotEmptyRow == null ? 0 : result.IndexOf(lastNotEmptyRow); //int lastNotEmptyRowsInDataIndexBeforeEmpty = // result // .Where(row => !row.IsEmpty) // .Select(row => result.IndexOf(row)) // .Where(i => i < lastEmptyRowsInDataIndex) // .OrderByDescending(i => i) // .FirstOrDefault(); if (lastEmptyRowsInDataIndex > 4 && result.Count - lastEmptyRowsInDataIndex < 15) { List <ExelRow> similarityIndexes = new List <ExelRow>(); for (int i = lastNotEmptyRowsInDataIndexBeforeEmpty; i >= 0 && i > lastNotEmptyRowsInDataIndexBeforeEmpty - 10; i--) { similarityIndexes.Add(result[i]); } for (int z = result.Count - 1; z >= lastEmptyRowsInDataIndex; z--) { if (result[z].NotEmptyCells.Count() <= 4 && !similarityIndexes.Select(s => s.Similarity(result[z])).Any(d => d > 0.6)) { result.RemoveAt(z); } else { break; } } } pp1.Value = 25; var isRowsSimilar = new Func <ExelRow, ExelRow, bool>((r1, r2) => { var cnt = Math.Min(r1.Cells.Count, r2.Cells.Count); for (int i = 0; i < cnt; i++) { if (string.Compare(r1.Cells[i].Value.Trim(), r2.Cells[i].Value.Trim(), true) != 0) { return(false); } } return(true); }); var defStart = 4; var minCountForDeleteSimilarity = Properties.Settings.Default.MaxRowsInGroupCountToDeleteSimilarityRows; for (int i = result.Count - 1; i >= defStart; i--) { var n = 1; if ((i - n >= defStart) && result[i].Similarity(result[i - n]) >= 0.8 && isRowsSimilar(result[i], result[i - n])) { var rowIndexes = new List <int>(new int[] { i - n }); do { n++; rowIndexes.Add(i - n); } while ((i - n >= defStart) && result[i].Similarity(result[i - n]) >= 0.8 && isRowsSimilar(result[i], result[i - n])); if (n + 1 >= minCountForDeleteSimilarity) { rowIndexes .OrderByDescending(ind => ind) .ToList() .ForEach(ind => result.RemoveAt(ind)); i -= n; continue; //result.RemoveAt(i); } } } pp1.Value = 50; #endregion //Delete all empty rows from data if (deleteEmptyRows) { for (int z = result.Count - 1; z >= 0; z--) { var r1 = result[z]; if (r1.IsEmpty) { result.RemoveAt(z); } } } pp1.Value = 100; } return(result.ToArray()); }
private void UpdateStep(int step, bool action, bool clear = false, byte insideThreadCount = 3) { if (clear) UrlsToAddList.Clear(); #region step 1 if (step == 1 && action) { object lockAdd = new Object(); UrlsToAddList.Clear(); #if DEBUG insideThreadCount = 1; #else insideThreadCount = ThreadCount; #endif ParseRuleConnectionType type = NewParseRule.Connection; int minWidth = NewParseRule.MinImageWidth; int minHeight = NewParseRule.MinImageHeight; bool collectIMGTags = NewParseRule.CollectIMGTags; bool collectLINKTags = NewParseRule.CollectLINKTags; bool collectMETATags = NewParseRule.CollectMETATags; byte threadCount = this.ThreadCount; BackgroundWorker bw = new BackgroundWorker(); bw.DoWork += (s, e) => { Helpers.PercentageProgress progress = new Helpers.PercentageProgress(); progress.Change += (sP, eP) => { bw.ReportProgress((int)eP.Value); }; List<UrlResultWrapper> urlResultWrapper = new List<UrlResultWrapper>(); var urls = e.Argument as StringUrlWithResultWrapper[]; if (urls != null) urls .Where(item => item != null && !string.IsNullOrWhiteSpace(item.Value) && Helper.IsWellFormedUriString(item.Value, UriKind.Absolute)) .Select(sw => new { item = sw, prgItem = progress.GetChild() }) .ToArray() .AsParallel() .WithDegreeOfParallelism(insideThreadCount) .ForAll( (sw) => { var item = new UrlResultWrapper() { Value = sw.item.Value }; System.Drawing.Size minSize = new System.Drawing.Size() { Width = minWidth, Height = minHeight }; var result = Helper.GetAllImagesFromUrl(item.Value, minSize, collectIMGTags, collectLINKTags, collectMETATags, threadCount, sw.prgItem, true, type); foreach (ParseImageResult res in result) item.ParseResult.Add(res); if (item.ParseResult.Count > 0) lock (lockAdd) { urlResultWrapper.Add(item); } }); e.Result = urlResultWrapper; }; bw.RunWorkerCompleted += (s, e) => { if (e.Error != null) throw e.Error; try { List<UrlResultWrapper> urlResultWrapper = e.Result as List<UrlResultWrapper>; foreach (var item in urlResultWrapper) { if (item.ParseResult != null) foreach (var ps in item.ParseResult) ps.IsSelected = (item.ParseResult.IndexOf(ps) == 0); UrlsToAddList.Add(item); } } finally { bw.Dispose(); IsBusy = false; } }; bw.WorkerReportsProgress = true; bw.ProgressChanged += (s, e) => { LoadedPercent = e.ProgressPercentage; }; IsBusy = true; bw.RunWorkerAsync(Urls.ToArray()); while (bw.IsBusy) Helper.DoEvents(); bw = null; } #endregion #region step 2 else if (step == 2 && action) { HtmlNodeWithUrl[] nodes = UrlsToAddList .Where(n => !string.IsNullOrWhiteSpace(n.Value)) .Select(i => { ParseImageResult res = i.ParseResult.Where(i2 => i2.IsSelected).FirstOrDefault(); return new HtmlNodeWithUrl() { Node = res == null ? null : res.Node, Url = res == null ? new Uri(i.Value, UriKind.RelativeOrAbsolute) : res.Url }; } ) .Where(i3 => i3 != null && i3.Node != null) .ToArray(); ParseRule newRule = Helper.GetRule(nodes, NewParseRule.Label, NewParseRule.MinImageSize, NewParseRule.CollectIMGTags, NewParseRule.CollectLINKTags, NewParseRule.CollectMETATags); newRule.CopyObject(NewParseRule, new string[] { "Connection" }); ShowRuleModeCommand.Execute(null); } #endregion if (step >= UrlsToAddTabControl.Items.Count) for (int i = UrlsToAddTabControl.Items.Count - 1; i >= 0; i--) (UrlsToAddTabControl.Items[i] as TabItem).Visibility = (i == UrlsToAddTabControl.Items.Count - 1) ? System.Windows.Visibility.Visible : System.Windows.Visibility.Collapsed; else if (step < 0) for (int i = UrlsToAddTabControl.Items.Count - 1; i >= 0; i--) (UrlsToAddTabControl.Items[i] as TabItem).Visibility = (i == 0) ? System.Windows.Visibility.Visible : System.Windows.Visibility.Collapsed; else { for (int i = UrlsToAddTabControl.Items.Count - 1; i >= 0; i--) (UrlsToAddTabControl.Items[i] as TabItem).Visibility = (i == step) ? System.Windows.Visibility.Visible : System.Windows.Visibility.Collapsed; } UrlsToAddTabControl.SelectedIndex = UrlsToAddTabControl.Items.IndexOf(UrlsToAddTabControl.Items.Cast<TabItem>().FirstOrDefault(ti => ti.Visibility == System.Windows.Visibility.Visible)); }
public static BackgroundWorker Start(string fileName, long currentOperatorId, IEnumerable<SheetRulePair> ruleToSheets) { BackgroundWorker result = new BackgroundWorker(); result.DoWork += (s, e) => { StringBuilder errors = new StringBuilder(); bool showLogAnytime = false; BackgroundWorker current = s as BackgroundWorker; PercentageProgress fullProgress = new PercentageProgress(); var readRulesProgress = fullProgress.GetChild(); var getLinksProgress = fullProgress.GetChild(); var writeExcelFileProgress = fullProgress.GetChild(); fullProgress.Change += (s2, args) => { current.ReportProgress((int)args.Value); }; ObservableCollection<OutputRow> rowsToExport = new ObservableCollection<OutputRow>(); bool wasException = false; var logSession = Helpers.Old.Log.SessionStart("ReExport.ReExport()", true); try { #region Read Rules Helpers.Old.Log.Add(logSession, string.Format("total sheets count: '{0}'", ruleToSheets.Count())); int rulesToSheets = ruleToSheets.Count(); int ind = 0; foreach (var item in ruleToSheets) { var mappingRule = item.Rule; var ds = item.Sheet; if (mappingRule != null && ds != null) { if (ds.MainHeader == null) { Helpers.Old.Log.Add(logSession, string.Format("should update main header row...")); ds.UpdateMainHeaderRow(mappingRule.MainHeaderSearchTags .Select(h => h.Tag) .Union(SettingsProvider.CurrentSettings.HeaderSearchTags.Split(new char[] { ',' })) .Select(i => i.Trim()) .Where(i => !string.IsNullOrEmpty(i)) .Distinct() .ToArray()); ds.UpdateHeaders(mappingRule.SheetHeadersSearchTags .Select(h => h.Tag.Trim()) .Where(i => !string.IsNullOrEmpty(i)) .Distinct() .ToArray()); } var oc = new ObservableCollection<OutputRow>(mappingRule.Convert(ds, new string[] { "Code" })); Helpers.Old.Log.Add(logSession, string.Format("row count on sheet '{0}' : '{1}'", ds.Name, oc.Count)); rowsToExport = new ObservableCollection<OutputRow>(rowsToExport.Union(oc)); Helpers.Old.Log.Add(logSession, string.Format("subtotal row count on sheets: '{0}'", rowsToExport.Count)); } ind++; readRulesProgress.Value = ((decimal)ind / (decimal)rulesToSheets) * 100m; } Helpers.Old.Log.Add(logSession, string.Format("total row count to export: '{0}'", rowsToExport.Count)); #endregion #region Get Links Helpers.Old.Log.Add(logSession, string.Format("Try to get links...")); var idsToGet = rowsToExport .Select(i => new ReExportData(i.Code.Trim(), i.OriginalIndex, i.OriginalSheet)) .Cast<ReExportData>() .ToList(); string outerMap; string outerPdf; try { HttpDataClient.Default.GetResourcesList(currentOperatorId, idsToGet, out outerMap, out outerPdf); } catch(Exception ex) { throw new Exception(string.Format("Ошибка при получении ссылок для кодов (кол-во: {0})", idsToGet.Count), ex); } //Log.Add(logSession, string.Format("Get CODES from server...")); //foreach (var i in idsToGet) // Log.Add(logSession, string.Format("[Code:'{0}',Location:'{1}',Map:'{2}',Photo:'{3}']", i.Code, i.LinkLocation, i.LinkMap, i.LinkPhoto)); //Log.Add(logSession, string.Format("Get CODES done")); //HttpDataAccess.GetResourcesList(currentOperatorId, idsToGet, out outerMap, out outerPdf); Helpers.Old.Log.Add(logSession, string.Format("Links getted")); getLinksProgress.Value = 100; #endregion #region Write Excel File Workbook wb = new Workbook(fileName); foreach (var r in ruleToSheets.Where(r => r.Sheet != null)) { var sheet = wb.Worksheets.Cast<Worksheet>().FirstOrDefault(sht => sht.Name.ToLower().Trim() == r.Sheet.Name.ToLower().Trim()); if (sheet != null) { int CodeColumnIndex = -1; string CodeColumnName = string.Empty; var convData = r.Rule.ConvertionData.FirstOrDefault(cd => cd.PropertyId == "Code" ); if (convData != null) { var block = convData.Blocks.Blocks.FirstOrDefault(); if (block != null) { var func = block.UsedFunctions.FirstOrDefault(); if (func != null) { CodeColumnName = func.Function.ColumnName; if (string.IsNullOrWhiteSpace(func.Function.ColumnName) || func.Function.SelectedParameter == Core.Converter.Functions.FunctionParameters.CellNumber) CodeColumnIndex = func.Function.ColumnNumber; } } } if (!string.IsNullOrWhiteSpace(CodeColumnName) || (CodeColumnIndex != -1)) { if (CodeColumnIndex == -1) CodeColumnIndex = r.Sheet.MainHeader.Cells.FirstOrDefault(c => string.Compare(c.Value, CodeColumnName, true) == 0)?.OriginalIndex ?? -1; #region Add cells to rows if (CodeColumnIndex >= 0) { int lastIndex = sheet.Cells.Rows.Cast<Row>().Select(c => { if (c.LastCell != null) for (int i = c.LastCell.Column; i >= 0; i--) { var cell = c.GetCellOrNull(i); if (!string.IsNullOrWhiteSpace( cell == null || cell.Value == null ? null : cell.Value.ToString().Trim() ) ) return cell.IsMerged ? cell.GetMergedRange().FirstColumn + cell.GetMergedRange().ColumnCount - 1 : i; } return 0; } ) .Union(new int[] { 0 }) .Max(); var h0 = sheet.Cells[r.Sheet.MainHeader.Index, lastIndex + 0]; var h1 = sheet.Cells[r.Sheet.MainHeader.Index, lastIndex + 1]; var h2 = sheet.Cells[r.Sheet.MainHeader.Index, lastIndex + 2]; var h3 = sheet.Cells[r.Sheet.MainHeader.Index, lastIndex + 3]; sheet.Cells.Columns[h1.Column].IsHidden = false; sheet.Cells.Columns[h2.Column].IsHidden = false; sheet.Cells.Columns[h3.Column].IsHidden = false; h1.Value = "Фото"; h2.Value = "Схема"; h3.Value = "Карта"; Style h0Style = h0.GetStyle(); h1.SetStyle(h0Style); h2.SetStyle(h0Style); h3.SetStyle(h0Style); foreach (var item in idsToGet.Where(i => i.OriginalSheet == sheet.Name)) { if (!string.IsNullOrWhiteSpace(item.LinkPhoto)) { var c1 = sheet.Cells[item.OriginalIndex, lastIndex + 1]; c1.Value = "фото"; sheet.Hyperlinks.Add(c1.Name, 1, 1, item.LinkPhoto); } if (!string.IsNullOrWhiteSpace(item.LinkLocation)) { var c2 = sheet.Cells[item.OriginalIndex, lastIndex + 2]; c2.Value = "схема"; sheet.Hyperlinks.Add(c2.Name, 1, 1, item.LinkLocation); } if (!string.IsNullOrWhiteSpace(item.LinkMap)) { var c3 = sheet.Cells[item.OriginalIndex, lastIndex + 3]; c3.Value = "карта"; sheet.Hyperlinks.Add(c3.Name, 1, 1, item.LinkMap); } } if (sheet.Cells.LastCell != null) { int lastRowIndex = sheet.Cells.LastCell.Row; int rowAdd = 3; if (outerPdf != null) { var outer0 = sheet.Cells[lastRowIndex + rowAdd, CodeColumnIndex + 0]; var outer1 = sheet.Cells[lastRowIndex + rowAdd, CodeColumnIndex + 1]; outer0.Value = "Скачать оффлайн PDF-презентацию"; outer1.Value = outerPdf; sheet.Hyperlinks.Add(outer0.Name, 1, 1, outerPdf); rowAdd++; } if (outerMap != null) { var outer0 = sheet.Cells[lastRowIndex + rowAdd, CodeColumnIndex + 0]; var outer1 = sheet.Cells[lastRowIndex + rowAdd, CodeColumnIndex + 1]; outer0.Value = "Карта"; outer1.Value = outerMap; sheet.Hyperlinks.Add(outer0.Name, 1, 1, outerMap); rowAdd++; } } } else { errors.AppendFormat("в Excel-файле на вкладке '{0}' невозможно найти колонку с именем '{1}'", sheet.Name, CodeColumnName); errors.AppendLine(); //throw new Exception(string.Format("в Excel-файле на вкладке '{0}' невозможно найти колонку с кодом '{1}'", sheet.Name, CodeColumnName)); } #endregion } else { errors.AppendFormat("Отсутствует правило для определения кода для вкладки '{0}'", sheet.Name); errors.AppendLine(); //throw new Exception(string.Format("Отсутствует правило для определения кода для вкладки '{0}'", sheet.Name)); } } } wb.Save(fileName); e.Result = errors.ToString(); #endregion } catch (Exception ex) { wasException = true; Helpers.Old.Log.Add(logSession, ex); throw ex; } finally { current.ReportProgress(100); Helpers.Old.Log.SessionEnd(logSession, wasException || showLogAnytime); } }; result.WorkerReportsProgress = true; result.WorkerSupportsCancellation = true; return result; }
public static ExelRow[] LoadRows(Worksheet sheet, int count = 0, Action<int> progressReport = null, bool deleteEmptyRows = true) { var result = new List<ExelRow>(); var totalRowsCount = 0; var loaded = 0; var pp = new Helpers.PercentageProgress(); if (progressReport != null) pp.Change += (s, e) => { progressReport((int)e.Value); }; var pp0 = pp.GetChild(weight: 9); var pp1 = pp.GetChild(weight: 1); try { totalRowsCount = Math.Max(sheet.Cells.MaxRow + 1, sheet.Cells.Rows.Count); } catch { totalRowsCount = sheet.Cells.Rows.Count; } if (count <= 0) count = totalRowsCount; #region Select max column index for sheet int maxColumnIndex = sheet.Cells.Rows.Cast<Row>().Take(count).Select(c => { if (c.LastCell != null) for (int i = c.LastCell.Column; i >= 0; i--) { var cell = c.GetCellOrNull(i); if (!string.IsNullOrWhiteSpace( cell == null || cell.Value == null ? null : cell.Value.ToString().Trim() ) ) return cell.IsMerged ? cell.GetMergedRange().FirstColumn + cell.GetMergedRange().ColumnCount - 1 : i; } return 0; } ) .Union(new int[] { 0 }) .Max(); #endregion #region Read rows if (sheet.Cells.Count > 0) { var lockObj = new Object(); var lockCell = new Object(); sheet.Cells.MultiThreadReading = true; var items = sheet.Cells.Rows.Cast<Row>() .Where(r => r.Index < count) .AsParallel() .Select(row => { Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("ru-RU"); Thread.CurrentThread.CurrentUICulture = Thread.CurrentThread.CurrentCulture; var r = new ExelRow() { Index = row.Index }; var currMaxColumnsIndex = row.LastCell == null ? 0 : row.LastCell.Column; int lastFilledColumnIndex = Math.Min(currMaxColumnsIndex, maxColumnIndex); #region Read data from cells var cells = Enumerable.Range(0, lastFilledColumnIndex + 1) .Select(cellIndex => new { OriginalCell = row.GetCellOrNull(cellIndex), Index = cellIndex }) .Select(c => new { OriginalCell = c.OriginalCell, ResultCell = new ExelCell() { Value = string.Empty, FormatedValue = string.Empty, CellStyle = new Style(), OriginalIndex = c.Index } }) .Select(i => { if (i.OriginalCell != null) { lock(lockCell) i.ResultCell.FormatedValue = i.OriginalCell.StringValue; var link = GetHyperlinkForCell(i.OriginalCell, sheet); if (link != null) i.ResultCell.HyperLink = link.Address; else { var formula = string.Empty; lock(lockCell) formula = i.OriginalCell.Formula; if (!string.IsNullOrWhiteSpace(formula)) i.ResultCell.HyperLink = formula.Split(new char[] { '\"' }).FirstOrDefault(str => str.Contains("http")); } if (i.OriginalCell.IsMerged) { i.ResultCell.IsMerged = true; var content = string.Empty; var values = (IEnumerable)i.OriginalCell.GetMergedRange().Value; if (values != null) foreach (var value in values) content += value; i.ResultCell.Value = content; } else if (i.OriginalCell.Value != null) { i.ResultCell.Value = i.OriginalCell.Value.ToString(); } else { i.ResultCell.Value = string.Empty; } try { var comment = sheet.Comments[i.OriginalCell.Row, i.ResultCell.OriginalIndex]; if (comment != null) i.ResultCell.Comment = comment.Note; } catch { } var style = i.OriginalCell.GetStyle(); i.ResultCell.CellStyle = style; i.ResultCell.Color = (style != null) ? (DefColors.Any(clr => ColorsEqual(clr, style.BackgroundColor)) ? style.ForegroundColor : style.BackgroundColor) : System.Drawing.Color.White; i.ResultCell.Color = System.Drawing.Color.FromArgb((i.ResultCell.Color.R > byte.MinValue || i.ResultCell.Color.G > byte.MinValue || i.ResultCell.Color.B > byte.MinValue) && i.ResultCell.Color.A == byte.MinValue ? byte.MaxValue : i.ResultCell.Color.A, i.ResultCell.Color.R, i.ResultCell.Color.G, i.ResultCell.Color.B); } return new { ResultCell = i.ResultCell, Index = i.ResultCell.OriginalIndex }; }) .OrderBy(i => i.Index) .Select(i => i.ResultCell) .ToArray(); r.Cells.AddRange(cells); #endregion var lastStyle = (maxColumnIndex > 0) ? r.Cells[lastFilledColumnIndex].CellStyle : new Style(); for (int i = r.Cells.Count; i <= maxColumnIndex; i++) r.Cells.Add(new ExelCell() { Value = string.Empty, CellStyle = lastStyle }); lock (lockObj) { loaded++; pp0.Value = (decimal)loaded * 100m / (decimal)count; } return r; }) .OrderBy(r => r.Index) .Where(r => !deleteEmptyRows || !r.IsEmpty) // delete empty rows .ToArray(); result.AddRange(items); } #endregion if (result.Count > 0) { #region Delete bottom //#### Try to delete bottom info #### //get last empty index var lastEmptyRow = result.LastOrDefault(row => row.IsEmpty); int lastEmptyRowsInDataIndex = lastEmptyRow == null ? 0 : result.IndexOf(lastEmptyRow); //int lastEmptyRowsInDataIndex = // result // .Where(row => row.IsEmpty) // .Select(row => result.IndexOf(row)) // .OrderByDescending(i => i) // .FirstOrDefault(); //get last non empty index before last empty to check similarity var lastNotEmptyRow = result.Take(lastEmptyRowsInDataIndex == 0 ? result.Count : lastEmptyRowsInDataIndex).LastOrDefault(row => !row.IsEmpty); int lastNotEmptyRowsInDataIndexBeforeEmpty = lastNotEmptyRow == null ? 0 : result.IndexOf(lastNotEmptyRow); //int lastNotEmptyRowsInDataIndexBeforeEmpty = // result // .Where(row => !row.IsEmpty) // .Select(row => result.IndexOf(row)) // .Where(i => i < lastEmptyRowsInDataIndex) // .OrderByDescending(i => i) // .FirstOrDefault(); if (lastEmptyRowsInDataIndex > 4 && result.Count - lastEmptyRowsInDataIndex < 15) { List<ExelRow> similarityIndexes = new List<ExelRow>(); for (int i = lastNotEmptyRowsInDataIndexBeforeEmpty; i >= 0 && i > lastNotEmptyRowsInDataIndexBeforeEmpty - 10; i--) similarityIndexes.Add(result[i]); for (int z = result.Count - 1; z >= lastEmptyRowsInDataIndex; z--) { if (result[z].NotEmptyCells.Count() <= 4 && !similarityIndexes.Select(s => s.Similarity(result[z])).Any(d => d > 0.6)) result.RemoveAt(z); else break; } } pp1.Value = 25; var isRowsSimilar = new Func<ExelRow, ExelRow, bool>((r1, r2) => { var cnt = Math.Min(r1.Cells.Count, r2.Cells.Count); for (int i = 0; i < cnt; i++) if (string.Compare(r1.Cells[i].Value.Trim(), r2.Cells[i].Value.Trim(), true) != 0) return false; return true; }); var defStart = 4; var minCountForDeleteSimilarity = Properties.Settings.Default.MaxRowsInGroupCountToDeleteSimilarityRows; for (int i=result.Count-1; i>= defStart; i--) { var n = 1; if ((i - n >= defStart) && result[i].Similarity(result[i - n]) >= 0.8 && isRowsSimilar(result[i], result[i - n])) { var rowIndexes = new List<int>(new int[] { i - n }); do { n++; rowIndexes.Add(i - n); } while ((i - n >= defStart) && result[i].Similarity(result[i - n]) >= 0.8 && isRowsSimilar(result[i], result[i - n])); if (n + 1 >= minCountForDeleteSimilarity) { rowIndexes .OrderByDescending(ind => ind) .ToList() .ForEach(ind => result.RemoveAt(ind)); i -= n; continue; //result.RemoveAt(i); } } } pp1.Value = 50; #endregion //Delete all empty rows from data if (deleteEmptyRows) for (int z = result.Count - 1; z >= 0; z--) { var r1 = result[z]; if (r1.IsEmpty) result.RemoveAt(z); } pp1.Value = 100; } return result.ToArray(); }
public void Initialize() { InitializeError = string.Empty; if (Export2CsvCommand == null) Export2CsvCommand = new RelayCommand(() => Export2Csv()); if (Export2DbCommand == null) Export2DbCommand = new RelayCommand(Export2Db); if (UpdateErrorsCommand == null) UpdateErrorsCommand = new RelayCommand(UpdateErrors); if (UpdateSelectedErrorCommand == null) UpdateSelectedErrorCommand = new RelayCommand(UpdateSelectedError); if (UpdateSelectedWarningCommand == null) UpdateSelectedWarningCommand = new RelayCommand(UpdateSelectedWarning); loadWorker = new BackgroundWorker() { WorkerReportsProgress = true, WorkerSupportsCancellation = true }; loadWorker.DoWork += (s, prm) => { var Disp = prm.Argument as System.Windows.Threading.Dispatcher; ObservableCollection<OutputRow> rowsToExport = new ObservableCollection<OutputRow>(); Guid logSession = Helpers.Old.Log.SessionStart("ExportViewModel.Initialize()"); try { Log.Add(string.Format("total sheets count: '{0}'", App.Locator.Import.Document.DocumentSheets.Count)); var addErr = new List<Error>(); var addGErr = new List<GlobalError>(); var progress = new PercentageProgress(); foreach (var item in App.Locator.Import .ExportRules .Where(r => r.Rule != App.Locator.Import.NullRule) .Select(r => new { Rule = r, Progress = progress.GetChild() }) .ToArray() ) { if (((BackgroundWorker)s).CancellationPending || s != loadWorker) break; var mappingRule = item.Rule.Rule; var ds = item.Rule.Sheet; if (mappingRule == null || ds == null) { if (!string.IsNullOrWhiteSpace(item.Rule.Status)) addGErr.Add(new GlobalError() { Description = item.Rule.Status }); item.Progress.Value = 100; continue; } else { if (ds.MainHeader == null) { Log.Add(string.Format("should update main header row...")); ds.UpdateMainHeaderRow(mappingRule.MainHeaderSearchTags .Select(h => h.Tag) .Union(SettingsProvider.CurrentSettings.HeaderSearchTags.Split(new char[] { ',' })) .Select(i => i.Trim()) .Where(i => !string.IsNullOrEmpty(i)) .Distinct() .ToArray()); ds.UpdateHeaders(mappingRule.SheetHeadersSearchTags .Select(h => h.Tag.Trim()) .Where(i => !string.IsNullOrEmpty(i)) .Distinct() .ToArray()); } var oc = new ObservableCollection<OutputRow>(mappingRule.Convert(ds, progressAction: (i) => { item.Progress.Value = i; ((BackgroundWorker)s).ReportProgress((int)progress.Value); }, isCanceled: () => { return ((BackgroundWorker)s).CancellationPending; }, additionalErrorAction: (e, r) => { addErr.Add(new Error() { Description = e.GetExceptionText(includeStackTrace: false, clearText: true).Trim(), RowNumber = r }); })); Log.Add(string.Format("row count on sheet '{0}' : '{1}'", ds.Name, oc.Count)); rowsToExport = new ObservableCollection<OutputRow>(rowsToExport.Union(oc)); Log.Add(string.Format("subtotal row count on sheets: '{0}'", rowsToExport.Count)); } } ExelConvertionRule.RemoveRepeatingId(rowsToExport.ToList()); var UrlsPhoto = new UrlCollection(); var UrlsSchema = new UrlCollection(); var UrlsAll = new UrlCollection(); var photos = rowsToExport.Select(r => r.Photo_img).Where(r => Helper.IsWellFormedUriString(r, UriKind.Absolute)).Distinct(); var schemas = rowsToExport.Select(r => r.Location_img).Where(r => Helper.IsWellFormedUriString(r, UriKind.Absolute)).Distinct(); var all = photos.Union(schemas).Distinct(); foreach (var p in photos) UrlsPhoto.Add(new StringUrlWithResultWrapper(p)); foreach (var p in schemas) UrlsSchema.Add(new StringUrlWithResultWrapper(p)); foreach (var p in all) UrlsAll.Add(new StringUrlWithResultWrapper(p)); if (!((BackgroundWorker)s).CancellationPending && s == loadWorker) Disp.Invoke(System.Windows.Threading.DispatcherPriority.Normal, new Action(() => { UrlCollection.Clear(); if (UrlsPhoto.Count > 0) UrlCollection.Add(new UrlCollectionAdditional() { Name = DBParsers.Labels.ElementAt(0), Collection = UrlsPhoto }); if (UrlsSchema.Count > 0) UrlCollection.Add(new UrlCollectionAdditional() { Name = DBParsers.Labels.ElementAt(1), Collection = UrlsSchema }); if (UrlsPhoto.Count > 0 && UrlsSchema.Count > 0 && UrlsAll.Count > 0) UrlCollection.Add(new UrlCollectionAdditional() { Name = "Все", Collection = UrlsAll }); UrlCollectionSelectedIndex = UrlCollection.Count - 1; RowsToExport = rowsToExport; UpdateErrors(addErr, addGErr); })); else prm.Cancel = true; } catch (Exception ex) { Helpers.Old.Log.Add(logSession, ex.GetExceptionText()); throw ex; } finally { Log.Add(string.Format("total row count to export: '{0}'", rowsToExport.Count)); Helpers.Old.Log.SessionEnd(logSession); } }; loadWorker.ProgressChanged += (s, e) => { if (s == loadWorker) LoadProgress = e.ProgressPercentage; }; loadWorker.RunWorkerCompleted += (s, e) => { if (s == loadWorker) try { if (e.Cancelled) throw new Exception("Загрузка отменена пользователем"); if (e.Error != null) throw e.Error; } catch (Exception ex) { InitializeError = ex.GetExceptionText(); } finally { if (s == loadWorker) { IsLoading = false; loadWorker = null; } } ((BackgroundWorker)s).Dispose(); }; LoadProgress = 0; IsLoading = true; CancelCommand.RaiseCanExecuteChanged(); loadWorker.RunWorkerAsync(System.Windows.Threading.Dispatcher.CurrentDispatcher); }
private void Load() { if (loadWorker != null) { try { loadWorker.CancelAsync(); } catch { } } LoadingProgress = 0; IsDocumentLoaded = false; Error = string.Empty; if (string.IsNullOrWhiteSpace(Path)) { return; } IsBusy = true; loadWorker = new BackgroundWorker() { WorkerReportsProgress = true, WorkerSupportsCancellation = true }; var init = new AsyncLoadInit() { DeleteEmptyRows = this.DeleteEmptyRows, WorkBookFilePath = this.Path, PreloadCount = Settings.SettingsProvider.CurrentSettings.PreloadedRowsCount }; var applyRes = new Action <Workbook, ExelSheet[]>((w, s) => { WorkBook = w; DocumentSheets.Clear(); var oldSheetName = (SelectedSheet == null ? string.Empty : SelectedSheet.Name) ?? string.Empty; foreach (var s2 in s) { DocumentSheets.Add(s2); } SelectedSheet = DocumentSheets.FirstOrDefault(ss => ss.Name == oldSheetName) ?? DocumentSheets.FirstOrDefault(); }); loadWorker.DoWork += (s, e) => { Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("ru-RU"); Thread.CurrentThread.CurrentUICulture = Thread.CurrentThread.CurrentCulture; var bw = (BackgroundWorker)s; var prms = (AsyncLoadInit)e.Argument; var res = new AsyncLoadResult() { StartSettings = prms }; var pp = new Helpers.PercentageProgress(); pp.Change += (sP, eP) => { bw.ReportProgress((int)eP.Value); }; var pp0 = pp.GetChild(); pp0.Weight = 1; var pp1 = pp.GetChild(); pp1.Weight = 3; var pp2 = pp.GetChild(); pp2.Weight = 6; //var pp3 = pp.GetChild(); //pp3.Weight = 3; bw.ReportProgress((int)pp.Value, "Открытие документа..."); res.WorkBook = new Workbook(prms.WorkBookFilePath); pp0.Value = 100; bw.ReportProgress((int)pp.Value, "Чтение первых записей на страницах..."); var subRes = AsyncDocumentLoader.LoadSheets(res.WorkBook, prms.PreloadCount, (i) => { pp1.Value = i; }, prms.DeleteEmptyRows).ToArray(); bw.ReportProgress((int)pp.Value, new AsyncLoadResult() { WorkBook = res.WorkBook, WorkSheets = subRes }); bw.ReportProgress((int)pp.Value, "Чтение всех записей на страницах..."); res.WorkSheets = AsyncDocumentLoader.LoadSheets(res.WorkBook, 0, (i) => { pp2.Value = i; }, prms.DeleteEmptyRows).ToArray(); //foreach (var ws in res.WorkSheets.Select(w => new { WorkSheet = w, Progress = pp3.GetChild() }).ToArray()) //{ // ws.WorkSheet.UpdateHeaders(); //} bw.ReportProgress((int)pp.Value, "Применение результата..."); e.Result = res; }; loadWorker.ProgressChanged += (s, e) => { if (loadWorker != s) { return; } LoadingProgress = e.ProgressPercentage; var res = e.UserState as AsyncLoadResult; if (res != null) { applyRes(res.WorkBook, res.WorkSheets); } else { var status = e.UserState as string; if (status != null) { Status = status; } } }; loadWorker.RunWorkerCompleted += (s, e) => { if (loadWorker == s) { if (e.Cancelled) { Error = "Задание отменено пользователем"; } if (e.Error != null) { Error = e.Error.GetExceptionText(); } else { var res = e.Result as AsyncLoadResult; if (res != null) { if (res.WorkSheets.Count() == 0) { Error = "В документе не найдено ни одного листа"; } Status = "Применение результатов (подсчёт заголовков и прочее)..."; applyRes(res.WorkBook, res.WorkSheets); } else { Error = "Внутренняя ошибка приложения"; } } Status = "Загрузка документа завершена" + (string.IsNullOrWhiteSpace(Error) ? string.Empty : " с ошибками"); LoadingProgress = 100; IsDocumentLoaded = true; IsBusy = false; loadWorker = null; } ((BackgroundWorker)s).Dispose(); }; loadWorker.RunWorkerAsync(init); }
public static ExelSheet[] LoadSheets(Workbook workbook, int count = 0, Action<int> progressReport = null, bool deleteEmptyRows = true) { var result = new List<ExelSheet>(); //var totalRowsCount = 0; //var loaded = 0; //totalRowsCount = workbook.Worksheets.Cast<Worksheet>().Select(i => i.Cells.Rows.Count).Sum(); Helpers.PercentageProgress prg = new Helpers.PercentageProgress(); prg.Change += (s, e) => { if (progressReport != null) progressReport((int)e.Value); }; foreach (var sheetItem in workbook .Worksheets .Cast<Worksheet>() .Where(s => s.Cells.Rows.Count > 1) .Select(s => new { Sheet = s, ProgressInfo = prg.GetChild() }) .ToArray() ) { ExelSheet sht = new ExelSheet() { Name = sheetItem.Sheet.Name }; sht.Rows.AddRange( LoadRows(sheetItem.Sheet, count, new Action<int>((i) => { sheetItem.ProgressInfo.Value = i; }), deleteEmptyRows) ); if (sht.Rows.Count > 0) result.Add(sht); } return result.ToArray(); }
/// <summary> /// Получить соответствия данным из загружаемой таблицы /// </summary> /// <param name="dataTable">Загружаемая таблица</param> /// <param name="progressAction">Действие для отображения прогресса</param> /// <param name="logAction">Действие для отображения лога</param> /// <returns>Словарь соответсвия найденных данных для каждой строки таблицы</returns> public IDictionary<DataRow, DataPreviewRow> Preview(DataTable dataTable, Action<decimal> progressAction = null, Action<string> logAction = null) { progressAction = progressAction ?? new Action<decimal>((i) => { }); logAction = logAction ?? new Action<string>((i) => { }); using (var logSession = Log.Session($"{this.GetType().Name}.{nameof(Preview)}()", VerboseLog)) try { logSession.Output = (strs) => strs.ToList().ForEach(s => logAction(s)); if (dataTable == null) throw new ArgumentNullException(nameof(dataTable)); var pp = new Helpers.PercentageProgress(); var ppPrepare = pp.GetChild(weight: 0.1m); var ppHostes = pp.GetChild(weight: 0.1m); var ppPhones = pp.GetChild(weight: 0.1m); var ppMarks = pp.GetChild(weight: 0.1m); var ppCities = pp.GetChild(weight: 0.1m); var ppStreets = pp.GetChild(weight: 0.7m); var ppAddresses = pp.GetChild(weight: 0.15m); var ppRows = pp.GetChild(weight: 0.3m); pp.Change += (s, e) => progressAction(e.Value); #region Get column names by column types logSession.Add("Get column names by column types"); var columnNames = dataTable.Columns.OfType<DataColumn>().Select(c => c.ColumnName.ToUpper()).ToArray(); var columnByTypes = typeof(ColumnTypes) .GetEnumValues() .Cast<ColumnTypes>() .Select(ct => new { Type = ct, Column = Account.Settings.GetColumnFor(ct), }) .Where(c => c.Column != null) .Select(i => new { i.Type, i.Column, ExistsInDataTable = columnNames.Contains(i.Column.ColumnName.ToUpper()), }).ToArray(); var badColumns = columnByTypes.Where(c => c.Column == null).Concat(i => RoyaltyRepository.Extensions.Extensions.GetEnumNameFromType(i.Type), ", "); if (!string.IsNullOrWhiteSpace(badColumns)) throw new Exception(string.Format(Resources.DataCalculator_Preview_ColumnInSettingsNotSetted, badColumns)); var columnDict = columnByTypes.ToDictionary(i => i.Type, i => new { i.Column.ColumnName, i.ExistsInDataTable }); #endregion #region Prepare data logSession.Add("Parse incoming data"); var excludes = Account.Dictionary.Excludes .Select(e => e.Exclude) .ToArray(); var subRes0 = dataTable.Rows .Cast<DataRow>() .AsParallel() .Select(dr => new { Row = dr, IncomingAddressValue = columnDict[ColumnTypes.Address].ExistsInDataTable ? dr[columnDict[ColumnTypes.Address].ColumnName] : null, IncomingHostValue = columnDict[ColumnTypes.Host].ExistsInDataTable ? dr[columnDict[ColumnTypes.Host].ColumnName] : null, IncomingPhoneValue = columnDict[ColumnTypes.Phone].ExistsInDataTable ? dr[columnDict[ColumnTypes.Phone].ColumnName] : null, IncomingCityValue = columnDict[ColumnTypes.City].ExistsInDataTable ? dr[columnDict[ColumnTypes.City].ColumnName] : null, IncomingAreaValue = columnDict[ColumnTypes.Area].ExistsInDataTable ? dr[columnDict[ColumnTypes.Area].ColumnName] : null, IncomingMarkValue = columnDict[ColumnTypes.Mark].ExistsInDataTable ? dr[columnDict[ColumnTypes.Mark].ColumnName] : null, }) .Select(dr => new { dr.Row, IncomingAddress = (dr.IncomingAddressValue == DBNull.Value || dr.IncomingAddressValue == null) ? string.Empty : dr.IncomingAddressValue.ToString(), IncomingHost = (dr.IncomingHostValue == DBNull.Value || dr.IncomingHostValue == null) ? string.Empty : dr.IncomingHostValue.ToString(), IncomingPhone = (dr.IncomingPhoneValue == DBNull.Value || dr.IncomingPhoneValue == null) ? string.Empty : dr.IncomingPhoneValue.ToString(), IncomingCity = (dr.IncomingCityValue == DBNull.Value || dr.IncomingCityValue == null) ? string.Empty : dr.IncomingCityValue.ToString(), IncomingArea = (dr.IncomingAreaValue == DBNull.Value || dr.IncomingAreaValue == null) ? string.Empty : dr.IncomingAreaValue.ToString(), IncomingMark = (dr.IncomingMarkValue == DBNull.Value || dr.IncomingMarkValue == null) ? string.Empty : dr.IncomingMarkValue.ToString(), }) .Select(dr => new { dr.Row, IncomingAddress = Parser.Address.FromString(dr.IncomingAddress, dr.IncomingArea, excludes), IncomingHost = Parser.Host.FromString(dr.IncomingHost), IncomingPhone = Parser.Phone.FromString(dr.IncomingPhone), dr.IncomingCity, dr.IncomingMark, }) .ToArray(); ppPrepare.Value = 100; #endregion #region Join hostes or create new logSession.Add("Join hostes or create new"); var hosts = subRes0 .AsParallel() .Select(i => i.IncomingHost.Hostname) .Distinct() .LeftOuterJoin(Repository.HostGet(), h => h.ToUpper(), h => h.Name.ToUpper(), (h, host) => new { HostName = h, Host = host }) .ToArray() .Select(i => i.Host ?? Repository.HostNew(i.HostName)) .ToArray(); ppHostes.Value = 50; var subRes1 = subRes0 .Join(hosts, i => i.IncomingHost.Hostname, h => h.Name, (i, h) => new { i.Row, i.IncomingAddress, i.IncomingCity, i.IncomingPhone, i.IncomingHost, i.IncomingMark, Host = h, }); ppHostes.Value = 100; #endregion #region Join phones or create new logSession.Add("Join phones or create new"); var phones = subRes0 .AsParallel() .Select(i => i.IncomingPhone.PhoneNumber) .Distinct() .LeftOuterJoin(Repository.PhoneGet(), p => p, p => p.PhoneNumber, (p, phone) => new { PhoneNumber = p, Phone = phone }) .ToArray() .Select(i => i.Phone ?? Repository.PhoneNew(i.PhoneNumber, Account)) .ToArray(); ppPhones.Value = 50; var subRes2 = subRes1 .Join(phones, i => i.IncomingPhone.PhoneNumber, p => p.PhoneNumber, (i, p) => new { i.Row, i.IncomingPhone, i.IncomingAddress, i.IncomingCity, i.IncomingHost, i.IncomingMark, i.Host, Phone = p, }); ppPhones.Value = 100; #endregion #region Join marks logSession.Add("Join marks"); var defMark = Repository.MarkGet(MarkTypes.Unknown); #pragma warning disable 618 var subRes3 = subRes2 .LeftOuterJoin(Repository.MarkGet(), i => i.IncomingMark.ToUpper(), m => m.SystemName.ToUpper(), (i, m) => new { i.Row, i.IncomingPhone, i.IncomingAddress, i.IncomingCity, i.IncomingHost, i.IncomingMark, i.Host, i.Phone, Mark = m ?? defMark, }); #pragma warning restore 618 ppMarks.Value = 100; #endregion #region Join cities or create new logSession.Add("Join cities or create new"); var cities = subRes0 .AsParallel() .Select(i => i.IncomingCity) .Distinct() .LeftOuterJoin(Repository.CityGet(), c => c.ToUpper(), c => c.Name.ToUpper(), (c, city) => new { CityName = c, City = city }) .ToArray() .Select(i => i.City ?? Repository.CityNew(i.CityName)) .ToArray(); ppCities.Value = 50; var subRes4 = subRes3 .Join(cities, i => i.IncomingCity.ToUpper(), c => c.Name.ToUpper(), (i, c) => new { i.Row, i.IncomingPhone, i.IncomingAddress, i.IncomingCity, i.IncomingHost, i.IncomingMark, i.Host, i.Phone, i.Mark, City = c }); ppCities.Value = 100; #endregion #region Join streets logSession.Add("Join streets"); var aP = new Parser.AddressParser(Account, Repository); var aPR = aP.Parse(subRes4 .GroupBy(i => new { i.City, i.IncomingAddress.Street, House = i.IncomingAddress.House.ToString(), i.IncomingAddress.Area }) .Select(g => new { City = g.Key.City, Address = new Parser.Address(g.Key.Street, g.Key.House, g.Key.Area) }) .Select(g => new Parser.AddressParserIncomingParameter() { City = g.City, Address = g.Address }) .ToArray(), !UseDictionary, (progress) => ppStreets.Value = progress, (str) => logSession.Add(str)); var subRes5 = subRes4 .LeftOuterJoin(aPR, r => new { r.IncomingAddress.Street, House = r.IncomingAddress.House.ToString(), r.IncomingAddress.Area, r.City.Name }, i => new { i.Key.Address.Street, House = i.Key.Address.House.ToString(), i.Key.Address.Area, i.Key.City.Name }, (r, i) => new { r.Row, LoadedRow = new { i.Value.Address, //change old address to new address i.Value.Street, r.Mark, r.Phone, r.City, r.Host, }, }); #endregion #region Update addresses logSession.Add("Update house numbers"); var expectedData = Account.Data .Where(d => !string.IsNullOrWhiteSpace(d.HouseNumber)) .GroupBy(d => new { d.Phone, d.Street }) .Select(d => new { d.Key.Phone, d.Key.Street, Houses = d.Select(i => i.HouseNumber), Cnt = d.Count() }) .Where(i => i.Cnt == 1) .Select(i => new { i.Phone, i.Street, HouseNumber = i.Houses.FirstOrDefault() }); var currentData = subRes5 .Where(d => !string.IsNullOrWhiteSpace(d.LoadedRow.Address.House.ToString())) .GroupBy(d => new { d.LoadedRow.Phone, d.LoadedRow.Street }) .Select(d => new { d.Key.Phone, d.Key.Street, Houses = d.Select(i => i.LoadedRow.Address.House.Number), Cnt = d.Count() }) .Where(i => i.Cnt == 1) .Select(i => new { i.Phone, i.Street, HouseNumber = i.Houses.FirstOrDefault() }); ppAddresses.Value = 50; var subRes6 = subRes5 .LeftOuterJoin(currentData, r => new { r.LoadedRow.Phone.PhoneNumber, r.LoadedRow.Street }, d => new { d?.Phone?.PhoneNumber, d?.Street }, (r, d) => new { Data = r, Grouped = d }) .LeftOuterJoin(expectedData, r => new { r.Data.LoadedRow.Phone.PhoneNumber, r.Data.LoadedRow.Street }, d => new { d?.Phone?.PhoneNumber, d?.Street }, (r, d) => new { r.Data.Row, LoadedRow = new { //Address = new Parser.Address(r.Data.LoadedRow.Address.Street, (r.Data.LoadedRow.Address.House.Number?.ToString() ?? r.Grouped?.HouseNumber?.ToString() ?? d?.HouseNumber ?? string.Empty), r.Data.LoadedRow.Address.Area), HouseNumber = (r.Data.LoadedRow.Address.House.Number?.ToString() ?? r.Grouped?.HouseNumber?.ToString() ?? d?.HouseNumber ?? string.Empty), r.Data.LoadedRow.Street, r.Data.LoadedRow.Mark, r.Data.LoadedRow.Phone, r.Data.LoadedRow.City, r.Data.LoadedRow.Host, } }); ppAddresses.Value = 100; #endregion #region Load rows logSession.Add("Load rows"); var ppRowsPrepare = ppRows.GetChild(); var ppRowsLoad = ppRows.GetChild(); var rowCount0 = (decimal)subRes6.Count() - 1; var currentIndex0 = 0m; var res = subRes6.Select(r => new { r.Row, DataRecord = Repository.AccountDataRecordNew(Account, r.LoadedRow), Index = (ppRowsPrepare.Value = (currentIndex0++) / rowCount0 * 100m) }) .Select(r => new { r.Row, LoadedRow = new DataPreviewRow() { DataRecord = r.DataRecord, DataRecordAdditional = Repository.AccountDataRecordAdditionalNew(r.DataRecord), }, } ) .ToArray(); var rowCount1 = (decimal)dataTable.Rows.Count - 1; var currentIndex1 = 0m; var columns = Account.AdditionalColumns.Where(c => !string.IsNullOrWhiteSpace(c.ColumnName)); res.ToList() .ForEach(r => { foreach (var c in columns) { var pi = r.LoadedRow.DataRecordAdditional.GetType().GetProperty(c.ColumnSystemName); pi.SetValue(r.LoadedRow.DataRecordAdditional, r.Row[c.ColumnName]); } ppRowsLoad.Value = currentIndex1++ / rowCount1 * 100m; }); #endregion return res.ToDictionary(i => i.Row, i => i.LoadedRow); } catch (Exception ex) { logSession.Add(ex); logSession.Enabled = true; throw; } }
/// <summary> /// Обновление меток в данных /// </summary> /// <param name="phonesToUpdate">Телефоны, которые нужно проверить для обновления меток</param> /// <param name="progressAction">Действие для отображения прогресса</param> /// <param name="logAction">Действие для отображения лога</param> private void UpdateMarks(IEnumerable<Phone> phonesToUpdate, Action<decimal> progressAction = null, Action<string> logAction = null) { progressAction = progressAction ?? new Action<decimal>((i) => { }); logAction = logAction ?? new Action<string>((i) => { }); using (var logSession = Log.Session($"{this.GetType().Name}.{nameof(UpdateMarks)}()", VerboseLog)) try { logSession.Output = (strs) => strs.ToList().ForEach(s => logAction(s)); if (phonesToUpdate == null) throw new ArgumentNullException(nameof(phonesToUpdate)); var progress = new Helpers.PercentageProgress(); var quickMarksProgress = progress.GetChild(); var similarityPhonesProgress = progress.GetChild(); progress.Change += (s, e) => progressAction(e.Value); var phones = phonesToUpdate.Select(p => new { Phone = p, Progress = similarityPhonesProgress.GetChild() }).ToList(); logSession.Add($"update marks for ({phones.Count}) phones start..."); var now = DateTime.UtcNow; var startFindDate = now - (Account.Settings.TimeForTrust ?? new TimeSpan()); var numberSeries = Account.SeriesOfNumbers.Where(s => s.DigitCount > 0).ToList(); #region Get default marks var phoneMarksDictionary = phonesToUpdate .Join(Account.Data, p => p, d => d.Phone, (p, d) => d) .GroupBy(i => i.Phone) .Select(g => new { Phone = g.Key, CountWithoutConstraint = g.Count(), CountWithConstraint = g.Count(d => d.Created <= startFindDate) }) .Select(i => { var markType = MarkTypes.Default; if (i.CountWithoutConstraint >= 3) { markType = MarkTypes.NotTrusted; } else { if (i.CountWithConstraint > 0) { if (i.CountWithoutConstraint == 1) { markType = MarkTypes.Trusted; } else { if (i.CountWithoutConstraint == 2) markType = MarkTypes.HalfTrusted; } } else { if (i.CountWithoutConstraint == 2) markType = MarkTypes.Suspicious; } } return new { i.Phone, markType }; }) .ToDictionary(i => i.Phone, i => i.markType); quickMarksProgress.Value = 100; #endregion #if DEBUG phones.ForEach(phoneItem => #else Parallel.ForEach(phones, phoneItem => #endif { var phone = phoneItem.Phone; var currentMarkType = phoneMarksDictionary[phone]; logSession.Add($"mark selected for phone '{phone}' is '{currentMarkType}'"); #region Serial numbers if (currentMarkType == MarkTypes.Default) { var prgPrepare = phoneItem.Progress.GetChild(); var validNumberSeries = numberSeries .Where(ns => phone.PhoneNumber.Length >= ns.DigitCount) .Select(ns => new { NumberSeries = ns, Progress = phoneItem.Progress.GetChild() }) .ToList(); prgPrepare.Value = 100; validNumberSeries.ForEach(ns => { logSession.Add($"similarity action start for phone '{phone}'..."); var startFindSimilarityDate = now - ns.NumberSeries.Delay; string phoneSimilarity = phone.PhoneNumber.Substring(0, phone.PhoneNumber.Length - (int)ns.NumberSeries.DigitCount); var similaryDataQuery = Account.Data .Where(r => r.Phone.PhoneNumber.StartsWith(phoneSimilarity)); if (ns.NumberSeries.Delay.Ticks > 0) similaryDataQuery = similaryDataQuery.Where(r => r.Created >= startFindSimilarityDate); var similarytyPhones = similaryDataQuery.Select(sd => sd.Phone).Distinct().Except(new Phone[] { phone }).ToList(); if (similarytyPhones.Count > 0) { currentMarkType = MarkTypes.Suspicious; lock (phoneMarksDictionary) { phoneMarksDictionary[phone] = currentMarkType; similarytyPhones.ForEach(sp => { if (phoneMarksDictionary.ContainsKey(sp)) phoneMarksDictionary[sp] = currentMarkType; else phoneMarksDictionary.Add(sp, currentMarkType); }); } } ns.Progress.Value = 100; }); } else { phoneItem.Progress.Value = 100; } #endregion logSession.Add($"mark detection done for phone '{phone}'. Finally mark is '{currentMarkType}'."); }); var allMarks = Repository.MarkGet().ToArray(); var phoneMarksToUpdate = Account.PhoneMarks .Join(phoneMarksDictionary, pm => pm.Phone, md => md.Key, (pm, md) => new { Item = pm, NewMarkType = md.Value }) .Join(allMarks, i => i.NewMarkType, m => m.Type, (i, m) => new { i.Item, NewMark = m }) .ToList(); phoneMarksToUpdate.ForEach(i => i.Item.Mark = i.NewMark); } catch (Exception ex) { logSession.Add(ex); logSession.Enabled = true; throw; } }
/// <summary> /// Вставить записи в основную таблицу данных аккаунта /// </summary> /// <param name="previewRows">Rows to insert</param> /// <param name="importFile">Import queue file</param> /// <param name="progressAction">Действие для отображения прогресса</param> /// <param name="logAction">Действие для отображения лога</param> /// <returns>Возвращает обработанные данные, которые можно экспортировать</returns> public IEnumerable<AccountDataRecord> Import(IEnumerable<DataPreviewRow> previewRows, ImportQueueRecordFileInfo importFile, Action<decimal> progressAction = null, Action<string> logAction = null) { progressAction = progressAction ?? new Action<decimal>((i) => { }); logAction = logAction ?? new Action<string>((i) => { }); using (var logSession = Log.Session($"{this.GetType().Name}.{nameof(Import)}()", VerboseLog)) try { logSession.Output = (strs) => strs.ToList().ForEach(s => logAction(s)); if (previewRows == null) throw new ArgumentNullException(nameof(previewRows)); var progress = new Helpers.PercentageProgress(); var prgPreparation = progress.GetChild(weight: 0.3m); var prgUpdateMarks = progress.GetChild(weight: 0.6m); var prgPrepareExport = progress.GetChild(weight: 0.1m); progress.Change += (s, e) => progressAction(e.Value); logSession.Add($"grouping {previewRows.Count()} rows..."); var dataToImport = previewRows .GroupBy(i => new { i.DataRecord.Phone, i.DataRecord.Street, i.DataRecord.HouseNumber }) .Select(i => i.LastOrDefault()) .LeftOuterJoin(Account.Data, g => new { g.DataRecord.Phone, g.DataRecord.Street, g.DataRecord.HouseNumber }, d => new { d.Phone, d.Street, d.HouseNumber }, (g, d) => new { Existed = d, Insert = g }) .ToList(); if (importFile != null) dataToImport.ForEach(r => Repository.ImportQueueRecordFileAccountDataRecordNew(importFile, r.Existed ?? r.Insert.DataRecord)); prgPreparation.Value = 50; logSession.Add($"grouping rows done. Insert it in account data."); //Repository.AccountDataRecordAdd(dataToImport.Where(i => i.Existed == null).Select(i => i.Insert.DataRecord), Account, false); dataToImport.ForEach(i => { if (i.Existed == null) { Repository.AccountDataRecordAdd(i.Insert.DataRecord, saveAfterInsert: false); } else { i.Insert.DataRecord.CopyObject(i.Existed, new string[] { nameof(i.Insert.DataRecord.DataAdditional), nameof(i.Insert.DataRecord.LoadedByQueueFiles), nameof(i.Insert.DataRecord.AccountDataRecordUID), nameof(i.Insert.DataRecord.Created), nameof(i.Insert.DataRecord.Exported) }); i.Existed.CopyObject(i.Insert.DataRecord, new string[] { nameof(i.Insert.DataRecord.DataAdditional), nameof(i.Insert.DataRecord.LoadedByQueueFiles), nameof(i.Insert.DataRecord.AccountDataRecordUID) }); i.Insert.DataRecordAdditional.CopyObject(i.Existed.DataAdditional, new string[] { nameof(i.Insert.DataRecordAdditional.AccountDataRecordUID) }); } }); prgPreparation.Value = 100; UpdateMarks(dataToImport.Select(i => i.Insert.DataRecord.Phone).Distinct(), (i) => prgUpdateMarks.Value = i, s => logSession.Add($"update marks: {s}")); var exportStart = DateTime.UtcNow - (Account.Settings.IgnoreExportTime ?? new TimeSpan()); logSession.Add($"prepare results to return records older then '{exportStart}'"); var rowsToExport = dataToImport .Where(r => (r.Insert.DataRecord.Exported ?? DateTime.MinValue) <= exportStart) .Select(r => r.Insert.DataRecord); prgPrepareExport.Value = 100; return rowsToExport; } catch (Exception ex) { logSession.Add(ex); logSession.Enabled = true; throw; } }
public void DataCalculator_Preview() { SqlLogEnabled = false; var rnd = new Random(); rnd.Next(); var alph = "йцукенгшщзфывапролдячсмить"; var maxCnt = 100; var cityNames = Enumerable.Range(0, 3).Select(i => $"TestCity{i}").ToArray(); var areaNames = Enumerable.Range(0, 10).Select(i => $"TestArea{i}").ToArray(); var streetNames = Enumerable.Range(0, 100).Select(i => Enumerable.Range(0, 6).Select(n => alph[rnd.Next(alph.Length)].ToString()).Concat(c => c)).ToArray(); var hostNames = new string[] { "testhost0.ru", "testhost1.ru" }; try { hostNames.ToList().ForEach((h) => { Rep.HostRemove(Rep.HostGet(h), saveAfterRemove: false); var host = Rep.HostNew(h); Rep.HostAdd(host, saveAfterInsert: false); }); Rep.SaveChanges(); var acc = Rep.GetAccount(defAccountName, eagerLoad: new string[] { "Settings.Columns.ColumnType" }); cityNames.ToList().ForEach((s) => { Rep.CityRemove(Rep.CityGet(s), saveAfterRemove: false); }); Rep.SaveChanges(); cityNames.ToList().ForEach((s) => { var city = Rep.CityNew(s); areaNames.ToList().ForEach((a) => { var area = Rep.AreaNew(a, city: city); streetNames.ToList().ForEach((ss) => { if (rnd.Next(5) != 0) Rep.StreetNew(RoyaltyDataCalculator.Parser.Address.FromString(ss, area.Name, acc.Dictionary.Excludes.Select(e => e.Exclude)).Street, area); }); }); }); Rep.SaveChanges(); var addrCol = acc.Settings.GetColumnFor(RoyaltyRepository.Models.ColumnTypes.Address); var areaCol = acc.Settings.GetColumnFor(RoyaltyRepository.Models.ColumnTypes.Area); var cityCol = acc.Settings.GetColumnFor(RoyaltyRepository.Models.ColumnTypes.City); var cityHost = acc.Settings.GetColumnFor(RoyaltyRepository.Models.ColumnTypes.Host); var cityPhone = acc.Settings.GetColumnFor(RoyaltyRepository.Models.ColumnTypes.Phone); var csvLines = new List<string>(); var colValues = acc.Settings.Columns .Where(c => c.ColumnType.ImportTableValidation) .Select(c => new { Name = c.ColumnName.ToLower(), Type = c.ColumnType }) .ToArray(); var columns = string.Empty; foreach (var colName in colValues) columns += (string.IsNullOrWhiteSpace(columns) ? string.Empty : ";") + colName.Name; csvLines.Add(columns); var lastAddedPhone = string.Empty; var genNewData = new Func<RoyaltyRepository.Models.ColumnTypes, string>( (ct) => { var res = string.Empty; switch(ct) { case RoyaltyRepository.Models.ColumnTypes.Phone: if (!string.IsNullOrWhiteSpace(lastAddedPhone) && rnd.Next(0, 5) == 0) res = lastAddedPhone; else for (int n = 0; n < 5; n++) res += rnd.Next(10, 99).ToString(); break; case RoyaltyRepository.Models.ColumnTypes.Address: res = streetNames.Union(new string[] { "defaultPreviewStreet2" }).ToArray()[rnd.Next(0, streetNames.Length + 1)] + ((rnd.Next(0, 5) != 0) ? ", " + rnd.Next(1,99) : string.Empty); break; case RoyaltyRepository.Models.ColumnTypes.Area: res = areaNames.Union(new string[] { string.Empty, "defaultPreviewArea2" }).ToArray()[rnd.Next(0, areaNames.Length + 2)]; break; case RoyaltyRepository.Models.ColumnTypes.City: res = cityNames.Union(new string[] { "defaultPreviewCity1" }).ToArray()[rnd.Next(0, cityNames.Length + 1)]; break; case RoyaltyRepository.Models.ColumnTypes.Host: res = (rnd.Next(3) == 0 ? string.Empty : @"http://") + hostNames.Union(new string[] { "testhost2.ru" }).ToArray()[rnd.Next(0, hostNames.Length + 1)] + "/" + Guid.NewGuid().ToString("N"); break; } return res; } ); for (int i = 0; i < maxCnt; i++ ) { columns = string.Empty; foreach (var colName in colValues) columns += (string.IsNullOrWhiteSpace(columns) ? string.Empty : ";") + genNewData(colName.Type.Type); csvLines.Add(columns); } Rep.SaveChanges(); using (var dc = new DataCalculator(acc, Rep)) { var prg0 = new Helpers.PercentageProgress(); var prg1 = prg0.GetChild(); var prg2 = prg0.GetChild(); var prg3 = prg0.GetChild(); prg0.Change += (s, e) => Helpers.Log.Add($"Progress: {e.Value.ToString("N2")}"); var log = new Action<string>((s) => Helpers.Log.Add(s)); var l = Helpers.CSV.CSVFile.Load(csvLines.AsEnumerable(), tableValidator: dc.TableValidator, rowFilter: dc.RowFilter); dc.Prepare(l.Table, i => prg1.Value = i, log); var previewRes = dc.Preview(l.Table, i => prg2.Value = i, log); Assert.AreEqual(l.Table.Rows.Count, previewRes.Count()); var readyToExport = dc.Import(previewRes.Values, null, i => prg3.Value = i, log); Rep.SaveChanges(); Helpers.Log.Add($"Data imported and ready to export"); Rep.AccountDataRecordRemove(acc.Data); } } finally { hostNames.ToList().ForEach((s) => { Rep.HostRemove(Rep.HostGet(s), saveAfterRemove: false); }); cityNames.ToList().ForEach((s) => { Rep.CityRemove(Rep.CityGet(s), saveAfterRemove: false); }); Rep.SaveChanges(); SqlLogEnabled = true; } }