Supprimer du texte d’une cellule d’un document de feuille de calcul

Cette rubrique montre comment utiliser les classes du Kit de développement logiciel (SDK) Open XML pour Office afin de supprimer par programmation du texte d’une cellule d’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

Dans l’exemple de code suivant, vous supprimez du texte d’une cellule d’un SpreadsheetDocument package de documents. Ensuite, vous vérifiez si d’autres cellules du document de feuille de calcul font toujours référence au texte supprimé de la ligne, et si ce n’est pas le cas, vous supprimez le texte de l’objet à l’aide SharedStringTablePart de la Remove méthode . Ensuite, vous propre l’objet SharedStringTablePart en appelant la RemoveSharedStringItem méthode .

C#

// Given a document, a worksheet name, a column name, and a one-based row index,
// deletes the text from the cell at the specified column and row on the specified worksheet.
static void DeleteTextFromCell(string docName, string sheetName, string colName, uint rowIndex)
{
    // Open the document for editing.
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
    {
        IEnumerable<Sheet>? sheets = document.WorkbookPart?.Workbook?.GetFirstChild<Sheets>()?.Elements<Sheet>()?.Where(s => s.Name is not null && s.Name == sheetName);
        if (sheets is null || sheets.Count() == 0)
        {
            // The specified worksheet does not exist.
            return;
        }
        string? relationshipId = sheets.First()?.Id?.Value;

        if (relationshipId is null)
        {
            // The worksheet does not have a relationship ID.
            return;
        }

        WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart!.GetPartById(relationshipId);

        // Get the cell at the specified column and row.
        Cell? cell = GetSpreadsheetCell(worksheetPart.Worksheet, colName, rowIndex);
        if (cell is null)
        {
            // The specified cell does not exist.
            return;
        }

        cell.Remove();
    }
}

Visual Basic

' Given a document, a worksheet name, a column name, and a one-based row index,
' deletes the text from the cell at the specified column and row on the specified worksheet.
Sub DeleteTextFromCell(docName As String, sheetName As String, colName As String, rowIndex As UInteger)
    ' Open the document for editing.
    Using document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)
        Dim sheets As IEnumerable(Of Sheet) = document.WorkbookPart?.Workbook?.GetFirstChild(Of Sheets)()?.Elements(Of Sheet)()?.Where(Function(s) s.Name IsNot Nothing AndAlso s.Name = sheetName)
        If sheets Is Nothing OrElse sheets.Count() = 0 Then
            ' The specified worksheet does not exist.
            Return
        End If
        Dim relationshipId As String = sheets.First()?.Id?.Value

        If relationshipId Is Nothing Then
            ' The worksheet does not have a relationship ID.
            Return
        End If

        Dim worksheetPart As WorksheetPart = CType(document.WorkbookPart.GetPartById(relationshipId), WorksheetPart)

        ' Get the cell at the specified column and row.
        Dim cell As Cell = GetSpreadsheetCell(worksheetPart.Worksheet, colName, rowIndex)
        If cell Is Nothing Then
            ' The specified cell does not exist.
            Return
        End If

        cell.Remove()
    End Using
End Sub

Dans l’exemple de code suivant, vous vérifiez que la cellule spécifiée par le nom de colonne et l’index de ligne existe. Si c’est le cas, le code retourne la cellule ; sinon, elle retourne null.

C#

// Given a worksheet, a column name, and a row index, gets the cell at the specified column and row.
static Cell? GetSpreadsheetCell(Worksheet worksheet, string columnName, uint rowIndex)
{
    IEnumerable<Row>? rows = worksheet.GetFirstChild<SheetData>()?.Elements<Row>().Where(r => r.RowIndex is not null && r.RowIndex == rowIndex);
    if (rows is null || rows.Count() == 0)
    {
        // A cell does not exist at the specified row.
        return null;
    }

    IEnumerable<Cell> cells = rows.First().Elements<Cell>().Where(c => string.Compare(c.CellReference?.Value, columnName + rowIndex, true) == 0);

    if (cells.Count() == 0)
    {
        // A cell does not exist at the specified column, in the specified row.
        return null;
    }

    return cells.FirstOrDefault();
}

Visual Basic

' Given a worksheet, a column name, and a row index, gets the cell at the specified column and row.
Function GetSpreadsheetCell(worksheet As Worksheet, columnName As String, rowIndex As UInteger) As Cell
    Dim rows As IEnumerable(Of Row) = worksheet.GetFirstChild(Of SheetData)()?.Elements(Of Row)().Where(Function(r) r.RowIndex IsNot Nothing AndAlso r.RowIndex.Equals(rowIndex))
    If rows Is Nothing OrElse rows.Count() = 0 Then
        ' A cell does not exist at the specified row.
        Return Nothing
    End If

    Dim cells As IEnumerable(Of Cell) = rows.First().Elements(Of Cell)().Where(Function(c) String.Compare(c.CellReference?.Value, columnName & rowIndex, True) = 0)

    If cells.Count() = 0 Then
        ' A cell does not exist at the specified column, in the specified row.
        Return Nothing
    End If

    Return cells.FirstOrDefault()
End Function

Dans l’exemple de code suivant, vous vérifiez si d’autres cellules du document de feuille de calcul référencent le texte spécifié par le shareStringId paramètre . S’ils ne font pas référence au texte, vous le supprimez de l’objet SharedStringTablePart . Pour ce faire, transmettez un paramètre qui représente l’ID du texte à supprimer et un paramètre qui représente le package de SpreadsheetDocument document. Ensuite, vous effectuez une itération au sein de chaque Worksheet objet et comparez le contenu de chaque Cell objet à l’ID de chaîne partagée. Si d’autres cellules du document de feuille de calcul font toujours référence à l’objet SharedStringItem , vous ne supprimez pas l’élément de l’objet SharedStringTablePart . Si d’autres cellules du document de feuille de calcul ne font plus référence à l’objet SharedStringItem , vous supprimez l’élément de l’objet SharedStringTablePart . Ensuite, vous effectuez une itération au sein de chaque Worksheet objet et objet et Cell actualisez les références de chaîne partagée.

C#

// Given a shared string ID and a SpreadsheetDocument, verifies that other cells in the document no longer 
// reference the specified SharedStringItem and removes the item.
static void RemoveSharedStringItem(int shareStringId, SpreadsheetDocument document)
{
    bool remove = true;

    if (document.WorkbookPart is null)
    {
        return;
    }

    foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())
    {
        var cells = part.Worksheet.GetFirstChild<SheetData>()?.Descendants<Cell>();

        if (cells is null)
        {
            continue;
        }

        foreach (var cell in cells)
        {
            // Verify if other cells in the document reference the item.
            if (cell.DataType is not null &&
                cell.DataType.Value == CellValues.SharedString &&
                cell.CellValue?.Text == shareStringId.ToString())
            {
                // Other cells in the document still reference the item. Do not remove the item.
                remove = false;
                break;
            }
        }

        if (!remove)
        {
            break;
        }
    }

    // Other cells in the document do not reference the item. Remove the item.
    if (remove)
    {
        SharedStringTablePart? shareStringTablePart = document.WorkbookPart.SharedStringTablePart;

        if (shareStringTablePart is null)
        {
            return;
        }

        SharedStringItem item = shareStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(shareStringId);
        if (item is not null)
        {
            item.Remove();

            // Refresh all the shared string references.
            foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())
            {
                var cells = part.Worksheet.GetFirstChild<SheetData>()?.Descendants<Cell>();

                if (cells is null)
                {
                    continue;
                }

                foreach (var cell in cells)
                {
                    if (cell.DataType is not null && cell.DataType.Value == CellValues.SharedString && int.TryParse(cell.CellValue?.Text, out int itemIndex))
                    {
                        if (itemIndex > shareStringId)
                        {
                            cell.CellValue.Text = (itemIndex - 1).ToString();
                        }
                    }
                }
            }
        }
    }
}

Visual Basic

' Given a shared string ID and a SpreadsheetDocument, verifies that other cells in the document no longer 
' reference the specified SharedStringItem and removes the item.
Sub RemoveSharedStringItem(shareStringId As Integer, document As SpreadsheetDocument)
    Dim remove As Boolean = True

    If document.WorkbookPart Is Nothing Then
        Return
    End If

    For Each part In document.WorkbookPart.GetPartsOfType(Of WorksheetPart)()
        Dim cells = part.Worksheet.GetFirstChild(Of SheetData)()?.Descendants(Of Cell)()

        If cells Is Nothing Then
            Continue For
        End If

        For Each cell In cells
            ' Verify if other cells in the document reference the item.
            If cell.DataType IsNot Nothing AndAlso
               cell.DataType.Value = CellValues.SharedString AndAlso
               cell.CellValue?.Text = shareStringId.ToString() Then
                ' Other cells in the document still reference the item. Do not remove the item.
                remove = False
                Exit For
            End If
        Next

        If Not remove Then
            Exit For
        End If
    Next

    ' Other cells in the document do not reference the item. Remove the item.
    If remove Then
        Dim shareStringTablePart As SharedStringTablePart = document.WorkbookPart.SharedStringTablePart

        If shareStringTablePart Is Nothing Then
            Return
        End If

        Dim item As SharedStringItem = shareStringTablePart.SharedStringTable.Elements(Of SharedStringItem)().ElementAt(shareStringId)
        If item IsNot Nothing Then
            item.Remove()

            ' Refresh all the shared string references.
            For Each part In document.WorkbookPart.GetPartsOfType(Of WorksheetPart)()
                Dim cells = part.Worksheet.GetFirstChild(Of SheetData)()?.Descendants(Of Cell)()

                If cells Is Nothing Then
                    Continue For
                End If

                For Each cell In cells
                    Dim itemIndex As Integer

                    If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString AndAlso Integer.TryParse(cell.CellValue?.Text, itemIndex) Then
                        If itemIndex > shareStringId Then
                            cell.CellValue.Text = (itemIndex - 1).ToString()
                        End If
                    End If
                Next
            Next
        End If
    End If
End Sub

Exemple de code

Voici un exemple de code complet en C# et Visual Basic.

C#

static void DeleteTextFromCell(string docName, string sheetName, string colName, uint rowIndex)
{
    // Open the document for editing.
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
    {
        IEnumerable<Sheet>? sheets = document.WorkbookPart?.Workbook?.GetFirstChild<Sheets>()?.Elements<Sheet>()?.Where(s => s.Name is not null && s.Name == sheetName);
        if (sheets is null || sheets.Count() == 0)
        {
            // The specified worksheet does not exist.
            return;
        }
        string? relationshipId = sheets.First()?.Id?.Value;

        if (relationshipId is null)
        {
            // The worksheet does not have a relationship ID.
            return;
        }

        WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart!.GetPartById(relationshipId);

        // Get the cell at the specified column and row.
        Cell? cell = GetSpreadsheetCell(worksheetPart.Worksheet, colName, rowIndex);
        if (cell is null)
        {
            // The specified cell does not exist.
            return;
        }

        cell.Remove();
    }
}

// Given a worksheet, a column name, and a row index, gets the cell at the specified column and row.
static Cell? GetSpreadsheetCell(Worksheet worksheet, string columnName, uint rowIndex)
{
    IEnumerable<Row>? rows = worksheet.GetFirstChild<SheetData>()?.Elements<Row>().Where(r => r.RowIndex is not null && r.RowIndex == rowIndex);
    if (rows is null || rows.Count() == 0)
    {
        // A cell does not exist at the specified row.
        return null;
    }

    IEnumerable<Cell> cells = rows.First().Elements<Cell>().Where(c => string.Compare(c.CellReference?.Value, columnName + rowIndex, true) == 0);

    if (cells.Count() == 0)
    {
        // A cell does not exist at the specified column, in the specified row.
        return null;
    }

    return cells.FirstOrDefault();
}

// Given a shared string ID and a SpreadsheetDocument, verifies that other cells in the document no longer 
// reference the specified SharedStringItem and removes the item.
static void RemoveSharedStringItem(int shareStringId, SpreadsheetDocument document)
{
    bool remove = true;

    if (document.WorkbookPart is null)
    {
        return;
    }

    foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())
    {
        var cells = part.Worksheet.GetFirstChild<SheetData>()?.Descendants<Cell>();

        if (cells is null)
        {
            continue;
        }

        foreach (var cell in cells)
        {
            // Verify if other cells in the document reference the item.
            if (cell.DataType is not null &&
                cell.DataType.Value == CellValues.SharedString &&
                cell.CellValue?.Text == shareStringId.ToString())
            {
                // Other cells in the document still reference the item. Do not remove the item.
                remove = false;
                break;
            }
        }

        if (!remove)
        {
            break;
        }
    }

    // Other cells in the document do not reference the item. Remove the item.
    if (remove)
    {
        SharedStringTablePart? shareStringTablePart = document.WorkbookPart.SharedStringTablePart;

        if (shareStringTablePart is null)
        {
            return;
        }

        SharedStringItem item = shareStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(shareStringId);
        if (item is not null)
        {
            item.Remove();

            // Refresh all the shared string references.
            foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())
            {
                var cells = part.Worksheet.GetFirstChild<SheetData>()?.Descendants<Cell>();

                if (cells is null)
                {
                    continue;
                }

                foreach (var cell in cells)
                {
                    if (cell.DataType is not null && cell.DataType.Value == CellValues.SharedString && int.TryParse(cell.CellValue?.Text, out int itemIndex))
                    {
                        if (itemIndex > shareStringId)
                        {
                            cell.CellValue.Text = (itemIndex - 1).ToString();
                        }
                    }
                }
            }
        }
    }
}

Visual Basic

Sub DeleteTextFromCell(docName As String, sheetName As String, colName As String, rowIndex As UInteger)
    ' Open the document for editing.
    Using document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)
        Dim sheets As IEnumerable(Of Sheet) = document.WorkbookPart?.Workbook?.GetFirstChild(Of Sheets)()?.Elements(Of Sheet)()?.Where(Function(s) s.Name IsNot Nothing AndAlso s.Name = sheetName)
        If sheets Is Nothing OrElse sheets.Count() = 0 Then
            ' The specified worksheet does not exist.
            Return
        End If
        Dim relationshipId As String = sheets.First()?.Id?.Value

        If relationshipId Is Nothing Then
            ' The worksheet does not have a relationship ID.
            Return
        End If

        Dim worksheetPart As WorksheetPart = CType(document.WorkbookPart.GetPartById(relationshipId), WorksheetPart)

        ' Get the cell at the specified column and row.
        Dim cell As Cell = GetSpreadsheetCell(worksheetPart.Worksheet, colName, rowIndex)
        If cell Is Nothing Then
            ' The specified cell does not exist.
            Return
        End If

        cell.Remove()
    End Using
End Sub

' Given a worksheet, a column name, and a row index, gets the cell at the specified column and row.
Function GetSpreadsheetCell(worksheet As Worksheet, columnName As String, rowIndex As UInteger) As Cell
    Dim rows As IEnumerable(Of Row) = worksheet.GetFirstChild(Of SheetData)()?.Elements(Of Row)().Where(Function(r) r.RowIndex IsNot Nothing AndAlso r.RowIndex.Equals(rowIndex))
    If rows Is Nothing OrElse rows.Count() = 0 Then
        ' A cell does not exist at the specified row.
        Return Nothing
    End If

    Dim cells As IEnumerable(Of Cell) = rows.First().Elements(Of Cell)().Where(Function(c) String.Compare(c.CellReference?.Value, columnName & rowIndex, True) = 0)

    If cells.Count() = 0 Then
        ' A cell does not exist at the specified column, in the specified row.
        Return Nothing
    End If

    Return cells.FirstOrDefault()
End Function

' Given a shared string ID and a SpreadsheetDocument, verifies that other cells in the document no longer 
' reference the specified SharedStringItem and removes the item.
Sub RemoveSharedStringItem(shareStringId As Integer, document As SpreadsheetDocument)
    Dim remove As Boolean = True

    If document.WorkbookPart Is Nothing Then
        Return
    End If

    For Each part In document.WorkbookPart.GetPartsOfType(Of WorksheetPart)()
        Dim cells = part.Worksheet.GetFirstChild(Of SheetData)()?.Descendants(Of Cell)()

        If cells Is Nothing Then
            Continue For
        End If

        For Each cell In cells
            ' Verify if other cells in the document reference the item.
            If cell.DataType IsNot Nothing AndAlso
               cell.DataType.Value = CellValues.SharedString AndAlso
               cell.CellValue?.Text = shareStringId.ToString() Then
                ' Other cells in the document still reference the item. Do not remove the item.
                remove = False
                Exit For
            End If
        Next

        If Not remove Then
            Exit For
        End If
    Next

    ' Other cells in the document do not reference the item. Remove the item.
    If remove Then
        Dim shareStringTablePart As SharedStringTablePart = document.WorkbookPart.SharedStringTablePart

        If shareStringTablePart Is Nothing Then
            Return
        End If

        Dim item As SharedStringItem = shareStringTablePart.SharedStringTable.Elements(Of SharedStringItem)().ElementAt(shareStringId)
        If item IsNot Nothing Then
            item.Remove()

            ' Refresh all the shared string references.
            For Each part In document.WorkbookPart.GetPartsOfType(Of WorksheetPart)()
                Dim cells = part.Worksheet.GetFirstChild(Of SheetData)()?.Descendants(Of Cell)()

                If cells Is Nothing Then
                    Continue For
                End If

                For Each cell In cells
                    Dim itemIndex As Integer

                    If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString AndAlso Integer.TryParse(cell.CellValue?.Text, itemIndex) Then
                        If itemIndex > shareStringId Then
                            cell.CellValue.Text = (itemIndex - 1).ToString()
                        End If
                    End If
                Next
            Next
        End If
    End If
End Sub

Voir aussi

Informations de référence sur la bibliothèque de classes du Kit de développement logiciel (SDK) Open XML