Thursday, July 19, 2012

Extracting Images from an Excel File

A new question has been raised inside Foxite which is how we can extract and save images from within an excel workbook so I thought, how indeed?

We can do manually by clicking an image (or a group of images) then choosing copy, opening paint and pasting it over there.  Man, that would be tiresome.

Or we can ensure the format to be of xlsx (2007 and 2010) then later renaming the extension from xlsx into zip, extracting that newly renamed zip file and digging into subfolder xl, then media to get all the jpeg and png images from within the worksheet file.   Read this if you are confused:  http://sandstorm36.blogspot.com.au/2012/02/why-appendimport-from-xlsx-fails.html

Another way which is much simpler because it does not require that manual renaming of xlsx to zip is this, saving the excel file into an HTML format and digging as well into the folder that holds images for the HTML.  And this is the way I decided to go for this example.  Here is the result:





And here are the codes:

Local oForm
oForm=Newobject("Form1")
oForm.Show
Read Events
Return

Define Class Form1 As Form
    Height = 390
    Width = 500
    AutoCenter = .T.
    Caption = 'Extracting Images from an Excel'


    Add Object grid1 As Grid With ;
        GridLines = 0, ;
        Height = 328, ;
        Left = 10, ;
        Top = 50, ;
        Width = 150,;
        GridLines = 0,;
        DeleteMark = .F.,;
        GridLineColor = Rgb(192,192,192),;
        FontName = 'Tahoma',;
        FontSize = 10,;
        AllowCellSelection = .F.,;
        RecordSource = 'junk'


    Add Object Image1 As Image With;
        top = 15, Left = 180, Width = 300, Height =300,;
        Stretch = 2

    Procedure Load
        Set Talk Off
        Set Safety Off
        #Define xlHtml    44

        Local lcfile, lnloop, lcHTML
        lcfile = Getfile('xlsx')
        lcHTML = Addbs(Getenv("tmp"))+Sys(3)

        If !Empty(m.lcfile)
            Local loExcel As excel.Application
            loExcel = Createobject("Excel.Application")
            With loExcel
                .Workbooks.Open(m.lcfile)
                * Save into HTML format
                .ActiveWorkbook.SaveAs(m.lcHTML,xlHtml, .F.,.F.)
                .ActiveWorkbook.Close
                .Quit
            Endwith
        Endif

        Create Cursor junk (ImageName c(20), xImage M)
        * get images
        Set Default To (m.lcHTML)+'_files'
        lnFound = Adir(laImages,'image*.*')
        If m.lnFound > 0
            For lnloop = 1 To m.lnFound
                Insert Into junk Values ;
       (m.laImages[m.lnloop,1],Filetostr(m.laImages[m.lnloop,1]))
            Endfor
            Go Top
        Endif
    ENDPROC
   
    PROCEDURE Init
        thisform.ShowImage()
    ENDPROC


    Procedure grid1.AfterRowColChange
        Lparameters nColIndex
        thisform.ShowImage()
    Endproc

    Procedure grid1.Click
        Lparameters nColIndex
        thisform.ShowImage()
    Endproc

    Procedure ShowImage
        * PictureVal in my end is having problem when format is
        * PNG,so we will not work directly with image values here
        If Getwordnum(Alltrim(junk.ImageName),2,'.') = 'PNG'
            Local lcfile
            lcfile = Getenv("TMP")+'\tempimage'
            Strtofile(junk.xImage,m.lcfile)
            Thisform.Image1.Picture = m.lcfile
        ELSE
            * Work directly with image values
            Thisform.Image1.PictureVal = junk.xImage 
        Endif
    Endproc

    Procedure Destroy
        Clear Events
    Endproc

Enddefine


Shown above is how we can use Picture and PictureVal properties for your comparison.


Cheers!

3 comments:

  1. Jun,
    You are one of the best man, no only for what you accomplish but also for helping others to learn w/o asking nothing in return.
    Question, if i would like to have the same functionality but open dwg files you know drawings files, how can this be accomplish ?
    Thanks a lot
    Ernesto Paez from the foxite forum

    ReplyDelete
  2. Thank you Ernesto. Unfortunately, I don't think autocad supports automation which is what makes extracting of multiple images possible here because I was able to save it into an html format, and from there were able to dig deeper into its sub-folder.

    ReplyDelete
  3. Thanks for sharing Jun, check out this Excel picture add-in automation for image extraction.

    ReplyDelete