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

ConsKa

Well-known member
Joined
Dec 11, 2020
Messages
92
Programming Experience
Beginner
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:

C#:
            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:

C#:
            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

Staff member
Joined
Apr 6, 2019
Messages
2,583
Location
Chesapeake, VA
Programming Experience
10+
Perhaps I am missing something, but why is this question posted under the WPF subforum?
 

ConsKa

Well-known member
Joined
Dec 11, 2020
Messages
92
Programming Experience
Beginner
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

C#:
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...

C#:
_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.
 
Last edited:

Sheepings

Retired Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,971
Location
UK
Programming Experience
10+
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.
 
Top Bottom