Вычисление суммы смежных ячеек в электронной таблице

Дата последнего изменения: 14 октября 2010 г.

Применимо к: Excel 2010 | Office 2010 | PowerPoint 2010 | Word 2010

В этой статье
Получение объекта SpreadsheetDocument
Базовая структура документа SpreadsheetML
Механизм работы примера кода
Пример кода

В этом разделе показано, как использовать классы в пакете SDK Open XML версии 2.0 для Microsoft Office для вычисления суммы смежных ячеек в документе электронной таблицы программными средствами.

Для компиляции кода, представленного в этом разделе, требуются следующие директивы сборки.

using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

Imports System.Collections.Generic
Imports System.Linq
Imports System.Text.RegularExpressions
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

Получение объекта SpreadsheetDocument

В Open XML SDK пакет документа Excel представлен классом SpreadsheetDocument. Для открытия документа Excel и работы с ним следует создать экземпляр класса SpreadsheetDocument из документа. После создания экземпляра из документа можно получить доступ к основной части Workbook, которая содержит листы. Текст документа представлен в пакете в виде XML-кода с использованием разметки SpreadsheetML.

Чтобы создать экземпляр класса на основе документа, необходимо вызвать один из методов Open. Доступно несколько таких методов с разными сигнатурами. В примере кода в этом разделе используется метод Open(String, Boolean) с сигнатурой, требующей два параметра. Первый параметр принимает строку с полным путем, представляющим открываемый документ. Второй параметр имеет значение true или false и указывает, следует ли открыть документ для редактирования. Если этот параметр принимает значение false, внесенные в документ изменения не сохраняются.

Ниже показан пример кода, вызывающего метод Open с помощью инструкции using.

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

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

Инструкцию using рекомендуется использовать вместо традиционной последовательности .Open, .Save, .Close. Это позволяет обеспечить автоматический вызов метода Dispose (внутреннего метода, используемого пакетом SDK Open XML для очистки ресурсов) при достижении закрывающей скобки. Блок, следующий за инструкцией using, создает область для объекта, создаваемого или именуемого в инструкции using, в данном случае — document.

Базовая структура документа SpreadsheetML

Базовая структура документа SpreadsheetML состоит из элементов Sheets и Sheet, которые ссылаются на листы в книге. Для каждого листа создается отдельный XML-файл. Например, разметка SpreadsheetML для книги с двумя листами MySheet1 и MySheet2 размещена в файле Workbook.xml и показана в следующем примере кода.

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

XML-файлы листов содержат один или несколько элементов уровня блока, такие как SheetData. sheetData представляет таблицу ячеек и содержит один или несколько элементов Row. row содержит один или несколько элементов Cell. Каждая ячейка содержит элемент CellValue, представляющий значение ячейки. Например, разметка SpreadsheetML для первого листа книги, в котором только у ячейки A1 имеется значение 100, размещена в файле в файле Sheet1.xml и показана в следующем примере кода.

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

Использование SDK Open XML версии 2.0 позволяет создать структуру и содержимое документа с помощью строго типизированных классов, соответствующих элементам SpreadsheetML. Эти классы находятся в пространстве имен DocumentFormat.OpenXML.Spreadsheet. В следующей таблице представлены классы, соответствующие элементам workbook, sheets, sheet, worksheet и sheetData.

Механизм работы примера кода

В приведенном ниже примере кода методу CalculateSumOfCellRange передается параметр, представляющий полный путь к исходному файлу SpreadsheetML, параметр, представляющий имя листа с ячейками, параметр, представляющий имя первой ячейки массива смежных ячеек, параметр, представляющий имя последней ячейки массива смежных ячеек, а также параметр, представляющий имя ячейки для отображения результата.

Затем код открывает файл для редактирования в качестве пакета документа SpreadsheetDocument для чтения и записи, а также возвращает указанный объект Worksheet. Код возвращает индекс строки для первой и последней ячейки в непрерывном диапазоне ячеек путем вызова метода GetRowIndex. Код возвращает имя столбца для первой и последней ячейки в непрерывном диапазоне путем вызова метода GetColumnName.

Для каждого объекта Row в непрерывном диапазоне ячеек код выполняет итерацию по каждому объекту Cell и определяет, находится ли столбец ячейки в непрерывном диапазоне, посредством вызова метода CompareColumn. Если ячейка находится в непрерывном диапазоне ячеек, код добавляет значение ячейки к сумме. Затем код возвращает объект SharedStringTablePart, если он существует. В противном случае код создает его с помощью метода AddNewPart. Код вставляет результат в объект SharedStringTablePart с помощью метода InsertSharedStringItem.

Код вставляет новую ячейку в лист для отображения результата, вызвав метод InsertCellInWorksheet и задав значение ячейки, а затем сохраняет лист. Дополнительные сведения см. в разделе cc861607(v=office.14).md.

// 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 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);
        if (sheets.Count() == 0)
        {
            // The specified worksheet does not exist.
            return; 
        }

        WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
        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.Value >= firstRowNum && r.RowIndex.Value <= lastRowNum))
        {
            foreach (Cell cell in row)
            {
                string columnName = GetColumnName(cell.CellReference.Value);
                if (CompareColumn(columnName, firstColumn) >= 0 && CompareColumn(columnName, lastColumn) <= 0)
                {
                    sum += double.Parse(cell.CellValue.Text);
                }
            }
        }

        // 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 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 Shared 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.
    Using document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)
        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 += Double.Parse(cell.CellValue.Text)
                End If
            Next cell
        Next row

        ' 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, 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 Subxxxxxxx

Чтобы получить индекс строки, код передает параметр, который представляет имя ячейки, и создает новое регулярное выражения для сопоставления части индекса строки из имени ячейки. Дополнительные сведения о регулярных выражениях см. в статье Элементы языка регулярных выражений. Для получения индекса строки код вызывает метод Regex.Match и затем возвращает индекс строки.

// Given a cell name, parses the specified cell to get the row index.
private 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 row index.
Private Shared Function GetRowIndex(ByVal cellName As String) As UInteger
    ' Create a regular expression to match the row index portion the cell name.
    Dim regex As New Regex("\d+")
    Dim match As Match = regex.Match(cellName)

    Return UInteger.Parse(match.Value)
End Function

Затем код возвращает имя столбца путем передачи параметра, представляющего имя ячейки, и создает новое регулярное выражение для сопоставления имени столбца из имени ячейки. Регулярное выражение сопоставляет любое сочетание прописных и строчных букв. Для получения имени столбца код вызывает метод Regex.Match и затем возвращает имя столбца.

// Given a cell name, parses the specified cell to get the column name.
private 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 a cell name, parses the specified cell to get the column name.
Private Shared 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 New Regex("[A-Za-z]+")
    Dim match As Match = regex.Match(cellName)

    Return match.Value
End Function

Для сравнения двух столбцов код передает два параметра, которые представляют сравниваемые столбцы. Если первый столбец длиннее второго, код возвращает значение 1. Если второй столбец длиннее первого, код возвращает значение -1. В противном случае код сравнивает значения столбцов с помощью метода Compare и возвращает результат.

// Given two columns, compares the columns.
private 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 two columns, compares the columns.
Private Shared 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

Для вставки SharedStringItem код передает параметр, представляющий текст для вставки в ячейку, и параметр, представляющий объект SharedStringTablePart для электронной таблицы. Если объект ShareStringTablePart не содержит объект SharedStringTable, код создаст его. Если текст уже существует в объекте ShareStringTable, то код возвращает индекс объекта SharedStringItem, который представляет текст. Если текст не существует, код создает новый объект SharedStringItem, представляющий текст. Затем код возвращает индекс объекта SharedStringItem, который представляет текст.

// Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
// and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
    // If the part does not contain a SharedStringTable, create it.
    if (shareStringPart.SharedStringTable == 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 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 Shared 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 += 1
    Next item

    ' 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

Последний шаг — вставка ячейки в лист. Для этого код передает параметры, представляющие имя столбца и номер строки ячейки, а также параметр, представляющий лист с данной ячейкой. Если указанная строка не существует, код создает строку и добавляет ее на лист. Если указанный столбец существует, код находит ячейку, соответствующую строке в этом столбце, и возвращает ячейку. Если указанный столбец не существует, код создает его и вставляет в лист. Затем код определяет, где вставить ячейку в столбце, выполняя итерацию по элементам строки для поиска ячейки, которая расположена сразу после указанной строки. Код сохраняет эту строку в переменной refCell. Код вставляет новую ячейку перед ячейкой, на которую ссылается refCell, с помощью метода InsertBefore. Затем код возвращает новый объект Cell.

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

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

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

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

        worksheet.Save();
        return newCell;
    }
}

' Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
' If the cell already exists, returns it. 
Private Shared 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

    ' 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 = rowIndex).Count() <> 0 Then
        row = sheetData.Elements(Of Row)().Where(Function(r) r.RowIndex = rowIndex).First()
    Else
        row = New Row() With {.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).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 cell

        Dim newCell As New Cell() With {.CellReference = cellReference}
        row.InsertBefore(newCell, refCell)

        worksheet.Save()
        Return newCell
    End If
End Function

Пример кода

В следующем примере кода вычисляется сумма смежных ячеек в документе электронной таблицы. Результат вставляется в объект SharedStringTablePart и в указанную ячейку результата. Для вызова метода CalculateSumOfCellRange можно использовать следующий пример:

string docName = @"C:\Users\Public\Documents\Sheet1.xlsx";
string worksheetName = "John";
string firstCellName = "A1";
string lastCellName = "A3";
string resultCell = "A4";
CalculateSumOfCellRange(docName, worksheetName, firstCellName, lastCellName, resultCell);

Dim docName As String = "C:\Users\Public\Documents\Sheet1.xlsx"
Dim worksheetName As String = "John"
Dim firstCellName As String = "A1"
Dim lastCellName As String = "A3"
Dim resultCell As String = "A4"
CalculateSumOfCellRange(docName, worksheetName, firstCellName, lastCellName, resultCell)

После запуска программы можно открыть файл "Sheet1.xlsx" и проверить сумму значений столбца на листе "John" в указанной ячейке.

Ниже полностью приведен пример кода для языков C# и Visual Basic.

private 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);
        if (sheets.Count() == 0)
        {
            // The specified worksheet does not exist.
            return;
        }

        WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
        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.Value >= firstRowNum && r.RowIndex.Value <= lastRowNum))
        {
            foreach (Cell cell in row)
            {
                string columnName = GetColumnName(cell.CellReference.Value);
                if (CompareColumn(columnName, firstColumn) >= 0 && CompareColumn(columnName, lastColumn) <= 0)
                {
                    sum += double.Parse(cell.CellValue.Text);
                }
            }
        }

        // 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.
private 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.
private 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.
private 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.
private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
    // If the part does not contain a SharedStringTable, create it.
    if (shareStringPart.SharedStringTable == 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. 
private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
    Worksheet worksheet = worksheetPart.Worksheet;
    SheetData sheetData = worksheet.GetFirstChild<SheetData>();
    string cellReference = columnName + rowIndex;

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

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

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

        worksheet.Save();
        return newCell;
    }
}

' Given a document name, 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

См. также

Ссылка

Class Library Reference

Другие ресурсы

LINQ

Лямбда-выражения

Лямбда-выражения (руководство по программированию в C#)