Re^3: Delete entire row without using Win32::OLE

[poj]

I also read about Spreadsheet::Read which cannot work with formatted values

What ?

my @row = Spreadsheet::Read::row ($book->[1], 3)
Get full row of formatted values (like $sheet->{A3} .. $sheet->{G3})

poj

[ravi45722]

Sorry poj I missed that. I moved much forward through your suggestions. But i want to EDIT a .xlsx now for that i selected Excel::Writer::XLSX its creating a new file but not editing. Later i used Spreadsheet::ParseExcel::SaveParser which editing .xls files but not .XLSX. What I have to do to edit my .xlsx file(without loosing previous data).

.

[Corion]

Please have a look at the documentation of Spreadsheet::ParseExcel::SaveParser. It does not "edit" your file, it loads and then overwrites your excel file. You will have to find out how to alter the data structure and then do that. I've already shown you approaches to that, but you seem to want to avoid these.

[ravi45722]

Corion I read that and done program with that. But its not "overwriting" my xlsx file.

# Open an existing file with SaveParser
my $parser   = Spreadsheet::ParseExcel::SaveParser->new();
my $template = $parser->Parse('B_MUM_dashboard_month_latency_corrected
+_FDB.XLS');

# Get the first worksheet.
my $sheet = $template->worksheet(0);
my $row  = $max_rows;
my $col  = 1;

while (my @row = $sth->fetchrow_array())
{
        foreach my $value (@row)
        {
        $sheet->AddCell( $row, $col, $value );
        ++$col;
    }
}
$template->SaveAs('B_MUM_dashboard_month_latency_corrected_FDB.XLS');

Its working very well for .xls files. But I want to "overwrite" .XLSX files

[poj]

How simple is your spreadsheet ? Does it have merged cells, borders, colors, formulae, graphics, etc ?. If it doesn't try this program which should create a copy.Then either adapt it for your update or explain some more the problem you have.

#!perl
use strict;
use Spreadsheet::ParseXLSX;
use Excel::Writer::XLSX;
use Data::Dump 'pp';

my $file = 'c://temp//Book1';
my @data=();
read_file($file.'.xlsx');
#pp \@data;
write_file($file.'_copied.xlsx');


sub read_file {
  my $file = shift;
  print "Reading $file\n";
  my $parser = Spreadsheet::ParseXLSX->new();
  my $wb1 = $parser->parse($file);
  if ( !defined $wb1 ) {
    die $parser->error(), ".\n";
  }

  for my $n ( 1 .. $wb1->worksheet_count() ) {
    my $ws = $wb1->worksheet($n-1);
    my ( $row_min, $row_max ) = $ws->row_range();
    my ( $col_min, $col_max ) = $ws->col_range();
    my @tmp = ();
    for my $row ( $row_min .. $row_max ) {
      for my $col ( $col_min .. $col_max ) {     
        my $cell = $ws->get_cell( $row, $col );
        next unless $cell;
        push @tmp,join "\t",$row,$col,$cell->value();
      }
    }
    my $name = $ws->get_name;
    print "Reading sheet $n $name\n";
    push @data,[$name,\@tmp];
  }
}

sub write_file {
  my $file = shift;
  print "Creating copy workbook $file\n";  
  
  my $wb2 = Excel::Writer::XLSX->new( $file );
  for my $n (1..@data){
    my $name = $data[$n-1][0];
    my $ar   = $data[$n-1][1];
    print "Adding sheet $n $name\n";
    my $ws = $wb2->add_worksheet($name); 
    for (@$ar){
      my ($row,$col,$value)  = split "\t",$_;
      $ws->write( $row, $col, $value );
    }
  }
  $wb2->close();
}

poj

[ravi45722]

poj I know reading this will bore you. But i cant upload my excel sheet here. I tried ur code. Its working well. But my problem is somewhat different. I am working on SDP data. I have a xlsx datasheet which created through "R" scripting. When I am trying to read the values in that sheet it returning the default value but not exact value.

When ur code runs it reverts like this

AA            AB    AC    AD    AE            AF     AG             AH
+    AI    AJ
0.7          SubScore    2    SubScore98            SubScore 98       
+    SubScore        
0.448840495    7.48        7.48    99.66021651    2.26    99.78083497 
+   2.26    97.33    97.33
0.584906759    7.48        7.48    99.66985331    2.26    99.79158604 
+   2.26    97.33    97.33
0.632038673    7.48        7.48    99.67817389    2.26    99.79316929 
+   2.26    97.33    97.33
0.599607733    7.48    #N/A    7.48    99.6643225    2.26    99.792901
+4    2.26    97.33    97.33

The values in the excel sheet

0.448840495    7.48        7.48    99.66021651    2.42    99.78083497 
+   2.45    99.34    99.34
0.584906759    6.93        7.48    99.66985331    2.42    99.79158604 
+   2.45    98.80    98.80
0.632038673    6.87        7.48    99.67817389    2.43    99.79316929 
+   2.45    98.69    98.69

The formulaes in the excel sheet

AA 0.448840495386917   
AB =IF(AA15<AA$13,IF(AA15<AA$12,AA$8,((AA15-AA$13)*(AA$8-AA$9)/(AA$12-
+AA$13))+AA$9),IF(AA15>AA$14,AA$10,AA$9-((AA$13-AA15)*(AA$9-AA$10)/(AA
+$13-AA$14))))
AC [EMPTY CELL]
AD =IF(AC15<AC$13,IF(AC15<AC$12,AC$8,((AC15-AC$13)*(AC$8-AC$9)/(AC$12-
+AC$13))+AC$9),IF(AC15>AC$14,AC$10,AC$9-((AC$13-AC15)*(AC$9-AC$10)/(AC
+$13-AC$14))))
AE 99.6602165102621
AF =IF(AE15>AE$13,IF(AE15>AE$12,AE$8,((AE15-AE$13)*(AE$8-AE$9)/(AE$12-
+AE$13))+AE$9),IF(AE15<AE$14,AE$10,AE$9-((AE$13-AE15)*(AE$9-AE$10)/(AE
+$13-AE$14))))
AG 99.7808349699302
AH =IF(AG15>AG$13,IF(AG15>AG$12,AG$8,((AG15-AG$13)*(AG$8-AG$9)/(AG$12-
+AG$13))+AG$9),IF(AG15<AG$14,AG$10,AG$9-((AG$13-AG15)*(AG$9-AG$10)/(AG
+$13-AG$14))))
AI =SUMIF($C$14:$AH$14,"SubScore",C15:AH15)    (I need to read this va
+lue. By default its 97.33. Exact value is 99.34)
AJ =AI15

if we read the cell->value it have to read it as 99.34 but its reading it as 97.33 (Default values occur through formulae)

Note: Suppose if you open the sheet and delete the last empty (Nothing in that rows. Saved with formulae for next days) rows or if you change any small change (editing any cell overall the sheet) and save it. Then its showing the exact value 99.34.

But i dont know how that other cell doing what here. So, I think to open the xlsx sheet and delete a row(just for check) from last of the sheet through perl and check it either it showing 99.34 or 97.33.