Resolved This has me a little confused...Columns in Excel.

[ConsKa]

So I made a simple program, and what it does is read through ExcelOne and collects two pieces of data (Col A and Col B), then compares Col A from ExcelOne to a column in ExcelTwo and where there is a match adds Col A and B from ExcelOne to ExcelTwo - then saves ExcelTwo as a new spreadsheet.

Simple enough.

What has me confused is this...since i am adding the new pieces of data, I insert a new Column A and Column B in ExcelTwo This is because I want to visually check that Column A from ExcelOne matches - it is a QC process.

Here is the code:

            sheet.Columns["A"].Insert();
            sheet.Columns["B"].Insert();
            sheet.Cells[1, 1] = "File Name";
            sheet.Cells[1, 2] = "Data";

So far so simple. Here is my foreach:

            foreach (var data in dict.Keys)
            {
                for (int k = 1; k <= rowCount; k++)
                {
                    if (xlRange.Cells[k, "A"].Value.ToString() == data)
                    {
                        sheet.Cells[k, "B"] = data;
                        sheet.Cells[k, "A"] = dict[data];
                    }
                }
            }

Notice something? I am searching Column A, for a match, so that I can write data to Column A.....

This stumped me for about an hour, because I was searching across...Column C....which is where I expected the data to now be, after adding a new Column A and Column B.

My only thought is that because it isn't saved, it is creating a new temporary Excel, which isn't the original excel, and inserting the new columns into that new temp excel, and then writing the data into that new excel, while still reading the original Excel, in its original format.

If you happen to know whether that is right or wrong, or know the right answer, would love to understand what is happening here.

 

[Skydiver]

Perhaps I am missing something, but why is this question posted under the WPF subforum?

 

[ConsKa]

I created it in WPF. I suppose it is more general then specific to.

 

[JohnH]

Thread moved to Third Party forum.

xlRange holds the data that was assigned to it, it is not a dynamic lookup to an address range in the sheet.

 

[ConsKa]

It is odd, because I am trying to port it to .NET Core and some parts didn't seem to work and needed a little rejigging.

For instance

sheet.Columns["A"].Insert();

Just does not work.

Now, when I do essentially the same code (below) it is adding two new columns to the original and the saveas file...

_Excel.Range newRng2 = sheet.get_Range("A1:B1", Type.Missing);

newRng2.EntireColumn.Insert(XlInsertShiftDirection.XlShiftToRight, XlInsertFormatOrigin.XlFormatFromRightOrBelow);

So for one version it doesn't impact the original, and creates it in a temp file but in this version now it does impact the original?

It is a little confusing.

Just discovered that the excel when moved to a shared location, initiates autosave function....which means it autosaves as you complete work on it. So that clears that up.

 

[NoUserHere]

Please mark your topic as resolved so we don't have to read it all to determine you found the answer. I've done it for you in this case.