perl Spreadsheet::WriteExcel is not compatible with office and wps

[Pysion-lin]

Sorry because Spreadsheet::WriteExcel is not open issue.

When I use Spreadsheet::WriteExcel to operate xls, it appears that wps can normally display all the processed background colors and font colors, but office can not display normally, there are always some colors lost, may I ask why

I hope that friends who encounter the same problem can share the solution, thank you very much

[Pysion-lin]

my code is as follwos

my $parser   = Spreadsheet::ParseExcel->new();
my ($book,@sheets,$sheet);

# $status= (-e $inputFile) ? "$inputFile" : "not exists!";

$book = $parser->parse($inputFile);
@sheets = @{ $book->{Worksheet} };
$sheet =shift @sheets ;

my ($workbook,$worksheet);

eval{
$workbook  = Spreadsheet::WriteExcel->new($outFile);
$worksheet = $workbook->add_worksheet('genotype');
};
ROW_LOOP: for ($row = 0; $row < $sheet->{MaxRow} + 1; $row++){
    for ($col = 0; $col < $sheet->{MaxCol} + 1;$col++){
        my $cell = $sheet->get_cell($row, $col);
        next unless $cell;
        my $a = 'beta substitution';
        my $b = 'alpha substitution';
        my $c = 'deletionResults';
        if ($cell->value() gt $a or $cell->value() gt $b or $cell->value() gt $c){
            $worksheet->freeze_panes(1, 5);
            if ($col != 0){
                $worksheet->set_column($col, $col,15); # set Columns C-E width
            }
        }

        #On s'occupe du format des cellules
        my $format_w = $workbook->add_format();
        my $format_r = $cell->get_format();

        #Font
        if(my $font = $format_r->{Font}) {
            $format_w->set_font($font->{Name});
            $format_w->set_size($font->{Height});
            $format_w->set_color($font->{Color});
            $format_w->set_bold($font->{Bold});
            $format_w->set_italic($font->{Italic});
            $format_w->set_underline($font->{UnderlineStyle});
            $format_w->set_font_strikeout($font->{Strikeout});
            $format_w->set_font_script($font->{Super});
        }

        #Proctection
        $format_w->set_locked($format_r->{Lock});
        $format_w->set_hidden($format_r->{Hidden});

        #Alignment
        $format_w->set_align($format_r->{AlignH});
        $format_w->set_valign($format_r->{AlignV});
        $format_w->set_rotation($format_r->{Rotate});
        $format_w->set_text_wrap($format_r->{Wrap});
        $format_w->set_text_justlast($format_r->{JustLast});
        $format_w->set_shrink($format_r->{Shrink});

        #Pattern
        if(my $pattern = $format_r->{Fill}) {
            $format_w->set_pattern(@$pattern[0]);
            $format_w->set_fg_color(@$pattern[1]);
            $format_w->set_bg_color(@$pattern[2]);
        }

        #Border style
        if(my $borderStyle = $format_r->{BdrStyle}) {
            $format_w->set_left(@$borderStyle[0]);
            $format_w->set_right(@$borderStyle[1]);
            $format_w->set_top(@$borderStyle[2]);
            $format_w->set_bottom(@$borderStyle[3]);
        }

        #Border color
        if(my $borderColor = $format_r->{BdrColor}) {
            $format_w->set_left_color(@$borderColor[0]);
            $format_w->set_right_color(@$borderColor[1]);
            $format_w->set_top_color(@$borderColor[2]);
            $format_w->set_bottom_color(@$borderColor[3]);
        }   

        $worksheet->write($row, $col,$cell->value(),$format_w);
    }   

}

[jmcnamara]

You will need to include a small, complete example that demonstrates the issue. Otherwise there is no way to determine what the issue is.

[Pysion-lin]

You will need to include a small, complete example that demonstrates the issue. Otherwise there is no way to determine what the issue is.

this is my code:copy a file and process it before saving it

#!/usr/bin/perl
use strict;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
use Text::CSV_XS;
use File::DosGlob 'glob';
use Encode; 
use Encode::CN;
use Tk;
use File::Copy qw(move);
use Data::Dump qw(dump);

# input.xls:Files with multiple background colors and font color annotations
min_max("input.xls","output.xls");

sub min_max{

my ($inputFile1,$outFile1) = @_;
my $code_str = "euc-cn";
my $status;
$status= $outFile1.";".$inputFile1;

my $inputFile = encode($code_str,$inputFile1);  # my $csvFile2=encode("euc-cn",$csvFile)
my $outFile = encode($code_str,$outFile1);

my $parser   = Spreadsheet::ParseExcel->new();
my ($book,@sheets,$sheet);

# $status= (-e $inputFile) ? "$inputFile" : "not exists!";

$book = $parser->parse($inputFile);
@sheets = @{$book->{Worksheet}};
$sheet =shift @sheets ;

my ($workbook,$worksheet);

eval{
$workbook  = Spreadsheet::WriteExcel->new($outFile);
$worksheet = $workbook->add_worksheet('genotype');
};

sub min_max_index{
    my ($rows,$PN,$PNM,@NVS);
    my @indices;
    my ($sheet,$workbook,$worksheet,$start_col,$end_col,$offset_col,@p_items) =  @_;

    for ($PN=$start_col; $PN <= $end_col;$PN=$PN+$offset_col)
    {   
        my $header_cell = $sheet->get_cell(0, $PN);
        my $sample_col = 1;
        my @NV;
        push @NV,99999999;

        # print "\$max_row,\$max_col",$max_row,$max_col,"\n";
        my $flag_color=0;
        my $col_index = 0;
        for (my $i=0;$i < @p_items;$i++){
            my ($tmp_min,$tmp_max,$min_row,$min_col,$max_row,$max_col);
            $tmp_min = 99999999;
            $tmp_max = -99999999;
            for ($rows = 1; $rows < $sheet->{MaxRow} + 1; $rows++){
                my $sample_cell = $sheet->get_cell($rows, $sample_col);
                next if !defined $sample_cell || $rows == $sheet->{MaxRow}-1;
                my $sample = $sample_cell->value;

                if ($sample eq "NTC" or $sample eq "PC")
                {
                    push @NV,99999999;
                    next;
                }

                if ($sample =~ /\bCount\b/)
                {
                    #print "Count:",$sample,"\n";
                    last;
                }
                my $cell = $sheet->get_cell($rows, $PN + $col_index);
                next if !defined $cell || $rows == $sheet->{MaxRow}-1;

                my $background_color = $cell->get_format()->{Fill}->[1];

                if ($background_color eq "64"){
                    if ($cell->value < $tmp_min )
                    {
                        $tmp_min = $cell->value;
                        $min_row = $rows;
                        $min_col = $PN + $col_index;
                    }
                    if ($cell->value > $tmp_max )
                    {
                        $tmp_max = $cell->value;
                        $max_row = $rows;
                        $max_col = $PN + $col_index;
                    }
                }
            }
            if (@p_items[$col_index] eq "min"){
                push @indices,[$min_row,$min_col];
            }
            if (@p_items[$col_index] eq "max"){
                push @indices,[$max_row,$max_col];
            }
            $col_index++;
        }
    }

    return @indices
}
my @process_items = ("min","max","min");
my @ret_indices = min_max_index($sheet,$workbook,$worksheet,5,(3 * 20 + 5 - 1),3,@process_items);

my @process_items2 = ("max","max","max","max","max","max","min");
my @ret_indices2 = min_max_index($sheet,$workbook,$worksheet,65,65 + 7 - 1,7,@process_items2);
@ret_indices = (@ret_indices,@ret_indices2);

my ($row, $col);
ROW_LOOP: for ($row = 0; $row < $sheet->{MaxRow} + 1; $row++){
    for ($col = 0; $col < $sheet->{MaxCol} + 1;$col++){
        my $cell = $sheet->get_cell($row, $col);
        next unless $cell;
        my $a = 'beta substitution';
        my $b = 'alpha substitution';
        my $c = 'deletionResults';
        if ($cell->value() gt $a or $cell->value() gt $b or $cell->value() gt $c){
            $worksheet->freeze_panes(1, 5);
            if ($col != 0){
                $worksheet->set_column($col, $col,15); # set Columns C-E width
            }
        }

        #On s'occupe du format des cellules
        my $format_w = $workbook->add_format();
        my $format_r = $cell->get_format();

        #Font
        if(my $font = $format_r->{Font}) {
            #$format_w->set_font($font->{Name});
            #$format_w->set_size($font->{Height});

            my $tmp_fill=$format_r->{Fill}->[1];
            $format_w->set_bg_color($tmp_fill);

            for my $item (@ret_indices){
                if ($col == @$item[1] and $row == @$item[0]){
                    #print "A:@$item[0],@$item[1]\r\n"; 
                    $format_w->set_color("red");
                    last;
                }else{
                    $format_w->set_color($font->{Color});
                }
            }
            for my $item (@ret_indices2){
                if ($col == @$item[1] and $row == @$item[0]){
                    #print "A:@$item[0],@$item[1]\r\n"; 
                    $format_w->set_color("red");
                    last;
                }else{
                    $format_w->set_color($font->{Color});
                }
            }

            $format_w->set_bold($font->{Bold});
            #$format_w->set_italic($font->{Italic});
            #$format_w->set_underline($font->{UnderlineStyle});
            #$format_w->set_font_strikeout($font->{Strikeout});
            #$format_w->set_font_script($font->{Super});
        }

        #Proctection
        $format_w->set_locked($format_r->{Lock});
        $format_w->set_hidden($format_r->{Hidden});

        #Alignment
        $format_w->set_align($format_r->{AlignH});
        $format_w->set_valign($format_r->{AlignV});
        $format_w->set_rotation($format_r->{Rotate});
        $format_w->set_text_wrap($format_r->{Wrap});
        $format_w->set_text_justlast($format_r->{JustLast});
        $format_w->set_shrink($format_r->{Shrink});

        #Pattern
        if(my $pattern = $format_r->{Fill}) {
            $format_w->set_pattern(@$pattern[0]);
            $format_w->set_fg_color(@$pattern[1]);
            $format_w->set_bg_color(@$pattern[2]);
        }

        #Border style
        if(my $borderStyle = $format_r->{BdrStyle}) {
            $format_w->set_left(@$borderStyle[0]);
            $format_w->set_right(@$borderStyle[1]);
            $format_w->set_top(@$borderStyle[2]);
            $format_w->set_bottom(@$borderStyle[3]);
        }

        #Border color
        if(my $borderColor = $format_r->{BdrColor}) {
            $format_w->set_left_color(@$borderColor[0]);
            $format_w->set_right_color(@$borderColor[1]);
            $format_w->set_top_color(@$borderColor[2]);
            $format_w->set_bottom_color(@$borderColor[3]);
        }   

        $worksheet->write($row, $col,$cell->value,$format_w);
    }   

}

$workbook->close();
}

MainLoop;

[jmcnamara]

That isn't small and without the input.xls file it isn't a working example.

Please reduce this down to the minimum possible example that demonstrates the issue. It should only include Spreadsheet::WriteExcel code (not Spreadsheet::ParseExcel code) and it should produce a file with one cell that demonstrates the issue.

If there isn't an issue with that then introduce Spreadsheet::ParseExcel and create a similar small one cell example that demonstrates the issue.

[Pysion-lin]

That isn't small and without the input.xls file it isn't a working example.

Please reduce this down to the minimum possible example that demonstrates the issue. It should only include Spreadsheet::WriteExcel code (not Spreadsheet::ParseExcel code) and it should produce a file with one cell that demonstrates the issue.

If there isn't an issue with that then introduce Spreadsheet::ParseExcel and create a similar small one cell example that demonstrates the issue.

I'm sorry. This is a file.

input.xls

[Pysion-lin]

That isn't small and without the input.xls file it isn't a working example.

Please reduce this down to the minimum possible example that demonstrates the issue. It should only include Spreadsheet::WriteExcel code (not Spreadsheet::ParseExcel code) and it should produce a file with one cell that demonstrates the issue.

If there isn't an issue with that then introduce Spreadsheet::ParseExcel and create a similar small one cell example that demonstrates the issue.

My development environment

Perl version : 5.036003 OS name : MSWin32 Module versions: (not all are required) Spreadsheet::WriteExcel 2.40 Parse::RecDescent 1.967015 File::Temp 0.2311 OLE::Storage_Lite 0.22 IO::Stringy 2.113 Spreadsheet::ParseExcel 0.66 Scalar::Util 1.63 Unicode::Map (not installed)

[jmcnamara]

I think this may be an Excel issue. It may be related to too many formats in the file.

Anyway, older versions of Excel display the file as expected (see below), so it is unlikely to be a Spreadsheet::WriteExcel issue. Also, as you pointed out, it works in WPS so the issue seems to be in Excel:

Also, if you change from Spreadsheet::WriteExcel to Excel::Writer::XLSX the output is as expected:

#!/usr/bin/perl
use strict;
use Spreadsheet::ParseExcel;
# use Spreadsheet::WriteExcel; # 1. Remove this.
use Excel::Writer::XLSX;  # 2. Add this.
use Text::CSV_XS;
use File::DosGlob 'glob';
use Encode;
use Encode::CN;
#use Tk;
use File::Copy qw(move);
use Data::Dump qw(dump);

# input.xls:Files with multiple background colors and font color annotations
min_max( "input.xls", "output.xlsx" ); # 3. Change to .xlsx.

sub min_max {

    my ( $inputFile1, $outFile1 ) = @_;
    my $code_str = "euc-cn";
    my $status;
    $status = $outFile1 . ";" . $inputFile1;

    my $inputFile =
      encode( $code_str, $inputFile1 ); # my $csvFile2=encode("euc-cn",$csvFile)
    my $outFile = encode( $code_str, $outFile1 );

    my $parser = Spreadsheet::ParseExcel->new();
    my ( $book, @sheets, $sheet );

    # $status= (-e $inputFile) ? "$inputFile" : "not exists!";

    $book   = $parser->parse( $inputFile );
    @sheets = @{ $book->{Worksheet} };
    $sheet  = shift @sheets;

    my ( $workbook, $worksheet );

    eval {
        $workbook  = Excel::Writer::XLSX->new( $outFile ); # 4. Change this.
        $worksheet = $workbook->add_worksheet( 'genotype' );
    };

    # Everything else the same.

This also gives the expected output:

So overall I don't think this is a Spreadsheet::WriteExcel issue, and if it is, I don't update that module any more so it won't be fixed.

[Pysion-lin]

I think this may be an Excel issue. It may be related to too many formats in the file.

Anyway, older versions of Excel display the file as expected (see below), so it is unlikely to be a Spreadsheet::WriteExcel issue. Also, as you pointed out, it works in WPS so the issue seems to be in Excel:

Also, if you change from Spreadsheet::WriteExcel to Excel::Writer::XLSX the output is as expected:

#!/usr/bin/perl
use strict;
use Spreadsheet::ParseExcel;
# use Spreadsheet::WriteExcel; # 1. Remove this.
use Excel::Writer::XLSX;  # 2. Add this.
use Text::CSV_XS;
use File::DosGlob 'glob';
use Encode;
use Encode::CN;
#use Tk;
use File::Copy qw(move);
use Data::Dump qw(dump);

# input.xls:Files with multiple background colors and font color annotations
min_max( "input.xls", "output.xlsx" ); # 3. Change to .xlsx.

sub min_max {

    my ( $inputFile1, $outFile1 ) = @_;
    my $code_str = "euc-cn";
    my $status;
    $status = $outFile1 . ";" . $inputFile1;

    my $inputFile =
      encode( $code_str, $inputFile1 ); # my $csvFile2=encode("euc-cn",$csvFile)
    my $outFile = encode( $code_str, $outFile1 );

    my $parser = Spreadsheet::ParseExcel->new();
    my ( $book, @sheets, $sheet );

    # $status= (-e $inputFile) ? "$inputFile" : "not exists!";

    $book   = $parser->parse( $inputFile );
    @sheets = @{ $book->{Worksheet} };
    $sheet  = shift @sheets;

    my ( $workbook, $worksheet );

    eval {
        $workbook  = Excel::Writer::XLSX->new( $outFile ); # 4. Change this.
        $worksheet = $workbook->add_worksheet( 'genotype' );
    };

    # Everything else the same.

This also gives the expected output:

So overall I don't think this is a Spreadsheet::WriteExcel issue, and if it is, I don't update that module any more so it won't be fixed.

Thank you very much for your scheme. I will test it and share the test results with you