Notes of my Lotus Notes + my Datastage hunt: 2012

Friday, December 21, 2012

Notes document to HTML Excel sheet - Exporting data - web form

Code for exporting data from notes documents to an excel sheet and show it in html form of excel sheet:

Sub Initialize
On Error Goto GenErr
Print "content-type: Application/msexcel"
Print "Content-Disposition: attachment; filename=Report1.xls"
Print "<html>"
Print "<table border=0>"
Print "<tr><td colspan=5><center>"
Print "<b><span id='RptHead'> Report For Test</span></b>"
Print "</center></td></tr>"
Print "</table>"

Dim LOSession As New NotesSession
Dim LOVw As NotesView
Dim LODb As NotesDatabase
Dim LODoc As notesdocument
Dim LTDate As String
Dim LVNo As Variant
Set LODoc = LOSession.DocumentContext
'LTDate=LODoc.Query(0)

Set LODb=LOSession.CurrentDatabase
Set LOVw = LODb.GetView( "vwExcel" )
Set LODoc = LOVw.GetFirstDocument

Print "<table border=2 bordercolor=Black>"
Print "<tr>"
Print "<td>Sl No </td>"
Print "<td> Initiators</td>"
Print "<td>KLID </td>"
Print "<td>SBU </td>"

Print "<td>Project Short Name </td>"
Print "<td>Product Group </td>"
Print "<td>Product </td>"
Print "<td>Function </td>"
Print "<td>LOB </td>"
Print "<td>Rating by Experts </td>"
Print "<td>Project Manager </td>"
Print "<td>Proposal Manager </td>"

Print "<td>Process Area </td>"
Print "<td>Process Item </td>"
Print "<td>Key Learning Title</td>"
Print "<td>Problem Description </td>"
Print "<td>Analysis </td>"
Print "<td>Solution Adopted </td>"
Print "<td>Learnings </td>"
Print "<td>Implication </td>"
Print "<td>DO's List</td>"
Print "<td>DONOT's List </td>"
Print "<td>References </td>"

Print "<td>Approver </td>"
Print "<td>Comments </td>"

Print "</tr>"
Print "<tr>"
Print "</tr>"

j=1
While Not ( LODoc Is Nothing )

  Print "<tr>"
  Dim LVTemp As Variant
  Print "<td>" & j &"</td>"
  For i=0 To 23
   'LVTemp = LODoc.ColumnValues( i )
   'Msgbox LVTemp
   'Print "<td>" & LVTemp & "</td>"

   Print "<td>" & LODoc.ColumnValues( i ) & "</td>"


  Next
  Print "</tr>"
  j=j+1

  Set LODoc = LOVw.GetNextDocument( LODoc )
Wend

Print "</table>"
Exit Sub
GenErr:
Msgbox "Error on SaveKtips agent , Error On line " & Erl & " Error is " & Error & " Err is " & Err
Exit Sub

End Sub

Notes document to Excel sheet - Exporting data - client form

Code for exporting data from notes documents to an excel sheet:

Sub Initialize
Dim ws As New Notesuiworkspace
Dim session As New NotesSession
Dim view As NotesView
Dim db As NotesDatabase
Dim doc As notesdocument
Dim psno,mth,yr,filename As String

Set db=session.CurrentDatabase
Set view = db.GetView( "Vashi_emp" )
Set doc = view.GetFirstDocument

CreateExcelObject = True
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
  sMessage = "Could not create spreadsheet." & Chr$( 10 ) & _
  "Make sure Excel is installed on this computer."
  Msgbox sMessage, 16, "Creation of Spreadsheet Object Failed"
  CreateExcelObject = False

End If
xlApp.DisplayAlerts = False
Set xlworkbook = xlApp.Workbooks.Add
Set xlSheet=xlworkbook.ActiveSheet
xlSheet.Name="ATTENDANCEREPORT"

row=1
col=1
xlapp.visible = True
Title = " Attendance Report"
xlsheet.Cells(row,col+2).value=Title
xlApp.Rows("1:1").select
xlapp.Selection.font.Bold=True
xlapp.Selection.font.underline=True
col = 1

xlsheet.Cells(row+2,col).value="PSNO"
xlApp.Rows("3:3").select
xlapp.Selection.font.Italic=True
xlapp.selection.Font.Name="Arial Black"
xlapp.selection.Font.size ="10"
xlapp.Selection.font.Bold=False
Print row
xlsheet.Cells(row+2,col+1).value="name"
xlApp.Rows("3:3").select
xlapp.Selection.font.Italic=True
xlapp.selection.Font.Name="Arial Black"
xlApp.selection.Columns.Autofit
xlapp.selection.Font.size ="10"
xlapp.Selection.font.Bold=False
Print col
xlsheet.Cells(row+2,col+2).value="dlgeusername"
xlApp.Rows("3:3").select
xlapp.Selection.font.Italic=True
xlapp.selection.Font.Name="Arial Black"
xlApp.selection.Columns.Autofit
xlapp.selection.Font.size ="10"
xlapp.Selection.font.Bold=False
xlsheet.Cells(row+2,col+3).value="status"
xlApp.Rows("3:3").select
xlapp.Selection.font.Italic=True
xlapp.selection.Font.Name="Arial Black"
xlApp.selection.Columns.Autofit
xlapp.selection.Font.size ="10"
xlapp.Selection.font.Bold=False
xlsheet.Cells(row+2,col+4).value="costcode"
xlApp.Rows("3:3").select
xlapp.Selection.font.Italic=True
xlapp.selection.Font.Name="Arial Black"
xlApp.selection.Columns.Autofit
xlapp.selection.Font.size ="10"
xlapp.Selection.font.Bold=False
xlsheet.Cells(row+2,col+5).value="cader"
xlApp.Rows("3:3").select
xlapp.Selection.font.Italic=True
xlapp.selection.Font.Name="Arial Black"
xlApp.selection.Columns.Autofit
xlapp.selection.Font.size ="10"
xlapp.Selection.font.Bold=False
xlsheet.Cells(row+2,col+6).value="location"
xlApp.Rows("3:3").select
xlapp.Selection.font.Italic=True
xlapp.selection.Font.Name="Arial Black"
xlApp.selection.Columns.Autofit
xlapp.selection.Font.size ="10"
xlapp.Selection.font.Bold=False
xlsheet.Cells(row+2,col+7).value="joindate"
xlApp.Rows("3:3").select
xlapp.Selection.font.Italic=True
xlapp.selection.Font.Name="Arial Black"
xlApp.selection.Columns.Autofit
xlapp.selection.Font.size ="10"
xlapp.Selection.font.Bold=False
xlsheet.Cells(row+2,col+8).value="confirmdate"
xlApp.Rows("3:3").select
xlapp.Selection.font.Italic=True
xlapp.selection.Font.Name="Arial Black"
xlApp.selection.Columns.Autofit
xlapp.selection.Font.size ="10"
xlapp.Selection.font.Bold=False
xlsheet.Cells(row+2,col+9).value="deptname"
xlApp.Rows("3:3").select
xlapp.Selection.font.Italic=True
xlapp.selection.Font.Name="Arial Black"
xlApp.selection.Columns.Autofit
xlapp.selection.Font.size ="10"
xlapp.Selection.font.Bold=False
xlsheet.Cells(row+2,col+10).value="deptcode"
xlApp.Rows("3:3").select
xlapp.Selection.font.Italic=True
xlapp.selection.Font.Name="Arial Black"
xlApp.selection.Columns.Autofit
xlapp.selection.Font.size ="10"
xlapp.Selection.font.Bold=False

grow = row+3
Print grow
row = grow
col = 1
j=0

While Not ( doc Is Nothing )

  Dim currentProduct As Variant
  For i=0 To 8
   currentProduct = doc.ColumnValues( i )

   xlsheet.Cells(row+j,col+i).value=currentProduct
  Next
  j=j+1
  Set doc = view.GetNextDocument( doc )
Wend

End Sub

Excel sheet to notes Document - Importing Data

Importing excel sheet information into individual documents in LN client application:

Sub Initialize

Dim xlFilename As String
'xlFilename = "C:\Documents and Settings\ggouda\My Documents\EMP_WRS_DETAIL.xls"
'// This is the name of the Excel file that will be imported

xlFilename=Inputbox("Please enter path of the spreadsheet - Example: C:\Excel.xls" & Chr(10) &_
" C:\spreadsheet.xls", "File Path Inquiry Box", " enter path here ....")

Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Set db = session.CurrentDatabase
Set doc = New NotesDocument(db)
Dim One As String
Dim row As Integer
Dim written As Integer

'// Connect to Excel and open the file. Start pulling over the records.
Dim Excel As Variant
Dim xlWorkbook As Variant
Dim xlSheet As Variant
Print "Connecting to Excel..."
Set Excel = CreateObject( "Excel.Application.9" )
Excel.Visible = False '// Don't display the Excel window
Print "Opening " & xlFilename & "..."
Excel.Workbooks.Open xlFilename '// Open the Excel file
Set xlWorkbook = Excel.ActiveWorkbook
Set xlSheet = xlWorkbook.ActiveSheet
'// Cycle through the rows of the Excel file, pulling the data over to Notes
Goto Records
Print "Disconnecting from Excel..."
xlWorkbook.Close False '// Close the Excel file without saving (we made no changes)
Excel.Quit '// Close Excel
Set Excel = Nothing '// Free the memory
Print " " '// Clear the status line

Records:
row = 1 '// Integers intialize to zero
written = 0
Print "Starting import from Excel file..."
Do While True
Finish:
With xlSheet
row = row + 1
Set view = db.GetView("Import")
Set doc = db.CreateDocument '// Create a new doc
doc.Form = "Person"
doc.Employee_ID = .Cells( row, 1 ).Value
doc.Prj_Cost_Centre= .Cells(row, 2 ).Value
doc.WRS_Number= .Cells(row, 3).Value
doc.Proj_Title = .Cells( row, 4 ).Value
doc.Project_Location = .Cells(row, 7).Value
doc.Dispatcher = .Cells( row, 8).Value
doc.Project_manager = .Cells(row, 9).Value
doc.Wk_Ending = .Cells( row, 10).Value
doc.Employee_DOJ = .Cells( row, 11).Value
doc.Employee_Name = .Cells( row, 12).Value
doc.Employee_Role = .Cells( row, 13).Value
doc.ST_Hrs = .Cells( row, 14).Value
doc.OT_Hrs = .Cells( row, 15).Value
doc.Total_Hrs = .Cells( row, 16).Value
doc.Misc_Exp = .Cells( row, 17).Value
doc.Travel = .Cells( row, 18).Value
doc.Total_Bill = .Cells( row, 19).Value
Call doc.Save( True, True ) '// Save the new doc
written = written + 1
Print Str(written)
If .Cells( row, 1 ).Value = "" Then
Goto Done
End If
End With
Loop
Return
Done:
Messagebox "Import Complete - Total number of WRS documents imported ---> " & written

End Sub

To get the html code of an excel sheet

Let's say you have an excel sheet provided to you by your lead and he wants you to get similar look and feel using html code in your web application.

Here's a shortcut to get the html code of an excel sheet:

The OLE/COM commands which you will be utilizing are available in the version of Excel you are using.

Hit alt + shift + F11 key to pull up the Microsoft Script Editor and then Ctrl+Alt+J to bring up the Object Browser, this is the cheap way to find out what is available for you to code with.

Function to catch Enter key press

Function to Catch Enter Key press and accordingly perform the required task:

function catchEnter(){
        if(window.event.keyCode == 13){
                document.all.S.click();
        }
}

Validation date and number through regular expression

Validation of number through regular expression:

function validateNumber( f ) {
        var regex = new RegExp();
        //regex = /^\d+$/;
        regex = /(^-?\d\d*\.\d*$)|(^-?\d\d*$)|(^-?\.\d\d*$)/
        if ( regex.test( f.value ) ) {
                return;
        } else {
                alert("Please enter a number value");
                f.value = "";
                f.focus();
                return;
        }
}

Validation of date through regular expression:

function CheckDate(dateV)
{
        var dateStr = dateV.value;
        if(dateStr==""){
                return;
        }
        if(dateStr=="tbd" || dateStr=="TBD"){
                return;
        }
        isValidDate(dateV, dateStr);
}

function isValidDate(dateV, dateStr)
{
        var datePat = /^(\d{1,2})(\/|-)(\d{1,2})\2(\d{2}|\d{4})$/;
        var matchArray = dateStr.match(datePat);
        if (matchArray == null) {
                alert("Date is not in a valid format.")
                dateV.focus();
                return;
        }
        month = matchArray[1]; // parse date into variables
        day = matchArray[3];
        year = matchArray[4];
        if (month < 1 || month > 12) {
                alert("Month must be between 1 and 12.");
                dateV.focus();
                return;
        }
        if (day < 1 || day > 31) {
                        alert("Day must be between 1 and 31.");
                        dateV.focus();
                return;
        }
        if ((month==4 || month==6 || month==9 || month==11) && day==31) {
                alert("Month "+month+" doesn't have 31 days!");
                dateV.focus();
                return;
        }
        if (month == 2) {
                var isleap = (year % 4 == 0 && (year % 100 != 0 || year % 400 == 0));
                if (day>29 || (day==29 && !isleap)) {
                        alert("February " + year + " doesn't have " + day + " days!");
                        dateV.focus();
                        return false;
                }
        }
        return;
}

Extracting attachments from web application

Here's a code for extracting attachments/files from documents in LN web application:

Sub Initialize
        On Error Goto errhandle
        Dim session As New NotesSession
        Dim db As notesdatabase
        Dim view As NotesView
        Dim doc As NotesDocument

        Dim object As NotesEmbeddedObject

        Dim collection As NotesDocumentCollection
        Dim currentResponse As NotesDocument

        Dim tempDirectory, actualDir As String
        Dim nameAtt, result As Variant
        Dim numAtt, count As Integer

        tempDirectory="P:\\Gayatri\\Temp\\"
'       tempDirectory="O:\\CITC\\ECAS\\GRCE\\"
        Set db = session.CurrentDatabase
        Set view = db.GetView("KBByCategory1")

        numAtt = 0
        count=0
        cnt = 0

        Dim myNames() As String

        Set doc = view.GetFirstDocument

        While Not (doc Is Nothing)

                result = Evaluate("@Attachments", doc)
                nameAtt = Evaluate("@AttachmentNames", doc)
                numAtt = result(0)

                If numAtt > 0 Then
                        For i=0 To numAtt-1 Step 1
                                If nameAtt(0) <> "" Then
                                        Set object = doc.GetAttachment( nameAtt( i ) )
                                        indx = 0
                                        flag = "no"
                                        For k = 1 To cnt Step 1
                                                If nameAtt( i ) = myNames( k ) Then
                                                        indx = indx + 1
                                                        flag = "yes"
                                                End If
                                        Next
                                        If flag = "yes" Then
                                                filename = Left$(nameAtt( i ),Len(nameAtt( i ))-4) + "_" + indx +       Right$(nameAtt( i ), 4)
                                        Else
                                                filename = nameAtt( i )
                                        End If

                                        actualDir = tempDirectory & filename
                                        Call object.ExtractFile(actualDir)
                                        count = count + 1

                                End If
                        Next
                End If

                If numAtt > 0 Then
                        Redim Preserve myNames(cnt+numAtt)
                        For j = 1 To numAtt
                                myNames(cnt + j) = nameAtt( j - 1 )
                        Next
                        cnt = cnt + numAtt
                End If

                '------------------------------- Response ------------------
                Set collection = doc.Responses
                Set currentResponse = collection.GetFirstDocument

                While Not ( currentResponse Is Nothing )

                        result = Evaluate("@Attachments", currentResponse)
                        nameAtt = Evaluate("@AttachmentNames", currentResponse)
                        numAtt = result(0)

                        If numAtt > 0 Then
                                For i=0 To numAtt-1 Step 1
                                        If nameAtt(0) <> "" Then

                                                Set object = currentResponse.GetAttachment( nameAtt( i ) )
                                                indx = 0
                                                flag = "no"
                                                For k = 1 To cnt Step 1
                                                        If nameAtt( i ) = myNames( k ) Then
                                                                indx = indx + 1
                                                                flag = "yes"
                                                        End If
                                                Next
                                                If flag = "yes" Then
                                                        filename = Left$(nameAtt( i ),Len(nameAtt( i ))-4) + "_" + indx +       Right$(nameAtt( i ), 4)
                                                Else
                                                        filename = nameAtt( i )
                                                End If

                                                actualDir = tempDirectory & "Responses\\" & filename
                                                Call object.ExtractFile(actualDir)
                                                count = count + 1
                                        End If
                                Next
                        End If

                        If numAtt > 0 Then
                                Redim Preserve myNames(cnt+numAtt)
                                For j = 1 To numAtt
                                        myNames(cnt + j) = nameAtt( j - 1 )
                                Next
                                cnt = cnt + numAtt
                        End If

                        Set currentResponse = collection.GetNextDocument( currentResponse )

                Wend

                Set doc = view.GetNextDocument (doc)

        Wend
        Msgbox cnt
        For i = 1 To 21 Step 1
                Msgbox myNames(i)
        Next
        Msgbox count
        Exit Sub
errhandle:
        Messagebox " Error line: " & Erl() & " error : " & Err() & " err() : " & Err()
End Sub

Notes of my Lotus Notes + my Datastage hunt

Pages

Notes of my Lotus Notes Headline Animator

Search My Blog