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', 'Cve. Doc.', 'Fecha Entrada', 'Fecha Salida', 'RFC Imp/Exp', 'CURP', 'Peso Bruto', 'Contribuciones', 'Banco', 'Ped. Orig.', 'Ped. Rectif']]; $Book->ActiveSheet->Pictures->Insert( "C:\\proyecto\\aaa.bmp" )->Select; 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 encabezados 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") -> {HorizontalAlignment} = 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(xlEdgeBottom) -> {LineStyle} = xlDouble; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeBottom) -> {Weight} = xlThick; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeBottom) -> {ColorIndex} = 1; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeLeft) -> {LineStyle} = xlContinuous; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeLeft) -> {Weight} = xlThin; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeTop) -> {LineStyle} = xlContinuous; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeTop) -> {Weight} = xlThin; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeBottom) -> {LineStyle} = xlContinuous; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeBottom) -> {Weight} = xlThin; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeRight) -> {LineStyle} = xlContinuous; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeRight) -> {Weight} = xlThin; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlInsideVertical) -> {LineStyle} = xlContinuous; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlInsideVertical) -> {Weight} = xlThin; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlInsideHorizontal) -> {LineStyle} = xlContinuous; $Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlInsideHorizontal) -> {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 correspondiente 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 WEEK (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=localhost", "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 WEEK (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!R2C1:R82C2,2,FALSE)']]; $Excel -> {DisplayAlerts} = 0; # This turns off the "This file already exists" message. my $file2 = "M.$file"; print "este es el archivo : $file2\n\n\n"; $Book -> SaveAs("$file2"); $Book->Close();