How to programmatically create an excel sheet from an excel template?
I am creating an excel sheet from a tempalte excel sheet.
I have code
try
{
FileInfo newFile = new FileInfo(@"D:\ExcelFromTemplate.xlsx");
FileInfo template = new FileInfo(@"D:\template.xlsx");
using (ExcelPackage xlPackage = new ExcelPackage(newFile,template))
{
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets["Sheet1"];
ExcelCell cell = worksheet.Cell(5,1);
cell.Value = "15";
//worksheet.Cell(5, 1).Value = "Soap";
//xlPackage.Save();
Response.Write("Excel file created successfully");
}
}
catch (Exception ex)
{
Response.WriteFile(ex.InnerException.ToString());
}
this code creates a new excel file just like template excel file, but cannot add cell value. What for?
I tried this in two ways, as in the previous code for cell (5,1). But the excel sheet is created without specifying the cell value. How can we add it.
Best regards, Girish
+2
source to share
1 answer
You need to save the file to save your changes. Using save ()
try
{
FileInfo newFile = new FileInfo(@"D:\ExcelFromTemplate.xlsx");
FileInfo template = new FileInfo(@"C:\Example.xlsx");
using (ExcelPackage xlPackage = new ExcelPackage(newFile , template))
{
//Added This part
foreach (ExcelWorksheet aworksheet in xlPackage.Workbook.Worksheets)
{
aworksheet.Cell(1, 1).Value = aworksheet.Cell(1, 1).Value;
}
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets["My Data"];
ExcelCell cell = worksheet.Cell(5, 1);
cell.Value = "15";
//worksheet.Cell(5, 1).Value = "Soap";
xlPackage.Save( );
//Response.Write("Excel file created successfully");
}
}
catch (Exception ex)
{
//Response.WriteFile(ex.InnerException.ToString());
}
You have a problem. The problem is inherent in ExcelPackage. To do this, you need to open each sheet and make some changes to save it.
Search the forum for additional explanations.
+2
source to share