Excel Interop - Please Help - Thanks

Hi,

Im trying to iterate through worksheets from a work book and basically place each of the cell values into a list box. Here is the code i have so far but i either get null errors or Unable to cast object of type 'System.String' to type 'System.Object[,]'.

using

namespace

{

System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Alias = Microsoft.Office.Interop.Excel;
using System.Collections;EXpublic partial class Form1 : Form

{

{

InitializeComponent();

}

{

Alias.Scansheets(workbook);

public Form1()private void simpleButton1_Click(object sender, EventArgs e)Application app = new Alias.Application();Workbook workbook = app.Workbooks.Open("C:\\Book2.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

private void Scansheets(Workbook wookBookIn)
int numsheets = wookBookIn.Sheets.Count;
for (int sheetNum = 1; sheetNum < (numsheets); sheetNum++)
Worksheet sheet = (Worksheet)wookBookIn.Sheets[sheetNum];
Range excelRange = sheet.UsedRange;
object[,] valArray = (object[,])excelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);
foreach (object value in valArray)
if (value != null)
else
{listBox1.items.add(value);

Hope you can  help. it's driving me crazy

Thanks
Anthony

Based on the code you posted, the program ignores any values that are not null and only adds the values that are null.

Yes i dont get the null errors anymore but i get Unable to cast object of type 'System.String' to type 'System.Object[,]'. at the end of sheet 1

Any Ideas?

My suspicion is that at least one sheet is empty and so the excelRange.get_Value is returning 'Empty' (which may get translated in .NET to string.Empty) rather than a two dimensional object array.

Also, as the Sheets collection is one-based, you should be iterating up to and including 'numSheets'.

So, I'd try:

private void Scansheets(Workbook wookBookIn)

{

   int numsheets = wookBookIn.Sheets.Count;

   for (int sheetNum = 1; sheetNum <= numsheets; sheetNum++)

   {

      Worksheet sheet = (Worksheet)wookBookIn.Sheets[sheetNum];

      Range excelRange = sheet.UsedRange;

      object temp = excelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);

      if (temp != null && (temp is Array))

      {

         object[,] valArray = (object[,])temp;

         foreach (object value in valArray)

         if (value != null)

         {

            listBox1.Items.Add(value);

         }

      }

   }

}  

Perfect Vulpes, thanks alot :)

Yeah your right, i used for (int sheetNum = 1; sheetNum < (numsheets + 1); sheetNum++)

Works Perfectly :) Didnt think about testing to see whether it was the object that was NULL lol

Note that I answered the question. After I answered the question, a new question was asked.

Hi Sam,

Thanks for your help but the 2nd question was part of my original question. It was only that Vulpes answer completely answered my question so i marked as correct.

But still i thank you for your response and I am grateful.

Regards
Anthony