ASP.Netのお勉強

OLEDBを用いてExcelデータ抽出@ASP.Net

OLEDBを利用して、Excelに接続し、Excelのデータを抽出します。
以下では、Excelに接続し、シートに対してSQL文を発行して、
データを抽出し、GridViewに表示しています。

●aspx

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default4.aspx.vb" Inherits="Default4" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Excelのデータを抽出</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="Button1" runat="server" Text="Button" />
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
    </div>
    </form>
</body>
</html>


●aspx.vb

Imports System.Data
Imports System.Data.OleDb

Partial Class Default4
    Inherits System.Web.UI.Page

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim con As OleDbConnection
        Dim cmd As OleDbCommand

        Dim da As OleDbDataAdapter
        Dim ds As DataSet

        '接続文字列
    Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                                        & "Data Source=C:\test\Book1.xls;" _
                                        & "Extended Properties=""Excel 8.0;HDR=YES"""

        'OleDbConnectionの生成
        con = New OleDbConnection(sConnectionString)

        '接続
        con.Open()

        'OleDbCommandの生成
        cmd = New OleDbCommand("SELECT * FROM [Sheet1$]", con)

        'OleDbDataAdapter作成
        da = New OleDbDataAdapter
        da.SelectCommand = cmd

        'DataSetの生成
        ds = New DataSet
        da.Fill(ds, "Excel")

        'GridViewに配置、DataBind
        GridView1.DataSource = ds.Tables(0).DefaultView
        GridView1.DataBind()

        '接続を閉じる
        con.Close()

    End Sub
End Class


Copyright (C) ASP.Netのお勉強. All Rights Reserved.