Inserting a bitmap image into excel

Question

Hi All,

 I have created a BitmapImage object in C# from SQL server database table:

var image = new BitmapImage();

using (var mem = new System.IO.MemoryStream((byte[])dataRow[columnName]))

{

    mem.Position = 0;

    image.BeginInit();

    image.CreateOptions = BitmapCreateOptions.PreservePixelFormat;

    image.CacheOption = BitmapCacheOption.OnLoad;

    image.UriSource = null;

    image.StreamSource = mem;

    image.EndInit();

}

image.Freeze();

I want it to place/insert into excel worksheet on top left corner(say first 3 rows and first 3 columns). Please advise how can I achieve the same in WPF ASAP.

Regards,

Pratham

Answer 1

You can do something like this : test with a bitmapImage I loaded from a file =>

            Clipboard.SetImage(bitmapImage);

            Excel.Application excelApp = new Excel.Application();
            excelApp.DisplayAlerts = false; // Disable confirmation dialogs
            Excel.Workbook workbook = excelApp.Workbooks.Add();
            Excel.Worksheet worksheet = workbook.ActiveSheet;

            Excel.Range cell = worksheet.Cells[1, 1];
            cell.Select();

            worksheet.Paste();

            //excelApp.Visible = true;

            string sSaveFile = @"E:\Sources\WPF_Excel_InsertImage\ExcelFile.xlsx";
            workbook.SaveAs(sSaveFile);
            workbook.Close();
            excelApp.Quit();
            Marshal.ReleaseComObject(excelApp);
            Marshal.ReleaseComObject(workbook);
            Marshal.ReleaseComObject(worksheet);
            MessageBox.Show("Excel file saved at : " + sSaveFile);

Answer 2

Hi, @Pratham Jain. Welcome to Microsoft Q&A. 

You could consider using worksheet.Shapes.AddPicture to add a picture at the specified location.

Refer to the following method:

    public void SaveImageToExcel()
    {
        BitmapImage image = GetImage();

        string tempImagePath = "Your temporary file path";
        // Convert BitmapImage to byte array
        byte[] imageBytes = BitmapImageToByteArray(image);

        // Save the byte array as a .jpg file
        File.WriteAllBytes(tempImagePath, imageBytes);



        string excelPath = "The Excel path you want to save to";

        var _excelApp = new Excel.Application();
        var _books = (Excel.Workbooks)_excelApp.Workbooks;
        var _book = (Excel._Workbook)(_books.Add());
        var _sheets = (Excel.Sheets)_book.Worksheets;
        var _sheet = (Excel._Worksheet)(_sheets.get_Item(1));
        _sheet.PageSetup.Zoom = false;
        _sheet.PageSetup.FitToPagesWide = 1;

        //Insert image from current location
        Excel.Range cell = (Excel.Range)_sheet.Cells[3, 3];
        var left = (float)((double)cell.Left);
        var top = (float)((double)cell.Top);
        //Image size
        float width = image.PixelWidth;
        float height = image.PixelHeight;
        //Insert a picture
        _sheet.Shapes.AddPicture(tempImagePath, Microsoft.Office.Core.MsoTriState.msoTrue, Microsoft.Office.Core.MsoTriState.msoTrue, left, top, width, height);
        
        _book.SaveAs(excelPath);
        _book.Close();
        _excelApp.Quit();
        Marshal.ReleaseComObject(_excelApp);
        Marshal.ReleaseComObject(_book);
        Marshal.ReleaseComObject(_sheet);
    }

    private byte[] BitmapImageToByteArray(BitmapImage bitmapImage)
    {
        byte[] data;
        JpegBitmapEncoder encoder = new JpegBitmapEncoder();
        encoder.Frames.Add(BitmapFrame.Create(bitmapImage));
        using (MemoryStream ms = new MemoryStream())
        {
            encoder.Save(ms);
            data = ms.ToArray();
        }
        return data;
    }

1.Microsoft.Office.Core.MsoTriState requires installing MicrosoftOfficeCore via Nuget.

2.Here, I assume that you have obtained the BitmapImage through GetImage();, so convert it to byte[] and then store it as a temporary .jpg image. If you get the byte[] directly from the database, you could ignore this conversion step.

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.