Excel to HTML Table | Javacodepoint

Display Excel data in HTML Table using SheetJS in JavaScript

This article shows you how you display the Excel data in an HTML table using SheetJS in Javascript. SheetJS is a javascript library used to work with various spreadsheet formats. In another article, we explained Converting Excel file data to JSON using the SheetJS library in Javascript. Today in this article, we will explain how to display Excel spreadsheet data in an HTML table using javascript.

Here first we convert the Excel data into JSON format then JSON data will be shown in HTML tabular format. Let’s see how it can be done.

SheetJS Library

SheetJS is a powerful library written in pure Javascript. It is a Parser and writer for various spreadsheet formats.

How to use SheetJS?

As it is a javascript library, we have to include it in the HTML document to use this library. There are various CDN links that can be used to include it. Let’s see one of the CDN links below:

Library:	xlsx.min.js
CDN Link:	https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.5/xlsx.min.js

Now let’s see the below script that we have to include in the tag of the HTML document,

How to display Excel data in HTML Table using Javascript?

Follow the below steps to display excel data in the HTML table:

1. Define HTML
2. Includes the SheetJS library
3. Write the Javascript logic to upload and validate the Excel file
4. Read the Excel data into JSON format
5. Convert JSON data to the HTML table

1. Define HTML

Let’s define the below HTML code in an HTML document file:

Upload an excel file to display in HTML Table

Upload

2. Includes the SheetJS library

Include the CDN link of library xlsx.min.js in the tag of an HTML file as follows:

3. Write the Javascript logic to upload and validate the Excel file

We will define a javascript upload() method to upload and validate the Excel file. The upload() method will allow users to upload a valid Excel file only. Let’s see the below logic that we have to write in tag:

// Method to upload a valid excel file function upload() < var files = document.getElementById('file_upload').files; if(files.length==0)< alert("Please choose any file. "); return; >var filename = files[0].name; var extension = filename.substring(filename.lastIndexOf(".")).toUpperCase(); if (extension == '.XLS' || extension == '.XLSX') < //Here calling another method to read excel file into json excelFileToJSON(files[0]); >else < alert("Please select a valid excel file."); >>

4. Read Excel file in Javascript and convert it into JSON format

We have defined a javascript method excelFileToJSON() that will read the Excel file and convert the data into JSON format. Inside excelFileToJSON() , we have read the data of the Excel file by using a file reader as a binary string using readAsBinaryString() method. After that, we used XLSX which has a built-in facility to convert our binary string into a JSON object. And the XLSX.utils.sheet_to_json() method is used to read sheet data in JSON.

//Method to read excel file and convert it into JSON function excelFileToJSON(file)< try < var reader = new FileReader(); reader.readAsBinaryString(file); reader.onload = function(e) < var data = e.target.result; var workbook = XLSX.read(data, < type : 'binary' >); var result = <>; var firstSheetName = workbook.SheetNames[0]; //reading only first sheet data var jsonData = XLSX.utils.sheet_to_json(workbook.Sheets[firstSheetName]); alert(JSON.stringify(jsonData)); //displaying the json result into HTML table displayJsonToHtmlTable(jsonData); > >catch(e) < console.error(e); >>

5. Convert JSON data to the HTML Table using Javascript

Now we have defined a javascript method displayJsonToHtmlTable() to display the JSON data in an HTML table.

//Method to display the data in HTML Table function displayJsonToHtmlTable(jsonData)< var table=document.getElementById("display_excel_data"); if(jsonData.length>0)< var htmlData='Student NameAddressEmail IDAge'; for(var i=0;i'+row["Address"] +''+row["Email ID"]+''+row["Age"]+''; > table.innerHTML=htmlData; >else < table.innerHTML='There is no data in Excel'; >>

Display Excel data in HTML Table [complete example]

This example shows you Excel to HTML table Conversion. Let’s see the complete code below (putting all the above code in a single HTML file):

       
Upload an excel file to display in HTML Table
  Upload 
 
     

Test and Live Demo

Let’s assume a sample Excel file(students.xlsx) that we are going to upload. As you can see in the below image this Excel file contains the student’s data(name, address, email id, and age).

Let’s see the result once we upload the above Excel file to this application.

Conclusion

In this tutorial, you have seen how you display the Excel file data in an HTML Table using Javascript. You have seen here, that can be easily done by using the SheetJS library. As you have seen this library has the capability to convert binary strings to JSON format that is used here to display in an HTML table.

Related articles:

Источник

Embed Excel Sheet in HTML

In some use cases, we have a complicated Excel sheet to share on our personal website and we would like to make it interactive. Fortunately, Microsoft allows the user to do so via OneDrive and it supports both HTML iframe and JavaScript.

In this blog post, I would like the discuss how to embed Excel sheet in HTML, the issues that I encountered, and how to fix the issues.

Embed Excel Sheet in HTML

Generate Code

Once the Excel sheet has been created or uploaded to Microsoft OneDrive, generating the Excel embed code could be as easy as clicking File → Share → Embed → Generate HTML .

Insert Code

Then we could copy the HTML iframe code or the JavaScript code and insert it into our website.

Using the default HTML iframe code will result in an embedded Excel sheet as follows. If you see lots of white spaces before the table, it is a bug from Microsoft and the bug will show in most of the browsers except the Brave browser.

iframe width=«402» height=«346» frameborder=«0» scrolling=«no» src=«https://onedrive.live.com/embed?resid=6C685993F809A9F8%212757&authkey=%21AABR6KgqMF_ImYs&em=2&wdAllowInteractivity=False&AllowTyping=True&wdDownloadButton=True&wdInConfigurator=True&wdInConfigurator=True&edesNext=true&ejss=false»> iframe>

Using the default JavaScript code will result in an embedded Excel sheet as follows.

div id=«myExcelDiv» style=«width: 402px; height: 346px»> div>

* Excel Web App into a div with The full API is documented at
* https://msdn.microsoft.com/en-US/library/hh315812.aspx. There you can find out how to programmatically get
* values from your Excel file and how to use the rest of the object model. —>
script type=«text/javascript» src=«https://onedrive.live.com/embed?resid=6C685993F809A9F8%212757&authkey=%21AABR6KgqMF_ImYs&em=3&wdDivId=%22myExcelDiv%22&wdDownloadButton=1&wdAllowInteractivity=0&wdAllowTyping=1»> script>

Fix JavaScript Issues

The width of the embedded Excel sheet seems to be a problem, but we could modify the width values in the style. A bigger problem is, unlike the iframe embedding, the JavaScript embedding does not allow the user to input values and one HTML can only have one Excel sheet embedded (without problems) because the div id is a pre-defined value myExcelDiv .

Personally, I favor the JavaScript embedding over the HTML iframe embedding. So we would like to try solving those problems on our own.

We noticed that there is an argument wdAllowInteractivity=0 in the src for both JavaScript and iframe , although it does not prevent the user from typing in iframe , changing it to 1 allows the user to type successfully.

In addition, wdDivId=%22myExcelDiv%22 is the place for specifying custom div id for embedding Excel sheets. We could use new div id here.

Finally, we can also make the webpage opening a little bit faster by making the loading of JavaScript async .

Fix Preview

The fixed JavaScript code is as follows. We could see that now the embedded Excel sheet looks pretty nice.

div id=«myExcelDiv1» style=«width: 100%; height: 400px»> div>
script type=«text/javascript» src=«https://onedrive.live.com/embed?resid=6C685993F809A9F8%212757&authkey=%21AABR6KgqMF_ImYs&em=3&wdDivId=%22myExcelDiv1%22&wdDownloadButton=1&wdAllowInteractivity=1&wdAllowTyping=1» async> script>

Embed Excel Sheet in HTML

Источник