Excel Export - a Lotus script reference code fragment

Fragment of lotus script code that shall help you get a clear idea of how to export stuffs from lotus notes to excel 

Note: For more info on manipulation using excel objects, you can go through the following site, which holds the complete dom information on excel object:
http://msdn.microsoft.com/en-us/library/syyd7czh%28VS.80%29.aspx

  Dim excelObj As Variant
    On Error Goto errHandler
   
    Set excelObj=createObject("Excel.Application")
    Call excelObj.Application.workbooks.open("C:\Documents and Settings\karthikeyan_a\Desktop\Dashboard.xls")
    Call excelObj.ActiveSheet.ChartObjects("Chart 2").Activate
    Call excelObj.ActiveChart.ChartArea.Copy
   
    excelObj.Range("F3").Select
    'give values between 0 to 100 and see the difference in the image being produced
    excelObj.ActiveCell.FormulaR1C1 = chartValue
   
    excelObj.Range("H2").Select
    'give the graph title
    excelObj.ActiveCell.FormulaR1C1 = chartTitle
   
    excelObj.activeworkbook.save
    excelObj.activeworkbook.close(True)
    excelObj.quit
   
    Call uidoc.GotoField(rtFieldName)
    Call uidoc.Paste
    Call uidoc.GotoField(rtFieldName)
   
    Exit Function
errHandler:
    Msgbox Error,,Cstr(Erl) & " Clear Graph function"
    Exit Function

Import Lotus Notes Documents from Excel - a reference guide

A Sample fragment of lotus script code that can be used as a reference to get an idea about how to import documents from datas present in an excel sheet

Note: For more info on manipulation using excel objects, you can go through the following site, which holds the complete dom information on excel object:
http://msdn.microsoft.com/en-us/library/syyd7czh%28VS.80%29.aspx

Sub Click(Source As Button)
    'initiaition
    'handle errors in case of abrupt termination   
    On Error Goto errHandler
   
    'declare all the necessary variables and objects required for further manipulation
    Dim workspace As New NotesUIWorkspace
    Dim session As New NotesSession
    Dim currDB As NotesDatabase
    Dim importedDoc As NotesDocument
   
    'declaring variants
    Dim workbookPath As Variant
    Dim excel As Variant
    Dim xlSheet1 As Variant
    Dim xlSheet2 As Variant
    Dim xlSheet3 As Variant
    Dim xlSheet4 As Variant
   
    'declaring arrays
    Dim proposalDetails(0 To 6) As String
    Dim companyDetails(0 To 14) As String
    Dim mandateInformation(0 To 2) As String
    Dim proposalOutcome(0 To 8) As String
   
    'declaring strings
    Dim cellValue As String
    Dim validFlag As String
   
    'declaring integers
    Dim itr As Integer
    Dim rowCount As Integer
    Dim colCount As Integer
    Dim column As Integer
    Dim importedDocumentCount As Integer
    Dim userChoice As Integer
   
    'declaring booleans
    Dim continue As Boolean
   
    'initializing variables and arrays
    continue=True
    itr=0
    rowCount=6
    colCount=2
    importedDocumentCount=0
   
    proposalDetails(0)="CompleteCode"
    proposalDetails(1)="PTitle"
    proposalDetails(2)="PDateOfProposal"
    proposalDetails(3)="PEstimateEffort"
    proposalDetails(4)="PAnalyst"
    proposalDetails(5)="ShowPAnalyst"
    proposalDetails(6)="PBranch"
   
    companyDetails(0)="PCompanyName"
    companyDetails(1)="PLocation"
    companyDetails(2)="PCompanyType"
    companyDetails(3)="PPIC"
    companyDetails(4)="PAddressLine1"
    companyDetails(5)="PAddressLine2"
    companyDetails(6)="PCity"
    companyDetails(7)="PCountry"
    companyDetails(8)="PZipCode"
    companyDetails(9)="PTelephone"
    companyDetails(10)="PFax"
    companyDetails(11)="PEmail"
    companyDetails(12)="PContactPerson"
    companyDetails(13)="PPFC"
    companyDetails(14)="PDesignation"
   
    mandateInformation(0)="PMandateType"
    mandateInformation(1)="PMandateValue"
    mandateInformation(2)="PMandateDetails"
   
    proposalOutcome(0)="PProposalStatus"
    proposalOutcome(1)="PLost"
    proposalOutcome(2)="PWon"
    proposalOutcome(3)="PFinalClearancePersonTech"
    proposalOutcome(4)="PNameOfBidders"
    proposalOutcome(5)="PDraftStatus"
    proposalOutcome(6)="PKeyDiscussions"
    proposalOutcome(7)="PFinalClearancePersonComm"
    proposalOutcome(8)="PIMaCSStatus"
   
    userChoice=Messagebox("Please ensure that you have not left any empty rows in between the data in excel sheet" & Chr(13) & _
    "and make sure that the Excel file is created using the design template present in the profile document" & Chr(13) &_
    "Click yes to proceed",4+32,"Caution")
    If userChoice=7 Then Exit Sub
   
    'create the excel object reference
    Set excel=CreateObject("Excel.Application")
    'if the excel object is not set then inform the user and exit
    If excel Is Nothing Then
        Msgbox "Unable to create object reference for the Excel Application"  & Chr(13) &  _
        "Please make sure that the Excel appication is properly installed on your machine",,"Operation failed"
        Exit Sub
    End If
   
    'do not display the file and use it from the backend
    excel.visible=False   
   
    'bring up an open file dialog to let the user choose an excel file to be imported from
    workbookPath=workspace.OpenFileDialog(False)
   
    If Isempty(workbookPath) Then Exit Sub
   
    'open the excel file
    Call Excel.Application.Workbooks.Open(Trim(WorkBookPath(0)))
    Set xlSheet1=Excel.Application.Workbooks(1).WorkSheets(1)   
    Set xlSheet2=Excel.Application.Workbooks(1).WorkSheets(2)   
    Set xlSheet3=Excel.Application.Workbooks(1).WorkSheets(3)   
    Set xlSheet4=Excel.Application.Workbooks(1).WorkSheets(4)   
   
    'if any one of the sheet is found missing then inform the user that the file chosen does not follow the
    'template in the profile document and hence the process shall be aborted
    If (xlSheet1 Is Nothing) Or (xlSheet2 Is Nothing) Or (xlSheet3 Is Nothing) Or (xlSheet4 Is Nothing)  Then
        Msgbox "Please chose a valid Excel file that uses the design template provided in the configuration document",,"Invalid Excel File"
        Exit Sub
    End If
   
    'set the handle for the current database
    Set currDB=session.CurrentDatabase
   
    'loop through all the 4 sheets in the excel file and create a equallent document in the current database
    While continue    'start of main while
       
        'initialize the cell value to null
        cellValue=""   
        'initialize the valid flag to null
        validFlag=""
       
        'create a new document
        Set importedDoc=currDB.CreateDocument
       
        'create the document using the proposal form
        Call importedDoc.ReplaceItemValue(FORM_ITEM,PROPOSAL_FORM_NAME)
       
        'populate the values from the excel work sheets to the document
        'get values from work sheet 1
        For itr=0 To 6
            column=colCount+itr
            cellValue=xlSheet1.Cells(rowCount,column).value
            validFlag=ValidFlag+cellValue
            Call importedDoc.ReplaceItemValue(proposalDetails(itr),cellValue)
        Next
       
        'if there aren't any values in the row of interest in the first sheet then stop proceeding
        If Trim(validFlag)="" Then
            continue=False       
            Goto skipLoop
        End If
       
        'get values from worksheet 2
        For itr=0 To 14
            column=colCount+itr
            cellValue=xlSheet2.Cells(rowCount,column).value
            Call importedDoc.ReplaceItemValue(companyDetails(itr),cellValue)
        Next
       
        'get values from work sheet 3
        For itr=0 To 2
            column=colCount+itr
            cellValue=xlSheet3.Cells(rowCount,column).value
            Call importedDoc.ReplaceItemValue(mandateInformation(itr),cellValue)
        Next
       
        'get values from work sheet 4
        For itr=0 To 8
            column=colCount+itr
            cellValue=xlSheet4.Cells(rowCount,column).value
            Call importedDoc.ReplaceItemValue(proposalOutcome(itr),cellValue)
        Next
       
        'save the imported document
        Call importedDoc.Save(True,False)
       
        'increase the importedDocumentCount to be displayed in the process report at the end
        importedDocumentCount=importedDocumentCount+1
       
        'increase the row count to create new documents from the values present in the next row
        rowCount=rowCount+1
skipLoop:       
    Wend    'end of main while
   
    'refresh the view to help the user see the changes instantaneously
    Call workspace.ViewRefresh
   
    'inorder to prevent memory overload or memory heap
    'ensure that neither the excel document nor the excel application is open or running in the backend
'    Call excel.close
   
    Call excel.quit
    'Set excel=Nothing
   
    'inform the process report to the user
    Msgbox "A total of " & Cstr(importedDocumentCount) & " documents has been imported from the chosen excel Document",,"Process Report"   
    Exit Sub
   
    'imform the user about the errors that has resulted in abrupt termination
errHandler:
    'if excel is not installed in the machine then inform the user to install the same before proceeding
    If Err=208 Then
        Msgbox "Microsoft Excel is not installed in your machine ",,"Aborting process"
        Exit Sub
       
    'if the user has chosen a file other that an excel file inform the user about what type of file he must choose   
    Elseif Err=213 Then
        Msgbox "Please choose a valid Microsoft Excel file developed based on the template provided in the profile document",,"Aborting process"
        Exit Sub
    End If
   
    'populate the error message in the status bar
    Print "Error : ***" & Error & "*** has occured on line ***" & Cstr(Erl) & "*** with error number ***" &  _
    Cstr(Err) & "***"
   
    'inform the user about the where about the information on the error that has occured
    Msgbox "An error has occured while attempting to import details from the excel document" & Chr(13) &  _
    "The technical details of the error are as printed in the status bar" & Chr(13) &  _
    "Please inform your administrator to contact the support team immediately to fix the issue"
    Exit Sub
End Sub

#6 : Bugs I Found : Javascript comments get compiled in XPages

I had the following code in a button on the XPage.
try {
var id="#{javascript: getComponent(prototype.name).getId()}"; //---this line produces error
    alert(id);
} catch (error)    {
    alert(error.message);
}
When I attempted to open the XPage , I got the following error,
 /**********************************************************/
Unexpected runtime error
The runtime has encountered an unexpected error.
500 Exception
Error while executing JavaScript action expression
Script interpreter error, line=1, col=30: 'getComponent()' is null

►  Stack Trace

 /**********************************************************/

Hence I attempted something different after commenting the same line as follows
try {
//var id="#{javascript: getComponent(prototype.name).getId()}";
var id="#{javascript: getComponentInstanceId()}";
    alert(id);
} catch (error)    {
    alert(error.message);
}
and previewed the same on the notes client...
Oops.... got the same error ....

After tinkering a bit, I removed the comment and placed a code as is mentioned below,
try {
var id="#{javascript: getComponentInstanceId()}";
    alert(id);
} catch (error)    {
    alert(error.message);
}

and that worked.....

A possible bug with javascript comments in XPages

Delete operator in JavaScript

I never knew that a keyword named as Delete existed in javascript.
And that was until I started studying the Lotus Domino Designer XPage
Reference documents. This seems to be a pretty useful operator and is used to
deletes an object, an object's property, or an element at a specified index
in an array.

The following code fragment will help you with better understanding of the same
/**************************************************************/
var arr = {
    "number": 42,
    "year" : 2007,
    "hello" : "world",
     "foo" : "bar"
 }

for(var ele in arr) {
    alert(ele + " : " + arr[ele]);
}
 
delete arr['hello']; //Removes the 'hello' key of the array

for(var ele in arr) {
    alert(ele + " : " + arr[ele]); //The 'hello' key and its value will be missing
}
/**************************************************************/

document.getElementById in XPages

 After I created my first XPage, I added a Button and a Edit Box from the control palette to the page. Now I have mentioned the name of the field as "myField".

And in the onclick event of the button, I had put the following code on the Client side javascript

var fieldVal=document.getElementById("myField").value;
alert(fieldVal);

When I clicked the button on Notes Client it said "Undefined"

So the fix to this issue is as follows,

var fieldVal=document.getElementById("#{id:myField}").value;
alert(fieldVal);

And that worked fine alerting my values that i put in the edit box.
I assume that the JSF process (probably) on the back end, puts my field id as  #{id:fieldName} format .

Problem: Unable to view XPages on NotesClient 8.5.1

As soon as I installed Lotus Notes 8.5.1, I was anxious to see how XPages will look on Notes Client. So I opened up a database which My team was already working on,

1. Created a new XPage,

1. Added the text "Hello Notes Client XPage World"

1. and Previewed the same on Notes Client.

Oops it did not work....

Ok I knew that they work well on web(I have personally practised a few before),

hence I previewed the same on the browser, 

Oops again, it did not work....

The error that I received were the following, for the Notes Client 

/***************************************************************/ 

Page Not Found

URL: http://127.0.0.1:1179/xsp/Domino!!someFolder/Mockup2.nsf/myXpage.xsp 
404 Exception

Could not create the page /sdf.xsp because the class xsp.Sdf could not be found. Please check your spelling.
Cannot find class xsp.MyXPage in NSF
► Stack Trace ▼

/************************************************************/

and the following on IE 

/************************************************************/ 

HTTP 404 - File not found 

Internet Explorer

/************************************************************/ 

This bothered me for almost a day or so, until my friend helped me out with this.....

The solution is as follows,   

1) Open your XPage in Designer    

2) On the top margin click Project    

3) Select Build Automatically (Refer to following image)  

4) Save your XPage and preview in client as well as on the web

Voila, that solved my problem

Case insensitive sorting of Arrays in Javascript

Let me Assume elementArr as a javascript array. Then the following function, will sort the contents of the array in alphabetical order and case insensitive fashion and will work fine with IE6+ and Firefox. Not yet tested in Safari and Opera.


        elementArr.sort(function(x,y){
             var a = String(x).toUpperCase();
             var b = String(y).toUpperCase();
            if (a > b)     {
                return 1
            }    else  if (a < b) {
                return -1
            }    else    {   
                return 0;
            }   
        });

If the contents of the array are say [a,B,A,z,e,C], then the resultant array shall be some thing like this, [a,A,B,C,e,z]

Hope this helps :)

Basic Validation for fields in a form

'****************************************************************
'@Purpose    :    To perform basic validation on fields that is to check wether they are empty or not
'@Author        :    Karthikeyan A
'@Return        :    Boolean
'@Logic        :     Parameter "manadatoryFields" must contain field labels and field names seperated by "~" and each pair inturn must be seperated by "$#$"

'****************************************************************
Function performBasicValidation(mandatoryFields As String,uidoc As NotesUIDocument) As Boolean
    'mark the flow of control moving into the current function
    performBasicValidation=False
   
    'declare all variables and functions necessary for further manipulation
    Dim session As New NotesSession   
    Dim fields As Variant
    Dim currentField As String
   
    'initiation
    'handle errors in case of abrupt termination
    On Error Goto basicValidationErrHandler
   
   
    'process the provided list of mandatory fields and values to check if they have some value or not
    fields=Split(mandatoryFields,"$#$")
   
    Forall vall In fields
        currentField=Strright(vall,"~")
        If Not currentField="" Then
            If Trim(Replace(Replace(uidoc.FieldGetText(currentField),Chr(10),""),Chr(13),""))="" Then
                Call uidoc.GotoField(currentField)
                Msgbox "Please provide the" &Cstr(Strleft(vall,"~")) & Chr(13) & "Note: All fields marked with * are Mandatory",,"Information required"
                performBasicValidation=False
                Exit Function
            End If
        End If
    End Forall
   

   
    'mark the flow of control moving out of the current function
    performBasicValidation=True
    Exit Function
    'log the errors that resulted in abrupt termination
basicValidationErrHandler:
    Msgbox "An error occured while attempting to validate the document ",,"Validation failed"
    Print "Error : " & Error & " occured on line " & Cstr(Erl) & " with error number " & Cstr(Err) & " in basic validation while processing field ***" & currentField & "***"
   
    Exit Function
End Function

Displaying Alphabets using CSS in a nice fashion

The following code snippet will display alphabets in a nice simple fashion.

Open up a Notes form, paste the following code into it, select all the text and say Pass-Thru Html

<div id="author_alphabetic"></div>

<style type="text/css">
#author_alphabetic {
    text-align:center;
}
#author_alphabetic ul {
}
#author_alphabetic li {
    float:left;
    margin:3px 3px 0 0;
}
#author_alphabetic a {
    position:relative;
    width:20px;
    height:13px;
    border:1px solid rgb(203, 214, 149);
    text-decoration:none;
    font:.7em/.7em "Times New Roman";
    float:left;
    display:inline;
    color:#330;
}
#author_alphabetic a span {
    background:url(../image/alpha_br.png) no-repeat right bottom;
    width:16px;
    height:16px;
    display:block;
    position:absolute;
    top:0;
    left:0;
}
#author_alphabetic a:hover {
    background:rgb(224, 232, 158);
    z-index:100;
}
#author_alphabetic a:hover span.first {
    display:block;
    font-size:1em;
    line-height:1.1;
    font-weight:bold;
    color:#15362d;
    cursor:pointer;
    width:30px;
    height:20px;
    top:-1px;
    left:-1px;
    background:rgb(224, 232, 158);
    border:1px solid rgb(203, 214, 149);
}
#author_alphabetic a:hover span span {
  
    display:block;
    position:absolute;
    width:25px;
    height:25px;
}
</style>


Copy and Paste the following function into your javascript header

function loadAlpha()    {

var row1=new Array(26);
    row1[0]="A";
    row1[1]='B';
    row1[2]='C';
    row1[3]='D';
    row1[4]='E';
    row1[5]='F';

    row1[6]='G';
    row1[7]='H';
    row1[8]='I';
    row1[9]='J';
    row1[10]='K';
    row1[11]='L';

    row1[12]='M';
    row1[13]='N';
    row1[14]='O';
    row1[15]='P';
    row1[16]='Q';
    row1[17]='R';

    row1[18]='S';
    row1[19]='T';
    row1[20]='U';
    row1[21]='V';
    row1[22]='W';
    row1[23]='X';

    row1[24]='Y';
    row1[25]='Z';
    row1[26]='#';  
  
    var alphaContainer=    document.getElementById('author_alphabetic');
    alphaContainer.innerHTML="<ul>";
    for (itr=0;itr<=26;itr++)    {
        alphaContainer.innerHTML+='<li><a href="#" title="'+ row1[itr]+
        '"><span class="first"><span>'+ row1[itr] +
        '</span></span></a></li>';
    }
        alphaContainer.innerHTML+="</ul>";
        return true;
}

Copy and Paste the following function into the onload event of your form 

loadAlpha()

Now save and preview your form on the  browser. You will get a nice listing of alphabets.

How ever it looks better in fire fox than in IE

Hope this helps