将Excel(xls)文件转换为不带GUI的逗号分隔(csv)文件 [英] converting an Excel (xls) file to a comma separated (csv) file without the GUI
问题描述
有没有一个简单的方法来翻译XLS到CSV格式的文件,而无需启动Excel窗口应用程序?
Is there a simple way to translate an XLS to a CSV formatted file without starting the Excel windowed application?
我需要用脚本处理一些Excel XLS工作簿。为此,我需要将xls文件转换为csv文件。这可以通过Excel应用程序中的保存来完成。但是,我想自动化这(所以,不打开Excel应用程序窗口)。
I need to process some Excel XLS workbooks with scripts. For this i need to convert the xls file into a csv file. This can be done with a save-as from the Excel application. But, i would like to automate this (so, not open the Excel application window).
如果工作簿中的第一张工作表被转换为CSV格式,就足够了。
我需要只处理该表中的数据。
It will suffice if the first sheet from the workbook gets translated to the CSV format. I need to just process data in that sheet.
我的Cygwin和Excel安装在我的系统上 - 如果这有帮助。
I have Cygwin and Excel installed on my system -- if that helps.
编辑:Ok,我有一个Perl的工作解决方案。更新以供他人日后使用。
Ok, i have a working solution with Perl. Updating for future use by others.
我安装了 Spreadsheet :: ParseExcel 模组。
,然后使用 read-excel.pl 示例。
I installed the Spreadsheet::ParseExcel module. and then used read-excel.pl sample.
我的代码是这个示例代码的一个稍微变化,如下所示。
My code is a slight variation of this sample code, as below.
#!/usr/bin/perl -w
# For each tab (worksheet) in a file (workbook),
# spit out columns separated by ",",
# and rows separated by c/r.
use Spreadsheet::ParseExcel;
use strict;
my $filename = shift || "Book1.xls";
my $e = new Spreadsheet::ParseExcel;
my $eBook = $e->Parse($filename);
my $sheets = $eBook->{SheetCount};
my ($eSheet, $sheetName);
foreach my $sheet (0 .. $sheets - 1) {
$eSheet = $eBook->{Worksheet}[$sheet];
$sheetName = $eSheet->{Name};
print "#Worksheet $sheet: $sheetName\n";
next unless (exists ($eSheet->{MaxRow}) and (exists ($eSheet->{MaxCol})));
foreach my $row ($eSheet->{MinRow} .. $eSheet->{MaxRow}) {
foreach my $column ($eSheet->{MinCol} .. $eSheet->{MaxCol}) {
if (defined $eSheet->{Cells}[$row][$column])
{
print $eSheet->{Cells}[$row][$column]->Value . ",";
} else {
print ",";
}
}
print "\n";
}
}
更新:这是一个Powershell脚本,也可能很容易使用;
as-is从此MSDN博客 and,SO Reference 。
Update: Here is a Powershell script that might also be easy to work with; as-is from this MSDN blog and, SO Reference.
$excel = New-Object -comobject Excel.Application
$workbooks = $excel.Workbooks.Open("C:\test.xlsx")
$worksheets = $workbooks.Worksheets
$worksheet = $worksheets.Item(1)
$range = $worksheet.UsedRange
foreach($row in $range.Rows)
{
foreach($col in $row.Columns)
{
echo $col.Text
}
}
更新:最近我在这个网站上遇到了一个Windows工具 CSVed 。 a href =http://superuser.com/questions/54356/program-to-display-csv-nicely-from-clipboard/54362#54362>超级用户答复,对某些人可能有用。 / p>
Update: I recently came across a Windows tool CSVed at this Superuser answer which might be useful to some people.
推荐答案
使用perl脚本。使用CPAN的Spreadsheet :: ParseExcel perl模块解析xls文件,然后输出csv应该可以正常工作。
Use a perl script. Using the Spreadsheet::ParseExcel perl module from CPAN to parse the xls file followed by output as csv should work fine.
http://search.cpan.org/dist/Spreadsheet-ParseExcel
你也可以尝试使用VBScript。
You could also try using VBScript.
这篇关于将Excel(xls)文件转换为不带GUI的逗号分隔(csv)文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!