DataGridView EXCEL読込(C#/VB.NET) [サンプルソース] [ヨーキー景吾の逃走]

トップ > サンプルソース > DataGridView EXCEL読込(C#/VB.NET)

DataGridView EXCEL読込(C#/VB.NET)

2010年02月12日

DataGridViewにEXCELファイルを読込(入力)するサンプル(ソース/コード)です。こちらの DataGridViewに便利なメソッドを追加するサンプルにメソッドを追加してお試し頂けます。少し修正すればこのサンプルだけで使用出来ます。

DataGridViewに便利なメソッドを追加するその他のサンプルはこちらです。
DataGridView EXCEL出力(C#/VB.NET)
DataGridView CSV読込(C#/VB.NET)
DataGridView CSV出力(C#/VB.NET)
DataGridView HTML出力(C#/VB.NET)
DataGridView その他のサンプルソース


' -----------------------------------------------------------
' DataGridViewにEXCELを読込するサンプル(VB.NET/VS2005)
' Excelを参照設定する必要があります
' [参照の追加],[COM],[Microsoft Excel *.* Object Library]
' Imports Microsoft.Office.Interop (必要)
' Imports System.Runtime.InteropServices (必要)
Public Sub ReadExcel(ByVal fp As String)
    ' EXCEL起動
    Dim objExcel As Excel.Application = Nothing
    Dim objWorkBook As Excel.Workbook = Nothing
    Dim objWorkSheet As Excel.Worksheet = Nothing
    objExcel = New Excel.Application
    objWorkBook = objExcel.Workbooks.Open(fp)
    objWorkSheet = objWorkBook.Sheets(1)

    ' EXCELの各セルの値をDataGridViewに表示
    Dim v As Object
    Dim r As Integer = 0
    Me.Rows.Clear()
    Do
        Me.Rows.Add()
        Dim blnNew As Boolean = False
        For c As Integer = 0 To Me.Columns.Count - 1
            v = objWorkSheet.Cells( _
                r + 1, c + 1).Value
            If v Is Nothing = False Then
                blnNew = True
                Me.Rows(r).Cells(c).Value = _
                    v.ToString()
            End If
        Next
        If blnNew = False Then
            Me.Rows.RemoveAt(r)
            Exit Do
        End If
        r += 1
    Loop While (True)

    ' EXCEL解放
    objWorkBook.Saved = True
    objWorkBook.Close(False)
    objExcel.Quit()
    Marshal.ReleaseComObject(objWorkSheet)
    Marshal.ReleaseComObject(objWorkBook)
    Marshal.ReleaseComObject(objExcel)
    objWorkSheet = Nothing
    objWorkBook = Nothing
    objExcel = Nothing
End Sub
' -----------------------------------------------------------

// ----------------------------------------------------------
// DataGridViewにEXCELを読込するサンプル(C#.NET/VS2005)
// Excelを参照設定する必要があります
// [参照の追加],[COM],[Microsoft Excel *.* Object Library] 
// using Excel = Microsoft.Office.Interop.Excel; (必要) 
// using System.Runtime.InteropServices; (必要) 
public void ReadExcel(String fp)
{
    // EXCEL起動＆表示
    Excel.Application objExcel = null;
    Excel.Workbook objWorkBook = null;
    Excel.Worksheet objWorkSheet = null;
    objExcel = new Excel.Application();
    objWorkBook = objExcel.Workbooks.Open(fp,
        Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing); 
    objWorkSheet = (Excel.Worksheet)objWorkBook.Sheets[1];

    // EXCELの各セルの値をDataGridViewに表示
    Object v;
    int r = 0;
    this.Rows.Clear();
    do{
        this.Rows.Add();
        Boolean blnNew = false;
        for (int c = 0; c <= this.Columns.Count - 1; c++)
        {
            v = ((Excel.Range)objWorkSheet.Cells[
                r + 1, c + 1]).Value2;
            if (v != null)
            {
                blnNew = true;
                this.Rows[r].Cells[c].Value = v.ToString();
            }
        }
        if (blnNew == false)
        {
            this.Rows.RemoveAt(r);
           break;
        }
        r += 1;
    }while(true);

    // EXCEL解放
    objWorkBook.Saved = true;
    objWorkBook.Close(false, Type.Missing, Type.Missing);
    objExcel.Quit();
    Marshal.ReleaseComObject(objWorkSheet);
    Marshal.ReleaseComObject(objWorkBook);
    Marshal.ReleaseComObject(objExcel);
    objWorkSheet = null;
    objWorkBook = null;
    objExcel = null;
}
// ----------------------------------------------------------