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オブジェクトでやってみました
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)