在 Perl 中读取原生 Excel 文件
在我为 Linux Journal 网络版撰写的上一篇文章中，我讨论了一个基于 Web 的程序，该程序查询 SQL 数据库并输出原生 Excel 文件。那篇文章是基于我前段时间为一个客户编写的程序。今天，我将要写的是做完全相反的事情；今天的程序将接受一个 Excel 电子表格，从中收集数据，并添加到 SQL 数据库中。这个程序也是基于我正在为一个客户编写的程序。事实上，这两个程序都是我为同一个客户编写的。
过去，我的客户会使用标准的 ASCII 文件将数据上传到数据库中。他们在 Excel 中处理数据，并在需要将其放入 SQL 数据库时导出数据。然而，似乎他们总是难以记住他们的数据应该是以逗号分隔格式还是制表符分隔格式导出。仅凭这一点就造成了一些令人沮丧的时刻。但情况变得更糟。
逗号分隔的数据在文本编辑器中很容易读取，并且在编程上也不难操作。这种格式非常适合大多数类型的数据，例如姓名和电话号码。但它并不适用于所有类型的数据。例如，逗号分隔格式非常适合存储关于我的朋友的信息
John,Q,Public,15055551234
很容易看出我们有名字、中间名首字母、姓氏和电话号码。但是，逗号分隔格式对于存储关于我的朋友的儿子 John Q. Public, Jr 的信息效果不佳
John,Q,Public, Jr.,15055551234
因此，似乎制表符分隔格式可能更好
John Q Public, Jr. 15055551234
嗯，这看起来好多了。但是，有一个问题。当我输入这些数据时，我不小心在名字的末尾放了一个空格。你能看到吗？不，我也看不到，但它就在那里，并且可能会在未来给我们带来很多问题。
最后，如果我们有不止几个字段要处理怎么办？制表符分隔格式很快就会变得难以在编辑器中查看。
因此，似乎将我们的数据保持为原生 Excel 格式是一个不错的选择。上面的讨论可能看起来有点夸张，但我的客户过去确实遇到了这些问题，并且一直
让他们感到沮丧。因此，我们决定使用 Excel 作为输出和输入数据格式。这对我的客户来说效果很好，因为他们熟悉在 Excel 中操作数据，并且我能够使用在 OpenOffice 中创建的电子表格进行测试。
唯一剩下的问题是确保我们都同意哪些列包含哪些数据字段。我们的程序采取了一些额外的步骤来确保这个问题消失。
对于本文，我构思了一个场景，让我能够演示 Spreadsheet::ParseExcel Perl 模块的许多功能。
在这个例子中，我们有一群人去学校收集关于孩子的数据。我们正在收集全名和电话号码。我们还想知道他们是否有 A 等平均分或 F 等平均分。我们还希望能够按学校分隔数据。对于那些平均分为 A 等的孩子，我们将他们的名字颜色更改为绿色；对于那些平均分为 F 等的孩子，我们将他们的名字设置为红色。此外，我们正在为每所学校创建一个新的工作表，并以孩子就读的学校命名工作表。显然，这种方法不是很有效，但正如我所说，这是一个人为设计的例子，旨在演示模块的各个部分。
本演示的重点是从电子表格中获取原始数据，获取每个
电子表格中的工作表名称，并收集关于每个单元格中格式的信息。一旦你了解了所有这些是如何完成的，你就可以使用该模块来做任何你可能需要做的事情。
但首先，我们需要一个数据库来存储结果。我们只需要一个表来存储我们的数据，这就是它
drop table children;
create table children (
name varchar(50),
phone char(11),
school varchar(50),
code char(6)
);
我知道，使用 varchar(50) 来存储孩子的姓名和学校可能有点过分了。此外，你会注意到我在代码字段中使用了 char(6)。代码字段将以 RRGGBB 十六进制格式存储来自姓名字段的颜色信息。一旦数据进入数据库，稍微美化一下就不用费太多力气了。
现在我们已经定义了数据库表，我们需要一个 Web 表单，以便我们可以上传我们的数据。这个简单的
表单将完成这项工作
<html>
<head>
<title>数据上传页面</title>
</head>
<body>
上传您的数据
<form name=main method=POST action=/cgi-bin/load.pl ENCTYPE="multipart/form-data">
<input type=file name=file>
<input type=submit>
</form>
</body>
</html>
现在我们所做的就是将此文件保存到我们 Web 服务器的 webspace 中的 html 文件中。当我们的数据收集器将他们的浏览器指向结果 URL 时，他们将能够将他们的 Excel 数据上传到我们的数据库中。
接下来我们需要一些数据。看看图 1 和图 2。
嗯，一切看起来都不错……除了有人搞砸了。学校 A 的列与学校 B 的列顺序不同。我们的程序处理这个问题比让用户一致地创建文档更容易。从这里，我们可以看到我们有两所学校的数据，学校 A 和学校 B。我们还可以看到我们的一名学生是 A 等学生，一名是 F 等学生。其余的可能介于两者之间。
好吧，让我们最后看看程序。
并不是说这个程序是火箭科学，我将把它分解成几部分并依次讨论。
首先是样板代码
==================================================
#!/usr/bin/perl
use DBI;
use CGI;
use Spreadsheet::ParseExcel;
$cgi = new CGI;
$dbh = DBI->connect("dbi:Pg:dbname=test;host=db.example.com", "postgres", "password") ||
die "Can't connect to database.\n";
$file = $cgi->param("file");
$workbook = Spreadsheet::ParseExcel::Workbook->Parse($cgi->param("file"));
==================================================
正如你所看到的，这与我之前讨论的程序几乎相同。我没有使用 perl 的 “-w” 或 “use strict” 功能，因为一旦这个程序工作并产生正确的输出，我就对我的所有变量是否都已初始化等不感兴趣；我感兴趣的是程序的结果。
在这里，我们创建一个新的 CGI 对象以及一个 DBI 和 Spreadsheet::ParseExcel 对象。
在本例中，我们将来自 CGI 对象的文件句柄传递给 Spreadsheet::ParseExcel 构造函数进行解析。
更进一步
==================================================
foreach $sheet (@{$workbook->{Worksheet}}) {
foreach $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {
if ($sheet->{Cells}[0][$col]->{Val} eq "Name") {
$name = $col;
}
if ($sheet->{Cells}[0][$col]->{Val} eq "Phone") {
$phone = $col;
}
}
==================================================
在这段代码块中，我们开始循环遍历工作簿中的每个工作表。然后，对于每个工作表，我们确定我们的数据收集器使用的列范围，并开始查看我们的列标题。我们试图找出哪些列包含姓名和电话字段。一旦我们知道我们的列是如何组织的，我们就准备开始解析数据了。
==================================================
foreach $row ($sheet->{MinRow}+1 .. $sheet->{MaxRow}) {
$child_name = $sheet->{Cells}[$row][$name]->{Val};
$child_phone = $sheet->{Cells}[$row][$phone]->{Val};
$child_school = $sheet->{Name};
$child_code = Spreadsheet::ParseExcel->ColorIdxToRGB(
$sheet->{Cells}[$row][$name]->{Format}->{Font}->{Color});
==================================================
在这个代码块中，我们循环遍历每一行，就像我们之前循环遍历每一列一样。只是这次，我们跳过包含标题的行。然后我们使用 $sheet 对象来获取 child_name 和 child_phone 字段的单元格值。然后我们从每个工作表的名称中获取学校名称。
最后，我们从 name 列中获取一些格式信息。
==================================================
$dbh->do("insert into children (name,phone,school,code) values
(\'$child_name\', \'$child_phone\',
\'$child_school\', \'$child_code\')");
}
}
==================================================
接下来，我们将数据插入到数据库中。
==================================================
print $cgi->header();
print <<EOF
<html>
<head>
<title>文件已上传>
</head>
<body>
谢谢。
</body>
</html>
EOF
;
exit;
==================================================
最后，我们告诉用户我们已经获取了他们上传的信息并将其插入到数据库中。这里没有什么特别花哨的东西。事实上，在现实世界中，收集和显示一些摘要数据来表明所有数据都已插入，并指示是否检测到任何错误，这将是有意义的。但对于我们的示例，这已经足够好了。
现在示例数据文件已上传，我们可以要求我们的数据库向我们展示它有什么
查询：select * from children;
结果是
name | phone | school | code ------------+-------------+----------+-------- John Smith | 5551234 | School A | 000000 Sam Adams | 5554321 | School A | 00FF00 Jane Doe | 5550000 | School A | FF0000 Mike D | 5552222 | School B | 000000 John A | 5559999 | School B | 000000 (5 rows)
这正是我们所期望的。
因此，通过使用 Spreadsheet::ParseExcel Perl 模块，我们消除了用户尝试以错误格式导入数据的可能性，并且我们让他们可以轻松地在他们感觉最舒适的任何程序中操作他们的数据，同时仍然确保我们最终可以将结果数据放入我们的 SQL 数据库中。
这是一个解决人为设计问题的示例程序，但我希望它演示了在 Perl 中处理 Excel 文件有多么容易。
