Interactive web tables, Jexcel
I found a post that had an article about an Excel type spreadsheet that you have in your web page with the benefits listed below, so I thought I’d have a play with it.
jExcel is a lightweight vanilla javascript plugin to create amazing web-based interactive tables and spreadsheets compatible with Excel or any other spreadsheet software. You can create an online spreadsheet table from a JS array, JSON, CSV or XSLX files. You can copy from excel and paste straight to your jExcel spreadsheet and vice versa. It is very easy to integrate any third party javascript plugins to create your own custom columns, custom editors, and customize any feature into your application. jExcel has plenty of different input options through its native column types to cover the most common web-based application requirements. It is a complete solution for web data management. Create amazing applications with jExcel javascript spreadsheet.
https://bossanova.uk/jexcel/v3/docs/getting-started
Main advantages
Make rich and user-friendly web interfaces and applications
You can easily handle complicated data inputs in a way users are used.
Improve your user software experience
Create rich CRUDS and beautiful UI
Compatibility with excel: users can move data around with common copy and paste shortcuts
Easy customizations with easy third-party plugin integrations
Lean, fast and simple to use
Thousands of successfully user cases
Speed up your work dealing with difficult data entry in a web-based software
I thought I’d try it out. It has a few interesting features that add some functionality to online pages.
You can put your data in the sheet, or can link to a JSON or CSV or XMLS file.
You can also click on sells and have pull down list, click to upload image file (although I’ll need to check where the image is stored) and can do a calendar date picker as well as filter tables ascending/descending- Also apparently can cut/paste from an Excel Sheet (I wonder if it’ll do google sheet & libre calc?) I need to check out if you can do formula in them too.
Example 1 – putting code into web page
The example below uses information from this page.
The source for the table above is shown below. It is calling the jexcel.js & jsuites.js code from the bossanova.uk site and then rendering it on the web page.
In the above table you are able to click on the PHOTO cell and upload a photo. I used 1/a screenshot and 2/ The image you see in the table. The string that represents that image is 65,500 characters long, so it creates a string for coding the image. I thought it would be a handy way to link the image to the table by uploading an image and then copying the string, so that it would be in the table.
I copied the “data:image/png;base64,iVB……….” (and so on for 65,500 characters), and the spreadsheet got upset as I think cells used to have a limit of 250 characters. Libre Office accepted the 65,500 chars but said it didn’t like it. It does seem a cumbersome way to store images. When using the developers tool in the browser it only shows some of the string and if you try to copy it it freezes the browser.
Column formatting is done in in the “jexcel(document.getElementById(‘spreadsheet’), {
data:data,” with “WIDTH”
There are different type cells, such as text, dropdown, calendar, image, checkbox & color that are quite interesting to play with on a web page.
<html>
<script src="https://bossanova.uk/jexcel/v3/jexcel.js"></script>
<link rel="stylesheet" href="https://bossanova.uk/jexcel/v3/jexcel.css" type="text/css" />
<script src="https://bossanova.uk/jsuites/v2/jsuites.js"></script>
<link rel="stylesheet" href="https://bossanova.uk/jsuites/v2/jsuites.css" type="text/css" />
<div id="spreadsheet"></div>
<script>
var data = [
['Jazz', 'Honda', '2019-02-12', '', true, '$ 2.000,00', '#777700'],
['Civic', 'Honda', '2018-07-11', '', true, '$ 4.000,01', '#007777'],
];
jexcel(document.getElementById('spreadsheet'), {
data:data,
columns: [
{ type: 'text', title:'Car', width:120 },
{ type: 'dropdown', title:'Make', width:200, source:[ "Alfa Romeo", "Audi", "Bmw", "Honda" ] },
{ type: 'calendar', title:'Available', width:200 },
{ type: 'image', title:'Photo', width:120 },
{ type: 'checkbox', title:'Stock', width:80 },
{ type: 'numeric', title:'Price', width:100, mask:'$ #.##,00', decimal:',' },
{ type: 'color', width:100, render:'square', }
]
});
</script>
</html>
I did add some rows (right click mouse on the row numbers ) and added some data, but it didn’t save it when I refreshed the page, maybe this is because the data is embedded in the code in this case and not in a file.
Example 2 – from file
I then decided to download v3 of the code to my website to test out calling the code from a file
In the table above (see code below) you can RIGHT MOUSE CLICK on heater for extra functions, one being SAVEAS- If you alter a couple of items (see example below) you can then SAVEAS and open in a Spreadsheet the altered table, but the ORIGINAL is not altered, if you do a refresh on the web page the original data is displayed
The download CSV file can be modified but the original file stays the same. I’m not sure that this is a good thing.
The code for example 2
<script src="https://max-drake.cc/data/jexcel/c/jexcel.js"></script>
<link rel="stylesheet" href="https://max-drake.cc/data/jexcel/c/jexcel.css" type="text/css" />
<script src="https://max-drake.cc/data/jexcel/c/jsuites.js"></script>
<link rel="stylesheet" href="https://max-drake.cc/data/jexcel/c/jsuites.css" type="text/css" />
<div id="spreadsheet1"></div>
<p><button id='download'>Export my spreadsheet as CSV</button></p>
<script>
mySpreadsheet = jexcel(document.getElementById('spreadsheet1'), {
csv:'https://max-drake.cc/data/jexcel/x/jexcel.csv',
csvHeaders:true,
columns: [
{ type:'text', width:150 },
{ type:'text', width:50 },
{ type:'text', width:80 },
{ type:'text', width:100 },
{ type:'text', width:100 },
]
});
document.getElementById('download').onclick = function () {
mySpreadsheet.download();
}
</script>
The code above was adjusted to call the jexel.js & jsuites.js from my VPS (virtual private server), this is so that the code will continue to work if there are updates (Note: I did have to go to file properties and unblock them after downloading them from Github ). With the CSV file there is only one type of data which is the “Text” type.
End comment
I was talking with someone that used Excel a lot and so that is why I decided to explore it. On playing with it I think it has potential but can’t, at this point in time, see a specific use case that I want it for, so part 1 of the post is to make a note of it. I’ll think about some applications for it next.
I was thinking of the conditional formatting of my conditional elements (there is a colour picker from a list) so whether this could be used like my embedded online Excel heat map? I may need to explore this further. It does allow for organizing information in ascending/descending but that also resets when you refresh the page too.
This is a bit like DataTables but I’m not sure if its as robust. That calls a DataBase so can have a lot larger Dataset, but maybe not the different types of Data (selectable calendar, photos, pick from list, checkbox). Also it has a couple of different things than Excel Embed on Web page.
Web Data 1. Capturing Web data via Google sheets and Displaying on Web Page
Asset Tiger 1. free Online Asset Management Service with Free mobile app
