Lesen aus Excel um umwandlung in double

[mark01]

Hallo zusammen,

ich lese aus einer Excel-datei Umsatzwerte in eine DataTable. Dieses Table wird dann in einem Datagrid angezeigt (insgesamt ca. 15000 Sätze). Das einlesen und anzeigen klappt gut.

Einige der Werte möchte ich gerne direkt beim einlesen Umwandeln (am Besten in eine Währung bzw Double) bzw berechnungen durchführen. Ich habe das ganze schon mal zu Fuß gemacht (also jede Row einzeln gelesen und die Werte einer Variable zugeordnet und die Row zum Table hinzugefügt) das allerdings dauert zu lange (> 15 Minuten)

 oSheet = oBook.Worksheets(1)
                aConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pFilename & ";Extended Properties=""Excel 12.0;HDR=NO;"""

                Dim query As String = "Select F1 as Kunde, F3 as Artikel, 'D' as Herk, '" & pJahr & "' as Jahr, CAST(F5 as Double) as Okt, F6 as Nov, F7 as Dez, F8 as Jan, F9 as Feb, F10 as Mrz, F11 as Apr, F12 as Mai, F13 as Jun, F14 as Jul, F15 as Aug, F16 as Sep, F17 as gesJahr, '1' As Faktor, '" & admName & "' as ADM from [" & oBook.ActiveSheet.Name & "$] WHERE F1 LIKE '7%' "
                Dim con As OleDbConnection = New OleDbConnection(aConnection)
                con.Open()
                Dim command As OleDb.OleDbCommand = New OleDbCommand(query, con)
                Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(command)

                adapter.Fill(dt)

So mache ich das zur Zeit und mit der Geschwindigkeit bin ich zufrieden (ca. 20sec). Nur die Monatswerte OKT-SEP und gesJahr sollten Umsätze sein. Ich habe bereits schon CAST(F6 as double), Cdbl(F6) usw. versucht, leider bekomme ich für alle eine Fehlermeldung. Hat jemand noch einen Tipp wie das klappen könnte ?

Danke schon mal im voraus
mark01

[fanderlf]

Also mit dem Teil kenn ich mich leider nicht aus, wenns möglich ist kannst Du Dir ja mal OpenXML anschauen. Ich denke da wäre die API wesentlich flexibler. Alternativ vielleicht auch Office Interop, allerdings befürchte ich, dass es damit ziemlich langsam wird.

[mark01]

Ja, das mit der Geschwindigkeit ist das Problem bei Interop. Deswegen die Lösung mit dem SELECT...

[fanderlf]

Vielleicht gehts ja mit OpenXML? Das geht wirklich ZIEMLICH flott. Dafür muss das Dokument aber auch im OpenXML Format vorliegen. Plugins zum Abspeichern in OpenXML gibts glaub ich ab Word2003

[mark01]

Nee, das klappt nicht da diese Daten aus SAP kommen und die Schnittstelle nicht geändert werden kann. Ich muß mit dem auskommen was ich habe...

[fanderlf]

ok das ist kacke..

Ließ doch alle Datensätze ein... Bau Dir ein zweites DataSet was identisch aufgebaut ist nur die Strings werden durch double ersetzt. Dann ließt Du einmal alle Daten aus der DB und konvertierst sie anschließend nach double.
Ich denke bei 15000 Zeilen dürfte das eigentlich nicht so lange dauern.

Wenn Du jede Zeile einzeln einließt dauerts natürlich ewig, weil Du ja jedesmal wieder übers Netz musst und das ist natürlich der absolute Overkill

[Womble]

Hallo Mark01,

kannst Du mal eine Exceltabelle in den Thread hängen, die ersten 5 Zeilen die Umsätze haben reichen aus, die Felder Kunde und Artikel kannst Du ja mit Spieldaten übertippen. Ich will mir nur mal ansehen können wie die Felder mit den Umsätzen im Excel dann "ursprünglich" vorliegen.

Gruß Womble

[M.Dietz]

Mal sehen, als Laie hat man da ja einen etwas anderen Blick drauf, vielleicht kann ich ja helfen, wenn nicht ignoriert's einfach:

Kann es sein, dass die Excel-Tabelle einen Header hat? Und dass dieser Header in der entsprechenden Spalte nicht in eine Double konvertierbar ist (weil z.B. "Umsatz Oktober" drinsteht)?

Connection Strings für die Verbindung zu Excel sagen mir nicht so viel, aber vielleicht reicht es ja aus, das HDR=Yes zu setzen, damit die erste Zeile ignoriert wird...?

[mark01]

Naja, ignorieren wäre nicht nett. Manchmal klappt das mit dem Ansatz gut. In dem FAll leider nicht. In der Tat klappt das mit dem casten von einem String nicht, daher werden vorher alle Headers usw. ignoriert. Daran liegt es also nicht.

Bisher bin ich auch noch nicht wirklich weiter gekommen und beschäftige mich mit anderen Punkten der implementierung. Vielleicht kommt ja noch ein Geistesbitz...

Danke aber
mark01

[M.Dietz]

Könntest Du trotzdem mal die von Womble erwähnte gekürzte und notfalls verfremdete Excel-Tabelle hier einhängen, damit wir es mal ausprobieren können?

Dein Connection String sagt aus, dass die erste Zeile eben KEINEN Header enthält, also bei Verwendung der Tabelle als DataSource erwartet das Programm schon in der ersten Zeile Daten, und das widerspricht Deiner Aussage "daher werden vorher alle headers usw. ignoriert".

Wenn Du in deinem ursprünglichen programm (bei dem Du selbst ausgelesen hast) erst bei Zeile 2 angefangen hast, dann hast DU den Header ignoriert, nicht das System!

Gerade wenn Dein SQL-String Spaltennamen enthält (ist dann auch einfacher zu lesen) sollte die Tabelle diese Spaltennamen in der ersten Zeile haben, und der Connection-String braucht dann ein HDR=YES
(siehe http://www.connectionstrings.com/excel)

Gruß
Martin

[mark01]

Hallo,

den Beitrag von Womble hab' ich doch glatt überlesen. Sorry ) Hier die excel-datei. In einer solchen Datei gibt es in Durchschnitt 30000 Zeilen. Eine automatische Umwandlung spart hier schon ganz schön Zeit. Bisher wird die Formatierung von Hand gemacht. Bei 30 Dateien dauert es 2-3 Stunden bis alles fertig ist.

Danke schon mal
Gruß
mark01

[Womble]

Hi mark01,

kannst Du mal ein Stück Code posten wo Du Dir aus der dt eine Zeile holst und diese dann verarbeitest, denn so wie ich den Zugriff mache bekomme ich die Felder als Zahlen.

Gruß Stephan

[mark01]

Wie schon gesagt, ich fülle eine Datatable und gehe nicht Zeilenweise darüber. Das Zeilenweise habe ich versucht, dauert aber zu lange.

        Dim dt As DataTable = New DataTable()
        Cursor.Current = Cursors.WaitCursor
        'Dim oOffice As New Microsoft.Office.Interop.Excel.Application
        Dim oBook As Microsoft.Office.Interop.Excel.Workbook
        Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim aConnection As String
        Dim admName As String = clsUtils.GetInstance.getADMNo(pFilename)
        LogMessage("Variablen erzeugt")
        Try
            If File.Exists(pFilename) Then
                LogMessage("DAtei existiert")
                Debug.Print(pFilename)
                oBook = xlApp.Workbooks.Open(aPath & pFilename)
                oSheet = oBook.Worksheets(1)
                aConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pFilename & ";Extended Properties=""Excel 12.0;HDR=NO;"""

                Dim query As String = "Select F1 as Kunde, F3 as Artikel, 'D' as Herk, '" & pJahr & "' as Jahr, F5 as Okt, F6 as Nov, F7 as Dez, F8 as Jan, F9 as Feb, F10 as Mrz, F11 as Apr, F12 as Mai, F13 as Jun, F14 as Jul, F15 as Aug, F16 as Sep, F17 as gesJahr, '1' As Faktor, '" & admName & "' as ADM from [" & oBook.ActiveSheet.Name & "$] WHERE F1 LIKE '7%' "
                Dim con As OleDbConnection = New OleDbConnection(aConnection)
                con.Open()
                Dim command As OleDb.OleDbCommand = New OleDbCommand(query, con)
                Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(command)

                adapter.Fill(dt)
                dt.TableName = oBook.ActiveSheet.Name

                adapter.Dispose()
                command.Dispose()
                con.Close()
                con.Dispose()
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Poste doch mal den Code den Du verwendest. Evtl. hilft da ja weiter.

Gruß
mark01

[Womble]

Hallo mark01,

dann sind die NULL Values Dein Problem, z.B. Zelle E33 in Deiner Muster xls. Bei zeilenweiser Verarbeitung kann ich das prüfen und abfangen, aber der Fill will das vorher fertig abgehandelt haben, wenn dem da ein NULL über den Weg läuft dann bockt er, zumindest bei mir.

Sobald ich mal im Bereich E33:P40 alle leeren Felder mit 0 belegt geht der Fill.

Wenn man Funktionen aufrufen könnte gäbe es per COALESCE(F5, 0) z.B. die Möglichkeit das der Select wenn er NULL vorfindet eine 0 liefert, bei mir hat es leider nicht geklappt. Ich habe auch sämtliche Varianten die mir von anderen SQL Dialekten geläufig sind probiert (ISNULL, NVL, IFNULL).

Gruß Stephan

[mark01]

Genau, das Problem hatte ich auch :-(

Danke für die Mühe! Einen Versuch war's wert.

Gruß
mark01