Define cell format on AlaSQL/JS-XLSX Excel Export
Is it possible to define cell format on AlaSQL export to Excel?
I'm maintaining a system that exports grid data to Excel using AlaSQL. The problem is that Excel data is not being converted to NUMBER. DATE values do OK, but Numeric types are always displayed as General. By forcing a convertion to Number type in JS, the message "Number stored as Text" is shown on xls file.
I think it would be easier solving this problem using js-xlsx directly, but it would generate lots of changes in the project and this is not an option.
I need to use something like it's shown here jsFiddle, passing json options to alasql function.
Below, a simple piece of code to serve as an example:
<head>
<script src="http://cdn.jsdelivr.net/alasql/0.2/alasql.min.js"></script>
<script src="http://alasql.org/console/xlsx.core.min.js"></script>
</head>
<script>
function download() {
var opts = {
sheetid: 'Default',
headers: true
};
alasql.fn.to_number = function(val){
return new Number(val);
};
var query = 'SELECT to_number("1") as NumericColumn INTO XLSX("Test.xlsx", ?)';
alasql(query, [opts, function data(){}]);
};
</script>
I appreciate your attention.
Answer
AlaSQL has XLSXML() export function with coloring functionality:
var data = [{city:"London",population:5000000},
{city:"Moscow",population:12000000},
{city:"Mexico",population:20000000},
{city:"New York",population:20000000},
];
var opts = {
headers:true,
column: {style:{Font:{Bold:"1"}}},
rows: {1:{style:{Font:{Color:"#FF0077"}}}},
cells: {1:{1:{
style: {Font:{Color:"#00FFFF"}}
}}}
};
alasql('SELECT * INTO XLSXML("restest280b.xls",?) FROM ?',[opts,data]);
Here you can define style for any column, row, or cell in the sheet.