Solved

Excel Data Reader - Read Excel files in .NET

Posted on 2011-03-15
1
8,303 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

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…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

752 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