Iterating through workbook capturing text

[Matt Giedt]

Hello all.

I'm trying to write a simple C# interop class that, when given a valid
path to an Excel file, returns the textual contents of that file.

(Assume that the info parameter refers to a valid XLS file.)

public string parse( FileInfo info )
{
StringBuilder sb = new StringBuilder();
Excel.Application app = new Excel.Application();
app.Visible = false;

Excel.Workbook wb = app.Workbooks.Open(@info.FullName,
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 );

wb.Activate();

//
// Attempt # 1:
//

try
{
Excel.Range range = app.get_Range( "Sheets", Type.Missing );
sb.Append( range.Text );
}
catch( Exception e )
{
Console.WriteLine( "Error parsing excel document." );
Console.WriteLine( e.Message );
Console.WriteLine( e.StackTrace );
}

//
// Prints the exception:
//
// Error parsing excel document.
// Exception from HRESULT: 0x800A03EC.
// at Excel.ApplicationClass.get_Range(Object Cell1, Object Cell2)
//

//
// Attempt # 2:
//

try
{
foreach( Excel._Worksheet sheet in wb.Worksheets )
{
foreach( Excel.Range cell in sheet.Cells )
{
sb.Append( cell.Text );
}
}
}
catch( Exception e )
{
Console.WriteLine( "Error parsing excel document." );
Console.WriteLine( e.Message );
Console.WriteLine( e.StackTrace );
}

//
// Prints the exception:
//
// Error parsing excel document.
// Member not found.
// at System.RuntimeType.ForwardCallToInvokeMember
// (String memberName, BindingFlags flags,
// Object target, Int32[] aWrapperTypes,
// MessageData& msgData)
// at Excel.Range.GetEnumerator()
//

wb.Close(false, Type.Missing, Type.Missing);
app.Quit();
return sb.ToString();
}

Any help would be greatly appreciated.
TIA,
-Matt

 

[Jeff Webb]

Why don't you just use interop to save the file as an XML Spreadsheet, then use an XSLT transform

Otherwise, you probably want to replace sheet.Cells with sheet.UsedRange, and I think cell.Text should be cell.Value in your second attempt

-- Jeff

 

[onedaywhen]

The Excel.Range object's Text property is read-only. try using the
Value (actually, with c# Value2 is more usable) or Formula properties
instead.