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）