Mise à jour des sous-totaux dans les fichiers Excel

Question

J'utilise la bibliothèque OpenXML pour prendre une table de données et la coller dans un fichier Excel préformaté. Cela fonctionne bien.

Le problème que je rencontre est qu'il y a une ligne de sous-total en haut du fichier Excel préformaté, qui est configurée pour faire un sous-total de chaque colonne de ces données (il y a donc un sous-total en haut de chaque colonne). Lorsque j'ouvre le fichier Excel après sa création, ces valeurs sont toutes définies sur 0, elles n'ont pas été mises à jour lorsque le tableau de données a été inséré. Si vous mettez en surbrillance l'une de ces cellules de sous-total et que vous appuyez sur la touche Entrée, elle se met à jour et affiche la bonne valeur.

Quel est le moyen le plus simple pour que ces valeurs soient mises à jour et affichent la valeur correcte dès que l'utilisateur ouvre la feuille de calcul téléchargée ?

Code pour la création de la feuille de calcul :

MemoryStream memoryStream = SpreadsheetReader.StreamFromFile(TemplateDirectory + @"\" + "exceltTemplate.xlsx");
doc = SpreadsheetDocument.Open(memoryStream, true);
worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, currentSheetName);
writer = new WorksheetWriter(doc, worksheetPart);
cellName = "A8";
writer.PasteDataTable(reports.Tables[0], cellName);
SpreadsheetWriter.Save(doc);

Answer 1

La bibliothèque OpenXML ne recalcule pas les résultats des formules. Une façon de résoudre ce problème est de supprimer les valeurs (et non les formules) de vos cellules de sous-total. Cela permet à Excel d'effectuer un nouveau calcul automatique.

Ce lien l'explique plus en détail. À peu près au milieu de la page, vous verrez le code suivant, qui est la partie importante pour vos besoins :

// remove all values of cells with formulas on a sheet
// so that Excel refreshes them upon Open
public static void ClearAllValuesInSheet
      (SpreadsheetDocument spreadSheet, string sheetName)
{
    WorksheetPart worksheetPart =
        GetWorksheetPartByName(spreadSheet, sheetName);

    foreach (Row row in
       worksheetPart.Worksheet.
          GetFirstChild().Elements())
    {
    foreach (Cell cell in row.Elements())
    {
        if (cell.CellFormula != null &&
              cell.CellValue != null)
        {
        cell.CellValue.Remove();
        }
    }

    }

    worksheetPart.Worksheet.Save();
}

Ce code supprime les valeurs de toutes les cellules qui contiennent des formules et des valeurs. Pour accélérer le processus, vous pouvez le personnaliser assez facilement pour ne traiter que les cellules de sous-total.