xlsgen > overview > Pictures

Pictures are a convenient type of object that adds up to the formatting of your Excel workbooks. A typical usage of pictures is company logos. The pictures are exposed in xlsgen the following way :

 


Rich support for pictures in xlsgen

 

xlsgen supports all major picture file formats including :

Each Excel workbook supports one or more pictures in each worksheet. There is no known limitations in the amount of pictures you can add.

Pictures are added by embedding a blob inside the Excel file. Also, xlsgen factorizes pictures and stores the same picture only once, which helps keep the file low in size. xlsgen uses two mechanisms to avoid inserting the same picture twice : 1) the filepath 2) the actual content.

Each time a picture is inserted, you can accurately position it in any worksheet. The user is provided two models to position a picture, including :

Below is a drawing explaining how it works :


Positioning a picture in the Excel grid

There are two separate programming interfaces to insert a picture. The main and richest one is through the Pictures collection. The other interfaces is through the NewPicture method of the IXlsWorksheet object, as well as the NewPictureInPixels method.

 

Inserting a picture

When inserting a picture, what's really only needed is a filename and the top-left corner where to insert it. When doing so, xlsgen inserts the picture at a normal scale and calculates the offsets with the Excel grid. If you'd like to put more constraints, you are free to use some of the options of the picture object including the ability to :

Here are two examples :

Java code

// insert picture at B3
XlsPicture pic1 = wksht.getPictures().Add("picture.gif");
pic1.putLeftColumn(2);
pic1.putTopCell(3);

wksht.putLabel(3,6, "picture inserted at default size");

// insert picture at B15, double its size, unlocked aspect ratio
XlsPicture pic2 = wksht.getPictures().Add("picture.gif");
pic2.putLeftColumn(2);
pic2.putTopCell(15);
pic2.putScaleWidth(200);
pic2.putScaleHeight(200);
pic2.putLockAspectRatio(false);

wksht.putLabel(11,6, "picture inserted at twice its size");
wksht.putLabel(12,6, "with an unlocked aspect ratio when the user stretches it.");
VB code

' insert picture at B3
Dim pic1 As IXlsPicture
Set pic1 = wksht.Pictures.Add("picture.gif")
pic1.LeftColumn = 2
pic1.TopCell = 3

wksht.Label(3, 6) = "picture inserted at default size"

' insert picture at B15, double its size, unlocked aspect ratio
Dim pic2 As IXlsPicture
Set pic2 = wksht.Pictures.Add("picture.gif")
pic2.LeftColumn = 2
pic2.TopCell = 15
pic2.ScaleWidth = 200
pic2.ScaleHeight = 200
pic2.LockAspectRatio = False

wksht.Label(11, 6) = "picture inserted at twice its size"
wksht.Label(12, 6) = "with an unlocked aspect ratio when the user stretches it."
C# code

// insert picture at B3
IXlsPicture pic1 = wksht.Pictures.Add("picture.gif");
pic1.LeftColumn = 2;
pic1.TopCell = 3;

wksht.set_Label(3,6, "picture inserted at default size");

// insert picture at B15, double its size, unlocked aspect ratio
IXlsPicture pic2 = wksht.Pictures.Add("picture.gif");
pic2.LeftColumn = 2;
pic2.TopCell = 15;
pic2.ScaleWidth = 200;
pic2.ScaleHeight = 200;
pic2.LockAspectRatio = 0;

wksht.set_Label(11,6, "picture inserted at twice its size");
wksht.set_Label(12,6, "with an unlocked aspect ratio when the user stretches it.");
C/C++ code

// insert picture at B3
xlsgen::IXlsPicturePtr pic1 = wksht->Pictures->Add(L"picture.gif");
pic1->LeftColumn = 2;
pic1->TopCell = 3;

wksht->Label[3][6] = L"picture inserted at default size";

// insert picture at B15, double its size, unlocked aspect ratio
xlsgen::IXlsPicturePtr pic2 = wksht->Pictures->Add(L"picture.gif");
pic2->LeftColumn = 2;
pic2->TopCell = 15;
pic2->ScaleWidth = 200;
pic2->ScaleHeight = 200;
pic2->LockAspectRatio = FALSE;

wksht->Label[11][6] = L"picture inserted at twice its size";
wksht->Label[12][6] = L"with an unlocked aspect ratio when the user stretches it.";

 

The old way of inserting a picture

(kept only for backwards compatibility reason)

Let's assume your top-left corner would be (r=4,c=3) and your bottom-right corner would be (r=9, c=4).

The following code snippets add a picture in the first worksheet.

VB code

Dim wksht1 As IXlsWorksheet
Set wksht1 = wbk.AddWorksheet("sheet1")

' add a picture from location C:\mypicture.jpeg
' top-left corner = (4,3)
' bottom-right corner = (9,4)
' topoffset = 0 (in 1440_ith units)
' leftoffset = 300 (in 1440_ith units)
' bottomoffset = 0 (in 1440_ith units)
' rightoffset = 600 (in 1440_ith units)

wksht1.NewPicture("C:\mypicture.jpeg", 4,3, 9,4, 0,300,0,600)
C# code

IXlsWorksheet wksht1 = wbk.AddWorksheet( "sheet1" );

// work with this worksheet

// add a picture from location C:\mypicture.jpeg
// top-left corner = (4,3)
// bottom-right corner = (9,4)
// topoffset = 0 (in 1440_ith units)
// leftoffset = 300 (in 1440_ith units)
// bottomoffset = 0 (in 1440_ith units)
// rightoffset = 600 (in 1440_ith units)

wksht1.NewPicture(@"C:\mypicture.jpeg", 4,3, 9,4, 0,300,0,600);
C/C++ code

xlsgen::IXlsWorksheetPtr wksht1;
wksht1 = wbk->AddWorksheet( L"sheet1" );

// add a picture from location C:\mypicture.jpeg
// top-left corner = (4,3)
// bottom-right corner = (9,4)
// topoffset = 0 (in 1440_ith units)
// leftoffset = 300 (in 1440_ith units)
// bottomoffset = 0 (in 1440_ith units)
// rightoffset = 600 (in 1440_ith units)

wksht1->NewPicture("C:\\mypicture.jpeg", 4,3, 9,4, 0,300,0,600);

The programming interface of worksheets is described here.

 

Inserting a picture from a memory buffer

It is also possible to pass a memory buffer and a picture file type instead of a file, to accomodate scenarios where everything is done in memory. Here are examples how to do so in VB and C/C++ :

Java code

// In this example, we read an existing file and make it a buffer in memory
// for the sake of providing a full example, but it works as well if you have a buffer in memory
// passed by some other function in your application.

XlsEngine engine = new XlsEngine("./../../../xlsgen.dll");

XlsWorkbook workbook = engine.New("myfile.xls");

XlsWorksheet wksht = workbook.AddWorksheet("sheet1");

try
{
  FileInputStream fis = new FileInputStream("IMG_2837.jpg");
  int fileSize = fis.available();
  byte[] bs = new byte[fileSize];

  fis.read(bs);

  fis.close();

  ' Insert the picture by passing :
  '   - the byte[] buffer
  '   - the picture type
  '   - the location (row1, col1, row2, col2) and offsets
  wksht.NewPictureInMemory(bs, xlsgen.picturetype_jpeg, 1,4, 6,8, 0,0,0,0);
}
catch(Exception e)
{
  wksht.putLabel(1,2, "Memory picture insertion did not work");
}

workbook.Close();
VB code

' In this example, we read an existing file and make it a buffer in memory
' for the sake of providing a full example, but it works as well if you have a buffer in memory
' passed by some other function in your application.

Public Sub Process()

' Read a picture IMG_2837.jpg
Dim Line As String * 4096
Dim s As String

Open "IMG_2837.jpg" For Binary Access Read As #1
Do While Not EOF(1)
Get #1, , Line
s = s & Line
Loop
Close #1

Dim buffer_input() As Byte
ReDim buffer_input(Len(s))

For i = 1 To Len(s)
  buffer_input(i - 1) = Asc(Mid(s, i, 1))
Next

' Create an xlsgen engine instance
Dim engine As CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

Dim wbk As IXlsWorkbook
Set wbk = engine.New("sample.xls")

Dim wksht As IXlsWorksheet
Set wksht = wbk.AddWorksheet("sheet1")

' Insert the picture by passing :
'   - the byte array buffer
'   - the picture type
'   - the location (row1, col1, row2, col2) and offsets
wksht.NewPictureInMemory buffer_input, picturetype_jpeg, 2, 2, 5, 4, 0, 0, 0, 0

' Finalize the Excel file
wbk.Close
C# code

// In this example, we read an existing file and make it a buffer in memory
// for the sake of providing a full example, but it works as well if you have a buffer in memory
// passed by some other function in your application.

using System.IO;
using System.Runtime.InteropServices;
using xlsgen;

// read a picture

System.IO.FileStream fs = new System.IO.FileStream("IMG_2837.jpg", System.IO.FileMode.Open);

int length = (int) fs.Length;

byte[] fileBytes = new byte[length];

BinaryReader br = new BinaryReader(fs);
br.Read(fileBytes, 0, length);
br.Close();
fs.Close();

// create a simple Excel file and add a picture from a memory buffer

xlsgen.CoXlsEngine engine = new xlsgen.CoXlsEngine();

xlsgen.IXlsWorkbook wbk = engine.New ("sample.xls");
xlsgen.IXlsWorksheet wksht = wbk.AddWorksheet("Sheet1");

wksht.NewPictureInMemory(fileBytes, xlsgen.enumPictureType.picturetype_jpeg, 
	2, 2, // top-left corner cell
	5, 4, // bottom-right corner cell
	0, //top offset (0 means aligned to grid)
	0, //left offset (0 means aligned to grid)
	0, //bottom offset (0 means aligned to grid)
	0 //right offset (0 means aligned to grid)
        );

wbk.Close();

System.Runtime.InteropServices.Marshal.ReleaseComObject(engine);
C/C++ code

// In this example, we read an existing file and make it a buffer in memory
// for the sake of providing a full example, but it works as well if you have a buffer in memory
// passed by some other function in your application.

xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

xlsgen::IXlsWorkbookPtr wbk;
wbk = engine->New( L"sample.xls" );

xlsgen::IXlsWorksheetPtr wksht = wbk->AddWorksheet("Sheet1");


FILE* f = fopen("IMG_2837.jpg", "rb"); // max 8KB for this buffer
BYTE membuf[8192 + 1];
int membuflen = 0;
DWORD dwRead;

while ((dwRead = fread(membuf + membuflen, 1, 4096, f)) > 0)
    membuflen += dwRead;

fclose(f);

SAFEARRAY* pSA = ::SafeArrayCreateVector(VT_UI1, 0, membuflen);

BYTE HUGEP *plong = NULL;

::SafeArrayAccessData(pSA, (void HUGEP**)&plong);
if (plong)
{
    memcpy(plong, membuf, membuflen);

    ::SafeArrayUnaccessData(pSA);

    VARIANT vinput;
    vinput.vt = VT_ARRAY | VT_UI1;
    vinput.parray = pSA;

    _variant_t v(vinput);

    wksht->NewPictureInMemory(v, xlsgen::picturetype_jpeg, 
      2, 2, /* top-left corner cell*/
      5, 4, /* bottom-right corner cell*/
      0, /*top offset (0 means aligned to grid)*/
      0, /*left offset (0 means aligned to grid)*/
      0, /*bottom offset (0 means aligned to grid)*/
      0 /*right offset (0 means aligned to grid)*/);
}

::SafeArrayDestroy(pSA);

wbk->Close();

 

Inserting a picture from Internet

The same method call used for inserting pictures from a hard drive can actually be used to specify a url where a picture can be downloaded from.

The url needs not specify an image file by itself, nor be a static url. xlsgen invokes the http request on your behalf, and our engine reads the response headers in order to know what is the content type of the picture being downloaded (for instance image/gif represents a GIF picture). This is transparent to the client application and the web server hosting the picture.

C/C++ code
xlsgen::IXlsPicturePtr pic1 = worksheet->Pictures->Add(L"http://xlsgen.arstdesign.com/demo0.gif");
pic1->TopCell = 2;
pic1->LeftColumn = 2;
pic1->Borders->Style = xlsgen::chartborderstyle_solid;
pic1->Borders->Color = xlsgen::colorRed;

 

Inserting a picture from a HBITMAP

Developers using the regular Windows graphics API know that a bitmap is stored as a HBITMAP. Since this feature is independent of which programming language you are using, the Windows API being exposed to each programming language, it is of particular interest to make it possible to insert a picture with xlsgen using a simple HBITMAP as parameter. That is what the following piece of C/C++ code does :

// build a HBITMAP by loading a bitmap in memory

HBITMAP hBmp = (HBITMAP) ::LoadImage(hInstance, 
                                     "350_original.bmp", 
                                     IMAGE_BITMAP, 
                                     0, 0, 
                                     LR_LOADFROMFILE);

// create a simple spreadsheet

xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

xlsgen::IXlsWorkbookPtr wbk = engine->New( L"sample.xls" );
xlsgen::IXlsWorksheetPtr wksht = wbk->AddWorksheet( L"samplesheet" );

// insert the picture by passing the HBITMAP

AddPicture(wksht, hBmp, 5/*top*/, 2/*left*/, 15/*bottom*/, 6/*right*/);

wbk->Close();


void AddPicture(xlsgen::IXlsWorksheetPtr wksht, HBITMAP hBmp, int top, int left, int bottom, int right)
{
    HDC hdcScreen = CreateDC("DISPLAY", NULL, NULL, NULL); 
    HDC hdcCompatible = CreateCompatibleDC(hdcScreen); 

    HBITMAP hOldBmp = (HBITMAP) ::SelectObject(hdcCompatible, hBmp);

    PBITMAPINFO pbi;

    {
        // Retrieve the bitmap color format, width, and height. 
        BITMAP bmp; 
        GetObject(hBmp, sizeof(BITMAP), (LPSTR)&bmp);

        // Convert the color format to a count of bits. 
        WORD cClrBits = (WORD)(bmp.bmPlanes * bmp.bmBitsPixel); 
        if (cClrBits == 1) 
            cClrBits = 1; 
        else if (cClrBits <= 4) 
            cClrBits = 4; 
        else if (cClrBits <= 8) 
            cClrBits = 8; 
        else if (cClrBits <= 16) 
            cClrBits = 16; 
        else if (cClrBits <= 24) 
            cClrBits = 24; 
        else cClrBits = 32; 

        // Allocate memory for the BITMAPINFO structure. (This structure 
        // contains a BITMAPINFOHEADER structure and an array of RGBQUAD 
        // data structures.) 

         if (cClrBits != 24) 
             pbi = (PBITMAPINFO) LocalAlloc(LPTR, 
                        sizeof(BITMAPINFOHEADER) + 
                        sizeof(RGBQUAD) * (1 << cClrBits)); 

        // There is no RGBQUAD array for the 24-bit-per-pixel format. 

         else 
             pbi = (PBITMAPINFO) LocalAlloc(LPTR, 
                        sizeof(BITMAPINFOHEADER)); 

        // Initialize the fields in the BITMAPINFO structure. 

        pbi->bmiHeader.biSize = sizeof(BITMAPINFOHEADER); 
        pbi->bmiHeader.biWidth = bmp.bmWidth; 
        pbi->bmiHeader.biHeight = bmp.bmHeight; 
        pbi->bmiHeader.biPlanes = bmp.bmPlanes; 
        pbi->bmiHeader.biBitCount = bmp.bmBitsPixel; 
        if (cClrBits < 24) 
            pbi->bmiHeader.biClrUsed = (1 << cClrBits); 

        // If the bitmap is not compressed, set the BI_RGB flag. 
        pbi->bmiHeader.biCompression = BI_RGB; 

        // Compute the number of bytes in the array of color 
        // indices and store the result in biSizeImage. 
        // For Windows NT, the width must be DWORD aligned unless 
        // the bitmap is RLE compressed. This example shows this. 
        // For Windows 95/98/Me, the width must be WORD aligned unless the 
        // bitmap is RLE compressed.
        pbi->bmiHeader.biSizeImage = ((pbi->bmiHeader.biWidth * cClrBits +31) & ~31) /8
                                      * pbi->bmiHeader.biHeight; 
        // Set biClrImportant to 0, indicating that all of the 
        // device colors are important. 
        pbi->bmiHeader.biClrImportant = 0; 
    }

    PBITMAPINFOHEADER pbih = (PBITMAPINFOHEADER) pbi;

    LPBYTE lpBits = (LPBYTE) GlobalAlloc(GMEM_FIXED, pbih->biSizeImage);

    int nbScanlines = GetDIBits(hdcCompatible, hBmp, 0, (WORD) pbih->biHeight, lpBits, pbi, DIB_RGB_COLORS);

    BITMAPFILEHEADER hdr;       // bitmap file-header 

    int membufferlen = sizeof(BITMAPFILEHEADER) + 
                 pbih->biSize + 
                 pbih->biClrUsed * sizeof(RGBQUAD) + 
                 pbih->biSizeImage;

    SAFEARRAY* pSA = ::SafeArrayCreateVector(VT_UI1, 0, membufferlen);

    BYTE HUGEP* plong = NULL;

    ::SafeArrayAccessData(pSA, (void HUGEP**)&plong);
    if (plong)
    {

        hdr.bfType = 0x4d42;        // 0x42 = "B" 0x4d = "M" 
        hdr.bfSize = (DWORD) membufferlen; 
        hdr.bfReserved1 = 0; 
        hdr.bfReserved2 = 0; 

        // Compute the offset to the array of color indices. 
        hdr.bfOffBits = (DWORD) sizeof(BITMAPFILEHEADER) + 
                        pbih->biSize + pbih->biClrUsed * sizeof (RGBQUAD); 

        
        // Copy the BITMAPFILEHEADER into the .BMP file. 
        memcpy(plong, &hdr, sizeof(BITMAPFILEHEADER));

        // Copy the BITMAPINFOHEADER and RGBQUAD array into the file. 
        memcpy(plong + sizeof(BITMAPFILEHEADER), pbih, sizeof(BITMAPINFOHEADER) + pbih->biClrUsed * sizeof (RGBQUAD));

        // Copy the array of color indices into the .BMP file. 
        DWORD cb = pbih->biSizeImage; 
        memcpy(plong + sizeof(BITMAPFILEHEADER) + sizeof(BITMAPINFOHEADER) + pbih->biClrUsed * sizeof (RGBQUAD), lpBits, cb);

        ::SafeArrayUnaccessData(pSA);

        VARIANT vinput;
        vinput.vt = VT_ARRAY | VT_UI1;
        vinput.parray = pSA;

        _variant_t v(vinput);

        wksht->NewPictureInMemory(v, xlsgen::picturetype_bmp, 
          top, left,
          bottom, right,
          0, /*top offset (0 means aligned to grid)*/
          0, /*left offset (0 means aligned to grid)*/
          0, /*bottom offset (0 means aligned to grid)*/
          0 /*right offset (0 means aligned to grid)*/);
    }

    ::SafeArrayDestroy(pSA);

    ::SelectObject(hdcCompatible, hOldBmp);

    ::DeleteDC(hdcCompatible);
    ::DeleteDC(hdcScreen);
}

 

Introspecting existing pictures

Other than adding pictures, xlsgen has an introspection programming interface which lets you query the position and size of any existing picture in a file.

Note that, because the same programming interface actually works with other objects such as vector drawings and charts, the main name of the programming interface is Shapes, not Pictures.

In the following scenario, what we do is get the position and size of an existing shape, then insert a picture on top of that shape.

VB code

Dim wbk As IXlsWorkbook
wbk = engine.Open("Book1_existingshapes.xls", "output.xls")

Dim wksht As IXlsWorksheet
wksht = wbk.WorksheetByIndex(2)

Dim shape As IXlsShape
shape = wksht.Shapes.Item(1)

Dim nameShape As String
nameShape = shape.Name

Dim cellLeft
Dim cellRight
Dim cellTop
Dim cellBottom

cellLeft = shape.LeftColumn
cellRight = shape.RightColumn
cellTop = shape.TopCell
cellBottom = shape.BottomCell

Dim cellLeftOffset
Dim cellRightOffset
Dim cellTopOffset
Dim cellBottomOffset

cellLeftOffset = shape.LeftOffset
cellRightOffset = shape.RightOffset
cellTopOffset = shape.TopOffset
cellBottomOffset = shape.BottomOffset

' insert picture databar1.jpg using above information
wksht.NewPicture("databar1.jpg", _
                 cellTop, cellLeft, _
                 cellBottom - 1, cellRight - 1, _
                 cellTopOffset, cellLeftOffset, _
                 -cellBottomOffset, -cellRightOffset)

wbk.Close()
C/C++ code

xlsgen::IXlsWorkbookPtr wbk = engine->Open(L"Book1_existingshapes.xls", L"output.xls");

xlsgen::IXlsWorksheetPtr wksht = wbk->WorksheetByIndex[2];

xlsgen::IXlsShapePtr shape = wksht->Shapes->Item[1];

_bstr_t nameShape = shape->Name;

int cellLeft = shape->LeftColumn;
int cellRight = shape->RightColumn;
int cellTop = shape->TopCell;
int cellBottom = shape->BottomCell;

int cellLeftOffset = shape->LeftOffset;
int cellRightOffset = shape->RightOffset;
int cellTopOffset = shape->TopOffset;
int cellBottomOffset = shape->BottomOffset;

// insert picture databar1.jpg using above information
wksht->NewPicture(L"databar1.jpg",
                 cellTop, cellLeft,
                 cellBottom > 1 ? (cellBottom - 1) :  1, cellRight > 1 ? (cellRight - 1) :  1,
                 cellTopOffset, cellLeftOffset,
                 cellBottom > 1 ? - cellBottomOffset : (1024 - cellBottomOffset), 
                 cellRight > 1 ? - cellRightOffset : (1024 - cellRightOffset));

wbk->Close();

 

Deleting pictures or shapes

It is possible to delete one or more pictures in an existing file. The Shape object supports a Delete method.

Thanks to the fact that Shapes are an abstraction for pictures, vector drawings, charts, visual components, ...it is possible to use the same method in order to delete those pictures, vector drawings, charts, visual components, ...

 

Extracting pictures

Any picture in existing Excel files can be extracted. The extraction is either directly written to a file, or to a memory block.

 

Changing the display order of pictures or shapes

It is possible to change the display order of any picture or shape. The Shape object supports a BringToFront method. By iterating this mechanism, it is possible to redefine the display order of any picture or shape (shape are pictures, vector drawings, charts, visual components, ...).

 

Specifying the chroma key color
C/C++ code
xlsgen::IXlsPicturePtr picture1 = worksheet->Pictures->Add( L"input\\a.gif" );
picture1->LeftColumn = 1;
picture1->TopCell = 1;
picture1->ChromaKeyColor = 0xFF0000; // RED chroma key


Setting the chroma key color lets one see through the picture

 

xlsgen documentation. © ARsT Design all rights reserved.