Visure v2

Imports iTextSharp.text
Imports iTextSharp.text.api
Imports iTextSharp.text.pdf
Imports iTextSharp.text.pdf.parser
Imports System.Text
Imports System.IO
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Text.RegularExpressions
Imports System.Data.SqlClient
Imports Microsoft.VisualBasic.CompilerServices
Imports System.Configuration



Public Class Visure

    Public Shared tabella As New Data.DataTable
    Public Shared CnSql As SqlConnection
    Public Shared CnSOGEUS As SqlConnection
    Public Shared PATH_SERVER_PDF As String
    Public Shared PATH_SERVER_REPORT As String
    Public Shared PATH_SERVER_SPEDIZIONI As String
    Public Shared PATH_SERVER_SOGEUS As String

    Public Shared IP_PATH_SERVER_PDF As String
    Public Shared PATH_SERVER_PDF_OUTPUT As String
    Public Shared IP_PATH_SERVER_PDF_OUTPUT As String
    Dim DirPdf As String = ""
    Dim fileExcel As String = ""
    Dim nProprietari As Integer
    Dim Tab As String
    Dim TempTab As String
    Dim connectionString As String = "Server=AUTOINFORMSRV;Database=SIMONE;User ID=utente421;Password=simone;Integrated Security=SSPI;"

    Private Sub Exec_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Exec.Click
        'Try
        'Controlli input
        Dim result As DialogResult = Windows.Forms.DialogResult.Yes
        Tab = "VISUREDAPDF_"
        TempTab = "TempExcelTable_"
        ProgressBar1.Step = 1
        If fileExcel = "" Or DirPdf = "" Then
            MsgBox("Selezionare un file excel ed una cartella pdf prima di continuare.")
            Exit Sub
        End If

        If Path.GetFileNameWithoutExtension(fileExcel).Contains(Tab) Then
            result = MessageBox.Show("Un file excel già elaborato è stato selezionato, sei sicuro di voler continuare?", "Attenzione!", MessageBoxButtons.YesNo)
        End If

        If result = DialogResult.Yes Then
            ProgressBar1.Value = 0
            Dim sDb As String
            If chkPgt.Checked Then
                sDb = "PEUGEOT"
                Tab = Tab.Replace("_", "_PAI")
                TempTab = TempTab.Replace("_", "_PAI")

            Else
                sDb = "CITROEN"
                Tab = Tab.Replace("_", "_CAI")
                TempTab = TempTab.Replace("_", "_CAI")
            End If
            CreateTable(TempTab)
            TableCleaner("Anagrafica")
            TableDeleter("Anagrafica")
            TableCleaner(Tab)
            TableDeleter(Tab)

            CnSqlOpen(sDb, connectionString)
            ProgressBar1.Visible = True
            LeggiRinoninaPdfByTarga(DirPdf, 1, "aa", 1, Tab)
        Else
            Exit Sub
        End If
        'Catch ex As Exception
        'MsgBox("Un errore è stato rilvevato in Exec_Click. Errore:" & vbLf & ex.Message)
        'Err.Clear()
        'End Try
    End Sub

    Public Function LeggiRinoninaPdfByTarga(ByVal sFolderPdfs As String, ByVal idTipoArchiviazionePDF As Integer, ByVal sDesTipo As String, ByVal lTestata As Long, ByVal Tab As String) As Boolean
        Dim dir As DirectoryInfo
        Dim file As FileInfo
        Dim s As String
        Dim sFileInput As String
        Dim sFileTxt As String
        Dim TableToExcel As New Data.DataTable
        Dim counter As Integer = 0
        Dim check As Boolean = True

        LeggiRinoninaPdfByTarga = True

        'Try

        dir = New DirectoryInfo(sFolderPdfs)

        For Each file In dir.GetFiles("*.PDF", SearchOption.TopDirectoryOnly)

            sFileInput = file.Directory.ToString + "\" + file.Name.ToString
            sFileTxt = file.Directory.ToString + "\" + file.Name.ToString.Replace("PDF", "txt")

            s = ParsePdfText(sFileInput)

            My.Computer.FileSystem.WriteAllText(sFileTxt, s, False)

            LeggiTargaEDateDaPdf(sFileTxt, sFileInput, idTipoArchiviazionePDF, sDesTipo, lTestata, Tab, check)

            counter += 1
            check = False
            If counter Mod 20 = 0 Then
                ProgressBar1.PerformStep()
            End If
        Next file
        ExcelManagment(fileExcel)
        Return LeggiRinoninaPdfByTarga
        'Catch ex As Exception
        'LeggiRinoninaPdfByTarga = False
        'Return LeggiRinoninaPdfByTarga
        'End Try

    End Function

    Public Shared Function ParsePdfText(ByVal sourcePDF As String, _
                                   Optional ByVal fromPageNum As Integer = 0, _
                                   Optional ByVal toPageNum As Integer = 0) As String
        Dim sb As New System.Text.StringBuilder
        Try
            Dim reader As New iTextSharp.text.pdf.PdfReader(sourcePDF)
            Dim pageBytes() As Byte = Nothing
            Dim token As iTextSharp.text.pdf.PRTokeniser = Nothing
            Dim tknType As Integer = -1
            Dim tknValue As String = String.Empty

            If fromPageNum = 0 Then
                fromPageNum = 1
            End If
            If toPageNum = 0 Then
                toPageNum = reader.NumberOfPages
            End If

            If fromPageNum > toPageNum Then
                Throw New ApplicationException("Errore parametri")
            End If

            For i As Integer = fromPageNum To toPageNum Step 1
                pageBytes = reader.GetPageContent(i)
                If Not IsNothing(pageBytes) Then

                    token = New iTextSharp.text.pdf.PRTokeniser(pageBytes)
                    While token.NextToken()
                        tknType = token.TokenType()
                        tknValue = token.StringValue
                        Select Case tknType

                            Case 1  ' iTextSharp.text.pdf.PRTokeniser.TK_NUMBER
                                Dim dValue As Double
                                Dim r As Double
                                If Double.TryParse(tknValue, dValue, Nothing, r) Then
                                    If dValue < -8000 Then
                                        sb.Append(r)
                                    End If
                                End If

                            Case 2  ' iTextSharp.text.pdf.PRTokeniser.TK_STRING
                                sb.Append(token.StringValue)
                            Case 3  ' iTextSharp.text.pdf.PRTokeniser.TK_NAME
                                '
                            Case 4  ' iTextSharp.text.pdf.PRTokeniser.TK_COMMENT
                                '
                            Case 5  ' iTextSharp.text.pdf.PRTokeniser.TK_START_ARRAY
                                '
                            Case 6  ' iTextSharp.text.pdf.PRTokeniser.TK_END_ARRAY
                                sb.Append(Environment.NewLine)
                                'sb.Append(" ")
                            Case 7  ' iTextSharp.text.pdf.PRTokeniser.TK_START_DIC
                                '
                            Case 8  ' iTextSharp.text.pdf.PRTokeniser.TK_END_DIC
                                '
                            Case 9  ' iTextSharp.text.pdf.PRTokeniser.TK_REF
                                '

                            Case 10 ' iTextSharp.text.pdf.PRTokeniser.TK_OTHER
                                Select Case tknValue
                                    Case "TJ"
                                        sb.Append(" ")
                                    Case "ET"      ', "TD", "Td" , "Tm" , "T*"
                                        sb.Append(Environment.NewLine)
                                End Select

                        End Select


                    End While
                End If
            Next i
            reader.Close()
        Catch ex As Exception
            MsgBox("Un errore è stato rilvevato in ParePdfText. Errore:" & vbLf & ex.Message)
            Err.Clear()
            Return String.Empty
        End Try

        Return sb.ToString()

    End Function

    Public Sub LeggiTargaEDateDaPdf(ByVal sfileTxtInput As String, ByVal sfilePdfInput As String, ByVal TipoArchiviazionePDF As Integer, ByVal DesTipo As String, ByVal lTestata As Long, ByVal Tab As String, ByVal check As Boolean)
        Dim FilePdf As FileInfo
        Dim sRiga As String
        Dim sTarga As String
        Dim sId As String
        Dim FileTxt As FileInfo
        FileTxt = New System.IO.FileInfo(sfileTxtInput)
        Dim reader As StreamReader
        Dim readerDate As StreamReader
        sId = FileTxt.Name.Substring(0, FileTxt.Name.LastIndexOf(".").ToString)
        FilePdf = New System.IO.FileInfo(sfilePdfInput)
        Dim text As New StringBuilder()
        reader = My.Computer.FileSystem.OpenTextFileReader(sfileTxtInput)
        readerDate = My.Computer.FileSystem.OpenTextFileReader(sfileTxtInput)
        Try

            Dim sqlCmd As String = "CREATE Table " & Tab & " (Targa NVarChar (7) NOT NULL, "
            Dim sqlIns As String = "INSERT INTO " & Tab & " VALUES(@Targa, "
            Dim MyCmdIns As New SqlCommand(sqlIns, CnSql)
            Dim DateTrovate As MatchCollection = Regex.Matches(text.ToString, "[0-9]*/[0-9]*/[0-9]{4}")
            sRiga = reader.ReadLine.TrimStart
            sTarga = Mid(sRiga, 22, 7)
            MyCmdIns.Parameters.Add("@Targa", SqlDbType.NVarChar)
            MyCmdIns.Parameters("@Targa").Value = sTarga

            'Lettura campi scelti e setup sqlCmd

            Dim telaioInizio As Integer
            Dim telaioFine As Integer
            Dim telaio As String

            telaioInizio = sRiga.IndexOf("Telaio") + 6
            telaioFine = sRiga.IndexOf("Fabbrica / Tipo")
            telaio = sRiga.Substring(telaioInizio, telaioFine - telaioInizio)

            sqlCmd = sqlCmd & "Telaio NVarChar (17) NULL, "
            sqlIns = sqlIns & "@Telaio, "
            MyCmdIns.Parameters.Add("@Telaio", SqlDbType.NVarChar)
            MyCmdIns.Parameters("@Telaio").Value = telaio




            Dim produttoreInizio As Integer
            Dim produttoreFine As Integer
            Dim produttore As String

            produttoreInizio = sRiga.IndexOf("Fabbrica / Tipo") + 15
            produttoreFine = sRiga.IndexOf("Data Immatricolazione")
            produttore = sRiga.Substring(produttoreInizio, produttoreFine - produttoreInizio)

            sqlCmd = sqlCmd & "Produttore NVarChar (100) NULL, "
            sqlIns = sqlIns & "@Produttore, "
            MyCmdIns.Parameters.Add("@Produttore", SqlDbType.NVarChar)
            MyCmdIns.Parameters("@Produttore").Value = produttore



            Dim immatricolazioneInizio As Integer
            Dim immatricolazioneFine As Integer
            Dim immatricolazione As String

            immatricolazioneInizio = sRiga.IndexOf("Data Aggiornamento Carta Circolazione") + Len("Data Aggiornamento Carta Circolazione")
            immatricolazioneFine = sRiga.IndexOf("KW")
            immatricolazione = sRiga.Substring(immatricolazioneInizio, immatricolazioneFine - immatricolazioneInizio)

            sqlCmd = sqlCmd & "DataImmatricolazione smalldatetime NULL, "
            sqlIns = sqlIns & "@DataImmatricolazione, "
            MyCmdIns.Parameters.Add("@DataImmatricolazione", SqlDbType.SmallDateTime)
            MyCmdIns.Parameters("@DataImmatricolazione").Value = Convert.ToDateTime(immatricolazione.Replace("#", ""))


            Dim cartaInizio As Integer
            Dim cartaFine As Integer
            Dim carta As String

            cartaInizio = sRiga.IndexOf("Data Aggiornamento Carta Circolazione") + Len("Data Aggiornamento Carta Circolazione")
            cartaFine = sRiga.IndexOf("KW")
            carta = sRiga.Substring(cartaInizio, cartaFine - cartaInizio)

            sqlCmd = sqlCmd & "DataCartaCircolazione smalldatetime NULL, "
            sqlIns = sqlIns & "@DataCartaCircolazione, "
            MyCmdIns.Parameters.Add("@DataCartaCircolazione", SqlDbType.SmallDateTime)
            MyCmdIns.Parameters("@DataCartaCircolazione").Value = Convert.ToDateTime(carta)



            Dim kwInizio As Integer
            Dim kwFine As Integer
            Dim kw As String

            kwInizio = sRiga.IndexOf("KW") + 2
            kwFine = sRiga.IndexOf("Classe / Uso")
            kw = sRiga.Substring(kwInizio, kwFine - kwInizio)

            sqlCmd = sqlCmd & "KW decimal(10,2) NULL, "
            sqlIns = sqlIns & "@KW, "
            MyCmdIns.Parameters.Add("@KW", SqlDbType.Decimal)
            MyCmdIns.Parameters("@KW").Value = Convert.ToDecimal(kw.Replace(",", "."))



            Dim classeInizio As Integer
            Dim classeFine As Integer
            Dim classe As String

            classeInizio = sRiga.IndexOf("Classe / Uso") + 12
            classeFine = sRiga.IndexOf("Carrozzeria")
            classe = sRiga.Substring(classeInizio, classeFine - classeInizio)

            sqlCmd = sqlCmd & "Classe NVarChar(100) NULL, "
            sqlIns = sqlIns & "@Classe, "
            MyCmdIns.Parameters.Add("@Classe", SqlDbType.NVarChar)
            MyCmdIns.Parameters("@Classe").Value = classe



            Dim cilindrataInizio As Integer
            Dim cilindrataFine As Integer
            Dim cilindrata As String

            cilindrataInizio = sRiga.IndexOf("Cilindrata") + 10
            cilindrataFine = sRiga.IndexOf("Alimentazione")
            cilindrata = sRiga.Substring(cilindrataInizio, cilindrataFine - cilindrataInizio)

            sqlCmd = sqlCmd & "Cilindrata nvarchar(4) NULL, "
            sqlIns = sqlIns & "@Cilindrata, "
            MyCmdIns.Parameters.Add("@Cilindrata", SqlDbType.NVarChar)
            MyCmdIns.Parameters("@Cilindrata").Value = cilindrata



            Dim alimentazioneInizio As Integer
            Dim alimentazioneFine As Integer
            Dim alimentazione As String
            alimentazioneInizio = sRiga.IndexOf("Alimentazione") + 13
            alimentazioneFine = sRiga.IndexOf("Tara")
            alimentazione = sRiga.Substring(alimentazioneInizio, alimentazioneFine - alimentazioneInizio)

            sqlCmd = sqlCmd & "Alimentazione NVarChar(100) NULL, "
            sqlIns = sqlIns & "@Alimentazione, "
            MyCmdIns.Parameters.Add("@Alimentazione", SqlDbType.NVarChar)
            MyCmdIns.Parameters("@Alimentazione").Value = alimentazione



            Dim taraInizio As Integer
            Dim taraFine As Integer
            Dim tara As String

            taraInizio = sRiga.IndexOf("Tara") + 4
            taraFine = sRiga.IndexOf("Portata")
            tara = sRiga.Substring(taraInizio, taraFine - taraInizio)

            sqlCmd = sqlCmd & "Tara NVarChar(10) NULL, "
            sqlIns = sqlIns & "@Tara, "
            MyCmdIns.Parameters.Add("@Tara", SqlDbType.NVarChar)
            MyCmdIns.Parameters("@Tara").Value = tara



            Dim postiInizio As Integer
            Dim postiFine As Integer
            Dim posti As String

            postiInizio = sRiga.IndexOf("Posti n.ro") + 10
            postiFine = sRiga.IndexOf("Assi n.ro")
            posti = sRiga.Substring(postiInizio, postiFine - postiInizio)

            sqlCmd = sqlCmd & "Posti NVarChar(10) NULL, "
            sqlIns = sqlIns & "@Posti, "
            MyCmdIns.Parameters.Add("@Posti", SqlDbType.NVarChar)
            MyCmdIns.Parameters("@Posti").Value = posti



            Dim assiInizio As Integer
            Dim assiFine As Integer
            Dim assi As String

            assiInizio = sRiga.IndexOf("Assi n.ro") + 9
            assiFine = sRiga.IndexOf("Prima Formalita'")
            assi = sRiga.Substring(assiInizio, assiFine - assiInizio)

            sqlCmd = sqlCmd & "Assi NVarChar(10) NULL, "
            sqlIns = sqlIns & "@Assi, "
            MyCmdIns.Parameters.Add("@Assi", SqlDbType.NVarChar)
            MyCmdIns.Parameters("@Assi").Value = assi



            Dim primaFormalitaInizio As Integer
            Dim primaFormalitaFine As Integer
            Dim primaFormalita As String

            primaFormalitaInizio = sRiga.IndexOf("Prima Formalita'") + 16
            primaFormalitaFine = sRiga.IndexOf("Data Prima Formalita'")
            primaFormalita = sRiga.Substring(primaFormalitaInizio, primaFormalitaFine - primaFormalitaInizio)

            sqlCmd = sqlCmd & "PrimaFormalita nvarchar(100) NULL, "
            sqlIns = sqlIns & "@PrimaFomalita, "
            MyCmdIns.Parameters.Add("@PrimaFomalita", SqlDbType.NVarChar)
            MyCmdIns.Parameters("@PrimaFomalita").Value = primaFormalita



            Dim dataPrimaFormalitaInizio As Integer
            Dim dataPrimaFormalitaFine As Integer
            Dim dataPrimaFormalita As String

            dataPrimaFormalitaInizio = sRiga.IndexOf("Data Prima Formalita'") + 21
            dataPrimaFormalitaFine = dataPrimaFormalitaInizio + 10
            dataPrimaFormalita = sRiga.Substring(dataPrimaFormalitaInizio, dataPrimaFormalitaFine - dataPrimaFormalitaInizio)

            sqlCmd = sqlCmd & "DataPrimaFormalita smalldatetime NULL, "
            sqlIns = sqlIns & "@DataPrimaFormalita, "
            MyCmdIns.Parameters.Add("@DataPrimaFormalita", SqlDbType.SmallDateTime)
            MyCmdIns.Parameters("@DataPrimaFormalita").Value = Convert.ToDateTime(dataPrimaFormalita)



            Dim iultimaforminizio As Integer
            Dim iultimaformfine As Integer
            Dim sFormalita As String

            iultimaforminizio = sRiga.IndexOf("Ultima Formalita'") + 17
            iultimaformfine = sRiga.IndexOf("Data Ultima Formalita'")
            sFormalita = sRiga.Substring(iultimaforminizio, iultimaformfine - iultimaforminizio)

            sqlCmd = sqlCmd & "UltimaFormalita NVarChar(100) NULL, "
            sqlIns = sqlIns & "@UltimaFormalita, "
            MyCmdIns.Parameters.Add("@UltimaFormalita", SqlDbType.NVarChar)
            MyCmdIns.Parameters("@UltimaFormalita").Value = sFormalita



            Dim sDataUltForm As String
            Dim sDataUltFormInizio As Integer
            Dim sDataUltFormFine As Integer

            sDataUltFormInizio = sRiga.IndexOf("Data Ultima Formalita'") + 22
            sDataUltFormFine = sDataUltFormInizio + 10
            sDataUltForm = sRiga.Substring(sDataUltFormInizio, sDataUltFormFine - sDataUltFormInizio)

            sqlCmd = sqlCmd & "DataUltimaFormalita smalldatetime NULL, "
            sqlIns = sqlIns & "@DataUltimaFormalita, "
            MyCmdIns.Parameters.Add("@DataUltimaFormalita", SqlDbType.SmallDateTime)
            MyCmdIns.Parameters("@DataUltimaFormalita").Value = Convert.ToDateTime(sDataUltForm)



            Dim attoInizio As Integer
            Dim attoFine As Integer
            Dim atto As String

            attoInizio = sRiga.IndexOf("Atto") + 4
            attoFine = sRiga.IndexOf("Data Atto")
            atto = sRiga.Substring(attoInizio, attoFine - attoInizio)

            sqlCmd = sqlCmd & "Atto NVarChar(100) NULL, "
            sqlIns = sqlIns & "@Atto, "
            MyCmdIns.Parameters.Add("@Atto", SqlDbType.NVarChar)
            MyCmdIns.Parameters("@Atto").Value = atto



            Dim dataAttoInizio As Integer
            Dim dataAttoFine As Integer
            Dim dataAtto As String

            dataAttoInizio = sRiga.IndexOf("Data Atto") + 9
            dataAttoFine = sRiga.IndexOf("Prezzo del Veicolo")
            dataAtto = sRiga.Substring(dataAttoInizio, dataAttoFine - dataAttoInizio)

            sqlCmd = sqlCmd & "DataAtto smalldatetime NULL, "
            sqlIns = sqlIns & "@DataAtto, "
            MyCmdIns.Parameters.Add("@DataAtto", SqlDbType.SmallDateTime)
            MyCmdIns.Parameters("@DataAtto").Value = Convert.ToDateTime(dataAtto)



            Dim prezzoVeicoloInizio As Integer
            Dim prezzoVeicoloFine As Integer
            Dim prezzoVeicolo As String

            prezzoVeicoloInizio = sRiga.IndexOf("Prezzo del Veicolo") + 18
            prezzoVeicoloFine = sRiga.IndexOf("Proprietario")
            prezzoVeicolo = sRiga.Substring(prezzoVeicoloInizio, prezzoVeicoloFine - prezzoVeicoloInizio).Replace("*", "").Replace(".", "").Replace(",", ".").Replace(" ", "").ToUpper.Replace("EURO", "")

            sqlCmd = sqlCmd & "PrezzoVeicolo decimal(10,2) NULL, "
            sqlIns = sqlIns & "@PrezzoVeicolo, "
            MyCmdIns.Parameters.Add("@PrezzoVeicolo", SqlDbType.Decimal)
            MyCmdIns.Parameters("@PrezzoVeicolo").Value = Convert.ToDecimal(prezzoVeicolo.Substring(0, prezzoVeicolo.Length - 3))

            'Chiusura Select / Insert
            sqlIns = sqlIns.Substring(0, sqlIns.Length - 2)
            sqlIns = sqlIns & ")"
            sqlCmd = sqlCmd & "PRIMARY KEY(Targa))"

            If check Then
                Using myCmd As New SqlCommand(sqlCmd, CnSql)
                    myCmd.CommandTimeout = 0
                    myCmd.CommandType = CommandType.Text
                    myCmd.ExecuteNonQuery()
                End Using
            Else
                sqlCmd = ""
            End If
            MyCmdIns.CommandText = sqlIns
            MyCmdIns.CommandTimeout = 0
            MyCmdIns.CommandType = CommandType.Text
            MyCmdIns.ExecuteNonQuery()




            'Gestione blocco propretario
            Dim counter As Integer = 0
            MyCmdIns = New SqlCommand(sqlIns, CnSql)

            'Ridimensionamento sRiga al blocco proprietario
            sRiga = sRiga.Substring(sRiga.IndexOf("Proprietario"), sRiga.IndexOf("ANNOTAZIONI") - sRiga.IndexOf("Proprietario"))

            'Conto n proprietari
            For Each dato As Match In Regex.Matches(sRiga, "Proprietario")
                counter += 1
            Next
            nProprietari = counter

            'Dichiarazione array di indici blocco proprietario
            Dim indexProprietario(counter) As Integer
            Dim indexTipo(counter) As Integer
            Dim indexDataNascita(counter) As Integer
            Dim indexComuneNascita(counter) As Integer
            Dim indexComuneResidenza(counter) As Integer
            Dim indexIndirizzo(counter) As Integer

            'Dichiarazione array blocco proprietario
            Dim proprietario(counter) As String
            Dim tipo(counter) As String
            Dim nascita(counter) As String
            Dim comuneNascita(counter) As String
            Dim residenza(counter) As String
            Dim indirizzo(counter) As String
            Dim arrCmd(counter) As String
            Dim arrIns(counter) As String

            'Dichiarazione Parametri SQL
            MyCmdIns.Parameters.Add("@Targa", SqlDbType.NVarChar)
            MyCmdIns.Parameters.Add("@Proprietario", SqlDbType.NVarChar)
            MyCmdIns.Parameters.Add("@Tipo", SqlDbType.NVarChar)
            MyCmdIns.Parameters.Add("@DataNascita", SqlDbType.SmallDateTime)
            MyCmdIns.Parameters.Add("@ComuneNascita", SqlDbType.NVarChar)
            MyCmdIns.Parameters.Add("@ComuneResidenza", SqlDbType.NVarChar)
            MyCmdIns.Parameters.Add("@Indirizzo", SqlDbType.NVarChar)

            'inizializzazione array di indici blocco proprietario
            counter = 0


            'Proprietario
            For Each dato As Match In Regex.Matches(sRiga, "Proprietario")
                indexProprietario(counter) = dato.Index
                counter += 1
            Next
            counter = 0

            'Sesso / Tipo Società
            For Each dato As Match In Regex.Matches(sRiga, "Sesso / Tipo Societa'")
                indexTipo(counter) = dato.Index
                counter += 1
            Next
            counter = 0

            'Data di nascita
            For Each dato As Match In Regex.Matches(sRiga, "Data di nascita")
                indexDataNascita(counter) = dato.Index
                counter += 1
            Next
            counter = 0

            'Comune di nascita
            For Each dato As Match In Regex.Matches(sRiga, "Comune di nascita")
                indexComuneNascita(counter) = dato.Index
                counter += 1
            Next
            counter = 0

            'Comune di residenza
            For Each dato As Match In Regex.Matches(sRiga, "Comune di residenza")
                indexComuneResidenza(counter) = dato.Index
                counter += 1
            Next
            counter = 0

            'Indirizzo
            For Each dato As Match In Regex.Matches(sRiga, "Indirizzo")
                indexIndirizzo(counter) = dato.Index
                counter += 1
            Next
            counter = 0

            'Estrazione blocco proprietario da PDF
            For Each dato As Match In Regex.Matches(sRiga, "Proprietario")
                sqlCmd = "CREATE Table Anagrafica(Targa_FK NVarChar (7) FOREIGN KEY REFERENCES " & Tab & "(Targa), "
                sqlIns = "INSERT INTO Anagrafica VALUES(@Targa, "

                'Targa_FK
                MyCmdIns.Parameters("@Targa").Value = sTarga

                'Proprietario
                proprietario(counter) = sRiga.Substring(indexProprietario(counter) + Len("Proprietario"), indexTipo(counter) - (indexProprietario(counter) + Len("Proprietario")))
                arrCmd(counter) = "Proprietario nvarchar(100) NULL, "
                arrIns(counter) = "@Proprietario, "
                MyCmdIns.Parameters("@Proprietario").Value = proprietario(counter)
                sqlCmd = sqlCmd & arrCmd(counter)
                sqlIns = sqlIns & arrIns(counter)


                'Sesso / Tipo Società
                tipo(counter) = sRiga.Substring(indexTipo(counter) + Len("Sesso / Tipo Societa'"), indexDataNascita(counter) - (indexTipo(counter) + Len("Sesso / Tipo Societa'")))
                arrCmd(counter) = "Tipo nvarchar(100) NULL, "
                arrIns(counter) = "@Tipo, "
                MyCmdIns.Parameters("@Tipo").Value = tipo(counter)
                sqlCmd = sqlCmd & arrCmd(counter)
                sqlIns = sqlIns & arrIns(counter)


                'Data di Nascita
                nascita(counter) = sRiga.Substring(indexDataNascita(counter) + Len("Data di nascita"), indexComuneNascita(counter) - (indexDataNascita(counter) + Len("Data di nascita")))
                arrCmd(counter) = "DataNascita smalldatetime NULL, "
                arrIns(counter) = "@DataNascita, "
                MyCmdIns.Parameters("@DataNascita").Value = Convert.ToDateTime(nascita(counter))
                sqlCmd = sqlCmd & arrCmd(counter)
                sqlIns = sqlIns & arrIns(counter)


                'Comune di Nascita
                comuneNascita(counter) = sRiga.Substring(indexComuneNascita(counter) + Len("Comune di nascita"), indexComuneResidenza(counter) - (indexComuneNascita(counter) + Len("Comune di nascita")))
                arrCmd(counter) = "ComuneNascita nvarchar(100) NULL, "
                arrIns(counter) = "@ComuneNascita, "
                MyCmdIns.Parameters("@ComuneNascita").Value = comuneNascita(counter)
                sqlCmd = sqlCmd & arrCmd(counter)
                sqlIns = sqlIns & arrIns(counter)


                'Comune di Residenza
                residenza(counter) = sRiga.Substring(indexComuneResidenza(counter) + Len("Comune di residenza"), indexIndirizzo(counter) - (indexComuneResidenza(counter) + Len("Comune di residenza")))
                arrCmd(counter) = "ComuneResidenza nvarchar(100) NULL, "
                arrIns(counter) = "@ComuneResidenza, "
                MyCmdIns.Parameters("@ComuneResidenza").Value = residenza(counter)
                sqlCmd = sqlCmd & arrCmd(counter)
                sqlIns = sqlIns & arrIns(counter)


                'Indirizzo
                If counter = 0 And nProprietari <> 1 Then
                    indirizzo(counter) = sRiga.Substring(indexIndirizzo(counter) + Len("Indirizzo"), indexProprietario(counter + 1) - (indexIndirizzo(counter) + Len("Indirizzo")))
                Else
                    indirizzo(counter) = sRiga.Substring(indexIndirizzo(counter) + Len("Indirizzo"), sRiga.Length() - (indexIndirizzo(counter) + Len("Indirizzo")))
                End If
                arrCmd(counter) = "Indirizzo nvarchar(100) NULL, "
                arrIns(counter) = "@Indirizzo, "
                MyCmdIns.Parameters("@Indirizzo").Value = indirizzo(counter)
                sqlCmd = sqlCmd & arrCmd(counter)
                sqlIns = sqlIns & arrIns(counter)

                'Chiusura Select / Insert
                sqlIns = sqlIns.Substring(0, sqlIns.Length - 2) & ")"
                sqlCmd = sqlCmd & "ID int IDENTITY(1,1) PRIMARY KEY)"
                If counter = 0 And check Then
                    Using myCmd As New SqlCommand(sqlCmd, CnSql)
                        myCmd.CommandTimeout = 0
                        myCmd.CommandType = CommandType.Text
                        myCmd.ExecuteNonQuery()
                    End Using
                Else
                    sqlCmd = ""
                End If
                MyCmdIns.CommandText = sqlIns
                MyCmdIns.CommandTimeout = 0
                MyCmdIns.CommandType = CommandType.Text
                MyCmdIns.ExecuteNonQuery()

                counter += 1
            Next

            reader.Close()
            readerDate.Close()

            'Elimina il file txt analizzato
            FileTxt.Delete()
        Catch ex As Exception
            reader.Close()
            readerDate.Close()
            FileTxt.Delete()
            Err.Clear()
        End Try

    End Sub

    Public Shared Function CnSqlOpen(ByVal db As String, ByVal connectionString As String) As Boolean
        Try
            Dim flag As Boolean
            If (CnSql Is Nothing) Then
                CnSql = New SqlConnection(connectionString)
                CnSql.Open()
                Return True
            End If
            If (CnSql.State = ConnectionState.Open) Then
                Return True
            End If
            CnSql = New SqlConnection(connectionString)
            CnSql.Open()
            flag = True
            Return flag
        Catch ex As Exception
            MsgBox("Un errore è stato rilevato in CnSqlOpen. Errore:" & vbLf & ex.Message)
        End Try
    End Function

    Public Shared Function fsql(ByVal argDato As Object, ByVal argTipo As String) As Object
        Try
            Select Case argTipo
                'Intero'
                Case "I"

                    If IsNull(argDato) Then

                        Return " null "
                    Else
                        If Len(argDato) = 0 Then
                            Return " null "
                        Else
                            Return argDato
                        End If
                    End If
                    'Data'
                Case "D"
                    If IsNull(argDato) Then
                        Return " null "
                    Else
                        If Len(argDato) = 0 Then
                            Return " null "
                        Else
                            Return "'" & argDato & "'"
                        End If
                    End If
                    'Testo'
                Case "T"
                    If IsNull(argDato) Then
                        Return " null "
                    Else
                        If Len(argDato) = 0 Then
                            Return " null "
                        Else
                            If InStr(argDato, Chr(39)) = 0 Then
                                Return "'" & (argDato) & "'"
                            Else
                                Return "'" & Replace(argDato, "'", "''") & "'"
                            End If
                        End If
                    End If
                    'float'
                Case "F"
                    If IsNull(argDato) Then
                        Return " null "
                    Else
                        If Len(argDato) = 0 Then
                            Return " null "
                        Else
                            If argDato - Int(argDato) <> 0 Then
                                Return Replace(argDato, ",", ".")
                            Else
                                Return argDato
                            End If
                        End If
                    End If

                    'boolean'
                Case "B"
                    If IsNull(argDato) Then
                        Return " null "
                    Else
                        If argDato = "Vero" Then
                            Return " 1 "
                        Else
                            Return " 0 "
                        End If
                    End If
            End Select
        Catch ex As Exception
            MsgBox("Un errore è stato rilevato in fsql. Errore:" & vbLf & ex.Message)
            Err.Clear()
        End Try
        Return "null"
    End Function

    Public Shared Function IsNull(ByVal arg As Object) As Boolean
        Try
            If Len(arg) = 0 Then
                Return True
            Else
                Return False
            End If


        Catch ex As Exception
            Return True
        End Try
    End Function

    Private Sub chkCitroen_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkCit.CheckedChanged
        chkPgt.Checked = Not chkCit.Checked
    End Sub

    Private Sub chkPgt_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkPgt.CheckedChanged
        chkCit.Checked = Not chkPgt.Checked
    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        chkPgt.Checked = True
        Targa.Checked = True
        Telaio.Checked = True
        UltimaFormalita.Checked = True
        DataUltimaFormalita.Checked = True
        PrezzoVeicolo.Checked = True
        Proprietario.Checked = True
        SessoTipoSocieta.Checked = True
        DataNascita.Checked = True
        TableCleaner("Anagrafica")
        TableDeleter("Anagrafica")
        TableCleaner(Tab)
        TableDeleter(Tab)
    End Sub

    Private Sub UP_Excel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UP_Excel.Click
        Try

            Excel_Check.ForeColor = Color.Green
            Excel_Check.Text = ""
            OpenFileExcel.InitialDirectory = "C:\"
            OpenFileExcel.Filter = "File Excel|*.xls; *.xlsx"

            If OpenFileExcel.ShowDialog = DialogResult.OK Then
                fileExcel = OpenFileExcel.FileName
                Excel_Check.Text = "File selezionato correttamente."
                Excel_TextBox.Text = fileExcel
                If Path.GetFileNameWithoutExtension(fileExcel).ToUpper.Contains("CIT") Or Path.GetFileNameWithoutExtension(fileExcel).ToUpper.Contains("CAI") Then
                    chkCit.Checked = True
                Else
                    chkPgt.Checked = True
                End If
            Else
                Excel_Check.ForeColor = Color.Red
                Excel_Check.Text = "Impossibile selezionare il file."
                Exit Sub
            End If
        Catch ex As Exception
            MsgBox("Un errore è stato rilevato in Excel_Click. Errore:" & vbLf & ex.Message)
            Err.Clear()
        End Try
    End Sub

    Private Sub UP_Pdf_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UP_Pdf.Click
        Try
            Pdf_Check.ForeColor = Color.Green
            Pdf_Check.Text = ""

            If OpenDirPdf.ShowDialog = DialogResult.OK Then
                DirPdf = OpenDirPdf.SelectedPath
                Pdf_Check.Text = "Cartella selezionata correttamente."
                PDF_TextBox.Text = DirPdf
            Else
                Pdf_Check.ForeColor = Color.Red
                Pdf_Check.Text = "Impossibile selezionare la cartella."
                Exit Sub
            End If
        Catch ex As Exception
            MsgBox("Un errore è stato rilevato in Pdf_Click. Errore:" & vbLf & ex.Message)
            Err.Clear()
        End Try
    End Sub

    Private Sub ExcelManagment(ByVal filePath As String)
        Try
            Dim sFileName As String = filePath
            Dim MyConnection As OleDbConnection
            Dim DtTab As New System.Data.DataTable
            Dim sTipoFile As String
            ProgressBar1.Step = 10

            'Individuo il tipo di file Excel scelto
            If IO.Path.GetExtension(sFileName.ToUpper) = ".XLS" Then
                sTipoFile = "Excel 8.0"
                MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & sFileName & "';Extended Properties=" & sTipoFile & ";")

            Else
                sTipoFile = "Excel 12.0"
                MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & sFileName & "';Extended Properties=" & sTipoFile & ";")
            End If
            ProgressBar1.PerformStep()

            ' Apre connessione con Excel
            MyConnection.Open()

            'Popola data table
            Dim myTableName = MyConnection.GetSchema("Tables").Rows(0)("TABLE_NAME")
            Dim MyCommand As OleDbDataAdapter = New OleDbDataAdapter(String.Format("SELECT * FROM [{0}] ", myTableName), MyConnection)
            MyCommand.TableMappings.Add("Table", "    ")
            ProgressBar1.PerformStep()
            MyCommand.Fill(DtTab)
            MyConnection.Close()

            'insert and select datatable into db
            TableCleaner(TempTab)
            DataTableToSql(DtTab)
            Dim sqlCmd = "SELECT " & TempTab & ".Targa as 'Targa ACI', Modello, Dealer, CodiceDealer as 'Codice Dealer', Venduto as 'Venduta a', Valore as 'Valore vendite Eurotax', "

            'Popola tabella risultante
            If chkPgt.Checked Then
                sqlCmd = "SELECT " & TempTab & ".Targa as 'Targa ACI', " & TempTab & ".DataVendita as 'Data di Vendita', Modello, Dealer, CodiceDealer as 'Codice Dealer', Venduto as 'Venduta a', Valore as 'Valore vendite Eurotax', "
            End If

            'Creazione Select
            If Telaio.Checked Then
                sqlCmd = sqlCmd & "Telaio, "
            End If

            If Produttore.Checked Then
                sqlCmd = sqlCmd & "Produttore, "
            End If

            If Immatricolazione.Checked Then
                sqlCmd = sqlCmd & "DataImmatricolazione as 'Data di Immatricolazione', "
            End If

            If CartaDiCircolazione.Checked Then
                sqlCmd = sqlCmd & "DataCartaCircolazione as 'Data Aggiornamento Carta di Circolazione', "
            End If

            If KW.Checked Then
                sqlCmd = sqlCmd & "KW, "
            End If

            If Classe.Checked Then
                sqlCmd = sqlCmd & "Classe as 'Classe/Uso', "
            End If

            If Cilindrata.Checked Then
                sqlCmd = sqlCmd & "Cilindrata, "
            End If

            If Alimentazione.Checked Then
                sqlCmd = sqlCmd & "Alimentazione, "
            End If

            If Tara.Checked Then
                sqlCmd = sqlCmd & "Tara, "
            End If

            If Posti.Checked Then
                sqlCmd = sqlCmd & "Posti as 'Posti n.ro', "
            End If

            If Assi.Checked Then
                sqlCmd = sqlCmd & "Assi as 'Assi n.ro', "
            End If

            If PrimaFormalita.Checked Then
                sqlCmd = sqlCmd & "PrimaFormalita as 'Prima Formalità', "
            End If

            If DataPrimaFormalita.Checked Then
                sqlCmd = sqlCmd & "DataPrimaFormalita as 'Data Prima Formalità', "
            End If

            If UltimaFormalita.Checked Then
                sqlCmd = sqlCmd & "UltimaFormalita as 'Ultima Formalità', "
            End If

            If DataUltimaFormalita.Checked Then
                sqlCmd = sqlCmd & "DataUltimaFormalita as 'Data Ultima Formalità', "

            End If

            If Atto.Checked Then
                sqlCmd = sqlCmd & "Atto, "
            End If

            If DataAtto.Checked Then
                sqlCmd = sqlCmd & "DataAtto as 'Data Atto', "
            End If

            If PrezzoVeicolo.Checked Then
                sqlCmd = sqlCmd & "PrezzoVeicolo as 'Prezzo del Veicolo', "
            End If

            If Proprietario.Checked Then
                sqlCmd = sqlCmd & "Proprietario, "
            End If

            If SessoTipoSocieta.Checked Then
                sqlCmd = sqlCmd & "Tipo as 'Sesso/Tipo Società', "
            End If

            If DataNascita.Checked Then
                sqlCmd = sqlCmd & "DataNascita as 'Data di Nascita', "
            End If

            If Comune.Checked Then
                sqlCmd = sqlCmd & "ComuneNascita as 'Comune di Nascita', "
            End If

            If Residenza.Checked Then
                sqlCmd = sqlCmd & "ComuneResidenza as 'Comune di Residenza', "
            End If

            If Indirizzo.Checked Then
                sqlCmd = sqlCmd & "Indirizzo as 'Indirizzo di Residenza', "
            End If
            sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 2) & " FROM " & TempTab & ", " & Tab & ", Anagrafica WHERE " & TempTab & ".Targa = " & Tab & ".Targa AND " & Tab & ".Targa = Anagrafica.Targa_FK"

            Dim RemoteDtTab As New System.Data.DataTable
            Using myConn As New SqlConnection(connectionString)
                myConn.Open()
                Using loader As New SqlDataAdapter(sqlCmd, myConn)
                    loader.Fill(RemoteDtTab)
                    ProgressBar1.Value = 100
                    MsgBox("Elaborazione Completata.")
                    ProgressBar1.Value = 0
                End Using
            End Using

            'Export to excel
            MsgBox("Inizio salvataggio file excel.")
            Dim strFilePath As String = filePath.Replace(Path.GetFileName(filePath), "")
            ExcelExport(RemoteDtTab, strFilePath)

        Catch ex As Exception
            MsgBox("Un errore è stato rilevato in ExcelManagment. Errore:" & vbLf & ex.Message)
            ProgressBar1.Value = 0
            Err.Clear()
            Exit Sub
        End Try
    End Sub

    Private Sub ExcelExport(ByVal ExportDataTable As DataTable, ByVal filePath As String)
        Try
            ProgressBar1.Value = 0
            ProgressBar1.Step = 1
            ProgressBar1.PerformStep()

            'Dichiarazione variabili
            Dim excel As New Microsoft.Office.Interop.Excel.Application
            Dim wBook As Microsoft.Office.Interop.Excel.Workbook
            Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
            wBook = excel.Workbooks.Add()
            wSheet = wBook.ActiveSheet()
            Dim dt As System.Data.DataTable = ExportDataTable
            Dim dc As System.Data.DataColumn
            Dim dr As System.Data.DataRow
            Dim colIndex As Integer = 0
            Dim rowIndex As Integer = 0
            Dim Counter As Integer = 0
            Dim indexProprietario As Integer
            Dim indexTipo As Integer
            Dim indexDataN As Integer
            Dim indexComuneN As Integer
            Dim indexComuneRes As Integer
            Dim indexIndirizzo As Integer

            'Scrittura header e salvataggio index blocco proprietari
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                excel.Cells(1, colIndex) = dc.ColumnName
                If dc.ColumnName = "Proprietario" Then
                    indexProprietario = colIndex
                End If
                If dc.ColumnName = "Sesso/Tipo Società" Then
                    indexTipo = colIndex
                End If
                If dc.ColumnName = "Data di Nascita" Then
                    indexDataN = colIndex
                End If
                If dc.ColumnName = "Comune di Nascita" Then
                    indexComuneN = colIndex
                End If
                If dc.ColumnName = "Comune di Residenza" Then
                    indexComuneRes = colIndex
                End If
                If dc.ColumnName = "Indirizzo di Residenza" Then
                    indexIndirizzo = colIndex
                End If
            Next

            'Scrittura file excel
            For Each dr In dt.Rows
                rowIndex += 1
                colIndex = 0
                Counter += 1
                If Counter Mod 10 = 0 Then
                    ProgressBar1.PerformStep()
                End If
                For Each dc In dt.Columns
                    colIndex += 1
                    excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
                Next
            Next
            rowIndex = 0


            'Unione proprietari multipli
            For Each dr In dt.Rows
                rowIndex += 1
                If wSheet.Cells(rowIndex, 1).Value = wSheet.Cells(rowIndex + 1, 1).Value Then
                    excel.DisplayAlerts = False
                    For Counter = 1 To indexProprietario - 1
                        wSheet.Range(wSheet.Cells(rowIndex, Counter), wSheet.Cells(rowIndex + 1, Counter)).Merge()
                    Next
                End If

            Next


            Dim range As String = "A1:F1"
            Dim helper As Integer = 0

            'Impostazione Range
            For Each ctrl As Control In Controls
                If TypeOf ctrl Is CheckBox AndAlso DirectCast(ctrl, CheckBox).Checked Then
                    helper += 1
                End If
            Next

            If chkPgt.Checked Then
                helper += 1
            End If

            Select Case helper
                Case 2
                    range = "A1:F1"
                Case 3
                    range = "A1:G1"
                Case 4
                    range = "A1:H1"
                Case 5
                    range = "A1:I1"
                Case 6
                    range = "A1:J1"
                Case 7
                    range = "A1:K1"
                Case 8
                    range = "A1:L1"
                Case 9
                    range = "A1:M1"
                Case 10
                    range = "A1:N1"
                Case 11
                    range = "A1:O1"
                Case 12
                    range = "A1:P1"
                Case 13
                    range = "A1:Q1"
                Case 14
                    range = "A1:R1"
                Case 15
                    range = "A1:S1"
                Case 16
                    range = "A1:T1"
                Case 17
                    range = "A1:U1"
                Case 18
                    range = "A1:V1"
                Case 19
                    range = "A1:W1"
                Case 20
                    range = "A1:X1"
                Case 21
                    range = "A1:Y1"
                Case 22
                    range = "A1:Z1"
                Case 23
                    range = "A1:AA1"
                Case 24
                    range = "A1:AB1"
                Case 25
                    range = "A1:AC1"
                Case 26
                    range = "A1:AD1"
                Case 27
                    range = "A1:AE1"
            End Select


            'creazione stile
            Dim style As Microsoft.Office.Interop.Excel.Style = wSheet.Application.ActiveWorkbook.Styles.Add("NewStyle")
            style.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow)
            style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft



            'applicazione stile
            wSheet.Range(range).Style = "NewStyle"
            wSheet.Range(range).BorderAround(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium)
            wSheet.Columns.AutoFit()
            ProgressBar1.Step = 10
            ProgressBar1.PerformStep()



            Dim strFileName = filePath & Tab & ".xlsx"
            If System.IO.File.Exists(strFileName) Then
                ProgressBar1.PerformStep()
                System.IO.File.Delete(strFileName)

            End If
            ProgressBar1.PerformStep()
            wBook.SaveAs(strFileName)
            wBook.Close()
            ProgressBar1.PerformStep()
            excel.Quit()
            ProgressBar1.Value = 100
            MsgBox("File " & Path.GetFileName(strFileName) & " è stato salvato correttamente in " & strFileName)
            ProgressBar1.Value = 0
            ProgressBar1.Visible = False
            Dim result As DialogResult = Windows.Forms.DialogResult.No
            result = MessageBox.Show("Vuoi continuare con un'altra elaborazione?", "Leggi visure da pdf", MessageBoxButtons.YesNo)
            If result = Windows.Forms.DialogResult.Yes Then
                Excel_Check.Visible = False
                Pdf_Check.Visible = False
                Excel_TextBox.Text = ""
                PDF_TextBox.Text = ""
                GC.Collect()
            Else
                Me.Close()
            End If

        Catch ex As Exception
            MsgBox("Un errore è stato rilevato in ExcelEsport. Errore:" & vbLf & ex.Message)
            ProgressBar1.Value = 0
            Err.Clear()
        End Try
    End Sub

    Private Sub DataTableToSql(ByVal dt As DataTable)
        Try
            ProgressBar1.Step = 1
            Dim myConn As New SqlConnection(connectionString)
            Dim myCmd As SqlCommand
            Dim sqlCmd As String = "INSERT INTO " & TempTab & " VALUES(@Targa, @Modello, @Dealer, @Venduto, @CodiceDealer, @Valore)"
            If TempTab.Contains("PAI") Then
                sqlCmd = "INSERT INTO " & TempTab & " VALUES(@Targa, @DataVendita, @Modello, @Dealer, @Venduto, @CodiceDealer, @Valore)"
                myCmd = New SqlCommand(sqlCmd, myConn)
                myCmd.Parameters.Add("@DataVendita", SqlDbType.SmallDateTime)
            Else
                myCmd = New SqlCommand(sqlCmd, myConn)
            End If
            Dim counter As Integer = 0
            Dim dataVendita As String
            myConn.Open()
            myCmd.Parameters.Add("@Targa", SqlDbType.VarChar)
            myCmd.Parameters.Add("@Modello", SqlDbType.VarChar)
            myCmd.Parameters.Add("@Dealer", SqlDbType.VarChar)
            myCmd.Parameters.Add("@CodiceDealer", SqlDbType.VarChar)
            myCmd.Parameters.Add("@Venduto", SqlDbType.VarChar)
            myCmd.Parameters.Add("@Valore", SqlDbType.Float)
            For Each dr As DataRow In dt.Rows
                If chkCit.Checked Then
                    myCmd.Parameters("@Targa").Value = dr(0).ToString()

                    myCmd.Parameters("@Modello").Value = dr(1).ToString()

                    myCmd.Parameters("@Dealer").Value = dr(2).ToString()

                    myCmd.Parameters("@CodiceDealer").Value = dr(3).ToString()

                    myCmd.Parameters("@Venduto").Value = dr(4).ToString()

                    myCmd.Parameters("@Valore").Value = Convert.ToDouble(dr(5).ToString().Replace(",", "."))

                Else

                    myCmd.Parameters("@Targa").Value = dr(0).ToString()

                    dataVendita = "01/" & dr(2).ToString() & "/" & dr(1).ToString()
                    myCmd.Parameters("@DataVendita").Value = dataVendita

                    myCmd.Parameters("@Modello").Value = dr(3).ToString()

                    myCmd.Parameters("@Dealer").Value = dr(4).ToString()

                    myCmd.Parameters("@Venduto").Value = dr(5).ToString()

                    myCmd.Parameters("@CodiceDealer").Value = dr(6).ToString()


                    myCmd.Parameters("@Valore").Value = Convert.ToDouble(dr(7).ToString().Replace(",", "."))

                End If
                myCmd.ExecuteNonQuery()
                counter += 1
                If counter Mod 20 = 0 Then
                    ProgressBar1.PerformStep()
                End If

            Next
            ProgressBar1.Value = 100
            myConn.Close()
        Catch ex As Exception
            MsgBox("Un errore è stato rilevato in DataTableToSql. Errore:" & vbLf & ex.Message)
            ProgressBar1.Value = 0
            Exit Sub
        End Try
    End Sub

    Private Sub TableCleaner(ByVal tablename As String)
        Try
            Using myConn As New SqlConnection(connectionString)
                myConn.Open()
                Using myCmd As New SqlCommand("DELETE FROM " & tablename, myConn)
                    myCmd.ExecuteNonQuery()
                End Using
            End Using
        Catch ex As Exception
            Err.Clear()
        End Try
    End Sub

    Private Sub TableDeleter(ByVal tablename As String)
        Try
            Using myConn As New SqlConnection(connectionString)
                myConn.Open()
                Using myCmd As New SqlCommand("DROP TABLE " & tablename, myConn)
                    myCmd.ExecuteNonQuery()
                End Using
            End Using
        Catch ex As Exception
            Err.Clear()
        End Try
    End Sub

    Private Sub all_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles all.Click
        Telaio.Checked = True
        Produttore.Checked = True
        Immatricolazione.Checked = True
        CartaDiCircolazione.Checked = True
        KW.Checked = True
        Classe.Checked = True
        Cilindrata.Checked = True
        Alimentazione.Checked = True
        Tara.Checked = True
        Posti.Checked = True
        Assi.Checked = True
        PrimaFormalita.Checked = True
        DataPrimaFormalita.Checked = True
        UltimaFormalita.Checked = True
        DataUltimaFormalita.Checked = True
        Atto.Checked = True
        DataAtto.Checked = True
        PrezzoVeicolo.Checked = True
        Proprietario.Checked = True
        SessoTipoSocieta.Checked = True
        DataNascita.Checked = True
        Comune.Checked = True
        Residenza.Checked = True
        Indirizzo.Checked = True
    End Sub

    Private Sub none_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles none.Click
        Telaio.Checked = False
        Produttore.Checked = False
        Immatricolazione.Checked = False
        CartaDiCircolazione.Checked = False
        KW.Checked = False
        Classe.Checked = False
        Cilindrata.Checked = False
        Alimentazione.Checked = False
        Tara.Checked = False
        Posti.Checked = False
        Assi.Checked = False
        PrimaFormalita.Checked = False
        DataPrimaFormalita.Checked = False
        UltimaFormalita.Checked = False
        DataUltimaFormalita.Checked = False
        Atto.Checked = False
        DataAtto.Checked = False
        PrezzoVeicolo.Checked = False
        Proprietario.Checked = False
        SessoTipoSocieta.Checked = False
        DataNascita.Checked = False
        Comune.Checked = False
        Residenza.Checked = False
        Indirizzo.Checked = False
    End Sub

    Private Sub DefSel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DefSel.Click
        Produttore.Checked = False
        Immatricolazione.Checked = False
        CartaDiCircolazione.Checked = False
        KW.Checked = False
        Classe.Checked = False
        Cilindrata.Checked = False
        Alimentazione.Checked = False
        Tara.Checked = False
        Posti.Checked = False
        Assi.Checked = False
        PrimaFormalita.Checked = False
        DataPrimaFormalita.Checked = False
        Atto.Checked = False
        DataAtto.Checked = False
        Comune.Checked = False
        Residenza.Checked = False
        Indirizzo.Checked = False

        Telaio.Checked = True
        UltimaFormalita.Checked = True
        DataUltimaFormalita.Checked = True
        PrezzoVeicolo.Checked = True
        Proprietario.Checked = True
        SessoTipoSocieta.Checked = True
        DataNascita.Checked = True
    End Sub

    Private Sub CreateTable(ByVal tableName As String)
        Try
            Dim sqlStr As String
            If tableName.Contains("_CAI") Then
                sqlStr = "CREATE TABLE TempExcelTable_CAI(" & _
     "Targa nvarchar(7) PRIMARY KEY," & _
     "Modello nvarchar(1000) NULL," & _
     "Dealer nvarchar(1000) NULL," & _
     "Venduto nvarchar(1000) NULL," & _
     "CodiceDealer nvarchar(10) NULL," & _
     "Valore float(17) NULL)"
            Else
                sqlStr = "CREATE TABLE TempExcelTable_PAI(" & _
     "Targa nvarchar(7) PRIMARY KEY," & _
     "DataVendita smalldatetime NULL," & _
     "Modello nvarchar(1000) NULL," & _
     "Dealer nvarchar(1000) NULL," & _
     "Venduto nvarchar(1000) NULL," & _
     "CodiceDealer nvarchar(10) NULL," & _
     "Valore float(17) NULL)"
            End If
            Using myConn As New SqlConnection(connectionString)
                Using myCmd As New SqlCommand(sqlStr, myConn)
                    myCmd.CommandTimeout = 0
                    myCmd.CommandType = CommandType.Text
                    myCmd.ExecuteNonQuery()
                End Using
            End Using
        Catch ex As Exception
            Err.Clear()
            Exit Sub
        End Try
    End Sub
End Class