使用perl将csv文件转换为多选项卡xls (excel)文件

时间:2021-05-05 14:04:05

I am using perl module WriteExcel to convert a | delimited text file into xls file, I am using below code to do so

我正在使用perl模块WriteExcel将一个带分隔符的|文本文件转换为xls文件,我使用下面的代码来实现这一点

#!/usr/bin/perl -w
use strict;
use Spreadsheet::WriteExcel;

# Create a new workbook and add a worksheet
my $workbook  = Spreadsheet::WriteExcel->new($filename);
my $worksheet = $workbook->add_worksheet("Colorful Example");

open(FH,"<$my_path/source_file.txt")
   or die "Cannot open file: $!\n";
my ($x,$y) = (0,0);
while (<FH>){
   chomp;
   my @list = split /\t/,$_;
   foreach my $c (@list){
      $worksheet->write($x, $y++, $c);
   }
   $x++; $y=0;
}
close(FH);
$workbook->close();     # Close Workbook

By this code i can convert the file into single tab excel. I am wondering how can i convert the text file into multi tab xls (excel file having multiple worksheets) file when number of rows exceed 65000.

通过这段代码,我可以将文件转换为单个选项卡excel。我想知道,当行数超过65000时,如何将文本文件转换为多选项卡xls(具有多个工作表的excel文件)文件。

1 个解决方案

#1


1  

You are adding your worksheet with this line.

您正在用这一行添加工作表。

my $worksheet = $workbook->add_worksheet("Colorful Example");

What you need to do is check in your loop where you process lines if you have exceeded the line limit, and if you did, replace the worksheet handle.

您需要做的是在循环中检查,如果超过了行限制,就在循环中处理行,如果超过了行限制,就替换工作表句柄。

$worksheet = $workbook->add_worksheet('foo') if $rows > 65_000;

#1


1  

You are adding your worksheet with this line.

您正在用这一行添加工作表。

my $worksheet = $workbook->add_worksheet("Colorful Example");

What you need to do is check in your loop where you process lines if you have exceeded the line limit, and if you did, replace the worksheet handle.

您需要做的是在循环中检查,如果超过了行限制,就在循环中处理行,如果超过了行限制,就替换工作表句柄。

$worksheet = $workbook->add_worksheet('foo') if $rows > 65_000;