仅在Excel中读取具有特定范围值的单元格 - 使用VSTO C＃ [英] Only read cells with values with in a specific range in Excel - using VSTO C#

问题描述

 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.

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);

然后我制作了一个Word表来写入数据。

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

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.

推荐答案

你的代码看起来还不错。



string str =（string）（MyRange.Cells [rCnt，cCnt] as Excel.Range）.Value2.ToString（）;

异常可能在上面一行。



不要将案例直接输入字符串。

而是使用 null coalesce operator （??），使用 Convert.ToString（）或直接检查是否为空。

原因是如果你试图投了一个null将被抛出一个exceltion将被抛出。



这是你可以做的一个例子 -

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.

这篇关于仅在Excel中读取具有特定范围值的单元格 - 使用VSTO C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！