1 Replies - 690 Views - Last Post: 06 February 2010 - 01:55 AM Rate Topic: -----

#1 mikecole79  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 05-February 10

Automating Excel through C#

Posted 05 February 2010 - 11:51 AM

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

Is This A Good Question/Topic? 0
  • +

Replies To: Automating Excel through C#

#2 LetMeFinclOut  Icon User is offline

  • D.I.C Head

Reputation: 11
  • View blog
  • Posts: 115
  • Joined: 14-May 09

Re: Automating Excel through C#

Posted 06 February 2010 - 01:55 AM

If memory serves correctly, the command would be:
<excelWorsheet>.Cells[<row>, <col>].Value


First, you'll need to get the <excelWorksheet> object that you'll be reading from. For example...
Excel._Workbook excelWorkbook = ecxelApp.Workbooks.Open(<filepath>)
Excel._Worksheet excekWorksheet = excelWorkbook.Sheets[<sheetID>]

...where <sheetID> is either the sheet index (starting at 1) or the sheet name.

Also note that the <row> and <col> values for Cells[,] also start at 1.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1