|
/*
* Created by Dusty Candland
* Date: 3/27/2005 3:36 PM
*
*/
using System;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;
namespace FasterExcelAccess
{
/// <summary>
/// Description of TimedAccess.
/// </summary>
public class TimedAccess
{
public TimedAccess() {}
public void Read() {
ApplicationClass app = new ApplicationClass();
Workbook book = null;
Worksheet sheet = null;
Range range = null;
try {
DateTime startTime = DateTime.Now;
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];
Console.WriteLine("Open document Seconds: {0}", DateTime.Now.Subtract(startTime).TotalSeconds);
startTime = DateTime.Now;
// 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);
Console.WriteLine("Get Data Range Seconds: {0}", DateTime.Now.Subtract(startTime).TotalSeconds);
ReadRange(range);
ReadCells(range);
}
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;
}
}
private void ReadRange(Range range) {
DateTime startTime = DateTime.Now;
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();
}
Console.WriteLine("Range Read Seconds: {0}", DateTime.Now.Subtract(startTime).TotalSeconds);
}
private void ReadCells(Range range) {
DateTime startTime = DateTime.Now;
// View the values
Console.Write("\t");
for (int j = 1; j <= range.Columns.Count; j++) {
Console.Write("{0}\t", j);
}
Console.WriteLine();
for (int i = 1; i <= range.Rows.Count; i++) {
Console.Write("{0}\t", i);
for (int j = 1; j <= range.Columns.Count; j++) {
Console.Write("{0}\t", ((Range)range.Cells[i, j]).Value2);
}
Console.WriteLine();
}
Console.WriteLine("Cells Read Seconds: {0}", DateTime.Now.Subtract(startTime).TotalSeconds);
}
}
}
|
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.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.