[方法] スプレッドシート ドキュメントのセルからテキストを削除する (Open XML SDK)How to: Delete text from 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 delete text from a cell in a spreadsheet document programmatically.

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

    using System.Collections.Generic;
    using System.Linq;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    Imports System.Collections.Generic
    Imports System.Linq
    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, call one of the Open() 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 following using statement code example calls the Open method.

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

using ステートメントは、典型的な .Open、.Save、.Close シーケンスの代わりに使える推奨手段です。これを使用すると、対応する右かっこに達したとき、Dispose メソッド (Open XML SDK でリソースのクリーンアップに使われる内部メソッド) が自動的に呼び出されます。using ステートメントに続くブロックが、using ステートメントで作成または指定したオブジェクト (このケースでは document) のスコープとして設定されます。The **using statement provides a recommended alternative to the typical .Open, .Save, .Close sequence. It verifies 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 document.


SpreadsheetML ドキュメントの基本構造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 ファイルには、SheetData などのブロック レベル要素が 1 つ以上含まれています。セルの表を表す sheetData には、Row 要素が 1 つ以上含まれています。The worksheet XML files contain one or more block level elements such as 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 文書パッケージのセルからテキストを削除します。In the following code example, you delete text from a cell in a SpreadsheetDocument document package. 次に、その行から削除されたテキストがこのスプレッドシート文書内の他のセルからまだ参照されているかどうかを確認し、参照がないなら、Remove メソッドを使用して SharedStringTablePart オブジェクトからそのテキストを削除します。Then, you verify if other cells within the spreadsheet document still reference the text removed from the row, and if they do not, you remove the text from the SharedStringTablePart object by using the Remove method. 次に、RemoveSharedStringItem メソッドを呼び出すことにより、SharedStringTablePart オブジェクトをクリーンアップします。Then you clean up the SharedStringTablePart object by calling the RemoveSharedStringItem method.

    // Given a document, a worksheet name, a column name, and a one-based row index,
    // deletes the text from the cell at the specified column and row on the specified worksheet.
    public static void DeleteTextFromCell(string docName, string sheetName, string colName, uint rowIndex)
    {
        // Open the document for editing.
        using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
        {
            IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);
            if (sheets.Count() == 0)
            {
                // The specified worksheet does not exist.
                return;
            }
            string relationshipId = sheets.First().Id.Value;
            WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);

            // Get the cell at the specified column and row.
            Cell cell = GetSpreadsheetCell(worksheetPart.Worksheet, colName, rowIndex);
            if (cell == null)
            {
                // The specified cell does not exist.
                return;
            }
            cell.Remove();
            worksheetPart.Worksheet.Save();
        }
    }
    ' Given a document, a worksheet name, a column name, and a one-based row index,
    ' deletes the text from the cell at the specified column and row on the specified worksheet.
    Public Shared Sub DeleteTextFromCell(ByVal docName As String, ByVal sheetName As String, ByVal colName As String, ByVal rowIndex As UInteger)
        ' Open the document for editing.
        Using document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)
            Dim sheets As IEnumerable(Of Sheet) = document.WorkbookPart.Workbook.GetFirstChild(Of Sheets)().Elements(Of Sheet)().Where(Function(s) s.Name = sheetName)
            If sheets.Count() = 0 Then
                ' The specified worksheet does not exist.
                Return
            End If
            Dim relationshipId As String = sheets.First().Id.Value
            Dim worksheetPart As WorksheetPart = CType(document.WorkbookPart.GetPartById(relationshipId), WorksheetPart)

            ' Get the cell at the specified column and row.
            Dim cell As Cell = GetSpreadsheetCell(worksheetPart.Worksheet, colName, rowIndex)
            If cell Is Nothing Then
                ' The specified cell does not exist.
                Return
            End If

            cell.Remove()
            worksheetPart.Worksheet.Save()

        End Using
    End Sub

次のコード例では、列名と行インデックスで指定したセルが存在するかどうかを確認します。存在する場合は、そのセルを返します。それ以外の場合は null を返します。In the following code example, you verify that the cell specified by the column name and row index exists. If so, the code returns the cell; otherwise, it returns null.

    // Given a worksheet, a column name, and a row index, gets the cell at the specified column and row.
    private static Cell GetSpreadsheetCell(Worksheet worksheet, string columnName, uint rowIndex)
    {
        IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex);
        if (rows.Count() == 0)
        {
            // A cell does not exist at the specified row.
            return null;
        }

        IEnumerable<Cell> cells = rows.First().Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0);
        if (cells.Count() == 0)
        {
            // A cell does not exist at the specified column, in the specified row.
            return null;
        }

        return cells.First();
    }
    ' Given a worksheet, a column name, and a row index, gets the cell at the specified column and row.
    Private Shared Function GetSpreadsheetCell(ByVal worksheet As Worksheet, ByVal columnName As String, ByVal rowIndex As UInteger) As Cell
        Dim rows As IEnumerable(Of Row) = worksheet.GetFirstChild(Of SheetData)().Elements(Of Row)().Where(Function(r) r.RowIndex = rowIndex)
        If rows.Count() = 0 Then
            ' A cell does not exist at the specified row.
            Return Nothing
        End If

        Dim cells As IEnumerable(Of Cell) = rows.First().Elements(Of Cell)().Where(Function(c) String.Compare(c.CellReference.Value, columnName & rowIndex, True) = 0)
        If cells.Count() = 0 Then
            ' A cell does not exist at the specified column, in the specified row.
            Return Nothing
        End If

        Return cells.First()
    End Function

次のサンプル コードでは、スプレッドシート文書内の他のセルが、shareStringId パラメーターで指定されたテキストを参照しているかどうかを確認します。In the following code example, you verify if other cells within the spreadsheet document reference the text specified by the shareStringId parameter. そのテキストが参照されていない場合、それを SharedStringTablePart オブジェクトから削除します。If they do not reference the text, you remove it from the SharedStringTablePart object. そのためには、削除するテキストの ID を表すパラメーターと、 SpreadsheetDocument ドキュメント パッケージを表すパラメーターを渡します。You do that by passing a parameter that represents the ID of the text to remove and a parameter that represents the SpreadsheetDocument document package. 次に、各 Worksheet オブジェクトに対して反復処理を行い、各 Cell オブジェクトの内容と、共有文字列 ID とを比較します。Then you iterate through each Worksheet object and compare the contents of each Cell object to the shared string ID. スプレッドシート文書内の他のセルがその SharedStringItem オブジェクトをまだ参照している場合は、SharedStringTablePart オブジェクトからその項目を削除しません。If other cells within the spreadsheet document still reference the SharedStringItem object, you do not remove the item from the SharedStringTablePart object. スプレッドシート文書内の他のセルがその SharedStringItem オブジェクトを参照していない場合は、SharedStringTablePart オブジェクトからその項目を削除します。If other cells within the spreadsheet document no longer reference the SharedStringItem object, you remove the item from the SharedStringTablePart object. 次に、各 Worksheet オブジェクトおよび Cell オブジェクトに対する反復処理を行い、共有文字列の参照を更新します。Then you iterate through each Worksheet object and Cell object and refresh the shared string references. 最後に、ワークシートと SharedStringTable オブジェクトを保存します。Finally, you save the worksheet and the SharedStringTable object.

    // Given a shared string ID and a SpreadsheetDocument, verifies that other cells in the document no longer 
    // reference the specified SharedStringItem and removes the item.
    private static void RemoveSharedStringItem(int shareStringId, SpreadsheetDocument document)
    {
        bool remove = true;

        foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())
        {
            Worksheet worksheet = part.Worksheet;
            foreach (var cell in worksheet.GetFirstChild<SheetData>().Descendants<Cell>())
            {
                // Verify if other cells in the document reference the item.
                if (cell.DataType != null &&
                    cell.DataType.Value == CellValues.SharedString &&
                    cell.CellValue.Text == shareStringId.ToString())
                {
                    // Other cells in the document still reference the item. Do not remove the item.
                    remove = false;
                    break;
                }
            }

            if (!remove)
            {
                break;
            }
        }

        // Other cells in the document do not reference the item. Remove the item.
        if (remove)
        {
            SharedStringTablePart shareStringTablePart = document.WorkbookPart.SharedStringTablePart;
            if (shareStringTablePart == null)
            {
                return;
            }

            SharedStringItem item = shareStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(shareStringId);
            if (item != null)
            {
                item.Remove();

                // Refresh all the shared string references.
                foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())
                {
                    Worksheet worksheet = part.Worksheet;
                    foreach (var cell in worksheet.GetFirstChild<SheetData>().Descendants<Cell>())
                    {
                        if (cell.DataType != null &&
                            cell.DataType.Value == CellValues.SharedString)
                        {
                            int itemIndex = int.Parse(cell.CellValue.Text);
                            if (itemIndex > shareStringId)
                            {
                                cell.CellValue.Text = (itemIndex - 1).ToString();
                            }
                        }
                    }
                    worksheet.Save();
                }

                document.WorkbookPart.SharedStringTablePart.SharedStringTable.Save();
            }
        }
    }
    ' Given a shared string ID and a SpreadsheetDocument, verifies that other cells in the document no longer 
    ' reference the specified SharedStringItem and removes the item.
    Private Shared Sub RemoveSharedStringItem(ByVal shareStringId As Integer, ByVal document As SpreadsheetDocument)
        Dim remove As Boolean = True

        For Each part In document.WorkbookPart.GetPartsOfType(Of WorksheetPart)()
            Dim worksheet As Worksheet = part.Worksheet
            For Each cell In worksheet.GetFirstChild(Of SheetData)().Descendants(Of Cell)()
                ' Verify if other cells in the document reference the item.
                If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString AndAlso cell.CellValue.Text = shareStringId.ToString() Then
                    ' Other cells in the document still reference the item. Do not remove the item.
                    remove = False
                    Exit For
                End If
            Next cell

            If Not remove Then
                Exit For
            End If
        Next part

        ' Other cells in the document do not reference the item. Remove the item.
        If remove Then
            Dim shareStringTablePart As SharedStringTablePart = document.WorkbookPart.SharedStringTablePart
            If shareStringTablePart Is Nothing Then
                Return
            End If

            Dim item As SharedStringItem = shareStringTablePart.SharedStringTable.Elements(Of SharedStringItem)().ElementAt(shareStringId)
            If item IsNot Nothing Then
                item.Remove()

                ' Refresh all the shared string references.
                For Each part In document.WorkbookPart.GetPartsOfType(Of WorksheetPart)()
                    Dim worksheet As Worksheet = part.Worksheet
                    For Each cell In worksheet.GetFirstChild(Of SheetData)().Descendants(Of Cell)()
                        If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then
                            Dim itemIndex As Integer = Integer.Parse(cell.CellValue.Text)
                            If itemIndex > shareStringId Then
                                cell.CellValue.Text = (itemIndex - 1).ToString()
                            End If
                        End If
                    Next cell
                    worksheet.Save()
                Next part

                document.WorkbookPart.SharedStringTablePart.SharedStringTable.Save()
            End If
        End If
    End Sub

サンプル コードSample Code

次のコード サンプルでは、スプレッドシート ドキュメントの特定のセルからテキストを削除します。次の例で示すように、"Sheet3.xlsx" ファイルから DeleteTextFromCell メソッドを呼び出すことで、プログラムを実行できます。このとき、行 2、列 B、およびワークシートの名前を指定します。The following code sample is used to delete text from a specific cell in a spreadsheet document. You can run the program by calling the method DeleteTextFromCell from the file "Sheet3.xlsx" as shown in the following example, where you specify row 2, column B, and the name of the worksheet.

    string docName = @"C:\Users\Public\Documents\Sheet3.xlsx";
    string sheetName  = "Jane";
    string colName = "B";
    uint rowIndex = 2;
    DeleteTextFromCell( docName,  sheetName,  colName, rowIndex);
    Dim docName As String = "C:\Users\Public\Documents\Sheet3.xlsx"
    Dim sheetName As String = "Jane"
    Dim colName As String = "B"
    Dim rowIndex As UInteger = 2
    DeleteTextFromCell(docName, sheetName, colName, rowIndex)

以下に、C# および Visual Basic の完全なコード サンプルです。The following is the complete code sample in both C# and Visual Basic.

    // Given a document, a worksheet name, a column name, and a one-based row index,
    // deletes the text from the cell at the specified column and row on the specified worksheet.
    public static void DeleteTextFromCell(string docName, string sheetName, string colName, uint rowIndex)
    {
        // Open the document for editing.
        using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
        {
            IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);
            if (sheets.Count() == 0)
            {
                // The specified worksheet does not exist.
                return;
            }
            string relationshipId = sheets.First().Id.Value;
            WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);

            // Get the cell at the specified column and row.
            Cell cell = GetSpreadsheetCell(worksheetPart.Worksheet, colName, rowIndex);
            if (cell == null)
            {
                // The specified cell does not exist.
                return;
            }

            cell.Remove();
            worksheetPart.Worksheet.Save();
        }
    }

    // Given a worksheet, a column name, and a row index, gets the cell at the specified column and row.
    private static Cell GetSpreadsheetCell(Worksheet worksheet, string columnName, uint rowIndex)
    {
        IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex);
        if (rows.Count() == 0)
        {
            // A cell does not exist at the specified row.
            return null;
        }

        IEnumerable<Cell> cells = rows.First().Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0);
        if (cells.Count() == 0)
        {
            // A cell does not exist at the specified column, in the specified row.
            return null;
        }

        return cells.First();
    }

    // Given a shared string ID and a SpreadsheetDocument, verifies that other cells in the document no longer 
    // reference the specified SharedStringItem and removes the item.
    private static void RemoveSharedStringItem(int shareStringId, SpreadsheetDocument document)
    {
        bool remove = true;

        foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())
        {
            Worksheet worksheet = part.Worksheet;
            foreach (var cell in worksheet.GetFirstChild<SheetData>().Descendants<Cell>())
            {
                // Verify if other cells in the document reference the item.
                if (cell.DataType != null &&
                    cell.DataType.Value == CellValues.SharedString &&
                    cell.CellValue.Text == shareStringId.ToString())
                {
                    // Other cells in the document still reference the item. Do not remove the item.
                    remove = false;
                    break;
                }
            }

            if (!remove)
            {
                break;
            }
        }

        // Other cells in the document do not reference the item. Remove the item.
        if (remove)
        {
            SharedStringTablePart shareStringTablePart = document.WorkbookPart.SharedStringTablePart;
            if (shareStringTablePart == null)
            {
                return;
            }

            SharedStringItem item = shareStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(shareStringId);
            if (item != null)
            {
                item.Remove();

                // Refresh all the shared string references.
                foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())
                {
                    Worksheet worksheet = part.Worksheet;
                    foreach (var cell in worksheet.GetFirstChild<SheetData>().Descendants<Cell>())
                    {
                        if (cell.DataType != null &&
                            cell.DataType.Value == CellValues.SharedString)
                        {
                            int itemIndex = int.Parse(cell.CellValue.Text);
                            if (itemIndex > shareStringId)
                            {
                                cell.CellValue.Text = (itemIndex - 1).ToString();
                            }
                        }
                    }
                    worksheet.Save();
                }

                document.WorkbookPart.SharedStringTablePart.SharedStringTable.Save();
            }
        }
    }
    ' Given a document, a worksheet name, a column name, and a one-based row index,
    ' deletes the text from the cell at the specified column and row on the specified sheet.
    Public Sub DeleteTextFromCell(ByVal docName As String, ByVal sheetName As String, ByVal colName As String, ByVal rowIndex As UInteger)
        ' Open the document for editing.
        Dim document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)

        Using (document)
            Dim sheets As IEnumerable(Of Sheet) = document.WorkbookPart.Workbook.GetFirstChild(Of Sheets)().Elements(Of Sheet)().Where(Function(s) s.Name = sheetName.ToString())
            If (sheets.Count = 0) Then
                ' The specified worksheet does not exist.
                Return
            End If

            Dim relationshipId As String = sheets.First.Id.Value
            Dim worksheetPart As WorksheetPart = CType(document.WorkbookPart.GetPartById(relationshipId), WorksheetPart)

            ' Get the cell at the specified column and row.
            Dim cell As Cell = GetSpreadsheetCell(worksheetPart.Worksheet, colName, rowIndex)
            If (cell Is Nothing) Then
                ' The specified cell does not exist.
                Return
            End If

            cell.Remove()
            worksheetPart.Worksheet.Save()

        End Using
    End Sub

    ' Given a worksheet, a column name, and a row index, gets the cell at the specified column and row.
    Private Function GetSpreadsheetCell(ByVal worksheet As Worksheet, ByVal columnName As String, ByVal rowIndex As UInteger) As Cell
        Dim rows As IEnumerable(Of Row) = worksheet.GetFirstChild(Of SheetData)().Elements(Of Row)().Where(Function(r) r.RowIndex = rowIndex.ToString())
        If (rows.Count = 0) Then
            ' A cell does not exist at the specified row.
            Return Nothing
        End If

        Dim cells As IEnumerable(Of Cell) = rows.First().Elements(Of Cell)().Where(Function(c) String.Compare(c.CellReference.Value, columnName + rowIndex.ToString(), True) = 0)
        If (cells.Count = 0) Then
            ' A cell does not exist at the specified column, in the specified row.
            Return Nothing
        End If

        Return cells.First
    End Function

    ' Given a shared string ID and a SpreadsheetDocument, verifies that other cells in the document no longer 
    ' reference the specified SharedStringItem and removes the item.
    Private Sub RemoveSharedStringItem(ByVal shareStringId As Integer, ByVal document As SpreadsheetDocument)
        Dim remove As Boolean = True

        For Each part In document.WorkbookPart.GetPartsOfType(Of WorksheetPart)()
            Dim worksheet As Worksheet = part.Worksheet
            For Each cell In worksheet.GetFirstChild(Of SheetData)().Descendants(Of Cell)()
                ' Verify if other cells in the document reference the item.
                If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString AndAlso cell.CellValue.Text = shareStringId.ToString() Then
                    ' Other cells in the document still reference the item. Do not remove the item.
                    remove = False
                    Exit For
                End If
            Next

            If Not remove Then
                Exit For
            End If
        Next

        ' Other cells in the document do not reference the item. Remove the item.
        If remove Then
            Dim shareStringTablePart As SharedStringTablePart = document.WorkbookPart.SharedStringTablePart
            If shareStringTablePart Is Nothing Then
                Exit Sub
            End If

            Dim item As SharedStringItem = shareStringTablePart.SharedStringTable.Elements(Of SharedStringItem)().ElementAt(shareStringId)
            If item IsNot Nothing Then
                item.Remove()

                ' Refresh all the shared string references.
                For Each part In document.WorkbookPart.GetPartsOfType(Of WorksheetPart)()
                    Dim worksheet As Worksheet = part.Worksheet
                    For Each cell In worksheet.GetFirstChild(Of SheetData)().Descendants(Of Cell)()
                        If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then
                            Dim itemIndex As Integer = Integer.Parse(cell.CellValue.Text)
                            If itemIndex > shareStringId Then
                                cell.CellValue.Text = (itemIndex - 1).ToString()
                            End If
                        End If
                    Next
                    worksheet.Save()
                Next

                document.WorkbookPart.SharedStringTablePart.SharedStringTable.Save()
            End If
        End If
    End Sub

関連項目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)