how to create excel sheet from template excel sheet programmatically?

Question

I am creating a excel sheet from tempalte excel sheet.

I have a 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 the new excel file same as the template excel file but could not add the cell value. Why?

I had tried it with 2 ways as in above code for cell(5,1). But the excel sheet creates without writting cell value. How we can add it.

Consider using previous questions answers http://stackoverflow.com/questions/1624095/how-to-add-dropdown-in-excel-sheet-programmatically/1624157#1624157 http://stackoverflow.com/questions/1623266/how-to-create-excel-sheet-data-directly-from-the-net-application/1623292#1623292 — Muhammad Akhtar, Oct 27 '09 at 05:22
why did you comment out the call to .Save()? Did you get an error? If so: what error was it?? Permissions again?? — marc_s, Oct 27 '09 at 08:17

score 4 · Answer 1 · edited Mar 13 '18 at 03:55

You need to save the file to persist the changes made. 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());
        }

Got the issue. The problem is inherent to ExcelPackage. For the same you have to open each sheet and do some changes for it to get saved.

Search the forum for more explanation.

Whenever I had added xlPackage.Save(); then it gives me error "Object reference not set to an instance of an object." What is the issue???? — Girish, Oct 27 '09 at 06:53
Sorry for delay was busy with something. Is this excel package related to following http://excelpackage.codeplex.com/ — Sachin Chavan, Oct 27 '09 at 09:43

how to create excel sheet from template excel sheet programmatically?

1 Answers1