Compartilhar via

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

  • Artigo

Este tópico mostra como utilizar as classes no SDK Open XML para o Office para calcular a soma de um intervalo contíguo de células num documento de folha de cálculo programaticamente.

Estrutura básica de um documento de folha de cálculoML

A estrutura de documentos básica de um SpreadsheetML documento consiste nos Sheets elementos e Sheet , que referenciam as folhas de cálculo no livro. Um arquivo XML separado é criado para cada planilha. Por exemplo, o SpreadsheetML para um Workbook que tem duas folhas de cálculo com o nome MySheet1 e MySheet2 está localizado no ficheiro Workbook.xml e é apresentado no seguinte exemplo de código.

    <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
    <workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
        <sheets>
            <sheet name="MySheet1" sheetId="1" r:id="rId1" /> 
            <sheet name="MySheet2" sheetId="2" r:id="rId2" /> 
        </sheets>
    </workbook>

Os ficheiros XML da folha de cálculo 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 Row elementos. A row contém um ou mais Cell elementos. Cada célula contém um CellValue elemento que representa o valor da célula. Por exemplo, o para a SpreadsheetML primeira folha de cálculo num livro, que tem apenas o valor 100 na célula A1, está localizado no ficheiro Sheet1.xml e é apresentado no seguinte exemplo de código.

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

Com o SDK Open XML, pode criar a estrutura do documento e o conteúdo que utiliza classes com tipos fortes que correspondem a SpreadsheetML elementos. Pode encontrar estas classes no DocumentFormat.OpenXML.Spreadsheet espaço de nomes. A tabela seguinte lista os nomes das classes que correspondem aos workbookelementos , sheets, sheet, worksheete sheetData .

Elemento SpreadsheetML Abrir Classe SDK XML Descrição
<workbook/> 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.
<worksheet/> 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 por transmitir ao método CalculateSumOfCellRange um parâmetro que representa o caminho completo para o ficheiro de origem SpreadsheetML , um parâmetro que representa o nome da folha de cálculo 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 onde pretende que o resultado seja apresentado.

Em seguida, o código abre o ficheiro para edição como um SpreadsheetDocument pacote de documentos para acesso de leitura/escrita e o código obtém o objeto especificado Worksheet . Em seguida, obtém o índice da linha para a primeira e última célula no intervalo contíguo ao chamar o GetRowIndex método . Obtém o nome da coluna para a primeira e última célula no intervalo contíguo ao chamar o GetColumnName método .

Para cada Row objeto dentro do intervalo contíguo, o código itera através de cada Cell objeto e determina se a coluna da célula está dentro do intervalo contíguo ao chamar o CompareColumn método . Se a célula estiver dentro do intervalo contíguo, o código adiciona o valor da célula à soma. Em seguida, obtém o SharedStringTablePart objeto se existir. Se não existir, cria um com o AddNewPart método . Insere o resultado no SharedStringTablePart objeto ao chamar o InsertSharedStringItem método .

O código insere uma nova célula para o resultado na folha de cálculo ao chamar o InsertCellInWorksheet método e define o valor da célula. Para obter mais informações, veja como inserir uma célula numa folha de cálculo.

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);
    }
}

Para obter o índice de linhas, o código transmite um parâmetro que representa o nome da célula e cria uma nova expressão regular para corresponder à parte do índice da linha do nome da célula. Para obter mais informações sobre expressões regulares, veja Elementos de Linguagem de Expressão Regular. Obtém o índice de linhas ao chamar o Match método e, em seguida, devolve o índice de linhas.

// 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);
}

Em seguida, o código obtém o nome da coluna ao transmitir 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. Esta expressão regular corresponde a qualquer combinação de letras maiúsculas ou minúsculas. Obtém o nome da coluna ao chamar o Match método e, em seguida, devolve o nome da coluna.

// 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;
}

Para comparar duas colunas, o código passa em dois parâmetros que representam as colunas a comparar. Se a primeira coluna for maior do que a segunda coluna, devolve 1. Se a segunda coluna for maior do que a primeira coluna, devolve -1. Caso contrário, compara os valores das colunas com e Compare devolve o resultado.

// 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);
    }
}

Para inserir um SharedStringItem, o código passa num parâmetro que representa o texto a inserir na célula e um parâmetro que representa o SharedStringTablePart objeto da folha de cálculo. Se o ShareStringTablePart objeto não contiver um SharedStringTable objeto, cria um. Se o texto já existir no ShareStringTable objeto, devolve o índice do SharedStringItem objeto que representa o texto. Se o texto não existir, crie um novo SharedStringItem objeto que represente o texto. Em seguida, devolve o índice do SharedStringItem objeto que representa o texto.

// 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)));

    return i;
}

O passo final é inserir uma célula na folha de cálculo. O código faz isso ao transmitir parâmetros que representam o nome da coluna e o número da linha da célula e um parâmetro que representa a folha de cálculo que contém a célula. Se a linha especificada não existir, cria a linha e acrescenta-a à folha de cálculo. Se a coluna especificada existir, localiza a célula que corresponde à linha nessa coluna e devolve a célula. Se a coluna especificada não existir, cria a coluna e insere-a na folha de cálculo. Em seguida, determina onde inserir a nova célula na coluna ao iterar através dos elementos da linha para localizar a célula que vem diretamente a seguir à linha especificada, por ordem sequencial. Guarda esta linha na refCell variável . Insere a nova célula antes da célula referenciada com refCell o InsertBefore método . Em seguida, devolve o novo Cell objeto.

// 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);

        return newCell;
    }
}

Código de exemplo

Este é o código de exemplo completo em C# e em Visual Basic.

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);
    }
}

// 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)));

    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);

        return newCell;
    }
}

Confira também