Retrieve pictures from Excel file using OLEDB

Question

I have an Excel sheet with two column, one is a number , and second column have a picture. i want to read these data from c# with oledb connection, i can read number easily , but pictures is not contained in second column , so in c# i just get first column.

now, how can i read the images ? i want to extract the numbers and related images from this excel sheet.

Answer 1

This is an older topic but I figured I would add some of my code thus far.

This example assumes you have a Windows application that you've placed a Picturebox on called "pictureBox1".

It also assumes you add a reference to Excel (Microsoft.Office.Interop.Excel).

Pictures are bound to your workbook and not part of the cells themself as Jay has mentioned. You CAN find where the image should go pretty easily by using the TopLeftCell and BottomRightCell.

Now, you'll need to write a loop to pull all of the images our of the document but I'll leave that to you.

        string file = @"C:\sample.xlsx";

        if(System.IO.File.Exists(file))
        {

            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            excelApp.Visible = true; //FOR TESTING ONLY
            Microsoft.Office.Interop.Excel.Workbook wb = excelApp.Workbooks.Open(file,
                                Type.Missing,
                                Type.Missing,
                                Type.Missing,
                                Type.Missing,
                                Type.Missing,
                                Type.Missing,
                                Type.Missing,
                                Type.Missing,
                                Type.Missing,
                                Type.Missing,
                                Type.Missing,
                                Type.Missing,
                                Type.Missing,
                                Type.Missing);

            Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[1];   //Selects the first sheet
            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)ws.Cells[1, 1];      //Select cell A1
            object cellValue = range.Value2;

            #region Extract the image
            Microsoft.Office.Interop.Excel.Picture pic = (Microsoft.Office.Interop.Excel.Picture)ws.Pictures(1);

            if (pic != null)
            {
                //This code will detect what the region span of the image was
                int startCol = (int)pic.TopLeftCell.Column;
                int startRow = (int)pic.TopLeftCell.Row;
                int endCol = (int)pic.BottomRightCell.Column;
                int endRow = (int)pic.BottomRightCell.Row;


                pic.CopyPicture(Microsoft.Office.Interop.Excel.XlPictureAppearance.xlScreen, Microsoft.Office.Interop.Excel.XlCopyPictureFormat.xlBitmap);
                if (Clipboard.ContainsImage())
                {
                    Image img = Clipboard.GetImage();
                    this.pictureBox1.Image = img;
                }
            }
            #endregion

            //Close the workbook
            wb.Close(false,Type.Missing,Type.Missing);

            //Exit Excel
            excelApp.Quit();
        }

Answer 2

Not possible, I'm afraid.

Pictures do not live in cells -- you can place them over the cell, and you can size them to look like they are in the cell, but they in no way occupy that cell.

You can manipulate image contents of a worksheet using VBA and COM interop, but not OLEDB.

Answer 3

Nick's answer work great for me in my web application with a little change it was not copying image to clipboard

 Thread thread = new Thread(() =>
                {
                    foreach (var pic in ws.Pictures())
                    {
                        if (pic != null)
                        {
                            //This code will detect what the region span of the image was
                            int startCol = pic.TopLeftCell.Column;
                            int startRow = pic.TopLeftCell.Row;
                            int endCol = pic.BottomRightCell.Column;
                            int endRow = pic.BottomRightCell.Row;
                            pic.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap);
                            if (Clipboard.GetDataObject() != null)
                            {
                                Image img = Clipboard.GetImage();
                            }
                        }
                    }
                });
                thread.SetApartmentState(ApartmentState.STA);
                //Set the thread to STA
                thread.Start();
                thread.Join();

Works for me