public List <CellsRemain> Main(BackgroundWorker worker, DoWorkEventArgs e, System.Drawing.Color color, Range selRange, bool komb, ProgressBar progressBar, int maxKomb) { Worksheet actSheet = Globals.ThisAddIn.Application.ActiveSheet; Range cellSel = Globals.ThisAddIn.Application.ActiveCell; //Range fromCell; //List<Range> remainCell = new List<Range>(); List <CellsRemain> remainCell = new List <CellsRemain>(); List <Range> ranges = new List <Range>(); bool found; count = 1; if (actSheet != null && cellSel != null) { //int column = .Column; //int rowAdd; //if (actSheet.Cells[1, column].Value is string) //check header //{ // fromCell = (Range)actSheet.Cells[2, column]; // rowAdd = 1; //} //else //{ // fromCell = (Range)actSheet.Cells[1, column]; // rowAdd = 0; //} decimal sumCoverted = 0; object[,] arrVal; List <Range> selCol; if (selRange.Count > 1) { arrVal = selRange.Value; selCol = selRange.Cast <Range>().Select(r => r).ToList(); } else { var untilCell = (Range)actSheet.Cells[actSheet.UsedRange.Rows.Count, selRange.Cells.Column]; var startCell = (Range)actSheet.Cells[1, selRange.Cells.Column]; selCol = actSheet.get_Range(startCell, untilCell).Cast <Range>().Select(r => r).ToList();; arrVal = actSheet.get_Range(startCell, untilCell).Value; } if (selCol.Count > 2) { if (progressBar.InvokeRequired) { progressBar.Invoke(new System.Action(() => { progressBar.Minimum = 1; progressBar.Maximum = selCol.Count; progressBar.Value = 2; progressBar.Refresh(); } )); } else { progressBar.Minimum = 1; progressBar.Maximum = selCol.Count; progressBar.Step = 1; progressBar.Value = 2; progressBar.Refresh(); } } //object[,] arrVal; //var arrVal = selRange.Value; int index = 0; foreach (Range element in selCol) { if (worker.CancellationPending) { e.Cancel = true; } found = false; //fr.progressBar1.PerformStep(); //fr.label4.Text = count.ToString(); //fr.Refresh(); //var hid = element.EntireRow.Hidden; worker.ReportProgress(count); count++; if (element.Value is string || element.Interior.ColorIndex == 6 || element.Value == null || element.Value is DateTime || element.EntireRow.Hidden == true || element.EntireColumn.Hidden == true) //check cell value - Hidden can work with filter { continue; } else { decimal keySearch = Convert.ToDecimal(element.Value * -1); //int index = element. for (int i = index; i <= arrVal.Length - 1; i++) //foreach(Range el2 in selCol) { if (selCol[i].Value is string || selCol[i].Value == null) { continue; } else { try { sumCoverted = Convert.ToDecimal(arrVal[i, 1]); } catch (Exception) { continue; } if (sumCoverted == keySearch && selCol[i].Interior.ColorIndex != 6 && selCol[i].EntireRow.Hidden != true) { element.Interior.ColorIndex = 6; selCol[i].Interior.ColorIndex = 6; found = true; break; } } } if (!found) { CellsRemain notFound = new CellsRemain { Summa = element, }; remainCell.Add(notFound); } } index++; } } if (komb) { FindKomb(remainCell, 0m, maxKomb, worker, e, progressBar); } return(remainCell); }
public void TarkistaPari(Range selRange, bool komb, int maxKomb) { Worksheet actSheet = Globals.ThisAddIn.Application.ActiveSheet; Range cellSel = Globals.ThisAddIn.Application.ActiveCell; //Range fromCell; //List<Range> remainCell = new List<Range>(); List <CellsRemain> remainCell = new List <CellsRemain>(); List <Range> ranges = new List <Range>(); bool found; count = 1; resultQty = 0; if (actSheet != null && cellSel != null) { decimal sumCoverted = 0; object[,] arrVal; List <Range> selCol; List <decimal> listValues = new List <decimal>(); if (selRange.Count > 1) { arrVal = selRange.Value; selCol = selRange.Cast <Range>().Select(r => r).ToList(); } else { var untilCell = (Range)actSheet.Cells[actSheet.UsedRange.Rows.Count, selRange.Cells.Column]; var startCell = (Range)actSheet.Cells[1, selRange.Cells.Column]; selCol = actSheet.get_Range(startCell, untilCell).Cast <Range>().Select(r => r).ToList();; //arrVal = actSheet.get_Range(startCell, untilCell).Value.XlRangeValueDataType.IsArray; } foreach (Range item in selCol) { decimal addElement = Convert.ToDecimal(item.Value); listValues.Add(addElement); } if (selCol.Count > 2) { textBoxStatus.Invoke(new System.Action(() => textBoxStatus.Text = "Etsi pari")); } //object[,] arrVal; //var arrVal = selRange.Value; int index = 0; //foreach (Range element in selCol) for (int j = 0; j < listValues.Count; j++) //test { if (worker.CancellationPending) { workEventArgs.Cancel = true; } found = false; worker.ReportProgress(count); count++; //var col = element.Interior.Color; //var col2 = ColorTranslator.ToOle(color); //if (element.Value is string || element.Interior.Color == ColorTranslator.ToOle(color) || element.Value == null || element.Value is DateTime || element.EntireRow.Hidden == true || element.EntireColumn.Hidden == true) //check cell value - Hidden can work with filter if (selCol[j].Value is string || selCol[j].Interior.Color == ColorTranslator.ToOle(color) || selCol[j].Value == null || selCol[j].Value is DateTime || selCol[j].EntireRow.Hidden == true || selCol[j].EntireColumn.Hidden == true) //test { continue; } else { decimal keySearch = listValues[j] * -1; //Convert.ToDecimal(element.Value * -1); //int index = element. index++; for (int i = index; i <= listValues.Count - 1; i++) //selCol -> listValues //foreach(Range el2 in selCol) { if (selCol[i].Value is string || selCol[i].Value == null) { continue; } else { try { sumCoverted = listValues[i]; //Convert.ToDecimal(selCol[i].Value); } catch (Exception) { continue; } if (sumCoverted == keySearch && selCol[i].Interior.Color != ColorTranslator.ToOle(color) && selCol[i].EntireRow.Hidden != true) { selCol[j].Interior.Color = color; //element selCol[i].Interior.Color = color; resultQty += 2; found = true; break; } } } if (!found) { CellsRemain notFound = new CellsRemain { Summa = selCol[j] }; remainCell.Add(notFound); } } } } if (komb) { if (remainCell.Count > 300 && maxKomb == 10) { MessageBox.Show("Ei voi tarkista kombinaation koska se on lian paljion numeroja", "Error"); } else if (remainCell.Count > 150 && maxKomb == 15) { MessageBox.Show("Ei voi tarkista kombinaation koska se on lian paljion numeroja", "Error"); } else if (remainCell.Count > 20 && maxKomb == 20) { MessageBox.Show("Ei voi tarkista kombinaation koska se on lian paljion numeroja", "Error"); } else { textBoxStatus.Invoke(new System.Action(() => textBoxStatus.Text = "Etsi kombinaatio")); HashSet <int> result = FindKomb(remainCell, 0m, maxKomb); resultKomb = new List <CellsRemain>(); foreach (int foundIndex in result) { remainCell[foundIndex].Summa.Interior.Color = color; } resultQty += result.Count; } } textBoxStatus.Invoke(new System.Action(() => textBoxStatus.Text = "Loppunut - löytyy kpl " + resultQty.ToString())); }