バージョン

Excel の名前付きテーブルの作成のコード例

説明

以下のコードは、 Patients という名前の WorksheetTable を含む、Workbook オブジェクトを返します。この表はいくつかのコード例で使用されます。

コード

Visual Basic の場合:

Imports System.Collections.Generic
Imports Infragistics.Documents.Excel
Namespace ExcelDefaultFont
    Public Class ExcelExampleTable
        Public Shared Function CreateExampleWorkbook() As Workbook
            Dim workbook As Workbook = New Infragistics.Documents.Excel.Workbook(Infragistics.Documents.Excel.WorkbookFormat.Excel2007)
            Dim worksheet As Worksheet = workbook.Worksheets.Add("WorksheetSorting")
            ' Add data to worksheet for column header
            worksheet.Rows(0).Cells(0).Value = "First Name"
            worksheet.Rows(0).Cells(1).Value = "Last Name"
            worksheet.Rows(0).Cells(2).Value = "DOB"
            worksheet.Rows(0).Cells(3).Value = "YOB"
            worksheet.Rows(0).Cells(4).Value = "Acceptance Date"
            worksheet.Rows(0).Cells(5).Value = "Severity"
            worksheet.Columns(2).Width = 3000
            worksheet.Columns(4).Width = 3000
            ' Fill example data
            Dim patients As List(Of Patient) = GetExamplePatients()
            Dim currentRow As Integer = 1
            Dim worksheetRow As Infragistics.Documents.Excel.WorksheetRow
            For Each patient As Patient In patients
                Dim currentCell As Integer = 0
                worksheetRow = worksheet.Rows(currentRow)
                worksheetRow.Cells(currentCell).Value = patient.FirstName
                worksheetRow.Cells(System.Threading.Interlocked.Increment(currentCell)).Value = patient.LastName
                worksheetRow.Cells(System.Threading.Interlocked.Increment(currentCell)).Value = DateTime.Parse(patient.DOB.ToShortDateString())
                worksheetRow.Cells(System.Threading.Interlocked.Increment(currentCell)).Value = Integer.Parse(patient.DOB.Year.ToString())
                worksheetRow.Cells(System.Threading.Interlocked.Increment(currentCell)).Value = DateTime.Parse(patient.AcceptanceDate.ToShortDateString())
                worksheetRow.Cells(System.Threading.Interlocked.Increment(currentCell)).Value = patient.Severity
                currentRow += 1
            Next
            Dim region As New Infragistics.Documents.Excel.WorksheetRegion(worksheet, 0, 0, (currentRow - 1), 5)
            Dim table As Infragistics.Documents.Excel.WorksheetTable = region.FormatAsTable(True)
            table.Name = "Patients"
            Return workbook
        End Function
        Private Shared Function GetExamplePatients() As List(Of Patient)
            Dim patients As New List(Of Patient)()
            patients.Add(New Patient() With { _
             .FirstName = "John", _
             .LastName = "Rizzo", _
             .DOB = New DateTime(1964, 1, 20), _
             .AcceptanceDate = New DateTime(2012, 4, 17), _
             .Severity = "Low" _
            })
            patients.Add(New Patient() With { _
             .FirstName = "Arnie", _
             .LastName = "Smith", _
             .DOB = New DateTime(1958, 4, 7), _
             .AcceptanceDate = New DateTime(2012, 1, 8), _
             .Severity = "High" _
            })
            patients.Add(New Patient() With { _
             .FirstName = "John", _
             .LastName = "Young", _
             .DOB = New DateTime(1967, 11, 22), _
             .AcceptanceDate = New DateTime(2012, 11, 14), _
             .Severity = "High" _
            })
            patients.Add(New Patient() With { _
             .FirstName = "Jack", _
             .LastName = "Cruz", _
             .DOB = New DateTime(1981, 3, 27), _
             .AcceptanceDate = New DateTime(2012, 5, 25), _
             .Severity = "Medium" _
            })
            patients.Add(New Patient() With { _
             .FirstName = "Rose", _
             .LastName = "Burns", _
             .DOB = New DateTime(1978, 7, 7), _
             .AcceptanceDate = New DateTime(2012, 6, 2), _
             .Severity = "Low" _
            })
            patients.Add(New Patient() With { _
             .FirstName = "June", _
             .LastName = "Lewis", _
             .DOB = New DateTime(1979, 2, 1), _
             .AcceptanceDate = New DateTime(2012, 11, 2), _
             .Severity = "High" _
            })
            patients.Add(New Patient() With { _
             .FirstName = "Scott", _
             .LastName = "Jones", _
             .DOB = New DateTime(1982, 6, 5), _
             .AcceptanceDate = New DateTime(2012, 12, 6), _
             .Severity = "High" _
            })
            patients.Add(New Patient() With { _
             .FirstName = "Moses", _
             .LastName = "Perez", _
             .DOB = New DateTime(1961, 5, 26), _
             .AcceptanceDate = New DateTime(2012, 3, 26), _
             .Severity = "Low" _
            })
            patients.Add(New Patient() With { _
             .FirstName = "Duncan", _
             .LastName = "Copper", _
             .DOB = New DateTime(1961, 10, 13), _
             .AcceptanceDate = New DateTime(2012, 4, 16), _
             .Severity = "Medium" _
            })
            patients.Add(New Patient() With { _
             .FirstName = "Derek", _
             .LastName = "Pierce", _
             .DOB = New DateTime(1954, 4, 17), _
             .AcceptanceDate = New DateTime(2012, 8, 25), _
             .Severity = "Low" _
            })
            patients.Add(New Patient() With { _
             .FirstName = "Tim", _
             .LastName = "Stevens", _
             .DOB = New DateTime(1957, 10, 21), _
             .AcceptanceDate = New DateTime(2012, 2, 3), _
             .Severity = "High" _
            })
            patients.Add(New Patient() With { _
             .FirstName = "Donna", _
             .LastName = "Collins", _
             .DOB = New DateTime(1984, 8, 7), _
             .AcceptanceDate = New DateTime(2012, 9, 13), _
             .Severity = "High" _
            })
            patients.Add(New Patient() With { _
             .FirstName = "James", _
             .LastName = "Manning", _
             .DOB = New DateTime(1954, 9, 20), _
             .AcceptanceDate = New DateTime(2012, 8, 13), _
             .Severity = "Low" _
            })
            patients.Add(New Patient() With { _
             .FirstName = "Jose", _
             .LastName = "Dotel", _
             .DOB = New DateTime(1945, 10, 23), _
             .AcceptanceDate = New DateTime(2012, 4, 11), _
             .Severity = "Low" _
            })
            Return patients
        End Function
        Public Class Patient
            Public Property FirstName() As String
                Get
                    Return m_FirstName
                End Get
                Set(value As String)
                    m_FirstName = Value
                End Set
            End Property
            Private m_FirstName As String
            Public Property LastName() As String
                Get
                    Return m_LastName
                End Get
                Set(value As String)
                    m_LastName = Value
                End Set
            End Property
            Private m_LastName As String
            Public Property DOB() As DateTime
                Get
                    Return m_DOB
                End Get
                Set(value As DateTime)
                    m_DOB = Value
                End Set
            End Property
            Private m_DOB As DateTime
            Public Property AcceptanceDate() As DateTime
                Get
                    Return m_AcceptanceDate
                End Get
                Set(value As DateTime)
                    m_AcceptanceDate = Value
                End Set
            End Property
            Private m_AcceptanceDate As DateTime
            Public Property Severity() As String
                Get
                    Return m_Severity
                End Get
                Set(value As String)
                    m_Severity = Value
                End Set
            End Property
            Private m_Severity As String
        End Class
    End Class
End Namespace

C# の場合:

using System;
using System.Collections.Generic;
using Infragistics.Documents.Excel;
namespace ExcelDefaultFont
{
    public class ExcelExampleTable
    {
        public static Workbook CreateExampleWorkbook()
        {
            Workbook workbook = new Infragistics.Documents.Excel.Workbook(Infragistics.Documents.Excel.WorkbookFormat.Excel2007);
            Worksheet worksheet = workbook.Worksheets.Add("WorksheetSorting");
            // Add data to worksheet for column header
            worksheet.Rows[0].Cells[0].Value = "First Name";
            worksheet.Rows[0].Cells[1].Value = "Last Name";
            worksheet.Rows[0].Cells[2].Value = "DOB";
            worksheet.Rows[0].Cells[3].Value = "YOB";
            worksheet.Rows[0].Cells[4].Value = "Acceptance Date";
            worksheet.Rows[0].Cells[5].Value = "Severity";
            worksheet.Columns[2].Width = 3000;
            worksheet.Columns[4].Width = 3000;
            // Fill example data
            List<Patient> patients = GetExamplePatients();
            int currentRow = 1;
            Infragistics.Documents.Excel.WorksheetRow worksheetRow;
            foreach (Patient patient in patients)
            {
                int currentCell = 0;
                worksheetRow = worksheet.Rows[currentRow];
                worksheetRow.Cells[currentCell].Value = patient.FirstName;
                worksheetRow.Cells[++currentCell].Value = patient.LastName;
                worksheetRow.Cells[++currentCell].Value = DateTime.Parse(patient.DOB.ToShortDateString());
                worksheetRow.Cells[++currentCell].Value = int.Parse(patient.DOB.Year.ToString());
                worksheetRow.Cells[++currentCell].Value = DateTime.Parse(patient.AcceptanceDate.ToShortDateString());
                worksheetRow.Cells[++currentCell].Value = patient.Severity;
                currentRow++;
            }
            Infragistics.Documents.Excel.WorksheetRegion region = new Infragistics.Documents.Excel.WorksheetRegion(worksheet, 0, 0, (currentRow - 1), 5);
            Infragistics.Documents.Excel.WorksheetTable table = region.FormatAsTable(true);
            table.Name = "Patients";
            return workbook;
        }
        private static List<Patient> GetExamplePatients()
        {
            List<Patient> patients = new List<Patient>();
            patients.Add(new Patient { FirstName = "John", LastName = "Rizzo", DOB = new DateTime(1964, 1, 20), AcceptanceDate = new DateTime(2012, 4, 17), Severity = "Low" });
            patients.Add(new Patient { FirstName = "Arnie", LastName = "Smith", DOB = new DateTime(1958, 4, 7), AcceptanceDate = new DateTime(2012, 1, 8), Severity = "High" });
            patients.Add(new Patient { FirstName = "John", LastName = "Young", DOB = new DateTime(1967, 11, 22), AcceptanceDate = new DateTime(2012, 11, 14), Severity = "High" });
            patients.Add(new Patient { FirstName = "Jack", LastName = "Cruz", DOB = new DateTime(1981, 3, 27), AcceptanceDate = new DateTime(2012, 5, 25), Severity = "Medium" });
            patients.Add(new Patient { FirstName = "Rose", LastName = "Burns", DOB = new DateTime(1978, 7, 7), AcceptanceDate = new DateTime(2012, 6, 2), Severity = "Low" });
            patients.Add(new Patient { FirstName = "June", LastName = "Lewis", DOB = new DateTime(1979, 2, 1), AcceptanceDate = new DateTime(2012, 11, 2), Severity = "High" });
            patients.Add(new Patient { FirstName = "Scott", LastName = "Jones", DOB = new DateTime(1982, 6, 5), AcceptanceDate = new DateTime(2012, 12, 6), Severity = "High" });
            patients.Add(new Patient { FirstName = "Moses", LastName = "Perez", DOB = new DateTime(1961, 5, 26), AcceptanceDate = new DateTime(2012, 3, 26), Severity = "Low" });
            patients.Add(new Patient { FirstName = "Duncan", LastName = "Copper", DOB = new DateTime(1961, 10, 13), AcceptanceDate = new DateTime(2012, 4, 16), Severity = "Medium" });
            patients.Add(new Patient { FirstName = "Derek", LastName = "Pierce", DOB = new DateTime(1954, 4, 17), AcceptanceDate = new DateTime(2012, 8, 25), Severity = "Low" });
            patients.Add(new Patient { FirstName = "Tim", LastName = "Stevens", DOB = new DateTime(1957, 10, 21), AcceptanceDate = new DateTime(2012, 2, 3), Severity = "High" });
            patients.Add(new Patient { FirstName = "Donna", LastName = "Collins", DOB = new DateTime(1984, 8, 7), AcceptanceDate = new DateTime(2012, 9, 13), Severity = "High" });
            patients.Add(new Patient { FirstName = "James", LastName = "Manning", DOB = new DateTime(1954, 9, 20), AcceptanceDate = new DateTime(2012, 8, 13), Severity = "Low" });
            patients.Add(new Patient { FirstName = "Jose", LastName = "Dotel", DOB = new DateTime(1945, 10, 23), AcceptanceDate = new DateTime(2012, 4, 11), Severity = "Low" });
            return patients;
        }
        public class Patient
        {
            public string FirstName { get; set; }
            public string LastName { get; set; }
            public DateTime DOB { get; set; }
            public DateTime AcceptanceDate { get; set; }
            public string Severity { get; set; }
        }
    }
}

関連コンテンツ

トピック

以下のトピックでは、このトピックに関連する情報を提供しています。

トピック 目的

このセクションでは、Infragistics Excel Engine が提供する様々な特徴と機能を効果的に使用するための、タスク ベースの重要な情報について説明します。

このトピックは、テーブルで列をフィルターする方法を説明します。テーブルの列は、WorksheetTableColumn の Appy…Filter メソッドの 1 つを呼び出すことによってフィルターできます。

このトピックは、テーブルで列を並べ替える方法を説明します。テーブルの列は、並べ替え条件を WorksheetTableColumn.SortCondition プロパティに適用することで並べ替えることができます。