Add an image to excel using $.ig.excel

Hello All,

My question is that,

How do I add an image in a worksheet using Ignite UI jquery excel library. I want to use ig.excel library in my angular js controller. I have added below code in controller.js file . Please check and give me some solution.

var workbook = new $.ig.excel.Workbook($.ig.excel.WorkbookFormat.excel2007);
var sheet = workbook.worksheets().add('Sheet1');
var image = new Image(100, 200);
image.src = 'banner.jpg';
var imageShape = new $.ig.excel.WorksheetImage(image);

imageShape.topLeftCornerCell().getCellAddressString(sheet.rows(0), 0, 0, true)
imageShape.bottomRightCornerCell().getCellAddressString(sheet.rows(5), 3, 0, true)
sheet.shapes().add(imageShape);

while running this code , below error is shown
Cannot call method 'getCellAddressString' of null

I am stuck with this problem . Please help.

Thanks.

Hello,

API documentation says that there is not "getCellAddressString" method exposed by the topLeftCornerCell. You should instead pass a cell object to it as follows:

imageShape.topLeftCornerCell(sheet.getCell('C9'));

Please let me know if this solves the situation.

Hello,

Thanks for the reply, but it does not solve the problem. It shows the below error

Cannot call method 'getHashCode' of null

Please help asap

Thanks,

Hi,

I am currently investigating this error and will update once I have more information.

Hello,

Okay. Please give some solution as soon as possible.

Thanks,

Hello,

I have investigated your issue, and I have asked our engineering staff to examine this further.  To ensure that it will receive attention, I have logged this behavior in our internal tracking system with a Development ID of 221812.  The next step will be for a developer to review my investigation and confirm my findings or to offer a fix, or other resolution.

I will leave this case open and update you with any new information after the review.  You can also continue to send updates to this case at any time.

You can view the status of the development issue connected to this case by selecting the "Development Issues" tab when viewing this case on the web site.  

Please let me know if you need more information.

Hello,

You can track the status of the issue in the following case CAS*175080*K2G2H9

Hello,

I have been facing the same issue, has this issue been fixed already?

Hello,

No , the issue hasn't solved it yet. I dropped this process and took another way to attach an image in excel. I had created an html table and export it to an excel. In that way I can be able to attach an image. Hope that helps.

Thanks.

Do you have a sample of the workaround of the process you applied for your problem? And did you use the ig worksheet to add image to excel?

Hi,

The issue is released and will be available in the service release trhat is scheduled to roll out on 24 March 2017. Please let me know if you have further questions.

Hi,

Could you provide the code for add an image to the excel sheet

Here it is:  

 var image = new Image();
   image.width = 400;
   image.height = 400;
   image.src = "banner.jpg";
   var imageShape = new $.ig.excel.WorksheetImage(image);  
   imageShape.topLeftCornerCell(sheet.getCell('C9'));
   imageShape.bottomRightCornerCell(sheet.getCell('D10'));
   worksheet.shapes().add(imageShape);

This code was suggested previously

It shows the below error

Cannot call method 'getHashCode' of null.

You mentioned to have a workaround for it, in your new release do you have the code for it?

Thanks,

Phanindra.

I am also getting the same error. The code is below. Any Idea what I am missing here?

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"&gt;
<head>
<title></title>

<style>
.right {
float: right;
}

#exportButton {
float: left;
}
</style>
<!–Required scripts–>
<script src="http://code.jquery.com/jquery-1.11.3.min.js"></script&gt;
<!– External files for exporting –>
<script src="http://www.igniteui.com/js/external/FileSaver.js"></script&gt;
<script src="http://www.igniteui.com/js/external/Blob.js"></script&gt;

<!– Ignite UI Loader Script –>
<script src="http://cdn-na.infragistics.com/igniteui/2016.2/latest/js/infragistics.loader.js"></script&gt;

<script>
$.ig.loader({
scriptPath: "http://cdn-na.infragistics.com/igniteui/2016.2/latest/js/&quot;,
cssPath: "http://cdn-na.infragistics.com/igniteui/2016.2/latest/css/&quot;,
resources: 'modules/infragistics.util.js,' +
'modules/infragistics.documents.core.js,' +
'modules/infragistics.excel.js'
});
</script>
<script>

function createFormattingWorkbook() {

var workbook = new $.ig.excel.Workbook($.ig.excel.WorkbookFormat.excel2007);
var sheet = workbook.worksheets().add('Sheet1');
sheet.columns(0).setWidth(96, $.ig.excel.WorksheetColumnWidthUnit.pixel);
sheet.columns(4).setWidth(80, $.ig.excel.WorksheetColumnWidthUnit.pixel);
sheet.columns(6).setWidth(96, $.ig.excel.WorksheetColumnWidthUnit.pixel);

var image = new Image();
image.width = 400;
image.height = 400;
image.src = "https://cdn2.iconfinder.com/data/icons/social-18/512/Facebook-2-64.png&quot;;
var imageShape = new $.ig.excel.WorksheetImage(image);
imageShape.topLeftCornerCell(sheet.getCell('C9'));
imageShape.bottomRightCornerCell(sheet.getCell('D10'));
sheet.shapes().add(imageShape);

// Add merged regions for regions A1:D2 and E1:G2
var mergedCellA1D2 = sheet.mergedCellsRegions().add(0, 0, 1, 3);
var mergedCellE1G2 = sheet.mergedCellsRegions().add(0, 4, 1, 6);

// Add two large headers in merged cells above the data
mergedCellA1D2.value('Acme, Inc.');
mergedCellA1D2.cellFormat().alignment($.ig.excel.HorizontalCellAlignment.center);
mergedCellA1D2.cellFormat().fill($.ig.excel.CellFill.createSolidFill('#ED7D31'));
mergedCellA1D2.cellFormat().font().colorInfo(new $.ig.excel.WorkbookColorInfo($.ig.excel.WorkbookThemeColorType.light1));
mergedCellA1D2.cellFormat().font().height(16 * 20);

mergedCellE1G2.value('Invoice #32039');
mergedCellE1G2.cellFormat().alignment($.ig.excel.HorizontalCellAlignment.center);
mergedCellE1G2.cellFormat().fill($.ig.excel.CellFill.createSolidFill('#FFC000'));
mergedCellE1G2.cellFormat().font().colorInfo(new $.ig.excel.WorkbookColorInfo($.ig.excel.WorkbookThemeColorType.light1));
mergedCellE1G2.cellFormat().font().height(16 * 20);

// Format some rows and columns that should have similar formatting so we don't have to set it on individual cells.
sheet.rows(2).cellFormat().font().bold(true);
sheet.columns(4).cellFormat().formatString('$#,##0.00_);[Red]($#,##0.00)');
sheet.columns(6).cellFormat().formatString('$#,##0.00_);[Red]($#,##0.00)');

// Add a light color fill to all cells in the A3:G17 region to visually separate it from the rest of the sheet. We can iterate
// all cells in the regions by getting an enumerator for the region and enumerating each item.
var light1Fill = $.ig.excel.CellFill.createSolidFill(new $.ig.excel.WorkbookColorInfo($.ig.excel.WorkbookThemeColorType.light1));
var cells = sheet.getRegion('A3:G17').getEnumerator();
while (cells.moveNext()) {
cells.current().cellFormat().fill(light1Fill);
}

// Populate the sheet with data
sheet.getCell('A3').value('Date');
sheet.getCell('B3').value('Description');
sheet.getCell('D3').value('Qty');
sheet.getCell('E3').value('Cost/Unit');
sheet.getCell('G3').value('Total');

sheet.getCell('A4').value(new Date('12/22/2014'));
sheet.getCell('B4').value('Garage Door');
sheet.getCell('D4').value(1);
sheet.getCell('E4').value(1875);
sheet.getCell('G4').applyFormula('=D4*E4');

sheet.getCell('A5').value(new Date('12/22/2014'));
sheet.getCell('B5').value('Trim');
sheet.getCell('D5').value(3);
sheet.getCell('E5').value(27.95);
sheet.getCell('G5').applyFormula('=D5*E5');

sheet.getCell('A6').value(new Date('12/22/2014'));
sheet.getCell('B6').value('Install/Labor');
sheet.getCell('D6').value(8);
sheet.getCell('E6').value(85);
sheet.getCell('G6').applyFormula('=D6*E6');

// Add a grand total which is bold and larger than the rest of the text to call attention to it.
sheet.getCell('E17').value('GRAND TOTAL');
sheet.getCell('E17').cellFormat().font().height(14 * 20);
sheet.getCell('E17').cellFormat().font().bold(true);

sheet.getCell('G17').applyFormula('=SUM(G4:G16)');
sheet.getCell('G17').cellFormat().font().height(14 * 20);
sheet.getCell('G17').cellFormat().font().bold(true);

// Save the workbook
saveWorkbook(workbook, "Formatting.xlsx");
}

function saveWorkbook(workbook, name) {
workbook.save({ type: 'blob' }, function (data) {
saveAs(data, name);
}, function (error) {
alert('Error exporting: : ' + error);
});
}

</script>

</head>
<body>
<button id="exportButton" onclick="createFormattingWorkbook()">Create File</button>
<br />
<img alt="Result in Excel" src="http://www.igniteui.com/images/samples/client-side-excel-library/excel-formatting.png&quot; />
</body>
</html>

Hi,

It turns out that the constructor for the Worksheet image only takes a data uri. You cannot provide an Image element.

Hello Maanesh,

It is recommended to use base 64 encoded image, currently you cannot pass an image element to create a WorksheetImage. If you want to use Image my suggestion is to draw it into a canvas and then use the toDataUrl to have it create a data url that you could then use when creating a WorksheetImage.

Code snippet:

// Add imagevar image = “data:image/png;base64,iVB…”

var imageShape = new $.ig.excel.WorksheetImage(image); imageShape.topLeftCornerCell(sheet.getCell(‘C9’));imageShape.bottomRightCornerCell(sheet.getCell(‘F10’));sheet.shapes().add(imageShape);

Let me know if I can be of further assistance.

I seem to be experiencing this exact same issue:

TypeError: Cannot read property 'getHashCode' of null

at Class.m (http://localhost:5000/lib/infragistics-igniteui/js/modules/infragistics.ext_collections.js:15:8550)

at Class.tryGetValue (http://localhost:5000/lib/infragistics-igniteui/js/modules/infragistics.ext_collections.js:15:9475)

at http://localhost:5000/lib/infragistics-igniteui/js/modules/infragistics.ext_collections.js:15:7563

at Class.item (http://localhost:5000/lib/infragistics-igniteui/js/modules/infragistics.ext_collections.js:15:7603)

at http://localhost:5000/lib/infragistics-igniteui/js/modules/infragistics.excel_serialization_openxml.js:27:14256

at http://localhost:5000/lib/infragistics-igniteui/js/modules/infragistics.excel_serialization_openxml.js:27:14285

at http://localhost:5000/lib/infragistics-igniteui/js/modules/infragistics.excel_serialization_openxml.js:27:14410

at Cons.bi (http://localhost:5000/lib/infragistics-igniteui/js/modules/infragistics.excel_serialization_openxml.js:27:14624)

at Cons.a8 (http://localhost:5000/lib/infragistics-igniteui/js/modules/infragistics.excel_serialization_openxml.js:27:19449)

at Cons.a7 (http://localhost:5000/lib/infragistics-igniteui/js/modules/infragistics.excel_serialization_openxml.js:27:18941)

This is using the 2017.1 release and the following code inside the "cellExported" delegate for GridExcelExporter:

// checking cell needs image transformation

//…

// empty the cell
args.xlRow.setCellValue(args.columnIndex, "");

var xlCell = args.xlRow.cells(args.columnIndex);

// load the image
var image = new Image(50, 50);
image.src = args.cellValue;
var imageShape = new $.ig.excel.WorksheetImage(image);

// insert image into the workbook
imageShape.topLeftCornerPosition({ x: 0.0, y: 0.0 });
imageShape.bottomRightCornerPosition({ x: 100.0, y: 100.0 });
imageShape.topLeftCornerCell(xlCell);
imageShape.bottomRightCornerCell(xlCell);
sender._worksheet.shapes().add(imageShape);

Any help on this issue would be great or is this just something that is not going to be supported?

You cannot use the image object directly, you need to convert the image to canvas, then convert the canvas todataUrl. 

var image = new Image(50, 50);

image.src = args.cellValue;

var canvas = document.createElement("canvas");

canvas.width = image.width;

canvas.height = image.height;

canvas.getContext("2d").drawImage(image, 0, 0);

var imageUrl = canvas.toDataURL();

var imageShape = new $.ig.excel.WorksheetImage(imageUrl );

This also does not work as by the time the export happens the image has not loaded and that a shape of 0 width and 0 height is added and leaves me with a blank cell.

Unfortunately this function does not allow me to even inject the code into the onload function as this is then not possible to wait on so that the export does not continue regardless and add no images.

Hi Mathew,

I don’t understand why you would not be able to execute code that creates an Excel worksheet in the image onload event, I think this should be possible, so you can send us a sample if you need assistance on this.

To allow for a way to construct a WorksheetImage, there is the ability for the constructor to take a data url for a base 64 encoded image. Image element cannot be passed in order to create a WorksheetImage. Passing image need to be handled by the developer – e.g. handle the load of the image to wait for the image to be available, then draw that image into a canvas and then use the toDataUrl to have it create a data url that they could then use when creating a WorksheetImage.
 
So to get the sample that was attached to work  we need to convert the image to a base 64 image string.
e.g.
var image = "data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAQUAAAAlCAMAAAB8kwwhAAABCFBMVEUAAAABmf9mZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmYBmf9mZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmYBmP9mZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmZmYCmf8Bmf8Cmf8Cmf8AmP9mZmYBmf////8Ajf8JmP/G4/8Alv8Ai/+p1v/u9/8Aj/+Wzv/O5/8anP8Ak/+Lxv8fnP8Ae//h8f9mtf90u/8Afv+i0v/U6//B4v+63/8Ahv9Mq/97wv9ovP/P6v9hs//azDGiAAAAOHRSTlMA8PjkOF4aQLqVj06ewqMG3bICCmU32FgVcexHD/Ijavs8H3jG8NPNmDAp5q1ShLaKf6n7OjTi4pWcsSAAAAeZSURBVGje5Zppe9JAEMdXQiEBwhUkCYGEq5yFAl7RYqtV0ar1Pr7/NzGzRyZXE330Vf3XRzab7DG/nZ3dDZBHdzwdH98RH8feHwovHpDbrHuP996fpz384/KTeH2f3GbdeRwXMkHdJbdZd9DYOAo/cespJNuNOf+RL1yegQ6vDmdCF+z/y/N9IgVbI/9CheTsrr1Ia6BmL4YkrrQC87kdbVo00c1zXzh8eAp6/+X907A+fXt8CFJoVZQN7cBIjvZSryjbJkuUfFV0IDaj6c1oFTVaGw9IVM2BotY7vU5bNvHxVUVR8jxd3E06veW0kqcXSikoZd0l3a0yGxGU0Wp4BXKd6U7XfASbsQRN6ITMS9sROYaxPnv7BPTh6sWTqD4zDHtGQXVdd0BxuFEMDdfN1SBx4gZ04mVY/lVvEy6kuzMSkaVi4Y4pcqveVYslFXG3D6Zu3YgKpJDzGmr6UEcS3my3WPtlP+/UsCoNhc2Iy5fU4hdXryMMIPMy4AunXuOUwpHrqgahwo4uazyBasB44WWE3djtYSVoI0rusuyZlz6KPWA2yTqBwtJ164LCYhy+XYLMvItSdc9jyTGYeJZC4e3+HH0BBjzPKbiqFqEgUQoV79ZE5RpxCp3xtO6C0FfZjW1o/u7YkDU225LcgeSEUVIEhSLNHJWdKbhWl+gqaAIXNHVqkIKEFGzW6LS6Xs/kJffNGjyeU1Y6Ha8jfbsix1m+8PzNOYCKUYDKCzdRGJCIsRVCjPIYcCA68CxX6obqABuP2CO2Ak3YYQpVyIMitRP3dEW4FjnPt3k6SMGgEE4sQjU06+6M9ydnMSuWpsFXSowLN1HYxykwxsYNFMpRClXaPxhe1iV0TD73/Up3iEXvzJokTAFA6iy0WwRr8ijICRR2Ee8zKhuI7MwnQBaFLNaI189A764Zhef06sMndvHqRl9Ab8im4PeqhSWY+9f8FUNy6ZCixHqIFGAiFAkqlUJRQEDBsEEsURJ2Ta++XF9fX11fnDEKP75cXFycXX9Np9CnNth/QkEJUrC9CmSv+75fmzBhACsqRkFlsyubAu8tZKOwHcmIUzj/+R709Quj8PEAhl+8S48LnVIOMAwz4wJSkIOANrDeeDljuBB31ySdgoLDm0lh6OX28wQVnIkyYkiMC0ghY0bMyy5QnSdQ2JR1qqM8pzCj8zznFReDbfQAgImRAqJGbp5BYZGj6+/idyisYMqSiPwdTV3HjMT9wkewO5uCRRzEEKKAqnIKY/1odNqH4Ee4tnR2DHt+/wc0SKRRwIDUmy2yKWxx/oc1ZOunivMW9gtI4QXbIhzO95kzAnybYVjmb6ZwQimg6sIVukvveY0+3WcYywCLpFPAfV9/VsiiMMMVKIrhlG+ZLJwRSOH621vQZ/SFDAqkRUdmEKWQ6+Wo+rMQhdyJH0xbfMc098PdKhTLFtM2l4UUmJWVvguS9AwK0BGHJMuRcC8ZpXABF2B0eEbsb6Dge2hnEKFgzvNURZtTaMNja5z1zTbfQEG5nB33BdzlHiEF/zyFd1IoKOgLcRnrDnPWZgKFcxBSyPAFkE4HeRCmUIyuEasRghelKkbBE9SkiLhQ93eW8wQKqEGDzopFKoWRiMrJGq6pT43iFPYMwJ9QICuoTCpsUvYLkDHBkeF7QDZreuBLBozOUlQOMsyRaZoQzfU4BW6iu0mlAM41ISkqStBzLe4LqKw1Ak21wJCxnEqhRde4/kAUccPaQqaMdgWjxyCRAilBo6kUYP3pF0mK8nRHEaHw+uoioKuvmRS4LLqIp1NgFtW7eI5C8TOVA/4xJEHtvPaGyRQWEG1SKPB9gUrSpEJfOYU9p/DxxTMUnCOyZoTQoJNJgZ8bTv3QN7GKXJDv8MXTVWtBS8G6ZpCCNioFKEzSKQy4m6Fs6ozWzg5QsEK+EFcmBcTQ+w0K3bYIDVVuFB4o2k2x7J50MY5PWUmkYNVZ5MWTYRoFUo1gWHXWXjca/lHE7oH3MQr7x5cfkwngjNinUyD5ZTYFku+ziWrn8L2COPXoPIHvF2pHbbjSkAILeEtauQ7cWxkUDLpAyyt6ZegndBA0cNxNV7yIGuMO+tPfUiBzKZsCMVkIUCLHJkt0RlPFu6Z1qQEWsNaQQm0KmeNqRWXRJIMCGbZdCva0ulPrYl00IVFvzBp0HpdxpfyePSP8qDYQFFYkgkGibVcTKeCozwrgh6ET9FScqbo7N6SOFX7vCPb6wvifd4MUOi70hGOQXZRAGcxTAifrw4ubMbw5oC/I+A4aKeAZpZZMgUc/ERqk2CkHmKqs4/o0YGZVRLGKH0jyE3G3XcSwBOUjFIRaUEAUYZNNq/otrAlS2J8fvn9+mqzPwZXSMp2WAQnbcVpDEtacndmLZuRWwWmZCxHZzZZTclpOgQSltbzqNJauWTO1LS2libyeo5mm49j8WUeeSMu2bHaDXzk4puVXpjuOHqy8XIEK6+PqSvPnYHUKObM5QQqA4fLsJrEHgEKWtBr5N9IKQyOlrlphqJE/Urcw5NCQ3NCIf0+5j39v/z99Z53yRTX//B8pCNv/K1+491sU9rf8tywPxS+X8JdNfgokPm7175p+AcQh0p+Lw6joAAAAAElFTkSuQmCC";
 
var imageShape = new $.ig.excel.WorksheetImage(image);"