Create/Modify Excel spreadsheet without using a Com Object in PowerShell?

0
votes

Is there a way to generate an excel spreadsheet using PowerShell without using a Com Object? I have a few really ugly scripts that open an Excel Com Object, add data, run some macros, and save the file to create reports I need. If I could define spreadsheet formatting, then convert a csv to that format using PowerShell, I assume it would be faster, and an overall better script. How could I do that? Is it possible?

2
excelpowershellreportpowershell-2.0spreadsheet
Without any COM object or just without the Excel object? Here is a way with OleDb: blogs.technet.com/b/heyscriptingguy/archive/2008/09/15/… – Richard Morgan
@RichardMorgan I don't want to have Excel installed on the system the script is running on. Is there a format that can be written/defined in plain text (similar to XML) to create custom reports? – EGr
@RichardMorgan I also would want to add color/formatting to cells where necessary. – EGr
If you want to apply formatting and execute VBA inside the workbook, I think your only option is to invoke Excel via COM. A plain dump of the data to CSV can be done via OleDb or ODBC, but beyond that you're into Excel territory. – alroc
@alroc I actually would prefer not to keep the VBA, and instead define the formatting elsewhere. – EGr

2 Answers

2
votes

Are the workbooks .xls or .xlsx files? The latter are basically a bunch of XML files in a Zip archive, so you could extract the XML files from the archive and proceed from there. If you need to hand-craft a .xlsx file this posting on visualbasicscript.com may help.

0
votes

If you are satisfied with accessing the raw data in the spreadsheet/workbook, dumping it to CSV, and/or doing some additional processing outside Excel, see this answer for getting started with accessing the data via ODBC & System.Data.