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 is OK, but Numeric types always appears 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 project and this is not an option.

I need to use something like shown in this jsFiddle, passing json options to alasql function.

Here is a simple code as a problem 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.

Answers:

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.

Tags

Recent Questions

Top Questions

Home Tags Terms of Service Privacy Policy DMCA Contact Us

©2020 All rights reserved.