Calcular a soma de intervalo de células em um documento de planilha

Este tópico mostra como usar as classes no SDK Open XML para Office para calcular a soma de um intervalo contíguo de células em um documento de planilha programaticamente.

Estrutura básica de um documento de planilha

A estrutura básica de um documento SpreadsheetML consiste nos elementos Sheets e Sheet, que referenciam as planilhas na pasta de trabalho. Um arquivo XML separado é criado para cada planilha. Por exemplo, o SpreadsheetML de uma Pasta de trabalho que tem duas planilhas chamadas MySheet1 e MySheet2 está localizado no arquivo Workbook.xml e é mostrado no exemplo de código a seguir.

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

Os arquivos XML da planilha contêm um ou mais elementos de nível de bloco, como sheetData , representa a tabela de células e contém um ou mais elementos row . Uma linha contém um ou mais elementos Cell. Cada célula contém um elemento CellValue que representa o valor da célula. Por exemplo, a PlanilhaML da primeira planilha em uma pasta de trabalho, que tem apenas o valor 100 na célula A1, está localizada no arquivo Sheet1.xml e é mostrada no exemplo de código a seguir.

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

Usando o SDK Do Open XML, você pode criar a estrutura de documentos e o conteúdo que usa classes fortemente tipdas que correspondem a elementos SpreadsheetML . Você pode encontrar essas classes no namespace DocumentFormat.OpenXML.Spreadsheet. A tabela a seguir lista os nomes das classes que correspondem aos elementos workbook, sheets, sheet, worksheet e sheetData.

Elemento SpreadsheetML	Classe SDK Open XML	Descrição
pasta de trabalho	DocumentFormat.OpenXML.Spreadsheet.Workbook	O elemento raiz para a parte do documento principal.
sheets	DocumentFormat.OpenXML.Spreadsheet.Sheets	O contêiner para as estruturas de nível de bloco, como sheet, fileVersion e outras indicadas na especificação ISO/IEC 29500.
sheet	DocumentFormat.OpenXml.Spreadsheet.Sheet	Uma planilha que aponta para um arquivo de definição de planilha.
planilha	DocumentFormat.OpenXML.Spreadsheet. Planilha	Um arquivo de definição de planilha que contém os dados de planilha.
sheetData	DocumentFormat.OpenXML.Spreadsheet.SheetData	A tabela de células, agrupadas por linhas.
row	DocumentFormat.OpenXml.Spreadsheet.Row	Uma linha na tabela de células.
c	DocumentFormat.OpenXml.Spreadsheet.Cell	Uma célula em uma linha.
v	DocumentFormat.OpenXml.Spreadsheet.CellValue	O valor de uma célula.

Como funciona o código de exemplo

O código de exemplo começa passando para o método CalculateSumOfCellRange um parâmetro que representa o caminho completo para o arquivo SpreadsheetML de origem, um parâmetro que representa o nome da planilha que contém as células, um parâmetro que representa o nome da primeira célula no intervalo contíguo, um parâmetro que representa o nome da última célula no intervalo contíguo, e um parâmetro que representa o nome da célula em que você deseja que o resultado seja exibido.

Em seguida, o código abre o arquivo para edição como um pacote de documento de PlanilhaDocument para acesso de leitura/gravação, o código obtém o objeto planilha especificado. Em seguida, ele obtém o índice da linha para a primeira e última célula no intervalo contíguo chamando o método GetRowIndex . Ele obtém o nome da coluna para a primeira e última célula no intervalo contíguo chamando o método GetColumnName .

Para cada objeto Row dentro do intervalo contíguo, o código itera por meio de cada objeto Cell e determina se a coluna da célula está dentro do intervalo contíguo chamando o método CompareColumn . Se a célula estiver dentro do intervalo contíguo, o código adicionará o valor da célula à soma. Em seguida, ele obtém o objeto SharedStringTablePart se ele existir. Se ele não existir, ele criará um usando o método AddNewPart . Ele insere o resultado no objeto SharedStringTablePart chamando o método InsertSharedStringItem .

O código insere uma nova célula para o resultado na planilha chamando o método InsertCellInWorksheet e defina o valor da célula. Para obter mais informações, confira como inserir uma célula em uma planilha e, em seguida, salvar a planilha.

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

Para obter o índice de linha, o código passa um parâmetro que representa o nome da célula e cria uma nova expressão regular para corresponder à parte do índice de linha do nome da célula. Para obter mais informações sobre expressões regulares, consulte Elementos de Linguagem de Expressão Regular. Ele obtém o índice de linha chamando o método Regex.Match e retorna o índice de linha.

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

Em seguida, o código obtém o nome da coluna passando um parâmetro que representa o nome da célula e cria uma nova expressão regular para corresponder à parte do nome da coluna do nome da célula. Essa expressão regular corresponde a qualquer combinação de letras maiúsculas ou minúsculas. Ele obtém o nome da coluna chamando o método Regex.Match e retorna o nome da coluna.

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

Para comparar duas colunas, o código passa em dois parâmetros que representam as colunas a serem comparadas. Se a primeira coluna for maior que a segunda coluna, ela retornará 1. Se a segunda coluna for maior que a primeira coluna, ela retornará -1. Caso contrário, ele compara os valores das colunas usando o Compare e retorna o resultado.

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

Para inserir um SharedStringItem, o código passa em um parâmetro que representa o texto a ser inserido na célula e um parâmetro que representa o objeto SharedStringTablePart para a planilha. Se o objeto ShareStringTablePart não contiver um objeto SharedStringTable , ele criará um. Se o texto já existir no objeto ShareStringTable , ele retornará o índice do objeto SharedStringItem que representa o texto. Se o texto não existir, crie um novo objeto SharedStringItem que represente o texto. Em seguida, ele retorna o índice do objeto SharedStringItem que representa o texto.

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

A etapa final é inserir uma célula na planilha. O código faz isso passando parâmetros que representam o nome da coluna e o número da linha da célula e um parâmetro que representa a planilha que contém a célula. Se a linha especificada não existir, ela criará a linha e a acrescentará à planilha. Se a coluna especificada existir, ela localizará a célula que corresponde à linha nessa coluna e retornará a célula. Se a coluna especificada não existir, ela criará a coluna e a inserirá na planilha. Em seguida, ele determina onde inserir a nova célula na coluna iterando através dos elementos de linha para localizar a célula que vem diretamente após a linha especificada, em ordem sequencial. Ele salva essa linha na variável refCell . Ele insere a nova célula antes da célula referenciada por refCell usando o método InsertBefore . Em seguida, ele retorna o novo objeto Cell .

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

Código de exemplo

Este é o código de exemplo completo em C# e em 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

Confira também

• Abrir referência de biblioteca de classes do SDK XML