Alasql: Any way to set columns options when export to xlsx

[volkovoleg]

When I try to export json into xlsx file styles doesn't work.

var options = {
    headers: true,
    rows: { 1: { style: { Font: { Color: "#FF0077" } } } },
    columns: [{ columnid: 'Name', width: 500 }, { columnid: 'E-mail', width: 200 }],
                      cells: {
                          1: {
                              1: {
                                   style: { Font: { Color: "#00FFFF" } }
                                  }
                              }
                            }
                        };
alasql("SELECT * INTO XLSX('myfile.xlsx',?) FROM ?", [options,data]);

If export into xls styles works. In xlsx - headers, titles, colors, other columns options not working.
I try to use XLSXML to export in xlsx but is this case I get error when opening file: "Excel cannot open the file 'filename.xlsx' because the file format for the file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file".

Is there any way to make some styles in xlsx files? Or the only decision is to use old (.xls) Excel version?

[mathiasrw]

You must do this with XLSXML

Please provide a code example that generates the error so we can fix it

[volkovoleg]

Links I used

My code.

var data = [{
                    name: "John Smith",
                    email: "[email protected]"
                }, 
                {
                    name: "Jane Smith",
                    email: "[email protected]"
                }, 
                {
                    name: "Jan Smith",
                    email: "[email protected]"
                }, 
                {
                    name: "Jake Smith",
                    email: "[email protected]"
                }
               ];
                var options = {
                    headers: true,
                    rows: { 1: { style: { Font: { Color: "#FF0077" } } } },
                    columns: [{ columnid: 'name', width: 500 }, { columnid: 'email', width: 200 }],
                    cells: {
                        1: {
                            1: {
                                style: { Font: { Color: "#00FFFF" } }
                            }
                        }
                    }
                };
                alasql("SELECT * INTO XLSXML('file.xlsx',?) FROM ?", [options, data]);

File saved success, but when I try to open it Excel get error "Excel cannot open the file 'filename.xlsx' because the file format for the file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file".

Error

Saved file
file.xlsx

[volkovoleg]

Is there any decision or advice how to solve it? Or styles is enable only for old excel versions?

[mathiasrw]

I have not been able to identify why things are not working. Please post here if you have any further inputs that can lead to fixing this...

[reviewher]

@volkovoleg if you manually rename the file with an xls extension it should work.

@mathiasrw Excel 2016 prevents file type punning. This represents a change from older versions of Excel. In the past you could save a sreadsheetml file with the XLSX file extension and things would just work. Now it gives you the aforementioned error. The fix is to generate a download with the XLS extension.

[truemanfirst]

please, the question have been solved? i can export the excel with the XLS,but could not change the style,and it support Chinese?

[mathiasrw]

Did renaming to .xls solve this?

Please let us know if you find any solution...

[houkk]

No

[JuanAtienza]

Hi,

I imported this libraries in my project:

<script src="https://cdn.jsdelivr.net/alasql/0.3/alasql.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.8.0/xlsx.core.min.js"></script>

This is my code in my controller:

var opts = {
          headers:true, 
          column: {
              style:{
                  Font:{
                      Bold:"1",
                      Color:"#3C3741"
                  },
                  Alignment:{
                      Horizontal:"Center"
                  },
                                 Interior:{
                                      Color:"#7CEECE",
                                      Pattern:"Solid"
                                  }
                            }
        };
        alasql('SELECT * INTO XLSXML("restest280b.xls",?) FROM ?',[opts,data]);

This worked for me.
I think that your problem is in the xlsx version library and in your file extension. I tried with ".xlsx" and it failed. You have to use "xls" extension

[arivasvera]

Hi @JuanAtienza , Width works for you?

[JuanAtienza]

Hi @arivasvera, unfortunately this doesn´t work for me.
I am going to edit my previous response

[nikzsaz]

Hii,
Is there any progress on this issue?
It seems like alasql('SELECT * INTO XLS("newData.xls",?) FROM ?',[mystyle,items]); is very fine and giving the desired result to me. But all i am worried about is the message during opening of this file.

[mathiasrw]

No progress. If you find any way to avoid this message please please please let us know!

[pankaj358]

Hi All,
Any updates on this issue,
alasql ('SELECT * INTO XLSX ("file-name", ?) FROM ?', [mystyle, data-array] );

As I am able to apply style for XLSXML, but if data is large, then this file is not opening, complaining that this is corrupted.

Please let me know, If anyone solved this issue with XLSX

[nikzsaz]

No not yet!! Better to go with XLS that is giving the desired result.

[aallvviinn]

@pankaj358 I am getting same error message if the data is large (9k rows x 20 cols) with below code.
alasql('Select * INTO XLSXML("StyledExcel.xls",?) FROM ?'), [opts, json]);

If I use following code then I don't see any issue with file corrupt message but styles are missing.
alasql('Select * INTO XLSX("StyledExcel.xls",?) FROM ?'), [opts, json]);

Anyone has any workaround?