Reading Excel Files

I’ve run into the problem a few times of needing to convert rows in an Excel file into an object for some kind of processing. I found an example that was pretty trim in what it was doing so I ended up using it as a model for a new template class.

The basic idea is that you create a new class and provide the overrides for converting a row into an object and after the main method returns you end up with a Collection where T is the type of Data Transfer Object (DTO) that your application can use.

One more modification that I’m planning on adding is to stream the DTOs to a method instead of returning a collection. Calling a processor will allow for a large number of rows to be processed without storing the DTOs in a collection and thus not incurring the memory overhead.

Here is the code so far: [more]

1:using System;
2:using System.Collections.ObjectModel;
3:using System.Data.OleDb;
4:using System.IO;
5:
6:namespace SomeNameSpace
7: {
8:publicclass DigestExcel
9: {
10:///
11:/// True if exceptions should be thrown during processing
12:///
13:publicbool ThrowExceptions { get; set; }
14:
15:///
16:/// Output StringToWrite to a log
17:///
18:/// A string to be written to a log
19:protectedvirtualvoid WriteLogFileEntry(string StringToWrite)
20: {
21:// This method should be implemented if logging is desired
22: }
23:
24:///
25:/// Convert a row to a DTO
26:///
27:/// The DataReader to build DTOs from
28:/// A new DTO object
29:protectedvirtual T ParseDataRow(OleDbDataReader DataReader)
30: {
31:// This method should be implemented
32:thrownew NotImplementedException(“You must implement ParseDataRow”);
33: }
34:
35:///
36:/// Reads each row of an Excel file sheet and returns a collection of DTOs
37:///
38:/// The fully qualified path to the exxcell file
39:/// The sheet/tab name to be processed
40:/// A collection of T objects
41:public Collection ProcessExcelFile(string FileName, string SheetName)
42: {
43: Collection objectList = new Collection();
44:
45: OleDbConnection conn = new OleDbConnection();
46:try
47: {
48: WriteLogFileEntry(String.Format(“Opening File: {0}”, FileName));
49:
50:// Make sure the file exists first
51:if( File.Exists( FileName ) == false )
52: {
53:thrownew FileNotFoundException(String.Format(” Unable to locate requested file, aborting: {0}”, FileName));
54: }
55:
56: conn = new OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + FileName + “;Extended Properties=\”Excel 8.0;\””);
57: conn.Open();
58:
59: OleDbCommand cmd = new OleDbCommand(String.Format(“SELECT * FROM [{0}$]”, SheetName), conn);
60:
61: OleDbDataReader dataReader = cmd.ExecuteReader();
62:
63:int rowCounter = 0;
64:while (dataReader.Read())
65: {
66:// Advance the row number for logging
67: ++rowCounter;
68:
69: WriteLogFileEntry(String.Format(” Processing data row {0}”, rowCounter));
70:
71:try
72: {
73:// Add the next row to the collection
74: objectList.Add(ParseDataRow(dataReader));
75: }
76:catch(Exception up)
77: {
78:string exceptionMessage = String.Format(” Exception processing row {0}: {1}”, rowCounter, up.Message);
79:
80:// If we throw exceptions up then the outer catch will describe the exception so we wont log it here
81:if( ThrowExceptions )
82: {
83:thrownew ApplicationException(exceptionMessage, up);
84: }
85:
86:// If there is an exception then log it
87: WriteLogFileEntry(exceptionMessage);
88: }
89: }
90: }
91:catch(Exception up)
92: {
93:string exceptionMessage = String.Format(” Exception processing file {0}: {1}”, FileName, up.Message);
94:
95:// If there is an exception then log it
96: WriteLogFileEntry(exceptionMessage);
97:
98:if (ThrowExceptions)
99: {
100:thrownew ApplicationException(exceptionMessage, up);
101: }
102: }
103:finally
104: {
105: conn.Close();
106: WriteLogFileEntry(” Processing file completed”);
107: }
108:
109:return objectList;
110: }
111: }
112: }
In order to use the template you can define a new class and inherit from the DigestExcel class like this:
1:publicclass DigestSample : DigestExcel
2: {
3:protectedoverridevoid WriteLogFileEntry(string StringToWrite)
4: {
5: Console.WriteLine(StringToWrite);
6: }
7:
8:protectedoverride SampleDTO ParseDataRow(System.Data.OleDb.OleDbDataReader DataReader)
9: {
10: SampleDTO newRow = new SampleDTO();
11:
12: newRow.Value1 = DataReader[0].ToString().Trim();
13: newRow.Value2 = DataReader[1].ToString().Trim();
14:
15:return newRow;
16: }
17: }

Advertisements