To work with.xlsx files, please includexlsx fromhttps://cdnjs.com/libraries/xlsx

<scriptsrc="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.core.min.js"></script>

Please note that when interacting with files AlaSQLwill run async. We strongly recommend you touse the promise notation.

alasql.promise('select City, Population from xlsx("cities.xlsx") where Population > 100000').then(function(data){console.log(data);}).catch(function(err){console.log('Error:',err);});

alasql.promise('SELECT * INTO XLSX("restest280b.xlsx") FROM ?',[data]).then(function(data){console.log('Data saved');}).catch(function(err){console.log('Error:',err);});

To store data in multiple sheets have a look athttp://jsfiddle.net/np3pea44/

XLSX() function supports the following options:

Sheet name:

alasql.promise('select * from xlsx("cities.xlsx",{sheetid:"Sheet2"})').then(function(data){console.log(data);}).catch(function(err){console.log('Error:',err);});

By default AlaSQL read data from sheet "Sheet1".

Cells range:

alasql.promise('select * from xlsx("cities.xlsx",{range:"A1:D100"})').then(function(data){console.log(data);}).catch(function(err){console.log('Error:',err);});

By default AlaSQL read all data in the sheet.

Read headers from data range (true/false):

alasql.promise('select * from xlsx("cities.xlsx",{headers:true})').then(function(data){console.log(data);}).catch(function(err){console.log('Error:',err);});

By default AlaSQL headers are set totrue

varmystyle={headers:true,column:{style:{Font:{Bold:"1"}}},rows:{1:{style:{Font:{Color:"#FF0077"}}}},cells:{1:{1:{style:{Font:{Color:"#00FFFF"}}}}}};alasql.promise('SELECT * INTO XLSXML("restest280b.xls",?) FROM ?',[mystyle,data]).then(function(data){console.log('Data saved');}).catch(function(err){console.log('Error:',err);});

See the working example injsFiddle

Please note that you can avoid letting AlaSQL try to add extension to filenames by settingautoExt:false in the option.

You can also pass the file as a Blob:

alasql('SELECT * FROM XLSX(?)',[myblobData]);

AlaSQL uses js-xlsx library to read and export Excel files.

js-xlsx at Github:https://github.com/SheetJS/js-xlsx

You can provide parameters to how js-xlsx should read the files in the config object in the second parameter of the xlsx() function.

Since version 2 AlaSQL defaults to load excel dates as JS date objects. To change the default behaviour setalasql.options.excel = {cellDates: false};. To change a single request you can provide{cellDates: false} as the secondary parameter to thexlsx() function.

By default AlaSQL look through first 10 records to collect infomation about all columns. You can change this flag to be sure that AlaSQL collect information about all columns in all records (it may take a time for large recordsets).

alasql.options.columnlookup = 10;

To read blank column values from xls,xlsx and csv you can use this notation for column names [0],[1],[2] like:

SELECT [0],[2] FROM XLS('filename.xls') WHERE [3] > 100

Uploading files directly into AlaSQL? Have a look athttps://github.com/agershun/alasql/issues/971#issuecomment-360121191

If you need Excel 2003 files please check out:XLS