Copy a page froma Worksheet to new excel in Epplus

Try this Below Code,

///

private static byte[] GetReportExcelFile(Dictionary reportResults, ReportDetails reportDetails)

{

var tmpGuid = Guid.NewGuid();

var tempFolderForExcelFile = $"{DefaultFolderForExcelFiles}{tmpGuid}";

var exportFilePath = $"{tempFolderForExcelFile}\\{DefaultExcelFileName}";

var templateFilePath = string.Empty;

try

{

Cleanup.DeleteOldFiles(DefaultFolderForExcelFiles, 5, false, true);

if (!Directory.Exists(tempFolderForExcelFile))

{

Directory.CreateDirectory(tempFolderForExcelFile);

}

var excelExportManager = new ExcelExportManager();

if (!string.IsNullOrEmpty(reportDetails.Template))

{

// Create resultfile from template

exportFilePath = $"{tempFolderForExcelFile}\\OutputReport_{reportDetails.Template}";

templateFilePath = $"{tempFolderForExcelFile}\\Template_{reportDetails.Template}";

File.WriteAllBytes(templateFilePath, reportDetails.TemplateContent);

}

excelExportManager.AddDataSetToExcelFile(reportResults, exportFilePath, templateFilePath);

using (var filstr = File.Open(exportFilePath, FileMode.Open))

{

using (var ms = new MemoryStream())

{

ms.SetLength(filstr.Length);

filstr.Read(ms.GetBuffer(), 0, (int)filstr.Length);

ms.Flush();

return ms.ToArray();

}

catch (Exception ex)

{

throw new ReportingException(ex.Message);

}

finally

{

if (!string.IsNullOrEmpty(tempFolderForExcelFile))

{

Directory.Delete(tempFolderForExcelFile, true);

}

public void AddDataSetToExcelFile(Dictionary resultSet, string exportFilePath, string templateFilePath = null)

{

var excelToExport = new FileInfo(exportFilePath);

FileInfo template = null;

if (!string.IsNullOrEmpty(templateFilePath))

{

template = new FileInfo(templateFilePath);

}

using (var excelPackage = string.IsNullOrEmpty(templateFilePath) ? new ExcelPackage(excelToExport) : new ExcelPackage(excelToExport, template))

{

foreach (var result in resultSet)

{

var sheetCount = 1;

var sourceName = result.Key;

foreach (DataTable resultTable in result.Value.Tables)

{

var proposedSheetName = result.Value.Tables.Count == 1 ? sourceName : string.Format("{0}_{1}", sourceName, sheetCount);

var worksheet = excelPackage.Workbook.Worksheets.FirstOrDefault(ws => ws.Name == proposedSheetName);

if (worksheet == null)

{

worksheet = excelPackage.Workbook.Worksheets.Add(proposedSheetName);

FillWorksheetWithDataTableContent(ref worksheet, resultTable, TableStyles.Medium27);

}

else

{

FillWorksheetWithDataTableContent(ref worksheet, resultTable);

}

sheetCount++;

}

excelPackage.SaveAs(excelToExport);

}

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sundaram Subramanian9y ago
Try this Below Code,

///
///
///
private static byte[] GetReportExcelFile(Dictionary reportResults, ReportDetails reportDetails)
{
var tmpGuid = Guid.NewGuid();
var tempFolderForExcelFile = $"{DefaultFolderForExcelFiles}{tmpGuid}";
var exportFilePath = $"{tempFolderForExcelFile}\\{DefaultExcelFileName}";
var templateFilePath = string.Empty;
try
{
Cleanup.DeleteOldFiles(DefaultFolderForExcelFiles, 5, false, true);
if (!Directory.Exists(tempFolderForExcelFile))
{
Directory.CreateDirectory(tempFolderForExcelFile);
}
var excelExportManager = new ExcelExportManager();
if (!string.IsNullOrEmpty(reportDetails.Template))
{
// Create resultfile from template
exportFilePath = $"{tempFolderForExcelFile}\\OutputReport_{reportDetails.Template}";
templateFilePath = $"{tempFolderForExcelFile}\\Template_{reportDetails.Template}";
File.WriteAllBytes(templateFilePath, reportDetails.TemplateContent);
}
excelExportManager.AddDataSetToExcelFile(reportResults, exportFilePath, templateFilePath);
using (var filstr = File.Open(exportFilePath, FileMode.Open))
{
using (var ms = new MemoryStream())
{
ms.SetLength(filstr.Length);
filstr.Read(ms.GetBuffer(), 0, (int)filstr.Length);
ms.Flush();
return ms.ToArray();
}
}
}
catch (Exception ex)
{
throw new ReportingException(ex.Message);
}
finally
{
if (!string.IsNullOrEmpty(tempFolderForExcelFile))
{
Directory.Delete(tempFolderForExcelFile, true);
}
}
}
public void AddDataSetToExcelFile(Dictionary resultSet, string exportFilePath, string templateFilePath = null)
{
var excelToExport = new FileInfo(exportFilePath);
FileInfo template = null;
if (!string.IsNullOrEmpty(templateFilePath))
{
template = new FileInfo(templateFilePath);
}
using (var excelPackage = string.IsNullOrEmpty(templateFilePath) ? new ExcelPackage(excelToExport) : new ExcelPackage(excelToExport, template))
{
foreach (var result in resultSet)
{
var sheetCount = 1;
var sourceName = result.Key;
foreach (DataTable resultTable in result.Value.Tables)
{
var proposedSheetName = result.Value.Tables.Count == 1 ? sourceName : string.Format("{0}_{1}", sourceName, sheetCount);
var worksheet = excelPackage.Workbook.Worksheets.FirstOrDefault(ws => ws.Name == proposedSheetName);
if (worksheet == null)
{
worksheet = excelPackage.Workbook.Worksheets.Add(proposedSheetName);
FillWorksheetWithDataTableContent(ref worksheet, resultTable, TableStyles.Medium27);
}
else
{
FillWorksheetWithDataTableContent(ref worksheet, resultTable);
}
sheetCount++;
}
}
excelPackage.SaveAs(excelToExport);
}
}
Rahul Prasad9y ago
Try this.

//load workbook1
Workbook book1 = new Workbook();
book1.LoadFromFile(@"..\book1.xlsx");

//load workbook2
Workbook book2= new Workbook();
book2.LoadFromFile(@"..\book2.xlsx");

//get worksheet you want to copy from book1
Worksheet sheet = book1.Worksheets[0];

//add a copy to book2
book2.Worksheets.AddCopy(sheet);

//save
book2.SaveToFile(@"..\book2.xlsx");

reference: http://freenetexcel.codeplex.com/
Gokhul Varman9y ago
You can get a clear solution if you refer the link:https://stackoverflow.com/questions/27044854/how-to-copy-excelworksheet-from-template-with-new-codemodule