อ่าน Excel ง่ายๆ ด้วย ExcelDataReader รองรับ xls, xlsx โดยไม่ต้องติดตั้ง Ms Office (VB.Net, C#)

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

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

//5. Data Reader methods
while (excelReader.Read())
{
	//excelReader.GetInt32(0);
}

//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();

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()
'...
'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()

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using Excel;

namespace ExcelReader
{
    public partial class myWebForm : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            using (FileStream stream = File.Open(Server.MapPath("Xls/myExcel.xlsx"), FileMode.Open, FileAccess.Read))
            {
                IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                excelReader.IsFirstRowAsColumnNames = true;
                DataSet ds = excelReader.AsDataSet();

                this.myGridView.DataSource = ds.Tables[0];
                this.myGridView.DataBind();
            }

        }
    }
}

Imports System.IO
Imports System.Data
Imports Excel

Public Class myWebForm
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Using stream As FileStream = File.Open(Server.MapPath("Xls/myExcel.xlsx"), FileMode.Open, FileAccess.Read)
            Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateOpenXmlReader(stream)
            excelReader.IsFirstRowAsColumnNames = True
            Dim ds As DataSet = excelReader.AsDataSet()

            Me.myGridView.DataSource = ds.Tables(0)
            Me.myGridView.DataBind()
        End Using
    End Sub

End Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using Excel;

namespace ExcelReader
{
    public class ExcelColumn
    {
        public string Col1 { get; set; }
        public string Col2 { get; set; }
        public string Col3 { get; set; }
        public string Col4 { get; set; }
        public string Col5 { get; set; }
    }

    public partial class myWebForm : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            using (FileStream stream = File.Open(Server.MapPath("Xls/myExcel.xlsx"), FileMode.Open, FileAccess.Read))
            {
                IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                excelReader.IsFirstRowAsColumnNames = true;
                var ls = new List<ExcelColumn>();
                while (excelReader.Read())
                {
                    //excelReader.GetString(0);
                    //excelReader.GetString(1);
                    //excelReader.GetString(2);
                    //excelReader.GetString(3);
                    //excelReader.GetString(4);
                    ls.Add(new ExcelColumn
                    {
                        Col1 = excelReader.GetString(0),
                        Col2 = excelReader.GetString(1),
                        Col3 = excelReader.GetString(2),
                        Col4 = excelReader.GetString(3),
                        Col5 = excelReader.GetString(4),
                    });
                }

                this.myGridView.DataSource = ls;
                this.myGridView.DataBind();
            }
        }
    }
}

Imports System.IO
Imports System.Data
Imports Excel

Public Class ExcelColumn
    Public Property Col1 As String
        Get
            Return m_Col1
        End Get
        Set
            m_Col1 = Value
        End Set
    End Property
    Private m_Col1 As String
    Public Property Col2() As String
        Get
            Return m_Col2
        End Get
        Set
            m_Col2 = Value
        End Set
    End Property
    Private m_Col2 As String
    Public Property Col3() As String
        Get
            Return m_Col3
        End Get
        Set
            m_Col3 = Value
        End Set
    End Property
    Private m_Col3 As String
    Public Property Col4() As String
        Get
            Return m_Col4
        End Get
        Set
            m_Col4 = Value
        End Set
    End Property
    Private m_Col4 As String
    Public Property Col5() As String
        Get
            Return m_Col5
        End Get
        Set
            m_Col5 = Value
        End Set
    End Property
    Private m_Col5 As String
End Class

Public Class myWebForm
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Using stream As FileStream = File.Open(Server.MapPath("Xls/myExcel.xlsx"), FileMode.Open, FileAccess.Read)
            Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateOpenXmlReader(stream)
            excelReader.IsFirstRowAsColumnNames = True
            Dim ls = New List(Of ExcelColumn)()
            While excelReader.Read()
                'excelReader.GetString(0)
                'excelReader.GetString(1)
                'excelReader.GetString(2)
                'excelReader.GetString(3)
                'excelReader.GetString(4)
                ls.Add(New ExcelColumn() With {
                    .Col1 = excelReader.GetString(0),
                    .Col2 = excelReader.GetString(1),
                    .Col3 = excelReader.GetString(2),
                    .Col4 = excelReader.GetString(3),
                    .Col5 = excelReader.GetString(4)})
            End While

            Me.myGridView.DataSource = ls
            Me.myGridView.DataBind()
        End Using
    End Sub

End Class