how to create excel sheet from template excel sheet programmatically ?

Question

Hi to All,

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.

Regards, Girish

Answer 1

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 porblem 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.