Available in the Full Version
Javascript Excel - Worksheet Charts
This sample demonstrates how to create charts within a worksheet using the Infragistics Excel Engine.
This sample uses CTP (Community Technical Preview) features. The API and behavior may change when these features are released with full support.
This sample is designed for a larger screen size.
On mobile, try rotating your screen, view full size, or email to another device.
Code View
Copy to Clipboard
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<style>
.right {
float: right;
}
#exportButton {
float: left;
}
</style>
<link href="http://cdn-na.infragistics.com/igniteui/2023.2/latest/css/themes/infragistics/infragistics.theme.css" rel="stylesheet"/>
<link href="http://cdn-na.infragistics.com/igniteui/2023.2/latest/css/structure/infragistics.css" rel="stylesheet"/>
<!--Required scripts-->
<script src="https://igniteui.com/js/modernizr.min.js"></script>
<script src="https://code.jquery.com/jquery-1.9.1.min.js"></script>
<script src="https://code.jquery.com/ui/1.10.3/jquery-ui.min.js"></script>
<!-- External files for exporting -->
<script src="https://www.igniteui.com/js/external/Blob.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/infragistics.core.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/infragistics.lob.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/infragistics.dv.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/infragistics.excel-bundled.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/infragistics.spreadsheet-bundled.js"></script>
<script type="text/javascript" src="/data-files/expense-data.js"></script>
<script src="https://www.igniteui.com/js/external/FileSaver.js"></script>
</head>
<body>
<div id="chart">
</div>
<div id="grid">
</div>
<br />
<button id="exportButton" onclick="createWorkbook()">Create File</button>
<br />
<script type="text/javascript">
var alphabet = "A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z".split(";");
var headers = "Expenses;Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec".split(";");
var months = "Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec".split(';');
var data = getGridData();
var chartData = getChartData(data, months);
$(function(){
$("#chart").igCategoryChart({
width: "800px",
height: "400px",
dataSource: chartData,
yAxisMinimumValue: 0
});
$("#grid").igGrid({
dataSource: data,
width: "800px",
height: "300px"
});
});
function createWorkbook() {
var workbook = new $.ig.excel.Workbook($.ig.excel.WorkbookFormat.excel2007);
var sheet = workbook.worksheets().add("Sheet1");
sheet.defaultColumnWidth(200 * 20);
for (var i = 0; i < headers.length; i++) {
var cell = sheet.getCell(alphabet[i] + "3");
cell.value(headers[i]);
}
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < 13; j++) {
var stringAddress = alphabet[j] + (i + 4).toString();
var cell = sheet.getCell(stringAddress);
switch (j) {
case 0: {
cell.value(data[i].Expenses);
break;
}
case 1: {
cell.value(data[i].Jan);
break;
}
case 2: {
cell.value(data[i].Feb);
break;
}
case 3: {
cell.value(data[i].Mar);
break;
}
case 4: {
cell.value(data[i].Apr);
break;
}
case 5: {
cell.value(data[i].May);
break;
}
case 6: {
cell.value(data[i].Jun);
break;
}
case 7: {
cell.value(data[i].Jul);
break;
}
case 8: {
cell.value(data[i].Aug);
break;
}
case 9: {
cell.value(data[i].Sep);
break;
}
case 10: {
cell.value(data[i].Oct);
break;
}
case 11: {
cell.value(data[i].Nov);
break;
}
case 12: {
cell.value(data[i].Dec);
break;
}
}
}
}
sheet.rows(0).height(5000);
var cell1 = sheet.getCell('A1');
var cell2 = sheet.getCell('M1');
var chart = sheet.shapes().addChart($ig.excel.ChartType.columnClustered, cell1, { x: 0, y: 0 }, cell2, { x: 100, y: 100 });
chart.setSourceData('Sheet1!B3:M8', true);
saveWorkbook(workbook, "Table.xlsx");
}
function saveWorkbook(workbook, name) {
workbook.save({ type: 'blob' }, function (data) {
saveAs(data, name);
}, function (error) {
alert('Error exporting: ' + error);
});
}
</script>
</body>
</html>
function getRandomBetween(min, max) {
return Math.floor(Math.random() * (max - min + 1)) + min;
}
function getGridData(){
var data = [
{
"Expenses": "Expense 1",
"Jan": getRandomBetween(100, 400),
"Feb": getRandomBetween(100, 400),
"Mar": getRandomBetween(100, 400),
"Apr": getRandomBetween(100, 400),
"May": getRandomBetween(100, 400),
"Jun": getRandomBetween(100, 400),
"Jul": getRandomBetween(100, 400),
"Aug": getRandomBetween(100, 400),
"Sep": getRandomBetween(100, 400),
"Oct": getRandomBetween(100, 400),
"Nov": getRandomBetween(100, 400),
"Dec": getRandomBetween(100, 400)
},
{
"Expenses": "Expense 2",
"Jan": getRandomBetween(100, 400),
"Feb": getRandomBetween(100, 400),
"Mar": getRandomBetween(100, 400),
"Apr": getRandomBetween(100, 400),
"May": getRandomBetween(100, 400),
"Jun": getRandomBetween(100, 400),
"Jul": getRandomBetween(100, 400),
"Aug": getRandomBetween(100, 400),
"Sep": getRandomBetween(100, 400),
"Oct": getRandomBetween(100, 400),
"Nov": getRandomBetween(100, 400),
"Dec": getRandomBetween(100, 400)
},
{
"Expenses": "Expense 3",
"Jan": getRandomBetween(100, 400),
"Feb": getRandomBetween(100, 400),
"Mar": getRandomBetween(100, 400),
"Apr": getRandomBetween(100, 400),
"May": getRandomBetween(100, 400),
"Jun": getRandomBetween(100, 400),
"Jul": getRandomBetween(100, 400),
"Aug": getRandomBetween(100, 400),
"Sep": getRandomBetween(100, 400),
"Oct": getRandomBetween(100, 400),
"Nov": getRandomBetween(100, 400),
"Dec": getRandomBetween(100, 400)
},
{
"Expenses": "Expense 4",
"Jan": getRandomBetween(100, 400),
"Feb": getRandomBetween(100, 400),
"Mar": getRandomBetween(100, 400),
"Apr": getRandomBetween(100, 400),
"May": getRandomBetween(100, 400),
"Jun": getRandomBetween(100, 400),
"Jul": getRandomBetween(100, 400),
"Aug": getRandomBetween(100, 400),
"Sep": getRandomBetween(100, 400),
"Oct": getRandomBetween(100, 400),
"Nov": getRandomBetween(100, 400),
"Dec": getRandomBetween(100, 400)
},
{
"Expenses": "Expense 5",
"Jan": getRandomBetween(100, 400),
"Feb": getRandomBetween(100, 400),
"Mar": getRandomBetween(100, 400),
"Apr": getRandomBetween(100, 400),
"May": getRandomBetween(100, 400),
"Jun": getRandomBetween(100, 400),
"Jul": getRandomBetween(100, 400),
"Aug": getRandomBetween(100, 400),
"Sep": getRandomBetween(100, 400),
"Oct": getRandomBetween(100, 400),
"Nov": getRandomBetween(100, 400),
"Dec": getRandomBetween(100, 400)
}
];
return data;
}
function getChartElementStructure() {
var element = { "Month": "", "Expense1": 0, "Expense2": 0, "Expense3": 0, "Expense4": 0, "Expense5": 0 };
return element;
}
function getChartData(data, months) {
var chartData = [];
for (var i = 0; i < 12; i++) {
var element = getChartElementStructure();
switch (i) {
case 0: {
element.Month = months[i];
element.Expense1 = data[0].Jan;
element.Expense2 = data[1].Jan;
element.Expense3 = data[2].Jan;
element.Expense4 = data[3].Jan;
element.Expense5 = data[4].Jan;
break;
}
case 1: {
element.Month = months[i];
element.Expense1 = data[0].Feb;
element.Expense2 = data[1].Feb;
element.Expense3 = data[2].Feb;
element.Expense4 = data[3].Feb;
element.Expense5 = data[4].Feb;
break;
}
case 2: {
element.Month = months[i];
element.Expense1 = data[0].Mar;
element.Expense2 = data[1].Mar;
element.Expense3 = data[2].Mar;
element.Expense4 = data[3].Mar;
element.Expense5 = data[4].Mar;
break;
}
case 3: {
element.Month = months[i];
element.Expense1 = data[0].Apr;
element.Expense2 = data[1].Apr;
element.Expense3 = data[2].Apr;
element.Expense4 = data[3].Apr;
element.Expense5 = data[4].Apr;
break;
}
case 4: {
element.Month = months[i];
element.Expense1 = data[0].May;
element.Expense2 = data[1].May;
element.Expense3 = data[2].May;
element.Expense4 = data[3].May;
element.Expense5 = data[4].May;
break;
}
case 5: {
element.Month = months[i];
element.Expense1 = data[0].Jun;
element.Expense2 = data[1].Jun;
element.Expense3 = data[2].Jun;
element.Expense4 = data[3].Jun;
element.Expense5 = data[4].Jun;
break;
}
case 6: {
element.Month = months[i];
element.Expense1 = data[0].Jul;
element.Expense2 = data[1].Jul;
element.Expense3 = data[2].Jul;
element.Expense4 = data[3].Jul;
element.Expense5 = data[4].Jul;
break;
}
case 7: {
element.Month = months[i];
element.Expense1 = data[0].Aug;
element.Expense2 = data[1].Aug;
element.Expense3 = data[2].Aug;
element.Expense4 = data[3].Aug;
element.Expense5 = data[4].Aug;
break;
}
case 8: {
element.Month = months[i];
element.Expense1 = data[0].Sep;
element.Expense2 = data[1].Sep;
element.Expense3 = data[2].Sep;
element.Expense4 = data[3].Sep;
element.Expense5 = data[4].Sep;
break;
}
case 9: {
element.Month = months[i];
element.Expense1 = data[0].Oct;
element.Expense2 = data[1].Oct;
element.Expense3 = data[2].Oct;
element.Expense4 = data[3].Oct;
element.Expense5 = data[4].Oct;
break;
}
case 10: {
element.Month = months[i];
element.Expense1 = data[0].Nov;
element.Expense2 = data[1].Nov;
element.Expense3 = data[2].Nov;
element.Expense4 = data[3].Nov;
element.Expense5 = data[4].Nov;
break;
}
case 11: {
element.Month = months[i];
element.Expense1 = data[0].Dec;
element.Expense2 = data[1].Dec;
element.Expense3 = data[2].Dec;
element.Expense4 = data[3].Dec;
element.Expense5 = data[4].Dec;
break;
}
}
chartData.add(element);
}
return chartData;
}