Available in the Full Version
Javascript Excel - Import Data From Excel
This sample shows how to populate igGrid from excel worksheet using the Ignite UI for jQuery JavaScript Excel library.
This sample uses CTP (Community Technical Preview) features. The API and behavior may change when these features are released with full support.
- Download this sample Excel file
- Click Choose File/Browse button below and pick the sample Excel file or another excel file.
This sample is designed for a larger screen size.
On mobile, try rotating your screen, view full size, or email to another device.
The Excel file is read into Uint8Array object, which is then passed to the load method exposed by the Excel library. Once the worksheet is loaded into the Excel library object, we can read each cell value and build a JSON array that will be used as the igGrid data source. The data in the Excel file must start from the A1 cell. First-row values are used to build column definitions array for the igGrid, but you can also let the grid auto-generate the columns.
Code View
Copy to Clipboard
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Import Data From Excel</title>
<!-- Ignite UI for jQuery Required Combined CSS Files -->
<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" />
<style>
#sampleContainer ol {
padding: 0px 0px 0px 15px;
margin: 0;
}
#sampleContainer input {
margin: 10px 0;
}
#result {
display: none;
color: red;
}
</style>
<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>
<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/modules/infragistics.ext_core.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.ext_collections.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.ext_text.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.ext_io.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.ext_ui.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.documents.core_core.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.ext_collectionsextended.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.excel_core.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.ext_threading.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.ext_web.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.xml.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.documents.core_openxml.js"></script>
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/modules/infragistics.excel_serialization_openxml.js"></script>
</head>
<body>
<div>
<ol>
<li>Download this <a href="/HtmlSamples/javascript-excel-library/report.xlsx" download>sample Excel file</a></li>
<li>Click Choose File/Browse button below and pick the sample Excel file or another excel file.</li>
</ol>
<input type="file" id="input" accept="application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"/>
<div id="result"></div>
<table id="grid1"></table>
</div>
<script>
$(function () {
$("#input").on("change", function () {
var excelFile,
fileReader = new FileReader();
$("#result").hide();
fileReader.onload = function (e) {
var buffer = new Uint8Array(fileReader.result);
$.ig.excel.Workbook.load(buffer, function (workbook) {
var column, row, newRow, cellValue, columnIndex, i,
worksheet = workbook.worksheets(0),
columnsNumber = 0,
gridColumns = [],
data = [],
worksheetRowsCount;
// Both the columns and rows in the worksheet are lazily created and because of this most of the time worksheet.columns().count() will return 0
// So to get the number of columns we read the values in the first row and count. When value is null we stop counting columns:
while (worksheet.rows(0).getCellValue(columnsNumber)) {
columnsNumber++;
}
// Iterating through cells in first row and use the cell text as key and header text for the grid columns
for (columnIndex = 0; columnIndex < columnsNumber; columnIndex++) {
column = worksheet.rows(0).getCellText(columnIndex);
gridColumns.push({ headerText: column, key: column });
}
// We start iterating from 1, because we already read the first row to build the gridColumns array above
// We use each cell value and add it to json array, which will be used as dataSource for the grid
for (i = 1, worksheetRowsCount = worksheet.rows().count() ; i < worksheetRowsCount; i++) {
newRow = {};
row = worksheet.rows(i);
for (columnIndex = 0; columnIndex < columnsNumber; columnIndex++) {
cellValue = row.getCellText(columnIndex);
newRow[gridColumns[columnIndex].key] = cellValue;
}
data.push(newRow);
}
// we can also skip passing the gridColumns use autoGenerateColumns = true, or modify the gridColumns array
createGrid(data, gridColumns);
}, function (error) {
$("#result").text("The excel file is corrupted.");
$("#result").show(1000);
});
}
if (this.files.length > 0) {
excelFile = this.files[0];
if (excelFile.type === "application/vnd.ms-excel" || excelFile.type === "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" || (excelFile.type === "" && (excelFile.name.endsWith("xls") || excelFile.name.endsWith("xlsx")))) {
fileReader.readAsArrayBuffer(excelFile);
} else {
$("#result").text("The format of the file you have selected is not supported. Please select a valid Excel file ('.xls, *.xlsx').");
$("#result").show(1000);
}
}
})
});
function createGrid(data, gridColumns) {
if ($("#grid1").data("igGrid") !== undefined) {
$("#grid1").igGrid("destroy");
}
$("#grid1").igGrid({
columns: gridColumns,
autoGenerateColumns: true,
dataSource: data,
width: "100%"
});
}
</script>
</body>
</html>