データテーブルをExcelに出力するサンプルコード:アーキテクト360

データテーブルをExcelに出力するサンプルコード

アプリケーションのデータをExcelに出力したいケースがしばしばある。毎回ゼロから作り直すのは面倒であるため筆者が作成したある程度実用的なサンプルコードを公開する。マイクロソフトのサイトによるとExcelにデータを出力する方法はいくつかあるが、サンプルの方法はセル単位に出力する方式である。この方式の場合は、大量のデータを出力する際にパフォーマンスが悪くなる恐れがあるが、セル単位に出力する方式の特徴を生かして、データの型に応じてExcelの書式（数値とか文字列）を変えるようにしている。したがって、Excelに出力したデータをさらに集計して利用するようなケースに適している。また、シートの１行目は列名を表示してデータの項目がわかるようにしている。

//エクセルシートにデータを書き出す行番号
        private const int ROW_OFFSET = 2;
        //（注意）Excelシートの行、列のインデックスは１から始まる
        //エクセルシートにデータを書き出す列番号
        private const int COLUMN_OFFSET = 1;

// <summary>
	    // データテーブルをエクセルファイルに出力します
	    // </summary>
        public static bool CreateExcelFromDataTable(DataTable dt, string filePath, string sheetName)
        {
            //ヘッダー名称のリスト
            List<string> headers = new List<string>();

foreach (System.Data.DataColumn col in dt.Columns)
            {
                headers.Add(col.ColumnName);
            }

Microsoft.Office.Interop.Excel.Application xlsApplication = null;
            Microsoft.Office.Interop.Excel.Workbooks xlsBooks = null;
            Microsoft.Office.Interop.Excel.Workbook xlsBook = null;
            Microsoft.Office.Interop.Excel.Sheets xlsSheets = null;
            Microsoft.Office.Interop.Excel.Worksheet xlsSheet = null;
            Microsoft.Office.Interop.Excel.Range xlsRange = null;

try
            {
                xlsApplication = new Microsoft.Office.Interop.Excel.Application();
                xlsApplication.DisplayAlerts = false;
                //保存時の確認ダイアログを表示しない

xlsBooks = xlsApplication.Workbooks;
                xlsBook = xlsBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                xlsSheets = xlsBook.Worksheets;

//（注意）シートのインデックスは１から始まる
                xlsSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlsSheets[1];
                xlsSheet.Name = sheetName;
                
                for (int i = 0; i <= headers.Count - 1; i++)
                {
                    xlsRange = (Microsoft.Office.Interop.Excel.Range)xlsSheet.Cells[1, i + 1];
                    xlsRange.Value2 = headers[i];
                }

// セルに値を設定する。
                int sheetRowIndex = ROW_OFFSET;

foreach (DataRow row in dt.Rows)
                {
                    int sheetColumnIndex = COLUMN_OFFSET;

foreach (DataColumn column in dt.Columns)
                    {

if (!row.IsNull(column))
                        {
                            xlsRange = (Microsoft.Office.Interop.Excel.Range)xlsSheet.Cells[sheetRowIndex, sheetColumnIndex];

if (column.DataType.Name == "Integer" | 
                                column.DataType.Name == "Int32" | 
                                column.DataType.Name == "Decimal" | 
                                column.DataType.Name == "Long" | 
                                column.DataType.Name == "Double" | 
                                column.DataType.Name == "Short")
                            {
                                //セルの書式を数値型に設定
                                xlsRange.NumberFormatLocal = "G/標準";
                            }
                            else if (column.DataType.Name == "DateTime")
                             {
                             xlsRange.NumberFormatLocal = "yyyy/m/d h:mm";
                             }
                            else
                            {
                                //セルの書式を文字列型に設定
                                xlsRange.NumberFormatLocal = "@";
                            }

xlsRange.Value2 = row[column];
                            ReleaseComObject((object)xlsRange);
                            sheetColumnIndex += 1;
                        }
                    }
                    sheetRowIndex += 1;
                }

// 保存
                xlsBook.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, null, null, null, null, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

return true;
            }

catch (Exception ex)
            {
                return false;
            }
            finally
            {
                // エクセル関係のオブジェクトは必ず解放すること
                ReleaseComObject((object)xlsRange);
                ReleaseComObject((object)xlsSheet);
                ReleaseComObject((object)xlsSheets);
                xlsBook.Close(true,filePath,false);
                ReleaseComObject((object)xlsBook);
                ReleaseComObject((object)xlsBooks);
                xlsApplication.Quit();
                ReleaseComObject((object)xlsApplication);
            }
        }

/// <summary>
        /// COMオブジェクトを開放します。
        /// </summary>
        private static void ReleaseComObject(object target)
        {
            try
            {
                if ((target != null))
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(target);
                }
            }
            finally
            {
                target = null;
            }
        }

'（注意）Excelシートの行、列のインデックスは１から始まる
    Private Const ROW_OFFSET As Integer = 2 'エクセルシートにデータを書き出す行番号
    '（注意）Excelシートの行、列のインデックスは１から始まる
    Private Const COLUMN_OFFSET As Integer = 1 'エクセルシートにデータを書き出す列番号

’’’<summary>
    ’’’データテーブルをエクセルファイルに出力します
    ’’’</summary>
   Public Shared Function CreateExcelFromDataTable(ByVal dt As DataTable, _
                                                 ByVal filePath As String, _
                                                 ByVal sheetName As String) As Boolean
        'ヘッダー名称のリスト
        Dim headers As List(Of String) = New List(Of String)

For Each col As System.Data.DataColumn In dt.Columns
            headers.Add(col.ColumnName)
        Next

Dim xlsApplication As Excel.Application = Nothing
        Dim xlsBooks As Excel.Workbooks = Nothing
        Dim xlsBook As Excel.Workbook = Nothing
        Dim xlsSheets As Excel.Sheets = Nothing
        Dim xlsSheet As Excel.Worksheet = Nothing
        Dim xlsRange As Excel.Range = Nothing

Try
            xlsApplication = New Excel.Application
            xlsApplication.DisplayAlerts = False   '保存時の確認ダイアログを表示しない

xlsBooks = xlsApplication.Workbooks
            xlsBook = xlsBooks.Add
            xlsSheets = xlsBook.Worksheets

'（注意）シートのインデックスは１から始まる
            xlsSheet = DirectCast(xlsSheets.Item(1), Excel.Worksheet)
            xlsSheet.Name = sheetName

For i As Integer = 0 To headers.Count - 1
                xlsRange = DirectCast(xlsSheet.Cells(1, i + 1), Excel.Range)
                xlsRange.Value = headers.Item(i)
            Next

' セルに値を設定する。
            Dim sheetRowIndex As Integer = ROW_OFFSET

For Each row As DataRow In dt.Rows
                Dim sheetColumnIndex As Integer = COLUMN_OFFSET

For Each column As DataColumn In dt.Columns

If Not row.IsNull(column) Then
                        xlsRange = DirectCast(xlsSheet.Cells(sheetRowIndex, sheetColumnIndex), Excel.Range)

If column.DataType.Name = "Integer" Or _
                           column.DataType.Name = "Int32" Or _
                           column.DataType.Name = "Decimal" Or _
                            column.DataType.Name = "Long" Or _
                            column.DataType.Name = "Double" Or _
                            column.DataType.Name = "Short" Then
                            'セルの書式を数値型に設定
                            xlsRange.NumberFormatLocal = "G/標準"
                        ElseIf column.DataType.Name = "DateTime" Then
                             xlsRange.NumberFormatLocal = "yyyy/m/d h:mm"
                        Else
                            'セルの書式を文字列型に設定
                            xlsRange.NumberFormatLocal = "@"
                        End If

xlsRange.Value = row(column)
                        ReleaseComObject(DirectCast(xlsRange, Object))
                        sheetColumnIndex += 1
                    End If
                Next
                sheetRowIndex += 1
            Next

' 保存
            xlsBook.SaveAs(filePath)

Return True

Catch ex As Exception
            Return False
        Finally
            'エクセル関係のオブジェクトは必ず解放すること
            ReleaseComObject(DirectCast(xlsRange, Object))
            ReleaseComObject(DirectCast(xlsSheet, Object))
            ReleaseComObject(DirectCast(xlsSheets, Object))
            xlsBook.Close(False)
            ReleaseComObject(DirectCast(xlsBook, Object))
            ReleaseComObject(DirectCast(xlsBooks, Object))
            xlsApplication.Quit()
            ReleaseComObject(DirectCast(xlsApplication, Object))
        End Try

Return False
    End Function

''' <summary>
    ''' COMオブジェクトを開放します。
    ''' </summary>
    Private Shared Sub ReleaseComObject(ByRef target As Object)
        Try
            If Not target Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(target)
            End If
        Finally
            target = Nothing
        End Try
    End Sub

トラックバック

トラックバックURL：
http://www.apricot-jp.com/cgi/mt/mt-tb.cgi/298

アーキテクチャ,フレームワーク,設計のTips

データテーブルをExcelに出力するサンプルコード

トラックバック

関連情報