Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??
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

In reply to Re^4: How to use a formula in perl by padawan_linuxero
in thread How to use a formula in perl by padawan_linuxero

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others imbibing at the Monastery: (5)
    As of 2020-12-04 08:09 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?
      How often do you use taint mode?





      Results (58 votes). Check out past polls.

      Notices?