Insérer du texte dans 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 d’insérer du texte dans une cellule d’une nouvelle feuille de calcul d’un document de feuille de calcul par programmation.

---

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

Après avoir ouvert le SpreadsheetDocument document pour modification, le code insère un objet vide Worksheet dans un SpreadsheetDocument package de documents. Ensuite, insère un nouvel Cell objet dans la nouvelle feuille de calcul et insère le texte spécifié dans cette cellule.

C#

// Given a document name and text, 
// inserts a new work sheet and writes the text to cell "A1" of the new worksheet.
static void InsertText(string docName, string text)
{
    // Open the document for editing.
    using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
    {
        WorkbookPart workbookPart = spreadSheet.WorkbookPart ?? spreadSheet.AddWorkbookPart();

        // Get the SharedStringTablePart. If it does not exist, create a new one.
        SharedStringTablePart shareStringPart;
        if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
        {
            shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
        }
        else
        {
            shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
        }

        // Insert the text into the SharedStringTablePart.
        int index = InsertSharedStringItem(text, shareStringPart);

        // Insert a new worksheet.
        WorksheetPart worksheetPart = InsertWorksheet(workbookPart);

        // Insert cell A1 into the new worksheet.
        Cell cell = InsertCellInWorksheet("A", 1, worksheetPart);

        // Set the value of cell A1.
        cell.CellValue = new CellValue(index.ToString());
        cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
    }
}

Visual Basic

' Given a document name and text, 
' inserts a new work sheet and writes the text to cell "A1" of the new worksheet.
Sub InsertText(docName As String, text As String)
    ' Open the document for editing.
    Using spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)
        Dim workbookPart As WorkbookPart = If(spreadSheet.WorkbookPart, spreadSheet.AddWorkbookPart())

        ' Get the SharedStringTablePart. If it does not exist, create a new one.
        Dim shareStringPart As SharedStringTablePart
        If workbookPart.GetPartsOfType(Of SharedStringTablePart)().Count() > 0 Then
            shareStringPart = workbookPart.GetPartsOfType(Of SharedStringTablePart)().First()
        Else
            shareStringPart = workbookPart.AddNewPart(Of SharedStringTablePart)()
        End If

        ' Insert the text into the SharedStringTablePart.
        Dim index As Integer = InsertSharedStringItem(text, shareStringPart)

        ' Insert a new worksheet.
        Dim worksheetPart As WorksheetPart = InsertWorksheet(workbookPart)

        ' Insert cell A1 into the new worksheet.
        Dim cell As Cell = InsertCellInWorksheet("A", 1, worksheetPart)

        ' Set the value of cell A1.
        cell.CellValue = New CellValue(index.ToString())
        cell.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)
    End Using
End Sub

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 , le code en crée un. Si le texte existe déjà dans l’objet ShareStringTable , le code retourne l’index de l’objet SharedStringItem qui représente le texte. Sinon, il crée un objet SharedStringItem qui représente le texte.

Le code suivant vérifie si le texte spécifié existe dans l’objet SharedStringTablePart et ajoute le texte s’il n’existe pas.

C#

// Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
// and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
    // If the part does not contain a SharedStringTable, create one.
    shareStringPart.SharedStringTable ??= new SharedStringTable();

    int i = 0;

    // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
    foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
    {
        if (item.InnerText == text)
        {
            return i;
        }

        i++;
    }

    // The text does not exist in the part. Create the SharedStringItem and return its index.
    shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));

    return i;
}

Visual Basic

' Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
' and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
Function InsertSharedStringItem(text As String, shareStringPart As SharedStringTablePart) As Integer
    ' If the part does not contain a SharedStringTable, create one.
    If shareStringPart.SharedStringTable Is Nothing Then
        shareStringPart.SharedStringTable = New SharedStringTable()
    End If

    Dim i As Integer = 0

    ' Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
    For Each item As SharedStringItem In shareStringPart.SharedStringTable.Elements(Of SharedStringItem)()
        If item.InnerText = text Then
            Return i
        End If

        i += 1
    Next

    ' The text does not exist in the part. Create the SharedStringItem and return its index.
    shareStringPart.SharedStringTable.AppendChild(New SharedStringItem(New DocumentFormat.OpenXml.Spreadsheet.Text(text)))

    Return i
End Function

Le code ajoute un nouvel WorksheetPart objet à l’objet à l’aide WorkbookPart de la AddNewPart méthode . Il ajoute ensuite un nouvel Worksheet objet à l’objet WorksheetPart et obtient un ID unique pour la nouvelle feuille de calcul en sélectionnant l’objet maximal SheetId utilisé dans le document de feuille de calcul et en ajoutant un objet pour créer le nouvel ID de feuille. Il donne à la feuille de calcul un nom en concaténant le mot « Sheet » avec l'ID de feuille. Il ajoute ensuite le nouvel Sheet objet à la Sheets collection.

Le code suivant insère un nouvel Worksheet objet en ajoutant un nouvel WorksheetPart objet à l’objet WorkbookPart .

C#

// Given a WorkbookPart, inserts a new worksheet.
static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
{
    // Add a new worksheet part to the workbook.
    WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    newWorksheetPart.Worksheet = new Worksheet(new SheetData());

    Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>() ?? workbookPart.Workbook.AppendChild(new Sheets());
    string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

    // Get a unique ID for the new sheet.
    uint sheetId = 1;
    if (sheets.Elements<Sheet>().Count() > 0)
    {
        sheetId = sheets.Elements<Sheet>().Select<Sheet, uint>(s =>
        {
            if (s.SheetId is not null && s.SheetId.HasValue)
            {
                return s.SheetId.Value;
            }

            return 0;
        }).Max() + 1;
    }

    string sheetName = "Sheet" + sheetId;

    // Append the new worksheet and associate it with the workbook.
    Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
    sheets.Append(sheet);

    return newWorksheetPart;
}

Visual Basic

' Given a WorkbookPart, inserts a new worksheet.
Function InsertWorksheet(workbookPart As WorkbookPart) As WorksheetPart
    ' Add a new worksheet part to the workbook.
    Dim newWorksheetPart As WorksheetPart = workbookPart.AddNewPart(Of WorksheetPart)()
    newWorksheetPart.Worksheet = New Worksheet(New SheetData())

    Dim sheets As Sheets = If(workbookPart.Workbook.GetFirstChild(Of Sheets)(), workbookPart.Workbook.AppendChild(New Sheets()))
    Dim relationshipId As String = workbookPart.GetIdOfPart(newWorksheetPart)

    ' Get a unique ID for the new sheet.
    Dim sheetId As UInteger = 1
    If sheets.Elements(Of Sheet)().Count() > 0 Then
        sheetId = sheets.Elements(Of Sheet)().Select(Function(s)
                                                         If s.SheetId IsNot Nothing AndAlso s.SheetId.HasValue Then
                                                             Return s.SheetId.Value
                                                         End If

                                                         Return 0
                                                     End Function).Max() + 1
    End If

    Dim sheetName As String = "Sheet" & sheetId

    ' Append the new worksheet and associate it with the workbook.
    Dim sheet As New Sheet() With {
        .Id = relationshipId,
        .SheetId = sheetId,
        .Name = sheetName
    }
    sheets.Append(sheet)

    Return newWorksheetPart
End Function

Pour insérer une cellule dans une feuille de calcul, le code détermine où insérer la nouvelle cellule dans la colonne en itérant les éléments de ligne afin de trouver la cellule qui suit directement la ligne spécifiée, dans un ordre séquentiel. Il enregistre cette ligne dans la refCell variable. Il insère ensuite la nouvelle cellule avant la cellule référencée refCell à l’aide de la InsertBefore méthode .

Dans le code suivant, insérez un nouvel Cell objet dans un Worksheet objet .

C#

// Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
// If the cell already exists, returns it. 
static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
    Worksheet worksheet = worksheetPart.Worksheet;
    SheetData? sheetData = worksheet.GetFirstChild<SheetData>();
    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;
    }
}

Visual Basic

' Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
' If the cell already exists, returns it. 
Function InsertCellInWorksheet(columnName As String, rowIndex As UInteger, 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 IsNot Nothing AndAlso r.RowIndex.Equals(rowIndex)).Count() <> 0 Then
        row = sheetData.Elements(Of Row)().Where(Function(r) r.RowIndex IsNot Nothing AndAlso r.RowIndex.Equals(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 IsNot Nothing AndAlso c.CellReference.Value = columnName & rowIndex).Count() > 0 Then
        Return row.Elements(Of Cell)().Where(Function(c) c.CellReference IsNot Nothing AndAlso 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 New Cell() With {
            .CellReference = cellReference
        }
        row.InsertBefore(newCell, refCell)

        Return newCell
    End If
End Function

---

Exemple de code

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

C#

static void InsertText(string docName, string text)
{
    // Open the document for editing.
    using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
    {
        WorkbookPart workbookPart = spreadSheet.WorkbookPart ?? spreadSheet.AddWorkbookPart();

        // Get the SharedStringTablePart. If it does not exist, create a new one.
        SharedStringTablePart shareStringPart;
        if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
        {
            shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
        }
        else
        {
            shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
        }

        // Insert the text into the SharedStringTablePart.
        int index = InsertSharedStringItem(text, shareStringPart);

        // Insert a new worksheet.
        WorksheetPart worksheetPart = InsertWorksheet(workbookPart);

        // Insert cell A1 into the new worksheet.
        Cell cell = InsertCellInWorksheet("A", 1, worksheetPart);

        // Set the value of cell A1.
        cell.CellValue = new CellValue(index.ToString());
        cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
    }
}

// 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 one.
    shareStringPart.SharedStringTable ??= new SharedStringTable();

    int i = 0;

    // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
    foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
    {
        if (item.InnerText == text)
        {
            return i;
        }

        i++;
    }

    // The text does not exist in the part. Create the SharedStringItem and return its index.
    shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));

    return i;
}

// Given a WorkbookPart, inserts a new worksheet.
static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
{
    // Add a new worksheet part to the workbook.
    WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    newWorksheetPart.Worksheet = new Worksheet(new SheetData());

    Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>() ?? workbookPart.Workbook.AppendChild(new Sheets());
    string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

    // Get a unique ID for the new sheet.
    uint sheetId = 1;
    if (sheets.Elements<Sheet>().Count() > 0)
    {
        sheetId = sheets.Elements<Sheet>().Select<Sheet, uint>(s =>
        {
            if (s.SheetId is not null && s.SheetId.HasValue)
            {
                return s.SheetId.Value;
            }

            return 0;
        }).Max() + 1;
    }

    string sheetName = "Sheet" + sheetId;

    // Append the new worksheet and associate it with the workbook.
    Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
    sheets.Append(sheet);

    return newWorksheetPart;
}


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

Visual Basic

Sub InsertText(docName As String, text As String)
    ' Open the document for editing.
    Using spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)
        Dim workbookPart As WorkbookPart = If(spreadSheet.WorkbookPart, spreadSheet.AddWorkbookPart())

        ' Get the SharedStringTablePart. If it does not exist, create a new one.
        Dim shareStringPart As SharedStringTablePart
        If workbookPart.GetPartsOfType(Of SharedStringTablePart)().Count() > 0 Then
            shareStringPart = workbookPart.GetPartsOfType(Of SharedStringTablePart)().First()
        Else
            shareStringPart = workbookPart.AddNewPart(Of SharedStringTablePart)()
        End If

        ' Insert the text into the SharedStringTablePart.
        Dim index As Integer = InsertSharedStringItem(text, shareStringPart)

        ' Insert a new worksheet.
        Dim worksheetPart As WorksheetPart = InsertWorksheet(workbookPart)

        ' Insert cell A1 into the new worksheet.
        Dim cell As Cell = InsertCellInWorksheet("A", 1, worksheetPart)

        ' Set the value of cell A1.
        cell.CellValue = New CellValue(index.ToString())
        cell.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)
    End Using
End Sub

' 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.
Function InsertSharedStringItem(text As String, shareStringPart As SharedStringTablePart) As Integer
    ' If the part does not contain a SharedStringTable, create one.
    If shareStringPart.SharedStringTable Is Nothing Then
        shareStringPart.SharedStringTable = New SharedStringTable()
    End If

    Dim i As Integer = 0

    ' Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
    For Each item As SharedStringItem In shareStringPart.SharedStringTable.Elements(Of SharedStringItem)()
        If item.InnerText = text Then
            Return i
        End If

        i += 1
    Next

    ' The text does not exist in the part. Create the SharedStringItem and return its index.
    shareStringPart.SharedStringTable.AppendChild(New SharedStringItem(New DocumentFormat.OpenXml.Spreadsheet.Text(text)))

    Return i
End Function

' Given a WorkbookPart, inserts a new worksheet.
Function InsertWorksheet(workbookPart As WorkbookPart) As WorksheetPart
    ' Add a new worksheet part to the workbook.
    Dim newWorksheetPart As WorksheetPart = workbookPart.AddNewPart(Of WorksheetPart)()
    newWorksheetPart.Worksheet = New Worksheet(New SheetData())

    Dim sheets As Sheets = If(workbookPart.Workbook.GetFirstChild(Of Sheets)(), workbookPart.Workbook.AppendChild(New Sheets()))
    Dim relationshipId As String = workbookPart.GetIdOfPart(newWorksheetPart)

    ' Get a unique ID for the new sheet.
    Dim sheetId As UInteger = 1
    If sheets.Elements(Of Sheet)().Count() > 0 Then
        sheetId = sheets.Elements(Of Sheet)().Select(Function(s)
                                                         If s.SheetId IsNot Nothing AndAlso s.SheetId.HasValue Then
                                                             Return s.SheetId.Value
                                                         End If

                                                         Return 0
                                                     End Function).Max() + 1
    End If

    Dim sheetName As String = "Sheet" & sheetId

    ' Append the new worksheet and associate it with the workbook.
    Dim sheet As New Sheet() With {
        .Id = relationshipId,
        .SheetId = sheetId,
        .Name = sheetName
    }
    sheets.Append(sheet)

    Return newWorksheetPart
End Function


' Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
' If the cell already exists, returns it. 
Function InsertCellInWorksheet(columnName As String, rowIndex As UInteger, 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 IsNot Nothing AndAlso r.RowIndex.Equals(rowIndex)).Count() <> 0 Then
        row = sheetData.Elements(Of Row)().Where(Function(r) r.RowIndex IsNot Nothing AndAlso r.RowIndex.Equals(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 IsNot Nothing AndAlso c.CellReference.Value = columnName & rowIndex).Count() > 0 Then
        Return row.Elements(Of Cell)().Where(Function(c) c.CellReference IsNot Nothing AndAlso 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 New Cell() With {
            .CellReference = cellReference
        }
        row.InsertBefore(newCell, refCell)

        Return newCell
    End If
End Function

Voir aussi

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