Import pictures to Excel

[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?

[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

[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)

[tolekd]

Hello
It seems that I have found solution . 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

[Rathinagiri]

Hi,

Thanks for sharing man.

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

[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 ...

[Rathinagiri]

Oh! Thanks for the nice explanation Tolek Domanski.

[mol]

Thanks Tolek for sharing.

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

[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

[mol]

Ja mieszkam w Myszkowie - jakieś 40 km na południowy wschód od Częstochowy. Generalnie całkiem plisko od Praszki do Myszkowa