csv,txt,excel文件之间的转换,perl脚本

Text::CSV

Spreadsheet::WriteExcel

Spreadsheet::ParseExcel

Spreadsheet::ParseExcel::FmtUnicode

Unicode::Map

execl2txt:

#!/usr/bin/perl

#===============================================================================

#

# FILE: excel2txt.pl

#

# USAGE: ./excel2txt.pl excel_filename txt_filename

#

# DESCRIPTION: Convert a Excel document to a text file

#

#===============================================================================

use strict;

use warnings;

use Spreadsheet::ParseExcel;

use Spreadsheet::ParseExcel::FmtUnicode;

unless($ARGV[0] && $ARGV[1])

{

die "Please enter the filename of Excel document.

Usage : excel2txt.pl [Excel filename] [Text filename]

For example : excel2txt.pl input.xls output.txt";

}

# Chinese support

my $code = "CP936";

my $oFmtJ = Spreadsheet::ParseExcel::FmtUnicode->new(Unicode_Map => $code);

my $oExcel = new Spreadsheet::ParseExcel;

my $doc = $oExcel->Parse($ARGV[0],$oFmtJ) or die "File open error:$!\n";

my $outfile = $ARGV[1];

my $worksheet = $doc->{Worksheet}[0];

my $text;

# Get the excel data

for(my $row_num = $worksheet->{MinRow};$row_num <= $worksheet->{MaxRow};$row_num++)

{

for(my $col_num = $worksheet->{MinCol};$col_num <= $worksheet->{MaxCol};$col_num++)

{

$text .= $worksheet->{Cells}[$row_num][$col_num]?$worksheet->{Cells}[$row_num][$col_num]->Value:"";

if($col_num != $worksheet->{MaxCol})

{

$text .= "\t"; # print tab

}

else

{

$text .= "\r\n"; # print <CR>

}

}

}

# Write to file

open FH,">$outfile";

print FH $text;

close FH;

print "done!\n";

txt2excel:

#!/usr/bin/perl

#===============================================================================

#

# FILE: txt2excel.pl

#

# USAGE: ./txt2excel.pl [txt filename] [excel filename]

#

# DESCRIPTION: Convert text file to excel

#===============================================================================

use strict;

use warnings;

use Unicode::Map;

use Spreadsheet::WriteExcel;

unless($ARGV[0] && $ARGV[1])

{

die " USAGE: ./txt2excel.pl [txt filename] [excel filename] \n ";

}

my $filename = $ARGV[1];

my $tmp_filename = $ARGV[0];

my $workbook = Spreadsheet::WriteExcel->new($filename);

my $worksheet = $workbook->add_worksheet();

# Chinese support

my $Map = new Unicode::Map("GB2312");

# Convert word to unicode

sub w2u

{

my $word = shift;

return $Map->to_unicode($word);

}

my $row = 0;

my $col = 0;

open FH,$tmp_filename or die"file open error.\n";

while(<FH>)

{

chomp;

$col = 0;

s/\s+//g;

my @array = split(";",$_);

foreach my $value (@array)

{

$worksheet->write_unicode($row,$col,w2u($value));

$col++;

}

$row++;

}

close FH;

print "done!";

#!/usr/bin/perl -W

#

# File: excel2csv.pl

# License: GPL-2

use strict;

use warnings;

use Spreadsheet::ParseExcel;

use Spreadsheet::ParseExcel::FmtUnicode;

my $local_enc = "CP936";

unless($ARGV[0] && $ARGV[1])

{

die "Please enter the filename of Excel document.

Usage : excel2csv.pl [Excel filename] [Csv filename]

For example : excel2csv.pl input.xls output.csv\n";

}

my $oFmtJ = Spreadsheet::ParseExcel::FmtUnicode->new(Unicode_Map => $local_enc);

my $excel = Spreadsheet::ParseExcel->new();

my $book = $excel->Parse($ARGV[0], $oFmtJ);

my $last_sheet = $book->{SheetCount} - 1;

my $filename = $ARGV[1];

open my $fh, ">", $filename

or die "Can open $filename to write: $!\n";

for my $worksheet ( @{ $book->{Worksheet} }[ 0 .. $last_sheet ] ) {

next

if not defined $worksheet->{MaxRow}

or not defined $worksheet->{MaxCol};

my @row = $worksheet->{MinRow} .. $worksheet->{MaxRow};

my @col = $worksheet->{MinCol} .. $worksheet->{MaxCol};

for my $row ( @{ $worksheet->{Cells} }[ @row ] ) {

my @cellvalue = map {

$_ = $_->Value() if ref $_;

$_ = '' if not defined $_;

$_;

} @$row[ @col ];

print $fh join(";", @cellvalue), "\n";

}

}

close $fh;

#!/usr/bin/perl -w

use strict;

use Text::CSV;

use Spreadsheet::WriteExcel;

die("Usage: $0 [input filename(s)]\n") if (scalar @ARGV == 0);

my $xl = Spreadsheet::WriteExcel->new('output.xls');

foreach my $filename (@ARGV) {

my $csv = Text::CSV->new;

my $wsname = $filename;

$wsname =~ s/.csv//g;

$wsname =~ s/-/ /g;

my $ws = $xl->add_worksheet($wsname);

my $row = 1;

open FH, $filename; while (<FH>) {

$csv->parse($_);

my @fields = $csv->fields;

$ws->write("A$row", \@fields);

$row++;

}; close FH;

}