Showing posts with label Excel Sheet. Show all posts
Showing posts with label Excel Sheet. Show all posts

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
Labels: , , , ,

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
Labels: , , ,

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
Labels: , , ,