Einfügen von Text in eine Zelle in einem Tabellenkalkulationsdokument

In diesem Thema wird gezeigt, wie Sie die Klassen im Open XML SDK für Office verwenden, um Text programmgesteuert in eine Zelle in einem neuen Arbeitsblatt in einem Tabellenkalkulationsdokument einzufügen.

---

Grundlegende Struktur eines SpreadsheetML-Dokuments

Die grundlegende Struktur eines SpreadsheetML-Dokuments besteht aus den Sheets and Sheet-Elementen, die auf Arbeitsblätter in der Arbeitsmappe verweisen. Es wird eine separate XML-Datei für jedes Arbeitsblatt erstellt. Beispiel: SpreadsheetML für eine Arbeitsmappe, die zwei Arbeitsblätter „MySheet1“ und „MySheet2“ enthält, befindet sich in der Datei „Workbook.xml“ und wird im nachstehenden Codebeispiel angezeigt.

    <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
    <workbook xmlns=https://schemas.openxmlformats.org/spreadsheetml/2006/main xmlns:r="https://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>

Die XML-Dateien des Arbeitsblatts enthalten ein oder mehrere Elemente auf Blockebene, z. B. sheetData stellt die Zelltabelle dar und enthält ein oder mehrere Row-Elemente . Eine Zeile enthält ein oder mehrere Cell-Elemente. Jede Zelle enthält ein CellValue-Element, das den Wert der Zelle darstellt. Beispiel: SpreadsheetML für das erste Arbeitsblatt in einer Arbeitsmappe, das nur den Wert „100“ in Zelle A1 aufweist, befindet sich in der Datei „Sheet1.xml“ und wird im nachstehenden Codebeispiel angezeigt.

    <?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>

Mit dem Open XML SDK können Sie Dokumentstrukturen und Inhalte erstellen, die stark typisierte Klassen verwenden, die SpreadsheetML-Elementen entsprechen. Diese Klassen sind im DocumentFormat.OpenXML.Spreadsheet-Namespace enthalten. Die folgende Tabelle enthält die Namen der Klassen, die den Elementen workbook, sheets, sheet, worksheet und sheetData entsprechen.

SpreadsheetML-Element	Open XML SDK-Klasse	Beschreibung
workbook	DocumentFormat.OpenXML.Spreadsheet.Workbook	Das Stammelement des Hauptdokumentteils.
sheets	DocumentFormat.OpenXML.Spreadsheet.Sheets	Der Container für die Strukturen auf Blockebene, wie z. B. "sheet", "fileVersion" und andere Elemente, die in der Spezifikation ISO/IEC 29500 angegeben sind.
sheet	DocumentFormat.OpenXml.Spreadsheet.Sheet	Ein Blatt, das auf eine Blattdefinitionsdatei zeigt.
worksheet	DocumentFormat.OpenXML.Spreadsheet. Worksheet	Eine Blattdefinitionsdatei, welche die Blattdaten enthält.
sheetData	DocumentFormat.OpenXML.Spreadsheet.SheetData	Die Zellentabelle, die mithilfe von Zeilen gruppiert wird.
row	DocumentFormat.OpenXml.Spreadsheet.Row	Eine Zeile in der Zellentabelle.
c	DocumentFormat.OpenXml.Spreadsheet.Cell	Eine Zelle in einer Zeile.
v	DocumentFormat.OpenXml.Spreadsheet.CellValue	Der Wert einer Zelle.

Funktionsweise des Beispielcodes

Nach dem Öffnen des SpreadsheetDocument-Dokuments für die Bearbeitung fügt der Code ein leeres Worksheet-Objekt in ein SpreadsheetDocument-Dokumentpaket ein. Dann fügt er ein neues Cell-Objekt in das neue Arbeitsblatt und den angegebenen Text in diese Zelle ein.

C#

// Given a document name and text, 
// inserts a new work sheet and writes the text to cell "A1" of the new worksheet.
static void InsertText(string docName, string text)
{
    // Open the document for editing.
    using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
    {
        WorkbookPart workbookPart = spreadSheet.WorkbookPart ?? spreadSheet.AddWorkbookPart();

        // Get the SharedStringTablePart. If it does not exist, create a new one.
        SharedStringTablePart shareStringPart;
        if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
        {
            shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
        }
        else
        {
            shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
        }

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

        // Insert a new worksheet.
        WorksheetPart worksheetPart = InsertWorksheet(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();
    }
}

Visual Basic

' 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

Der Code übergibt einen Parameter, der den in die Zelle einzufügenden Text darstellt, und einen Parameter, der das SharedStringTablePart-Objekt für das Arbeitsblatt darstellt. Wenn das ShareStringTablePart-Objekt kein SharedStringTable-Objekt enthält, erstellt der Code ein Objekt. Wenn der Text bereits im ShareStringTable-Objekt vorhanden ist, gibt der Code den Index für das SharedStringItem-Objekt zurück, das den Text darstellt. Andernfalls erstellt er ein neues SharedStringItem-Objekt, das den Text darstellt.

Im folgenden Code wird überprüft, ob der angegebene Text im SharedStringTablePart-Objekt vorhanden ist. Falls nicht, wird der Text hinzugefügt.

C#

// 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.
static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
    // If the part does not contain a SharedStringTable, create one.
    if (shareStringPart.SharedStringTable is 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;
}

Visual Basic

' 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

Der Code fügt ein neues WorksheetPart-Objekt zum WorkbookPart-Objekt hinzu. Dabei kommt die AddNewPart-Methode zum Einsatz. Er fügt dann ein neues Worksheet-Objekt zum WorksheetPart-Objekt hinzu und ruft eine eindeutige ID für das neue Arbeitsblatt ab. Dazu wird die maximale Anzahl von SheetId-Objekten ausgewählt, die im Tabellenkalkulationsdokument verwendet werden, und ein Objekt wird zum Erstellen der neuen Blatt-ID hinzugefügt. Das Arbeitsblatt wird benannt, indem das Wort „Blatt“ mit der ID-Blatt verkettet wird. Dann wird das neue Sheet-Objekt zur Sheets-Sammlung hinzugefügt.

Der folgende Code fügt ein neues Worksheet-Objekt ein, indem dem WorkbookPart-Objekt ein neues WorksheetPart-Objekt hinzugefügt wird.

C#

// Given a WorkbookPart, inserts a new worksheet.
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>() ?? workbookPart.Workbook.AppendChild(new 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<Sheet, uint>(s =>
        {
            if (s.SheetId is not null && s.SheetId.HasValue)
            {
                return s.SheetId.Value;
            }

            return 0;
        }).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;
}

Visual Basic

' 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

Zum Einfügen einer Zelle in ein Arbeitsblatt bestimmt der Code die Stelle, an der die neue Zelle in die Spalte eingefügt werden soll. Hierzu werden die Zeilenelemente durchlaufen, um die Zelle zu finden, die in sequenzieller Reihenfolge direkt auf die angegebene Zeile folgt. Diese Zeile wird in der Variablen RefCell gespeichert. Die neue Zelle wird vor der von refCell referenzierten Zelle mit der InsertBefore-Methode eingefügt.

Fügen Sie im folgenden Code ein neuesCell-Objekt in ein Worksheet-Objekt ein.

C#

// Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
// If the cell already exists, returns it. 
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 is not null && r.RowIndex == rowIndex).Count() != 0)
    {
        row = sheetData!.Elements<Row>().Where(r => r.RowIndex is not null && 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 is not null && c.CellReference.Value == columnName + rowIndex).Count() > 0)
    {
        return row.Elements<Cell>().Where(c => c.CellReference is not null && 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;
    }
}

Visual Basic

' 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

---

Beispielcode

Es folgt der vollständige Beispielcode in C# und Visual Basic.

C#

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

// Given a document name and text, 
// inserts a new work sheet and writes the text to cell "A1" of the new worksheet.
static void InsertText(string docName, string text)
{
    // Open the document for editing.
    using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
    {
        WorkbookPart workbookPart = spreadSheet.WorkbookPart ?? spreadSheet.AddWorkbookPart();

        // Get the SharedStringTablePart. If it does not exist, create a new one.
        SharedStringTablePart shareStringPart;
        if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
        {
            shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
        }
        else
        {
            shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
        }

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

        // Insert a new worksheet.
        WorksheetPart worksheetPart = InsertWorksheet(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.
static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
    // If the part does not contain a SharedStringTable, create one.
    if (shareStringPart.SharedStringTable is 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.
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>() ?? workbookPart.Workbook.AppendChild(new 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<Sheet, uint>(s =>
        {
            if (s.SheetId is not null && s.SheetId.HasValue)
            {
                return s.SheetId.Value;
            }

            return 0;
        }).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. 
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 is not null && r.RowIndex == rowIndex).Count() != 0)
    {
        row = sheetData!.Elements<Row>().Where(r => r.RowIndex is not null && 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 is not null && c.CellReference.Value == columnName + rowIndex).Count() > 0)
    {
        return row.Elements<Cell>().Where(c => c.CellReference is not null && 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;
    }
}

Visual Basic

Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet


Module MyModule

    Sub Main(args As String())
        InsertText(args(0), args(1))
    End Sub

    ' 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

End Module

Siehe auch

Referenz zur Open XML SDK-Klassenbibliothek