Inhalt eines Excel-Sheets verarbeiten

  • VB.NET

Es gibt 14 Antworten in diesem Thema. Der letzte Beitrag () ist von vacuphase.

    Inhalt eines Excel-Sheets verarbeiten

    Hallo liebes Forum,
    letztes Mal wurde mir super geholfen und nun habe ich wieder etwas, wobei ich nicht weiterkomme.
    Die Aufgabe ist, ein Excel-Sheet, das Namen (auch doppelt vorkommende) mit Kontobewegungen (die sind alle verschieden) beinhaltet,
    mit VB abzufragen und jeden Namen (also Kunden) mit Kontobewegung in einer einzelnen .txt-Datei separat abzuspeichern.

    Ich bin nun soweit gekommen, dass mein Code alle Zeilen abgrast und jede Zeile in eine .txt speichert.

    Spoiler anzeigen

    VB.NET-Quellcode

    1. Imports System.IO
    2. Imports excel = Microsoft.Office.Interop.Excel
    4. Public Class Form1
    6. Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    7. Dim app As excel.Application = New excel.Application
    8. Dim fileReader As StreamReader = New StreamReader("bankkunden.xlsx")
    9. Dim output As String
    11. app.Workbooks.Open(CurDir() & "\bankkunden.xlsx")
    13. For i = 2 To 54
    14. 'output = datum & kontoveränderung & kontostand
    15. output = app.Range("A" & i).Value & " / " & app.Range("D" & i).Value
    17. Dim fileWriter As StreamWriter = New StreamWriter(i & ".txt", False)
    18. fileWriter.WriteLine(CStr(output))
    19. fileWriter.Close()
    20. Next
    22. End Sub
    24. End Class



    Leider bin ich nicht in der Lage herauszufinden, wie man das Excel-Sheet auf doppelte Namen überprüft und eben dann nicht jede Zeile einzeln speichert, sondern dann auch mal überspringt.
    Ebenso verhält es sich mit dem Verarbeiten der Buchungen. Ich weiß nicht, wie ich die so rausziehen kann, dass die zu den einzelnen Personen gehörigen Buchungen in die zur Person gehörige .txt reingeschrieben werden.

    Das Excel-Blatt sieht so aus:

    Spoiler anzeigen


    Datum Nachname Vorname Buchung
    02.02.2010 Simpson Homer 5,50
    02.02.2010 Simpson Homer -10,00
    20.02.2010 Burns Montgomery 50000000,00

    .
    .
    .



    Vielen Dank schonmal für Eure Hilfe :)

    fürs sortieren:

    VB.NET-Quellcode

    1. Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
    2. Dim test() =
    3. {Tuple.Create(1, "Hans", 50),
    4. tuple.Create(2, "Horst", 70),
    5. Tuple.Create(3, "Hans", 80),
    6. Tuple.Create(4, "Heidi", 70),
    7. Tuple.Create(5, "Hubert", 50),
    8. Tuple.Create(6, "Hansi", 70),
    9. Tuple.Create(7, "Hans", 50),
    10. Tuple.Create(8, "Horst", 70)} 'Erstellen der TestExcel ;)
    12. Dim AllePersonen() As String = (From k In test
    13. Select k.Item2
    14. Distinct).ToArray 'Aussortieren der Duplikate
    16. For Each EinzelPerson In AllePersonen 'Durchlaufen der Einzelpersonen
    17. Dim AktPerson = EinzelPerson
    18. Dim KontoSumme = (From p In test
    19. Where p.Item2 = AktPerson
    20. Select p.Item3).ToArray.Sum 'Summieren aller Einzelbewegungen
    22. TextBox3.AppendText(AktPerson &
    23. " hat zur Zeit: " &
    24. KontoSumme.ToString &
    25. "€ auf dem Konto." &
    26. vbNewLine)
    28. Next
    29. End Sub


    Die Range zum einlesen musste halt noch anpassen auf dein Excel Sheet... Wichtig fürs Sortieren ist einmal die neue Liste, die mit dem DISTINCT von Duplikaten befreit wird. Anhand dieser Liste gehste dann dein ganzes Blatt durch, sucht das jeweils komplett nach dem Namen ab und dann summiert der entsprechend die Kontobewegungen.
    Entsprechend bei Exce kannst du dann ja statt dem Tupel mit dem Range arbeiten und dann ensprechend den offset(zeile,spalte) absuchen oder auslesen

    Vielen Dank FloFuchs,
    Du hast mich schon viel weitergebracht :)
    Derzeit habe ich noch einen Fehler:
    Bei "[ ... ] ToArray.Sum sagt er mir
    "Fehler bei der Überladungsauflösung, da keine zugreifbare "Sum" diese Anzahl von Argumenten akzeptiert."

    Auch bin ich mir nicht sicher, ob das mit der Range so hinhaut ...
    Das Datum habe ich noch eingefügt und den FileWriter reingesetzt.

    Ich würde mich freuen, falls Du nochmal rüberschaust :)

    Spoiler anzeigen


    VB.NET-Quellcode

    1. Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    2. Dim app As excel.Application = New excel.Application
    3. Dim fileReader As StreamReader = New StreamReader("bankkunden.xlsx")
    4. Dim output As String
    5. app.Workbooks.Open(CurDir() & "\bankkunden.xlsx")
    7. Dim test() = {app.Range("A2:D54")}
    9. Dim AllePersonen() As String = (From k In test
    10. Select k.Item2
    11. Distinct).ToArray 'Aussortieren der Duplikate
    14. For Each EinzelPerson In AllePersonen 'Durchlaufen der Einzelpersonen
    15. Dim AktPerson = EinzelPerson
    16. Dim Datum = (From p In test
    17. Where p.Item2 = AktPerson
    18. Select p.Item1).ToArray.ToString
    19. Dim KontoSumme = (From p In test
    20. Where p.Item2 = AktPerson
    21. Select p.Item3).ToArray.Sum 'Summieren aller Einzelbewegungen
    23. Dim fileWriter As StreamWriter = New StreamWriter(EinzelPerson & ".txt", False)
    24. fileWriter.WriteLine(CStr(Datum & " / " & AktPerson & " / " & KontoSumme.ToString))
    25. fileWriter.Close()
    26. Next
    28. End Sub

    Und dann kannst du nicht einfach do das Tuple durch deinen Range ersetzen. Das musste schon etwas anders lösen. Schließlich kannste ja auch nicht einfach einen Range dann mit Item1, 2 3 ansprechen.
    du musst dann in deinem Range suchen und die Vergleiche dann auch entsprechend nicht über item sondern range.offset durchführen.
    Edit:
    kleines Beispiel um deine Excel-Datei in ein Tupel zu schaufeln:

    VB.NET-Quellcode

    1. xlApp.Visible = True
    2. Dim xlMappe = xlApp.Workbooks.Open(IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "Test.xlsx"))
    3. Dim xlTabelle = CType(xlMappe.Worksheets("Tabelle1"), Excel.Worksheet)
    4. Dim KtoBewegungen As New List(Of Tuple(Of Date, String, Double))
    5. For i = 1 To xlTabelle.UsedRange.Rows.Count
    6. Dim xlDatum As Date = Date.Parse(CType(xlTabelle.Cells(i, 1), Excel.Range).Value.ToString)
    7. Dim xlName = CType(xlTabelle.Cells(i, 2), Excel.Range).Value.ToString & " " & CType(xlTabelle.Cells(i, 3), Excel.Range).Value.ToString
    8. Dim xlSaldo As Double = Double.Parse(CType(xlTabelle.Cells(i, 4), Excel.Range).Value.ToString)
    9. KtoBewegungen.Add(Tuple.Create(xlDatum, xlName, xlSaldo))
    10. Next
    11. xlMappe.Close()
    12. xlApp.Quit()

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „FloFuchs“ ()

    Vielen Dank für Deine super Hilfe,
    ich glaube, wir sind nah an der Lösung.
    Deine Beispiele habe ich nun überführt und versucht anzupassen.
    Beim Ausführen des Programms sagt er mir in der Zeile

    VB.NET-Quellcode

    1. Dim xlDatum As Date = Date.Parse(CType(xlTabelle.Cells(i, 1), excel.Range).Value.ToString)
    ,
    dass der Objektverweis nicht auf eine Objektinstanz festgelegt sei.

    Woran liegt das denn? Ich kann mir keinen logischen Reim darauf machen.

    Ich hoffe Du hilfst mir noch weiter :)

    Hier der Kot:

    Spoiler anzeigen

    VB.NET-Quellcode

    1. Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    2. Dim app As excel.Application = New excel.Application
    3. app.Visible = False
    4. Dim xlMappe = app.Workbooks.Open(IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), CurDir() & "\bankkunden.xlsx"))
    5. Dim xlTabelle = CType(xlMappe.Worksheets("Buchungen"), excel.Worksheet)
    6. Dim KtoBewegungen As New List(Of Tuple(Of Date, String, Double))
    8. For i = 2 To xlTabelle.UsedRange.Rows.Count
    9. Dim xlDatum As Date = Date.Parse(CType(xlTabelle.Cells(i, 1), excel.Range).Value.ToString)
    10. Dim xlName = CType(xlTabelle.Cells(i, 2), excel.Range).Value.ToString & " " & CType(xlTabelle.Cells(i, 3), excel.Range).Value.ToString
    11. Dim xlSaldo As Double = Double.Parse(CType(xlTabelle.Cells(i, 4), excel.Range).Value.ToString)
    12. KtoBewegungen.Add(Tuple.Create(xlDatum, xlName, xlSaldo))
    13. Next
    14. xlMappe.Close()
    15. app.Quit()
    17. Dim AllePersonen() As String = (From k In KtoBewegungen
    18. Select k.Item2
    19. Distinct).ToArray 'Aussortieren der Duplikate
    21. For Each EinzelPerson In AllePersonen 'Durchlaufen der Einzelpersonen
    22. Dim AktPerson = EinzelPerson
    23. Dim Datum = (From p In KtoBewegungen
    24. Where p.Item2 = AktPerson
    25. Select p.Item1).ToArray.ToString
    26. Dim KontoSumme = (From p In KtoBewegungen
    27. Where p.Item2 = AktPerson
    28. Select p.Item3).ToArray.Sum 'Summieren aller Einzelbewegungen
    30. Dim fileWriter As StreamWriter = New StreamWriter(EinzelPerson & ".txt", False)
    31. fileWriter.WriteLine(CStr(Datum & " / " & AktPerson & " / " & KontoSumme.ToString))
    32. fileWriter.Close()
    33. Next
    35. End Sub

    1. War nicht an, nachdem ichs reingebaut habe, ging es auch nicht
    2. In der Datei steht was drin, hatte erst app.visible = true, da sah man, dass die richtige Datei aufgeht
    3. Weiß nicht genau, wie man das macht, er zeigt nur gelbe Zeilen und macht die Schleife anstandslos durch (müssen da Fehler angezeigt werden, wenn was faul ist)?

    Danke schonmal :)

    Also, hab da noch bissle was umgestellt...
    Wichtige Fragen... is die Excel richtig aufgebaut? wenn da zum Beispiel in Spalte A kein auf Datum formatierter Wert drin steht kann dein VB damit nix anfangen.
    Folgendes habe ich bei mir grade getestet, das läuft.
    Spoiler anzeigen

    VB.NET-Quellcode

    1. Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
    2. Dim app As New Excel.Application
    3. app.Visible = True
    4. Dim xlMappe = app.Workbooks.Open(
    5. IO.Path.Combine(Environment.GetFolderPath(
    6. Environment.SpecialFolder.MyDocuments),
    7. "Bankkunden.xlsx"))
    8. Dim xlTabelle = CType(xlMappe.Worksheets("Buchungen"), Excel.Worksheet)
    10. Dim KtoBewegungen As New List(Of Tuple(Of Date, String, Double))
    12. For i = 1 To xlTabelle.UsedRange.Rows.Count
    13. Dim xlDatum As Date = Date.Parse(CType(xlTabelle.Cells(i, 1), Excel.Range).Value.ToString)
    14. Dim xlName = CType(xlTabelle.Cells(i, 2), Excel.Range).Value.ToString & " " & CType(xlTabelle.Cells(i, 3), Excel.Range).Value.ToString
    15. Dim xlSaldo As Double = Double.Parse(CType(xlTabelle.Cells(i, 4), Excel.Range).Value.ToString)
    16. KtoBewegungen.Add(Tuple.Create(xlDatum, xlName, xlSaldo))
    17. Next
    18. xlMappe.Close()
    19. app.Quit()
    21. Dim AllePersonen() As String = (From k In KtoBewegungen
    22. Select k.Item2
    23. Distinct).ToArray 'Aussortieren der Duplikate
    25. For Each EinzelPerson In AllePersonen 'Durchlaufen der Einzelpersonen
    26. Dim AktPerson = EinzelPerson
    27. Dim Datum = (From p In KtoBewegungen
    28. Where p.Item2 = AktPerson
    29. Select p.Item1).ToArray.Max 'nicht .tostring sondern .max damit wir auch das passende Datum zum Endkontostand haben
    30. Dim KontoSumme = (From p In KtoBewegungen
    31. Where p.Item2 = AktPerson
    32. Select p.Item3).ToArray.Sum 'Summieren aller Einzelbewegungen
    33. Using sw As StreamWriter = New StreamWriter(
    34. Path.Combine(Environment.GetFolderPath(
    35. Environment.SpecialFolder.MyDocuments),
    36. EinzelPerson & ".txt"))
    37. sw.WriteLine(Datum.ToLongDateString & "|" & EinzelPerson & "|" & KontoSumme.ToString)
    38. End Using
    39. Next
    40. End Sub

    Wichtig:
    Schau dass Du richtig mit Path.Combine umgehst. Les es dir am nbesten noch mal auf MSDN durch. Dein Streamrider bitte mit Using benutzen! Und die aktuellen Parser benutzen (.Tostring)
    Schau es dir nochmal in Ruhe an, un vollzieh es nach. Falls Du Fragen zu ner Zeile hast... melde Dich

    Vielen Dank Flo,
    es geht leider noch nicht, gehabtes Problem.
    Die Excel ist auf jeden Fall richtig aufgebaut, er schriebt schön alles in die Variablen.
    Dazu habe ich mal rausgezogen, was in den Variablen steht.
    Die Iterationen scheint er durchzugehen aber aus unerfindlichem Grund bleibt er in der letzten Zeile stehen.

    Anbei mal ein Screenshot




    Und die Excel-Tabelle. Zeile 54 zeigt das, was als letztes in den Variablen steht.



    Hoffentlich findet sich der Fehler :)

    Dieser Beitrag wurde bereits 2 mal editiert, zuletzt von „vacuphase“ ()

    Edit:
    Ah ich denke: Schau dir mal bitte den Wert von

    VB.NET-Quellcode

    1. xlTabelle.UsedRange.Rows.Count
    speziell vom .Count
    Wahrscheinlich hast du unter deinen Daten noch ne Formatierung, dann zählt das auch als UsedRange. In der Zeile 55 versucht er dann Nothing in ein Date zu konvertieren, was dann natürlich einen Fehler gibt.
    Falls ja... packen wir das ganze einfach in eine große If - Abfrage

    VB.NET-Quellcode

    1. if CType(xlTabelle.Cells(i, 2), Excel.Range).Value.ToString ="" then
    2. exit for
    3. else
    4. 'Dein ganzer anderer Code
    5. end if


    Btw.. da das

    VB.NET-Quellcode

    1. CType(xlTabelle.Cells(i, 2), Excel.Range).Value.ToString

    so oft gebraucht wird, könntest du da noch ne Verbesserung einbringen. zB. das ganze mit Trycast machen statt mit Ctype und dann den Satz in ne Function auslagern.

    Dieser Beitrag wurde bereits 2 mal editiert, zuletzt von „FloFuchs“ ()

    DAS PROGRAMM FUNZT :D :D

    Das Problem war genau die Tabelle, er hat den UsedRange-Count auf 78 gesetzt und dann versucht einen leeren String in die Variablen zu schaufeln.
    Ich habe einfach manuell mal die Schleife auf "for i=2 to 54" gesetzt und schon hat er alles so erstellt, wie gedacht.
    Ich habe dann noch eine Kundennummer eingefügt, dass dann die .txt "kundennummer.txt" heißt :)

    Ich probiere nochmal mit der zusätzlichen if-Struktur, die Du vorgeschlagen hast.

    Bis hierhin erstmal viiiiielen Dank, da wäre ich nie drauf gekommen :)

    Der Kot:

    Spoiler anzeigen

    VB.NET-Quellcode

    1. Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    2. Dim app As New excel.Application
    3. app.Visible = False
    4. Dim xlMappe = app.Workbooks.Open(
    5. IO.Path.Combine(Environment.GetFolderPath(
    6. Environment.SpecialFolder.MyDocuments),
    7. CurDir() & "\Bankkunden.xlsx"))
    8. Dim xlTabelle = CType(xlMappe.Worksheets("Buchungen"), excel.Worksheet)
    10. Dim KtoBewegungen As New List(Of Tuple(Of Date, String, Double))
    12. For i = 2 To 54 'xlTabelle.UsedRange.Rows.Count
    13. Dim xlDatum As Date = Date.Parse(CType(xlTabelle.Cells(i, 1), excel.Range).Value.ToString)
    14. Dim xlName = CType(xlTabelle.Cells(i, 2), excel.Range).Value.ToString & " " & CType(xlTabelle.Cells(i, 3), excel.Range).Value.ToString
    15. Dim xlSaldo As Double = Double.Parse(CType(xlTabelle.Cells(i, 4), excel.Range).Value.ToString)
    16. KtoBewegungen.Add(Tuple.Create(xlDatum, xlName, xlSaldo))
    17. Next i
    18. xlMappe.Close()
    19. app.Quit()
    21. Dim AllePersonen() As String = (From k In KtoBewegungen
    22. Select k.Item2
    23. Distinct).ToArray 'Aussortieren der Duplikate
    25. Dim kdnr As Integer = 0
    26. For Each EinzelPerson In AllePersonen 'Durchlaufen der Einzelpersonen
    27. Dim AktPerson = EinzelPerson
    28. kdnr += 1
    29. Dim Datum = (From p In KtoBewegungen
    30. Where p.Item2 = AktPerson
    31. Select p.Item1).ToArray.Max 'nicht .tostring sondern .max damit wir auch das passende Datum zum Endkontostand haben
    32. Dim KontoSumme = (From p In KtoBewegungen
    33. Where p.Item2 = AktPerson
    34. Select p.Item3).ToArray.Sum 'Summieren aller Einzelbewegungen
    35. Using sw As StreamWriter = New StreamWriter(
    36. Path.Combine(Environment.GetFolderPath(
    37. Environment.SpecialFolder.MyDocuments),
    38. CurDir() & "\" & kdnr & ".txt"))
    39. sw.WriteLine(Datum.ToLongDateString & " | " & EinzelPerson & " | " & KontoSumme.ToString)
    40. End Using
    41. Next
    42. End Sub

    Schön wenns funktioniert:
    Noch nen kleinen Tip zu Path.Combine, das is dazu geacht, dass du die & und die \ weglassen kannst. Also

    VB.NET-Quellcode

    1. Path.Combine(Environment.GetFolderPath(
    2. Environment.SpecialFolder.MyDocuments),
    3. CurDir(), kdnr & ".txt"))

    Also das mit dem Prüfen des leeren Strings in Zeile 55 geht nicht ...
    Er schmeißt mir wieder an den Kopf, dass Objektverweis nicht auf Objektinstanz festgelegt sei ...
    Das i im If-Statement ist 55, also völlig korrekt.

    VB.NET-Quellcode

    1. For i = 2 To xlTabelle.UsedRange.Rows.Count
    2. If CType(xlTabelle.Cells(i, 2), excel.Range).Value.ToString = "" Then
    3. Exit For
    4. Else
    5. Dim xlDatum As Date = Date.Parse(CType(xlTabelle.Cells(i, 1), excel.Range).Value.ToString)
    6. Dim xlName = CType(xlTabelle.Cells(i, 2), excel.Range).Value.ToString & " " & CType(xlTabelle.Cells(i, 3), excel.Range).Value.ToString
    7. Dim xlSaldo As Double = Double.Parse(CType(xlTabelle.Cells(i, 4), excel.Range).Value.ToString)
    8. KtoBewegungen.Add(Tuple.Create(xlDatum, xlName, xlSaldo))
    9. End If
    10. Next i


    Was kann das sein? :)

    Hab es ganz anders gemacht jetzt :)
    Das funzt tadellos :)
    Danke Dir, ich melde mich bestimmt nochmal, muss nämlich jetzt die Kundendaten in eine Excel-Mappe schreiben ...

    VB.NET-Quellcode

    1. Dim zeile As Integer = 2
    2. Do While Not CStr(xlTabelle.Cells(zeile, 1).value) = ""
    3. Dim xlDatum As Date = Date.Parse(CType(xlTabelle.Cells(zeile, 1), excel.Range).Value.ToString)
    4. Dim xlName = CType(xlTabelle.Cells(zeile, 2), excel.Range).Value.ToString & " " & CType(xlTabelle.Cells(zeile, 3), excel.Range).Value.ToString
    5. Dim xlSaldo As Double = Double.Parse(CType(xlTabelle.Cells(zeile, 4), excel.Range).Value.ToString)
    6. KtoBewegungen.Add(Tuple.Create(xlDatum, xlName, xlSaldo))
    7. zeile += 1
    8. Loop

Facebook
Telefonspende