Only read cells with values with in a specific range in Excel - using VSTO C#

Question

4.00/5 (1 vote)

See more:

, +

SQL

 Hell all, I am doing an ExcelAddin in VisualStudio 2010 for Excel 2007. In my Excel Workbook I have a named range that I call MyRange. It goes from cells C10 to M25.The other cells contain data that I am not interestet in. How can I read the data from only the cells that have value in them within MyRange? I do not want to read the empty cells. I then want to take these values and copy them into a table in Word.

I have tried to use UsedRange but that reads everything from A1-M25 ( I only want to read the cells with value from C10-M25). Here is what I got so far.

C#

string FileName = @"C:\MyFile.xlsx";
Excel.Application xlApp = xlApp = new Excel.Application();
Excel.Workbook xlWorkBook = null;
Excel.Worksheet xlWorkSheet = null;

xlWorkBook = xlApp.Workbooks.Open(FileName);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

object cell1 = "C10", cell2 ="M25";
//Here are some different versions that I tried. I also tried to use the SpecialCell method //but it doesn´t seem to work. Maybe I don´t know how to?
Excel.Range namedRange = (Excel.Range)xlWorkSheet.get_Range("C10", "M25");
Excel.Range last = (Excel.Range)xlWorkSheet.UsedRange;
Excel.Range usedRange = (Excel.Range)xlWorkSheet.get_Range("C10", last);

Then I have made a Word table to write the data into.

C#

table = doc.Tables.Add(wrdRng, MyRange.Rows.Count,MyRange.Columns.Count, ref oMissing, ref oMissing);

         for (int rCnt = 1; rCnt <= MyRange.Rows.Count; rCnt++)
            {
               for (int cCnt = 1; cCnt <= MyRange.Columns.Count; cCnt++)
                   {

                      string str = (string)(MyRange.Cells[rCnt, cCnt] as Excel.Range).Value2.ToString();
                      table.Cell(rCnt, cCnt).Range.Text = str;

            // I am not sure about this last bit but it works when I have limited the Range to only the active cells
            //but as soon as it finds a cell with null it throws an exception

                    }
            }

All help is highly appreciated, thank you all.

Posted 12-Jul-12 6:22am

SteiniEy

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Answer 1 · 2012-07-12T06:29:00

Your code looks quite ok.

string str = (string)(MyRange.Cells[rCnt, cCnt] as Excel.Range).Value2.ToString();
The exception is probably in the above line.

Dont type case directly into string.
Instead either use the null coalesce operator (??), use Convert.ToString() or directly check for null.
The reason is if you try and cast a null into string an exceltion will be thrown.

Here is an example of what you could do -

string str = = (MyRange.Cells[rCnt, cCnt] as Excel.Range).Value2 ?? string.empty);<br />
<br />
You could also try<br />
string str = (MyRange.Cells[rCnt, cCnt] as Excel.Range).Value2 != string.empty ? MyRange.Cells[rCnt, cCnt] as Excel.Range).Value2.toString() : string.empty;<br />
<br />
Give one of these a try and check if you still get this error.