Purpose:
To determine the data rows and columns from user selected Excel file.
Liquid UI Code:
// SAPLSMTR_NAVIGATION.E0100.sjs
// User Interface - SAP Easy Access Screen
load('wsoffice'); // Need to load this file to display File Selection pop-up
clearscreen();
inputfield([1,0], "Excel Rows", [1,15], {"size":3, "name":"z_mm01_rows"});
inputfield([2,0], "Excel Columns", [2,15], {"size":3, "name":"z_mm01_cols"});
pushbutton([4,0],"@0V@Read Excel", "?", {"process":excelRowColumnCount});
// Remove blank spaces
String.prototype.trim=function(){return this.replace(/^\s+|\s+$/g,'');}
// To validate if the variable holds blank or null value
function isBlank(jvar){
if(typeof jvar == 'string') {
jvar = jvar.trim();
}
return(jvar == 'undefined' || jvar == null || jvar == "" || jvar == void 0);
}
// Function to Show File Open Dialog
function selectFileDialog(szPrompt){
if(szPrompt==void 0)
szPrompt = 'Select Excel File';
var dialog = new ActiveXObject('MsComDlg.CommonDialog');
// dialog.Filter='All Files(*.*)|*.*';
dialog.Filter='(*.xl*)|*.xl*'; // BD
dialog.MaxFileSize=32767;
//dialog.AllowMultiSelect = true;
dialog.DialogTitle=szPrompt;
dialog.Flags=0x200|0x80000|0x800|0x4|0x200000
dialog.ShowOpen();
//var ret = dialog.FileTitle;
var ret = dialog.FileName;
dialog = void 0;
return ret;
}
// Function to open Excel File From The File Selection Dialog
function openExcel(filename) {
if(excelObj == void 0)
excelObj = new ActiveXObject('Excel.Application');
excelBook = excelObj.Workbooks.Open(filename);
excelObj.Visible = true;
excelObj.ScreenUpdating = true;
}
// Determine Total Number of Columns with Data in Excel SpreadSheet
function determineNoOfDataColumns(excelActiveSheet, nColumnHeadingRow) {
var excelColumnCount = excelActiveSheet.Columns.Count;
for(var i = 1; i<excelColumnCount;i++) {
if (excelActiveSheet.Cells(nColumnHeadingRow, i).Value == undefined || typeof(excelActiveSheet.Cells(nColumnHeadingRow, i).Value) == 'undefined') {
break;
}
}
return i;
}
// Determine Total Number of Rows with Data in Excel SpreadSheet
function determineNoOfDataRows(excelActiveSheet, nTotalDataColumns, nStartDataRow) {
var excelRowCount = excelActiveSheet.Rows.Count;
for(var i = nStartDataRow; i<excelRowCount;i++) {
lastRowFound = false;
for(var j = 2; j<nTotalDataColumns;j++) {
if (excelActiveSheet.Cells(i, j).Value == undefined || typeof(excelActiveSheet.Cells(i, j).Value) == 'undefined') {
lastRowFound = true;;
} else {
lastRowFound = false;
break;
}
}
if(lastRowFound == true) {
// Last Row with Data + 1;
break;
}
}
return i;
}
// Function to determine rows and columns
function excelRowColumnCount(){
OPEN_EXCEL_FILE:;
if(excelObj == void 0) {
excelFileName = selectFileDialog('Select Excel File');
if(excelFileName.length) {
openExcel(excelFileName);
excelSheet = excelBook.ActiveSheet;
} else {
message('E: No Excel File Selected');
return; // If Problem opening selected excel file, stop the process
}
} else { // Excel is already open (Manually Opened or Re-run for Error Processing)
try {
excelSheet = excelBook.ActiveSheet;
// Check to see if we can read cell value, if not then Re-Open Excel File
var cellCheckValue = excelSheet.Cells(1, 2).Value;
}
catch(err) {
delete excelObj;
goto OPEN_EXCEL_FILE;
}
}
totalDataColumns = determineNoOfDataColumns(excelSheet,1);
totalDataRows = determineNoOfDataRows(excelSheet,totalDataColumns,1);
totalDataColumns = totalDataColumns-1;
totalDataRows = totalDataRows-1;
set('V[z_mm01_rows]','&V[totalDataColumns]');
set('V[z_mm01_cols]','&V[totalDataRows]');
}
See attachments for code samples!
