my $parser_batch = &getparser($file_batch);
my $workbook_batch = $parser_batch->parse($file_batch);
my $h2p = Lingua::Han::PinYin->new(capitalize => 1);
if ( !defined $workbook_batch ) {
die $parser_batch->error(), ".\n";
}
#这是XXX等整理的实验记录文件,用这个文件做两件事:
#1. 将样本编号记下来,用来在sent2BGI.xlsx里面去找相应的其他信息。
#2. 根据本表中其他信息,如文库编号等,去找到对应的fq1,fq2文件,也就是原始数据路径
for my $worksheet($workbook_batch->worksheets() ){
my $sheetname = $worksheet->{'Name'};
my %merged_areas = ();
#记录合并的单元格
my $merged_areas = $worksheet->get_merged_areas();
&broadcast_merged_areas($merged_areas,\%merged_areas);
# $merged_areas 是一个数组引用,里面是每个元素又是一个4元素数组的引用(4个元素分别是,左上横坐标,左上纵坐标,右下横坐标,右下纵坐标)
# 写入的时候要合并单元格:Excel::Writer::XLSX
# merge_range( $first_row, $first_col, $last_row, $last_col, $token, $format)
my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();
foreach my $row($row_min .. $row_max) {
if($row == 0 ){#第一行
foreach my $col($col_min .. $col_max) {
my ($row_tmp, $col_tmp) = ($row, $col);
if(exists $merged_areas{($row,$col)}){
($row_tmp, $col_tmp) = @{$merged_areas{($row,$col)}};#如果是合并单元格的一部分,就用左上角的坐标
}
my $cell = $worksheet->get_cell( $row_tmp, $col_tmp);
my $value = (defined $cell)?$cell->value():"";
核心在于:
my $merged_areas = $worksheet->get_merged_areas();
核心在这里:
# @$merged_areas 中每个元素都是一个匿名数组
# 每个匿名数组的元素都是某个合并单元格的($row_min, $col_min, $row_max, $col_max)
sub broadcast_merged_areas{
my ($merged_areas,$hash_ref) = @_;
foreach my $area(@$merged_areas) {
my($row_min, $col_min, $row_max, $col_max) = @$area;
foreach my $row_tmp($row_min..$row_max) {
foreach my $col_tmp($col_min..$col_max) {
$$hash_ref{($row_tmp,$col_tmp)} = [$row_min, $col_min];
# $$hash_ref{($row_tmp,$col_tmp)} = ($row_min, $col_min);#这样写是错的
}
}
}
}