Is it possible to import and export to a Excel Spreadsheet

Jaziar
Jaziar used Ask the Experts™
on
I have a database that people are wanting to import infomation to and from a spreadsheet.  Is this possible.  This is just a question - if it is I will sumbit A new question with a bunch of points and more details.  I just want to know if it is possible and hard is it to do?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Its easy and very much possible to do.   Just create a view with whatever field you want to export as columns.  And use File Export option.  Save the file as .wk4 extension and open it with excel.

You can add pts to this question itself if you are satisfied though.

;-)
Arun.
Yes it is possible. Either you can use OLE or convert the spreadsheet into csv file and write a file stream routine to input those values into your docs.

~Hemanth
Something to ponder if you need to code.  Here is something i got from my old system....


Sub ExportToExcel
      
      On Error Goto ErrorHandler
      
      Dim ws As New NotesUIWorkSpace
      Dim uiView As NotesUIView
      Dim vw As NotesView
      Dim doc As NotesDocument      
      Dim oexcel As Variant
      Dim oworkbook As Variant
      Dim xlSheet As Variant
      
      Dim row As Integer
      Dim col As Integer      
      Dim hdg List As String
      Dim leng List As Variant
      Dim fileName As String
      
      Set uiView = ws.CurrentView
      Set vw = uiView.View
      Set doc = vw.getfirstdocument
      
      hdg(0) = "Dealer Name"
      hdg(1) ="Dealer Legal Name"
      hdg(2)="Dealer Code"
      hdg(3)="Location Address"
      hdg(4)="Zip Code"
      hdg(5)="City"
      hdg(6)="State"
      hdg(7)="Phone"
      hdg(8)="Fax"
      hdg(9)="President Name"
      hdg(10)="Sales Mgr. Name"
      hdg(11)="Contract Signed"
      hdg(12)="DC Vans Regional Sales Mgr."
      
      leng(0) = 38.5
      leng(1) = 38.5
      leng(2) = 9.5
      leng(3) = 28.0
      leng(4) = 9.0
      leng(5) = 18.5
      leng(6) = 7.0
      leng(7) = 13.67      
      leng(8) = 13.67
      leng(9) = 17.5
      leng(10) = 14.0
      leng(11) = 12.5
      leng(12) = 19.33                                    
      
'      fileName$ = Environ$("APPDATA") & "\Export.xls"
'      fileName$ = Inputbox$( |The file will be extracted in the following location.  
'Please change it as applicable| , "Export Location" , fileName$  )
      
      Set oexcel = createobject("Excel.Application")
      Set oworkbook = oexcel.WorkBooks
      oworkbook.Add
      
      Set xlSheet = oexcel.Workbooks(1).Worksheets(1)
      
      col = 1
      row = 1
      Forall h In hdg            
            
            oexcel.cells(row,col).columnwidth = leng( col - 1) ' Column width
            oexcel.Columns(col).VerticalAlignment = 1
            oexcel.Cells(row, col).Font.Underline = 2 ' UnderLining the Heading            
            oexcel.Cells(row, col).Font.Bold =  True ' Bold
            oexcel.cells(row,col).WrapText = True 'Wrap Text
            oexcel.Cells(row,col) = Ucase(h) ' UpperCase Heading
            
            
            col = col + 1
      End Forall
      
      row = row + 2
      col = 1
      
      Do While Not doc Is Nothing
            
            If Trim(doc.TerminatedContract_1_2(0)) = "" Then
                  
                  oexcel.cells(row,col) =  doc.ce_marca_1_1_2_1(0)            
                  oexcel.cells(row,col).WrapText = True
                  col = col + 1
                  oexcel.cells(row,col) = doc.LegalName_1_2(0)
                  oexcel.cells(row,col).WrapText = True
                  col = col + 1
                  oexcel.cells(row,col) = doc.ce_marca_1_1_2(0)
                  oexcel.cells(row,col).WrapText = True
                  col = col + 1
                  oexcel.cells(row,col) = doc.ce_dir_1_1_1(0)
                  oexcel.cells(row,col).WrapText = True
                  col = col + 1
                  oexcel.cells(row,col) = doc.ce_cp_1_2_1(0)
                  oexcel.cells(row,col).WrapText = True
                  col = col + 1
                  oexcel.cells(row,col) = doc.ce_loc_1_1_1(0)
                  oexcel.cells(row,col).WrapText = True
                  col = col + 1
                  oexcel.cells(row,col) = doc.ce_pro_1_2_1(0)
                  oexcel.cells(row,col).WrapText = True
                  col = col + 1
                  oexcel.cells(row,col) = doc.ce_tel_1_1_1(0)
                  oexcel.cells(row,col).WrapText = True
                  col = col + 1
                  oexcel.cells(row,col) = doc.ce_fax_1_1_1(0)
                  oexcel.cells(row,col).WrapText = True
                  col = col + 1
                  oexcel.cells(row,col) = doc.ce_gerente_1_1_1(0)
                  oexcel.cells(row,col).WrapText = True
                  col = col + 1
                  oexcel.cells(row,col) = doc.SalesManager_1_1(0)
                  oexcel.cells(row,col).WrapText = True
                  col = col + 1
                  oexcel.cells(row,col) = doc.SignedContract_1_2(0)
                  oexcel.cells(row,col).WrapText = True            
                  col = col + 1
                  oexcel.cells(row,col) = doc.ce_resvent_au_1_1_1(0)                                                            
                  oexcel.cells(row,col).WrapText = True
                  row = row+1
                  col = 1
            End If
            Set doc = vw.getnextdocument(doc)
      Loop
      
'Display a message telling the user their file is ready
'      Msgbox "Excel file available at : " & fileName$ , 64, "DC Vans Dealers : Your Excel file is ready!"
'      oexcel.activeworkbook.saveas fileName$
'Show the excel application we started in the background
      oexcel.visible = True            
      
      
      oexcel.activeworkbook.close(1)
      
'      Call oexcel.quit
      Set oexcel = Nothing      
      
      Exit Sub      
ErrorHandler:
      Messagebox Error$ , 64 , Err()
      Exit Sub
End Sub
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Barry TiceBusiness Analyst
Commented:
I recently did this. In my case I've got a series of stuff that will be used for time-card entries, and the projects and assignments available will be changed often.

It's really a pain, because in our case we have to punch numbers in on a phone line for our actual time cards, but local managers want different reports of what people are working on that give more information than the phone system can provide. So all my coworkers have to enter our time in two places, plus doing a weekly status report saying what they worked on.

What I ended up doing is creating a profile document that let the database administrator define which fields are in which columns in the spreadsheet being imported: eight dialog-list fields called Column_1 through Column_8, where the administrator says which field is in that column. The querysave verifies that none of these are set the same, and then sets the values of eight other fields to indicate which numbered column holds that data.

The import action then cross-references the profile document to determine which column is which from the spreadsheet it will be importing from. This way if different managers end up with different spreadsheets for importing the phone-system time-card numbers, they can redefine the import order without me having to recode the import.

In this particular case, if the code contained in the spreadsheet already exists in the Notes database, it deletes the Notes record and imports a new version of it. (Actually, it doesn't delete it because few users have the authority to delete things. Instead, it sets a "Spike" field, which is what I use to denote a record as deleted.) Note that it uses a hidden view, "(TES Codes)" to lookup which records already exist in the database. (TES codes will be unique to each record.) There's another version of the import that skips importing the record if it finds a duplicate, but 95% of the code is the same.

Here is the code. It's run from a shared action button available from several views:
==== BEGIN PASTE ====
Sub Click(Source As Button)
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim doc As NotesDocument
      Dim pDoc As NotesDocument
      Dim workspace As New NotesUIWorkspace
      Dim view As NotesView
      Dim otherDoc As NotesDocument
      Dim Excel As Variant
      Dim xlWorkbook As Variant
      Dim xlSheet As Variant
      Dim fileName As String
      Dim xlRow As Integer
      Dim emptyCount As Integer
      Dim strText As String
      Dim intTRGProjectName As Integer
      Dim intTRGTaskNumber As Integer
      Dim intGPQTaskNumber As Integer
      Dim intTaskDescription As Integer
      Dim intDepartment As Integer
      Dim intProject As Integer
      Dim intAssignment As Integer
      Dim intTESCode As Integer
      Dim item As NotesItem
      
      ' Get and validate file name
      fileName = Trim$(Inputbox$("Enter the name path and name of the spreadsheet you wish to import:", "Select Spreadsheet"))
      If fileName = "" Then Exit Sub
      If Dir$(fileName) = "" Then
            Messagebox "You have provided an invalid file name.", 16, "Import Canceled"
            Exit Sub
      End If
      ' get column numbers from profile document
      Set db = session.CurrentDatabase
      Set view = db.GetView("(TES Codes)")
      Set pDoc = db.GetProfileDocument("_Profile Import Columns")
      intTRGProjectName = Val(pDoc.TRGProjectName(0))
      intTRGTaskNumber = Val(pDoc.TRGTaskNumber(0))
      intGPQTaskNumber = Val(pDoc.GPQTaskNumber(0))
      intTaskDescription = Val(pDoc.TaskDescription(0))
      intDepartment = Val(pDoc.Department(0))
      intProject = Val(pDoc.Project(0))
      intAssignment = Val(pDoc.Assignment(0))
      intTESCode = Val(pDoc.TESCode(0))
      
      ' Create and get hold of Excel spreadsheet
      Set Excel = CreateObject("Excel.Application")
      Excel.Visible = False
      Excel.Workbooks.Open fileName
      Set xlWorkbook = Excel.ActiveWorkbook
      Set xlSheet = xlWorkbook.ActiveSheet
      
      ' Extract entries
      xlRow = 2      ' Skip header/title line
      emptyCount = 0      ' How many blank lines have we hit in a row? If 5, stop importing
      Do While emptyCount < 5
            strText = xlSheet.Cells(xlRow, intTESCode).Value      ' This is the TES Code
            If Trim$(strText) >< "" Then
                  ' We may have a keeper
                  Set otherDoc = view.GetDocumentByKey(strText)
                  If Not otherDoc Is Nothing Then
                        ' Doc already existed. "Spike" it.
                        otherDoc.Spike = "1"
                        Call otherDoc.Save(True, False)
                  End If
                  ' Save the new document
                  Set doc = db.CreateDocument
                  doc.Form = "TES Entry"
                  doc.ProjectName = xlSheet.Cells(xlRow, intTRGProjectName).Value
                  doc.TRGTaskNumber = xlSheet.Cells(xlRow, intTRGTaskNumber).Value
                  doc.GPQTaskNumber = xlSheet.Cells(xlRow, intGPQTaskNumber).Value
                  doc.TaskDescription = xlSheet.Cells(xlRow, intTaskDescription).Value
                  doc.Department = xlSheet.Cells(xlRow, intDepartment).Value
                  doc.Project = xlSheet.Cells(xlRow, intProject).Value
                  doc.Assignment = xlSheet.Cells(xlRow, intAssignment).Value
                  doc.TESCode = strText
                  Set item = New NotesItem(doc, "AuthorsField", "[Admin]")
                  item.AppendToTextList("[Manager]")
                  item.IsAuthors = True
                  Call doc.Save(True, False)
                  
                  emptyCount = 0
            Else
                  emptyCount = emptyCount + 1
            End If
            xlRow = xlRow + 1
            If xlRow\100 = xlRow/100 Then Beep     ' Beep to let people know it's working
      Loop
      
      
      xlWorkbook.Close False            ' Close without saving
      Excel.Quit
      Set Excel = Nothing
      Set xlWorkbook = Nothing
      Set xlSheet = Nothing
      
      Call workspace.ViewRefresh
End Sub
===== END PASTE =====

Running locally, this imports about 20-25 records per second. Hope this helps.

-- b.r.t.
Barry TiceBusiness Analyst

Commented:
Note that this code puts two roles in the AuthorsField field, which is of course an Authors field. (The field is called AuthorsField instead of Authors to avoid conflicts with doc.Authors in other scripts.) Users with those roles assigned in the ACL have the authority to see these views and to perform the imports, or to edit the records once they're imported.

-- b.r.t.

Commented:
Wow, you people know how to make a point.  Even a small one in a big way.  'scuse me, gotta make some molehills out of a mountain.

Author

Commented:
Im still here - Not addressing that database at this time - but when I get to it - I will use this thread, so I am uping the points.
Thats good Jaziar, Keep me in mind to accept the answer from !

;-P

Barry TiceBusiness Analyst

Commented:
I'm in no hurry either. Take your time.
-- b.r.t.
Commented:
What if I'm in a hurry?




:)
Me too...

Commented:
Arun,

You would definitely be me too and the gimme gimmes.  Because somebody does it better.

qwaletee

Commented:
Just kidding.  Anyone catch the obscure reference?
Barry TiceBusiness Analyst

Commented:
Of "me to and the gimme gimmes"? Possibly the best cover band of all times?
Nope. I missed that one.

Commented:
Me FIRST and the Gimme Gimmes.  And, of course, as you say, Nobody Does It Better.

Best punk cover anyway :)

My fav is Puff the Magic Dragon.  Blows my mind.
Barry TiceBusiness Analyst

Commented:
Yes, my bad on the First.

I don't have Puff in my collection, I'm afraid. "Rainbow Connection" has always been a favorite, but I have a special place in my heart for the Paul Simon stuff. Meanwhile, "California Dreaming" always calls the Dead Milkmen's "Punk Rock Girl" to mind, which is usually a good thing. It's just too hard to pick a single favorite.

This is sounding more and more like a Lounge conversation.

Commented:
I dunno... punk rock lounge?  Seems like an oxymoron.

Author

Commented:
This problem has been kicked out - I am going to just split up the points

Commented:
I didn't deserve any points for this.

Author

Commented:
I did not use anyone's comments - I gave a greater number of points to the ones with the most text or who I thought gave it the college try.  I want everyone that takes the time to comment be able to get something for thier time - even though it may not lead to correct answers.  I have enjoyed the services of this forum many times and I am sure each and everyone of you have at one time answered a question of mine.  If indeed I did use someones comment that would have gotten a the points.

Commented:
What I meant was, i didn't even say anything technical here.  All my comments were about punk rock, people who act like a punk rock band, or a snide comment about mountains and molehills.

Maybe I'll just take the points for General Purpose Creativity.  Or language creativity.

Author

Commented:
This comment gave me a laugh

Wow, you people know how to make a point.  Even a small one in a big way.  'scuse me, gotta make some molehills out of a mountain.

So I gave points for that

Commented:
I had myself LOL when I wrote that.  Or was I laughing at the poor blokes who dribbled this one out to eight pages?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial