Using Spreadsheet:WriteExcel or whatever else I need

[bennierounder]

Hi guys

I'm wondering how i can insert a word into the top of an excel file using perl without messing with the format. I want to add a row at the top of the file and insert a word in the first row first column keeping same format, having dramas.

I have created a new file with the word i want in 0,0 then i want the other document to copy itself under this word. basically I just want everything to be a row lower than it is in the same format and then insert a word into the first row first column for encryption purposes. Can anyone help?

if ($WE_NEED_TO_DO_ENCRYPTION) {
    if ($ext =~ /XLS/i) {
      # open a new excel file insert the word 'encryted' on the 1st ro
+w, 1st column.
      my $workbook  = Spreadsheet::WriteExcel->new($report_type . '_' 
+. $time . $ext);
      my $worksheet = $workbook->add_worksheet();
      $worksheet->write(0, 0,  'encrypted');  
      # now copy report file into the new xsl file.
      # THIS IS WHERE I AM STUCK I NEED TO PUT THE REST OF ANOTHER FIL
+E HERE IN THE SAME FORMAT

[RMGir]

I can't help with your specific issue, but just yesterday I read this which points out that Spreadsheet::WriteExcel has been superseded by Excel::Write::XLSX.

I suggest starting with the new module and seeing if it implements any new features which might help...

[bennierounder]

Thanks it seems a good module, however it doesn't quite cater for my needs at this present time

Be nice if John McNamara was here to check this lol

[jmcnamara]

There is a Spreadsheet::ParseExcel::SaveParser module that allows you to re-write an Excel file but it doesn't always work very well and in this particular case it can't be used to shift the rows down.

So, you will probably have to use Spreadsheet::ParseExcel to read the old file and then use Spreadsheet::WriteExcel to write the new one with the rows shifted down.

The feasibility of that depends on the complexity of the input file.

--
John.

[Lotus1]

Another possibility is to use Win32::OLE. The link below tells how to first record a macro in Excel to do what needs to be done, then look at the VBA code and translate to Perl.

Use The Macro Recorder, Luke (Re: Using Win32::OLE and Excel - Tips and Tricks)

[stefbv]

The following might be a solution in the (unlikely) situation when the file is in the "ODF" format. Only tested with a file created with LibreOffice.

It uses the ODF::lpOD module developed by Jean-Marie Gouarné and requires at least Perl v5.10.

use strict;
use warnings;
use 5.010_000;

use ODF::lpOD;

my $file = shift;

my $doc = odf_document->get($file);

my $table = $doc->get_body->get_table();
$table->read_optimize(TRUE);

$table->add_row(number => 1, before => 0, empty => 'true');

$table->get_cell(0,0)->set_text("Bonjour le monde !");

$doc->save();

Regards, Stefan