EPP Plus appending previous sheet data to a new sheet

35 views Asked by At

I'm writing into an excel file using EPP plus, Basically I have 10 different dictionaries which are in foreach loop. I add one new sheet for each dictionary. When I add first sheet and print data it works well. When I add a next sheet, and add the data it also includes previous sheet data.

How to avoid this?

foreach (var (field, index) in fieldsList.WithIndex())
{
    var fieldName = field.Key;
    var fieldValue = field.Value.Value.AsDictionary();
    foreach (var item in fieldValue)
    {
        var internalValue = item.Value.Value.AsDictionary();
        var jsonData = JsonConvert.SerializeObject(internalValue, Formatting.Indented);
        var customFieldClasses = JsonConvert.DeserializeObject<CustomFieldClasses>(jsonData);
        var customFieldDictionary = ObjectToDictionaryConverter.ConvertToDictionary(customFieldClasses);

        foreach (var kvp in customFieldDictionary)
        {
            string customKey = kvp.Key;
            string customValue = (kvp.Value != null) ? kvp.Value.Content : "";
            if (!keyValuePairs.ContainsKey(customKey))
            {
                keyValuePairs[customKey] = new List<string>();
            }
            keyValuePairs[customKey].Add(customValue);
        }
    }
    ExportToExcel(keyValuePairs, excelFilePath, "DataPage" + index.ToString());
}
static void ExportToExcel(Dictionary<string, List<string>> data, string filePath, string sheetName)
{
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
    // Create a new Excel package
    using (var package = new ExcelPackage(filePath))
    {
        ExcelWorksheet worksheet;
        var existingWorksheet = package.Workbook.Worksheets.FirstOrDefault(ws => ws.Name == sheetName);
        // Add a worksheet to the package
        if (existingWorksheet == null)
        {
            worksheet = package.Workbook.Worksheets.Add(sheetName);
        }
        else
        {
            worksheet = existingWorksheet;
        }
        // Populate the worksheet with data from the dictionary
        
        int row = 1;
        int subRow = 1;
        int column = 1;
        foreach (var kvp in data)
        {
            worksheet.Cells[row, column].Value = kvp.Key;
            foreach (var item in kvp.Value)
            {
                subRow++;
                worksheet.Cells[subRow, column].Value = item;
            }
            column++;
            subRow = 1;
        }
        package.Save();
    }
}

I tried using both package.Save() and SaveAs, both work the same.

0

There are 0 answers