1

I'm using the following code to add a background image to Excel sheets using EPPlus.But the saved Excel does not have any background imgae.And on on opening the file with online excel it says that the workbook is damaged. 

foreach (var file in Filelist)
            {

                // Load workbook
                //var fileInfo = new FileInfo(@file);
                FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
                Bitmap bmp = new Bitmap("image.png");
                //ExcelPackage pkg = new ExcelPackage(fs);

                using (var package = new ExcelPackage(fs))
                {

                    // Itterate through workbook sheets
                    foreach (var sheet in package.Workbook.Worksheets)
                    {

                        sheet.BackgroundImage.Image = bmp;
                        sheet.Protection.IsProtected = false;

                    }

                    package.SaveAs(new FileInfo(@"New.xlsx"));
                }
                fs.Close();
            }
techno
  • 6,100
  • 16
  • 86
  • 192
  • Have you tried the technique in the question https://stackoverflow.com/questions/11588704/adding-images-into-excel-using-epplus – Mark McWhirter Jan 24 '20 at 01:28
  • @MarkMcWhirter It is used to add images in cells right? i want to add a background image. – techno Jan 24 '20 at 01:29

1 Answers1

3

The BackgroundImage on the sheet has a method SetFromFile which does the trick.

Full code

var file = @"c:\folder\spreadsheet.xlsx"; // Path to your source spreadsheet file here.
var image = @"c:\folder\background.png"; // Path to your background image here.
var imageFile = new FileInfo(image);

using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
using (var package = new ExcelPackage(fs))
{
    foreach (var sheet in package.Workbook.Worksheets)
    {
        sheet.BackgroundImage.SetFromFile(imageFile);
    }

    package.SaveAs(new FileInfo(@"c:\folder\new.xlsx")); // Path to destination spreadsheet file here;
}
pfx
  • 20,323
  • 43
  • 37
  • 57
  • Thanks,but this also does not work in my case.Have you tested the code? – techno Jan 28 '20 at 18:53
  • Yes, I have it up and running at my side ... with expected result. Can you give any feedback? – pfx Jan 28 '20 at 18:54
  • Please see my output Excel Sheet.https://www.justbeamit.com/varrv – techno Jan 28 '20 at 18:57
  • Also,i only replaced this line in the above code `foreach (var sheet in package.Workbook.Worksheets) { sheet.BackgroundImage.SetFromFile(new FileInfo("image.png")); sheet.Protection.IsProtected = false; }` – techno Jan 28 '20 at 18:59
  • Can you just try the code above as-is with a new clean Excel file, before integrating it in your system? – pfx Jan 28 '20 at 19:00
  • I tried your code in a fresh project.That wont work too.Can you send me the link to your output excel? – techno Jan 28 '20 at 19:06
  • I don't have excel installed.I use this to view excel https://sheet.zoho.com/sheet/excelviewer .On using the same your file also does not have the background.Do you use offline Excel? Microsoft Online Excel shows some error in the document. – techno Jan 28 '20 at 19:09
  • Than I'm quite sure, the issue is with the this viewer, since I have Excel installed and have the background image in place. Just rename the extension `.xslx` to `.zip` and extract the file. Then you will find a folder `xl\media` containing the background images (for each sheet), proving the image is there. – pfx Jan 28 '20 at 19:13
  • okay..Thanks for your help.I will check and get back tomorrow.Too late now. – techno Jan 28 '20 at 19:19
  • The issue was with the viewer.My code also works fine with other viewers eg: Polaris Office.I guess there is no other way to make the background image permanent in the file. – techno Jan 29 '20 at 06:27