Excel xlsx Export not reliable

[DeployDuckParticipant]

Hello,
I have a couple of problems with the new xlsx export from grid.
The table to export has about 2.500 columns of all datatypes. This is an important scientific project, and we urgently need a reliable export of the entire table. For the moment of testing the table has only about 30 rows. Export to csv seem working.

The problems with xlsx:
1. Not all colums are exported. Some columns are missing in between (simple text and number columns). All columns after ‘BFR’ are missing, which corresponds to column 1526).
2. Only the first column of type “date” is exported correctly all following columns of type “date” show something like 44014,3501157407. All datetime fields are from MySQL-Database fields of type “DATETIME”. In the “datafields” array for the grid all datetime fields are from type: date. The columns array for the grid is set to cellsformat: ‘dd.MM.yyyy HH:mm’
3. Excel (2019) shows multiple warnings/error-messages:

"We found a problem with some content... Shall we restore as much as possible?

Excel has completed the file-level review and repair. Some parts of this workbook have been repaired or discarded.
Fixed part: /xl/worksheets/sheet1.xml-Part with XML error. Error at loading. Row 3, column 0.
Removed records: cell information from /xl/worksheets/sheet1.xml-Part

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error014640_01.xml</logFileName><summary>Fehler in Datei 'C:\Users\dh\Downloads\Gesamtliste (2).xlsx'</summary><repairedParts><repairedPart>Reparierter Teil: /xl/worksheets/sheet1.xml-Part mit XML-Fehler.  Fehler beim Laden. Zeile 3, Spalte 0.</repairedPart></repairedParts><removedRecords><removedRecord>Entfernte Datensätze: Zellinformationen von /xl/worksheets/sheet1.xml-Part</removedRecord></removedRecords></recoveryLog>

Please help with this. We need a quick solution.
thank you.

With regards
DeployDuck

[DeployDuckParticipant]

Update:
I tried to use the older exportdata function to export the same table to the old ‘xls’ Format: and all the columns are present and all the datetime fields are shown correct. What remains are the warning messages from Excel and the fact that we have to offer our customers an outdated data format.

[Peter StoevKeymaster]

Hi,

We will need a sample which demonstrates the reported problem on your side in order to test and debug it.

Regards,
Peter

[DeployDuckParticipant]

Hello Peter,
sorry to hear this. Its absolutly impossible for me, to provide you with a sample.
Due to a confidentiality declaration, it is unfortunately not possible for me to send you an extract from the program. I have to see if I can find the time in the next few months or next year to build something similar for you as an example. I will not be able to do so in the next few months due to lack of time.

I have already used the exact same export function in several hundred tables without any problems. The table in question differs only in the number of columns and in no other way.
I therefore assume that your new export view will not be able to cope with the sheer amount of columns. The affected table currently has exactly 2358 columns with 33 rows. Columns of the type text, date, number (int and float), bool are included. Your exportview function exports this table to csv, tsv, json without errors. The older function exportdata also exports to xls !, csv, tsv, json without errors. Only the export to xlsx fails and only exports 1526 columns.

I use:
$("#MyGridID").jqxGrid('exportview', 'xlsx', hev.Spk(ExportFileName));
to export the data.

As a datasource I use:

var source = { 
            datatype: 'array',
            datafields: [
                { name: 'Pseudonym',           type: 'string' }, 
                { name: 'Group',               type: 'int' },
                { name: 'RegisterAt',          type: 'date' },
                ...
           ],
            id: 'ID',
            localdata: daten
        
        };
        var dataAdapter = new $.jqx.dataAdapter(source);

There were:
1977 * type ‘int’
267 * type: ‘string’
21 * type: ‘float’
93 * type: ‘date’

This is my Grid definition:

$("#GridID").jqxGrid({
            width: '100%', 
            autoheight: true,
            altrows: true,
            columnsresize: true,
            filterable: true,             
            showfilterrow: true,
            groupable: true,               
            showgroupmenuitems: true,               
            pageable: true,                 
            pagesize: [15,20,25], 
            pagesizeoptions: PageSizeSet.Pagesizeoptions,
            localization: GermanTranslationObject,     
            theme: MyTheme,
            source: dataAdapter,     
            columns:  SetColumns_ausw()  
        }); 

And my column definitions look like:

const SetColumns_omp_ausw_export = function(){
    let columns = [
        //Nutzer
        { text: 'Pseudonym',	    datafield: 'Pseudonym',	    columntype: 'textbox',	     		   width: 200 },
        { text: 'Group',            datafield: 'Group',	            columntype: 'textbox',	     		   width: 100 },
        { text: 'RegisterAt',       datafield: 'RegisterAt',	    columntype: 'textbox', filtertype: 'range', cellsformat: 'dd.MM.yyyy HH:mm',width: 180 },

        { text: 'EXAMPLE',	    datafield: 'EXAMPLE',	    columntype: 'numberinput',      cellsformat: 'f1',		    width: 100 },
        ...
     ];
    return columns;
};

To find the error, I compared the xlsx export column by column with the xls export in Excel. Both exports appear to be the same up to column AFZ / 858.

Column 858 is of type MySQL TINY(1) / bool and contains the values 0, 1 and null.
datadefinition is:
{ name: ‘GES_13_3b_T2’, type: ‘int’},

columndefinition is:
{ text: ‘GES_13_3b_T2’, datafield: ‘GES_13_3b_T2’, columntype: ‘textbox’, width: 100 },

Column 859 is of type MySQL BIGINT(20) /int and contains the values 0, 7, and null.
columndefinition is:
{ text: ‘GES_13_3c_T2’, datafield: ‘GES_13_3c_T2’, columntype: ‘textbox’, width: 100 },
datadefinition is:
{ name: ‘GES_13_3c_T2’, type: ‘int’},

Nothing special. I used the same settings everywhere without any problems. I can’t see any reason in the data.

After column 858 ( GES_13_3b_T2) there is a gab of 832 columns but no empty columns in excel.
The gab ends at a TINY(1) /int column and starts again with a TINY(1)/int column.
The remaining columns are then completely available in the export.

Have you any suggestion, why exportview faild?

thanks in advance.

[DeployDuckParticipant]

I did the job of providing you with all the details necessary for you to answer the question. Since I haven’t received an answer from you after 2 weeks, I have now turned to one of your competitors. They answered immediately.

Too bad. jqWidgets controls are good – but worthless without functioning customer service.

[DeployDuckParticipant]

Not interested in an answer any more.

close this.

[adminKeymaster]

Hi DeployDuck,

Thank you for the feedback.

The reason I asked for an example is simply, because this case, which is specific to your web application cannot be reproduced with a simple example.

Best regards,
Peter Stoev

jQWidgets Team
https://www.jqwidgets.com/

[Author]

Posts