在没有安装Excel的情况下将CSV复制到多页XLS。(CSVs to multi-sheet XLS without Excel installed. Powershell)

我有一个包含多个CSV文件的文件夹。 我想把它们全部用XLS（或xlsx）制作一张代表每个CSV文件的工作表。

这里的关键区别是Excel未安装且无法安装。

我知道我可以使用EPPlus库 - http://epplus.codeplex.com/ 。 我还读过使用Access数据库引擎 - http://www.microsoft.com/en-us/download/details.aspx?id=13255 。

我已经浏览了许多不同的脚本，用于将CSV转换为传统方式的XLS，并尝试将它们转换为使用其中一种，但似乎并没有走得太远。 没有Excel，这甚至可能吗？

如果它是一个可行的选择，我会愿意安装像LibreOffice这样的东西并学习脚本。

I have a folder with multiple CSV files. I'd like to take all of them and make a XLS (or xlsx) with a sheet representing each CSV file.

The key difference here is Excel is not installed and can't be.

I understand I may be able to use the EPPlus library - http://epplus.codeplex.com/. I've also read of using the Access Database Engine - http://www.microsoft.com/en-us/download/details.aspx?id=13255 .

I've gone through a number of different scripts for converting CSV to XLS in the traditional fashion and tried converting them to use one of these but don't seem to be getting very far. Is this even possible without Excel?

I would be open to installing something like LibreOffice and learning to script with that if it's a viable option.

最满意答案

如果您使用openxml格式* .xlsx，您可以操作excel文档而无需安装Office。

有一些powershell模块（powertools，epplus等）可以用来完成你想做的事情。 这是一个使用我为Spreadsheetlight编写的powershell包装器的解决方案：

创建一些csv文件

Get-Service | Export-Csv -Path c:\temp\Services.csv -NoTypeInformation Get-Process | Export-Csv -Path c:\temp\Processes.csv -NoTypeInformation

创建一个新的Excel文档来保存csv数据

$doc = New-SLDocument -WorkbookName bigxldoc -Path C:\temp -PassThru -Verbose

将csv文件导入excel

Get-ChildItem -Path C:\temp -Filter *.csv | Import-CSVToSLDocument -WorkBookInstance $doc -AutofitColumns -Verbose

保存文档

$doc | Save-SLDocument -Verbose

注意：这仅适用于*.xlsx格式，而不适用于*.xls

编辑-1

默认情况下，数据导入从Row2，Column2开始，可以轻松更改：

Get-ChildItem -Path C:\temp -Filter *.csv | Import-CSVToSLDocument -WorkBookInstance $doc -ImportStartCell A1 -AutofitColumns -Verbose

删除默认工作表'Sheet1'和保存文档

$doc | Remove-SLWorkSheet -WorkSheetName sheet1 -Verbose $doc | Save-SLDocument -Verbose

if you work with the openxml format *.xlsx you can manipulate excel documents without having to have office installed.

There are a few powershell modules(powertools,epplus etc) you can use to accomplish what you want to do. Here is a solution using a powershell wrapper that I wrote for Spreadsheetlight:

Create some csv files

Get-Service | Export-Csv -Path c:\temp\Services.csv -NoTypeInformation Get-Process | Export-Csv -Path c:\temp\Processes.csv -NoTypeInformation

Create a new excel document to hold csv data

$doc = New-SLDocument -WorkbookName bigxldoc -Path C:\temp -PassThru -Verbose

Import the csv files into excel

Get-ChildItem -Path C:\temp -Filter *.csv | Import-CSVToSLDocument -WorkBookInstance $doc -AutofitColumns -Verbose

Save the document

$doc | Save-SLDocument -Verbose

Note: this will only work with *.xlsx format and not *.xls

EDIT-1

By default the data import starts at Row2, Column2 which can be changed easily:

Get-ChildItem -Path C:\temp -Filter *.csv | Import-CSVToSLDocument -WorkBookInstance $doc -ImportStartCell A1 -AutofitColumns -Verbose

Remove the default worksheet 'Sheet1' and Save Document

$doc | Remove-SLWorkSheet -WorkSheetName sheet1 -Verbose $doc | Save-SLDocument -Verbose