Notes of my Lotus Notes + my Datastage hunt: Excel Sheet

Showing posts with label Excel Sheet. Show all posts

Tuesday, August 2, 2016

Exporting/Logging e-mail details in some excel sheet from any Folder or Inbox

If you want all these details logged in some excel sheet

1. Select the documents and go to File menu > select Export...

2. Select folder in which you want to save.
Select Save as type as Comma separated value.
Give some name, say, attachment_logs.csv.
Then click on Export button.

3. It would bring another small dialogue box.
If you want details of selected mails then select How Much to Export as 'Selected documents'.
Otherwise you may select 'All documents'.
Export Character Set keep as 'Default character set'.
Check the Details to Incorporate as 'Include View titles'.
Otherwise just with the data you may get confused.
Especially if you are NOT a lotus notes developer, then you would get confused with the extra column values that get exported.

4. Once the export is completed you would see the below message in the bottom pane of your mailbox.
You may delete the extra columns that get populated in the .csv file and keep only those you need.

The result would look somewhat like this:

Hope this helps!! :)

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.

Notes of my Lotus Notes + my Datastage hunt

Pages

Notes of my Lotus Notes Headline Animator

Search My Blog