[VB.NET] Esportare dati da Paradox a Excel versione 2003

[vankraster]

Io avrei la necessità di esportare dati da un db Paradox a un file Excel versione 2003, il problema è che ci sono riuscito ma siccome il db paradox ne ha 50.000 records e 15 campi per l'esportazione ci vogliono 3 ore. Tutto risolto tramite codice da vb 2010. Prima spiego il concetto che ho usato poi mettero il codice:
Apro il db Paradox lo interrogo con la query, apro il file excel e poi dal risultato per ogni riga inserisco nel file excel i vari campi. Quello che vi chiedo a voi e se posso migliorare il programma in modo che l'esportazione mi duri al massimo 1 ora.

Dim sExcelPath, sdbPath, sLog As String 
        sExcelPath = System.IO.Path.GetDirectoryName(Application.ExecutablePath)
         sdbPath = "C:\db_From" 
        sLog = sExcelPath & "\logs" 

        Dim sFIle As System.IO.StreamWriter 


        sFIle = System.IO.File.CreateText(sLog & "\" & Date.Now.DayOfYear & ".txt")
 
        ' Add some information to the file. 
        sFIle.WriteLine(DateTime.Now.TimeOfDay.ToString & "Creazione del db") 

        Dim tblmy As DataTable 
        Dim oledbParadoxconn As New OleDb.OleDbConnection 
        'EXCEL###################################### 
        Dim xlApp As Excel.Application 
        Dim xlWorkBook As Excel.Workbook 
        Dim xlWorkSheet As Excel.Worksheet 
        'variabili memmorizazione dalla query#############################################
         Dim ShopCOde, DelDate, StockCode, ShrtDesc, WEBDESC, BRAND, VarDesc, Varcode, VarAltCode, PurchCost, Purchvat, UnitPrice, PriceVat, Desc As String
         Dim DescFrom, DescTo, AdoCOst1, AdoCost2, InvtryDate, Stock, Gruppo, sGruppo, TMisura, GMisura, Misura, Stagione, Anno, Sesso, Colore, Materiale, Box, PICFile As String
 

        'COSTANTI##################################### 
        ShopCOde = "011" 
        DelDate = DateTime.Now.Date 



        Try 
            sFIle.WriteLine(DateTime.Now.TimeOfDay.ToString & "Inizio query db paradox")
             lblProc.Text = "Query database Paradox" 
            Dim Sqltext As String = "SELECT A.[Articolo Fornitore], A.Descrizione, A.MARCA, A.[ARTICOLO ETICHETTA], A.[CATEGORIA MERCEOLOGICA], A.[TIPO ARTICOLO], V.[DENOMINAZIONE VARIANTE 1], L.[PREZZO IVATO E], G.[DATA ULTIMO MOVIMENTO], G.[QTA INIZIALE]-G.[QTA SCARICO]+G.[QTA CARICO] AS TOT,U.UBICAZIONE, V.[VARIANTE 2], V.[VARIANTE 1] FROM ARTICOLI A, ARTBASE V, UBICAZIONE U, GIACENZA G,LISTINO L WHERE V.[ARTICOLO INTERNO]=A.[ARTICOLO] AND  A.[UBICAZIONE]=U.[CODICE]  AND G.[ARTICOLO]=V.[ARTICOLO ETICHETTA] AND L.[ARTICOLO]=A.[ARTICOLO]"
             Dim oledbconnstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sdbPath & ";Extended Properties=Paradox 5.x;"
             oledbParadoxconn.ConnectionString = oledbconnstr 
            oledbParadoxconn.Open() 

            Dim myDataAdapter As New OleDb.OleDbDataAdapter(Sqltext, oledbParadoxconn)
             Dim dtgetdata As New DataSet 


            myDataAdapter.Fill(dtgetdata, "aaaa") 

            '   Me.dataGrid1.DataSource = dtgetdata 
            '   Me.dataGrid1.DataMember = "aaaa" 
            tblmy = dtgetdata.Tables("aaaa") 

            '#####COPIARE DALLA COPIA AL NUOVO 
            'backupXLS.xls -copia 
            'fogl.xls = nuovo 
            'cancella file se esiste già 
            MsgBox("fine query") 
            sFIle.WriteLine(DateTime.Now.TimeOfDay.ToString & "fine della query")
 
            If System.IO.File.Exists(sExcelPath & "\fogl.xls") Then System.IO.File.Delete(sExcelPath & "\fogl.xls")
 
            System.IO.File.Copy(sExcelPath & "\backupXLS.xls", sExcelPath & "\fogl.xls")
 
            'EXCEL ################## 
            xlApp = New Excel.Application 
            xlWorkBook = xlApp.Workbooks.Open(sExcelPath & "\fogl.xls") 
            xlWorkSheet = xlWorkBook.Worksheets("Foglio1") 
            MsgBox("fine copia xls") 


            'Progress bar########################################### 
            Progress.Maximum = tblmy.Rows.Count 
            Dim i As Integer = 0 
            lblProc.Text = "Inserimento nel file di EXCEL i dati" 
            sFIle.WriteLine(DateTime.Now.TimeOfDay.ToString & "fine della copia del file excel - inizio della scrittura nel file excel")
             WEBDESC = "" 
            PurchCost = "0" 
            Purchvat = "0" 
            PriceVat = "20" 
            Desc = "0" 
            DescFrom = "" 
            DescTo = "" 
            AdoCOst1 = "0" 
            AdoCost2 = "0" 
            Materiale = "" 
            Box = "" 
            PICFile = "" 

            MsgBox(tblmy.Rows.Count) 

            For Each x In tblmy.Rows 
                StockCode = x("Articolo Fornitore") 
                ShrtDesc = x("Descrizione") 
                BRAND = x("Marca") 
                VarDesc = ShrtDesc & " " & x("Variante 2") 
                Varcode = StockCode & "." & x("Variante 2") 
                VarAltCode = x("Articolo etichetta") 
                UnitPrice = x("PREZZO IVATO E") 
                InvtryDate = x("DATA ULTIMO MOVIMENTO") 
                Stock = x("TOT") 
                Gruppo = x("CATEGORIA MERCEOLOGICA") 
                sGruppo = x("Tipo Articolo") 
                If IsDBNull(x("Denominazione Variante 1")) Then 
                    TMisura = "" 
                Else 
                    TMisura = x("Denominazione Variante 1") 
                End If 
                GMisura = x("TIPO ARTICOLO") 
                If IsDBNull(x("Variante 1")) Then 
                    Misura = "UNICA" 
                Else 
                    Misura = x("Variante 1") 
                End If 
                Stagione = x("UBICAZIONE") 
                Anno = Stagione 
                Sesso = Gruppo 
                If IsDBNull(x("Variante 2")) Then 
                    Colore = "." 
                Else 
                    Colore = x("Variante 2").ToString 
                End If 

                'SCrivi sul foglio Excel 
                xlWorkSheet.Cells(i + 2, 1).value = ShopCOde 
                xlWorkSheet.Cells(i + 2, 2).value = DelDate 
                xlWorkSheet.Cells(i + 2, 3).value = StockCode 
                xlWorkSheet.Cells(i + 2, 4).value = ShrtDesc 
                xlWorkSheet.Cells(i + 2, 5).value = WEBDESC 
                xlWorkSheet.Cells(i + 2, 6).value = BRAND 
                xlWorkSheet.Cells(i + 2, 7).value = VarDesc 
                xlWorkSheet.Cells(i + 2, 8).value = Varcode 
                xlWorkSheet.Cells(i + 2, 9).value = VarAltCode 
                xlWorkSheet.Cells(i + 2, 10).value = PurchCost 
                xlWorkSheet.Cells(i + 2, 11).value = Purchvat 
                xlWorkSheet.Cells(i + 2, 12).value = UnitPrice 
                xlWorkSheet.Cells(i + 2, 13).value = PriceVat 
                xlWorkSheet.Cells(i + 2, 14).value = Desc 
                xlWorkSheet.Cells(i + 2, 15).value = DescFrom 
                xlWorkSheet.Cells(i + 2, 16).value = DescTo 
                xlWorkSheet.Cells(i + 2, 17).value = AdoCOst1 
                xlWorkSheet.Cells(i + 2, 18).value = AdoCost2 
                xlWorkSheet.Cells(i + 2, 19).value = InvtryDate 
                xlWorkSheet.Cells(i + 2, 20).value = Stock 
                xlWorkSheet.Cells(i + 2, 21).value = Gruppo 
                xlWorkSheet.Cells(i + 2, 22).value = sGruppo 
                xlWorkSheet.Cells(i + 2, 23).value = TMisura 
                xlWorkSheet.Cells(i + 2, 24).value = GMisura 
                xlWorkSheet.Cells(i + 2, 25).value = Misura 
                xlWorkSheet.Cells(i + 2, 26).value = Stagione 
                xlWorkSheet.Cells(i + 2, 27).value = Anno 
                xlWorkSheet.Cells(i + 2, 28).value = Sesso 
                xlWorkSheet.Cells(i + 2, 29).value = Colore 
                xlWorkSheet.Cells(i + 2, 30).value = Materiale 
                xlWorkSheet.Cells(i + 2, 31).value = Box 
                xlWorkSheet.Cells(i + 2, 32).value = PICFile 

                'indicazione 
                Progress.Value = i 
                i += 1 
            Next 
            sFIle.WriteLine(DateTime.Now.TimeOfDay.ToString & "fine della scrittura file excel")
 
            'chiudi foglio EXCEL##################################### 
            xlWorkBook.Close() 
            xlApp.Quit() 

            releaseObject(xlApp) 
            releaseObject(xlWorkBook) 
            releaseObject(xlWorkSheet) 
            lblProc.Text = "Fine Inserimento - Inizion trasfero dati FTP"

[cyanuro]

Perchè generare un file excel direttamente?
Puoi fare un file xml in tempi molto molto più brevi e aprire questo da excel.

[vankraster]

Originariamente inviato da cyanuro
Perchè generare un file excel direttamente?
Puoi fare un file xml in tempi molto molto più brevi e aprire questo da excel.

Dev'essere tutto automatizzato, non posso far fare al utente nulla.

[cyanuro]

Ok,
In ogni caso usando l'automazione per forza di cose non potrai mai avere una applicazione più veloce.
Quando arriverai al fatidico limite 65536 (fino a excel 2007 escluso) avrai raggiunto il numero di righe massisme per foglio excel.

[gibra]

Se la disposizione dei dati su foglio Excel non è in formato tabellare, mi sa che puoi fare poco per ottimizzare se copi ogni valore in ogni singola cella.

A parte che nel tuo codice non mi pare di aver visto alcun uso delle proprietà atte a velocizzare la procedura come: Application.Visible = False, Calculation, Interactive , DisplayAlerts , etc. (ma possono essermi sfuggite).


Se invece i dati vanno per la maggior parte copiati su Excel in formato tabellare, allora puoi provare ad usare ADODB (non credo che funzioni ADO.NET) e sfruttare il metodo CopyFromRecordset di Excel:
http://msdn.microsoft.com/en-us/library/aa165427(v=office.10).aspx
Sicuramente più veloce.

Comunque, anche dovendo disporre alcuni dati (non tutti) in celle predefinite, converrebbe ancora usare il metodo CopyFromRecordset, importando i dati in un altro foglio per poi aggiustarli nel foglio di destinazione.

[vankraster]

Originariamente inviato da gibra
Se la disposizione dei dati su foglio Excel non è in formato tabellare, mi sa che puoi fare poco per ottimizzare se copi ogni valore in ogni singola cella.

A parte che nel tuo codice non mi pare di aver visto alcun uso delle proprietà atte a velocizzare la procedura come: Application.Visible = False, Calculation, Interactive , DisplayAlerts , etc. (ma possono essermi sfuggite).


Se invece i dati vanno per la maggior parte copiati su Excel in formato tabellare, allora puoi provare ad usare ADODB (non credo che funzioni ADO.NET) e sfruttare il metodo CopyFromRecordset di Excel:
http://msdn.microsoft.com/en-us/library/aa165427(v=office.10).aspx
Sicuramente più veloce.

Comunque, anche dovendo disporre alcuni dati (non tutti) in celle predefinite, converrebbe ancora usare il metodo CopyFromRecordset, importando i dati in un altro foglio per poi aggiustarli nel foglio di destinazione.

Non posso sfruttare il metodo CopyFromRecordset di Excel perchè alcuni campi devono essere opportunamente formatati prima di inserirli nel Excel. Temo che non ci sia nulla da fare a riguardo...

[gibra]

Originariamente inviato da vankraster
Non posso sfruttare il metodo CopyFromRecordset di Excel perchè alcuni campi devono essere opportunamente formatati prima di inserirli nel Excel. Temo che non ci sia nulla da fare a riguardo...

In effetti questo è l'unico problema.
Non ti resta che impostare con le proprietà che ti ho indicato (se già non lo fai).