Buttons - Excel - Creating formulas in result file gives errors

mgm

I have a problem with excel files I generate with dt/buttons.
All the data is there, file opens correctly but if I add a formula having empty cells in the range, Excel (v2010) gives an error.

For example, I have 10,<empty>,30 in A2,B2,C2 and put '=sum(A2:C2)' in D2
Just going to B2 (the empty cell) and typing <Enter> fix the bug => 40 is displayed.

There is something that Excel doesn't like in empty cells... (in the xml it's just an empty <v></v>, I found in the xml specs that an empty cell should not be present, by looking at the source this is not the case, there is a column loop inside the rows' loop).

I use latest versions.
Data comes from url/json, some values are == null (result of sql crosstab query).
I have orthogonal:null in exportOptions (no render function applied).
I have defaultContent:'' for every column.

Can you confirm this ?
If I rewrite the export loop to exclude empty cells (in this case, naming each cell will be mandatory from A1 to ZZ999...), will you accept this kind of patch ?

allan

Hi,

Requiring named cells would be quite frustrating (not your fault!) as it is something I had hoped to avoid (reduced complexity). I haven't looked much into that part of the Excel document spec, but presumably the cell names can just be defined in a loop as required.

Allan

mgm

I need this fix asap, but when I grep '<v>' in the whole tree, I found it in:

• datatables.js
• Buttons-1.0.3/js/buttons.html5.js
• Buttons-1.0.3/js/buttons.flash.js

Is the first one used ?

allan

Sounds like you are using the DataTables downloader to build the files for you. The datatables.js file is just a concatenation of the other files - including the two buttons files you note. So the first should be made int he buttons files and the datatables.js file updated if you want to continue using it as a single file to load.

Allan

mgm

Ok, I didn't noticed that as the zip file downloaded after the choices done in the download page contains many sub directories. Finally keeping only datatables.js/.css is enough :-)

mgm

Except that I need also ./DataTables-1.10.9/images/ (for sort icons) and flashExport.swf (I prefer to have it locally...)

mgm

I fixed the initial problem (formulas error) by writing only used cells in the xml.

Here is the "diff -u " :

@@ -94557,6 +94557,17 @@
        footer: false
 };
 
+var _colIndexToExcel = function ( colIndex ) // return Excel column name (A,B,C,...,AA,AB,...) from column index (1..n)
+{
+       var result = '';
+       while ( colIndex > 0 ) {
+               colIndex--;
+               result = String.fromCharCode( 'A'.charCodeAt(0) + (colIndex % 26) ) + result;
+        colIndex = ~~(colIndex / 26);
+       }
+       return result;
+};
+
 //
 // Excel (xlsx) export
 //
@@ -94575,19 +94586,24 @@
                // Set the text
                var xml = '';
                var data = dt.buttons.exportData( config.exportOptions );
+               var rowNum = 0;
                var addRow = function ( row ) {
+                       rowNum++;
                        var cells = [];
 
                        for ( var i=0, ien=row.length ; i<ien ; i++ ) {
-                               cells.push( $.isNumeric( row[i] ) ?
-                                       '<c t="n"><v>'+row[i]+'</v></c>' :
-                                       '<c t="inlineStr"><is><t>'+
-                                               row[i].replace(/&(?!amp;)/g, '&amp;')+
-                                       '</t></is></c>'
-                               );
+                               if ( row[i] != '' ) {
+                                       var colXlName = _colIndexToExcel( i+1 ) + rowNum;
+                                       cells.push( '<c r="' + colXlName + '" ' +
+                                               ($.isNumeric( row[i] ) ?
+                                               't="n"><v>' + row[i] + '</v>' :
+                                               't="inlineStr"><is><t>' + row[i].replace(/&(?!amp;)/g, '&amp;') + '</t></is>' ) +
+                                               '</c>'
+                                       );
+                               }
                        }
 
-                       return '<row>'+cells.join('')+'</row>';
+                       return '<row r="'+rowNum+'">'+cells.join('')+'</row>';
                };
 
                if ( config.header ) {

I add the row number to each row, the cell name (ie C5) to each cell and exclude empty cells.

allan

AWesome - thanks for posting this. I'll look into integrating it into Buttons.

Allan

mgm

I still have something I would like to fix before putting my website in production, I have a case where one of the cell contains the string "+1" (it's a film title).

The current code thinks it's a numeric and use excel's <v> tag instead of <t>.

This gives a warning when opening the file with Excel: "Excel found unreadable content in file.xls. Do you want to recover the content of this workbook ?"

The only way to avoid this is to have access to the column data type in the loop ?

Can you give me some tip to get this ?

mgm

This will also permit to see how to align numbers on the right in the print preview :-)

allan

Are you using Buttons 1.1.0 or the nightly version? It should not detect anything with + in it as a number - that should always be written as a string.

Allan

mgm

Version is 1.1.0 but if I have a film title like "2012", it should be seen as a string anyway to align it on the left (it's ok in the 'td' as it has the good type but not in export).

I'll try the nightly version tomorrow.

mgm

Sorry, I was on 1.0.3 :-(
I tried 1.1.0 and saw your changes but I still have my formula bug in the excel file.
But I have my patch for this...

What I really need is the way to find the column type, I will update the _exportData function to add the list of the columns' types but I tried many objects with "console.log/firebug" and nothing shows the type (dt.aoColumns does not exist, dt.columns.type neither, ...).

allan

There isn't really a way to get that in DataTables as the column type is an internal property. For example you could end up with something like monent-DDDD MM YYYY and lots of other strings that it would need to handle correctly. Instead the approach Buttons takes is to check each cell's data individually.

Allan

mgm

Does the word "really" in your response give me a chance to reach my goal ? ;-)

Your fix work for the film title "+1", it's seen as a string and excel doesn't cry anymore but for "2012", I am still stuck with the bad alignment...

(and other problems with titles containing "<-->" are still in my todo...)