Read Excel Work Sheets

Jun 18, 2009 at 5:46 PM

Good examples as I am able to read all the columns and rows in my spreadsheet. Question, is there a method to loop through and read the sheets within the spreadsheet?

 

thanks,

jay

Jun 30, 2009 at 11:30 PM
Edited Jun 30, 2009 at 11:35 PM

Hi Jay,

I falied to read values from my Excel file, could you share with me, how did you manage to read values from your Excel file?

Here is my code:

string content = // value returned from File Attachment control
bytes[] contentBytes = Convert.FromBase64String(content);
// This contentBytes not only contains the data in Excel file,
// also the file name, file size, etc.
bytes[] dataBytes = // get the bytes[] of only the data in Excel file
MemoryStream stream = new MemoryStream(dataBytes);

// The attached Excel is .xls file
IExcelDataReader reader = Factory.CreateReader(stream, ExcelFileType.Binary);
DataSet ds = reader.AsDataSet();

while(reader.Read())
{
    // ** reader.GetString(index) returns empty value;
}

DataTable dt = ds.Tables[0];
DataRowCollection rows = dt.Rows;
for(int i = 0; i < rows.Count; i++)
{
    foreach(DataColumn column in dt.Columns)
    {
        // ** rows[i][column.Ordinal] returns empty value;
    }
}

Thanks.

Jul 1, 2009 at 1:51 PM

This worked for me:

/* ------------------------------------------------------------------------------------------------------------------------------------

namespace: ExcelStreamReader

classname: ExcelStreamReaderClass

calling convention:

Delimiter selected:

ExcelStreamReaderClass ExcelReader = new ExcelStreamReaderClass("c:\\temp\\aec_100006.xls", ExcelStreamReaderClass.Delimiter.Pipe);

Delimiter default ('|'):

ExcelStreamReaderClass ExcelReader = new ExcelStreamReaderClass("c:\\temp\\aec_100006.xls");

parameters:

TabNum - spreadsheet tab to convert

TabNameList - List of the names of all tabs in the workbook.

TabCatalogList - List of the catalog of all tabs in the workbook.

--------------------------------------------------------------------------------------------------------------------------------------*/

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.IO;

using System.Data.OleDb;

using System.Data;

namespace ExcelStreamReader

{

public class ExcelStreamReaderClass : StreamReader

{

#region Public Items

public enum Delimiter { Pipe, Comma, Tab, All }

public int TabNum { get; set; }

public List<string> TabNameList { get { return _TableNameList; } }

public List<string> TabCatalogList { get { return _TableCatalogList; } }

#endregion

#region Private Data

private OleDbConnection _conn;

private DataTable _SchemaTable = new DataTable();

private List<OleDbDataReader> _DataReaderList = new List<OleDbDataReader>();

private List<string> _TableNameList = new List<string>();

private List<string> _TableCatalogList = new List<string>();

private char _Delimiter = '|';

private ProfileClass _Profile;

#endregion

public ExcelStreamReaderClass(string FilePathName) : base(FilePathName)

{

_Profile = new ProfileClass();

_Delimiter = '|';

}

public ExcelStreamReaderClass(string FilePathName, Delimiter del) : base(FilePathName)

{

_Profile = new ProfileClass();

switch (del)

{

case Delimiter.Pipe:

_Delimiter = '|';

break;

case Delimiter.Comma:

_Delimiter = ',';

break;

case Delimiter.Tab:

_Delimiter = '\t';

break;

case Delimiter.All:

break;

default:

break;

}

OpenWorkbook(FilePathName);

OpenExcelDataReader();

}

#region Excel setup routines

private static string BuildConnectionString(string FileName)

{

string ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1\"";

return (ConStr);

}

private void OpenWorkbook(string fn)

{

try

{

_conn = new OleDbConnection(BuildConnectionString(fn));

_conn.Open();

}

catch (Exception ex)

{

throw ex;

}

}

private void OpenExcelDataReader()

{

_Profile.Start("Loading Schema");

_SchemaTable = _conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

_Profile.Stop();

foreach (DataRow Row in _SchemaTable.Rows)

{

_Profile.Start("Loading table" + Row["TABLE_NAME"].ToString());

OleDbCommand cmd = new OleDbCommand("select * from [" + Row["TABLE_NAME"].ToString() + "]", _conn);

_TableNameList.Add(Row["TABLE_NAME"].ToString());

_TableCatalogList.Add(Row["TABLE_GUID"].ToString());

_DataReaderList.Add(cmd.ExecuteReader());

_Profile.Stop();

}

}

#endregion

#region Public access routines

private string s;

public IEnumerable<string> GetEnumerator

{

get

{

_Profile.Start("Reading lines");

while (_DataReaderList[TabNum].Read())

{

StringBuilder sbLine = new StringBuilder();

for (int i = 0; i < _DataReaderList[TabNum].FieldCount - 1; i++)

{

sbLine.Append(_DataReaderList[TabNum][i].ToString());

sbLine.Append(_Delimiter);

}

sbLine.Append(_DataReaderList[TabNum][_DataReaderList[TabNum].FieldCount - 1].ToString());

yield return (sbLine.ToString());

}

_Profile.Stop();

}

}

public override string ReadLine()

{

StringBuilder sbLine = new StringBuilder();

var x = _DataReaderList[TabNum].Read();

try

{

for (int i = 0; i < _DataReaderList[TabNum].FieldCount - 1; i++)

{

sbLine.Append(_DataReaderList[TabNum][i].ToString());

sbLine.Append(_Delimiter);

}

sbLine.Append(_DataReaderList[TabNum][_DataReaderList[TabNum].FieldCount - 1].ToString());

}

catch (Exception ex)

{

return (null);

}

return (sbLine.ToString());

}

public void DisplayProfile()

{

foreach (string line in _Profile.GetProfileEnumerator)

{

Console.WriteLine(line);

}

}

public override void Close()

{

_conn.Close();

base.Close();

}

#endregion

}

}

Steve Holle

Data Services Programmer

Tel: (406)255-9500

Fax: (406)255-9595

mailto:steveh@cu.net

SDG


From: fennychai [mailto:notifications@codeplex.com]
Sent: Tuesday, June 30, 2009 5:30 PM
To: Steve Holle
Subject: Re: Read Excel Work Sheets [ExcelDataReader:59975]

From: fennychai

Hi Jay,

I falied to read values from my Excel file, could you share with me, how did you manage to read values from your Excel file?

Here is my code:

string content = // value returned from File Attachment control
bytes[] contentBytes = Convert.FromBase64String(content);
// This contentBytes not only contains the data in Excel file,
// also the file name, file size, etc.
bytes[] dataBytes = // get the bytes[] of only the data in Excel file
MemoryStream stream = new MemoryStream(dataBytes);
 
// The attached Excel is .xls file
IExcelDataReader reader = Factory.CreateReader(stream, ExcelFileType.Binary);
DataSet ds = reader.AsDataSet();
 
while(reader.Read())
{
    // ** reader.GetString(index) returns empty value;
}
 
DataTable dt = ds.Tables[0];
DataRowCollection rows = dt.Rows;
for(int i = 0; i < rows.Count; i++)
{
    foreach(DataColumn column in dt.Columns)
    {
        // ** rows[i][column.GetOrdinal()] returns empty value;
    }
}

Thanks.

Read the full discussion online.

To add a post to this discussion, reply to this email (ExcelDataReader@discussions.codeplex.com)

To start a new discussion for this project, email ExcelDataReader@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com



DISCLAIMER:
This message is confidential, intended only for the named recipient(s) and may contain information that is privileged or exempt from disclosure under applicable law. If you are not the intended recipient(s), you are notified that the dissemination, distribution or copying of this message is strictly prohibited. If you receive this message in error, or are not the named recipient(s), please notify the sender at either the e-mail address or by calling the telephone number associated with this transmission. Please delete this e-mail from your computer (or discard this fax). Thank You.
Jul 1, 2009 at 2:43 PM
This is an example of what I used,



using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Text;
using Excel;
namespace ReadExcel
{
class Program
{
private static void ImportFromExcelFile(string filePath)
{
DataRow[] foundRows;
StreamWriter sw;
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

// Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = Factory.CreateReader(stream, ExcelFileType.Binary);

// Reading from a OpenXml Excel file (2007 format; *.xlsx)
//IExcelDataReader excelReader = Factory.CreateReader(stream, ExcelFileType.OpenXml);

// The result of each spreadsheet will be created in the result.Tables
//DataSet result = excelReader.AsDataSet();

// Reader methods
DataTable table = new DataTable();

while (excelReader.Read())
{
table.Columns.Add(new DataColumn());
// loop through the columns of the spreadsheet to collect the row values
for (int n = 0; n < excelReader.FieldCount; n++)
{
table.Rows.Add(excelReader.GetValue(n));
}
}

// go through the table columns
for (int k = 0; k < excelReader.FieldCount; k++)
{
// Sort by rows from left to right columns
foundRows = table.Select(null, table.TableName);

// go through the table rows
for (int i = 0; i <= foundRows.GetUpperBound(0); i++)
{
// do something
}
}

// Free resources
excelReader.Close();
}
}
}



Jay






From: [email removed]
To: [email removed]
Date: Wed, 1 Jul 2009 06:51:28 -0700
Subject: Re: Read Excel Work Sheets [ExcelDataReader:59975]

From: sholle

This worked for me:

/* ------------------------------------------------------------------------------------------------------------------------------------

namespace: ExcelStreamReader

classname: ExcelStreamReaderClass

calling convention:

Delimiter selected:

ExcelStreamReaderClass ExcelReader = new ExcelStreamReaderClass("c:\\temp\\aec_100006.xls", ExcelStreamReaderClass.Delimiter.Pipe);

Delimiter default ('|'):

ExcelStreamReaderClass ExcelReader = new ExcelStreamReaderClass("c:\\temp\\aec_100006.xls");

parameters:

TabNum - spreadsheet tab to convert

TabNameList - List of the names of all tabs in the workbook.

TabCatalogList - List of the catalog of all tabs in the workbook.

--------------------------------------------------------------------------------------------------------------------------------------*/

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.IO;

using System.Data.OleDb;

using System.Data;

namespace ExcelStreamReader

{

public class ExcelStreamReaderClass : StreamReader

{

#region Public Items

public enum Delimiter { Pipe, Comma, Tab, All }

public int TabNum { get; set; }

public List<string> TabNameList { get { return _TableNameList; } }

public List<string> TabCatalogList { get { return _TableCatalogList; } }

#endregion

#region Private Data

private OleDbConnection _conn;

private DataTable _SchemaTable = new DataTable();

private List<OleDbDataReader> _DataReaderList = new List<OleDbDataReader>();

private List<string> _TableNameList = new List<string>();

private List<string> _TableCatalogList = new List<string>();

private char _Delimiter = '|';

private ProfileClass _Profile;

#endregion

public ExcelStreamReaderClass(string FilePathName) : base(FilePathName)

{

_Profile = new ProfileClass();

_Delimiter = '|';

}

public ExcelStreamReaderClass(string FilePathName, Delimiter del) : base(FilePathName)

{

_Profile = new ProfileClass();

switch (del)

{

case Delimiter.Pipe:

_Delimiter = '|';

break;

case Delimiter.Comma:

_Delimiter = ',';

break;

case Delimiter.Tab:

_Delimiter = '\t';

break;

case Delimiter.All:

break;

default:

break;

}

OpenWorkbook(FilePathName);

OpenExcelDataReader();

}

#region Excel setup routines

private static string BuildConnectionString(string FileName)

{

string ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1\"";

return (ConStr);

}

private void OpenWorkbook(string fn)

{

try

{

_conn = new OleDbConnection(BuildConnectionString(fn));

_conn.Open();

}

catch (Exception ex)

{

throw ex;

}

}

private void OpenExcelDataReader()

{

_Profile.Start("Loading Schema");

_SchemaTable = _conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

_Profile.Stop();

foreach (DataRow Row in _SchemaTable.Rows)

{

_Profile.Start("Loading table" + Row["TABLE_NAME"].ToString());

OleDbCommand cmd = new OleDbCommand("select * from [" + Row["TABLE_NAME"].ToString() + "]", _conn);

_TableNameList.Add(Row["TABLE_NAME"].ToString());

_TableCatalogList.Add(Row["TABLE_GUID"].ToString());

_DataReaderList.Add(cmd.ExecuteReader());

_Profile.Stop();

}

}

#endregion

#region Public access routines

private string s;

public IEnumerable<string> GetEnumerator

{

get

{

_Profile.Start("Reading lines");

while (_DataReaderList[TabNum].Read())

{

StringBuilder sbLine = new StringBuilder();

for (int i = 0; i < _DataReaderList[TabNum].FieldCount - 1; i++)

{

sbLine.Append(_DataReaderList[TabNum][i].ToString());

sbLine.Append(_Delimiter);

}

sbLine.Append(_DataReaderList[TabNum][_DataReaderList[TabNum].FieldCount - 1].ToString());

yield return (sbLine.ToString());

}

_Profile.Stop();

}

}

public override string ReadLine()

{

StringBuilder sbLine = new StringBuilder();

var x = _DataReaderList[TabNum].Read();

try

{

for (int i = 0; i < _DataReaderList[TabNum].FieldCount - 1; i++)

{

sbLine.Append(_DataReaderList[TabNum][i].ToString());

sbLine.Append(_Delimiter);

}

sbLine.Append(_DataReaderList[TabNum][_DataReaderList[TabNum].FieldCount - 1].ToString());

}

catch (Exception ex)

{

return (null);

}

return (sbLine.ToString());

}

public void DisplayProfile()

{

foreach (string line in _Profile.GetProfileEnumerator)

{

Console.WriteLine(line);

}

}

public override void Close()

{

_conn.Close();

base.Close();

}

#endregion

}

}



Steve Holle
Data Services Programmer

Tel: (406)255-9500
Fax: (406)255-9595
mailto:steveh@cu.net
SDG

From: fennychai [mailto:notifications@codeplex.com]
Sent: Tuesday, June 30, 2009 5:30 PM
To: Steve Holle
Subject: Re: Read Excel Work Sheets [ExcelDataReader:59975]


From: fennychai
Hi Jay,
I falied to read values from my Excel file, could you share with me, how did you manage to read values from your Excel file?
Here is my code:
string content = // value returned from File Attachment control
bytes[] contentBytes = Convert.FromBase64String(content);
// This contentBytes not only contains the data in Excel file,
// also the file name, file size, etc.
bytes[] dataBytes = // get the bytes[] of only the data in Excel file
MemoryStream stream = new MemoryStream(dataBytes);
 
// The attached Excel is .xls file
IExcelDataReader reader = Factory.CreateReader(stream, ExcelFileType.Binary);
DataSet ds = reader.AsDataSet();
 
while(reader.Read())
{
    // ** reader.GetString(index) returns empty value;
}
 
DataTable dt = ds.Tables[0];
DataRowCollection rows = dt.Rows;
for(int i = 0; i < rows.Count; i++)
{
    foreach(DataColumn column in dt.Columns)
    {
        // ** rows[i][column.GetOrdinal()] returns empty value;
    }
}
Thanks.
Read the full discussion online.
To add a post to this discussion, reply to this email (ExcelDataReader@discussions.codeplex.com)
To start a new discussion for this project, email ExcelDataReader@discussions.codeplex.com
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com


DISCLAIMER:
This message is confidential, intended only for the named recipient(s) and may contain information that is privileged or exempt from disclosure under applicable law. If you are not the intended recipient(s), you are notified that the dissemination, distribution or copying of this message is strictly prohibited. If you receive this message in error, or are not the named recipient(s), please notify the sender at either the e-mail address or by calling the telephone number associated with this transmission. Please delete this e-mail from your computer (or discard this fax). Thank You.


Hotmail® has ever-growing storage! Don’t worry about storage limits. Check it out.
Jul 2, 2009 at 7:48 AM

Thanks Jay :-)

Basically, there's nothing wrong in both of our code, just I have chinese font-family in my cell's value. I have chanage all values to Arial and now it's working fine. But I have a column contains date value, and seems this excel reader doesn't understand date format data.

Any idea? :-)

Jul 2, 2009 at 2:45 PM
By the way, here's how to read multiple spreadsheets:

IExcelDataReader reader = Factory.CreateReader(fs, ExcelFileType.Binary);
DataSet ds = reader.AsDataSet();

foreach (DataTable dt in ds.Tables)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
// do something
}
}



Jay



Insert movie times and more without leaving Hotmail®. See how.
Jul 2, 2009 at 3:24 PM
Fenny,
What is the error that you're getting when it's attempting to return the column with the date? do you need to cast it?

Jay





Windows Live™ SkyDrive™: Get 25 GB of free online storage. Get it on your BlackBerry or iPhone.
Jul 3, 2009 at 12:39 AM

Jay,

There's no any error return. My date value is "11-10-2008", and the value returned from the excel reader is "39732".
I have checked the date column in excel file (select the cell, then right-clcik -> Format cells -> Number tab), the selected category is Date, if I select General or Text category, the sample value displayed at the right is "39732".

Thanks. :-)

Jul 3, 2009 at 1:11 AM
Hmm, I'm not sure Fenny. Can you try the code that I posted against your spreadsheet? I was able to pickup dates with my version.






From: [email removed]
To: [email removed]
Date: Thu, 2 Jul 2009 17:39:57 -0700
Subject: Re: Read Excel Work Sheets [ExcelDataReader:59975]

From: fennychai
Jay,
There's no any error return. My date value is "11-10-2008", and the value returned from the excel reader is "39732".
I have checked the date column in excel file (select the cell, then right-clcik -> Format cells -> Number tab), the selected category is Date, if I select General or Text category, the sample value displayed at the right is "39732".
Thanks. :-)


Windows Live™ SkyDrive™: Get 25 GB of free online storage. Get it on your BlackBerry or iPhone.
Jul 6, 2009 at 3:54 AM

Basically, I read the data from DataTable, which populated by reader.AsDataSet(). The DataTable always return me a string of the date.

In order to get the date, I have to read it from reader.GetDatTime(i).

By the way, we can read multiple spreadsheet from reader object as well:

IExcelDataReader reader = // create reader based on the excel version

while(reader.NextResult()) // loop through the spreadsheet
{
    while(reader.Read()) // loop through the row of current sheet
    {
        DateTime date = reader.GetDateTime(i)
    }
}

Again, thanks Jay, you lead me out of the problems :)

Nov 19, 2009 at 9:41 PM

I do have one Excel file that gives error "Error: Invalid file signature." while reading it.

I can open it with Excel okay and properties say that it's type of 97-2003 worksheet. But if I save it again as same type file then it can be read by most of the libraries, like this one. My problem here is that I should be able to read it as it's created from another application on pc that has no Excel installed.

I can send sample file if anyone interested of studying it. I got hint that it's older variable format Excel file type.. but haven't found any library capable of reading it, outside MS Excel that is..

Nov 19, 2009 at 11:36 PM

Dilbertti,

Checkout this post to see if it addresses your issue.

 

This is something I've been playing with recently.

Check this post: Write an excel workbook to a memory stream .NET

It points to a great library by Carlos Aguilar Mares, which lets you work with Excel workbooks as XML.

ExcelXMLWriter

You dont need Excel installed on the server (which is kinda breaking the MS licensing anyway as you are accessing this over the web).

You can load the Excel workbook as a stream using Workbook.Load(stream)

This is something I've been playing with recently.

Check this post: Write an excel workbook to a memory stream .NET

It points to a great library by Carlos Aguilar Mares, which lets you work with Excel workbooks as XML.

ExcelXMLWriter

You dont need Excel installed on the server (which is kinda breaking the MS licensing anyway as you are accessing this over the web).

You can load the Excel workbook as a stream using Workbook.Load(stream)

Nov 20, 2009 at 7:38 PM

Heretix,

Nope, no such luck. Writing would be easy as XML or even HTLM table but writing is not something that I can control.

I should be able to read the file as it is. I have tested numerous commercial libraries also but all failed. Only Excel itself seem to be able to read the file.

Next I will test OpenOffice if functionlity from it could be used..