While I was happy with the existing Export-Csv Cmdlet I missed some of the Excel features, like autofit and autofilter. Sure, I can open any CSV file in Excel and then apply the autofilter and autofit to get what I want. But that would mean more clicking.

So I thought I could write my own Export-Xlsx function to export directly to Excel. The first incarnation of that function wrote all the data of an object to each and every cell individually. While it is nice to see each cell being filled with data, it is ridiculously slow when the amount of data increases. Even a little increase.
So then I decided to first export all data to a temporary CSV-file and then open it in Excel, convert it to an Excel workbook and then save it again. Nice, but not quite nice enough.
The next incarnation didn’t export to CSV at all. Instead, it converted an object to CSV in memory and then converted it to a multidimensional array before adding the data to an Excel workbook. This was even nicer, since it didn’t involve any temporary files.

But then I figured: why go through the hassle of converting to CSV in the first place? Why not directly convert the object to a multidimensional array?
Enter the next incarnation of my export function.

Content with my proceedings so far I realized that perhaps it would’ve been even easier to simply google on the term ‘export-xls’. Duh!
It lead me to a function similar to the one I had just written myself, but with a couple of extra functionalities. How about adding a chart? Or appending to an existing Excel workbook?
While that function is quite similar to mine it used the Windows clipboard to copy the data to Excel and it used the Stop-Process Cmdlet to close the Excel instance (well, all running instances of Excel).
In my function I don’t overwrite anything the user has in his/her clipboard and I don’t kill any Excel process the user might be working in at that moment.

And so I took the liberty to borrow some ideas and code from the function I had found and added it to my function. As usual I declare the function globally, but in your case you might want to remove that bit.

Tagged . Bookmark the permalink.

Comments are closed.