Solved

Excel Data Reader - Read Excel files in .NET

Posted on 2011-03-15
1
8,140 Views
Last Modified: 2012-06-27
Has anyone ever use this Excel object from codeplex?

Only problem I have is that if there are columns which have the same name, this will crash. Do you know how to work around this?

http://exceldatareader.codeplex.com/

Dim stream As FileStream = File.Open(filePath, FileMode.Open, FileAccess.Read)

'1. Reading from a binary Excel file ('97-2003 format; *.xls)
Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateBinaryReader(stream)
'...
'2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateOpenXmlReader(stream)
'...
'3. DataSet - The result of each spreadsheet will be created in the result.Tables
Dim result As DataSet = excelReader.AsDataSet()   <<<<<<<<<<<<<<<<<<<<<<<< error >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
'...
'4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = True
Dim result As DataSet = excelReader.AsDataSet()

'5. Data Reader methods
While excelReader.Read()
    'excelReader.GetInt32(0);
End While

'6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close()
0
Comment
Question by:VBdotnet2005
1 Comment
 
LVL 11

Accepted Solution

by:
SAMIR BHOGAYTA earned 500 total points
ID: 35144466
Hi, follow the following steps for you answer.

Steps:

1. Include the following reference into the project :

Microsoft Excel 10.0 Object Library

Microsoft Office 10.0 Object Library

2. Include the name space i.e. using Excel.

3. Creating the ExcelApplicationClass,WorkBook and Range.

Find the complete code below:

using System;
using Excel;
namespace TestExcel
{
class ExcelApplication
{
[STAThread]
static void Main(string[] args)
{

string Path = @"c:\test.xls";
// initialize the Excel Application class
Excel.ApplicationClass app = new ApplicationClass();
// create the workbook object by opening  the excel file.
Excel.Workbook workBook = app.Workbooks.Open(Path,
0,
true,
5,
"",
"",
true,
Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
// Get The Active Worksheet Using Sheet Name Or Active Sheet
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.ActiveSheet;
int index = 0;
// This row,column index should be changed as per your need.
// that is which cell in the excel you are interesting to read.
object rowIndex = 2;
object colIndex1 = 1;
object colIndex2 = 2;
try
{
while ( ((Excel.Range)workSheet.Cells[rowIndex,colIndex1]).Value2 != null )
{
rowIndex = 2+index;
string firstName = ((Excel.Range)workSheet.Cells[rowIndex,colIndex1]).Value2.ToString();
string lastName = ((Excel.Range)workSheet.Cells[rowIndex,colIndex2]).Value2.ToString();
Console.WriteLine("Name : {0},{1} ",firstName,lastName);
index++;
}
}
catch(Exception ex)
{
app.Quit();
Console.WriteLine(ex.Message);
}
}

}
}
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question