Automating Excel through C#

[mikecole79]

I have been having nothing but trouble trying to get C# to read excel. I haven't even gotten to the writing part, but I imagine it's going to be a challenge, too. FYI, I'm pretty new at this, and don't have a ton of experience, so I'm sure I'm missing something that's obvious to someone who's been around a bit.

Basically, what I'm trying to do is write a simple program that will read information from 2 different workbooks, reading each of 4 columns from one book into a different list<string>, and reading the 2 columns from the other book into a dictionary<string, string>. Then I plan to spit all that information back out again, but it's going to be seperated into multiple sheets in 3 different books in a different order so that it fits everyones needs. Currently, the company I work for uses spreadsheets for just about everything, and when you're ordering a series of 1500 new phone numbers, and it's all on one spreadsheet, but needs to be seperated out as described above to go to different vendors, it can take all freakin day to do one. I have been using a Marcro recorder to do it since my boss assigned it to me, but that doesn't make me happy, and will still take 2 hours to do. Hence I'm trying to write this program.

Below is a code example for what I have so far for part of a read method.

do
            {
                try
                {
                    excelApp.ActiveCell.Text.ToString();
                    string Temp = excelApp.ActiveCell.Text.ToString();
                    LATA.Add(Temp);
                    excelApp.ActiveCell.FindNext(Temp);
                    rowIndex++;
                }

                catch (Exception e)
                {
                    MessageBox.Show("Exception " + e.Message + " Stack Trace: " + e.StackTrace);
                }

            }
            while (excelApp.Cells[rowIndex, colIndex] != null);

This particular method is supposed to circle through the information in the workbook, and if it is in a specific column (this is for column A), it reads the contents of the cell into LATA (which is list<string>). It doesn't work, and I have tried many, many different variations to try to get it. The closest I've come is with the line:

excelApp.ActiveCell.Text.ToString();

which will correctly read the information, but I can't seem to figure out the right syntax to get it to move to the next cell in the column, or even just the next cell period.

Any ideas or hints on what I can do to try to get this thing working? Any ideas at all, even if you think they're stupid or won't work, would be great. I've been arguing with this program for days, and have tried several different things, and so far the built in interop on VS2008 is the best solution I have (without spending several hundred dollars).

Thanks!

Mike

[apegram]

In VBA, there is/was an ActiveCell.Offset method, but it was more complicated than that. It would be ActiveCell.Offset(rowcount, columncount).Range("A1").Select. The "A1" was sort of like a grounding value, giving it the proper frame of reference. That may or may not be proper description for it, but I knew whenever I wanted to move to a different cell, the Range() value would always be A1 if I wanted it to work properly. I don't know if the same method is available in C#, however. I know you can read Excel using OleDb, though, if you can't get your present approach to work.

[mikecole79]

Thank you. I'm willing to be there's something similar in C#. I'm going to take a look right now. At least it's a different avenue to explore.

I know that OleDB can access things, but reading through that information, it looked more convoluted than this process should need to be. The biggest problems I've had is getting things to work right with the unmanaged code and the wrappers that VS provides. Sometimes it's just not the most stable thing in the world.

Thanks for the ideas. I'll put them to good use.

Mike

[maryjok3698]

Hello,

I meet same problem before, I use a c# excel component to solve the problem easily, It's name Spire.XLS.
OleDB can access things, but I wish you to find right way.

Hope help to you.

[jackflint]

I am using Zet Excel platform
Try it
It helped me a lot !