Excelファイルを読みデータベースに登録

Excelファイルを読み、データベースに登録するサンプルです。
ExcelCreatorを利用し、Excelファイルを読み、OracleのEMP表にインポートします。

(シチュエーション)

複数の人がインポートするExcelファイルを持っています。
で、人によって列位置や列のタイトルを自分がわかりやすくしたいとの事です。例えばAさんのExcelファイルはEMP表の「EMPNO」がC列、BさんはD列、またタイトルも[EMPNO]や[EMP NUMBER]だったりします。

インポートする行を設定可能にし、インポートした行にはフラグを立てます。

これらの要件を考慮したサンプルを作成します。

インポートするExcelファイル

人によって、列位置や列タイトルが違うとの事なので、この項目はセルA3を見る..等、固定できません。
なので、かっこ悪いけどちょっとしたルール設定をユーザにお願い。

  • 列項目を特定する為の文字列や数値をにタイトルの上につけてもらう。(例 「3」はDEPTNO等、ルールを決めておく)(A列のどこか)
  • データ行開始位置を特定できる文字列や数値を、セル(固定セル)につけてもらう。(A列のどこか)
  • データ行のどこかの列(例えば下図の「インポートする」列)が一定数連続して空白セルなら終了(もちろん空白の行は処理しない)

などなど、めんどくさくない程度にルール設定をさせてもらえれば、どうにでもなるでしょう。

AさんのExcelファイル

B3 - K3 に列を表す番号を入れてもらいます。
4行目は、列のタイトルですね。そしてセルA5には「DATA」と入れてもらい、その行からデータが始まるものとします。
AさんのExcelファイル

BさんのExcelファイル
B4 - K4 に列を表す番号を入れています。Aさんは3行目でした。
また、列位置もAさんとは異なってます。
5行目は、列のタイトルです。
AさんのExcelファイル

サンプルプログラム

Imports Oracle.DataAccess.Client

Public Class frmXlsCreator2_Import


    Private ConnectionString As String
    Private dt As DataTable = Nothing

    '列位置保管用
    Structure ColumnFieldIdx
        Dim isImport As Integer
        Dim ImportFlg As Integer
        Dim EMPNO As Integer
        Dim ENAME As Integer
        Dim JOB As Integer
        Dim MGR As Integer
        Dim HIREDATE As Integer
        Dim SAL As Integer
        Dim COMM As Integer
        Dim DEPTNO As Integer
    End Structure
    Private ColumnIndex As ColumnFieldIdx

    Private Sub frmXlsCrearor2_Import_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '接続文字列のセット
        ConnectionString = "DATA SOURCE=ORCL;User ID=scott;password=tiger;"

        Edit1.Text = My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\vbnetdb_ExcelCreatorSample.xls"
        getSheetList()

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        getSheetList()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ExcelImport(Edit1.Text, Combo1.SelectedItem.Content.ToString)
    End Sub

    ''' 
    ''' シート名を取得してコンボボックスにセット
    ''' 
    ''' 
    Private Sub getSheetList()
        Dim i As Integer

        XlsCr1.OpenBook(Edit1.Text, "")
        Combo1.Items.Clear()
        For i = 0 To XlsCr1.SheetCount - 1
            Combo1.Items.Add(XlsCr1.SheetName2(i))
        Next
        XlsCr1.CloseBook(False)
    End Sub

    ''' 
    ''' Excel Creator エラーイベント
    ''' 
    ''' 
    ''' 
    ''' 
    Private Sub XlsCreator1_Error(ByVal sender As Object, ByVal e As ExcelCreator.XlsCreatorEventArgs) Handles XlsCr1.Error
        Throw New Exception(e.ErrorNo.ToString & ":" & XlsCr1.ErrorMessage)

    End Sub

    ''' 
    ''' ExcelファイルからImport
    ''' 
    ''' 
    ''' 
    ''' 
    Private Sub ExcelImport(ByVal SourceFileName As String, ByVal SheetName As String)
        Dim col As Integer
        Dim row As Integer
        '連続空白セルカウンタ
        Dim NullCount As Integer = 0

        Try
            '既存のExcelファイルを読み書きオープン
            XlsCr1.OpenBook(SourceFileName, "")

            'シートの切り替え:シート名からシートNoを取得して切り替え
            XlsCr1.SheetNo = XlsCr1.SheetNo2(SheetName)

            '----------------------------------------
            '各列の列位置を取得
            '----------------------------------------
            '列位置格納用変数の初期化
            With ColumnIndex
                .isImport = -1
                .ImportFlg = -1
                .EMPNO = -1
                .ENAME = -1
                .JOB = -1
                .MGR = -1
                .HIREDATE = -1
                .SAL = -1
                .COMM = -1
                .DEPTNO = -1
            End With

            '列番号がセットされているセルの行位置、データ開始行の行位置を取得
            '列番号が設置されているセルの行位置は、A列で「HEAD」と記してもらう
            'データ開始行のセル行位置はA列で「DATA」と記してもらう
            Dim isEnd As Boolean = False
            Dim HeadNoRow As Integer = -1
            Dim HeadNoCol As Integer = 0
            Dim DataNoRow As Integer = -1
            row = 0
            While Not isEnd
                If XlsCr1.Pos(0, row).Str.ToUpper = "HEAD" Then
                    HeadNoRow = row
                    NullCount = 0
                ElseIf XlsCr1.Pos(0, row).Str.ToUpper = "DATA" Then
                    DataNoRow = row
                    NullCount = 0
                Else
                    NullCount += 1
                End If

                If HeadNoRow > -1 And DataNoRow > -1 Then
                    isEnd = True
                Else
                    row += 1
                    If NullCount > 9 Then
                        MessageBox.Show("列番号文字列(HEAD)、データ開始行文字列(DATA)が見つかりません..", _
                            "インポートを終了します", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        XlsCr1.CloseBook(False)
                        Exit Try
                    End If
                End If
            End While


            Dim MaxData As Size = XlsCr1.MaxData(ExcelCreator.xlPoint.ptMaxPoint)
            isEnd = False
            col = HeadNoCol + 1
            While Not isEnd
                If XlsCr1.Pos(col, HeadNoRow).Str.Trim.Length > 0 Then
                    Select Case XlsCr1.Pos(col, HeadNoRow).Str.Trim
                        Case "1" : ColumnIndex.isImport = col
                        Case "2" : ColumnIndex.ImportFlg = col
                        Case "3" : ColumnIndex.EMPNO = col
                        Case "4" : ColumnIndex.ENAME = col
                        Case "5" : ColumnIndex.JOB = col
                        Case "6" : ColumnIndex.MGR = col
                        Case "7" : ColumnIndex.HIREDATE = col
                        Case "8" : ColumnIndex.SAL = col
                        Case "9" : ColumnIndex.COMM = col
                        Case "10" : ColumnIndex.DEPTNO = col
                        Case Else
                    End Select
                    col += 1
                Else
                    isEnd = True
                End If
            End While

            '----------------------------------------
            'データ開始行
            '----------------------------------------
            isEnd = False
            row = DataNoRow
            While Not isEnd
                '「インポートする」列に空白セルが10個続いたら終わる事にする
                If XlsCr1.Pos(ColumnIndex.isImport, row).Str.Trim.Length > 0 Then
                    NullCount = 0

                    If XlsCr1.Pos(ColumnIndex.isImport, row).Str.Trim = "1" Then

                        'Oracle更新
                        ImportData( _
                            XlsCr1.Pos(ColumnIndex.EMPNO, row).Long, _
                            XlsCr1.Pos(ColumnIndex.ENAME, row).Str.TrimEnd, _
                            XlsCr1.Pos(ColumnIndex.JOB, row).Str.TrimEnd, _
                            XlsCr1.Pos(ColumnIndex.MGR, row).Long, _
                            DateTime.FromOADate(Convert.ToDouble(XlsCr1.Pos(ColumnIndex.HIREDATE, row).Value)), _
                            CDec(XlsCr1.Pos(ColumnIndex.SAL, row).Double), _
                            CDec(XlsCr1.Pos(ColumnIndex.COMM, row).Double), _
                            XlsCr1.Pos(ColumnIndex.DEPTNO, row).Long)

                        'インポート済みFLGセット
                        XlsCr1.Pos(ColumnIndex.ImportFlg, row).Long = 1

                    End If
                Else
                    NullCount += 1
                    If NullCount > 9 Then
                        isEnd = True
                    End If
                End If

                row += 1

            End While


            'Excelファイル保存
            XlsCr1.CloseBook(True)

            'Gridに表示
            ShowData()

        Catch ex As Exception
            MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
            XlsCr1.CloseBook(False)

        Finally

        End Try
    End Sub

    ''' 
    ''' EMP表データ登録・更新
    ''' 
    ''' 
    ''' 
    ''' 
    ''' 
    ''' 
    ''' 
    ''' 
    ''' 
    ''' 
    Private Sub ImportData(ByVal EmpNo As Integer, ByVal EName As String, ByVal Job As String, _
                            ByVal Mgr As Integer, ByVal HireDate As Date, ByVal Sal As Decimal, _
                            ByVal Comm As Decimal, ByVal DeptNo As Integer)

        Const cmdSelect As String = _
            "select 'HOGE' from EMP " & _
            "where " & _
            "EMPNO = :EMPNO and " & _
            "ROWNUM <= :WROWNUM"

        Const cmdInsert As String = _
            "insert into EMP (" & _
            "EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO" & _
            ") values (" & _
            ":EMPNO, :ENAME, :JOB, :MGR, :HIREDATE, :SAL, :COMM, :DEPTNO" & _
            ")"

        Const cmdUpdate As String = _
            "update EMP set " & _
            "ENAME = :ENAME, JOB = :JOB, MGR = :MGR, HIREDATE = :HIREDATE," & _
            "SAL = :SAL, COMM = :COMM, DEPTNO = :DEPTNO " & _
            "where " & _
            "EMPNO = :EMPNO"

        Dim isPresence As Boolean = False
        Dim rd As OracleDataReader = Nothing

        Try
            Using cn As New OracleConnection(ConnectionString)
                cn.Open()

                Using cmd As New OracleCommand(cmdSelect, cn)
                    cmd.BindByName = True
                    cmd.Parameters.Add("EMPNO", OracleDbType.Int32).Value = EmpNo
                    cmd.Parameters.Add("WROWNUM", OracleDbType.Int32).Value = 1
                    rd = cmd.ExecuteReader
                    If rd.Read Then
                        isPresence = True
                    End If
                    rd.Close()

                    If Not isPresence Then
                        cmd.CommandText = cmdInsert
                    Else
                        cmd.CommandText = cmdUpdate
                    End If

                    cmd.Parameters.Clear()
                    cmd.Parameters.Add("EMPNO", OracleDbType.Int32).Value = EmpNo
                    cmd.Parameters.Add("ENAME", OracleDbType.Varchar2).Value = EName
                    cmd.Parameters.Add("JOB", OracleDbType.Varchar2).Value = Job
                    cmd.Parameters.Add("MGR", OracleDbType.Int32).Value = Mgr
                    cmd.Parameters.Add("HIREDATE", OracleDbType.Date).Value = HireDate
                    cmd.Parameters.Add("SAL", OracleDbType.Decimal).Value = Sal
                    cmd.Parameters.Add("COMM", OracleDbType.Decimal).Value = Comm
                    cmd.Parameters.Add("DEPTNO", OracleDbType.Int16).Value = DeptNo

                    cmd.ExecuteNonQuery()

                End Using

                cn.Clone()

            End Using

        Catch ex As Exception
            Throw ex
        Finally
            If Not IsNothing(rd) Then
                If Not rd.IsClosed Then
                    rd.Close()
                End If
                rd.Dispose()
            End If
        End Try
    End Sub

    ''' 
    ''' データ表示
    ''' 
    ''' 
    Private Sub ShowData()
        Const cmdSelect As String = _
                "select " & _
                "EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO " & _
                "from EMP"
        Try
            Using da As New OracleDataAdapter(cmdSelect, ConnectionString)
                dt = New DataTable
                da.Fill(dt)
                C1TrueDBGrid1.DataSource = dt
            End Using

        Catch ex As Exception
            Throw ex
        End Try
    End Sub
End Class

履歴

2011/08/30
公開
Loading