EMP表の一覧Excelファイルを新規作成

サンプルプログラム

ODP.NETを利用して、EMP表の一覧Excelファイルを作成します。
ただEMP表のデフォルト行数は少なくてつまらないので、同じ定義のEMPTESTを作成し、10,000行をセットしました。

 Public Class frmXlsCreator1

    'ExcelCreator
    Private xlsCR As New ExcelCreator.XlsCreator
    Private ConnectionString As String

    Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click

        Const cmdSelect As String = _
             "select " & _
             "EMPNO, ENAME, JOB, NVL(MGR,0) MGR, HIREDATE, " & _
             "NVL(SAL,0) SAL, NVL(COMM,0) COMM, DEPTNO " & _
             "from EMPTEST " 

        Dim i As Integer
        Dim dt As New DataTable
        Dim xlsRowNo As Integer = 0     'Excelファイルの行位置
        Dim drow As DataRow

        Try
            Dim strTimeStart As String = DateTime.Now.ToString '開始時刻

            Using da As New OracleDataAdapter(cmdSelect, ConnectionString)
                da.Fill(dt)

                If dt.Rows.Count > 0 Then
                    '===================================================================
                    'Excel作成
                    '===================================================================
                    'ブック作成
                    xlsCR.CreateBook("EMP_LIST.xls", 1, ExcelCreator.xlVersion.ver2003)
                    'シート選択
                    xlsCR.SheetNo = 0
                    'シート名の設定
                    xlsCR.SheetName = "EMP LIST"

                    '-------------------------------------------------------------------
                    '見出し出力
                    '-------------------------------------------------------------------
                    For i = 0 To dt.Columns.Count - 1
                        xlsCR.Pos(i, 0).Str = dt.Columns(i).ColumnName.TrimEnd
                    Next
                    xlsRowNo += 1

                    '下線を引く
                    xlsCR.Pos(0, 0, dt.Columns.Count - 1, 0).Attr.LineBottom = ExcelCreator.xlLineStyle.lsNormal
                    '背景色を黄色にする
                    xlsCR.Pos(0, 0, dt.Columns.Count - 1, 0).Attr.BackColor = ExcelCreator.xlColor.xcYellow


                    '-------------------------------------------------------------------
                    '行出力
                    '-------------------------------------------------------------------
                    For i = 0 To dt.Rows.Count - 1
                        drow = dt.Rows(i)

                        xlsCR.Pos(0, xlsRowNo).Long = CInt(drow.Item("EMPNO"))
                        xlsCR.Pos(1, xlsRowNo).Str = drow.Item("ENAME").ToString.TrimEnd
                        xlsCR.Pos(2, xlsRowNo).Str = drow.Item("JOB").ToString.TrimEnd
                        xlsCR.Pos(3, xlsRowNo).Long = CInt(drow.Item("MGR"))
                        xlsCR.Pos(4, xlsRowNo).Value = drow.Item("HIREDATE")
                        xlsCR.Pos(4, xlsRowNo).Attr.Format = "yyyy/m/d"
                        xlsCR.Pos(5, xlsRowNo).Double = CDec(drow.Item("SAL"))
                        xlsCR.Pos(6, xlsRowNo).Double = CDec(drow.Item("COMM"))
                        xlsCR.Pos(7, xlsRowNo).Long = CInt(drow.Item("DEPTNO"))

                        xlsRowNo += 1

                    Next

                    ' クローズ
                    xlsCR.CloseBook(True)

                    Dim strTimeEnd As String = DateTime.Now.ToString '終了時刻
                    MessageBox.Show("EMP_LIST.xls を作成しました。" & vbCrLf & strTimeStart & "~" & strTimeEnd, "完了")

                    ' 作成した Excel ファイルを開く(実行環境に Excel がある場合のみ)
                    If IsNothing(Type.GetTypeFromProgID("Excel.Application")) Then
                        ' 実行環境に Excel がない場合、完了メッセージのみ
                        MessageBox.Show("EMP_LIST.xls を作成しました。", "作成完了")
                    Else
                        Dim dlg As DialogResult
                        dlg = MessageBox.Show( _
                                "EMP_LIST.xls を作成しました。" & vbCrLf & _
                                "作成した Excel ファイルを開きますか?", _
                                "完了", MessageBoxButtons.YesNo)

                        If dlg = DialogResult.Yes Then
                            ' Excel ファイル起動
                            Dim prFile As System.Diagnostics.Process
                            prFile = New System.Diagnostics.Process()
                            prFile = System.Diagnostics.Process.Start("EMP_LIST.xls")
                        End If
                    End If
                End If
            End Using

        Catch ex As Exception
            MessageBox.Show(ex.Message, "例外エラー", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally

        End Try
    End Sub

    Private Sub frmXlsCreator1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        '接続文字列のセット
        ConnectionString = "DATA SOURCE=ORCL;User ID=scott;password=tiger;"

    End Sub

    Private Sub XlsCreator1_Error(ByVal sender As Object, ByVal e As ExcelCreator.XlsCreatorEventArgs) Handles XlsCreator1.Error
        MessageBox.Show(e.ErrorNo.ToString)
    End Sub
End Class

コードの説明

  • Line31 : CreateBook メソッドで Bookを新規に作成します。

    作成するファイル名、シート数、Excelのバージョンを指定します。
    既存のファイル名を指定した場合、既存ファイルを削除した後に新規作成されますので要注意です。

  • Line41 : 見出行のセット ExcelCreator.Posクラスでセル値を設定

    Posクラスでは、出力対象となるセルの範囲を座標形式で設定します。
    ここでは.文字列として設定したいので、Strプロパティにセットしました。 

  • Line46,48 : 見出行の装飾 見出し行なのでアンダーラインと背景色を変えました
  • Line57 - 65 : 明細行のセット

    Posクラスを用いて、対象セルに値をセットしています。
    それぞれの型にあわせたプロパティ(Str,Longなど)にセットしています。

  • Line72 : ファイルをクローズしています

作成されたExcelファイル

このようにExcelファイルを簡単に作成する事が出来ます。

 

Excelオブジェクトでもやってみる

上記と同じ事をExcelオブジェクトでやってみました

 
    Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click

        Const cmdSelect As String = _
            "select " & _
            "EMPNO, ENAME, JOB, NVL(MGR,0) MGR, HIREDATE, " & _
            "NVL(SAL,0) SAL, NVL(COMM,0) COMM, DEPTNO " & _
            "from EMPTEST "

        'Excelオブジェクト
        Dim oExcel As New Excel.ApplicationClass
        'WorkBookオブジェクト
        Dim oBook As Excel.WorkbookClass = Nothing
        Dim oSheet As Excel.Worksheet = Nothing

        Dim i As Integer
        Dim dt As New DataTable
        Dim xlsRowNo As Integer = 1     'Excelファイルの行位置
        Dim drow As DataRow

        Try
            Dim FilePath As String = My.Application.Info.DirectoryPath & "\EMP_LIST2.xls"
            IO.File.Delete(FilePath)

            Dim strTimeStart As String = DateTime.Now.ToString '開始時刻

            'Excelを非表示にする
            oExcel.Application.Visible = False

            Using da As New OracleDataAdapter(cmdSelect, ConnectionString)
                da.Fill(dt)

                If dt.Rows.Count > 0 Then

                    oBook = CType(oExcel.Workbooks.Add(), Excel.WorkbookClass)
                    oSheet = CType(oBook.Worksheets(1), Excel.Worksheet)

                    '-------------------------------------------------------------------
                    '見出し出力
                    '-------------------------------------------------------------------
                    For i = 0 To dt.Columns.Count - 1
                        oSheet.Cells(xlsRowNo, i + 1) = dt.Columns(i).ColumnName.TrimEnd
                    Next
                    xlsRowNo += 1

                    '-------------------------------------------------------------------
                    '行出力
                    '-------------------------------------------------------------------
                    For i = 0 To dt.Rows.Count - 1
                        drow = dt.Rows(i)
                        With oSheet
                            .Cells(xlsRowNo, 1) = CInt(drow.Item("EMPNO"))
                            .Cells(xlsRowNo, 2) = drow.Item("ENAME").ToString.TrimEnd
                            .Cells(xlsRowNo, 3) = drow.Item("JOB").ToString.TrimEnd
                            .Cells(xlsRowNo, 4) = CInt(drow.Item("MGR"))
                            .Cells(xlsRowNo, 5) = drow.Item("HIREDATE")
                            .Cells(xlsRowNo, 6) = CDec(drow.Item("SAL"))
                            .Cells(xlsRowNo, 7) = CDec(drow.Item("COMM"))
                            .Cells(xlsRowNo, 8) = CInt(drow.Item("DEPTNO"))
                        End With

                        xlsRowNo += 1
                    Next

                    'ファイルを保存する
                    oBook.SaveAs(FilePath, Excel.XlFileFormat.xlExcel7)
                    oBook.Close(False)
                    oExcel.Quit()

                    Dim strTimeEnd As String = DateTime.Now.ToString '終了時刻
                    MessageBox.Show("EMP_LIST2.xls を作成しました。" & vbCrLf & strTimeStart & "~" & strTimeEnd, "完了")
                End If
            End Using

        Catch ex As Exception
            MessageBox.Show(ex.Message, "例外エラー", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
        End Try
    End Sub

自分の環境では、ヒジョーに遅かった....

実行時間の比較

1000行出力と、10000行出力を実行しました。
ExcelCreatorが圧倒的に速く処理出来ました。

  1000行出力時の実行時間 10000行出力
Excel Creator
3秒
4秒
Excel オブジェクト
21秒
4分11秒

筆者のPC環境が劣悪で全体的に遅いとは思うけれども、この差はすごいと思います。
(筆者のPC環境:WindowsXP SP3 : Dell GX270 :Pen4 2.8GHz : メモリ 1GB : Oracle10g)

修正履歴

Loading