New to our community ?

Discover a world of possibilities! Join us and explore a vibrant community where ideas flourish and connections thrive.

One of Our Valued Members

Thank you for being part of our community. Your presence enriches our shared experiences. Let's continue this journey together!

Home php codes Read excel file using JavaScript html

Read excel file using JavaScript html

Shefali Thakur
-
0

Welcome back to shortlearner.com , In this post we will see how to read a excel file with the help of JavaScript.  our previous tutorial we learned

Fetching data from excel sheet using JavaScript is very easy. We have used Html to display data on web browser and for design and layout used CSS.

how to retrieve data from excel sheet in html

How to Import an Excel file in MySql Database using php

How to Export Mysql table in CSV format.?

  • For presenting data in desire format we have used cascading style sheet. we have include below mentioned two CSS file for table layout. You can also design it in the way you want.

<link href="http://cdn-na.infragistics.com/igniteui/2016.2/latest/css/themes/infragistics/infragistics.theme.css" rel="stylesheet" /> 
<link href="http://cdn-na.infragistics.com/igniteui/2016.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>
  • Following JavaScript files are required to import to used predefined classes.
  • <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/2018.2/latest/js/infragistics.core.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/infragistics.lob.js"></script> 


<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.ext_core.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.ext_collections.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.ext_text.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.ext_io.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.ext_ui.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.documents.core_core.js"></script> 
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.ext_collectionsextended.js"></script> 
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.excel_core.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.ext_threading.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.ext_web.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.xml.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.documents.core_openxml.js"></script> 
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.excel_serialization_openxml.js"></script>
    • Below tag is used to select the file. 
      <input type="file" id="input" accept="application/vnd.ms-excel”>
      • Code to check selected file is excel file or not if not then display error message. 
              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);

        Below mentioned example shows the fetching of excel sheet data using JavaScript and display on the web browser using html.

      • <!DOCTYPE html> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head> 
<title>Import Data From Excel</title> 

<!-- Ignite UI Required Combined CSS Files --> 
<link href="http://cdn-na.infragistics.com/igniteui/2016.2/latest/css/themes/infragistics/infragistics.theme.css" rel="stylesheet" /> 
<link href="http://cdn-na.infragistics.com/igniteui/2016.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/2018.2/latest/js/infragistics.core.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/infragistics.lob.js"></script> 


<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.ext_core.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.ext_collections.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.ext_text.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.ext_io.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.ext_ui.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.documents.core_core.js"></script> 
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.ext_collectionsextended.js"></script> 
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.excel_core.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.ext_threading.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.ext_web.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.xml.js"></script> 

<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.documents.core_openxml.js"></script> 
<script type="text/javascript" src="http://cdn-na.infragistics.com/igniteui/2018.2/latest/js/modules/infragistics.excel_serialization_openxml.js"></script> 

</head> 
<body> 
<div> 
<ol> 

<li>Click Choose File/Browse button below and pick the sample Excel file.</li> 
</ol> 
<input type="file" id="input" accept="application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"/>

        display data in table format

        <div id="result"></div> 
<table id="grid1"></table> 
</div> 
<script> 

$(function () { 
$("#input").on("change", function () { 
var excelFile,

        //using fileReader class to read the file

      • 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 grid Columns array above
        We use each cell value and add it to JSON array, which will be used as data Source 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>

        Note: This example is used to get the data of only single excel sheet.It’s not applicable to fetch from multiple sheet.

      • Keep Coding
© Newspaper WordPress Theme by TagDiv