Imports Microsoft.Office.Interop.Excel
Imports System
Imports System.Runtime.InteropServices
Imports System.Windows.Forms
Imports Application = Microsoft.Office.Interop.Excel.Application

Namespace JpWordCheckForTTL
    Class ExcelHelper
        Private m_strPath As String = Nothing
        Private m_isCreateMode As Boolean = False
        Private MISSING_VALUE As Object = System.Reflection.Missing.Value
        Private m_AppMain As Application = Nothing
        Private m_Workbook As Workbook = Nothing
        Private m_Worksheet As Worksheet = Nothing

        Public Shared Function CreateExcelHelper() As ExcelHelper
            Dim appMain As Application = New Application()

            If appMain Is Nothing Then
                Return Nothing
            End If

            appMain.Visible = False
            appMain.UserControl = True
            Dim eh As ExcelHelper = New ExcelHelper()
            eh.m_AppMain = appMain
            Return eh
        End Function

        Public Function CreateExcel(ByVal strPath As String) As Boolean
            m_strPath = strPath
            m_isCreateMode = True
            m_Workbook = m_AppMain.Workbooks.Add(MISSING_VALUE)
            Return True
        End Function

        Public Function OpenExcel(ByVal strPath As String) As Boolean
            m_isCreateMode = True

            Try
                m_Workbook = m_AppMain.Workbooks.Open(strPath)

                If m_Workbook Is Nothing Then
                    MessageBox.Show("OpenExcel Error, m_Workbook is null")
                    Return False
                End If

                Return True
            Catch e As Exception
                MessageBox.Show("OpenExcel Error, Message : " & e.Message)
                Return False
            End Try
        End Function

        Public ReadOnly Property RowCount As Integer
            Get
                Return m_Worksheet.UsedRange.Cells.Rows.Count
            End Get
        End Property

        Public ReadOnly Property ColCount As Integer
            Get
                Return m_Worksheet.UsedRange.Cells.Columns.Count
            End Get
        End Property

        Public Sub SelectPage(ByVal Optional nPageIndex As Integer = 1)
            m_Worksheet = CType(m_Workbook.Worksheets.Item(nPageIndex), Worksheet)
        End Sub

        Public Function ReadGrid(ByVal nRow As Integer, ByVal nCol As Integer) As String
            Dim range As Range = m_Worksheet.Cells(nRow, nCol)
            Return CStr(range.Text)
        End Function

        Public Sub WriteGrid(ByVal nRow As Integer, ByVal nCol As Integer, ByVal strValue As String)
            m_Worksheet.Cells(nRow, nCol) = strValue
        End Sub

        Public Sub Save()
            If m_isCreateMode Then
                m_Workbook.SaveAs(m_strPath)
            Else
                MessageBox.Show("Not Create Mode, Can not call Function:Save to use.")
            End If
        End Sub

        Public Sub Close()
            m_Workbook.Close(True)
            m_AppMain.Quit()
            Kill(m_AppMain)
        End Sub

        <DllImport("User32.dll")>
        Public Shared Function GetWindowThreadProcessId(ByVal hWnd As IntPtr, <Out> ByRef Processid As Integer) As Integer

        End Function

        Public Shared Sub Kill(ByVal theApp As Microsoft.Office.Interop.Excel.Application)
            Dim iId As Integer = 0
            Dim intptr As IntPtr = New IntPtr(theApp.Hwnd)
            Dim p As System.Diagnostics.Process = Nothing

            Try
                GetWindowThreadProcessId(intptr, iId)
                p = System.Diagnostics.Process.GetProcessById(iId)

                If p IsNot Nothing Then
                    p.Kill()
                    p.Dispose()
                End If

            Catch e As Exception
                Throw e
            End Try
        End Sub
    End Class
End Namespace