When exporting to an Excel file, why is the sheet1.xml file in the Excel file not in xml format?

gillDong

Hi..

I've tried several methods to put a footer in the Excel file that displays when printed.
Through customize, I realized that I can edit the xml.

For example, I was able to specify my own font size by modifying style.xml like the code below.

var cellA1 = $('c[r=A1]', sheet);
var styles = xlsx.xl['styles.xml'];
var fontSize = '<sz val="14"/>'; // Font size 14
var alignment = '<alignment horizontal="center" vertical="center"/>'; // Center alignment

var cellXfs = styles.getElementsByTagName("fonts")[0];
var numCellXfs = cellXfs.getAttribute('count');
cellXfs.setAttribute('count', parseInt(numCellXfs) + 1);


var fontIndex =  getNextCustomStyleIndex(styles, "fonts");
styles.getElementsByTagName('fonts')[0].innerHTML += ` <font>
<sz val="12" />
<name val="Calibri" /><b />
</font>`;

styles.getElementsByTagName('cellXfs')[0].innerHTML +=  '<xf numFmtId="0" fontId="'+fontIndex+'" fillId="0" borderId="0" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1">' +
    fontSize + alignment + '</xf>'
// Apply the custom style to cell A1
cellA1.attr('s', customStyleIndex);

Realizing that this code worked, this time I tried to edit sheet1.xml to make it work.

var root = $(sheet).find('worksheet');
var headerFooter = $('<headerFooter/>');
var oddFooter = $('<oddFooter/>').text('&LTEST');
headerFooter.append(oddFooter);

// Append the footer to the root element

console.log(sheet);

var pageMargins = $('<pageMargins/>').attr({
    'left': '0.70866141732283472',
    'right': '0.70866141732283472',
    'top': '0.74803149606299213',
    'bottom': '0.74803149606299213',
    'header': '0.31496062992125984',
    'footer': '0.31496062992125984'
});

// Create the pageSetup element and set its attributes
var pageSetup = $('<pageSetup/>').attr({
    'paperSize': '9',
    'orientation': 'portrait',
    'r:id': 'rId1'
});

root.append(pageMargins);
root.append(pageSetup);
root.append(headerFooter);

However, this code did not work properly, so I changed the extension of the exported Excel file to zip, and then opened the sheet1.xml file.
The sheet1.xml file did not have an xml structure unlike other normal Excel files.
and I think this is what causes it to not work. Is there any way to solve this?

allan

It should be! If it isn't XML, then Excel would refuse to open it.

I've just a debugger on this example and confirmed that xlsx.xl.worksheets['sheet1.xml'] inside the customize function is an XML document.

One important point is that this:

$('<pageMargins/>')

might cause you problems, since it will create the element in the current HTML document, not the XML document.

I think you should do:

var pageMargins = sheet.createElement('pageMargins');
pageMargins.attr( ... )

Likewise for the pageSetup.

Allan