Excel Interop - Please Help - Thanks

Question

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

Vulpes · Answer

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

Sam Hobbs · Answer

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

Anthony Clarke · Answer

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

Anthony Clarke · Answer

Perfect Vulpes, thanks alot :) Yeah your right, i used for (int sheetNum = 1; sheetNum Works Perfectly :) Didnt think about testing to see whether it was the object that was NULL lol

Anthony Clarke · Answer

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?

Sam Hobbs · Answer

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