Berechnen der Summe eines Zellbereichs in einem Kalkulationstabellendokument

In diesem Thema wird gezeigt, wie Sie die Klassen im Open XML SDK für Office verwenden, um die Summe eines zusammenhängenden Zellbereichs in einem Tabellenkalkulationsdokument programmgesteuert zu berechnen.

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

Der Beispielcode übergibt zunächst an die CalculateSumOfCellRange-Methode einen Parameter, der den vollständigen Pfad zur SpreadsheetML-Quelldatei darstellt, einen Parameter, der den Namen des die Zellen enthaltenden Tabellenblatts darstellt, einen Parameter, der den Namen der ersten Zelle im zusammenhängenden Bereich darstellt, einen Parameter, der den Namen der letzten Zelle im zusammenhängenden Bereich darstellt, und einen Parameter, der den Namen der Zelle darstellt, in der das Ergebnis angezeigt werden soll.

Anschließend öffnet der Code die Datei zur Bearbeitung als SpreadsheetDocument-Dokumentpaket mit Lese-/Schreibzugriff und ruft das angegebene Worksheet-Objekt ab. Dann ruft er den Index der Zeile für die erste und letzte Zelle im zusammenhängenden Bereich ab, indem er die GetRowIndex-Methode aufruft. Schließlich ruft er den Namen der Spalte der ersten und letzten Zelle im zusammenhängenden Bereich ab, indem er die GetColumnName-Methode aufruft.

Für jedes Row-Objekt im zusammenhängenden Bereich werden die einzelnen Cell-Objekte durchlaufen, und es wird bestimmt, ob die sich Spalte der Zelle im zusammenhängenden Bereich befindet, indem die CompareColumn-Methode aufgerufen wird. Wenn sich die Zelle im zusammenhängenden Bereich befindet, wird der Wert der Zelle zu der Summe addiert. Es wird das SharedStringTablePart-Objekt abgerufen, falls vorhanden. Wenn sie nicht vorhanden ist, wird mithilfe der AddNewPart-Methode eine erstellt. Das Ergebnis wird in das SharedStringTablePart-Objekt eingefügt, indem die InsertSharedStringItem-Methode aufgerufen wird.

Der Code fügt eine neue Zelle für das Ergebnis in das Arbeitsblatt ein, indem die InsertCellInWorksheet-Methode aufgerufen und der Wert der Zelle festgelegt wird. Weitere Informationen finden Sie unter Einfügen einer Zelle in eine Kalkulationstabelle und anschließendes Speichern des Arbeitsblatts.

C#

static void CalculateSumOfCellRange(string docName, string worksheetName, string firstCellName, string lastCellName, string resultCell)
{
    // Open the document for editing.
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
    {
        IEnumerable<Sheet>? sheets = document.WorkbookPart?.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName);
        string? firstId = sheets?.First().Id;
        if (sheets is null || firstId is null || sheets.Count() == 0)
        {
            // The specified worksheet does not exist.
            return;
        }

        WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart!.GetPartById(firstId);
        Worksheet worksheet = worksheetPart.Worksheet;

        // Get the row number and column name for the first and last cells in the range.
        uint firstRowNum = GetRowIndex(firstCellName);
        uint lastRowNum = GetRowIndex(lastCellName);
        string firstColumn = GetColumnName(firstCellName);
        string lastColumn = GetColumnName(lastCellName);

        double sum = 0;

        // Iterate through the cells within the range and add their values to the sum.
        foreach (Row row in worksheet.Descendants<Row>().Where(r => r.RowIndex is not null && r.RowIndex.Value >= firstRowNum && r.RowIndex.Value <= lastRowNum))
        {
            foreach (Cell cell in row)
            {
                if (cell.CellReference is not null && cell.CellReference.Value is not null)
                {
                    string columnName = GetColumnName(cell.CellReference.Value);
                    if (CompareColumn(columnName, firstColumn) >= 0 && CompareColumn(columnName, lastColumn) <= 0 && double.TryParse(cell.CellValue?.Text, out double num))
                    {
                        sum += num;
                    }
                }
            }
        }

        // Get the SharedStringTablePart and add the result to it.
        // If the SharedStringPart does not exist, create a new one.
        SharedStringTablePart shareStringPart;
        if (document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
        {
            shareStringPart = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
        }
        else
        {
            shareStringPart = document.WorkbookPart.AddNewPart<SharedStringTablePart>();
        }

        // Insert the result into the SharedStringTablePart.
        int index = InsertSharedStringItem("Result: " + sum, shareStringPart);

        Cell result = InsertCellInWorksheet(GetColumnName(resultCell), GetRowIndex(resultCell), worksheetPart);

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

        worksheetPart.Worksheet.Save();
    }
}

Visual Basic

' Given a document name, a worksheet name, the name of the first cell in the contiguous range, 
' the name of the last cell in the contiguous range, and the name of the results cell, 
' calculates the sum of the cells in the contiguous range and inserts the result into the results cell.
' Note: All cells in the contiguous range must contain numbers.Private Sub CalculateSumOfCellRange(ByVal docName As String, ByVal worksheetName As String, ByVal firstCellName As String, _
Private Sub CalculateSumOfCellRange(ByVal docName As String, ByVal worksheetName As String, ByVal firstCellName As String,
ByVal lastCellName As String, ByVal resultCell As String)
    ' Open the document for editing.
    Dim document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)

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

        Dim worksheetPart As WorksheetPart = CType(document.WorkbookPart.GetPartById(sheets.First().Id), WorksheetPart)
        Dim worksheet As Worksheet = worksheetPart.Worksheet

        ' Get the row number and column name for the first and last cells in the range.
        Dim firstRowNum As UInteger = GetRowIndex(firstCellName)
        Dim lastRowNum As UInteger = GetRowIndex(lastCellName)
        Dim firstColumn As String = GetColumnName(firstCellName)
        Dim lastColumn As String = GetColumnName(lastCellName)

        Dim sum As Double = 0

        ' Iterate through the cells within the range and add their values to the sum.
        For Each row As Row In worksheet.Descendants(Of Row)().Where(Function(r) r.RowIndex.Value >= firstRowNum _
                                                                         AndAlso r.RowIndex.Value <= lastRowNum)
            For Each cell As Cell In row
                Dim columnName As String = GetColumnName(cell.CellReference.Value)
                If ((CompareColumn(columnName, firstColumn) >= 0) AndAlso (CompareColumn(columnName, lastColumn) <= 0)) Then
                    sum = (sum + Double.Parse(cell.CellValue.Text))
                End If
            Next
        Next

        ' Get the SharedStringTablePart and add the result to it.
        ' If the SharedStringPart does not exist, create a new one.
        Dim shareStringPart As SharedStringTablePart
        If (document.WorkbookPart.GetPartsOfType(Of SharedStringTablePart)().Count() > 0) Then
            shareStringPart = document.WorkbookPart.GetPartsOfType(Of SharedStringTablePart)().First()
        Else
            shareStringPart = document.WorkbookPart.AddNewPart(Of SharedStringTablePart)()
        End If

        ' Insert the result into the SharedStringTablePart.
        Dim index As Integer = InsertSharedStringItem(("Result:" + sum.ToString()), shareStringPart)

        Dim result As Cell = InsertCellInWorksheet(GetColumnName(resultCell), GetRowIndex(resultCell), worksheetPart)

        ' Set the value of the cell.
        result.CellValue = New CellValue(index.ToString())
        result.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)
        worksheetPart.Worksheet.Save()
    End Using
End Sub

Zum Abrufen des Zeilenindexes übergibt der Code einen Parameter, den den Zellennamen darstellt, und erstellt einen neuen regulären Ausdruck, der den Zeilenindexteil des Zellennamens abgleicht. Weitere Informationen über reguläre Ausdrücke finden Sie unter Sprachelemente für reguläre Ausdrücke. Es ruft den Zeilenindex durch Aufrufen der Regex.Match-Methode ab und gibt dann den Zeilenindex zurück.

C#

// Given a cell name, parses the specified cell to get the row index.
static uint GetRowIndex(string cellName)
{
    // Create a regular expression to match the row index portion the cell name.
    Regex regex = new Regex(@"\d+");
    Match match = regex.Match(cellName);

    return uint.Parse(match.Value);
}

Visual Basic

' Given a cell name, parses the specified cell to get the row index.
Private Function GetRowIndex(ByVal cellName As String) As UInteger
    ' Create a regular expression to match the row index portion the cell name.
    Dim regex As Regex = New Regex("\d+")
    Dim match As Match = regex.Match(cellName)
    Return UInteger.Parse(match.Value)
End Function

Der Code ruft dann den Spaltennamen ab, indem er einen Parameter übergibt, der den Namen der Zelle darstellt, und erstellt einen neuen regulären Ausdruck, der mit dem Spaltennamenteil des Zellennamens übereinstimmt. Dieser reguläre Ausdruck entspricht einer beliebigen Kombination von Groß- oder Kleinbuchstaben. Sie ruft den Spaltennamen durch Aufrufen der Regex.Match-Methode ab und gibt dann den Spaltennamen zurück.

C#

// Given a cell name, parses the specified cell to get the column name.
static string GetColumnName(string cellName)
{
    // Create a regular expression to match the column name portion of the cell name.
    Regex regex = new Regex("[A-Za-z]+");
    Match match = regex.Match(cellName);

    return match.Value;
}

Visual Basic

' Given a cell name, parses the specified cell to get the column name.
Private Function GetColumnName(ByVal cellName As String) As String
    ' Create a regular expression to match the column name portion of the cell name.
    Dim regex As Regex = New Regex("[A-Za-z]+")
    Dim match As Match = regex.Match(cellName)
    Return match.Value
End Function

Zum Vergleich zweier Spalten übernimmt der Code zwei Parameter, die die zu vergleichenden Spalten darstellen. Wenn die erste Spalte länger als die zweite ist, wird 1 zurückgegeben. Wenn die zweite Spalte länger als die erste ist, wird -1 zurückgegeben. Andernfalls werden die Werte der Spalten mithilfe von Vergleichen verglichen und das Ergebnis zurückgegeben.

C#

// Given two columns, compares the columns.
static int CompareColumn(string column1, string column2)
{
    if (column1.Length > column2.Length)
    {
        return 1;
    }
    else if (column1.Length < column2.Length)
    {
        return -1;
    }
    else
    {
        return string.Compare(column1, column2, true);
    }
}

Visual Basic

' Given two columns, compares the columns.
Private Function CompareColumn(ByVal column1 As String, ByVal column2 As String) As Integer
    If (column1.Length > column2.Length) Then
        Return 1
    ElseIf (column1.Length < column2.Length) Then
        Return -1
    Else
        Return String.Compare(column1, column2, True)
    End If
End Function

Zum Einfügen eines SharedStringItem-Objekts übernimmt der Code einen Parameter, der den in die Zelle einzufügenden Text darstellt, und einen Parameter, der das SharedStringTablePart-Objekt für die Tabellenkalkulation darstellt. Wenn das ShareStringTablePart-Objekt kein SharedStringTable-Objekt enthält, wird eines erstellt. Wenn der Text bereits im ShareStringTable-Objekt vorhanden ist, wird der Index für das SharedStringItem-Objekt zurückgegeben, das den Text darstellt. Ist der Text nicht vorhanden, wird ein neues SharedStringItem-Objekt erstellt, das den Text darstellt. Dann wird der Index für das SharedStringItem-Objekt zurückgegeben, das den Text darstellt.

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 it.
    if (shareStringPart.SharedStringTable is null)
    {
        shareStringPart.SharedStringTable = new SharedStringTable();
    }

    int i = 0;
    foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
    {
        if (item.InnerText == text)
        {
            // The text already exists in the part. Return its index.
            return i;
        }

        i++;
    }

    // The text does not exist in the part. Create the SharedStringItem.
    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 it.
    If (shareStringPart.SharedStringTable Is Nothing) Then
        shareStringPart.SharedStringTable = New SharedStringTable
    End If

    Dim i As Integer = 0
    For Each item As SharedStringItem In shareStringPart.SharedStringTable.Elements(Of SharedStringItem)()
        If (item.InnerText = text) Then
            ' The text already exists in the part. Return its index.
            Return i
        End If
        i = (i + 1)
    Next

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

    Return i
End Function

Im letzten Schritt wird eine Zelle in das Arbeitsblatt eingefügt. Dazu werden Parameter übergeben, die den Namen der Spalte und die Nummer der Zeile der Zelle darstellen, sowie ein Parameter, der das die Zelle enthaltende Arbeitsblatt darstellt. Wenn die angegebene Zeile nicht vorhanden ist, wird die Zeile erstellt und an das Arbeitsblatt angefügt. Wenn die angegebene Spalte vorhanden ist, wird die Zelle gesucht, die der Zeile in dieser Spalte entspricht, und diese Zelle zurückgegeben. Wenn die angegebene Spalte nicht vorhanden ist, wird die Spalte erstellt und in das Arbeitsblatt eingefügt. Dann wird die Stelle bestimmt, an der die neue Zelle in die Spalte eingefügt werden soll, indem die Zeilenelemente durchlaufen werden, 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 Zelle eingefügt, auf die refCell mit der InsertBefore-Methode verweist. Anschließend wird das neue Cell-Objekt zurückgegeben.

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>() ?? worksheet.AppendChild(new 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;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;


static void CalculateSumOfCellRange(string docName, string worksheetName, string firstCellName, string lastCellName, string resultCell)
{
    // Open the document for editing.
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
    {
        IEnumerable<Sheet>? sheets = document.WorkbookPart?.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName);
        string? firstId = sheets?.First().Id;
        if (sheets is null || firstId is null || sheets.Count() == 0)
        {
            // The specified worksheet does not exist.
            return;
        }

        WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart!.GetPartById(firstId);
        Worksheet worksheet = worksheetPart.Worksheet;

        // Get the row number and column name for the first and last cells in the range.
        uint firstRowNum = GetRowIndex(firstCellName);
        uint lastRowNum = GetRowIndex(lastCellName);
        string firstColumn = GetColumnName(firstCellName);
        string lastColumn = GetColumnName(lastCellName);

        double sum = 0;

        // Iterate through the cells within the range and add their values to the sum.
        foreach (Row row in worksheet.Descendants<Row>().Where(r => r.RowIndex is not null && r.RowIndex.Value >= firstRowNum && r.RowIndex.Value <= lastRowNum))
        {
            foreach (Cell cell in row)
            {
                if (cell.CellReference is not null && cell.CellReference.Value is not null)
                {
                    string columnName = GetColumnName(cell.CellReference.Value);
                    if (CompareColumn(columnName, firstColumn) >= 0 && CompareColumn(columnName, lastColumn) <= 0 && double.TryParse(cell.CellValue?.Text, out double num))
                    {
                        sum += num;
                    }
                }
            }
        }

        // Get the SharedStringTablePart and add the result to it.
        // If the SharedStringPart does not exist, create a new one.
        SharedStringTablePart shareStringPart;
        if (document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
        {
            shareStringPart = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
        }
        else
        {
            shareStringPart = document.WorkbookPart.AddNewPart<SharedStringTablePart>();
        }

        // Insert the result into the SharedStringTablePart.
        int index = InsertSharedStringItem("Result: " + sum, shareStringPart);

        Cell result = InsertCellInWorksheet(GetColumnName(resultCell), GetRowIndex(resultCell), worksheetPart);

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

        worksheetPart.Worksheet.Save();
    }
}

// Given a cell name, parses the specified cell to get the row index.
static uint GetRowIndex(string cellName)
{
    // Create a regular expression to match the row index portion the cell name.
    Regex regex = new Regex(@"\d+");
    Match match = regex.Match(cellName);

    return uint.Parse(match.Value);
}

// Given a cell name, parses the specified cell to get the column name.
static string GetColumnName(string cellName)
{
    // Create a regular expression to match the column name portion of the cell name.
    Regex regex = new Regex("[A-Za-z]+");
    Match match = regex.Match(cellName);

    return match.Value;
}

// Given two columns, compares the columns.
static int CompareColumn(string column1, string column2)
{
    if (column1.Length > column2.Length)
    {
        return 1;
    }
    else if (column1.Length < column2.Length)
    {
        return -1;
    }
    else
    {
        return string.Compare(column1, column2, true);
    }
}

// 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 it.
    if (shareStringPart.SharedStringTable is null)
    {
        shareStringPart.SharedStringTable = new SharedStringTable();
    }

    int i = 0;
    foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
    {
        if (item.InnerText == text)
        {
            // The text already exists in the part. Return its index.
            return i;
        }

        i++;
    }

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

    return i;
}

// 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>() ?? worksheet.AppendChild(new 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 System.Text.RegularExpressions
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet


Module MyModule

    Sub Main(args As String())
        CalculateSumOfCellRange(args(0), args(1), args(2), args(3), args(4))
    End Sub

    ' Given a document name, a worksheet name, the name of the first cell in the contiguous range, 
    ' the name of the last cell in the contiguous range, and the name of the results cell, 
    ' calculates the sum of the cells in the contiguous range and inserts the result into the results cell.
    ' Note: All cells in the contiguous range must contain numbers.Private Sub CalculateSumOfCellRange(ByVal docName As String, ByVal worksheetName As String, ByVal firstCellName As String, _
    Private Sub CalculateSumOfCellRange(ByVal docName As String, ByVal worksheetName As String, ByVal firstCellName As String,
    ByVal lastCellName As String, ByVal resultCell As String)
        ' Open the document for editing.
        Dim document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)

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

            Dim worksheetPart As WorksheetPart = CType(document.WorkbookPart.GetPartById(sheets.First().Id), WorksheetPart)
            Dim worksheet As Worksheet = worksheetPart.Worksheet

            ' Get the row number and column name for the first and last cells in the range.
            Dim firstRowNum As UInteger = GetRowIndex(firstCellName)
            Dim lastRowNum As UInteger = GetRowIndex(lastCellName)
            Dim firstColumn As String = GetColumnName(firstCellName)
            Dim lastColumn As String = GetColumnName(lastCellName)

            Dim sum As Double = 0

            ' Iterate through the cells within the range and add their values to the sum.
            For Each row As Row In worksheet.Descendants(Of Row)().Where(Function(r) r.RowIndex.Value >= firstRowNum _
                                                                             AndAlso r.RowIndex.Value <= lastRowNum)
                For Each cell As Cell In row
                    Dim columnName As String = GetColumnName(cell.CellReference.Value)
                    If ((CompareColumn(columnName, firstColumn) >= 0) AndAlso (CompareColumn(columnName, lastColumn) <= 0)) Then
                        sum = (sum + Double.Parse(cell.CellValue.Text))
                    End If
                Next
            Next

            ' Get the SharedStringTablePart and add the result to it.
            ' If the SharedStringPart does not exist, create a new one.
            Dim shareStringPart As SharedStringTablePart
            If (document.WorkbookPart.GetPartsOfType(Of SharedStringTablePart)().Count() > 0) Then
                shareStringPart = document.WorkbookPart.GetPartsOfType(Of SharedStringTablePart)().First()
            Else
                shareStringPart = document.WorkbookPart.AddNewPart(Of SharedStringTablePart)()
            End If

            ' Insert the result into the SharedStringTablePart.
            Dim index As Integer = InsertSharedStringItem(("Result:" + sum.ToString()), shareStringPart)

            Dim result As Cell = InsertCellInWorksheet(GetColumnName(resultCell), GetRowIndex(resultCell), worksheetPart)

            ' Set the value of the cell.
            result.CellValue = New CellValue(index.ToString())
            result.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)
            worksheetPart.Worksheet.Save()
        End Using
    End Sub

    ' Given a cell name, parses the specified cell to get the row index.
    Private Function GetRowIndex(ByVal cellName As String) As UInteger
        ' Create a regular expression to match the row index portion the cell name.
        Dim regex As Regex = New Regex("\d+")
        Dim match As Match = regex.Match(cellName)
        Return UInteger.Parse(match.Value)
    End Function

    ' Given a cell name, parses the specified cell to get the column name.
    Private Function GetColumnName(ByVal cellName As String) As String
        ' Create a regular expression to match the column name portion of the cell name.
        Dim regex As Regex = New Regex("[A-Za-z]+")
        Dim match As Match = regex.Match(cellName)
        Return match.Value
    End Function

    ' Given two columns, compares the columns.
    Private Function CompareColumn(ByVal column1 As String, ByVal column2 As String) As Integer
        If (column1.Length > column2.Length) Then
            Return 1
        ElseIf (column1.Length < column2.Length) Then
            Return -1
        Else
            Return String.Compare(column1, column2, True)
        End If
    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 it.
        If (shareStringPart.SharedStringTable Is Nothing) Then
            shareStringPart.SharedStringTable = New SharedStringTable
        End If

        Dim i As Integer = 0
        For Each item As SharedStringItem In shareStringPart.SharedStringTable.Elements(Of SharedStringItem)()
            If (item.InnerText = text) Then
                ' The text already exists in the part. Return its index.
                Return i
            End If
            i = (i + 1)
        Next

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

        Return i
    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