[方法] スプレッドシート ドキュメント内のセルにテキストを挿入する (Open XML SDK)How to: Insert text into a cell in a spreadsheet document (Open XML SDK)

このトピックでは、Open XML SDK 2.5 for Office を使用して、プログラムによってスプレッドシート ドキュメントの新しいワークシート内のセルにテキストを挿入する方法について説明します。This topic shows how to use the classes in the Open XML SDK 2.5 for Office to insert text into a cell in a new worksheet in a spreadsheet document programmatically.

このトピックのコードをコンパイルするには、次のアセンブリ ディレクティブが必要です。The following assembly directives are required to compile the code in this topic:

    using System.Linq;
    using DocumentFormat.OpenXml;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    Imports System.Linq
    Imports DocumentFormat.OpenXml
    Imports DocumentFormat.OpenXml.Packaging
    Imports DocumentFormat.OpenXml.Spreadsheet

SpreadsheetDocument オブジェクトを取得するGetting a SpreadsheetDocument Object

Open XML SDK では、SpreadsheetDocument クラスが Excel ドキュメント パッケージを表します。Excel ドキュメントを開いて操作するには、ドキュメントから SpreadsheetDocument クラスのインスタンスを作成します。ドキュメントからインスタンスを作成した後、ワークシートが入っているメインのブック パーツにアクセスできます。パッケージ内では、 SpreadsheetML マークアップを使用してドキュメントのテキストを XML で表現します。In the Open XML SDK, the SpreadsheetDocument class represents an Excel document package. To open and work with an Excel document, you create an instance of the SpreadsheetDocument class from the document. After you create the instance from the document, you can then obtain access to the main workbook part that contains the worksheets. The text in the document is represented in the package as XML using SpreadsheetML markup.

ドキュメントからクラス インスタンスを作成するには、Open() オーバーロード メソッドのいずれかを呼び出します。To create the class instance from the document that you call one of the Open() overload methods. メソッドにはいくつかの種類があり、それぞれシグネチャが異なります。Several are provided, each with a different signature. このトピックのサンプル コードでは、2 つのパラメーターを必要とするシグネチャを持つ Open(String, Boolean) メソッドを使用します。The sample code in this topic uses the Open(String, Boolean) method with a signature that requires two parameters. 最初のパラメーターは、開くドキュメントを表す完全なパスの文字列を受け取ります。The first parameter takes a full path string that represents the document that you want to open. 2 番目のパラメーターは true または false で、開いたファイルを編集するかどうかを指定します。The second parameter is either true or false and represents whether you want the file to be opened for editing. このパラメーターを false に指定した場合は、ドキュメントに対する変更が保存されません。Any changes that you make to the document will not be saved if this parameter is false.

Open メソッドを呼び出すコード、次の using ステートメントに示します。The code that calls the Open method is shown in the following using statement.

    // Open the document for editing.
    using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true)) 
    {
        // Insert other code here.
    }
    ' Open the document for editing.
    Using spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)
        ' Insert other code here.
    End Using

using ステートメントは、一般的な .Open、.Save、.Close シーケンスに代わる手段として推奨されます。このステートメントでは、閉じかっこに達すると、Dispose メソッド (リソースをクリーンアップする Open XML SDK の内部メ ソッド) が自動的に呼び出されます。using ステートメントに続くブロックは、using ステートメントで作成または指定されたオブジェクト (この場合は "spreadSheet") のスコープを設定します。The **using statement provides a recommended alternative to the typical .Open, .Save, .Close sequence. It ensures that the Dispose method (internal method used by the Open XML SDK to clean up resources) is automatically called when the closing brace is reached. The block that follows the using statement establishes a scope for the object that is created or named in the using statement, in this case spreadSheet.


SpreadsheetML ドキュメントの基本構造The Basic Structure of a SpreadsheetML Document

SpreadsheetML ドキュメントの基本構造は、Sheets 要素と Sheet 要素で構成されます。これらの要素は、ブック内のワークシートを参照します。The basic document structure of a SpreadsheetML document consists of the Sheets and Sheet elements, which reference the worksheets in the workbook. ワークシートごとに、それぞれの XML ファイルが作成されます。A separate XML file is created for each Worksheet. たとえば、MySheet1 と MySheet2 という名前の 2 つのワークシートがあるブックSpreadsheetML は Workbook.xml ファイル内にあり、次のコード例のように示されます。For example, the SpreadsheetML for a Workbook that has two worksheets name MySheet1 and MySheet2 is located in the Workbook.xml file and is shown in the following code example.

    <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
    <workbook xmlns=http://schemas.openxmlformats.org/spreadsheetml/2006/main xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
        <sheets>
            <sheet name="MySheet1" sheetId="1" r:id="rId1" /> 
            <sheet name="MySheet2" sheetId="2" r:id="rId2" /> 
        </sheets>
    </workbook>

ワークシート XML ファイルには、1 つ以上のブロック レベル要素 (SheetData など) が含まれます。The worksheet XML files contain one or more block level elements such as SheetData. sheetData はセルのテーブルを表しており、1 つ以上の Row 要素が含まれます。sheetData represents the cell table and contains one or more Row elements. row には、1 つ以上の Cell 要素が含まれます。A row contains one or more Cell elements. セルにはそれぞれ、セルの値を表す CellValue 要素が含まれています。Each cell contains a CellValue element that represents the value of the cell. たとえば、ブックにある最初のワークシートの SpreadsheetML が Sheet1.xml ファイル内に存在するとします。セル A1 に値 100 のみがある場合、SpreadsheetML は次のコード例のように示されます。For example, the SpreadsheetML for the first worksheet in a workbook, that only has the value 100 in cell A1, is located in the Sheet1.xml file and is shown in the following code example.

    <?xml version="1.0" encoding="UTF-8" ?> 
    <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <sheetData>
            <row r="1">
                <c r="A1">
                    <v>100</v> 
                </c>
            </row>
        </sheetData>
    </worksheet>

Open XML SDK 2.5 を使用すると、 SpreadsheetML 要素に対応する厳密に型指定されたクラスを使用してドキュメント構造とコンテンツを作成できます。これらのクラスは DocumentFormat.OpenXML.Spreadsheet 名前空間にあります。次の表に、 workbooksheetssheetworksheet、および sheetData の各要素に対応するクラスのクラス名を示します。Using the Open XML SDK 2.5, you can create document structure and content that uses strongly-typed classes that correspond to SpreadsheetML elements. You can find these classes in the DocumentFormat.OpenXML.Spreadsheet namespace. The following table lists the class names of the classes that correspond to the workbook, sheets, sheet, worksheet, and sheetData elements.

SpreadsheetML の要素SpreadsheetML Element Open XML SDK 2.5 のクラスOpen XML SDK 2.5 Class 説明Description
ブックworkbook DocumentFormat.OpenXml.Spreadsheet.WorkbookDocumentFormat.OpenXml.Spreadsheet.Workbook メイン ドキュメント パーツのルート要素。The root element for the main document part.
sheetssheets DocumentFormat.OpenXml.Spreadsheet.SheetsDocumentFormat.OpenXml.Spreadsheet.Sheets ISO/IEC 29500 の仕様で規定されている、シート、ファイル バージョン、その他のブロック レベル構造のコンテナー。The container for the block level structures such as sheet, fileVersion, and others specified in the ISO/IEC 29500 specification.
sheetsheet DocumentFormat.OpenXml.Spreadsheet.SheetDocumentFormat.OpenXml.Spreadsheet.Sheet シート定義ファイルを指し示すシート。A sheet that points to a sheet definition file.
ワークシートworksheet DocumentFormat.OpenXml.Spreadsheet.WorksheetDocumentFormat.OpenXml.Spreadsheet.Worksheet シート データが含まれているシート定義ファイル。A sheet definition file that contains the sheet data.
sheetDatasheetData DocumentFormat.OpenXml.Spreadsheet.SheetDataDocumentFormat.OpenXml.Spreadsheet.SheetData セルの表。行ごとにグループ化されています。The cell table, grouped together by rows.
rowrow DocumentFormat.OpenXml.Spreadsheet.RowDocumentFormat.OpenXml.Spreadsheet.Row セルの表内の行。A row in the cell table.
cc DocumentFormat.OpenXml.Spreadsheet.CellDocumentFormat.OpenXml.Spreadsheet.Cell 行内のセル。A cell in a row.
vv DocumentFormat.OpenXml.Spreadsheet.CellValueDocumentFormat.OpenXml.Spreadsheet.CellValue セルの値。The value of a cell.

サンプル コードの動作のしくみHow the Sample Code Works

SpreadsheetDocument ドキュメントを編集のために開くと、コードにより、空白の Worksheet オブジェクトが SpreadsheetDocument ドキュメント パッケージに挿入されます。After opening the SpreadsheetDocument document for editing, the code inserts a blank Worksheet object into a SpreadsheetDocument document package. 次に、新しい Cell オブジェクトが新しいワークシートに挿入され、指定したテキストがそのセルに挿入されます。Then, inserts a new Cell object into the new worksheet and inserts the specified text into that cell.

    // Given a document name and text, 
    // inserts a new worksheet and writes the text to cell "A1" of the new worksheet.
    public static void InsertText(string docName, string text)
    {
        // Open the document for editing.
        using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
        {
            // Get the SharedStringTablePart. If it does not exist, create a new one.
            SharedStringTablePart shareStringPart;
            if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
            {
                shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
            }
            else
            {
                shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
            }

            // Insert the text into the SharedStringTablePart.
            int index = InsertSharedStringItem(text, shareStringPart);

            // Insert a new worksheet.
            WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);

            // Insert cell A1 into the new worksheet.
            Cell cell = InsertCellInWorksheet("A", 1, worksheetPart);

            // Set the value of cell A1.
            cell.CellValue = new CellValue(index.ToString());
            cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

            // Save the new worksheet.
            worksheetPart.Worksheet.Save();
        }
    }
    ' Given a document name and text, 
    ' inserts a new worksheet and writes the text to cell "A1" of the new worksheet.
    Public Function InsertText(ByVal docName As String, ByVal text As String)
        ' Open the document for editing.
        Dim spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)

        Imports (spreadSheet)
            ' Get the SharedStringTablePart. If it does not exist, create a new one.
            Dim shareStringPart As SharedStringTablePart

            If (spreadSheet.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).Count() > 0) Then
                shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).First()
            Else
                shareStringPart = spreadSheet.WorkbookPart.AddNewPart(Of SharedStringTablePart)()
            End If

            ' Insert the text into the SharedStringTablePart.
            Dim index As Integer = InsertSharedStringItem(text, shareStringPart)

            ' Insert a new worksheet.
            Dim worksheetPart As WorksheetPart = InsertWorksheet(spreadSheet.WorkbookPart)

            ' Insert cell A1 into the new worksheet.
            Dim cell As Cell = InsertCellInWorksheet("A", 1, worksheetPart)

            ' Set the value of cell A1.
            cell.CellValue = New CellValue(index.ToString)
            cell.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)

            ' Save the new worksheet.
            worksheetPart.Worksheet.Save()

            Return 0
        End Imports
    End Function

スプレッドシートには、セルに挿入するテキストを表すパラメーターと、SharedStringTablePart オブジェクトを表すパラメーターが渡されます。The code passes in a parameter that represents the text to insert into the cell and a parameter that represents the SharedStringTablePart object for the spreadsheet. ShareStringTablePart オブジェクトに SharedStringTable オブジェクトが含まれていない場合、コードによって作成されます。If the ShareStringTablePart object does not contain a SharedStringTable object, the code creates one. ShareStringTable オブジェクトにテキストが既に存在する場合、コードによって、そのテキストを表す SharedStringItem オブジェクトのインデックスが返されます。If the text already exists in the ShareStringTable object, the code returns the index for the SharedStringItem object that represents the text. そうでない場合は、テキストを表す新しい SharedStringItem オブジェクトが作成されます。Otherwise, it creates a new SharedStringItem object that represents the text.

次のコードでは、指定されたテキストが SharedStringTablePart オブジェクト内に存在するかどうかを確認し、存在しない場合はそのテキストが追加されます。The following code verifies if the specified text exists in the SharedStringTablePart object and add the text if it does not exist.

    // Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
    // and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
    private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
    {
        // If the part does not contain a SharedStringTable, create one.
        if (shareStringPart.SharedStringTable == null)
        {
            shareStringPart.SharedStringTable = new SharedStringTable();
        }

        int i = 0;

        // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
        foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
        {
            if (item.InnerText == text)
            {
                return i;
            }

            i++;
        }

        // The text does not exist in the part. Create the SharedStringItem and return its index.
        shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
        shareStringPart.SharedStringTable.Save();

        return i;
    }
    ' Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
    ' and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
    Private Function InsertSharedStringItem(ByVal text As String, ByVal shareStringPart As SharedStringTablePart) As Integer
        ' If the part does not contain a SharedStringTable, create one.
        If (shareStringPart.SharedStringTable Is Nothing) Then
            shareStringPart.SharedStringTable = New SharedStringTable
        End If

        Dim i As Integer = 0

        ' Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
        For Each item As SharedStringItem In shareStringPart.SharedStringTable.Elements(Of SharedStringItem)()
            If (item.InnerText = text) Then
                Return i
            End If
            i = (i + 1)
        Next

        ' The text does not exist in the part. Create the SharedStringItem and return its index.
        shareStringPart.SharedStringTable.AppendChild(New SharedStringItem(New DocumentFormat.OpenXml.Spreadsheet.Text(text)))
        shareStringPart.SharedStringTable.Save()

        Return i
    End Function

コードによって、AddNewPart メソッドが使用され、新しい WorksheetPart オブジェクトが WorkbookPart オブジェクトに追加されます。The code adds a new WorksheetPart object to the WorkbookPart object by using the AddNewPart method. 次に、新しい Worksheet オブジェクトが WorksheetPart オブジェクトに追加され、新規ワークシートの一意の ID が取得されます。これを行うために、スプレッドシート ドキュメント内で使用する最大 SheetId オブジェクトが選択され、それが追加されて、新規シート ID が作成されます。It then adds a new Worksheet object to the WorksheetPart object, and gets a unique ID for the new worksheet by selecting the maximum SheetId object used within the spreadsheet document and adding one to create the new sheet ID. ワークシートには、「Sheet」部分にシート ID をつなげた名前が付けられます。It gives the worksheet a name by concatenating the word "Sheet" with the sheet ID. 次に、新しい Sheet オブジェクトが Sheets コレクションに追加されます。It then appends the new Sheet object to the Sheets collection.

次のコードでは、新しい WorksheetPart オブジェクトが WorkbookPart オブジェクトに追加されて、新しい Worksheet オブジェクトが挿入されます。The following code inserts a new Worksheet object by adding a new WorksheetPart object to the WorkbookPart object.

    // Given a WorkbookPart, inserts a new worksheet.
    private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
    {
        // Add a new worksheet part to the workbook.
        WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
        newWorksheetPart.Worksheet = new Worksheet(new SheetData());
        newWorksheetPart.Worksheet.Save();

        Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
        string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

        // Get a unique ID for the new sheet.
        uint sheetId = 1;
        if (sheets.Elements<Sheet>().Count() > 0)
        {
            sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
        }

        string sheetName = "Sheet" + sheetId;

        // Append the new worksheet and associate it with the workbook.
        Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
        sheets.Append(sheet);
        workbookPart.Workbook.Save();

        return newWorksheetPart;
    }
    ' Given a WorkbookPart, inserts a new worksheet.
    Private Function InsertWorksheet(ByVal workbookPart As WorkbookPart) As WorksheetPart
        ' Add a new worksheet part to the workbook.
        Dim newWorksheetPart As WorksheetPart = workbookPart.AddNewPart(Of WorksheetPart)()
        newWorksheetPart.Worksheet = New Worksheet(New SheetData)
        newWorksheetPart.Worksheet.Save()
        Dim sheets As Sheets = workbookPart.Workbook.GetFirstChild(Of Sheets)()
        Dim relationshipId As String = workbookPart.GetIdOfPart(newWorksheetPart)

        ' Get a unique ID for the new sheet.
        Dim sheetId As UInteger = 1
        If (sheets.Elements(Of Sheet).Count() > 0) Then
            sheetId = sheets.Elements(Of Sheet).Select(Function(s) s.SheetId.Value).Max() + 1
        End If

        Dim sheetName As String = ("Sheet" + sheetId.ToString())

        ' Add the new worksheet and associate it with the workbook.
        Dim sheet As Sheet = New Sheet
        sheet.Id = relationshipId
        sheet.SheetId = sheetId
        sheet.Name = sheetName
        sheets.Append(sheet)
        workbookPart.Workbook.Save()

        Return newWorksheetPart
    End Function

セルをワークシートに挿入するために、コードによって、新しいセルを列内のどこに挿入するかが決められます。これを行うために、行要素を順番に反復処理して、指定された行の直後にあるセルを見つけます。To insert a cell into a worksheet, the code determines where to insert the new cell in the column by iterating through the row elements to find the cell that comes directly after the specified row, in sequential order. その行は refCell 変数に保存されます。It saves that row in the refCell variable. 次に、InsertBefore メソッドが使用されて、refCell で参照されるセルの前に新しいセルが挿入されます。It then inserts the new cell before the cell referenced by refCell using the InsertBefore method.

次のコードでは、新しい Cell オブジェクトが Worksheet オブジェクトに挿入されます。In the following code, insert a new Cell object into a Worksheet object.

    // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
    // If the cell already exists, returns it. 
    private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
    {
        Worksheet worksheet = worksheetPart.Worksheet;
        SheetData sheetData = worksheet.GetFirstChild<SheetData>();
        string cellReference = columnName + rowIndex;

        // If the worksheet does not contain a row with the specified row index, insert one.
        Row row;
        if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
        {
            row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
        }
        else
        {
            row = new Row() { RowIndex = rowIndex };
            sheetData.Append(row);
        }

        // If there is not a cell with the specified column name, insert one.  
        if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
        {
            return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
        }
        else
        {
            // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
            Cell refCell = null;
            foreach (Cell cell in row.Elements<Cell>())
            {
                if (cell.CellReference.Value.Length == cellReference.Length)
                {
                  if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                  {
                    refCell = cell;
                    break;
                  }
                }
            }

            Cell newCell = new Cell() { CellReference = cellReference };
            row.InsertBefore(newCell, refCell);

            worksheet.Save();
            return newCell;
        }
    }
    ' Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
    ' If the cell already exists, return it. 
    Private Function InsertCellInWorksheet(ByVal columnName As String, ByVal rowIndex As UInteger, ByVal worksheetPart As WorksheetPart) As Cell
        Dim worksheet As Worksheet = worksheetPart.Worksheet
        Dim sheetData As SheetData = worksheet.GetFirstChild(Of SheetData)()
        Dim cellReference As String = (columnName + rowIndex.ToString())

        ' If the worksheet does not contain a row with the specified row index, insert one.
        Dim row As Row
        If (sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).Count() <> 0) Then
            row = sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).First()
        Else
            row = New Row()
            row.RowIndex = rowIndex
            sheetData.Append(row)
        End If

        ' If there is not a cell with the specified column name, insert one.  
        If (row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = columnName + rowIndex.ToString()).Count() > 0) Then
            Return row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = cellReference).First()
        Else
            ' Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
            Dim refCell As Cell = Nothing
            For Each cell As Cell In row.Elements(Of Cell)()
                If (String.Compare(cell.CellReference.Value, cellReference, True) > 0) Then
                    refCell = cell
                    Exit For
                End If
            Next

            Dim newCell As Cell = New Cell
            newCell.CellReference = cellReference

            row.InsertBefore(newCell, refCell)
            worksheet.Save()

            Return newCell
        End If
    End Function

サンプル コードSample Code

次のサンプル コードは、新しいワークシートを挿入し、"Sheet8.xlsx" という名前の特定のスプレッドシート ドキュメントの新しいワークシートのセル "A1" にテキストを書き込みます。 InsertText メソッドを呼び出す方法は、次の例に示すコードを参考にしてください。The following code sample is used to insert a new worksheet and write the text to the cell "A1" of the new worksheet for a specific spreadsheet document named "Sheet8.xlsx." To call the InsertText method you can use the following code as an example.

    InsertText(@"C:\Users\Public\Documents\Sheet8.xlsx", "Inserted Text");
    InsertText("C:\Users\Public\Documents\Sheet8.xlsx", "Inserted Text")

以下に、C# と Visual Basic による完全なサンプル コードを示します。The following is the complete sample code in both C# and Visual Basic.

    // Given a document name and text, 
     // inserts a new work sheet and writes the text to cell "A1" of the new worksheet.

     public static void InsertText(string docName, string text)
    {
        // Open the document for editing.
        using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
        {
            // Get the SharedStringTablePart. If it does not exist, create a new one.
            SharedStringTablePart shareStringPart;
            if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
            {
                shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
            }
            else
            {
                shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
            }

            // Insert the text into the SharedStringTablePart.
            int index = InsertSharedStringItem(text, shareStringPart);

            // Insert a new worksheet.
            WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);

            // Insert cell A1 into the new worksheet.
            Cell cell = InsertCellInWorksheet("A", 1, worksheetPart);

            // Set the value of cell A1.
            cell.CellValue = new CellValue(index.ToString());
            cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

            // Save the new worksheet.
            worksheetPart.Worksheet.Save();
        }
    }

            // Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
            // and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
            private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
            {
                // If the part does not contain a SharedStringTable, create one.
                if (shareStringPart.SharedStringTable == null)
                {
                    shareStringPart.SharedStringTable = new SharedStringTable();
                }

                int i = 0;

                // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
                foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
                {
                    if (item.InnerText == text)
                    {
                        return i;
                    }

                    i++;
                }

                // The text does not exist in the part. Create the SharedStringItem and return its index.
                shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
                shareStringPart.SharedStringTable.Save();

                return i;
            }

            // Given a WorkbookPart, inserts a new worksheet.
            private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
            {
                // Add a new worksheet part to the workbook.
                WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                newWorksheetPart.Worksheet = new Worksheet(new SheetData());
                newWorksheetPart.Worksheet.Save();

                Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
                string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

                // Get a unique ID for the new sheet.
                uint sheetId = 1;
                if (sheets.Elements<Sheet>().Count() > 0)
                {
                    sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }

                string sheetName = "Sheet" + sheetId;

                // Append the new worksheet and associate it with the workbook.
                Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                sheets.Append(sheet);
                workbookPart.Workbook.Save();

                return newWorksheetPart;
            }

            // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
            // If the cell already exists, returns it. 
            private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
            {
                Worksheet worksheet = worksheetPart.Worksheet;
                SheetData sheetData = worksheet.GetFirstChild<SheetData>();
                string cellReference = columnName + rowIndex;

                // If the worksheet does not contain a row with the specified row index, insert one.
                Row row;
                if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
                {
                    row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
                }
                else
                {
                    row = new Row() { RowIndex = rowIndex };
                    sheetData.Append(row);
                }

                // If there is not a cell with the specified column name, insert one.  
                if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
                {
                    return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
                }
                else
                {
                    // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                    Cell refCell = null;
                    foreach (Cell cell in row.Elements<Cell>())
                    {
                        if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                        {
                            refCell = cell;
                            break;
                        }
                    }

                    Cell newCell = new Cell() { CellReference = cellReference };
                    row.InsertBefore(newCell, refCell);

                    worksheet.Save();
                    return newCell;
                }
            }
    ' Given a document name and text, 
    ' inserts a new worksheet and writes the text to cell "A1" of the new worksheet.
    Public Function InsertText(ByVal docName As String, ByVal text As String)
        ' Open the document for editing.
        Dim spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)

        Using (spreadSheet)
            ' Get the SharedStringTablePart. If it does not exist, create a new one.
            Dim shareStringPart As SharedStringTablePart

            If (spreadSheet.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).Count() > 0) Then
                shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType(Of SharedStringTablePart).First()
            Else
                shareStringPart = spreadSheet.WorkbookPart.AddNewPart(Of SharedStringTablePart)()
            End If

            ' Insert the text into the SharedStringTablePart.
            Dim index As Integer = InsertSharedStringItem(text, shareStringPart)

            ' Insert a new worksheet.
            Dim worksheetPart As WorksheetPart = InsertWorksheet(spreadSheet.WorkbookPart)

            ' Insert cell A1 into the new worksheet.
            Dim cell As Cell = InsertCellInWorksheet("A", 1, worksheetPart)

            ' Set the value of cell A1.
            cell.CellValue = New CellValue(index.ToString)
            cell.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)

            ' Save the new worksheet.
            worksheetPart.Worksheet.Save()

            Return 0
        End Using
    End Function

    ' Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
    ' and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
    Private Function InsertSharedStringItem(ByVal text As String, ByVal shareStringPart As SharedStringTablePart) As Integer
        ' If the part does not contain a SharedStringTable, create one.
        If (shareStringPart.SharedStringTable Is Nothing) Then
            shareStringPart.SharedStringTable = New SharedStringTable
        End If

        Dim i As Integer = 0

        ' Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
        For Each item As SharedStringItem In shareStringPart.SharedStringTable.Elements(Of SharedStringItem)()
            If (item.InnerText = text) Then
                Return i
            End If
            i = (i + 1)
        Next

        ' The text does not exist in the part. Create the SharedStringItem and return its index.
        shareStringPart.SharedStringTable.AppendChild(New SharedStringItem(New DocumentFormat.OpenXml.Spreadsheet.Text(text)))
        shareStringPart.SharedStringTable.Save()

        Return i
    End Function

    ' Given a WorkbookPart, inserts a new worksheet.
    Private Function InsertWorksheet(ByVal workbookPart As WorkbookPart) As WorksheetPart
        ' Add a new worksheet part to the workbook.
        Dim newWorksheetPart As WorksheetPart = workbookPart.AddNewPart(Of WorksheetPart)()
        newWorksheetPart.Worksheet = New Worksheet(New SheetData)
        newWorksheetPart.Worksheet.Save()
        Dim sheets As Sheets = workbookPart.Workbook.GetFirstChild(Of Sheets)()
        Dim relationshipId As String = workbookPart.GetIdOfPart(newWorksheetPart)

        ' Get a unique ID for the new sheet.
        Dim sheetId As UInteger = 1
        If (sheets.Elements(Of Sheet).Count() > 0) Then
            sheetId = sheets.Elements(Of Sheet).Select(Function(s) s.SheetId.Value).Max() + 1
        End If

        Dim sheetName As String = ("Sheet" + sheetId.ToString())

        ' Add the new worksheet and associate it with the workbook.
        Dim sheet As Sheet = New Sheet
        sheet.Id = relationshipId
        sheet.SheetId = sheetId
        sheet.Name = sheetName
        sheets.Append(sheet)
        workbookPart.Workbook.Save()

        Return newWorksheetPart
    End Function

    ' Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
    ' If the cell already exists, return it. 
    Private Function InsertCellInWorksheet(ByVal columnName As String, ByVal rowIndex As UInteger, ByVal worksheetPart As WorksheetPart) As Cell
        Dim worksheet As Worksheet = worksheetPart.Worksheet
        Dim sheetData As SheetData = worksheet.GetFirstChild(Of SheetData)()
        Dim cellReference As String = (columnName + rowIndex.ToString())

        ' If the worksheet does not contain a row with the specified row index, insert one.
        Dim row As Row
        If (sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).Count() <> 0) Then
            row = sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).First()
        Else
            row = New Row()
            row.RowIndex = rowIndex
            sheetData.Append(row)
        End If

        ' If there is not a cell with the specified column name, insert one.  
        If (row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = columnName + rowIndex.ToString()).Count() > 0) Then
            Return row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = cellReference).First()
        Else
            ' Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
            Dim refCell As Cell = Nothing
            For Each cell As Cell In row.Elements(Of Cell)()
                If (String.Compare(cell.CellReference.Value, cellReference, True) > 0) Then
                    refCell = cell
                    Exit For
                End If
            Next

            Dim newCell As Cell = New Cell
            newCell.CellReference = cellReference

            row.InsertBefore(newCell, refCell)
            worksheet.Save()

            Return newCell
        End If
    End Function

関連項目See also

その他のリソースOther resources

Open XML SDK 2.5 クラス ライブラリ リファレンスOpen XML SDK 2.5 class library reference

統合言語クエリ (LINQ: Language-Integrated Query)Language-Integrated Query (LINQ)

ラムダ式Lambda Expressions

ラムダ式 (C# プログラミング ガイド)Lambda Expressions (C# Programming Guide)