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()));
}