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++;
		}
                    			
	}
}