Perl学习15之perl读excel表格

"pythonic生物人"的第25篇分享。

---

摘要

详细介绍Perl如何读取excel数据并简单输出

正文开始啦

---

1、待读入excel表格test.xlsx

2、excel表格中cell，row，col介绍

---

3、perl读取并输出每个sheet的第1，2，8列

#! /usr/bin/perl
use strict;
use warnings;
use Spreadsheet::XLSX;

#读入文件
my $excel = Spreadsheet::XLSX -> new ('./test.xlsx');
foreach my $sheet (@{$excel -> {Worksheet}}) {#Worksheet存储每个excel的sheet
        printf("SheetName: %s\n", $sheet->{Name});#输出sheet名字
       #判断sheet是否为空，等价于$sheet -> {MaxRow}=$sheet -> {MinRow}||$sheet -> {MinRow}
       $sheet -> {MaxRow} ||= $sheet -> {MinRow};
           #foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) {#行row循环，一行一行读
        foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) {#行row循环，一行一行读
               $sheet -> {MaxCol} ||= $sheet -> {MinCol};#判断每列是否为空
                                       my $cell1 = $sheet -> {Cells} [$row] [1];#第1列格子
                                       my $cell2 = $sheet -> {Cells} [$row] [2];
                                       my $cell8 = $sheet -> {Cells} [$row] [8];
                                           printf("%s\t%s\t%s\n", $cell1 -> {Val}, $cell2 -> {Val}, $cell8 -> {Val});
                                           #$cell -> {Val}为cell值
               }
       }

输出结果：

4、官方例子，perl读取并输出每个非空的cell值

#use Text::Iconv;
#my $converter = Text::Iconv -> new ("utf-8", "windows-1251");

# Text::Iconv is not really required.
# This can be any object with the convert method. Or nothing.

use Spreadsheet::XLSX;
#读入文件
my $excel = Spreadsheet::XLSX -> new ('test.xlsx');
foreach my $sheet (@{$excel -> {Worksheet}}) {##每个sheet，Worksheet存储每个excel的sheet
        printf("SheetName: %s\n", $sheet->{Name});#输出sheet名字
       ##判断sheet是否为空，等价于$sheet -> {MaxRow}=$sheet -> {MinRow}||$sheet -> {MinRow}
       $sheet -> {MaxRow} ||= $sheet -> {MinRow};
        foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) {##每行，行row循环，一行一行读
               $sheet -> {MaxCol} ||= $sheet -> {MinCol};#判断每列是否为空
               foreach my $col ($sheet -> {MinCol} ..  $sheet -> {MaxCol}) {##每列，一列一列读，$col为列号
                       my $cell = $sheet -> {Cells} [$row] [$col];##cell每个格子
                       if ($cell) {
                                                   printf("( %s , %s ) => %s\n", $row, $col, $cell -> {Val});#$cell -> {Val}为格子值
                       }
               }
       }
}

输出结果：

同系列文章

持续更新，欢迎您"关注"、"在看"、"分享"