perl 提取方括号中数据
I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green highlighting), Google Docs links confound (potentially require the recall and entry of a password) and Business Object, Crystal, iReports and the like all require some thought and action by the recipient (a non starter).
多年来，我一直困扰于生产和分发常规数据摘录，而且经常有要求明确要求将数据作为Excel附件通过电子邮件发送； 看起来特别是Excel：CSV文件混乱（没有红色或绿色突出显示），Google文档链接混乱（可能需要重新输入和输入密码），而Business Object，Crystal，iReports等都需要一些思考和行动，收件人（非入门者）。
Anyway as the source of the data has generally been a remote Oracle / MySQL / DB2 / Mongo... DB, and the requirements generally included a statement mandating the extract be available first thing Monday morning ,the obvious action would be to write and schedule (crontab) a script to:
无论如何，由于数据源通常是远程Oracle / MySQL / DB2 / Mongo ... DB，并且要求通常包括声明要求摘录在星期一早晨首先可用的声明，因此显而易见的动作是编写和计划（crontab）脚本以：
Connect to a DB
连接到数据库
Submit a query or two to Extract some Data
提交一两个查询以提取一些数据
Apply a bit of logic to reformat, add a few formula, filters, graphs and colour (Senior managers like Green)
应用一些逻辑来重新格式化，添加一些公式，过滤器，图表和颜色（像Green这样的高级管理人员）
Output the results as an Excel sheet
将结果输出为Excel工作表
Encode and Email the sheet (as if you'd been in all weekend) to one ore more recipients.
将工作表编码并通过电子邮件发送给您（就像您整个周末一样）给一个或多个收件人。
...and all while you're still seeking your morning caffeine hit.
...以及在您仍在寻找早晨咖啡因的所有时段。
The above actions can be performed reasonably easily in any of the half dozen languages you'll likely find on the average (non-hardened) Unix box: Perl (Practical Extraction and Reporting Language), PHP, Java, Python...... but as the purpose of this script resembles one of the languages acronym's I'll attempt to scribble down a few hints / code snippets to get you started in Perl.
可以使用您可能在平均（非强化）Unix机器上找到的六种语言中的任何一种，轻松地轻松地执行上述操作：Perl（实用提取和报告语言），PHP，Java，Python .....但是，由于该脚本的目的类似于一种语言的缩写，我将尝试写下一些提示/代码段以帮助您开始使用Perl。
Assuming your Server / PC / Mac has the applicable developer tools installed, along with access to your database server, and to an open email relay, you'll first want to pull down, from CPAN (ftp.perl.org - make sure any firewall's open), the necessary libraries to connect to a Database, create an Excel workbook and possibly encode a workbook.
假设您的Server / PC / Mac安装了适用的开发人员工具，以及对数据库服务器和开放式电子邮件中继的访问权，那么您首先要从CPAN（ ftp.perl.org-确保任何防火墙），连接到数据库，创建Excel工作簿以及可能对工作簿进行编码所需的库。
The following one liner should more than suffice:
以下一根衬板就足够了：
cpan YAML Data::Dumper Spreadsheet::WriteExcel Tie::IxHash Encode Scalar::Util DBI DBD::mysql JSON MongoDB File::Basename Excel::Writer::XLSX IO::File MIME::Base64 Encode::Encoding
Note:
注意事项 ：
Ideally run the above as root (sudo)
理想情况下，以超级用户（sudo）身份运行以上命令
Alter the DBI::xxxxxxx include to suite.
将DBI :: xxxxxxx包含更改为套件。
Generally just hit enter when prompted
通常在提示时按回车
You'll loose half an hour, or so, of your life while the above download / compile / install.
在进行上述下载/编译/安装时，您将失去半个小时左右的生命。
Next you'll want a few lines of code, ideally stuck in a separate package (file), to establish and return a connection to a DB. As this script is likely to contain some hard coded credentials, so make sure the package is ONLY accessible to applicable / calling user(s) , also externalising the DB connection code encourages re-use, and possibly prevents the proliferation of DB credentials:
接下来，您将需要几行代码，最好将其卡在单独的程序包（文件）中，以建立并返回与数据库的连接。 由于此脚本可能包含一些硬编码的凭据，因此请确保只有适用的/调用用户可以访问该程序包，并且将数据库连接代码外部化会鼓励重用，并可能防止数据库凭据的扩散：
myConnectDB.pm:
myConnectDB.pm ：
# MySQL database information
#
use strict;
use DBI;
package myConnectDB;
# Assign a variable, as the body must do something in lue of a proper constructor.
our $someVarName="myConnectDB";
return 1; # True
#-------------------------------------------------------------------------------
# Return a connection to database
#-------------------------------------------------------------------------------
sub db_connect {
# Default - Localhost
my $db="testDatabase";
my $host="127.0.0.1";
my $port="3306";
my $userid="someUser";
my $passwd="somePassword";
#
my $connectionInfo="DBI:mysql:database=$db;$host:$port";
#
if ( @_ == 3 ) {
($connectionInfo,$userid,$passwd) = @_;
}
# database information
my $dbh = DBI->connect($connectionInfo,$userid,$passwd)
or die "Couldn't connect to database: " . DBI->errstr;
return $dbh;
}
# #### END ####
Now we have some mechanism of connecting to the database we'll want some logic to create an Excel workbook. Again as your more likely than not to be asked to produce another / similar extract it is best to stick this and any other reusable / associated code in a separate package. So below is some fairly general purpose, but rather messy package stub that includes enough sample code to style and write out and order some or all of the columns in a passed data object, and either XLS or XLSX format workbook. Please note you'll need to go down the XLSX route if your extract has > 64K and < 2M rows.
现在我们有了一些连接数据库的机制，我们需要一些逻辑来创建Excel工作簿。 同样，由于很可能要求您生成另一个/类似的摘录，因此最好将此代码和任何其他可重用/关联的代码粘贴在单独的程序包中。 因此，以下是一些相当通用的用途，但它的凌乱性很强，它包含了足够的示例代码，可用来对传递的数据对象以及XLS或XLSX格式的工作簿中的部分或全部列进行样式设置，写出和排序。 请注意，如果提取的行数> 64K和<2M，则需要沿着XLSX路线行驶。
The required functionality is provided by a simple call to myExcelLib::convertToExcel
只需调用myExcelLib :: convertToExcel ， 即可提供所需的功能。 （）希望有一些合理的自我解释参数：
$sFilename - a name for the generated Excel workbook.
$ sFilename-生成的Excel工作簿的名称。
$oJsonData - a JSON style object, containing an ARRAY of HASHs (ROWS), with each HASH / containing a series of KEY (Column Name) / VALUE pairs (The VALUE can be a simple scalar / Boolean / timestamp VALUE or a HASH supplying a value and optional styling information).
$ oJsonData-一个JSON样式对象，包含一个HASH（ROWS）数组，每个HASH /包含一系列KEY（列名）/ VALUE对（VALUE可以是简单的标量/布尔值/时间戳VALUE或HASH提供值和可选的样式信息）。
$raOutFields - an Array listing the Keys / Columns, from the JSON Document that are to appear in the Sheet. The columns are added to the sheet in the order they appear in the array.
$ raOutFields-一个数组，列出要在工作表中显示的JSON文档中的键/列。 列以它们在数组中出现的顺序添加到工作表中。
$sFileFormat - Either XLS or XLSX
$ sFileFormat-XLS或XLSX
myExcelLib.pm
myExcelLib.pm
#
# Purpose: Assorted Excel related functions
#
use strict;
use Spreadsheet::WriteExcel;
use Excel::Writer::XLSX;
use Tie::IxHash;
use Scalar::Util 'reftype';
use JSON;
use File::Basename;
use Data::Dumper;
use IO::File;
package myExcelLib;
sub getValue;
my $myExcelLib = 1;
return 1; # True
# --------------------------------------------
sub base64_encode_file {
# see: http://perl.find-info.ru/perl/003/base64.htm (have been using this)
# alternatively: http://perldoc.perl.org/Encode/Encoder.html#Example%3a-base64-transcoder
# Or: http://perldoc.perl.org/MIME/Base64.html
}
# --------------------------------------------
sub getValue{
my ($sKey, $rhDoc,$bReturnRef)=@_;
my ($sKeyPt1,$sKeyPt2,$sKeyPt3,$refType);
$refType="";
if ( ($sKey =~ /^[^.]*[.]/) ){
($sKeyPt1, $sKeyPt2) = ($sKey =~ /^([^.]+)\.(.+)$/ );
$refType = reftype($rhDoc->{$sKeyPt1});
if ( $refType && $refType eq 'HASH' ) {
return getValue($sKeyPt2, $rhDoc->{$sKeyPt1});
}
elsif ( $refType && $refType eq 'ARRAY' ) {
if ( ($sKeyPt2 =~ /^[0-9]+$/) ){
return ($rhDoc->{$sKeyPt1}[$sKeyPt2]) if ( isNotARef($rhDoc->{$sKeyPt1}[$sKeyPt2]));
} else {
if ( ($sKeyPt2 =~ /^[0-9]+[.]/) ){
($sKeyPt2, $sKeyPt3) = ($sKeyPt2 =~ /^([^.]+)\.(.+)$/ );
return getValue($sKeyPt3, $rhDoc->{$sKeyPt1}[$sKeyPt2]);
} else { #Default to first element of ARRAY, if no index given
return getValue($sKeyPt2, $rhDoc->{$sKeyPt1}[0]);
}
}
}
} else {
if ( ref($rhDoc->{$sKey}) eq "DateTime") {
my $dt = $rhDoc->{$sKey};
$dt->set_time_zone('UTC');
return ($dt->datetime);
}
return $rhDoc->{$sKey} if ( $bReturnRef || isNotARef($rhDoc->{$sKey}));
}
return ("");
} ## End sub
# --------------------------------------------
sub isNotARef($){
my ($refToTest)=@_;
if( ! ref($refToTest) || ref($refToTest) eq 'boolean'){
return 1;
}
return 0;
}
# --------------------------------------------
sub convertToExcel($$$$){
my ($sFilename, $oJsonData, $raOutFields, $sFileFormat)=@_;
my ($iRow,$iCol,$rhDoc,$oTitleFormat,$oTotalFormat,$oTotalPCTFormat,$oPCTFormat,$sKey,$workbook,$sVal,$oFormat);
# Create a new workbook and add a worksheet.
$sFileFormat = 'xls' if ( ! $sFileFormat );
if ( $sFileFormat =~ /^xlsx$/i ){
$workbook = Excel::Writer::XLSX->new("$sFilename") || die ("Couldn't create: $sFilename");
}
elsif ( $sFileFormat =~ /^xls$/i ){
$workbook = Spreadsheet::WriteExcel->new("$sFilename") || die ("Couldn't create: $sFilename");
}
else {
die ("Error: Unknown file format");
}
my $worksheet = $workbook->add_worksheet();
# Define a few commonly used pre-set Formats /Styles that can be applied by NAME
# Title format
$oTitleFormat = $workbook->add_format(
bg_color => 12,
color => 22,
pattern => 1,
border => 1,
bold => 1
);
$oTotalFormat = $workbook->add_format(
bold => 1,
top => 1,
num_format => '#,##0'
);
$oTotalPCTFormat = $workbook->add_format(
bold => 1,
top => 1,
num_format => 10
);
$oPCTFormat = $workbook->add_format(
num_format => 10
);
#Write the Column headings
$iCol=0;
$iRow=0;
foreach $sKey ( @{$raOutFields} ) {
$worksheet->write($iRow, $iCol++, $sKey, $oTitleFormat);
}
#Freeze the first Row
$worksheet->freeze_panes(1, 0);
#Write any passed JSON Data
if ($oJsonData){
foreach $rhDoc ( @{$oJsonData} ) {
$iRow++;
$iCol=0;
foreach $sKey ( @{$raOutFields} ) {
$sVal = getValue($sKey, $rhDoc, 'true');
if ( ref($sVal) eq 'HASH' ) {
$oFormat = undef;
if ( $sVal->{ 'format' } ) {
if ( ref($sVal->{ 'format' }) eq 'HASH') {
$oFormat = $workbook->add_format($sVal->{ 'format' });
} elsif ( $sVal->{ 'format' } eq 'Total' ) {
$oFormat = $oTotalFormat;
} elsif ( $sVal->{ 'format' } eq 'TotalPCT' ) {
$oFormat = $oTotalPCTFormat;
} elsif ( $sVal->{ 'format' } eq 'PCT' ) {
$oFormat = $oPCTFormat;
}
}
$worksheet->write($iRow, $iCol, $sVal->{ 'value' }, $oFormat );
} else {
$worksheet->write($iRow, $iCol, $sVal);
}
$iCol++;
} # End - foreach $sKey
} #End Foreach row (JSON object)
}
return $iRow;
} ## End sub
Note: The above code has been cannibalised / butchered from a larger package I created some time ago, it's not complete, but the links will point you at a code snippet you can use to complete the Base64 encode logic / re-write. I have also cut out a load of other named Styles I personally use for my own purposes (left just enough to provide a few ideas). If you want to add you own names styles the spreadsheet::WriteExcel page provides plenty of info and examples. I have also omitted a few lines of code that walk the passed JSON style object, for all keys when $raOutFields is null.
注意：上面的代码已经从我前一段时间创建的一个更大的程序包中被蚕食/插入，它还不完整，但是链接会将您指向一个代码段，您可以使用该代码段来完成Base64编码逻辑/重新编写。 我还删除了我个人用于自己目的的其他命名样式的负载（仅提供一些想法）。 如果要添加自己的姓名样式， 电子表格:: WriteExcel页面提供了大量信息和示例。 对于$ raOutFields为null的所有键，我也省略了几行代码来传递传递的JSON样式对象。
As we now have a mechanism to connect to a Database and to convert a Data object into an Excel workbook it's time to glue the two together by creating the Calling / Reporting script.
现在我们有了一种连接数据库并将数据对象转换为Excel工作簿的机制，是时候通过创建“调用/报告”脚本将两者粘合在一起了。
The code below I hope is fairly self explanatory / vanilla, with the possible exception of the: get_report_info() function. Essentially this function takes a passed DB connection object and returns an Array containing the results from running one or more queries against a database, along with any local post processing, additional data or formula and styling you require. Each member of the returned array represents one Row of results.
我希望下面的代码是可以自我解释的/香草的，但可能有以下例外：get_report_info（）函数。 本质上，此函数采用传递的DB连接对象，并返回一个Array，其中包含对数据库运行一个或多个查询的结果以及所需的任何本地后期处理，其他数据或公式和样式。 返回数组的每个成员代表一个结果行。
The Rows themselves are encoded as a Hash, with the Column names as the Keys and the Value being either:
行本身被编码为哈希，列名作为键，值分别为：
A simple scalar value, probably returned from the database
一个简单的标量值，可能是从数据库返回的
An Excel formula string e.g.
Excel公式字符串，例如
=SUM(A2:A10) = SUM（A2：A10）A HASH containing two keys:
包含两个密钥的哈希：
2) "format" - Either the Name of a locally predefined style or a Hash defining the styling elements, see spreadsheet::WriteExcel
2）“格式”-本地预定义样式的名称或定义样式元素的哈希值，请参见电子表格:: WriteExcel
Once the results have been converted to an Excel workbook we simply Base64 / mime encode the file and send it as an attachment to a predefined distribution list.
将结果转换为Excel工作簿后，我们只需对Base64 / mime进行编码，然后将其作为附件发送到预定义的分发列表。
generateSomeReport.pl
generateSomeReport.pl
#!/usr/bin/perl -w
# Purpose: Example - Query a Database, play with then output, write to an Excel sheet, encode and email
use strict;
use DBI;
use myConnectDB;
use myExcelLib;
sub mail_monthly_report($);
# make a connection to the database DB
my $dbh = myConnectDB::db_connect();
print mail_report($dbh)."\n\n";
# disconnect from database
$dbh->disconnect;
exit;
# -------------------------------------------
# Retrieve the data for the report
# -------------------------------------------
sub get_report_info($) {
my ($dbh) = @_;
my ($sQuery, $sth, @aResults, $rhDoc, $iRow);
my ($dbSummary_seller, $dbSummary_producttype, $dbSummary_completed, $dbSummary_suspended, $dbSummary_cancelled, $dbSummary_count);
#
# PSEUDO SQL - Replace with something sensible
#
# prepare and execute query
$sQuery = "SELECT l.name as 'Seller',
l.productType,
IF((s.cancelled <> 0), 'N', s.completed) as Completed,
s.suspended,
IF((s.cancelled <> 0), 'Y', 'N') as Cancelled,
count(5)
FROM sales s,
login l
WHERE s.Seller = l.id
GROUP BY Seller,
s.productType,
Completed,
Cancelled,
s.suspended
ORDER BY Seller,
s.productType,
Completed,
Cancelled,
s.suspended";
$sth = $dbh->prepare($sQuery)
or die "Couldn't prepare statement: " . $dbh->errstr;
$sth->execute()
or die "Couldn't execute statement: " . $sth->errstr;
# assign fields to variables
$sth->bind_columns(undef,
\$dbSummary_seller,
\$dbSummary_producttype,
\$dbSummary_completed,
\$dbSummary_suspended,
\$dbSummary_cancelled,
\$dbSummary_count
);
#Make sure we have a a few numbers.
if ($sth->rows > 0) {
$iRow=1;
# output member list to browser as drop-down listings
while($sth->fetch()) {
$iRow++;
$rhDoc = undef;
$rhDoc->{ 'Seller' } = $dbSummary_seller;
$rhDoc->{ 'Product Type' } = $dbSummary_producttype;
$rhDoc->{ 'Completed' } = $dbSummary_completed;
$rhDoc->{ 'Suspended' } = $dbSummary_suspended;
$rhDoc->{ 'Cancelled' } = $dbSummary_cancelled;
$rhDoc->{ 'Product Count' } = $dbSummary_count;
#
# Add a formula to calculate the Percentage of the total
#
$rhDoc->{ 'Percentage Of All' }->{ 'value' } = "=(F$iRow/F".($sth->rows + 2).")";
$rhDoc->{ 'Percentage Of All' }->{ 'format' } = 'PCT';
push(@aResults,$rhDoc);
}
$sth->finish();
#
# Add a Styles Column Summary / Total Row to the Doc
#
$rhDoc = undef;
$rhDoc->{ 'Product Count' }->{ 'value' } = "=SUM(F2:F$iRow)";
$rhDoc->{ 'Product Count' }->{ 'format' } = 'Total';
$rhDoc->{ 'Percentage Of All' }->{ 'value' } = "=SUM(G2:G$iRow)";
$rhDoc->{ 'Percentage Of All' }->{ 'format' } = 'TotalPCT';
push(@aResults,$rhDoc);
return \@aResults;
}
return undef
}
# -------------------------------------------
# Extract and Email a file
# -------------------------------------------
sub mail_report($) {
my $raResults = get_report_info($_[0]);
if ($raResults) {
#
my $sFileName = "Some_Report.xls";
my $sTmpDir = '/tmp/';
my $sOriginator = "doNotReply\@some.host.com";
my $sRecipient = "some.user\@some.host.com";
my $sMessage = "\n\nSome report detailing some random statistics:\n\n";
#
my $sBoundary = "====" . time() . "====";
my $sendmail = "/usr/sbin/sendmail -t";
my $reply_to = "Reply-to: $sOriginator\n";
my $subject = "Subject: $sMessage\n";
my $send_to = "To: $sRecipient\n";
my $cc = "Cc: $sOriginator\n";
my $raOutFields = ['Seller', 'Product Type', 'Completed', 'Suspended', 'Cancelled', 'Product Count','Percentage Of All'];
my $iRowsWritten = myExcelLib::convertToExcel ("$sTmpDir$sFileName", undef, $raResults, $raOutFields, 'xls');
open(DLFILE, "<$sTmpDir$sFileName") || Error('open', 'file');
my @fileholder = <DLFILE>;
close (DLFILE) || Error ('close', 'file');
open(SENDMAIL, "|$sendmail") or die "Cannot open $sendmail: $!";
print SENDMAIL $reply_to;
print SENDMAIL $send_to;
print SENDMAIL $cc;
print SENDMAIL $subject;
print SENDMAIL "Mime-Version: 1.0\n";
print SENDMAIL "Content-Type: multipart/mixed; boundary=\"$sBoundary\"\n";
print SENDMAIL "Content-Disposition: inline\n\n";
print SENDMAIL "--$sBoundary\n";
print SENDMAIL "Content-type: text/plain\n\n";
print SENDMAIL $sMessage;
print SENDMAIL "--$sBoundary\n";
print SENDMAIL "Content-Type: application/octet-stream; name=\"$sFileName\"\n";
print SENDMAIL "Content-Disposition: attachment; filename=\"$sFileName\"\n";
print SENDMAIL "Content-Transfer-Encoding: base64\n\n";
print SENDMAIL myExcelLib::base64_encode_file("$sTmpDir$sFileName")."\n";
print SENDMAIL "\n--$sBoundary--\n";
close(SENDMAIL);
# Delete the local copy of the Excel sheet
unlink("$sTmpDir$sFileName");
#
return "$sMessage - has been emailed\n";
}
return 'Error: Unable to send email report';
}
## End ###
Note: You'll obviously need to alter the: SQL query, email recipients, email originator, email subject, and message. You may also need to alter the location of the sendmail binary and the instantiation of $sTmpDir
注意：您显然需要更改：SQL查询，电子邮件收件人，电子邮件发起者，电子邮件主题和消息。 您可能还需要更改sendmail二进制文件的位置和$ sTmpDir的实例化
perl 提取方括号中数据
7055
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
