Recently I have been asked to populate a collection of Items with the content of an Excel file, a rather common request that in the past I have fulfilled with third party C# libraries.
This time around however I wanted to use Sitecore Powershell: after a brief search I found the following code that would open a sample document from my hard drive:
$excelFilePath = "c:\temp\test.xlsx";
$objExcel = New-Object -ComObject Excel.Application
$wb = $objExcel.Workbooks.Open($excelFilePath)
Powershell, however, was not impressed:
Apparently the current user lacks the permission to use the Excel application, or to be more precise, the Excel COM Object. In a regular Powershell context it would be enough to run the Powershell prompt with Administrator privileges, but in our case it’s different: as the plugin author confirmed, the session runs externally with the privileges of the process.
In our case, the process in question is IIS’ w3wp.exe, and running THAT with Administrator privileges is a really, really bad idea for many reasons.
So you check in IIS what user account your Application Pool is assigned to:
…or alternatively, you ask Powershell itself by using the following command:
Either way, you must now open the Component Services from the Administrative Tools and navigate to Component Services -> Computers -> My Computer -> DCOM Config -> Microsoft Excel Application:
Right-click on Microsoft Excel Application and select Properties. From there, pick the Security tab
And Customize each property, giving both Local and Remote permission to the User you need – in my case, it was Network Service. But once again, Excel is not happy:
After attempting a number of solutions, I eventually discovered this to be a very odd bug in Excel: in order to allow remote manipulation of files through the Excel COM object, it is necessary for the following folders to exist:
(the latter only in 64-bit systems)
And on both of them, the User of your process must have Full Control – so once again, their Security tab must be edited as needed.
After this last hurdle, I was finally able to manipulate the file from my Sitecore instance of Powershell.