CODEWITHSUNDEEP
c#excelimagelistepplus
Emcrank
Emcrank

Reputation: 316

Writing Images to Excel file using EPPlus

I am trying to make the application generate an Excel file from a list of string type and a list of image type. I have the user enter a line then makes them take a screenshot and so on until G is pressed and then it should generate an Excel file with the format as so:

Row 1- Title-0-
Row 2- Header-1-
Row 3- Image-0-
Row 4- Header-2-
Row 5- Image-1-
Row 6- Header-3-
Row 7- Image-2-
Row 8- Header-4-
Row 9- Image-3-
Row 10- Header-5-
Row 11- Image-4-

...and so on until its done all in the collections.

I have created the List and List and I know that they both contain Strings and Images before I hit G as I have looked inspected the collections debug mode.

This is the code I have so far and the excel file looks right except there are no Images to be seen however it is re-sizing the rows to the pictures heights. I have never worked with Images before so think I could be missing something important but not sure what.

The Collections are passed into this method from a calling method String collection is named "withHeadersList", Image collection is named "withImgList".

Generate Excel Method:

public static bool GenerateTestPlan(List<String> withHeadersList, List<Image> withImgList, string stringOutputPath)
        {
            ExcelPackage newExcelPackage = CreateExcelPackage(withHeadersList[0]);
            ExcelWorksheet newExcelWorksheet = CreateWorkSheet(newExcelPackage, "Sheet1");

            SetCellValue(newExcelWorksheet, 1, 1, withHeadersList[0]); //Title
            newExcelWorksheet.Row(1).Style.Font.Size = 35;
            newExcelWorksheet.Row(1).Style.Font.Bold = true;

            int pRowIndex = 3;
            int hRowIndex = 2;
            for (int i = 1; i < withHeadersList.Count; i++)
            {
                SetCellValue(newExcelWorksheet, hRowIndex, 1, withHeadersList[i]);
                newExcelWorksheet.Row(hRowIndex).Style.Font.Size = 20;

                newExcelWorksheet.Row(pRowIndex).Height = withImgList[i - 1].Height;           //Set row height to height of screenshot


                var img = newExcelWorksheet.Drawings.AddPicture(withHeadersList[i], withImgList[i - 1]);    //Add Images (THINK THIS LAST PARAMETER IS THE PROBLEM)
                img.SetPosition(pRowIndex, Pixel2MTU(2), 1, Pixel2MTU(2));
                img.SetSize(withImgList[i - 1].Width, withImgList[i - 1].Height);

                hRowIndex += 2;
                pRowIndex += 2;
            }

            SaveExcelPackage(newExcelPackage, stringOutputPath);

            return true;
        }

Excel File here As you see it's like the images are just not being rendered.

Upvotes: 1

Views: 3972

Answers (1)

Darren Gourley
Darren Gourley

Reputation: 1808

Your issue is most certainly with this line:

img.SetPosition(pRowIndex, Pixel2MTU(2), 1, Pixel2MTU(2));

I'm not sure why you are converting pixels to anything considering SetPosition is looking for the offset in pixels. From the metadata:

    // Summary:
    //     Set the top left corner of a drawing. Note that resizing columns / rows after
    //     using this function will effect the position of the drawing
    //
    // Parameters:
    //   Row:
    //     Start row
    //
    //   RowOffsetPixels:
    //     Offset in pixels
    //
    //   Column:
    //     Start Column
    //
    //   ColumnOffsetPixels:
    //     Offset in pixels
    public void SetPosition(int Row, int RowOffsetPixels, int Column, int ColumnOffsetPixels);

I would recommend just passing through small values, such as 2, for the RowOffestPixels and ColumnOffsetPixels parameters:

img.SetPosition(pRowIndex, 2, 1, 2);

I found a method called Pixel2MTU(int pixels) on codeproject from a quick google search. The method is as follows:

public int Pixel2MTU(int pixels)
{
    int mtus = pixels * 9525;
    return mtus;
}

If this is the same method you are using, your images might be at the very far bottom right of your excel document.

Upvotes: 1

Related Questions