- 论坛徽章:
- 0
|
30可用积分
excel.rar
(5.01 MB, 下载次数: 8)
#!/usr/bin/perl -w
use strict;
use warnings;
use Encode;
use Data: umper;
use Spreadsheet: arseExcel;
use Spreadsheet: arseExcel::FmtUnicode;
use Spreadsheet::WriteExcel;
use Encode;
my $parser = new Spreadsheet: arseExcel;
my $oCode = "GB2312";
my $oFmtJ = Spreadsheet: arseExcel::FmtUnicode->new(Unicode_Map => $oCode);
# get all the catalogs from table Amber
my $amber_workbook = $parser-> arse('Amber Catalog 2013.xls', $oFmtJ);
my $amber_worksheet = $amber_workbook->worksheet('30801');
my @amber_catalog_array;
#Get all the catalog from table Amber Catalog 2013.xls
my ( $amber_row_min, $amber_row_max ) = $amber_worksheet->row_range();
for my $row ( ($amber_row_min + 1) .. $amber_row_max)
{
my $amber_catalog = $amber_worksheet->get_cell( $row, 1 );
next unless $amber_catalog;
$amber_catalog = $amber_catalog->unformatted();
push (@amber_catalog_array, $amber_catalog);
}
# Get all the datas from table Product Full_2014_03_02.xls which catalog no in the table Amber Catalog 2013.xls
$parser = new Spreadsheet: arseExcel;
my $full_workbook = $parser-> arse('Product_Full_2014_03_02.xls',$oFmtJ);
my $full_worksheet = $full_workbook->worksheet('Products');
my ( $product_row_min, $product_row_max ) = $full_worksheet->row_range();
my ( $product_col_min, $product_col_max ) = $full_worksheet->col_range();
my $workbook = Spreadsheet::WriteExcel->new('1.xls');
my $worksheet = $workbook->add_worksheet();
my $format = $workbook->add_format();
$format->set_bold();
$format->set_color('red');
$format->set_align('center');
for my $row ( $product_row_min .. $product_row_max ) {
my $product_catalog = $full_worksheet->get_cell( $row, 0);
next unless $product_catalog;
$product_catalog = $product_catalog->unformatted();
if( grep {$_ eq $product_catalog} @amber_catalog_array) {
next;
} else {
for my $new_col ( $product_col_min .. $product_col_max ) {
$product_catalog = $full_worksheet->get_cell( $row, $new_col);
unless ($product_catalog) {
print "\*";
next;
}
$product_catalog = $product_catalog->unformatted();
if($product_catalog =~ /[\x80-\xFF]{2}/) {
$worksheet->write($row, $new_col, decode('utf16-BE',$product_catalog));
} else {
$worksheet->write($row, $new_col, $product_catalog);
}
#print "$product_catalog";
}
print "\n";
}
}
这里有个问题,如果products中有中文字符或者特殊字符,比如汉字或者 全角的()或者阿尔法(a),贝塔(b),按照格式转换后一个cell中的所有字符均被转换了,请教大神们怎么处理?
|
最佳答案
查看完整内容
回复 3# dahe_1984 我在那个帖子回复你了…… 其实用EXCEL2007很爽,因为EXCEL2007采用UTF-8保存特殊字符,兼容ASCII不用考虑编码产生交集导致的辨别问题。以下代码只适用EXCEL2007生成b.xlsx的结果 另外read模块和write的模块在行和列的引用值上略有不同read模块从1开始算write模块的函数从0开始算。
|