Copy worksheet in Epplus Values only

Question

I am using Epplus to copy a worksheet from a wokbook and paste it in another workbook.I can able to copy the worksheet sucesssfully,by using the below code.

  ExcelPackage masterPackage = new ExcelPackage(new FileInfo(@"C:\\Users\\350154\\Desktop\\vb workouts\\testsample.xlsx"));
  ExcelPackage pckg = new ExcelPackage(new FileInfo("C:\\Users\\350154\\Desktop\\vb workouts\\as.xlsx"));
  string workSheetName = pckg.Workbook.Worksheets[1].Name;
   ExcelWorksheet pck = pckg.Workbook.Worksheets[1];
   pck.ConditionalFormatting.RemoveAll();
   masterPackage.Workbook.Worksheets.Add(workSheetName, pck);

The code copies the sheet sucessfully.But the copied sheet has formulas in their cells.So Values not copying in a new excel pls help me to solve this.

Answer 1

If you're just looking to copy the values from one spreadsheet into a new sheet in another, try this:

public static void CopySheetValues(string sourcePath, string sheetName, string destPath)
{
    using (var src = new ExcelPackage(new FileInfo(sourcePath)))
    using (var dest = new ExcelPackage(new FileInfo(destPath)))
    {
        var wsSrc = src.Workbook.Worksheets[sheetName];
        var wsDest = dest.Workbook.Worksheets[wsSrc.Name] ?? dest.Workbook.Worksheets.Add(wsSrc.Name);

        for (var r = 1; r <= wsSrc.Dimension.Rows; r++)
        {
            for (var c = 1; c <= wsSrc.Dimension.Columns; c++)
            {
                var cellSrc = wsSrc.Cells[r, c];
                var cellDest = wsDest.Cells[r, c];

                // Copy value
                cellDest.Value = cellSrc.Value;

                // Copy cell properties
                cellDest.Style.Numberformat = cellSrc.Style.Numberformat;
                cellDest.Style.Font.Bold = cellSrc.Style.Font.Bold;
                // TODO... Add any additional properties that you may want to copy over
            }
        }

        dest.Save();
    }
}

UPDATE: Sample code updated to show how formatting can also be copied from the source to the destination worksheet