SAS XLSX Export Failure: Character Data Lost in Pre-Created Excel Files (Win11 Environment)

[Stephanvd]

An XLSX file updated (not generated) with the SAS XLSX LIBNAME engine does not open successfully in Excel (windows 11), while the code did work in Windows 10.

Test:
1. XLSX File created in windows explorer (New -> Microsoft Excel Worksheet)
2. SAS code to add new sheet 'class' from sashelp.class
3. Error message during opening (see appendix): 
    1. We found a problem with some content in 'excel_win.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.
   2. Excel was able to open the file by repairing or removing the unreadable content. Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded. Removed Records: Cell information from /xl/worksheets/sheet2.xml part

Question: Could you please help with the possible cause, plus a check on how I can verify this and ultimately fix it?

Note: By creating the XLSX file from scratch in SAS, it works without issue.

Code: 

/*ERRORS: Update XLSX (Add sheet)*/
libname xls xlsx "<PATH>\test.xlsx"; 

data xls.class;
  set sashelp.class;
run;

libname xls clear ;

/*SUCCESSFULL: Create XLSX (Create file)*/
libname xls2 xlsx "<PATH>\test2.xlsx"; 

data xls2.class;
  set sashelp.class;
run;

libname xls2 clear ;

Or thoughts:

- The internal XML structure of the blank file, created by Windows/Excel, is somehow incompatible with the way the SAS LIBNAME XLSX engine modifies it to insert data

- When writing character data, a mismatch between a limited SAS session encoding and Excel's internal UTF-8 requirement, leads to a trans-coding error that corrupts the XML structure.

[ChrisHemedinger]

I recommend that you work with SAS Technical Support on this. You have a good summary of the problem here -- they can help identify if it's a bug or whether a fix is available, and maybe identify workarounds.

[Tom]

Do you have the same issue with an existing XLSX file that actually contains information?

Or only with an "empty" file created by the NEW menu item in Explorer?

When I tried it with PC-SAS it worked fine if I first entered something into at least one of the cells in the "new" xlsx file.