Merge excel with pdf

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 Tab As String = "VISUREDAPDF_"
    Dim TempTab As String = "TempExcelTable"
    Dim connectionString As String = "Server=****;Database=****;User ID=****;Password=****;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

            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")

                Else
                    sDb = "CITROEN"
                    Tab = Tab.Replace("_", "_CAI")
                End If
                CnSqlOpen(sDb, connectionString)
                TableCleaner(Tab)
                ProgressBar1.Visible = True
                LeggiRinoninaPdfByTarga(DirPdf, 1, "aa", 1, Tab)
                ExcelManagment(fileExcel)
            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
        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)

                counter += 1

                If counter Mod 20 = 0 Then
                    ProgressBar1.PerformStep()
                End If
            Next file
            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 Shared 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)
        Try
            Dim reader As StreamReader
            Dim readerDate As StreamReader
            Dim FileTxt As FileInfo
            Dim FilePdf As FileInfo
            Dim sRiga As String
            Dim sTarga As String
            Dim sId As String
            Dim sTelaio As String
            Dim cmd As SqlCommand

            Dim text As New StringBuilder()


            FileTxt = New System.IO.FileInfo(sfileTxtInput)
            sId = FileTxt.Name.Substring(0, FileTxt.Name.LastIndexOf(".").ToString)
            FilePdf = New System.IO.FileInfo(sfilePdfInput)

            reader = My.Computer.FileSystem.OpenTextFileReader(sfileTxtInput)
            readerDate = My.Computer.FileSystem.OpenTextFileReader(sfileTxtInput)

            text.Append(readerDate.ReadToEnd)
            Dim DateTrovate As MatchCollection = Regex.Matches(text.ToString, "[0-9]*/[0-9]*/[0-9]{4}")

            text = Nothing

            Dim bTargaPosizione As Boolean = False

            sRiga = reader.ReadLine.TrimStart

            Dim sFormalita As String

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

            Dim tableriga As Data.DataRow = tabella.Rows.Add()
            Dim iultimaforminizio As Integer
            Dim iultimaformfine As Integer

            Dim iprezzoinizio As Integer
            Dim iprezzofine As Integer
            Dim sPrezzo As String

            Dim iprinizio As Integer
            Dim iprfine As Integer
            Dim sPr As String

            Dim isessnizio As Integer
            Dim isessfine As Integer
            Dim sSesso As String



            sTarga = Mid(sRiga, 22, 7)
            sTelaio = Mid(sRiga, 35, 17)

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

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

            iprezzoinizio = sRiga.IndexOf("Prezzo del Veicolo") + 18
            iprezzofine = sRiga.IndexOf("* Euro") - 1
            sPrezzo = sRiga.Substring(iprezzoinizio, iprezzofine - iprezzoinizio).Replace("*", "").Replace(".", "").Replace(",", ".").Replace(" ", "")

            iprinizio = sRiga.IndexOf("Proprietario") + 12
            iprfine = sRiga.IndexOf("Sesso / Tipo Societa'")
            sPr = sRiga.Substring(iprinizio, iprfine - iprinizio)

            isessnizio = iprfine + 21
            isessfine = sRiga.IndexOf("Data di nascita")
            sSesso = sRiga.Substring(isessnizio, isessfine - isessnizio)
            Dim sinsert As String
            sinsert = "INSERT INTO " & Tab & " " & _
                     "VALUES(" & fsql(sTarga, "T") & "," & fsql(sTelaio, "T") & "," & _
                     sPrezzo & "," & fsql(sPr, "T") & "," & fsql(sSesso, "T") & ",'" & fsql(sFormalita, "T").ToString.Replace("'", "") & "'," & _
                    fsql(sDataUltForm, "D") & ")"

            cmd = New SqlCommand(sinsert, CnSql)
            cmd.CommandTimeout = 0
            cmd.CommandType = CommandType.Text

            cmd.ExecuteNonQuery()

            reader.Close()
            readerDate.Close()

            'Elimina il file txt analizzato
            FileTxt.Delete()
        Catch ex As Exception
            MsgBox("Un errore è stato rilvevato in LeggiTargaDataPdf. Errore:" & vbLf & ex.Message)
            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
                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

    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)

            'Popola tabella risultante
            Dim RemoteDtTab As New System.Data.DataTable
            Dim sql As String = "SELECT " & TempTab & ".Targa as 'Targa ACI', Modello, Dealer, CodiceDealer as 'Codice Dealer', Venduto as 'Venduta a', Valore as 'Valore vendite Eurotax', Telaio, PrezzodiVendita as 'Prezzo di Vendita', AttualeProprietario as 'Attuale Proprietatio', Tipo as 'Sesso/Tipo Società', UltimaFormalita as 'Ultima Formalità', DataUltimaFormalita as 'Data Ultima Formalità' FROM " & TempTab & ", " & Tab & " WHERE " & TempTab & ".Targa = " & Tab & ".Targa"
            Using myConn As New SqlConnection(connectionString)
                myConn.Open()
                Using loader As New SqlDataAdapter(sql, 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)
        End Try
    End Sub

    Private Sub ExcelExport(ByVal ExportDataTable As DataTable, ByVal filePath As String)
        Try
            ProgressBar1.Value = 0
            ProgressBar1.Step = 1
            ProgressBar1.PerformStep()
            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
            'stop 5 - excel stuff declared

            For Each dc In dt.Columns
                colIndex = colIndex + 1
                excel.Cells(1, colIndex) = dc.ColumnName
            Next
            'stop 6 - Header written

            For Each dr In dt.Rows
                rowIndex = rowIndex + 1
                colIndex = 0
                Counter += 1
                If Counter Mod 10 = 0 Then
                    ProgressBar1.PerformStep()
                End If
                For Each dc In dt.Columns
                    colIndex = colIndex + 1
                    excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
                Next
            Next
            ProgressBar1.Step = 10
            ProgressBar1.PerformStep()
            'stop 7 - rows written
            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)
            Application.Restart()
        Catch ex As Exception
            MsgBox("Un errore è stato rilevato in ExcelEsport. Errore:" & vbLf & ex.Message)
            Err.Clear()
        End Try
    End Sub

    Private Sub DataTableToSql(ByVal dt As DataTable)
        Try
            ProgressBar1.Step = 1
            Dim myConn As New SqlConnection(connectionString)
            Dim sqlCmd As String = "INSERT INTO " & TempTab & " VALUES(@Targa, @Modello, @Dealer, @Venduto, @CodiceDealer, @Valore)"
            Dim myCmd As New SqlCommand(sqlCmd, myConn)
            Dim counter As Integer = 0
            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

                    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)
            Exit Sub
        End Try
    End Sub

    Private Sub TableCleaner(ByVal tablename As String)
        Try
            Dim myConn As New SqlConnection(connectionString)
            Dim myCmd As New SqlCommand("Delete from " & tablename, myConn)
            myConn.Open()
            myCmd.ExecuteNonQuery()
            myConn.Close()
        Catch ex As Exception
            MsgBox("Un errore è stato rilevato in TableCleaner. Errore:" & vbLf & ex.Message)
            Err.Clear()
        End Try
    End Sub
End Class