Click here to Skip to main content
15,880,725 members
Articles / Programming Languages / C#

Faster MS Excel Reading using Office Interop Assemblies

Rate me:
Please Sign up or sign in to vote.
4.75/5 (43 votes)
31 Mar 20054 min read 351.8K   6K   127  
An article on reading Excel documents using C# and the MS Office 2003 Interop Assemblies.
/*
 * Created by Dusty Candland.
 * Date: 3/2/2005 8:27 PM
 * 
 */
using System;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;

namespace FasterExcelAccess
{
	class MainClass
	{
		public static void Main(string[] args)
		{
			#region Uncomment the code in this region to run the TimedAccess class
			// TimedAccess timedAccess = new TimedAccess();
			// timedAccess.Read();
			// return;
			#endregion
			
			ApplicationClass app = new ApplicationClass();
			Workbook book = null;
			Worksheet sheet = null;
			Range range = null;
			
			try {
				app.Visible = false;
				app.ScreenUpdating = false;
				app.DisplayAlerts = false;
				
				string execPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);
				
				book = app.Workbooks.Open(execPath + @"\..\..\Book1.xls", Missing.Value, Missing.Value, Missing.Value
				                                  , Missing.Value, Missing.Value, Missing.Value, Missing.Value
				                                 , Missing.Value, Missing.Value, Missing.Value, Missing.Value
				                                , Missing.Value, Missing.Value, Missing.Value);
				sheet = (Worksheet)book.Worksheets[1];
				
				// get a range to work with
				range = sheet.get_Range("A1", Missing.Value);
				// get the end of values to the right (will stop at the first empty cell)
				range = range.get_End(XlDirection.xlToRight);
				// get the end of values toward the bottom, looking in the last column (will stop at first empty cell)
				range = range.get_End(XlDirection.xlDown);
				
				// get the address of the bottom, right cell
				string downAddress = range.get_Address(
				    false, false, XlReferenceStyle.xlA1, 
				    Type.Missing, Type.Missing);
			
				// Get the range, then values from a1
				range = sheet.get_Range("A1", downAddress);
				object[,] values = (object[,])range.Value2;
				
				// Value2 is a two dimenial array dime one = row, dime two = column.
				Console.WriteLine("Col Count: " + values.GetLength(1).ToString());
				Console.WriteLine("Row Count: " + values.GetLength(0).ToString());
				
				// View the values
				Console.Write("\t");
				for (int j = 1; j <= values.GetLength(1); j++) {
					Console.Write("{0}\t", j);
				}
				Console.WriteLine();
				for (int i = 1; i <= values.GetLength(0); i++) {
					Console.Write("{0}\t", i);
					for (int j = 1; j <= values.GetLength(1); j++) {
						Console.Write("{0}\t", values[i, j]);
					}
					Console.WriteLine();
				}
				
			}
			catch (Exception e) {
				Console.WriteLine(e);
			}
			finally {
				range = null;
				sheet = null;
				if (book != null)
					book.Close(false, Missing.Value, Missing.Value);
				book = null;
				if (app != null)
					app.Quit();
				app = null;
			}
			
		}
	}
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions