MS Excel Compatibility
MS Excel Compatibility
Nicodeb
Posts: 4Questions: 1Answers: 0
Link to test case: Removed
Error messages shown: MS Excel Error : A problem has been found in the content of the file. Try to Repair : XML error on /xl/worksheets/sheet1.xml. Loading error. Line 2, column 0.
Description of problem: I had to make some customizations on the .xlsx to make readable data on the .xlsx export. The code works file when i open the exported file with LibreOffice but not with MS Excel. If I " save as " from LibreOffice, the result file works fine on MS Excel.
Here is the code :
function columnToLetter(column) {
var temp, letter = '';
while (column > 0)
{
temp = (column - 1) % 26;
letter = String.fromCharCode(temp + 65) + letter;
column = (column - temp - 1) / 26;
}
return letter;
}
// Enlève les balises html en préservant les sauts de lignes
function remove_tags(html) {
// Remplace les balises <br> par un placeholder
html = html.replace(/<br>/g,"$br$");
// Remplace les sauts de lignes par un placeholder
html = html.replace(/(?:\r\n|\r|\n)/g, '$n$');
// Créer une DIV temporaire
var tmp = document.createElement("DIV");
tmp.innerHTML = html;
// Efface toutes les balises HTML
html = tmp.textContent||tmp.innerText;
// Remplace les placeholders par des balise <br>
html = html.replace(/\$br\$/g,"<br>");
html = html.replace(/\$n\$/g,"<br>");
return html;
}
$(document).ready(function() {
var table = $('#home-table').DataTable({
paging: true,
autoWidth: true,
dom: 'Bfrtip',
buttons: [
{
extend: 'excelHtml5',
text: 'Excel', // Texte personnalisé pour le bouton Excel
extension: '.xlsx',
filename: 'UITS - Export Prestataires',
exportOptions: {
format: {
body: function(data,row,column,node) {
// Change les double guillemets en simple
data = data.replace( /"/g, "'" );
// Remplace le tiret par <br> et retire l'espace avant dans la colonne des procédés
if(column === 6) {
data = data.replace(/([^\s])\s?-/g, "$1<br>-");
}
// Enlève les balise html
data = remove_tags(data);
// Sépare les lignes
splitData = data.split('<br>');
// Enlève les lignes vides
splitData = splitData.filter(function(v){return v!==''});
data = '';
for (i=0; i < splitData.length; i++) {
// Ajoute des double guillements autour de chaque ligne
data += '\"' + splitData[i] + '\"';
// Ajoute CHAR(10) quand ce n'est pas la dernière ligne
if (i + 1 < splitData.length) {
data += ', CHAR(10), ';
}
}
return data;
}
},
columns: [1, 2, 3, 4, 5, 8, 9, 13, 17],
},
customize: function (xlsx) {
var sSh = xlsx.xl['styles.xml'];
var styleSheet = sSh.childNodes[0];
cellXfs = styleSheet.childNodes[5];
// Using this instead of "" (required for Excel 2007+, not for 2003)
var ns = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';
// Create a custom style
var lastStyleNum = $('cellXfs xf', sSh).length - 1;
var wrappedTopIndex = lastStyleNum + 1;
var newStyle = document.createElementNS(ns, "xf");
// Customize style
newStyle.setAttribute("numFmtId", 0);
newStyle.setAttribute("fontId", 0);
newStyle.setAttribute("fillId", 0);
newStyle.setAttribute("borderId", 0);
newStyle.setAttribute("applyFont", 1);
newStyle.setAttribute("applyFill", 1);
newStyle.setAttribute("applyBorder", 1);
newStyle.setAttribute("xfId", 0);
// Alignment (optional)
var align = document.createElementNS(ns, "alignment");
align.setAttribute("vertical", "top");
align.setAttribute("wrapText", 1);
newStyle.appendChild(align);
// Append the style next to the other ones
cellXfs.appendChild(newStyle);
var sheet = xlsx.xl.worksheets['sheet1.xml'];
// Width de la colonne procédé
$('col', sheet).eq(6).attr('width', 100);
//Width colonne Secteur d'activité
$('col', sheet).eq(5).attr('width', 30);
var firstExcelRow = 3;
table.rows({order: 'applied', search: 'applied'}).every( function ( rowIdx, tableLoop, rowLoop ) {
var node = this.node();
var num_colonne = 9;
// the cell with biggest number of line inside it determine the height of entire row
var maxCountLinesRow = 1;
for ( var indexCol = 1; indexCol <= num_colonne; indexCol++ ) {
var letterExcel = columnToLetter(indexCol);
$('c[r=' + letterExcel + ( firstExcelRow + rowLoop ) + ']', sheet).each(function(e) {
// Ajoute l'attribut string à toutes les cellules
$(this).attr('t', 'str');
// Compte le nombre de lignes
var countLines = ($('is t', this).text().match(/\"/g) || []).length / 2;
if ( countLines > maxCountLinesRow ) {
maxCountLinesRow = countLines;
}
// Insertion du style alignement top
$(this).attr('s', wrappedTopIndex );
var cellValue = $(this).text();
// Ajoute la formule et le concatener dans les cellules
$(this).append('<f>CONCATENATE(' + cellValue + ')</f>');
});
$('row:nth-child('+( firstExcelRow + rowLoop )+')', sheet).attr('ht', maxCountLinesRow * 15);
$('row:nth-child('+( firstExcelRow + rowLoop )+')', sheet).attr('customHeight', 1);
}
});
}
},
Answers
It's the last loop of the customize function that is breaking the file for MS Excel, if i comment it the export works fine but I don't have the customize cells size and line returns that I need.
The loop :
Thanks for the follow up and confirming that it isn't an error in DataTables / Buttons.
I really need to get around to writing an abstraction layer for this sort of thing sometime. Customing the XML is quite difficult and error prone.
I'm not entierly sure why Excel doesn't like that XML I'm afraid. What I normally do with this sort of thing is to create the file in Excel and save it. Then rename as .zip and unzip it to see what the XML you want is. Then you can dump the XML you are creating / modifying to see what the difference is.
Allan
Hi Allan, thanks for your answer and the zip tip
I'll try that out.
It was just a matter of adding <r> tag with <t xml:space="preserve"> to finally get it worked !
Nice one! Thanks for posting back.
Allan