HMGforum.com

Exclusive forum for HMG, a Free / Open Source xBase WIN32/64 Bits / GUI Development System
http://mail.hmgforum.com/

Import pictures to Excel

http://mail.hmgforum.com/viewtopic.php?t=1652

Page 1 of 1

Import pictures to Excel

Posted: Tue Aug 24, 2010 4:51 am
by tolekd
Hi
I am a beginner regarding HMG. In my first program I have a requirement to print pictures from dbf records (field PHOTO (char 200) with paths to the pictures) to Excel in rows. I don't know how to place properly these photos in Excel and how to resize them . Could you help me? :?:

Re: Import pictures to Excel

Posted: Tue Aug 24, 2010 5:06 am
by Rathinagiri
Hi,

I know there is a function in excel "ActiveSheet.Pictures.Insert("c:\untitled.bmp").Select to show images in a cell. Now we have to use this formula and the filename to show the pictures inside a worksheet.

To export from harbour to excel the following code can be used:

Note: This is not a functional sample. It is an extract from my Interest(ing) Calculator. It is just a sample.

Code: Select all

function loanexportxl
local oExcel, oH
local reportarr := {}
local row := 5
if interest.loanaccount.itemcount == 0
   msgstop("Nothing to export!","Error")
   return nil
endif
oExcel := TOleAuto():new( "Excel.Application" )
if Ole2TxtError() != "S_OK"
   MsgStop("Excel Not available.", "Error")
   return nil
endif
oExcel:WorkBooks:add()
oH := oExcel:Get( "ActiveSheet" )
oH:cells(row,1):value := "Principal Amount :"
oH:cells(row,2):value := interest.tprincipal.value  &&b5
row := row + 1
oH:cells(row,1):value := "Number of "+alltrim(intarr[interest.cterm.value])+ " Instalments :"
oH:cells(row,2):value := interest.tterm.value  &&b6
row := row + 1
oH:cells(row,1):value := "Amount of Instalment :"
oH:cells(row,2):value := interest.tinstalment.value &&b7
row := row + 1
oH:cells(row,1):value := "Interest Compounding  :"
oH:cells(row,2):value := alltrim(interest.cintcomp.item(interest.cintcomp.value)) &&b8
row := row + 1
oH:cells(row,1):value := "Interest Rate:"
oH:cells(row,2):value := interest.tintrate.value/100 &&b9
oH:cells(row,2):set( "NumberFormat", "0.00%" )
oH:cells(row,3):value := "p.a."
row := row + 2
oH:cells(row,1):value := interest.loanaccount.header(1)
oH:cells(row,2):value := interest.loanaccount.header(2)
oH:cells(row,3):value := interest.loanaccount.header(3)
oH:cells(row,4):value := interest.loanaccount.header(4)
oH:cells(row,5):value := interest.loanaccount.header(5)
row := row + 1
frow := row
for i := 1 to interest.loanaccount.itemcount
   if i == 1
      oH:cells(row,1):value := i
      oH:cells(row,2):value := "=$b$5"
      oH:cells(row,2):set ("NumberFormat","##########0.00")
   else
      oH:cells(row,1):value := i
      oH:cells(row,2):value := "=e"+alltrim(str(row-1,5,0))
      oH:cells(row,2):set ("NumberFormat","##########0.00")
   endif
   templine := interest.loanaccount.item(i)
   oH:cells(row,3):value := val(alltrim(templine[3]))
   oH:cells(row,3):set ("NumberFormat","##########0.00")
   oH:cells(row,4):value := val(alltrim(templine[4]))
   oH:cells(row,4):set ("NumberFormat","##########0.00")
   oH:cells(row,5):value := "=b"+alltrim(str(row,5,0))+"+c"+alltrim(str(row,5,0))+"-d"+alltrim(str(row,5,0))
   oH:cells(row,5):set ("NumberFormat","##########0.00")
   row := row + 1
next i
oH:cells(row,3):value := "=sum(c"+alltrim(str(frow,5,0))+":c"+alltrim(str(row-1,5,0))
oH:cells(row,3):set ("NumberFormat","##########0.00")
oH:cells(row,4):value := "=sum(d"+alltrim(str(frow,5,0))+":d"+alltrim(str(row-1,5,0))
oH:cells(row,4):set ("NumberFormat","##########0.00")
oH:cells(1,1):select()
oExcel:visible := .T.
oH:end()
oExcel:end()
return nil

Re: Import pictures to Excel

Posted: Tue Aug 24, 2010 5:48 am
by tolekd
Thx

I am importing data to Excel in similarly way. I knew about this function ActiveSheet.Pictures.Insert("c:\untitled.bmp").Select BUT I can't place and resize picture properly. It is my main problem - proper place i.e. oH:cells(row,4) and proper sizing(because photos may have different sizes) :?:

Re: Import pictures to Excel

Posted: Wed Aug 25, 2010 7:36 am
by tolekd
Hello
It seems that I have found solution :P . Part of my code is below. Crucial code is

Wypluj:Shapes:AddPicture(Alltrim(PRICE->PHOTO), .f.,.t. ,310 ,13.5+50*(i-2) , 60, 50)

i:=2
Do While !Eof()
Form_1.Progress_Excel.Value := Int ((i/LastRec())*100000)
Wypluj:Cells( i, 1 ):Value := Alltrim(PRICE->NRGR)
Wypluj:Cells( i, 2 ):Value := PRICE->NAZWA
Wypluj:Cells( i, nKolumna ):Value := PRICE->CENA_2
Wypluj:Cells( i, nKolumna+1 ):Value := PRICE->CENA_1
Wypluj:Cells( i, nKolumna+2 ):Value := PRICE->CENA_2
Wypluj:Cells( i, nKolumna+3 ):Value := PRICE->CENA_3
Wypluj:Cells( i, nKolumna+4 ):Value := PRICE->CENA_MIN
Wypluj:Cells( i, nKolumna+5 ):Value := PRICE->CENA_MAX
Wypluj:Cells( i, nKolumna+6 ):Value := PRICE->NOMI
Wypluj:Cells( i, nKolumna+7 ):Value := PRICE->PRAKTIKER
Wypluj:Cells( i, nKolumna+8 ):Value := PRICE->CASTORAMA
Wypluj:Cells( i, nKolumna+9 ):Value := PRICE->LER_MER
Wypluj:Cells( i, nKolumna+10 ):Value := PRICE->OBI
Wypluj:Cells( i, nKolumna+11 ):Value := PRICE->MERKURY
If !Empty(Alltrim(PRICE->PHOTO))
Wypluj:Shapes:AddPicture(Alltrim(PRICE->PHOTO), .f.,.t. ,310 ,13.5+50*(i-2) , 60, 50)
Endif

i:=i + 1
Select PRICE
DbSkip()
Enddo

Re: Import pictures to Excel

Posted: Wed Aug 25, 2010 8:10 am
by Rathinagiri
Hi,

Thanks for sharing man.

Now, the values 310, 13.5+50 * (i-2) represent what?

Re: Import pictures to Excel

Posted: Wed Aug 25, 2010 8:21 am
by tolekd
It was my first experience with this function which derives from Excel (I checked it in Excel 2003 and Excel 2007)

Function AddPicture(Filename As String, LinkToFile As MsoTriState, SaveWithDocument As MsoTriState, Left As Single, Top As Single, Width As Single, Height As Single) As Shape
Member of Excel.Shapes

Thanks these parameters I could place the picture (Left,Top) and resize it (Width,Height). All of that I did rather more intuitive ... :)

Re: Import pictures to Excel

Posted: Wed Aug 25, 2010 9:09 am
by Rathinagiri
Oh! Thanks for the nice explanation Tolek Domanski.

Re: Import pictures to Excel

Posted: Wed Aug 25, 2010 9:15 am
by mol
Thanks Tolek for sharing.

Miło widzieć kolejną osobę z Polski na forum :)

Re: Import pictures to Excel

Posted: Wed Aug 25, 2010 9:31 am
by tolekd
Hi my finally placement of pictures:

Wypluj:Shapes:AddPicture(Alltrim(PRICE->PHOTO), .f.,.t. ,320 ,15+49.5*(i-2) , 60, 46)

Sheet properties:
height of first row (headings 13.5)
height of rest rows 50
width of column contained picture 15 (difference with picture width - 60 different UM I don't know)

As you may rows are multiplayed by 49.5 (probably due to Excel grid thickness). It works..

To mol:

rzeczywiscie jestem z Polski i calkiem niedawno podczas lipcowego urlopu "sprobowalem" HMG . Jest fajne..
Do Czestochowy tez mam niedaleko (60km) tyle ze na mieszkam na polnoc od Jasnej Gory

Re: Import pictures to Excel

Posted: Wed Aug 25, 2010 9:36 am
by mol
Ja mieszkam w Myszkowie - jakieś 40 km na południowy wschód od Częstochowy. Generalnie całkiem plisko od Praszki do Myszkowa :D
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited