2

So im using a for loop to loop through all rows in an excel spreadsheet and i can display the current row number very easily by just using the "i" definition, however it prints on multiple lines since each iteraton displays with a Console.WriteLine() command.

What i would like is for it to only show it once, and have it display an updated iteration on one single line. Here is my current code:

void DeleteCells(string filePath)
    {

        int currRowNumber = 0;


        // create excel-instance:
        Application excel = new Application();
        // open the concrete file:
        Workbook excelWorkbook = excel.Workbooks.Open(@filePath);
        // select worksheet. NOT zero-based!!:
        _Worksheet excelWorkbookWorksheet = excelWorkbook.ActiveSheet;

        if(isclosing)
        {
            closeProgram(excel, excelWorkbook);
        }

        int numRows = excelWorkbookWorksheet.UsedRange.Rows.Count;
        Console.WriteLine("Number of rows: " + numRows);
        Console.Write("Checking Row #: " + currRowNumber);


        int numRowsDeleted = 0;
        int nullCounter = 0;
        //for (int j = 1; j <=)

        for (int i = 1; i < numRows + 4; i++)
        {
            //We want to skip every row that is null and continue looping until we have more than 3 rows in a row that are null, then break
            if (i > 1)
            {
                i -= 1;
            }

            //Create Worksheet Range
            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)excelWorkbookWorksheet.Cells[i, 2];
            string cellValue = Convert.ToString(range.Value);

            if (nullCounter == 5)
            {
                Console.WriteLine("Null row detected...breaking");
                Console.WriteLine("Number of rows deleted: " + numRowsDeleted);
                break;
            }

            if (cellValue != null)
            {
                if (cellValue.Contains("MESSAGE NOT CONFIGURED"))
                {
                    //Console.WriteLine("Deleting Row: " + Convert.ToString(cellValue));
                    ((Range)excelWorkbookWorksheet.Rows[i]).Delete(XlDeleteShiftDirection.xlShiftUp);
                    numRowsDeleted++;
                    //Console.WriteLine("Number of rows deleted: " + numRowsDeleted);
                    nullCounter = 0;
                    i--;
                    currRowNumber++;
                }
                else
                {
                    currRowNumber++;
                    nullCounter = 0;
                }
            }
            else
            {
                nullCounter++;
                //Console.WriteLine("NullCounter: " + nullCounter);
            }
            i++;

        }

        Console.WriteLine("Fixes Finished! Please check your excel file for correctness");

        closeProgram(excel, excelWorkbook);
    }

Sample output:

Row Number: 1
Row Number: 2
Row Number: 3
Row Number: 4
Row Number: 5

etc..

I want it to display only one line and continuously update the row number. How would i go about doing this? Any help is appreciated. Thanks.

UPDATE: So i have the following loop:

for (int i = 1; i < numRows + 2; i++) //numRows was +4, now +2
        {

            Console.Clear();

            Console.WriteLine("Number of rows: " + numRows);
            Console.Write("Checking Row #: " + currRowNumber);

            //We want to skip every row that is null and continue looping until we have more than 3 rows in a row that are null, then break
            if (i > 1)
            {
                i -= 1;
            }

            //Create Worksheet Range
            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)excelWorkbookWorksheet.Cells[i, 2];
            string cellValue = Convert.ToString(range.Value);

            if (nullCounter == 3) //was 5
            {
                Console.WriteLine("\nNull row detected...breaking");
                Console.WriteLine("Number of rows deleted: " + numRowsDeleted);
                break;
            }

            if (cellValue != null)
            {
                if (cellValue.Contains(searchText))
                {
                    //Console.WriteLine("Deleting Row: " + Convert.ToString(cellValue));
                    ((Range)excelWorkbookWorksheet.Rows[i]).Delete(XlDeleteShiftDirection.xlShiftUp);
                    numRowsDeleted++;
                    //Console.WriteLine("Number of rows deleted: " + numRowsDeleted);
                    nullCounter = 0;
                    i--;
                    currRowNumber++;
                    rowsPerSecond = i;
                }
                else
                {
                    currRowNumber++;
                    nullCounter = 0;
                }
            }
            else
            {
                nullCounter++;
                //Console.WriteLine("NullCounter: " + nullCounter);
            }
            i++;

        }

I want to calculate how many rows im looping through per second, then calculate from that number how long it will take to complete the entire loop, based on how many rows there are.

Again, any help is appreciated, thanks!

Ignition K6
  • 147
  • 1
  • 1
  • 14

6 Answers6

0

On each loop circle use the Clear() method before print output:

Console.Clear();
itikhomi
  • 1,560
  • 13
  • 13
0

You could do this:

const int ConsoleWidth = 80;
for(int i = 0; i < 10; i++)
{
    // pull cursor to the start of the line, delete it with spaces
    // then pull it back again
    for(int j = 0; j < ConsoleWidth; j++) Console.Write("\b");
    for(int j = 0; j < ConsoleWidth; j++) Console.Write(" ");
    for(int j = 0; j < ConsoleWidth; j++) Console.Write("\b");

    Console.Write("Row Number: {0}", i);

}
Surak of Vulcan
  • 348
  • 2
  • 11
  • got the first part of the problem working, now i need to calculate how long it will take to finish the loop. I updated the problem. – Ignition K6 Jun 30 '17 at 13:05
0

If you do

Console.Write("Row Number: {0}", i);

It will keep appending in front of previous text. If you do

Console.Clear();

Any message that you intend to write before this text will dissapear. If you know the exact position of the text, you can try to modify the text in console at that position as:

// First time write
Console.WriteLine("Row Number: {0}", i);

// x,y coordinates in the method for later iterations
Console.SetCursorPosition(11, 0);
Console.Write(i);
praty
  • 535
  • 2
  • 9
  • got the first part of the problem working, now i need to calculate how long it will take to finish the loop. I updated the problem. – Ignition K6 Jun 30 '17 at 13:05
0

If the amount of text in the row will never decrease (e.g. because the number only ever gets bigger) you can do this by using Console.Write() and prefixing the text with \r:

for (int i = 0; i < 100000; ++i)
    Console.Write($"\rRow Number: {i}");

Console.WriteLine();

If the text decreases in length because the number gets smaller, you can use a formatting string to output the number left-justified with extra spaces which will overwrite the extra digits:

for (int i = 100000; i >= 0; --i)
    Console.Write($"\rRow Number: {i, -6}");

Console.WriteLine();

Doing it this way has the advantage that you keep any previous text in the console and only update the single line. This also makes it a lot quicker compared to clearing the entire display every iteration.

Matthew Watson
  • 104,400
  • 10
  • 158
  • 276
  • got the first part of the problem working, now i need to calculate how long it will take to finish the loop. I updated the problem. – Ignition K6 Jun 30 '17 at 13:05
0

You can do as the previous answers suggested, here is another way.

 int line = Console.CursorTop;
 int len = new String("Row Number: ").length;
 Console.WriteLine("Row Number: ");
 for(your loop)
 {
  Console.WriteLine(i);
  //Print whatever else you want!
  Console.SetCursorPosition(len,line);
 }

Advantage of this approach is that the position is remembered and you can come back to the same position after displaying any other information to the user, This is useful when updating a single character in a screen full of text!

To answer the next question, use

Stopwatch s = new Stopwatch();
long millis =0,avg;
for(int i=0;i<loopLength;i++)
{
 s.Start();
 //Do work
 s.Stop();
 millis += s.ElapsedMilliseconds;
avg = millis/(i+1);
 Console.WriteLine("Time remaining :" + (TimeSpan.FromMilliseconds(avg*(loopLength - (i+1))).Seconds);
}
Suraj S
  • 1,019
  • 7
  • 18
0

try to use Console.SetCursorPosition before Console.Write

Remay
  • 69
  • 4