use OLE;
use Win32::OLE::Const 'Microsoft excel';
use Cwd;


my $directory = &getcwd;
my $xlsfile = "$directory/Test_Auswertung_RX.xlsx";

#--Signalbeispiel des neuen Datenpaktes. In Spalte B durchsuchen---
my @array = ( 
        'TEMP_SCRA', 'ST_RMMI_SCRA', 'ST_RFG_SCRA', 'ST_FLLV_PU_SCRA', 'ST_FLLV_LEV_SCRA', 
        'RFLV_SCRA', 'RELA_MIX_SCRA', 'FLLV_SCRA', 'DISP_RQ_GR_GRB', 'DISP_PRG_GRB', 'DISP_PO_GRB', 
        'DISP_GR_GRB', 'ST_SYS_ERR_OBD_SCR', 'ST_ILK_SCRS', 'RQ_MIL_DIAG_OBD_SCR', 'AVL_PWR_EL_GEY', 
        'ST_DIAG_OBD_6_PT_MAX_MUX', 'ST_DIAG_OBD_6_PT_IMME_MUX', 'DIAG_ST_OBD_6_PT_8', 'DIAG_ST_OBD_6_PT_7',
        'DIAG_ST_OBD_6_PT_6', 'DIAG_ST_OBD_6_PT_5', 'DIAG_ST_OBD_6_PT_4', 'DIAG_ST_OBD_6_PT_3', 'DIAG_ST_OBD_6_PT_2', 
        'DIAG_ST_OBD_6_PT_1', 'FLLUPT_GPWSUP', 'ST_GR_BAC', 'DSTN_SLRDI_GLB', 'CTR_SNW', 'CTR_SLRDI_GLB', 
        'CTR_SLP_BUS', 'CTR_FSL', 'ST_ILK_ERRM_FZM', 'ST_ENERG_FZM', 'QC_VEH, I_SC_MIN_VEH', 'I_SC_MAX_VEH', 
        'CLAS_BT', 'TYP_VEH', 'TYP_BODY', 'CLAS_PWR', 'NO_VECH_7', 'NO_VECH_6, NO_VECH_5', 'NO_VECH_4',
        'NO_VECH_3, NO_VECH_2', 'NO_VECH_1', 'RQ_PAIC', 'ST_FLLV_FUTA_SPAR', 'RNG', 'MILE_KM', 'FLLV_FUTA_RH', 
        'FLLV_FUTA_LH', 'FLLV_FUTA', 'T_SEC_COU_REL', 'T_DAY_COU_ABSL', 'STEA_FTAX_EFFV', 'QU_STEA_FTAX_EFFV', 'ST_CT_BTL', 
        'ST_CT_BON', 'ST_PHTR_ENGSTA_DRV', 'RQ_HTFL_AIC', 'DISP_ECO_CON_HTCL_COOD', 'DISP_ECO_CON_2_HTCL_COOD', 'CTR_RDI_AIC', 
        'AVL_TORQ_ACCM', 'AVL_SETQ_EFAN_AIC', 'AVL_RQMT_HTFL', 'ST_TAV_PFUTA_2', 'ST_SEN_FUFF_PFUTA_2', 'ST_PURG_RDI', 
        'ST_PLCHK_P_PFUTA_2', 'ST_PLCHK_PFUTA_TAV_2', 'ST_PLCHK_PFUTA_AIV_2', 'ST_PFUTA_TEMP_2', 'ST_PFUTA_P_2', 'ST_PFUTA_EX_P_2', 
        'ST_MOD_FULG_PFUTA_2', 'ST_FULG_RQ_PUBU', 'ST_AIV_PFUTA_2', 'ST_ACTR_LOKG_FUFF_PFUTA_2', 'ST_TRAI', 'ST_VEHSS_PBRK', 
        'TEMP_EX_UNFILT', 'TEMP_EX', 'ST_BLTB_SW_DR', 'TAR_PO_GRB', 'SPEC_TAR_GR_GRB_2', 'ST_PENG_GRB', 'ESTI_WMOM_CRP_IDLG', 
        'AVL_WMOM_CRP_IDLG', 'CTR_CR_SWO_EKP', 'AVL_OPMO_CHGE'
);

#--Ende Signalbeispiel---

#---Main----------
open_excel();

#---Ende Main-------


# ###################################################################
#  Sub Open excel File
# ###################################################################


sub open_excel 
{
        $excel = Win32::OLE->GetActiveObject('excel.Application')|| Win32::OLE->new('excel.Application');
        
                my $workbook = $excel -> Workbooks  -> Open ($xlsfile) or die ( "Can't open $xlsfile" );    
        
                $excel->{'Visible'} = 0;        #0 is hidden, 1 is visible
                $excel->{DisplayAlerts}=0;        #0 is hide alerts 
                
                
                $sheet = $workbook -> Worksheets (1);

                $excel->{'Visible'} = 0;        #0 is hidden, 1 is visible
                $excel->{DisplayAlerts}=0;        #0 is hide alerts 
                $sheet = $workbook -> Worksheets (1);
                
                # Find Last Row    
                $LastRow = $sheet -> UsedRange -> Find({What => "*",
                                        SearchDirection => xlPrevious,
                                        SearchOrder => xlByRows}) -> {Row};     
                   
                foreach (@array)
                {
                        $Signal = $_;
                        Search_Signal($Signal);
                }   
        
}


$excel->{'Visible'} = 1;
undef $workbook;
undef $excel;

 
 
 
sub Search_Signal
{        
        my ($In_file)= @_;
    for $row(16..$LastRow)
        {       $col=2;     
                            
       my $name = $sheet -> Cells($row,$col) -> {'Value'}; 
                if ($name eq $In_file)
                {
                        print "Signal $In_file found on line $row\n";
                        $row++;
                }
                                        
        }
}