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: cross2normal.pl

#/usr/bin/perl

###########################################################################################################################
# DESC: SCRIPT TO MODIFY CROSSTAB EXCEL REPORT TO NORMAL EXCEL REPORT.													  #
# Ussage: readWrite.pl file_name_with_path fixed_column ignore_rows sheet_name											  #
# Author: Arun Kumar Swain																								  #
# Company: Robust Designs (India) Pvt. Ltd																				  #
###########################################################################################################################

use Spreadsheet::ParseExcel;
my $XLS = new Spreadsheet::ParseExcel;

my $infile=$ARGV[0];
my $fixedcol=$ARGV[1];
my $ignrrows=$ARGV[2];
my $SheetName=$ARGV[3];

my $Workbook = $XLS->Parse($infile) or die "Parse Failllllllllllllllllllllllled\n";

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

my $allsh = 0;

if( "$SheetName" ne ""){ #Process one sheet
		for(my $Sheet=0; $Sheet < $Workbook->{SheetCount} ; $Sheet++){
				$WorkSheet = $Workbook->{Worksheet}[$Sheet];
				if( "$SheetName" eq "$WorkSheet->{Name}" ){
						my $tmpfname=">/tmp/$WorkSheet->{Name}".".csv";
						MakeXtab2Normal($tmpfname, $ignrrows, $fixedcol, $WorkSheet);
				}
		}
}
else					#Process All sheet. Case: Sheets are identical 
{
		for(my $Sheet=0; $Sheet < $Workbook->{SheetCount} ; $Sheet++){
				$WorkSheet = $Workbook->{Worksheet}[$Sheet];
				my $tmpfname=">/tmp/$WorkSheet->{Name}".".csv";
				MakeXtab2Normal($tmpfname, $ignrrows, $fixedcol, $WorkSheet);
		}

}
undef $XLS;

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

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

open($FILEH, $tmpfname) or die "File Creation failed Error:$!\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;
				}
				# 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
		$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]->{_Value} eq 0;

for( $CellCol= $WorkSheet->{MinCol} ; $CellCol< $fixedcol; $CellCol++){
								$Cell = $WorkSheet->{Cells}[$CellRow][$CellCol];
								if($Cell){
										push(@csvdata, $Cell->{_Value});
								}
								else{
										push(@csvdata, "");
								}
						}
						push(@csvdata, $HEADING[$HEADCNT]); #Xtab Name
						push(@csvdata, $WorkSheet->{Cells}[$CellRow][$HEADCNT]->{_Value}); #Xtab Value
						@csvdata=join("|", @csvdata);
						print $FILEH @csvdata, "\n";
				}
		}
		close($FILEH);
}