Импорт из Excel или Txt в БД Лотус

Тема в разделе "Lotus - Программирование", создана пользователем Oksana, 30 мар 2007.

  1. Oksana

    Oksana Lotus team
    Lotus team

    Регистрация:
    10 янв 2006
    Сообщения:
    330
    Симпатии:
    0
    Вроде тема поднимлась так или иначе, но то что нужно не нашла.
    Пожалуйста, поделитесь скриптом, который может имортировать файл Excel (или текстовый с разделителями) в документы базы Лотус.

    Особо волнует перенос полей типа дата, чтобы корректно в лотус вставились.

    Заранее благодарю.
     
  2. Iwer

    Iwer Гость

    Народ! Вот вытащил с Опен Нтф скрипт по импорту из Excel в Lotus.
    Код (LotusScript):
    Option Public
    Option Declare
    Option Compare Nocase
    %INCLUDE "LSCONST.LSS"
    Sub Initialize
    'from http://www.openntf.org/Projects/codebin/codebin.nsf/CodeSearch/DCD5A132F75581698625726700715FCA
    'Version 1.2, plus a few improvements
    'code from David Moore david.james.moore@gmail.com
    'This code works with Win 98, Win2000, WinXP
    'Insert the code into an action button in a view or make it available
    'from the action menu because it uses NotesUIWorkspace.
    'will import from Excel up to 256 columns by 65,536 rows
    Dim astrFields As Variant
    Dim session As New NotesSession
    Dim uiws As New NotesUIWorkspace
    Dim form As NotesForm
    Dim db As NotesDatabase
    Dim doc As NotesDocument
    Dim item As NotesItem
    Dim row As Integer
    Dim xlFilename As String
    Dim xlsApp As Variant
    Dim xlsWorkBook As Variant
    Dim xlsSheet As Variant
    Dim rows As Long
    Dim cols As Integer
    Dim x As Integer
    Dim itemName As String
    Dim flag As Integer
    Dim formAlias As String
    Dim sortEval As String
    Dim sortedList As Variant
    Dim indexLo As Long
    Dim indexHi As Long
    Dim fn As Variant
    Dim msg As String
    Dim i As Long
    Dim formname As String
    On Error Goto ErrorHandler
    Set db = session.CurrentDatabase
    fn= uiws.Prompt(1, "Reminder- Excel Worksheet Setup", "Make sure that the first row of your worksheet contains the EXACT Notes document field names from your form.")
    'Get Excel file name
    fn =uiws.OpenFileDialog(False, "Select the Excel File to Import", "Excel files | *.xls", "c:\My Documents")
    xlFilename = Cstr(fn(0)) ' This is the name of the Excel file that will be imported
    'Get list of form names
    Print "Preparing List of Database Forms ..."
    Redim formlist(Ubound(db.Forms))
    For x = 0 To Ubound(db.Forms)
    formlist(x)=db.Forms(x).name
    Print "Preparing List of Database Forms ..."& Cstr(x)
    Next
    'Sort the form names for the dialog box
    indexLo= Lbound(formlist)
    indexHi= Ubound(formlist)
    Call QuickSort(formlist , indexLo, indexHi)
    'Choose the form to use for import
    formname = uiws.Prompt(4, "Choose Import Form", "Please select which form is to be used for this input.", formlist(0), formlist)
    If formname= "" Then Exit Sub
    'Get the form object so that we can check field names
    Set form= db.GetForm(formname)
    'If the form has an alias, use it to select the form
    If Not Isempty(form.Aliases) Then formname = form.Aliases(Ubound(form.Aliases))
    'Next we connect to Excel and open the file. Then start pulling over the records.
    Print "Connecting to Excel..."
    ' Create the excel object
    Set xlsApp = CreateObject("Excel.Application")
    'Open the file
    Print "Opening the file : " & xlfilename
    xlsApp.Workbooks.Open xlfilename
    Set xlsWorkBook = xlsApp.ActiveWorkbook
    Set xlsSheet = xlsWorkBook.ActiveSheet
    xlsApp.Visible = False ' Do not show Excel to user
    xlsSheet.Cells.SpecialCells(11).Activate
    rows = xlsApp.ActiveWindow.ActiveCell.Row ' Number of rows to process
    cols = xlsApp.ActiveWindow.ActiveCell.Column ' Number of columns to process
    'Make sure we start at row 0
    row = 0
    Print "Starting import from Excel file..."
    Do While True
    row = row + 1
    'Check to make sure we did not run out of rows
    If row= rows+1 Then Goto Done
    'field definitions for notes come from first row (row, column)
    If row=1 Then
    astrFields = form.Fields
    Redim fd(1 To cols) As String
    For i=1 To cols
    'the replace function used here removes spaces from the field definitions in the first row
    fd(i) = xlsSheet.Cells( row, i ).Value
    If Len(fd(i)) Then
    fd(i)= Replace(fd(i), " ", "")
    If Isnull(Arraygetindex(astrFields, fd(i))) Then
    msg="The field name "& fd(i) &" does not appear in the form you have chosen."
    If Msgbox(msg, MB_OKCANCEL + MB_ICONEXCLAMATION + MB_DEFBUTTON2) <> 1 Then
    Goto Done
    End If
    End If 'flag=1
    End If
    Next 'For i=1 To cols
    Else 'row isn't = 1
    'Import each row into a new document
    'Create a new doc
    Set doc = db.CreateDocument
    doc.Form = FormName
    For i= 1 To cols
    If Len(fd(i)) Then _
    Set item = doc.ReplaceItemValue( fd(i), xlsSheet.Cells( row, i ).Value )
    Next ' i= 1 To cols
    'Save the new doc
    Call doc.Save( True, True )
    End If 'Not row = 1 Then
    Print "Processing document number "& Cstr(row) & " of " & Cstr(rows)
    Loop 'Do while true
    Done:
    On Error Resume Next 'protect against infinite error handing loops
    Print "Disconnecting from Excel..."
    If Not xlsWorkbook Is Nothing Then
    xlsWorkbook.Close False
    End If ' Not xlsWorkbook Is Nothing
    If Not xlsApp Is Nothing Then
    xlsApp.DisplayAlerts = False
    xlsApp.Quit
    Set xlsApp = Nothing
    End If 'Not xlsApp Is Nothing
    'Clear the status line
    Print
    Exit Sub
    ErrorHandler:
    Select Case Err
    Case 184
    Msgbox "No file chosen. Exiting Import."
    Print "No file chosen. Exiting Import."
    Resume Done
    Case 6
    Messagebox "Make sure that you do not have more than 65,536 rows of data to import." ,MB_OK+MB_ICONINFORMATION,"Error! "
    Print "Too many rows in Excel document. Exiting Import. Disconnecting from Excel..."
    Resume Done
    Case Else
    Msgbox "Lotus Notes Error # " & Err & ". Please contact your Notes administrator for help. Exiting Import."
    Print "Error # "& Err & " on line " & Erl & ": " & Error$
    Resume Done
    End Select
    End Sub
    Function QuickSort( anArray As Variant, indexLo As Long, indexHi As Long) As Variant
    Dim lo As Long
    Dim hi As Long
    Dim midValue As String
    Dim tmpValue As String
    lo = indexLo
    hi = indexHi
    If ( indexHi > indexLo) Then
    'get the middle element
    midValue = anArray( (indexLo + indexHi) /2)
    While ( lo <= hi )
    'find first element greater than middle
    While (lo < indexHi) And (anArray(lo) < midValue )
    lo = lo+1
    Wend
    'find first element smaller than middle
    While ( hi > indexLo ) And ( anArray(hi) > midValue )
    hi = hi - 1
    Wend
    'if the indexes have not crossed, swap
    If ( lo <= hi ) Then
    tmpValue = anArray(lo)
    anArray(lo) = anArray(hi)
    anArray(hi) = tmpValue
    lo = lo+1
    hi = hi -1
    End If
    Wend
    ' If the right index has not reached the left side of array, sort it again
    If( indexLo < hi ) Then
    Call QuickSort( anArray, indexLo, hi )
    End If
    'If the left index has not reached the right side of array, sort it again
    If( lo < indexHi ) Then
    Call QuickSort( anArray, lo, indexHi )
    End If
    End If
    QuickSort = anArray
    End Function


    Вот вопрос - как сделать импорт из КОНКРЕТНОЙ ячейки Excel в конткретное поле Notes
    Например из ячейки "B3" Excel в поле "Name" LN?
    Заранее спасибо.
     
    #2 Iwer, 15 окт 2007
    Последнее редактирование: 18 апр 2015
  3. Kizarek86

    Kizarek86 Lotus team
    Lotus team

    Регистрация:
    20 июл 2007
    Сообщения:
    857
    Симпатии:
    5
    Код (LotusScript):
    Dim xlFilename As String
    xlFilename = Inputbox$("Файл импорта по умолчанию.", "Файл для импорта Диск:\f2.XLS", "E:\1\1.xls")
    Dim session As New NotesSession
    Dim db As NotesDatabase
    Dim view As NotesView
    Dim doc As NotesDocument
    Set db = session.CurrentDatabase
    Dim row As Integer
    Dim written As Integer
    Dim number As Integer
    number = Inputbox$("Введите колличество строк", "Введите количество строк, содержащихся в импортируемом файле")
    Dim Excel As Variant
    Dim xlWorkbook As Variant
    Dim xlSheet As Variant
    Dim xlCells As Variant
    Set Excel = CreateObject("excel.application")
    Excel.Visible = False
    Print "Открыт файл " & xlFilename & "..."
    Excel.Workbooks.Open xlFilename '// открытие файла Excel
    Set xlWorkbook = Excel.ActiveWorkbook
    Set xlSheet = xlWorkbook.ActiveSheet
    Set xlCells = xlSheet.Cells
    row = 1
    written = 0
    Print "Starting import from Excel file..."
    Dim strName As String
    Add:
    row = row + 1
    written=written+1
    Print ("Импорт строки: "& Cstr(row) & " из: " &Cstr(number))
    Set view = db.GetView("По Датам")'представление где после импорта отображаются созданные документы
    strName = xlCells( row, 1). Value
    Set doc = view.GetDocumentByKey(strName ,True)
    If doc Is Nothing Then
    Set doc = db.CreateDocument
    [b]With doc
    .Form = "Form_Doc"
    .Otdel = xlCells(row, 1).Value
    .Dolzhnost = xlCells( row, 2 ).Value
    .Family = xlCells(row, 3).Value
    .Telefon = xlCells( row, 4).Value
    End With[/b]
    Else
    End If
    Call doc.Save( True, True )
    Set doc = Nothing
    If written < number Then Goto Add
    excel.quit
    Dim ws As New NotesUIWorkspace
    Call ws.ViewRefresh
    xCells (НомерСтроки,НомерСтолбца)
     
    #3 Kizarek86, 15 окт 2007
    Последнее редактирование модератором: 18 апр 2015
  4. Iwer

    Iwer Гость

    А! Так там циферки используются! А я-то наивный - генератор букв искал в коде. Спасибо, огромное! +1
    Кстати!
    В моей версии кода для открытия файла используется диалоговое окно, что предпочтительней, чем вводить путь к файлу с клавиатуры.

    fn =uiws.OpenFileDialog(False, "Select the Excel File to Import", "Excel files | *.xls", "c:\My Documents")
    xlFilename = Cstr(fn(0)) ' This is the name of the Excel file that will be imported
     
Загрузка...

Поделиться этой страницей