Add image from a folder to Excel worksheet

Question

I want to export all images from my image folder into Excel. I want to place every image in the image folder into a new excel worksheet. For example if there are ten images in the folder, I want to have ten Excel worksheets on the same Excel workbook with one image on each worksheet.

This is my code:

    string[] filesindirectory = Directory.GetFiles(Server.MapPath("~/Image"));
                int count = 0;
                foreach (string img in filesindirectory)
                {
                    count++;
                 ExcelWorksheet ws = objExcelPackage.Workbook.Worksheets.Add("Worksheet - " + count); //create new worksheet
                    System.Web.UI.WebControls.Image TEST_IMAGE = new System.Web.UI.WebControls.Image();
                    TEST_IMAGE.ImageUrl = "Image/" + Path.GetFileName(img);
                    TEST_IMAGE.ImageUrl = this.GetAbsoluteUrl(TEST_IMAGE.ImageUrl);

                   //I want to insert image here

var filepath= new FileInfo(@"C:\Users\user\Desktop\folder\" + datetime.ToString("dd-MM-yyyy_hh-mm-ss") + ".xlsx");
                        objExcelPackage.SaveAs(filepath);

This is my updated code:

string[] filesindirectory = Directory.GetFiles(Server.MapPath("~/Image"));
        int count = 0;
        foreach (string img in filesindirectory)
        {
            count++;
            ExcelWorksheet ws = objExcelPackage.Workbook.Worksheets.Add("Worksheet - " + count); //create new worksheet
            System.Web.UI.WebControls.Image TEST_IMAGE = new System.Web.UI.WebControls.Image();
            var filedest = new FileInfo(@"C:\Users\user\Desktop\Project\Project1\Source Code\Test\Test\Image\");
            System.Drawing.Image myimage = System.Drawing.Image.FromFile(filedest);
            var pic = ws.Drawings.AddPicture("NAME", myimage);

            var filepath = new FileInfo(@"C:\Users\user\Desktop\folder\" + datetime.ToString("dd-MM-yyyy_hh-mm-ss") + ".xlsx");
            objExcelPackage.SaveAs(filepath);

but I get the following error:

FileNotFoundException was unhandled by user code

, on this line:

var filedest = new FileInfo(@"C:\Users\user\Desktop\Project\Project1\Source Code\Test\Test\Image\");

How to add image from a folder to Excel worksheet using EPPlus with my current code?

Check out this link http://stackoverflow.com/questions/11588704/adding-images-into-excel-using-epplus — Nic, Oct 22 '15 at 02:07
hi @Nic, I have researched online and found this method: for(int a = 0; a < 5; a++) { ws.Row(a*5).Height = 39.00D; var picture = ws.Drawings.AddPicture(a.ToString(), logo); picture.SetPosition(a*5, 0, 2, 0); }, but I do not know how to use it in my codes. Please give me some advices thanks. — Felicia Soh, Oct 22 '15 at 02:09

Nic · Accepted Answer · 2015-10-22T03:08:48.527

1

This should help you...

// Variable
string[] filesDirectory = Directory.GetFiles(Server.MapPath("~/Image"));
int count = 0;

foreach(string img in filesDirectory)
{
    count++;
    ExcelWorksheet ws = pkg.Workbook.Worksheets.Add("Worksheet - " + count);

    // img variable actually is your image path
    System.Drawing.Image myImage = System.Drawing.Image.FromFile(img);

    var pic = ws.Drawings.AddPicture("NAME", myImage);

    // Row, RowoffsetPixel, Column, ColumnOffSetPixel
    pic.SetPosition(1, 0, 2, 0);
}

edited Oct 22 '15 at 03:08

answered Oct 22 '15 at 02:24

Nic

1,014
7
15

hi @Nic, have tried the method u suggested but I got 'FileNotFoundException was unhandled by user code' error, please look at my updated post thanks. – Felicia Soh Oct 22 '15 at 02:48
What are you doing with the `img` variable? Shouldn't it be passed to `System.Drawing.Image.FromFile`? – mason Oct 22 '15 at 02:48
@Nic, that doesn't really answer my question. You're looping over the files, and the path is in the `img` variable. Therefore, that's what you should pass to `System.Drawing.Image.FromFile` instead of "Your Image PATH". – mason Oct 22 '15 at 02:56
@mason I know what you mean.. I will update the answer so she won't confuse the actual answer is `img` == `"Your Image PATH" – Nic Oct 22 '15 at 03:07
@FeliciaSoh Check the answer and put your `var filepath = new FileInfo(@"C:\Users\user\Desktop\folder\" + datetime.ToString("dd-MM-yyyy_hh-mm-ss") + ".xlsx"); objExcelPackage.SaveAs(filepath);` out of the loop. – Nic Oct 22 '15 at 03:11
hi @Nic, thanks it works now! Thanks a lot for your help! :) – Felicia Soh Oct 22 '15 at 03:27

Add image from a folder to Excel worksheet

1 Answers1