Sorting list of points based on a min value criteria

HappyCode HappyCode

I hope you can help me out on this one. I have to do some "special sorting" of a list of points (x,y,z coordinates) as a part of a larger code.

This list (by definition) will:

i) Always have a column with all the values equal zero.

ii) The first and the last point will always be the same.

The sorting of the list will depend on which column is equal to zero. I can identify this column myself with no problem (see code) but I am struggling when it comes with the sorting bit. First I have to find a the point that meet some specific criteria, then reorganise the list based on this. I have explained the sorting criteria as comments in the code (see sections 2 and 3).

If you could provide assistance on 2) and 3) that would be great (the first part is ok I think).

Many thanks

The code:

using System;

using System.Collections.Generic;

using System.Linq;

class Program

{

[STAThread]

public static void Main(string[] args)

{

//Populating the list

var ListPoints = new List();

double[] P0 = { 10, 10, 0 };

double[] P1 = { 10, 0, 0 };

double[] P2 = { 0, 0, 0 };

double[] P3 = { 0, 10, 0 };

ListPoints.Add(P0);

ListPoints.Add(P1);

ListPoints.Add(P2);

ListPoints.Add(P3);

ListPoints.Add(P0);

///This list (by definition) will:

/// i) Always have a column with all the values equal zero

/// ii) The first and the last point will always be the same.

///We need to detect the column with all values = zero, because the list sorting will depend on this.

/// 1) Detect which columns has all values equal to zero using the columnZero variable

var counterX = new List();

var counterY = new List();

var counterZ = new List();

for (int i = 0; i < ListPoints.Count - 1; i++)

{

//First column with all values equal zero

if (ListPoints[i][0] == 0 && ListPoints[i][0] == ListPoints[i + 1][0]) { counterX.Add(1); }

//Second column with all values equal zero

if (ListPoints[i][1] == 0 && ListPoints[i][1] == ListPoints[i + 1][1]) { counterY.Add(1); }

//Third column with all values equal zero

if (ListPoints[i][2] == 0 && ListPoints[i][2] == ListPoints[i + 1][2]) { counterZ.Add(1); }

}

if (counterX.Count == ListPoints.Count - 1)

{ Console.WriteLine("all points of the 1st column are zero");}

if (counterY.Count == ListPoints.Count - 1)

{ Console.WriteLine("all points of the 2nd column are zero");}

if (counterZ.Count == ListPoints.Count - 1)

{ Console.WriteLine("all points of the 3rd column are zero");}

/// 2) Now a point "Q" must be found in the list according to this:

/// 2.1/ If the first column has all values = zero:

/// Find the row index of the smallest value in the second column.

/// If there are several rows in the second column with the same minimum value go and find between those which one has the smallest value in the third column.

/// If there is only one value in the second column keep that one.

/// 2.2/ If the second column has all values = zero:

/// Find the row index of the smallest value in the first column.

/// If there are several rows in the first column with the same minimum value go and find between those which one has the smallest value in the third column.

/// If there is only one value in the first column keep that one.

/// 2.3/ If the third column has all values = zero:

/// Find the row index of the smallest value in the first column.

/// If there are several rows in the first column with the same minimum value go and find between those which one has the smallest value in the second column.

/// If there is only one value in the first column keep that one.

///

/// 3) Once this value has been found we have to put the list starting by this point "Q", then copy the previous values at the end of the list and finally add again "Q".

/// Example:The column with all values = 0 is column 3 and the generic point "Q" is the point "P2" in my list. The input is P0-P1-P2-P3-P0 but I want to have it P2-P3-P0-P1-P2.

}

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes11y ago
If that bothers you, I'd just make Excel invisible whilst you're doing the updates:

xlApp.Visible = false;
HappyCode HappyCode11y ago
Thank you very much! It works perfect! Just being a bit "picky" myself... Is there a way to avoid the fact of having excel flashing on the screen at each new update of the list? It is not very important but a bit disturbing :-). Also I am scared it could have some impact on the computation time (?). Thank you again!
Vulpes11y ago
Yes, there were some problems in that snippet when opening, saving and closing the workbook. There were also a lot of Missing.Values in there which, following the changes to interop in C# 4.0, we can thankfully get rid of.

Anyway I've sorted all this out and it seems to be working OK now.

Here's the revised code with the changes highlighted:

using System;
using System.Collections.Generic;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;

class Program
{
[STAThread]
public static void Main(string[] args)
{
//Populating the list
var ListPoints = new List();

double[] P0 = { 10, 10, 0 };
double[] P1 = { 10, 0, 0 };
double[] P2 = { 0, 0, 0 };
double[] P3 = { 0, 10, 0 };

ListPoints.Add(P0);
ListPoints.Add(P1);
ListPoints.Add(P2);
ListPoints.Add(P3);
ListPoints.Add(P0);

///This list (by definition) will:
/// i) Always have a column with all the values equal zero
/// ii) The first and the last point will always be the same.
///We need to detect the column with all values = zero, because the list sorting will depend on this.

/// 1) Detect which columns has all values equal to zero using the columnZero variable
var counterX = new List();
var counterY = new List();
var counterZ = new List();

for (int i = 0; i < ListPoints.Count - 1; i++)
{
//First column with all values equal zero
if (ListPoints[i][0] == 0 && ListPoints[i][0] == ListPoints[i + 1][0]) { counterX.Add(1); }
//Second column with all values equal zero
if (ListPoints[i][1] == 0 && ListPoints[i][1] == ListPoints[i + 1][1]) { counterY.Add(1); }
//Third column with all values equal zero
if (ListPoints[i][2] == 0 && ListPoints[i][2] == ListPoints[i + 1][2]) { counterZ.Add(1); }
}

/// 2) Now a point "Q" must be found in the list according to this:
/// 2.1/ If the first column has all values = zero:
/// Find the row index of the smallest value in the second column.
/// If there are several rows in the second column with the same minimum value go and find between those which one has the smallest value in the third column.
/// If there is only one value in the second column keep that one.
/// 2.2/ If the second column has all values = zero:
/// Find the row index of the smallest value in the first column.
/// If there are several rows in the first column with the same minimum value go and find between those which one has the smallest value in the third column.
/// If there is only one value in the first column keep that one.
/// 2.3/ If the third column has all values = zero:
/// Find the row index of the smallest value in the first column.
/// If there are several rows in the first column with the same minimum value go and find between those which one has the smallest value in the second column.
/// If there is only one value in the first column keep that one.
///

/// 3) Once this value has been found we have to put the list starting by this point "Q", then copy the previous values at the end of the list and finally add again "Q".
/// Example:The column with all values = 0 is column 3 and the generic point "Q" is the point "P2" in my list. The input is P0-P1-P2-P3-P0 but I want to have it P2-P3-P0-P1-P2.

double min = Double.MaxValue;
int minIndex = -1;

if (counterX.Count == ListPoints.Count - 1)
{
Console.WriteLine("all points of the 1st column are zero");
min = ListPoints[0][1];
minIndex = 0;

for (int i = 1; i < ListPoints.Count - 1; i++)
{
if (ListPoints[i][1] < min)
{
min = ListPoints[i][1];
minIndex = i;
}
else if (ListPoints[i][1] == min)
{
if (ListPoints[i][2] < ListPoints[minIndex][2]) minIndex = i;
}
}
}

else if (counterY.Count == ListPoints.Count - 1)
{
Console.WriteLine("all points of the 2nd column are zero");
min = ListPoints[0][0];
minIndex = 0;

for (int i = 1; i < ListPoints.Count - 1; i++)
{
if (ListPoints[i][0] < min)
{
min = ListPoints[i][0];
minIndex = i;
}
else if (ListPoints[i][0] == min)
{
if (ListPoints[i][2] < ListPoints[minIndex][2]) minIndex = i;
}
}
}

else if (counterZ.Count == ListPoints.Count - 1)
{
Console.WriteLine("all points of the 3rd column are zero");
min = ListPoints[0][0];
minIndex = 0;

for (int i = 1; i < ListPoints.Count - 1; i++)
{
if (ListPoints[i][0] < min)
{
min = ListPoints[i][0];
minIndex = i;
}
else if (ListPoints[i][0] == min)
{
if (ListPoints[i][1] < ListPoints[minIndex][1]) minIndex = i;
}
}

}

if (minIndex > 0) // no change in ordering required if minIndex == 0
{
ListPoints.RemoveAt(ListPoints.Count - 1); // get rid of last element

// rotate list 'minIndex' places to left
for (int i = 1; i <= minIndex; i++)
{
double[] first = ListPoints[0];
for (int j = 1; j < ListPoints.Count; j++) ListPoints[j - 1] = ListPoints[j];
ListPoints[ListPoints.Count - 1] = first;
}

ListPoints.Add(ListPoints[0]); // add back what is now the first row at the end
Console.WriteLine("the row index of the Q point was {0}", minIndex);
Console.WriteLine("after reordering the list of points is now\n");
}
else
{
Console.WriteLine("the row index of the Q point was 0");
Console.WriteLine("so reordering was not needed and the list of points is still\n");
}

for (int i = 0, p = minIndex; i < ListPoints.Count; i++, p++)
{
if (i == ListPoints.Count - 1)
{
p = minIndex;
}
else if (p == ListPoints.Count - 1)
{
p = 0;
}

Console.Write("P{0} = ", p);
Console.Write("{ ");
Console.Write(String.Join(", ", ListPoints[i]));
Console.WriteLine(" }");
}

SaveToExcel(ListPoints);
}

static void SaveToExcel(List ListPoints)
{
string filePath = "somefile.xlsx";
Excel.Application xlApp = new Excel.Application();
xlApp.Visible = true;
xlApp.DisplayAlerts = false;
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath);
Excel.Sheets xlWorksheets = xlWorkbook.Worksheets;
Excel.Worksheet xlSheet1 = (Excel.Worksheet)xlWorksheets.get_Item("Sheet1");
Excel.Range range = xlSheet1.UsedRange;
int rowCount = range.Rows.Count;
xlSheet1.Cells[rowCount + 2, 1] = "Start";
int start = rowCount + 4;
int i ;

for (i = 0; i < ListPoints.Count; i++)
{
for (int j = 1; j < 4; j++)
{
xlSheet1.Cells[start + i, j] = ListPoints[i][j - 1];
}
}

xlSheet1.Cells[rowCount + 5 + i, 1] = "End";
xlWorkbook.Save();
xlWorkbook.Close(true, filePath);
xlSheet1 = null;
xlWorkbook = null;
xlApp.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
}
HappyCode HappyCode11y ago
Thanks for the link Vulpes. I have tried this code before but I could not make it work myself. Not sure what I was doing wrong (or if there is an error in the code snippet). Any ideas?
Vulpes11y ago
On the face of it, I'd have thought the best approach would be to use Excel Interop to figure out how many rows are in the existing worksheet, write the new stuff to the empty rows below that and then re-save the file.

There's some code in this thread which looks like it should work though I don't have time just now to test it:

https://social.msdn.microsoft.com/Forums/vstudio/en-US/cb9e0445-fc71-4eae-8742-80b2c831ed22/how-to-append-existing-excel-file-using-c-?forum=vstest

If you can't get it to work as you want it to, I'll have another look tomorrow.
HappyCode HappyCode11y ago
Hello Vulpes,

It is very nice to hear from you.

Many thanks for this. The possibility of having more than one column=0 is not a problem because if I have two columns equal to zero (the three of them will never be equal to zero at the same time by definition) the remaining one will be organised based on min value basis (desired result)  and this is indirectly specified in the conditions you have kindly set in the code. In other words, it is working perfectly as is! :)

On the other hand, I would like to ask you about an efficient way to print out "programatically" this list "ListPoints" to an existent excel file without deleting the previous content in the excel. The reason is because this list as part of a larger code will be filled and cleared programatically and I want to keep a copy of the values of the list in an excel file before the list is cleared.
The final excel should look like this (including the words Start and End in column A):

    A      B       C

    Start                     //List1 is copied below

    15     18   0

    20     40   50

    12     15   16

    End                       //List1 is cleared

    Start

    7      90   16            //New List1 is copied below

    20     15   50

    12     15   8

    End                       //List1 is cleared
...

Do you have any idea?

Thank you for your kind help again!

PS: Regarding the triangle problem... I have decided to move on and put this problem aside for the moment (I will have to be back solving this).
Vulpes11y ago
Hey, haven't spoken in a while!

Did you ever figure out the problem with the triangles? I seem to recall that there was a problem with everything we tried :(

Anyway this problem looks more tractable. Here's my attempt at 2) & 3) :

using System;
using System.Collections.Generic;
using System.Linq;

class Program
{

[STAThread]

public static void Main(string[] args)
{
//Populating the list
var ListPoints = new List();

double[] P0 = { 10, 10, 0 };
double[] P1 = { 10, 0, 0 };
double[] P2 = { 0, 0, 0 };
double[] P3 = { 0, 10, 0 };

ListPoints.Add(P0);
ListPoints.Add(P1);
ListPoints.Add(P2);
ListPoints.Add(P3);
ListPoints.Add(P0);

///This list (by definition) will:
/// i) Always have a column with all the values equal zero
/// ii) The first and the last point will always be the same.
///We need to detect the column with all values = zero, because the list sorting will depend on this.

/// 1) Detect which columns has all values equal to zero using the columnZero variable
var counterX = new List();
var counterY = new List();
var counterZ = new List();

for (int i = 0; i < ListPoints.Count - 1; i++)
{
//First column with all values equal zero
if (ListPoints[i][0] == 0 && ListPoints[i][0] == ListPoints[i + 1][0]) { counterX.Add(1); }
//Second column with all values equal zero
if (ListPoints[i][1] == 0 && ListPoints[i][1] == ListPoints[i + 1][1]) { counterY.Add(1); }
//Third column with all values equal zero
if (ListPoints[i][2] == 0 && ListPoints[i][2] == ListPoints[i + 1][2]) { counterZ.Add(1); }
}

/// 2) Now a point "Q" must be found in the list according to this:
/// 2.1/ If the first column has all values = zero:
/// Find the row index of the smallest value in the second column.
/// If there are several rows in the second column with the same minimum value go and find between those which one has the smallest value in the third column.
/// If there is only one value in the second column keep that one.
/// 2.2/ If the second column has all values = zero:
/// Find the row index of the smallest value in the first column.
/// If there are several rows in the first column with the same minimum value go and find between those which one has the smallest value in the third column.
/// If there is only one value in the first column keep that one.
/// 2.3/ If the third column has all values = zero:
/// Find the row index of the smallest value in the first column.
/// If there are several rows in the first column with the same minimum value go and find between those which one has the smallest value in the second column.
/// If there is only one value in the first column keep that one.
///

/// 3) Once this value has been found we have to put the list starting by this point "Q", then copy the previous values at the end of the list and finally add again "Q".
/// Example:The column with all values = 0 is column 3 and the generic point "Q" is the point "P2" in my list. The input is P0-P1-P2-P3-P0 but I want to have it P2-P3-P0-P1-P2.

double min = Double.MaxValue;
int minIndex = -1;

if (counterX.Count == ListPoints.Count - 1)
{
Console.WriteLine("all points of the 1st column are zero");
min = ListPoints[0][1];
minIndex = 0;

for (int i = 1; i < ListPoints.Count - 1; i++)
{
if (ListPoints[i][1] < min)
{
min = ListPoints[i][1];
minIndex = i;
}
else if(ListPoints[i][1] == min)
{
if(ListPoints[i][2] < ListPoints[minIndex][2]) minIndex = i;
}
}
}

else if (counterY.Count == ListPoints.Count - 1)
{
Console.WriteLine("all points of the 2nd column are zero");
min = ListPoints[0][0];
minIndex = 0;

for (int i = 1; i < ListPoints.Count - 1; i++)
{
if (ListPoints[i][0] < min)
{
min = ListPoints[i][0];
minIndex = i;
}
else if(ListPoints[i][0] == min)
{
if(ListPoints[i][2] < ListPoints[minIndex][2]) minIndex = i;
}
}
}

else if (counterZ.Count == ListPoints.Count - 1)
{
Console.WriteLine("all points of the 3rd column are zero");
min = ListPoints[0][0];
minIndex = 0;

for (int i = 1; i < ListPoints.Count - 1; i++)
{
if (ListPoints[i][0] < min)
{
min = ListPoints[i][0];
minIndex = i;
}
else if(ListPoints[i][0] == min)
{
if(ListPoints[i][1] < ListPoints[minIndex][1]) minIndex = i;
}
}

}

if (minIndex > 0) // no change in ordering required if minIndex == 0
{
ListPoints.RemoveAt(ListPoints.Count - 1); // get rid of last element

// rotate list 'minIndex' places to left
for(int i = 1; i <= minIndex; i++)
{
double[] first = ListPoints[0];
for(int j = 1; j < ListPoints.Count; j++) ListPoints[j - 1] = ListPoints[j];
ListPoints[ListPoints.Count - 1] = first;
}

ListPoints.Add(ListPoints[0]); // add back what is now the first row at the end
Console.WriteLine("the row index of the Q point was {0}", minIndex);
Console.WriteLine("after reordering the list of points is now\n");
}
else
{
Console.WriteLine("the row index of the Q point was 0");
Console.WriteLine("so reordering was not needed and the list of points is still\n");
}

for(int i = 0, p = minIndex; i < ListPoints.Count; i++, p++)
{
if (i == ListPoints.Count - 1)
{
p = minIndex;
}
else if (p == ListPoints.Count - 1)
{
p = 0;
}

Console.Write("P{0} = ", p);
Console.Write("{ ");
Console.Write(String.Join(", ", ListPoints[i]));
Console.WriteLine(" }");
}

}
}

The output is:

all points of the 3rd column are zero
the row index of the Q point was 2
after reordering the list of points is now

P2 = { 0, 0, 0 }
P3 = { 0, 10, 0 }
P0 = { 10, 10, 0 }
P1 = { 10, 0, 0 }
P2 = { 0, 0, 0 }

Incidentally, I've assumed that there will be just one column whose values are all zero as I'm not sure what should happen if there's more than one.