The weird characters you got from reading the Excel file from the FileReader
API comes from the structure of the file that differs a lot from a text file.
So reading it as text
with the FileReader
API will give those weirds character as a result.
What you can do is to use the FileReader
API to read it as a binary string
.
At this point if you try to log that binary string
you will also get weirds characters.
In order to get your file content you need to parse
that binary string
to extract the data it contains. This can be done quite easily using SheetJS.
import { read, writeFileXLSX, utils } from "//cdn.sheetjs.com/xlsx-0.18.7/package/xlsx.mjs";
const workbook = read[data, {
type:'binary',
}];
data
is the binary string
resulting from reading an Excel file as a binary string
with the FileReader
API.
workbook
is an object that contains all the data of your file.
The workbook.Sheets
instruction will give you access to all the sheets
that are in the file.
workbook.Sheets.sheet1
will give you access to the
content of the first sheet
of the file.
All the related arrays are from the {key:value}
type.
The content of a sheet
accessed this way is a single dimension object
array
wich contains all the cells of the sheet
starting from the first cell of the header to the last cell wich contains data.
Each of those cells has a key like this 'A1'
, 'B250'
, 'J3'
This array also have two more entries with those keys '!margin'
and '!ref'
:
'!margin'
refers to cells
margins so it may not represent any interest.
'!ref'
is more interesting as it contains the plage of cells containing data wich is a string
like this 'A1:J215'
from it you could get the amount of lines or the char of the last column.
If you want more informations you could check the SheetJS documentation and there is a more detailed example here : How to read an excel file contents on client side?
Note :
If you want to use this import statement in an html
page you'll need to do it inside those scripts tags
: ...
Here is a codepen where you can
test this method. There's only the most basic method. There are some shorter ways to do the same by using the SheetJS utils
functions to convert directly the sheet
content to another format.
In the previous post, I have explained how we can read csv file using javascript and HTML 5 filereader, now in this post, I have explained how we can use external library like xlsx to parse or read excel file using javascript and show it's contents in HTML table. When file is uploaded using Javascript, it is read as Binary string initially, and then binary data is read using xlsx plugin.
Read XLSX using Javascript
Let's beging by adding simple HTML file input and button to upload file
I have also included empty HTML div to create table inside it from our Excel file.
Now, we will create the function to upload the file and process the Excel file to get data from it and convert it into HTML table.
function UploadProcess[] {
//Reference the FileUpload element.
var fileUpload = document.getElementById["fileUpload"];
//Validate whether File is valid Excel file.
var regex = /^[[a-zA-Z0-9\s_\\.\-:]]+[.xls|.xlsx]$/;
if [regex.test[fileUpload.value.toLowerCase[]]] {
if [typeof [FileReader] != "undefined"] {
var reader = new FileReader[];
//For Browsers other than IE.
if [reader.readAsBinaryString] {
reader.onload = function [e] {
GetTableFromExcel[e.target.result];
};
reader.readAsBinaryString[fileUpload.files[0]];
} else {
//For IE Browser.
reader.onload = function [e] {
var data = "";
var bytes = new Uint8Array[e.target.result];
for [var i = 0; i < bytes.byteLength; i++] {
data += String.fromCharCode[bytes[i]];
}
GetTableFromExcel[data];
};
reader.readAsArrayBuffer[fileUpload.files[0]];
}
} else {
alert["This browser does not support HTML5."];
}
} else {
alert["Please upload a valid Excel file."];
}
};
function GetTableFromExcel[data] {
//Read the Excel File data in binary
var workbook = XLSX.read[data, {
type: 'binary'
}];
//get the name of First Sheet.
var Sheet = workbook.SheetNames[0];
//Read all rows from First Sheet into an JSON array.
var excelRows = XLSX.utils.sheet_to_row_object_array[workbook.Sheets[Sheet]];
//Create a HTML Table element.
var myTable = document.createElement["table"];
myTable.border = "1";
//Add the header row.
var row = myTable.insertRow[-1];
//Add the header cells.
var headerCell = document.createElement["TH"];
headerCell.innerHTML = "Id";
row.appendChild[headerCell];
headerCell = document.createElement["TH"];
headerCell.innerHTML = "Name";
row.appendChild[headerCell];
headerCell = document.createElement["TH"];
headerCell.innerHTML = "Country";
row.appendChild[headerCell];
headerCell = document.createElement["TH"];
headerCell.innerHTML = "Age";
row.appendChild[headerCell];
headerCell = document.createElement["TH"];
headerCell.innerHTML = "Date";
row.appendChild[headerCell];
headerCell = document.createElement["TH"];
headerCell.innerHTML = "Gender";
row.appendChild[headerCell];
//Add the data rows from Excel file.
for [var i = 0; i < excelRows.length; i++] {
//Add the data row.
var row = myTable.insertRow[-1];
//Add the data cells.
var cell = row.insertCell[-1];
cell.innerHTML = excelRows[i].Id;
cell = row.insertCell[-1];
cell.innerHTML = excelRows[i].Name;
cell = row.insertCell[-1];
cell.innerHTML = excelRows[i].Country;
cell = row.insertCell[-1];
cell.innerHTML = excelRows[i].Age;
cell = row.insertCell[-1];
cell.innerHTML = excelRows[i].Date;
cell = row.insertCell[-1];
cell.innerHTML = excelRows[i].Gender;
}
var ExcelTable = document.getElementById["ExcelTable"];
ExcelTable.innerHTML = "";
ExcelTable.appendChild[myTable];
};
In the above Javascript code, we are first adding references of XLSX plugin files and then adding two functions
- UploadProcess: Uploads the file on button click and convert it into Binary data, it also check if Browser is IE then process accordingly to convert file into binary.
- ProcessExcel: this function takes the binary data, reads the Sheet name, create Table element and append each row in it.
I have explained lines of code using Comment.
Suppose our sample Excel file looks like this
So, if use the above code in HTML/Javascript, output will be as below
Here is the fiddle link //jsfiddle.net/abj98oxf/3/
Read XLS file using Javascript
In Similar way, we can read .xls [excel] file also and show it in HTML table, I will repeat the same code, just few lines of code is different, also we will be using diferent plugin in it which is for .xls
function UploadProcess[] {
//Reference the FileUpload element.
var fileUpload = document.getElementById["fileUpload"];
//Validate whether File is valid Excel file.
var regex = /^[[a-zA-Z0-9\s_\\.\-:]]+[.xls|.xlsx]$/;
if [regex.test[fileUpload.value.toLowerCase[]]] {
if [typeof [FileReader] != "undefined"] {
var reader = new FileReader[];
//For Browsers other than IE.
if [reader.readAsBinaryString] {
reader.onload = function [e] {
GetTableFromExcel[e.target.result];
};
reader.readAsBinaryString[fileUpload.files[0]];
} else {
//For IE Browser.
reader.onload = function [e] {
var data = "";
var bytes = new Uint8Array[e.target.result];
for [var i = 0; i < bytes.byteLength; i++] {
data += String.fromCharCode[bytes[i]];
}
GetTableFromExcel[data];
};
reader.readAsArrayBuffer[fileUpload.files[0]];
}
} else {
alert["This browser does not support HTML5."];
}
} else {
alert["Please upload a valid Excel file."];
}
};
function GetTableFromExcel[data] {
//Read the Excel File data in binary
var cfb = XLS.CFB.read[data, {type: 'binary'}];
var workbook = XLS.parse_xlscfb[cfb];
//get the name of First Sheet.
var Sheet = workbook.SheetNames[0];
//Read all rows from First Sheet into an JSON array.
var excelRows = XLS.utils.sheet_to_row_object_array[workbook.Sheets[Sheet]];
//Create a HTML Table element.
var myTable = document.createElement["table"];
myTable.border = "1";
//Add the header row.
var row = myTable.insertRow[-1];
//Add the header cells.
var headerCell = document.createElement["TH"];
headerCell.innerHTML = "Id";
row.appendChild[headerCell];
headerCell = document.createElement["TH"];
headerCell.innerHTML = "Name";
row.appendChild[headerCell];
headerCell = document.createElement["TH"];
headerCell.innerHTML = "Country";
row.appendChild[headerCell];
headerCell = document.createElement["TH"];
headerCell.innerHTML = "Age";
row.appendChild[headerCell];
headerCell = document.createElement["TH"];
headerCell.innerHTML = "Date";
row.appendChild[headerCell];
headerCell = document.createElement["TH"];
headerCell.innerHTML = "Gender";
row.appendChild[headerCell];
//Add the data rows from Excel file.
for [var i = 0; i < excelRows.length; i++] {
//Add the data row.
var row = myTable.insertRow[-1];
//Add the data cells.
var cell = row.insertCell[-1];
cell.innerHTML = excelRows[i].Id;
cell = row.insertCell[-1];
cell.innerHTML = excelRows[i].Name;
cell = row.insertCell[-1];
cell.innerHTML = excelRows[i].Country;
cell = row.insertCell[-1];
cell.innerHTML = excelRows[i].Age;
cell = row.insertCell[-1];
cell.innerHTML = excelRows[i].Date;
cell = row.insertCell[-1];
cell.innerHTML = excelRows[i].Gender;
}
var ExcelTable = document.getElementById["ExcelTable"];
ExcelTable.innerHTML = "";
ExcelTable.appendChild[myTable];
};
The XLS file upload code is same as .XLSX one was, here are the changes
- We included difference JS plugin file :
- Changed few lines of code for function "GetTableFromExcel"
//Read the Excel File data in binary var cfb = XLS.CFB.read[data, {type: 'binary'}]; var workbook = XLS.parse_xlscfb[cfb]; //get the name of First Sheet. var Sheet = workbook.SheetNames[0]; //Read all rows from First Sheet into an JSON array. var excelRows = XLS.utils.sheet_to_row_object_array[workbook.Sheets[Sheet]];?
Rest of the code remains same.
Here is the working fiddle link : //jsfiddle.net/y3tx8wk4/1/
Note: You need to pass .xls file in this code to make it work.