how I can place an image just in one cell using npoi

frosty picture frosty · Apr 18, 2011 · Viewed 10.7k times · Source

I'm using npoi to generate excel docs. I have a requirement to add images to cells. Using the following code i can insert images into my doc. However the image span many cells. How can i ensure that the image just fits inside once cell.

public ActionResult NPOICreate()
{
    try
    {
        FileStream fs = new FileStream(Server.MapPath(@"\Content\NPOITemplate.xls"), FileMode.Open, FileAccess.ReadWrite);
        HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);
        var sheet = templateWorkbook.GetSheet("Sheet1");
        var patriarch = sheet.CreateDrawingPatriarch();
        HSSFClientAnchor anchor;
        anchor = new HSSFClientAnchor(0, 0, 0, 0, (short)4, 2, (short)6, 5);
        anchor.AnchorType = 2;
        var picture = patriarch.CreatePicture(anchor, LoadImage(@"D:\dev\Website/HumpbackWhale.jpg", templateWorkbook));
        picture.Resize();
        picture.LineStyle = HSSFPicture.LINESTYLE_DASHDOTGEL;
        sheet.ForceFormulaRecalculation = true;
        MemoryStream ms = new MemoryStream();
        templateWorkbook.Write(ms);
        TempData["Message"] = "Excel report created successfully!";
        return File(ms.ToArray(), "application/vnd.ms-excel", "NPOINewFile.xls");
    }
    catch (Exception ex)
    {
        TempData["Message"] = "Oops! Something went wrong.";

        return RedirectToAction("NPOI");
    }

}

Answer

mjv picture mjv · Apr 19, 2011

To the best of my knowledge, it isn't possible to assign an image object to a particular cell within Excel.
This is not a limitation of POI/NPOI, but rather the way Excel works: Pictures inserted into a spreadsheet just float (over the spreadsheet grid per se)...
At best one can make believe it is in the cell by ensuring that the size and position of the cell and of the picture match perfectly. There is a property of the picture (See "Format Picture" dialog, Properties section, also accessible through POI I'm sure) which allows to specify whether the picture will move and/or resize itself following actions on rows/cells surrounding it, but in the end, pictures remain a floating object very loosely related to a cell at best.

A common trick to assign a picture to a cell is by way of comments. The picture is then more formally bound to the cell but it is not shown as content but rather a comment data.
See for example this recipe. The idea is to use the background of the comment to be a color with a special fill effect which is the picture we wish to associate with the cell. Here again, there's got to be a way of achieving this programmatically with NPOI, but I cannot affirm this firsthand.