I have a template workbook with a sheet called ProdData, I need to copy this worksheet to my current workbook.
Using C# and EPPlus, how can I copy a worksheet from one workbook to another? When I look at intellisense it seems to only show that I can copy from within the same workbook.
How do I copy the worksheet to a NEW workbook?
This works for me.
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[1];
var wsDest = dest.Workbook.Worksheets[wsSrc.Name] ?? dest.Workbook.Worksheets.Add(wsSrc.Name);
for (var r = 1; r <= wsSrc.Dimension.Rows; r++)
{
Console.WriteLine("Row: " + r.ToString());
for (var c = 1; c <= wsSrc.Dimension.Columns; c++)
{
Console.WriteLine("Column: " + c.ToString());
var cellSrc = wsSrc.Cells[r, c];
var cellDest = wsDest.Cells[r, c];
if (cellDest.ToString() == "E10")
{
}
Console.WriteLine(cellDest.ToString());
// Copy value
cellDest.Value = cellSrc.Value;
// Copy cell properties
cellDest.Style.Numberformat = cellSrc.Style.Numberformat;
cellDest.Style.Font.Bold = cellSrc.Style.Font.Bold;
if (cellSrc.Style.Fill.BackgroundColor.Rgb != null)
{
if (cellSrc.Style.Fill.BackgroundColor.Rgb != "")
{
var color = cellSrc.Style.Fill.BackgroundColor.Rgb;
cellDest.Style.Fill.PatternType = ExcelFillStyle.Solid;
cellDest.Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#" + color));//.SetColor(color);
}
else
{
cellDest.Style.Fill.PatternType = ExcelFillStyle.Solid;
cellDest.Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#808080"));//.SetColor(color);
}
}// TODO... Add any additional properties that you may want to copy over
cellDest.Style.HorizontalAlignment =
cellSrc.Style.HorizontalAlignment;
cellDest.Style.VerticalAlignment =
cellSrc.Style.VerticalAlignment;
cellDest.Style.Border.Right.Style =
cellSrc.Style.Border.Right.Style;
cellDest.Style.Border.Left.Style =
cellSrc.Style.Border.Left.Style;
cellDest.Style.Border.Top.Style = cellSrc.Style.Border.Top.Style;
cellDest.Style.Border.Bottom.Style =
cellSrc.Style.Border.Bottom.Style;
cellDest.Style.WrapText = cellSrc.Style.WrapText;
}
}
dest.Save();
}
}
