用OleDb把EXCEL当作表读取时的注意事项

 经过实验，在VB.net下通过OleDb读取EXCEL文件时并不支持UNION, LEFT　ＪＩＯＮ等语句。ＩＮ语句能够取得正确数据，ＮＯＴ　ＩＮ却无法取得正确数据。

经过验证，可以用 NOT　ＥＸＩＳＴＳ语句代替ＮＯＴ IN,代码如下：

Protected Sub btnUnmatch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUnmatch.Click
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand
        Dim arrRSFundCd(), arrCustodyFundCd() As String
        Dim strSQL As String
        Dim strTemp As String = ""

        '接続文字列 
        Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                                            & "Data Source=" & Server.MapPath("~/Temp/balance.xls") & ";" _
                                            & "Extended Properties=""Excel 8.0;HDR=YES"""

        arrRSFundCd = WebConfigurationManager.AppSettings("GC_PassiveRecoRSFundCd").Split(","c)
        arrCustodyFundCd = WebConfigurationManager.AppSettings("GC_PassiveRecoJPMorganFundCd").Split(","c)

        'RS残高インポートファイルの存在性チェック
        If Not File.Exists(Server.MapPath("~/Temp/balance.xls")) Then
            lblInformation.ForeColor = Drawing.Color.Red
            lblInformation.Text = "RS残高ファイルを先にインポートしてください。"
            Exit Sub
        End If

        'ファンド件数チェック
        If arrRSFundCd.Length <> arrCustodyFundCd.Length Then
            lblInformation.ForeColor = Drawing.Color.Red
            lblInformation.Text = "ConfigファイルのRSファンドとCustodyファンドの件数が合っていません。もう一度確認してください。"
        End If

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

        '接続 
        con.Open()

        For i As Integer = 0 To arrRSFundCd.Length - 1

            strSQL = " INSERT INTO [UnMatchReport$] " & _
                    " SELECT R.[ポートフォリオ] AS [RSポートフォリオ], C.[Account Number] AS [Custodyファンドコード], R.[銘柄コード] AS [RS銘柄コード], C.[ISIN] AS [Custody銘柄コード],  R.[銘柄名（日本語）] AS [銘柄名], R.[保有数量(通常)] AS [RS保有数量], C.[Total Units] AS [Custody保有数量] " & _
                    " FROM [RS残高$] R, [Custody残高$] C WHERE " & _
                    " R.[ポートフォリオ] = " & arrRSFundCd(i) & " AND " & _
                    " C.[Account Number] = " & arrCustodyFundCd(i) & " AND " & _
                    " R.[銘柄コード] = C.[ISIN] AND " & _
                    " R.[保有数量(通常)] <> CSTR(C.[Total Units]) "

            cmd = New OleDbCommand(strSQL, con)
            cmd.ExecuteNonQuery()



            strSQL = " INSERT INTO [UnMatchReport$] " & _
                    "SELECT [ポートフォリオ] AS [RSポートフォリオ], NULL AS [Custodyファンドコード], [銘柄コード] AS [RS銘柄コード], NULL AS [Custody銘柄コード],  [銘柄名（日本語）] AS [銘柄名], [保有数量(通常)] AS [RS保有数量], NULL AS [Custody保有数量] " & _
                    " FROM [RS残高$] WHERE [ポートフォリオ] = " & arrRSFundCd(i) & "AND NOT EXISTS (SELECT * FROM [Custody残高$] WHERE [Account Number] = " & arrCustodyFundCd(i) & " AND [ISIN] = [RS残高$].[銘柄コード])"

            cmd = New OleDbCommand(strSQL, con)
            cmd.ExecuteNonQuery()
        Next

        '接続閉じる
        con.Close()

        lblInformation.ForeColor = Drawing.Color.Black
        lblInformation.Text = "UnMatchレポートを作成しました。"
    End Sub