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.