Excel Import Extension - filter import data

Question

Hi,

is it possible to filter data which is imorted with the excel import extension?

I don't want that I have the same entries in my table.

thanks

Answer 1

When the data is imported, you can delete any records you don't want, before you click the "save" button. the "added" records will all have an asterisk displayed on the left of the data.

You could also add a button that has code in its execute handler that loops through each "added" record & checks to see if it already exists, then programatically "deletes" it if it does.

Alternatively, if you're comfortable with code, you could modify the importer's code to do it during the actual import process. Sheel wouldn't have included code like that in his utility, because what's constitutes a "duplicate" could be different for every situation, so it wouldn't be all that easy to provide a way to detect that. Not impossible (if you look at his filter control), but not straightforward or easy.

---

Yann

(plus ça change, plus c'est la même chose!)

Answer 2

ok thanks.

hmm ... is it possible to skip the box which asks for the columns, because i always use the same csv file.

At the moment I prefer the button to check the added entries. Maybe you have some code examples for me how to loop through the added and existing entries? Because I am a beginner in vb :(

thanks :)

Answer 3

Again, it would be possible to skip the columns dialog IF you created a custom version of the utility, otherwise, no.

Don't worry about being a "beginner", everyone has to start somewhere.

This should work, but I didn't get a chance to test it.

VB:

    Private Sub YourButtonName_Execute()
      For Each entity In Me.YourCollectionName
        If (entity.Details.EntityState = EntityState.Added) _
        Then
          Dim propertyName = entity.YourPropertyName
          Dim exists = (Me.DataWorkspace.ApplicationData.YourTableName.Where( _
            Function(x) _
              x.PropertyName = propertyName _
              ).FirstOrDefault() _
            IsNot Nothing)

          If (exists = True) _
          Then
            entity.Delete()
          End If
        End If
      Next
    End Sub

C#:

	 private void YourButtonName_Execute()
	 {
			foreach (var entity in this.YourCollectionName)
			{
				if (entity.Details.EntityState == EntityState.Added)
				{
					var propertyName = entity.YourPropertyName;
					var exists = (this.DataWorkspace.ApplicationData.YourTableName.Where((x) => x.PropertyName == propertyName).FirstOrDefault() != null);

					if (exists == true)
					{
						entity.Delete();
					}
				}
			}
		}

Let me know if there's anything you need to ask about, or if there's a problem with the code. Obviously replace anything with "your" in the name with the appropriate name for your project.

---

Yann

(plus ça change, plus c'est la même chose!)

Answer 4

Thank you. The code works very well, but in one situation I have a problem. If I add the same data as I have already saved, the last entrie will not be deleted, because the index is out of range.

Do you have a solution for this problem?

thanks :)

Answer 5

What index can be out of range, we're not using any index? We're using a for each statement.

Your might need to list out your steps, what goes wrong & when.

---

Yann

(plus ça change, plus c'est la même chose!)

Answer 6

ok I try to give you the details :)

If I add the same (with same entries I mean, all entries are already saved in the table) entries and run the code there is at the last entrie while deleting the following error:

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

But If I add less then 6 same entries there is no problem, all for example 4 entries will be deleted with no problem.

I also used the debugger but I did not find an index or something else. So I cannot find the false value.

Maybe I post my code, because there is maybe the problem?

Private Sub checkData_Execute()
            ' Write your code here.
            For Each entity In Me.Kundes
                If (entity.Details.EntityState = EntityState.Added) Then
                    Dim propertyName = entity.Name
                    Dim exists = (Me.DataWorkspace.ApplicationData.Kundes.Where(Function(x) x.Name = propertyName).FirstOrDefault() IsNot Nothing)
                    If (exists = True) Then
                        entity.Delete()
                    End If

                End If
            Next

        End Sub

Thanks :)

 

 

 

Answer 7

Wow, it works for 4, but not for 6, that's bizarre.

I'll see if I can figure out what's going on.

---

Yann

(plus ça change, plus c'est la même chose!)

Answer 8

I may be barking up the wrong tree here but you appear to have a for ... Each loop and inside that you are deleting the iteration object. This usually causes problems because you are changing the collection you are iterating through.

If this is the case you have to change your for each loop to a for next one running backwards through the collection.

---

Simon Jones

Answer 9

ok ... this is maybe the problem.

I tried to figure out how I can implement the for next loop, but I'am a beginner in VB and I don't know how to do this.

Maybe you can help me with the for next loop to test it?

thanks.

Answer 10

hi!

maybe you can have 2 buttons, one for import from excel, second for remove duplicates?

in code for first button just import from excel, and in second something like this:

Private Sub Remove_dupes_Execute()

            Dim li As New List(Of RAS)

            Dim rasevi = Me.DataWorkspace.ApplicationData.Details.GetChanges.AddedEntities.OfType(Of RAS)()

            For Each r As RAS In rasevi

                Dim re = r

                Dim p = (From c In Me.DataWorkspace.ApplicationData.RASSet).Execute

                Dim q = p.FirstOrDefault(Function(o) o.Grupa = re.Grupa AndAlso o.Racunalo = re.Racunalo AndAlso _

                                             o.Operater = re.Operater AndAlso o.Pocetak_veze = re.Pocetak_veze AndAlso _

                                             o.Kraj_veze = re.Kraj_veze)

                If q IsNot Nothing Then

                    li.Add(re)

                End If

            Next            

            For Each l In li

                Dim lis = l

                Dim t = rasevi.FirstOrDefault(Function(o) o Is lis)

                If t IsNot Nothing Then

                    t.Delete()

                End If

            Next

  End Sub

 

can this help you? im not sure if this is ok, it works in my app, but there is probably better solution (cause im also beginner in all this)..

cheers!

Kivito

Answer 11

Simon,

You might be right, certainly normally that would be the case, but my understanding is that the entities don't *actually* get deleted, they get "marked" as deleted & won't actually be deleted until the "save" button is clicked. It's odd that it works' for 4, but not 6.

Kivito,

It was definitely my intention that the de-duplication process was a separate button to the import, but thinking about it now, I don't actually see why that would in fact be necessary.

---

Yann

(plus ça change, plus c'est la même chose!)

Answer 12

Where you have

For Each object As type In collection
 object.Delete
Next

you replace it with

 

For pointer = collection.Count -1 To 0 Step -1
 Dim object As type = collection.Item(pointer)
 object.Delete
Next

(Words in bold italics should be adapted to your particular situation).

Using this construct avoids the problem of deleting your iteration object and so inadvertently moving the iteration pointer on to the next item in the collection. This problem can lead to you skipping over items as you iterate or trying to move beyond the bounds of the collection.

---

Simon Jones

Answer 13

Yann,

Yes, it does depend on what the Delete command actually does but if it removes the object from the underlying collection, whether by hiding it or actually deleting it, this might explain the unusual symptoms displayed here.

---

Simon Jones

Answer 14

hi Yann,

in my case i did not know how to pause execution of "remove_dupes" to wait import_excel process to complete, so i put it on two buttons (like i wrote im newbie in all this)..

Kivito

 

Answer 15

Totally agree..

---

Yann

(plus ça change, plus c'est la même chose!)

Answer 16

There's nothing wrong with having two separate buttons (or suggesting it), as I said, that was my original intention. It was only when I saw your post that I wondered whether it could have in fact been all done with one button. But unless the import operation is done synchronously (which means code in the method that comes after the call to the import utility will not execute until the import is completed), the code de-dupe code could end up running before the import is done. So, it sounds like it may be a better idea to stick with two buttons after all.

---

Yann

(plus ça change, plus c'est la même chose!)

Answer 17

Oh, by the way, just in case anyone asks, you must count backwards through the collection, if you're removing items from it, not forwards even though it is easier to code. Removing items from the collection decrements collection.Count but the start and end points of the For Next loop are calculated once only at the start. (If you need to evaluate the end point every time round the loop then use a While or an Until loop, not a For Next.)

Many people have suggested that the For Each syntax is extended to add a Reversed or Backwards keyword to the end to make this kind of construct easier.

EG

 

For Each object As type In collection Backwards
 object.Delete
Next

 

---

Simon Jones

Answer 18

Hi Yann,

I don't know why but the entities are deleted immediately, they are not only marked, but I don't know why?

Answer 19

Ahhh! It must be because they've not been saved yet, that makes sense actually. So what Simon suggested (& you can use the method he supplied as a workaround) would be applicable then.

For pointer = collection.Count -1 To 0 Step -1
 Dim object As type = collection.Item(pointer)
 object.Delete
Next

---

Yann

(plus ça change, plus c'est la même chose!)

Answer 20

Hi Simon,

you are right, I have to begin my loop from backward, because the entities are deleted immediately and not only marked.

But I have a little problem with your code

Dim object As type = collection.Item(pointer)

I cannot use the .item method in my collection, so I cannot handle through my entries.

Maybe you have a solution for my problem?

thanks

Answer 21

In the original code, try replacing

 

 If (exists = True) _
 Then
  entity.Delete()
 End If

with

If (exists = True) _
Then
  Me.YourCollectionName.SelectedItem = entity
  Me.YourCollectionName.RemoveSelected()
End If

See if that helps, though it may suffer from the same problem. If that doesn't help, I'll write some code to import some record & test it myself. Sorry, I should have done that in the first place.

---

Yann

(plus ça change, plus c'est la même chose!)

Answer 22

Hi,

thank you, but know I get following error at

 Me.Items.RemoveSelected()

Error: Items is not a Member of 'LightSwitchApplication.EditableKundesGrid'

Ok, if you have time maybe you try it by yourselve :)

But know I am really a little bit frustrated, because I cannot loop backward through my entries :D

thanks :)

Answer 23

There will be an equivalent of the Item method in your collection, there has to be to be able to get one item from the collection by its index number.

Look through the methods in the Intellisense to find the best match.

---

Simon Jones

Answer 24

Sorry, "Items" was from my own code. I forgot to replace it with YourCollectionName as I had previously. Bad coincidence that my collection just happened to be called "Items".

So it should have been:

Me.YourCollectionName.RemoveSelected()

Sorry about that!

---

Yann

(plus ça change, plus c'est la même chose!)

Answer 25

Simon, I looked in the object browser for Microsoft.LightSwitch.Framework.Client.VisualCollection(Of T), & there wasn't anything there, no Item, nothing similar, the best I could find was SelectedItem, which is why I suggested setting the SelectedItem, then using RemoveSelected (the post is further down, so you may have missed it).

---

Yann

(plus ça change, plus c'est la même chose!)

Answer 26

Thank you Yann,

but now there is the next error in

<span style="white-space:pre">    </span>Me.Items.SelectedItem = entity

Error: '>' expected

 

Answer 27

All the HTML markup is not required and probably a mistake from the forums editor.

The code should read

Me.Items.SelectedItem = entity

---

Simon Jones

Answer 28

Hi,

finally I got it :)

It works with the for next loop :)

Here is the code

      For pointer = Me.Kundes.Count - 1 To 0 Step -1
        Dim kunde = Me.Kundes.ElementAt(pointer)
        If (kunde.Details.EntityState = EntityState.Added) Then
          Dim propertyName = kunde.Name
          Dim exists = (Me.DataWorkspace.ApplicationData.Kundes.Where(Function(x) x.Name = propertyName).FirstOrDefault() IsNot Nothing)
          If (exists = True) Then
            kunde.Delete()

          End If
        End If
      Next

Ok, now my last questions. Is it possible to start the check of the imported data after importing?

This is the code of the Import Button

Private Sub ImportfromExcel_Execute()
      ' Write your code here.
      LightSwitchUtilities.Client.ImportFromExcel(Me.Kundes)

    End Sub

If I place it after LightSwitchUtilities.Client...

it happens nothing, because there is first the pop up with the columns etc.

Thanks @ Yann, Simon and Kivito :)

 

 

 

 

Answer 29

Excellent. Glad you found the right incatation.

ElementAt is an interesting variation on the usual Item method. Such are the vaguaries of building on other people's work.

I suspect the ImportFromExcel code is executing on a different thread so any code you put after it is long completed by the time the Import actually happens.

You may be able to construct some code that waits for the other thread to complete before continuing or you may need to edit the ImportFromExcel code to add a call-back mechanism so it can tell you when it has finished.

---

Simon Jones

Answer 30

Simon, Yann, Kivito,

Forgive my ignorance but I haven't quite sassed the merits of walking backwards through a collection.

Could you educate a not-so-wise ol' man?

Thanks, Keith

---

E tenebris lux. ±
 Update : Re-read post about collection count. Thanks, Simon - That's why I'm not-so-wise!

Answer 31

When you use a For Each loop to run through a collection but delete or remove the current item from the collection, your pointer is moved on to the next item automatically and then you hit the Next statement which moves you on again. This means you've skipped an item in the collection without considering it.

If you use a For ... Next loop with an integer pointer, the start and end points of the pointer are evaluated only once as you enter the loop for the first time so if you are running from 0 to Count-1 the Count-1 is evaluated only at the start. If you then delete an item from the collection while inside the loop, Count is decremented by one but your loop will still try to take you on to what was the end of the collection when you first entered the loop. This means you'll run off the end of the collection and get an InvalidIndexException or something similar.

By running backwards from Count-1 to 0 you avoid this error because your end point (0) isn't going to move.

A similar problem occurs with trying to use a For Next loop and inserting items into a collection inside the loop. You may process some items twice or not at all (depending where the items were inserted into the collection and where your pointer was when they were inserted).

---

Simon Jones

Answer 32

Simon,

Thanks for an excellent reply.

BTW I ended up mod'ing Sheel's code, adding another function to permit checking for dupes on a specified column when populating the collection. I still however like the separate button approach. My approach does have performance penalties as you can imagine! Noticable on big collections.

I'm interested in this thread because I like to see suggestions to overcome my Unique Index validation mechanism whinge (of Fred and Ginger fame) in postings past.

Thanks, Keith

---

E tenebris lux. ±

Answer 33

Hi Keith,

maybe you cann tell me how you are mod'ing the Sheel's code?

If possible I want to mode it a little bit two :)

thanks :)

Answer 34

@Dragonalw

First thing - It would have been nice if you'd marked the guys who did the graft answers not yourself - Hint, Hint...... That's my trick!

 

I've done lots of customisations that I feel are beyond the scope of this thread, if not forum. However, one area that I've addressed, that may interest you and others, is that of navigation in choosing the workbook and worksheet. In my approach I quite simply check to see if Excel is running and prompt the user to launch Excel. And use the Active workbook and worksheet as being the data source that you want to load.

How :

Extract from MyXLhelper

Class MyXLHelper
    Implements IDisposable
 
    Private _excel As Object
 
#Region "Constants"
    Const XlListObjectSourceType_xlSrcRange = 1
    Const XlYesNoGuess_xlYes = 1
    Const XlDVType_xlValidateList = 3
    Const _XlDVAlertStyle_xlValidAlertStop = 1
    Const XL_NOTRUNNING As Long = 429
#End Region
 
    Private ReadOnly Property Excel
        Get
            Try
                If _excel Is Nothing Then
                    _excel = AutomationFactory.GetObject("Excel.Application")
                End If
                Return _excel
 
               
            Catch ex As Exception
 
            End Try
            Return _excel
 
        End Get
 
    End Property

next add a new Sub ActiveExcelIn:( in the ExcelImporter.vb)

 Public Sub ActiveExcelIn(ByVal collection As IVisualCollection)
        _collection = collection
 
        Dispatchers.Main.BeginInvoke(
    Sub()
 
        Try
            Dim excel As New MyXLHelper()
 
            If excel.GetActiveWorkSheetName <> "" And excel.GetActiveWorkbookName <> "" Then
           

                _excelDocRange = excel.ExcelUsedRange(excel.GetActiveWorkbookName, excel.GetActiveWorkSheetName)
 
....

          Else
                collection.Screen.Details.Dispatcher.BeginInvoke(
               Sub()
                   _collection.Screen.ShowMessageBox("Open the required worksheet that contains the data with Excel.")
               End Sub)
            End If
 
        Catch ex As SecurityException
            collection.Screen.Details.Dispatcher.BeginInvoke(
                Sub()
                    _collection.Screen.ShowMessageBox("Error: Silverlight Security error. Could not load Excel document.")
                End Sub
            )
        Catch comEx As COMException
            _collection.Screen.Details.Dispatcher.BeginInvoke(
                Sub()
                    _collection.Screen.ShowMessageBox("Open the required worksheet that contains the data with Excel.")
                End Sub
            )
        End Try
 
    End Sub)
    End Sub

The idea is that the user should be looking at the data in Excel that he wants to load into LightSwitch and that when the Excel Import Button execute code is called in the LS app. In my case I would be filling up a Grid for the IVisualCollection.

 Button execute code :

ActiveExcelIn(MyCollection)

 

For those who have had issues with not being able to load outside of "My Documents" due to the "security constraints" of Silverlight you'll find that if you can navigate to it in Excel running outside of the LightSwitch app you can use the Excel object to import into an OOB LS app.

Hope this has given you and others food for thought, Keith

---

E tenebris lux. ±

Answer 35

@ Keith

First sorry I did not know this about the marks ... I just marked this as answer because it was finally the thing worked in my app.

Thank you for your answer, looks very interesting :)

Next days I'am going to costumies my excel import :)

 

Answer 36

Thanks for that excellent idea Keith!

---

Yann

(plus ça change, plus c'est la même chose!)

Answer 37

Please find listed below entire copy of my changes made to Sheel Shah's ExcelImporter.ExcelHelper.vb and transfered to the new MyXLHelper class added to his LightSwitchUtilities.Client project I think it would be a good for you to see how I use the GetObject as opposed to the CreateObject and also how I Dispose. I also show how I use the Excel Interops to find the active sheet and workbook's names from the, currently running, Excel process.

' Imports Microsoft.VisualBasic
Imports System.Windows.Interop
Imports System.Runtime.InteropServices.Automation
 
Class MyXLHelper
    Implements IDisposable
 
    Private _excel As Object
 
#Region "Constants"
    Const XlListObjectSourceType_xlSrcRange = 1
    Const XlYesNoGuess_xlYes = 1
    Const XlDVType_xlValidateList = 3
    Const _XlDVAlertStyle_xlValidAlertStop = 1
    Const XL_NOTRUNNING As Long = 429
#End Region
 
    Private ReadOnly Property Excel
        Get
            Try
                If _excel Is Nothing Then
                    _excel = AutomationFactory.GetObject("Excel.Application")
                End If
                Return _excel
 
               
            Catch ex As Exception
 
            End Try
            Return _excel
 
        End Get
 
    End Property
 
 
    Public ReadOnly Property GetActiveWorkbookName() As String
        Get
            Dim result As String
            result = ""
 
            Try
                Dim myApp As Object
                myApp = Excel
                result = myApp.ActiveWorkbook.Name
            Catch ex As Exception
 
            End Try
            Return result
 
        End Get
 
    End Property
    Public ReadOnly Property GetActiveWorkSheetName() As String
        Get
            Dim result As String
            result = ""
 
            Try
                Dim myApp As Object
                myApp = Excel
                result = myApp.ActiveSheet.Name
            Catch ex As Exception
 
            End Try
            Return result
 
        End Get
 
    End Property
 
 
    Public ReadOnly Property GetWorkSheets(ByVal workBookIndex As Int32) As IEnumerable(Of ExcelWorkSheet)
        Get
            Dim workBook As Object = Excel.WorkBooks(workBookIndex)
 
            Dim workSheets As New List(Of ExcelWorkSheet)
            Dim index As Int32 = 1
            For Each workSheet In workBook.WorkSheets
                workSheets.Add(New ExcelWorkSheet(index, workSheet.Name))
                index = index + 1
            Next
            Return workSheets
        End Get
    End Property
 
    'Public ReadOnly Property GetRange(ByVal workBookIndex As Int32, ByVal workSheetIndex As Int32, ByVal cell1 As String, ByVal cell2 As String) As Object
    '    Get
    '        Dim workBook As Object = Excel.WorkBooks(workBookIndex)
    '        Dim workSheet As Object = workBook.WorkSheets(workSheetIndex)
    '        Return workSheet.Range(cell1, cell2)
    '    End Get
    'End Property
 
    Public ReadOnly Property ExcelUsedRange(ByVal workBookName As String, ByVal workSheetName As String) As String(,)
        Get
            Dim workBook As Object = Excel.WorkBooks(workBookName)
            Dim workSheet As Object = workBook.WorkSheets(workSheetName)
            Dim excelRange = workSheet.UsedRange
            Dim columnCount As Int32 = excelRange.Columns.Count
            Dim rowCount As Int32 = excelRange.Rows.Count
 
 
            Dim valueArray(rowCount - 1, columnCount - 1) As String
            For i = 1 To rowCount
                For j = 1 To columnCount
                    valueArray(i - 1, j - 1) = excelRange(i, j).Value
                Next
 
            Next
            Return valueArray
        End Get
    End Property
 
#Region "IDisposable Support"
    Private disposedValue As Boolean ' To detect redundant calls
 
    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                ' TODO: dispose managed state (managed objects).
            End If
 
            'Excel.DisplayAlerts = False
            'Excel.Quit()
 
 
            ' TODO: free unmanaged resources (unmanaged objects) and override Finalize() below.
            ' TODO: set large fields to null.
 
            '_beforeCloseEvent.RemoveEventHandler(New ComAutomationEventHandler(AddressOf BeforeCloseEventHandler))
            '_beforeCloseEvent = Nothing
            _excel = Nothing
        End If
        Me.disposedValue = True
    End Sub
 
    ' TODO: override Finalize() only if Dispose(ByVal disposing As Boolean) above has code to free unmanaged resources.
    'Protected Overrides Sub Finalize()
    '    ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
    '    Dispose(False)
    '    MyBase.Finalize()
    'End Sub
 
    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region
End Class
 
'Class ExcelWorkSheet
'    Public Sub New(ByVal index As Int32, ByVal name As String)
'        Me.Index = index
'        Me.Name = name
'    End Sub
'    Public Property Index As Int32
'    Public Property Name As String
'End Class
 

Hope this helps, Keith 

---

E tenebris lux. ±

Answer 38

Nice work Keith!

---

Yann

(plus ça change, plus c'est la même chose!)

Answer 39

Thanks Yann,

I hope Sheel picks up this thread and adds his thoughts. It is his baby, after all !

Keith

---

E tenebris lux. ±

Answer 40

Hi,

finally I got it :)

It works with the for next loop :)

Here is the code

      For pointer = Me.Kundes.Count - 1 To 0 Step -1
        Dim kunde = Me.Kundes.ElementAt(pointer)
        If (kunde.Details.EntityState = EntityState.Added) Then
          Dim propertyName = kunde.Name
          Dim exists = (Me.DataWorkspace.ApplicationData.Kundes.Where(Function(x) x.Name = propertyName).FirstOrDefault() IsNot Nothing)
          If (exists = True) Then
            kunde.Delete()

          End If
        End If
      Next

I have 4 fields in the primary key for my collection, can anyone show me how to make this work to check more than 1 field?

Also, when the duplicate items are removed I end up with validation error messages displayed at the top of the screen.

Thanks for all the help so far.

Answer 41

Hey Yann this code is for two buttons or for a single button??

---

rohit ab

Answer 42

can i get the same in c# code ??

---

rohit ab

Answer 43

Which code? There's been a lot of code suggested throughout the thread.

---

Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands

If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"

By doing this you'll help people find answers faster.

Answer 44

 i want code where we can filter the excel sheet data with database tables data?

---

rohit ab