无需 GUI 即可将 Excel (xls) 文件转换为逗号分隔 (csv) 文件 [英] converting an Excel (xls) file to a comma separated (csv) file without the GUI

问题描述

是否有一种简单的方法可以在不启动 Excel 窗口应用程序的情况下将 XLS 转换为 CSV 格式的文件?

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.

好的，我有一个 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
";
    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 "
";
    }
}

<小时>

更新:这是一个可能也很容易使用的 Powershell 脚本；原样来自 这个 MSDN 博客 还有，所以参考.

---

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:	est.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在这个 超级用户答案，这可能对某些人有用人.

---

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.

这篇关于无需 GUI 即可将 Excel (xls) 文件转换为逗号分隔 (csv) 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！