-1

I am reading an Excel document grid (Matrix) cell by cell in order to write them in another Excel document. The reading is done starting from a specific cell at some position and stops at some other specific position for example from cell A3 to A5 for 4 rows to get a matrix of cells. Now i want to rewrite these cells in the exact order they are in except that i want to reinitialize the start to be at the origin of the destination Excel document. I am trying with the following code :

int l=1;
int m=1;
for (int i = n1; i <= n2; i++)
{
    l++;
    for (int j = n3; j <= n4; j++)
    {
        m++;
        if (xlRange.Cells[i, j] != null && ((Excel.Range)xlRange.Cells[i, j]).Value2 != null)
        {  
            l = i; // these indices should be altered in order to write at the origin
            m = j; // these indices should be altered in order to write at the origin
            // Write at the destination Excel document worksheet (xlWorksheet2)
            xlWorksheet2.Cells[l,m] = ((Excel.Range)xlRange.Cells[i, j]).Value2.ToString();       
        }
    }
}

To note that both n1 could be equal to n2 and n3 could be equal to n4. My solution does not bring the start of the reading grid to the origin when writing to the destination Excel document. My question is how to define the indices of the array in order to write starting from while keeping the order of the cells as is.

UPDATE : using @Caius Jard suggested solution i get the following results in the destination Excel document : Excel Results

John Campbell
  • 19
  • 7
  • What are you asking.. how to set a variable `m` so it's equal to 1? – Caius Jard Jul 20 '21 at 07:43
  • i was thinking of shifting the whole read grid to the origin... How to define `l` and `m` variables in function of `i` and `j` – John Campbell Jul 20 '21 at 07:44
  • I think excel's origin is at 1,1 so if your `i/j` are `3->5/4->8` (loop within loop) then you set `l/m` to `1/1` outside any loop then increment `l/m` at the same time you increment `i/j` inside the loop. I think the thing that I'm struggling with, in relation to this question, is how come you know how to read an excel file, iterate it and pull values out of it - all quite advanced, but yet you don't appear to know how to declare an integer variable and set its value – Caius Jard Jul 20 '21 at 07:48
  • Please see my code update and let me know how to increment the `l/m` no because i tried what you suggest but it wrote the cells in a diagonal way – John Campbell Jul 20 '21 at 07:51
  • Increment an integer: `m++`. Don't increment them both at the same time (increment l at the same time as i and m as the same time a j), otherwise you'll write data at 1,1 then 2,2 then 3,3 etc – Caius Jard Jul 20 '21 at 07:54
  • *using @Caius Jard suggested solution (which is same as mine)* - my suggestion is definitely not the same as yours. You also assign to l/m when you shouldn't – Caius Jard Jul 20 '21 at 08:45

1 Answers1

0

l/m as 1. Increment in step with i/j

for(int i=n1,l=1; i <= n2; i++,l++)
{
  for (int j=n3,m=1; j <= n4; j++,m++)
Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • Pls check my updated question where i try what you suggest but it is actually writing in a diagonal way into the Excel document, do you consider the case when `j` is always a single value (not a range of values) or the opposite (`i` is a single value) and the other value is a range of values – John Campbell Jul 20 '21 at 08:05
  • *i try what you suggest* - I don't see my code in your question. After you've incorporated my suggestion you should also remove the lines containing the `// these indices should be altered in order to write at the origin` comments; if you assign to `l/m` outside of what I suggested, things will go wrong – Caius Jard Jul 20 '21 at 08:46
  • Sorry but i attach the results ouput of your exact solution, and it is not what i am looking for – John Campbell Jul 20 '21 at 09:15
  • That cannot be true; my code fragment is incomplete and of itself does not store anything in a cell. All it does is correctly increment `l` and `m` through combinations 1,1.. 1,2.. 1,3.. 2,1.. 2,2.. 2,3.. 3,1.. 3,2.. 3,3 etc and it is supposed to be that you use `l` and `m`m in something like `myWorkbook.Cells[l,m] ...`. If you're finding data in places other than these, something is wrong somewhere else (like where your original code overwrites `l` with `i` and `m` with `j` before it uses `l, m`, but it's not from this code – Caius Jard Jul 20 '21 at 10:50
  • that's weird but i don't get the desired results through your code – John Campbell Jul 20 '21 at 11:05