Insert a picture into excel file and set the picture as transparent

[Yunhua]

Hello, I want to insert a bmp file into excel worksheet, then set the picture as transparent. I am using CVI9.0 and excel2003.The code I have used is as below: HRESULT CVIFUNC ExcelRpt_InsertPicture_Set_Tranparent(CAObjHandle worksheetHandle, const char *fileName, int top, int left, int width, int height) { HRESULT __result = S_OK; CAObjHandle shapesHandle = 0; CAObjHandle pictureHandle = 0; __caErrChk (Excel_GetProperty (worksheetHandle, ExcelRpt_GetErrorInfo(), Excel_WorksheetShapes, CAVT_OBJHANDLE, &shapesHandle)); __caErrChk (Excel_ShapesAddPicture (shapesHandle, ExcelRpt_GetErrorInfo(), fileName, ExcelConst_msoFalse, ExcelConst_msoTrue, left, top, width, height, &pictureHandle)); //--To Set The Picture as Tranparent __caErrChk (Excel_SetProperty (pictureHandle, ExcelRpt_GetErrorInfo(), Excel_PictureFmtTransparentBackground, CAVT_LONG, ExcelConst_msoTrue)); __caErrChk (Excel_SetProperty (pictureHandle, NULL, Excel_PictureFmtTransparencyColor, CAVT_LONG, (long)RGB(255,255,255))); Error: CA_DiscardObjHandle(shapesHandle); CA_DiscardObjHandle(pictureHandle); return __result; } I modified the code from the NI's excelreport.c. But the SetProperty function always return errors. The picture was inserted to the file successfully, but not set as transparent. Anyone can help me?

[dcl9000]

Hi! Yunhua,

It's a bit tricky to change the transparency of an inserted picture in MS Excel, Word, or Powerpoint.

A direct inserted picture doesn't seem to allow you to change its transparency, at least in my MS Office 2003 version.

You need to create a "shape" first then "fill the shape" with a "user picture". 

The picture becomes a "fill effect" of the shape, which allows you to change its transparency.

Try to do it manually first and you'll understand this procedure.

Following C code works with my CVI 6.0 and Excel 2003 to realize what you have asked. 

Be sure to include excel2000.c and excel2000.fp in your CVI project.

/**************************************************************************************************************/

//----------------------------------------------------------------------------
// Insert a picture to the open Excel sheet and set transparency
// NOTE: An inserted picture won't allow you to change its transparency.
//       You need to create a shape (with correct scaled size) and then fill
//       the shape with the picture as its background. Then you can change
//       its transparency.
//----------------------------------------------------------------------------

HRESULT ExcelRpt_InsertPicture_Set_Tranparent (void)
{
   HRESULT  error=0 ;
   CAObjHandle shapesHandle = 0 ;
   CAObjHandle shapesShapeHandle = 0 ;
   CAObjHandle shapesShapeFillHandle = 0 ;
   CAObjHandle shapesPictureHandle = 0;
   char file_name[300];
   double top=100, left=100, width=576, height=480;    // final photos dimensions is width x height    
  

   /*----
    NOTE: The top and left is the coordinates of the top-left corner of the photo.
         (0,0) is the the top-left corner of the worksheet.
         The width and height is the final width and height of the photo appears in the Excel worksheet.
         Excel will re-scale it to match the size assigned by these numbers.
   ----*/
      
   
   GetProjectDir (file_name);           // must include complete directory info in the file name
   strcat (file_name, "\\D22-U2.jpg");
  
   SetWaitCursor (1);  

   // .... assume an Excel worksheet is already open and activated .....

   
   error = Excel_GetProperty (ExcelWorksheetHandle, &ErrorInfo, Excel_WorksheetShapes,
                              CAVT_OBJHANDLE, &shapesHandle) ;
   if (error<0)  goto Error ;
  
   error = Excel_ShapesAddShape (shapesHandle, &ErrorInfo, ExcelConst_msoShapeRectangle,
                                 left, top, width, height, &shapesShapeHandle) ;
   if (error<0)  goto Error ;
 
   error = Excel_GetProperty (shapesShapeHandle, &ErrorInfo, Excel_ShapeFill,
                              CAVT_OBJHANDLE, &shapesShapeFillHandle);
   if (error<0)  goto Error ;
  
   error = Excel_FillFormatUserPicture (shapesShapeFillHandle, &ErrorInfo, file_name) ;  
   if (error<0)  goto Error ;
  
   error = Excel_SetProperty (shapesShapeFillHandle, &ErrorInfo, Excel_FillFormatTransparency,
                              CAVT_DOUBLE, 0.5);     // 0.5=50% transparent
   if (error<0)  goto Error ;
   
 
Error:
    SetWaitCursor (0);
    CA_DiscardObjHandle(shapesHandle);
    CA_DiscardObjHandle(shapesShapeHandle);
    CA_DiscardObjHandle(shapesShapeFillHandle);
    CA_DiscardObjHandle(shapesPictureHandle);
    if (error<0)  ReportAppAutomationError(error);
   
   return  error ;
}