Available in the Full Version
Spreadsheet - Generate View and Save Data
This sample demonstrates how to generate a worksheet using Infragistics Ignite UI for jQuery JavaScript Excel library, display it via igSpreadsheet control, edit it and save the changes into a file.
This sample uses CTP (Community Technical Preview) features. The API and behavior may change when these features are released with full support.
Enable excel editing
This sample is designed for a larger screen size.
On mobile, try rotating your screen, view full size, or email to another device.
This sample shows how to generate an Excel table with custom data and formatting in the browser. Once the workbook is constructed, it is passed as an option to the igSpreadsheet control for visualization. When loaded, it can be saved locally via the exposed save method.
Code View
Copy to Clipboard
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Generate and Save Data</title>
<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" />
<script src="http://ajax.aspnetcdn.com/ajax/modernizr/modernizr-2.8.3.js"></script>
<script src="http://code.jquery.com/jquery-1.11.3.min.js"></script>
<script src="http://code.jquery.com/ui/1.11.1/jquery-ui.min.js"></script>
<!-- Ignite UI for jQuery Required Combined JavaScript Files -->
<script src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/infragistics.core.js"></script>
<script src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/infragistics.lob.js"></script>
<script src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/infragistics.excel-bundled.js"></script>
<script src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/infragistics.spreadsheet-bundled.js"></script>
<!-- saving worksheet -->
<script src="https://www.igniteui.com/js/external/FileSaver.js"></script>
<style>
.ui-igspreadsheet .ui-menu-item {
white-space:nowrap;
}
.spreadsheet-sample-btn
{
max-width: 200px;
padding: 8px;
background: #fff;
color:#444;
text-align: center;
border-radius: 3px;
font-size: 12px;
text-transform: uppercase;
margin:0 15px 15px 0;
cursor:pointer;
border:1px solid #ccc;
}
.spreadsheet-sample-btn:hover {
border-color:#09f;
}
.editing-controls-container {
display: inline-block;
position: relative;
min-width: 200px;
margin: 18px 15px;
}
.editing-controls-container #enableEditing {
margin-right: 6px;
border-radius: 3px;
position: absolute;
left: 0;
top: 10px;
}
.label-editing {
display:inline-block;
position: absolute;
left: 23px;
top: 15px;
}
</style>
</head>
<body>
<div>
<input class="spreadsheet-sample-btn" id="createWorkbookBtn" type="button" value="Generate Workbook" onclick="createWorkbook()">
<input class="spreadsheet-sample-btn" id="saveWorkbookBtn" type="button" value="Save Workbook" onclick="saveWorkbook()">
<div class="editing-controls-container">
<span for="enableEditing" class="show-row-text">
<div id="enableEditing"></div>
<span class="label-editing">Enable excel editing</span>
</span>
</div>
<div id="spreadsheet"></div>
</div>
<script>
$(function () {
var editingEnabled = true;
//Initializing igSpreadsheet
$("#spreadsheet").igSpreadsheet({
height: "600",
width: "100%",
isFormulaBarVisible: true,
editModeEntering: function (e, args) {
return editingEnabled;
}
});
$("#enableEditing").igCheckboxEditor({
checked: true,
valueChanged: function (evt, ui) {
editingEnabled = ui.newState;
}
});
});
//display the workbook via igSpreadsheet
function loadWorkbook(workbook) {
$("#spreadsheet").igSpreadsheet("option", "workbook", workbook);
}
//using the save method, which is exposed by the Excel library
function saveWorkbook(workbook, name) {
$("#spreadsheet").igSpreadsheet("option", "workbook")
.save({ type: 'blob' }, function (data) {
saveAs(data, name);
}, function (error) {
alert('Error exporting: : ' + error);
});
}
function createWorkbook() {
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(136, $.ig.excel.WorksheetColumnWidthUnit.pixel);
// 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().bold(true);
sheet.getCell('G17').applyFormula('=SUM(G4:G16)');
sheet.getCell('G17').cellFormat().font().bold(true);
// Load the workbook in igSpreadsheet
loadWorkbook(workbook);
//Set summary cell as active cell
$("#spreadsheet").igSpreadsheet("option", "activeCell", "G17");
}
</script>
</body>
</html>