thevan thevan - 2 months ago 31
ASP.NET (C#) Question

Swap worksheets within the excel workbook c#

For example, see the image

enter image description here

I want to swap the worksheet "Sheet1" to "Sheet3".

My Code using EPPlus:

ExcelPackage masterPackage = new ExcelPackage();
foreach (var file in files)
{
ExcelPackage pckg = new ExcelPackage(new FileInfo(file));

foreach (var sheet in pckg.Workbook.Worksheets)
{
//check name of worksheet, in case that worksheet with same name already exist exception will be thrown by EPPlus

string workSheetName = sheet.Name;
foreach (var masterSheet in masterPackage.Workbook.Worksheets)
{
if (sheet.Name == masterSheet.Name)
{
workSheetName = string.Format("{0}_{1}", workSheetName, DateTime.Now.ToString("yyyyMMddhhssmmm"));
}
}

//add new sheet

if (sheet.Name.Contains("MB_STORE_POTENTIALvsWALLET"))
{
masterPackage.Workbook.Worksheets.Add(workSheetName, sheet);

}
else
{
masterPackage.Workbook.Worksheets.Add(workSheetName, sheet);
masterPackage.Workbook.Worksheets.MoveToStart(1);
}
}
}
masterPackage.SaveAs(new FileInfo(resultFile));


How to do this? Any suggestion please..

Answer

This is working fine:

ExcelPackage masterPackage = new ExcelPackage();
foreach (var file in files)
{
    ExcelPackage pckg = new ExcelPackage(new FileInfo(file));
    foreach (var sheet in pckg.Workbook.Worksheets)
    {
        //check name of worksheet, in case that worksheet with same name already exist exception will be thrown by EPPlus
        string workSheetName = sheet.Name;
        foreach (var masterSheet in masterPackage.Workbook.Worksheets)
        {
            if (sheet.Name == masterSheet.Name)
            {
                workSheetName = string.Format("{0}_{1}", workSheetName, DateTime.Now.ToString("yyyyMMddhhssmmm"));
            }
        }

        //add new sheet
        if (sheet.Name.Contains("MB_STORE_POTENTIALvsWALLET"))
        {
             masterPackage.Workbook.Worksheets.Add(workSheetName, sheet);
        }
        else
        {
             masterPackage.Workbook.Worksheets.Add(workSheetName, sheet);
             masterPackage.Workbook.Worksheets.MoveBefore(2, 1);
        }
    }
}
masterPackage.SaveAs(new FileInfo(resultFile));
Comments