Libname XLSX on Linux stability

[RW9]

Am having a strange issue with libname xlsx which I wanted to see if anyone else was having. 

Scenario is, when creating an xlsx in SAS 9.4.1.M3 on linux (and then transferring it to windows), using the following code:

libname tmp xlsx "test.xlsx";
proc copy in=sashelp out=tmp memtype=data;
  select class;
run;
libname tmp clear;

I get an xlsx file, which is fine, and I can send the file via email also.  However when I include another dataset, say:

libname tmp xlsx "&_SASWS_./MUNDIPHARMA/test.xlsx";
data c1 c2; set sashelp.class; run;
proc copy in=work out=tmp memtype=data;
  select c1 c2;
run;
libname tmp clear;

This is the warning returned by the email system:

Microsoft Forefront Protection for Exchange Server removed a file since it was found to be infected.
File name: "winmail.dat->test (1).xlsx"
Malware name: "CorruptedCompressedFile"

So it seems that the xlsx engine works fine when one sheet is created, but on multiple sheets it is being thrown out as invalid.  If I take the file which gets rejected, open it in Excel, and saveas, the file created from there works fine, and is larger than the original as well (expected with the extra excel information).  

So my question is, does anyone know of an issue with the above, known bug or anything?

[SuryaKiran · Accepted solution]

Hi @RW9

I think this is not something withing the SAS. There are some discussions about this in Microsoft forums and seems like it may be a bug in Microsoft Forefront which is trying to identify the file. 

There is a setting which disable deleting of corrupted or multiparted archives.

 Forefront Server Security Administrator > Settings> General Options > Scanning

 checkbox name is "Delete Corrupted Compressed File"

For more info: https://social.technet.microsoft.com/Forums/forefront/en-US/dbfb3252-e0e6-4a29-8979-bfb5b5a36409/cur...

[RW9]

Thanks, I will let our IT look at that then.

[ChrisHemedinger]

Bug/behavior aside, my preferred method for creating multiple sheets (or replacing a sheet in an existing workbook) is PROC EXPORT with DBMS=XLSX.  I think it's more of the well-worn path, and it just seems to work more smoothly.  LIBNAME XLSX is convenient for quick discovery of what's in a spreadsheet (and I love using it for that) -- as long as the data sets are well formed with data starting in A:1.  But as it requires an exclusive lock on the workbook file, I find it tricky to use for complex data operations.

[RW9]

Thanks, I don't often use it so not an issue.  Will keep the dbms=xlsx option in mind.