OXIESEC PANEL

Name	Size	Modified	Perms
📄 -d	10 bytes	02/03/2023 07:34:13 AM	rw-r--r--
📁 ..	-	02/03/2023 08:06:37 AM	rwxr-xr-x
📄 config.pl	7.61 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 cross2normal.pl	3.71 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 dataextract.sh	2.51 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 etl.pl	5.42 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 etlproc	9.99 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 func.pl	4.01 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 globvar.sh	127 bytes	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 initetl.pl	1.88 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 libawk.awk	3.07 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 makeXtab2NormalExcel.pl	5.73 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 process.pl	6.91 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 process_changed.pl	5.69 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 readwrite.pl	4.16 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 rxcsv.pl	7.94 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 rxdb.pl	11.06 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 rxfile.pl	4.68 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 rxsalesforce.pl	3.5 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 rxxml.pl	13.83 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 rxxml2.pl	4.18 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 sendmail.pl	1.06 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 sourcequery.pl	4.18 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 sql.pl	23.98 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 store.pl	7.97 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 tags	13.14 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 task.pl	33.48 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📁 test	-	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 userfunc.pl	5.05 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 valid.pl	3.64 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 workflow.pl	3.94 KB	02/03/2023 07:34:13 AM	rwxr-xr-x
📄 xform.pl	2.62 KB	02/03/2023 07:34:13 AM	rwxr-xr-x

Editing: makeXtab2NormalExcel.pl

#/usr/bin/perl																										
###########################################################################################################################
# DESC: SCRIPT TO MODIFY CROSSTAB EXCEL REPORT TO NORMAL EXCEL REPORT.	          		                          #
#															  #	
# Ussage: makeXtab2NormalExcel.pl file_name_with_path fixed_column ignore_rows sheet_name	merge			  #
#															  #	
# Author: Arun Kumar Swain												  #
#															  #	
# Company: Robust Designs (India) Pvt. Ltd					  					  #
#															  #	
# Last Updated:Fri Mar 26 11:04:11 IST 2010										  #	
# 															  #	
# Update: Replace special characters while creating out file								  #	
###########################################################################################################################

my $infile=$ARGV[0];
my $fixedcol=$ARGV[1];
my $ignrrows=$ARGV[2];
my $SheetName=$ARGV[3];
my $merge=$ARGV[4]; #Newly added on 29-sep-09. we are merging all sheets if set 1
$DateCol=$ARGV[5];

#print "I got infile=$infile fixedcol=$fixedcol ignrrows=$ignrrows SheetName=$SheetName merge=$merge \n"; 
if($merge eq 1){	#If merge option is set then process all sheets. To process all sheets I am making sheet Name as blank.
	$SheetName="";
}
else{
	$merge=0;
}
if($DateCol == ""){
$DateCol=-1;
}
my $xlsxf=index($infile,".xlsx");
my $Workbook;
use Spreadsheet::ParseExcel::Utility qw(ExcelFmt);
if($xlsxf > 0){
	use Spreadsheet::XLSX;
	$Workbook =Spreadsheet::XLSX ->new($infile);
}
else{
	use Spreadsheet::ParseExcel;
	my $XLS = new Spreadsheet::ParseExcel;
	$Workbook = $XLS->Parse($infile) or print "Parse Failllllllllllllllllllllllled\n";
}

my($WorkSheet, $Cell, $CellRow, $CellCol);
my $flag=0;

my $allsh = 0, $PrintHeading=1, $tmpfname="";

if( "$SheetName" ne ""){ #Process one sheet
		for(my $Sheet=0; $Sheet < $Workbook->{SheetCount} ; $Sheet++){
				$WorkSheet = $Workbook->{Worksheet}[$Sheet];
				if( "$SheetName" eq "$WorkSheet->{Name}" ){
						$tmpfname=">/tmp/$WorkSheet->{Name}".".csv";
						MakeXtab2Normal($tmpfname, $ignrrows, $fixedcol, $WorkSheet, $PrintHeading);
				}
		}
}
else					#Process All sheet. Case: Sheets are identical 
{
		for(my $Sheet=0; $Sheet < $Workbook->{SheetCount} ; $Sheet++){
				$WorkSheet = $Workbook->{Worksheet}[$Sheet];
				if(($merge eq 1) && ($Sheet eq 0)){
					$tmpfname=">/tmp/$WorkSheet->{Name}".".csv";
					$PrintHeading=1;
				}
				elsif(($merge eq 1) && ($Sheet ne 0)){
					$tmpfname=">".$tmpfname if $Sheet eq 1;
					$PrintHeading=0;
				}
				else {
					$tmpfname=">/tmp/$WorkSheet->{Name}".".csv";
					$PrintHeading=1;
				}
				print "\n **********$tmpfname****No of Records=".$WorkSheet->{MaxRow} if defined $WorkSheet->{MaxRow}."***\n";
				MakeXtab2Normal($tmpfname, $ignrrows, $fixedcol, $WorkSheet, $PrintHeading);
		}

}
undef $XLS;

sub MakeXtab2Normal{
		my $tmpfname = shift;
		my $ignrrows = shift;
		my $fixedcol = shift;
		my $WorkSheet = shift;
		my $PrintHeading = shift;

my $FILEH;
		my @HEADING=();
		my $NXTROW, $CellRow, $CellCol,$MAXHEADCOL;

open($FILEH, $tmpfname) or die "File Creation failed Error:$!"."[".$tmpfname."]\n";
		print "\nCurrent SHEET:", $WorkSheet->{Name}, "\n";
		for( $CellRow= $WorkSheet->{MinRow}; defined $WorkSheet->{MaxRow} && $CellRow<= $WorkSheet->{MaxRow}; $CellRow++){
				#Ignore rows specified
				if($ignrrows ne "" && $CellRow<$ignrrows){
						next;
				}

# Dont print row if first column is blank
				next if $WorkSheet->{Cells}[$CellRow][$WorkSheet->{MinCol}]->{_Value} eq "" ;

# Prepare Headings 
				for( $CellCol= $WorkSheet->{MinCol} ; defined $WorkSheet->{MaxCol} && $CellCol<= $WorkSheet->{MaxCol}; $CellCol++){
						$Cell = $WorkSheet->{Cells}[$CellRow][$CellCol];
						
						if($Cell){
								push(@HEADING, $Cell->{_Value});
						}
						else{
								push(@HEADING, "NA");
						}
				}
				last;
		}

$NXTROW=++$CellRow;
		$MAXHEADCOL=$CellCol;

# Print Heading
print "\n***************************$PrintHeading**************************************\n";
		if($PrintHeading eq 1){
			$fixedcol = 1  if $fixedcol eq "" ;
			for(my $i=0; $i < $fixedcol; $i++){
				print $FILEH $HEADING[$i]."|";
			}
			print $FILEH "XTAB|Count \n";
		}

for(my $HEADCNT= $fixedcol ; $HEADCNT < $MAXHEADCOL; $HEADCNT++) {
				# Dont consider the total columns
				next if $HEADING[$HEADCNT] =~/[Tt]otal/;

for($CellRow=$NXTROW; defined $WorkSheet->{MaxRow} && $CellRow<= $WorkSheet->{MaxRow}; $CellRow++){
						my @csvdata = ();

# Dont print row if first column is blank
						next if $WorkSheet->{Cells}[$CellRow][$WorkSheet->{MinCol}]->{_Value} eq "" ;

# Dont print row if value is zero
						#next if $WorkSheet->{Cells}[$CellRow][$HEADCNT]->{Val} =~ /[\s\t-]0/;

# Dont print row if value is zero
						next if $WorkSheet->{Cells}[$CellRow][$HEADCNT]->{Val} <= 0;

# Dont print row if value is blank 
						#next if $WorkSheet->{Cells}[$CellRow][$HEADCNT]->{Val} eq "";

for( $CellCol= $WorkSheet->{MinCol} ; $CellCol< $fixedcol; $CellCol++){
								$Cell = $WorkSheet->{Cells}[$CellRow][$CellCol];
								if($Cell && ($CellCol == $DateCol-1)){
									my $val=ExcelFmt('yyyy-mm-dd',$Cell->{_Value});
									push(@csvdata, $val);
								}
								elsif($Cell){
									$Cell->{_Value} =~ s/[^[:print:]]//g;
									push(@csvdata, $Cell->{_Value});
								}
								else{
									push(@csvdata, "");
								}
						}
						push(@csvdata, $HEADING[$HEADCNT]); #Xtab Name
						push(@csvdata, $WorkSheet->{Cells}[$CellRow][$HEADCNT]->{Val}); #Xtab Value
						@csvdata=join("|", @csvdata);
						print $FILEH @csvdata, "\n";
				}
		}
		close($FILEH);
}