Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Data Reader - Read Excel files in .NET

Posted on 2011-03-15
1
Medium Priority
?
8,696 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
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…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

715 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