CSV到多张XLS而不安装Excel。电源外壳 [英] CSVs to multi-sheet XLS without Excel installed. Powershell

问题描述

我有一个包含多个CSV文件的文件夹。我想采取所有的，并制作一个XLS（或xlsx）与表示每个CSV文件的表。

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.

这里的关键区别是Excel是不安装的，不能。

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

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

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 .

我经历过不同的脚本用于将CSV转换为XLS的传统方式，并尝试转换他们使用这些，但似乎并没有变得很远。在没有Excel的情况下这是可能的吗？

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?

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

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

推荐答案

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

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

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

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:

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

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

Create a new excel document to hold csv data

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

将csv文件导入excel

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

注意：这仅适用于 *。xlsx 格式而不是 *。xls

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

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

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

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

Remove the default worksheet 'Sheet1' and Save Document

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

这篇关于CSV到多张XLS而不安装Excel。电源外壳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！