Partager via

Calculer la somme d’une plage de cellules dans un document de feuilles de calcul

  • Article

Cette rubrique montre comment utiliser les classes du Kit de développement logiciel (SDK) Open XML pour Office afin de calculer par programmation la somme d’une plage contiguë de cellules dans un document de feuille de calcul.

Structure de base d’un document spreadsheetML

La structure de base d’un SpreadsheetML document se compose des Sheets éléments et Sheet , qui font référence aux feuilles de calcul du classeur. Un fichier XML distinct est créé pour chaque feuille de calcul. Par exemple, le SpreadsheetML pour un Workbook qui a deux feuilles de calcul nommées MySheet1 et MySheet2 se trouve dans le fichier Workbook.xml et est illustré dans l’exemple de code suivant.

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

Les fichiers XML de feuille de calcul contiennent un ou plusieurs éléments de niveau bloc, tels que SheetData représente la table de cellules et contient un ou plusieurs Row éléments. Un row contient un ou plusieurs Cell éléments. Chaque cellule contient un CellValue élément qui représente la valeur de la cellule. Par exemple, le SpreadsheetML pour la première feuille de calcul d’un classeur, qui n’a que la valeur 100 dans la cellule A1, se trouve dans le fichier Sheet1.xml et est illustré dans l’exemple de code suivant.

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

À l’aide du Kit de développement logiciel (SDK) Open XML, vous pouvez créer une structure de document et un contenu qui utilisent des classes fortement typées qui correspondent à SpreadsheetML des éléments. Vous trouverez ces classes dans l’espace de DocumentFormat.OpenXML.Spreadsheet noms . Le tableau suivant répertorie les noms de classes des classes qui correspondent aux workbookéléments , sheets, sheet, worksheetet sheetData .

Élément SpreadsheetML Classe du Kit de développement logiciel (SDK) Open XML Description
<workbook/> DocumentFormat.OpenXML.Spreadsheet.Workbook Élément racine du composant document principal.
<sheets/> DocumentFormat.OpenXML.Spreadsheet.Sheets Conteneur des structures de niveau bloc comme les éléments de feuille, fileVersion et autres spécifiés par la norme ISO/IEC 29500.
<sheet/> DocumentFormat.OpenXml.Spreadsheet.Sheet Feuille qui pointe vers un fichier de définition de feuille.
<worksheet/> DocumentFormat.OpenXML.Spreadsheet. Feuilles de calcul Fichier de définition de feuille qui contient les données de feuille.
<sheetData/> DocumentFormat.OpenXML.Spreadsheet.SheetData Tableau de cellules, regroupées par lignes.
<row/> DocumentFormat.OpenXml.Spreadsheet.Row Ligne dans le tableau de cellules.
<c/> DocumentFormat.OpenXml.Spreadsheet.Cell Cellule d'une ligne.
<v/> DocumentFormat.OpenXml.Spreadsheet.CellValue Valeur d’une cellule.

Fonctionnement de l’exemple de code

L’exemple de code commence par passer à la méthode CalculateSumOfCellRange un paramètre qui représente le chemin complet du fichier source SpreadsheetML , un paramètre qui représente le nom de la feuille de calcul qui contient les cellules, un paramètre qui représente le nom de la première cellule de la plage contiguë, un paramètre qui représente le nom de la dernière cellule de la plage contiguë, et un paramètre qui représente le nom de la cellule dans laquelle vous souhaitez afficher le résultat.

Le code ouvre ensuite le fichier pour modification en tant que SpreadsheetDocument package de document pour l’accès en lecture/écriture, le code obtient l’objet spécifié Worksheet . Il obtient ensuite l’index de la ligne pour la première et la dernière cellule de la plage contiguë en appelant la GetRowIndex méthode . Il obtient le nom de la colonne pour la première et la dernière cellule de la plage contiguë en appelant la GetColumnName méthode .

Pour chaque Row objet de la plage contiguë, le code itère au sein de chaque Cell objet et détermine si la colonne de la cellule se trouve dans la plage contiguë en appelant la CompareColumn méthode . Si la cellule est dans la plage contiguë, le code ajoute la valeur de la cellule à la somme. Ensuite, il obtient l’objet SharedStringTablePart s’il existe. S’il n’existe pas, il en crée un à l’aide de la AddNewPart méthode . Il insère le résultat dans l’objet SharedStringTablePart en appelant la InsertSharedStringItem méthode .

Le code insère une nouvelle cellule pour le résultat dans la feuille de calcul en appelant la InsertCellInWorksheet méthode et définit la valeur de la cellule. Pour plus d’informations, consultez comment insérer une cellule dans une feuille de calcul.

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

Pour obtenir l’index de la ligne, le code passe un paramètre qui représente le nom de la cellule, puis crée une expression régulière pour trouver la partie index de ligne du nom de la cellule. Pour en savoir plus sur les expressions régulières, consultez l’article Langage des expressions régulières - Aide-mémoire. Il obtient l’index de ligne en appelant la Match méthode , puis retourne l’index de ligne.

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

Le code obtient ensuite le nom de la colonne en passant un paramètre qui représente le nom de la cellule, puis crée une expression régulière pour trouver la partie nom de la colonne du nom de la cellule. Cette expression régulière correspond à toute combinaison de lettres majuscules et minuscules. Il obtient le nom de colonne en appelant la Match méthode , puis retourne le nom de la colonne.

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

Pour comparer deux colonnes, le code passe deux paramètres qui représentent les colonnes à comparer. Si la première colonne est plus longue que la seconde, il retourne 1. Si la seconde colonne est plus longue que la première, il retourne -1. Sinon, il compare les valeurs des colonnes à l’aide de et Compare retourne le résultat.

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

Pour insérer un SharedStringItem, le code passe un paramètre qui représente le texte à insérer dans la cellule et un paramètre qui représente l’objet SharedStringTablePart de la feuille de calcul. Si l’objet ShareStringTablePart ne contient pas d’objet SharedStringTable , il en crée un. Si le texte existe déjà dans l’objet ShareStringTable , il retourne l’index de l’objet SharedStringItem qui représente le texte. Si le texte n’existe pas, créez un SharedStringItem objet qui représente le texte. Elle retourne ensuite l’index de l’objet SharedStringItem qui représente le texte.

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

L’étape finale consiste à insérer une cellule dans la feuille de calcul. Pour cela, le code passe des paramètres qui représentent le nom de la colonne et le numéro de ligne de la cellule, et un paramètre qui représente la feuille de calcul contenant la cellule. Si la ligne spécifiée n’existe pas, il la crée et l’ajoute à la feuille de calcul. Si la colonne spécifiée existe, il trouve la cellule qui correspond à la ligne dans cette colonne et retourne cette cellule. Si la colonne spécifiée n’existe pas, il la crée et l’insère dans la feuille de calcul. Il détermine ensuite l’emplacement où insérer la nouvelle cellule dans la colonne en itérant les éléments ligne afin de trouver la cellule située juste après la ligne spécifiée, dans l’ordre séquentiel. Cette ligne est enregistrée dans la refCell variable . Il insère la nouvelle cellule avant la cellule référencée refCell à l’aide de la InsertBefore méthode . Elle retourne ensuite le nouvel Cell objet.

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

Exemple de code

Voici un exemple de code complet en C# et 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;
    }
}

Voir aussi