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

Question

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

Pysion-lin opened this issue 4 months ago · 10 comments

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

Answer 1 · 2024-07-05T06:22:28.000Z

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);
	}	

}

Answer 2 · 2024-07-05T07:10:50.000Z

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

Answer 3 · 2024-07-05T07:31:02.000Z

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;

Answer 4 · 2024-07-05T08:06:45.000Z

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.

Answer 5 · 2024-07-05T08:19:45.000Z

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

Answer 6 · 2024-07-05T08:23:06.000Z

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)

Answer 7 · 2024-07-05T10:57:05.000Z

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.

Answer 8 · 2024-07-05T13:59:40.000Z

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

Answer 9 · 2024-07-09T02:03:13.000Z

Excel::Writer::XLSX

Hi, the Excel::Writer::XLSX solution you suggested really worked, thank you very much.

Answer 10 · 2024-07-09T02:03:55.000Z

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.

Hi, the Excel::Writer::XLSX solution you suggested really worked, thank you very much.