http://www.perlmonks.org?node_id=663660


in reply to How to use a formula in perl

Although I'm not entirely sure what you are trying to accomplish, as olus mentioned, you might want to use Spreadsheet::ParseExcel to read the files. However, you should look into Spreadsheet::WriteExcel if you want to generate an Excel formula and write out the Excel binary file format. However, please note that this module does not store results with the formula (in other words, it behaves as if autocalculate is off).

Replies are listed 'Best First'.
Re^2: How to use a formula in perl
by padawan_linuxero (Scribe) on Jan 22, 2008 at 20:01 UTC
    Hi sorry if it is a bot confusing, I have a script made in perl that generates a bunch of XLS files, but now I need to add some data on them but these data is store in another xls file call customs.xls, so I in excel create a formula using vlookup to get the data (just a customer number and the name of the customer) so I need to put the formula in the script and I just dont know how to do that add to a cell the formula and make the formula work. this is the formula that I need to put in my script but I have no Idea on how to do it
    =VLOOKUP(A9,PATENTES.xls!$A$2:$C$82,2,FALSO)
      Ah, I think I understand better now. You are getting the data from Excel files and you want to replicate the effect of =VLOOKUP(A9,PATENTES.xls!$A$2:$C$82,2,FALSO) on the data in a perl script.

      Rather than trying to sort out how VLOOKUP works on your worksheets, it would be much easier to help if you can post the perl code you are using to read the data, a small example of the data, and a description of the result you want. It seems like you may be able to read data into a hash using "customer number" as a key and the name as the value, but I'm just guessing at this point.

        Well actually the files are already xls files I just need to add format to the files to make them look better, one of those things is to add the customer name and the customer, that information is in another xls file. This is the code of what I am doing right now
        use DBI; use Win32::OLE; use Win32::OLE::Variant; use Win32::OLE::Const 'Microsoft Excel'; use Date::WeekOfYear; my $dir = "C:/proyecto/csv"; chdir ($dir); my @files = glob("*.xls"); for my $file (@files) { print "Processing $file ....."; print "Done!\n"; ###################################################################### +################################## #Termina creacion de los archivios de excel ###################################################################### +################################## ###################################################################### +################################## ###################################################################### +################################## my $datafilename = "d3"; print ("este es el archivo con el que trabajara : $file\n"); my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit'); #------ Abro el archivo CSV y lo dirijo al primera hoja de trabajo + ----- my $Book = $Excel->Workbooks->Open("$dir/$file"); my $Sheet = $Book->Worksheets(1); #----- View file ------------------------------------------- #$Excel->{'Visible'} = 1; $Sheet->Rows("1:16")->Insert(-4121,0); $Sheet->Columns('A:M')->Select(); $Sheet->Columns('A:M')->EntireColumn->Autofit(); $Sheet->Range("A16:L16")->{Value} = [[ 'Patente', 'Pedimento', 'Cv +e. Doc.', 'Fecha Entrada', 'Fecha Salida', 'RFC Imp/Exp', 'CURP', 'Pe +so Bruto', 'Contribuciones', 'Banco', 'Ped. Orig.', 'Ped. Rectif']]; $Book->ActiveSheet->Pictures->Insert( "C:\\proyecto\\aaa.bmp" )->S +elect; my $LastRow = $Sheet->UsedRange->Find({What=>"*", SearchDirection=>xlPrevious, SearchOrder=>xlByRows})->{Row}; my $LastCol = $Sheet->UsedRange->Find({What=>"*", SearchDirection=>xlPrevious, SearchOrder=>xlByColumns})->{Column}; #------ Imprimo en pantalla los valores ------- print "\nUltima Columna:"; print "$LastCol\n"; print "\nUltimo renglon:"; print "$LastRow\n"; #------ Ahora busco las celdas de la hoja de excel ----- #en este caso dejo la primera linea sin contar porque es la de los enc +abezados my ($Start_col, $Num_Cols,$End_col) = ('A', $LastCol, 'A'); ++$End_col while --$Num_Cols; my ($Start_row, $Num_rows) = (16, $LastRow); my $End_row = $Start_row + $Num_rows - 1; my $Range_str = "$Start_col$Start_row:$End_col$End_row"; my $range = $Sheet->Range($Range_str); $range->AutoFormat(2); $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> {Horizonta +lAlignment} = xlHAlignCenter; $Sheet -> Range('H:I') -> {HorizontalAlignment} = xlHAlignRight; $Sheet -> PageSetup -> {Orientation} = xlLandscape; $Sheet -> PageSetup -> {Zoom} = 75; $Sheet -> PageSetup -> {FitToPagesWide} = 1; $Sheet->Columns('H:H')->{NumberFormat}='0.000'; $Sheet->Columns('I:I')->{NumberFormat}='0'; $Sheet -> Range('A1:M15') -> Borders(xlEdgeBottom) -> {LineStyle +} = xlNone; $Sheet -> Range('A1:M15') -> Borders(xlEdgeLeft) -> {LineStyle +} = xlNone; $Sheet -> Range('A1:M15') -> Borders(xlEdgeTop) -> {LineStyle +} = xlNone; $Sheet -> Range('A1:M15') -> Borders(xlEdgeRight) -> {LineStyle +} = xlNone; $Sheet -> Range('A1:M15') -> Borders(xlEdgeBottom) -> {LineStyle +} = xlNone; $Sheet -> Range('A1:M15') -> Select(); $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl +EdgeBottom) -> {LineStyle} = xlDouble; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl +EdgeBottom) -> {Weight} = xlThick; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl +EdgeBottom) -> {ColorIndex} = 1; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl +EdgeLeft) -> {LineStyle} = xlContinuous; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl +EdgeLeft) -> {Weight} = xlThin; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl +EdgeTop) -> {LineStyle} = xlContinuous; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl +EdgeTop) -> {Weight} = xlThin; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl +EdgeBottom) -> {LineStyle} = xlContinuous; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl +EdgeBottom) -> {Weight} = xlThin; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl +EdgeRight) -> {LineStyle} = xlContinuous; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl +EdgeRight) -> {Weight} = xlThin; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl +InsideVertical) -> {LineStyle} = xlContinuous; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl +InsideVertical) -> {Weight} = xlThin; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl +InsideHorizontal) -> {LineStyle} = xlContinuous; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl +InsideHorizontal) -> {Weight} = xlThin; #---- Conectarse a la base de datos para extraer la tabla de semanas #my $datafilename = "d3"; my $dbh_semana1 = DBI->connect("DBI:mysql:database=$datafilename;host= +localhost", "root","xyz123", {'RaiseError'=>1}); if ( ! defined($dbh_semana1) ) { print STDERR "\n\nCould not open a connection to the database.\n\n +"; exit(); } my $sqlsemana1="SELECT SEMANA FROM semanas WHERE semana = (SELECT WEEK + (CURRENT_TIMESTAMP,1))"; my $sthsemana1 = $dbh_semana1->prepare($sqlsemana1); $sthsemana1->execute(); while (my $semana_row1 = $sthsemana1->fetch) { print "\nRELACION DE OPERACION CORRESPONDIENTES A LA SEMANA No. + @$semana_row1\n"; $Sheet->Cells(13,5)->{Value} = "Relacion de Operacion correspon +diente a la semanan No. @$semana_row1"; } my $dbh_semana2 = DBI->connect("DBI:mysql:database=$datafilename;host= +localhost", "root","xyz123", {'RaiseError'=>1}); if ( ! defined($dbh_semana2) ) { print STDERR "\n\nCould not open a connection to the database.\n\n +"; exit(); } my $sqlsemana2="SELECT FEC_FINAL FROM semanas WHERE semana = (SELECT W +EEK (CURRENT_TIMESTAMP,1))"; my $sthsemana2 = $dbh_semana2->prepare($sqlsemana2); $sthsemana2->execute(); while (my $semana_row2 = $sthsemana2->fetch) { $Sheet->Cells(11,7)->{Value} = "al @$semana_row2 "; } #---- FECHA INICIAL ----- my $dbh_semana = DBI->connect("DBI:mysql:database=$datafilename;host=l +ocalhost", "root","xyz123", {'RaiseError'=>1}); if ( ! defined($dbh_semana) ) { print STDERR "\n\nCould not open a connection to the database.\n\n +"; exit(); } my $sqlsemana="SELECT FEC_INICIA FROM semanas WHERE semana = (SELECT W +EEK (CURRENT_TIMESTAMP,1))"; my $sthsemana = $dbh_semana->prepare($sqlsemana); $sthsemana->execute(); while (my $semana_row = $sthsemana->fetch) { $Sheet->Cells(11,5)->{Value} = "Comprendida del @$semana_row "; } $Sheet->Range("C12")->{Value} = [[ '=LOOKUP(R[1]C[-2],PATENTES.xls!R2C +1:R82C2,2,FALSE)']]; $Excel -> {DisplayAlerts} = 0; # This turns off the "This file alre +ady exists" message. my $file2 = "M.$file"; print "este es el archivo : $file2\n\n\n"; $Book -> SaveAs("$file2"); $Book->Close();
        as you can see is just giving format to the file just that I can not find how to make the formula to work hope can you help me is the only thing missing to finish
        thank you