Home Forums Programming Powershell Powershell and Excel and SQL Server RE: Powershell and Excel and SQL Server

  • Yes. If you can guarantee that the spreadsheet will be in .xlsx form it makes things a lot simpler. Remember, an .xlsx document is really just a suite of XML files. Change the .xlsx extension to .zip on some spreadsheet, and you can explore how the data is arranged.

    Microsoft has an SDK that lets you manipulate these files with a .net object model.

    Welcome to the Open XML SDK 2.5 for Office

    http://msdn.microsoft.com/en-us/library/bb448854(v=office.15).aspx

    But most people find it very difficult to use.

    Someone has written a "wrapper" for this that makes it more like the the Excel Object Model that you used in your example

    ClosedXML

    http://closedxml.codeplex.com/

    Most of the examples are in C# or VB -- but you should be able to translate it.

    I used this successfully at a prior employer and I can't track down any sample code of what I did - but it does work.

    And since you are just reading text files that are XML formatted - it is pretty fast - AND you don't need Excel installed on that machine.

    Also Microsoft really discourages use of the Excel Object Model in a server environment. Every time you do something it spawns an instance of Excel - and try as you might you can't guarantee that the instance is always terminated - so you wind up with dozens or hundreds of Excel instances gumming up memory.