Public Class Frm_Import
    Public Overrides Function AcceptChanged() As Boolean

    End Function
    Public Overrides Function RejectChanged() As Boolean

    End Function
    Public Overrides Function HasChanged() As Boolean

    End Function
    Dim RecordCount As Long
    Dim TableList As DataTable
    Dim IsExcel As Boolean = False

    Private Sub Frm_Import_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        '//테이블목록 추가
        Add_Tablelist()
        Me.Button2.Enabled = False  '//실행버튼 사용불가
        Me.IsExcel = False
    End Sub

    Private Sub Add_Tablelist()
        Dim TabName As String
        Dim TabDesc As String = ""
        Dim Sort As String = ""

        Me.TableList = New DataTable
        Me.TableList.Columns.Add("Desc")
        Me.TableList.Columns.Add("Name")
        Me.TableList.Columns.Add("Sort")
        Me.TableList.Columns.Add("Cnt")

        For Each Dt As DataTable In DSET.Tables
            TabName = Dt.TableName
            TabDesc = ""
            Select Case TabName.ToUpper
                'Case "TBL_BUHA"
                'TabDesc = "부하"
                'Sort = "00"
                Case "TBL_BUNBAE"
                    TabDesc = "냉방분배"
                    Sort = "00"
                    'Case "TBL_COMMON"
                    'TabDesc = "공용코드"
                    'Sort = "00"
                Case "TBL_DESC"
                    TabDesc = "건물개요"
                    Sort = "00"
                Case "TBL_KONGJO"
                    TabDesc = "공조"
                    Sort = "00"
                Case "TBL_KONGKUB"
                    TabDesc = "난방공급"
                    Sort = "00"
                Case "TBL_MYOUN"
                    TabDesc = "입력면"
                    Sort = "00"
                Case "TBL_NANBANGKIKI"
                    TabDesc = "난방기기"
                    Sort = "00"
                Case "TBL_NANGBANGKIKI"
                    TabDesc = "냉방기기"
                    Sort = "00"
                Case "TBL_NBUNBAE"
                    TabDesc = "난방분배"
                    Sort = "00"
                Case "TBL_NEW"
                    TabDesc = "신재생및열병합"
                    Sort = "00"
                    'Case "TBL_PROFILE"
                    '    TabDesc = "프로필"
                    '    Sort = "00"
                Case "TBL_ZONE"
                    TabDesc = "입력존"
                    Sort = "00"
                Case "TBL_YK"
                    TabDesc = "열관류율(목록)"
                    Sort = "00"
                Case "TBL_YKDETAIL"
                    TabDesc = "*열관류율(내역)"
                    Sort = "00"
                    'Case "TBL_WEATHER"
                    'TabDesc = "기상데이터(목록)"
                    'Sort = "00"
                    'Case "WEATHER_CHA"
                    'TabDesc = "*기상데이터(차양)"
                    'Sort = "00"
                    'Case "WEATHER_ILSA"
                    'TabDesc = "*기상데이터(일사)"
                    'Sort = "00"
                    'Case "WEATHER_SUPDO"
                    'TabDesc = "*기상데이터(습도)"
                    'Sort = "00"
                    'Case "WEATHER_ILSA"
                    'TabDesc = "*기상데이터(일사)"
                    'Sort = "00"
            End Select
            If TabDesc <> "" Then Me.TableList.Rows.Add(New String() {TabDesc, TabName, Sort})

        Next
    End Sub

    Private Sub Frm_Import_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles Me.KeyDown
        If e.KeyCode = Keys.Escape Then
            Me.DialogResult = Windows.Forms.DialogResult.Cancel
        End If
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If radi.Checked Then
            File_Open()
            Me.Button2.Enabled = True
        Else
            '//내보내기 기능
            File_Save()
            Me.Button2.Enabled = True
        End If
    End Sub

    Private Sub File_Open()
        Dim Od As New OpenFileDialog
        Od.FileName = "c:\out.txt"
        Od.Filter = "탭으로 분리된 텍스트파일(*.txt)|*.txt|Ms Excel(*.xls)|*.xls"
        Od.FilterIndex = 0
        If Od.ShowDialog <> Windows.Forms.DialogResult.OK Then Return
        Me.tb_file.Text = Od.FileName
        Me.IsExcel = IIf(Od.FilterIndex = 1, False, True)


        '//테이블리스트의 데이터갯수 초기화
        For Each Dr As DataRow In Me.TableList.Rows
            Dr("cnt") = 0
        Next
        Me.TableList.AcceptChanges()

        '//혹시 연속으로 열경우를 대비해서 초기화한다.
        Select Case Od.FilterIndex
            Case 1  '//텍스트파일
                Dim Table As String
                Dim BUf As String = My.Computer.FileSystem.ReadAllText(Me.tb_file.Text, System.Text.Encoding.Default)
                For Each B As String In BUf.Split(CChar(vbCrLf))
                    If B.Trim <> "" AndAlso B.Trim.Substring(0, 1) <> "#" Then
                        Table = B.Split(CChar(vbTab))(0)

                        '//해당 테이블 이름을 테이블 리스트에 찾아서 카운터를 누적시킨다.
                        Dim DR() As DataRow = Me.TableList.Select("name='" & Table.Trim & "'")
                        If DR.GetUpperBound(0) = 0 Then
                            Try
                                DR(0)("cnt") = Val(DR(0)("cnt")) + 1
                            Catch ex As Exception
                                DR(0)("cnt") = 1
                            End Try
                        End If
                    End If
                Next
            Case 2
                '//엑셀데이터에서 정보를 얻어와야한다.
                Dim xlApp As Excel.Application = New Excel.Application ' CreateObject("Excel.Application")
                Dim xlBook As Excel.Workbook = xlApp.Workbooks.Open(Me.tb_file.Text)
                Dim xlSheet1 As Excel.Worksheet = xlBook.Worksheets(1)  '//첫번째 시트로고정

                Dim Rng As Excel.Range = xlSheet1.Range("A1")   '//Offset으로사용할것이므로 0,0셀을 선택한다

                '//줄과 열의 갯수를 가져옵니다.
                Dim rowCount As String = Rng.Offset(0, 4).Value  '//줄수를 입력(나중에 불러올때 가져올범위를 대략 판단)
                Dim ColCount As String = Rng.Offset(0, 5).Value

                If rowCount.Trim = "" OrElse rowCount = "0" Then
                    Dim V As String = InputBox("전체줄의 갯수를 입력하세요", "데이터갯수입력", 1000)
                    If V = "" Then
                        MsgBox("불러올 줄수를 확인할 수 없습니다", MsgBoxStyle.Critical, "확인")
                        Return
                    End If
                    If IsNumeric(V) = False Then
                        MsgBox("숫자만 입력가능합니다", MsgBoxStyle.Critical, "확인")
                        Return
                    End If
                    rowCount = V
                End If
                If rowCount.Trim = "" OrElse rowCount = "0" Then
                    ColCount = 60  '//60개를 넘지는 않으므로(아마두?)
                End If

                '//범위가 지정되었으므로 데이터를 확인한다.
                '//데이터의 시작줄은 항상 5번이다.
                Dim Tabname As String = ""
                Dim 열 As New ArrayList
                Dim z_cnt As Integer = 0
                For r As Integer = 5 To rowCount
                    Try
                        Tabname = Rng.Offset(r, 0).Value.ToString.Trim '//0번째열은 항상 테이블 이름이 오도록한다
                    Catch ex As Exception
                        Tabname = ""
                    End Try

                    If Tabname = "" OrElse Tabname.Trim.ToUpper = "TABLE" Then  '//이건 테이블을 제목줄이다

                    Else    '//테이블이름으로 판단하고 해당 리스트를 찾아서 값을 누적시킴
                        '//해당 테이블 이름을 테이블 리스트에 찾아서 카운터를 누적시킨다.
                        Dim DR() As DataRow = Me.TableList.Select("name='" & Tabname & "'")
                        If DR.GetUpperBound(0) = 0 Then
                            Try
                                DR(0)("cnt") = Val(DR(0)("cnt")) + 1
                            Catch ex As Exception
                                DR(0)("cnt") = 1
                            End Try
                        End If
                    End If
                Next

                '//Dispos Excel Object
                xlBook.Close()
                xlApp.Application.Quit()
                xlApp.Quit()
                xlApp = Nothing
                xlBook = Nothing
                xlSheet1 = Nothing
        End Select


        Me.ListView1.Items.Clear()  '//리스트뷰클리어
        Dim lv As ListViewItem
        For Each dr As DataRow In Me.TableList.Select("", "sort")   '//정렬시켜서 가져옴
            If DSET.Tables(dr("name").ToString).Rows.Count > 0 Then
                lv = Me.ListView1.Items.Add(dr("desc").ToString)  '//테이블설명
                lv.SubItems.Add(dr("cnt").ToString)  '//레코드숫자
                lv.SubItems.Add(dr("name").ToString)  '//테이블이름
            End If
        Next

    End Sub
    Private Sub File_Save()
        DSET.AcceptChanges()
        DSETR.AcceptChanges()

        Dim sd As New SaveFileDialog
        sd.FileName = "c:\out.txt"
        sd.Filter = "탭으로 분리된 텍스트파일(*.txt)|*.txt|Ms Excel(*.xls)|*.xls"
        sd.FilterIndex = 0
        If sd.ShowDialog <> Windows.Forms.DialogResult.OK Then Return
        Me.tb_file.Text = sd.FileName
      
        Me.IsExcel = IIf(sd.FilterIndex = 1, False, True)

        Me.ListView1.Items.Clear()  '//기존의 목록제거
        Dim Lv As ListViewItem
        For Each dr As DataRow In Me.TableList.Select("", "sort")   '//정렬시켜서 가져옴
            If DSET.Tables(dr("name")).Rows.Count > 0 Then
                Lv = Me.ListView1.Items.Add(dr("desc"))  '//테이블설명
                Lv.SubItems.Add(DSET.Tables(dr("name")).Rows.Count)  '//레코드숫자
                Lv.SubItems.Add(dr("name"))  '//테이블이름
            End If
        Next

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        If MsgBox("실행하시겠습니까?", MsgBoxStyle.Information Or MsgBoxStyle.OkCancel, "확인") <> MsgBoxResult.Ok Then Return

        If Me.radi.Checked Then
            If My.Computer.FileSystem.FileExists(Me.tb_file.Text) = False Then
                MsgBox("해당파일이 존재하지않아서 가져올 수 없습니다", MsgBoxStyle.Critical, "확인")
                Me.Button2.Enabled = False
                Return
            End If
            If Me.IsExcel Then
                InPutXls()
            Else
                INput()
            End If
        Else
            If Me.IsExcel Then
                OutputXLS()
            Else
                OutPut()
            End If
        End If
    End Sub

    Private Function Get_Listview(ByVal Tabname As String) As ListViewItem
        Dim Tname As String
        Dim R As ListViewItem = Nothing
        For Each Lv As ListViewItem In Me.ListView1.Items
            Tname = Lv.SubItems(2).Text
            If Tname.ToUpper.Trim = Tabname.ToUpper.Trim Then
                R = Lv
                Exit For
            End If
        Next
        Return R
    End Function
    Private Sub InPutXls()

        '//가져오는 영역의 데이터 클리어
        Work_msg("저장영역 데이터확인중...")
        For Each Lview As ListViewItem In Me.ListView1.Items
            If Lview.Checked And Me.chk_deldata.Checked Then   '   '//삭제명령이 잇엇다면 삭제를 한다.
                Work_msg("데이터 삭제중(" & Lview.SubItems(2).Text & ")")
                If DSET.Tables(Lview.SubItems(2).Text).Columns.IndexOf("code") <> -1 Then    '//코드가잇는 개체라면 코드빼고 삭제
                    For Each Dr As DataRow In DSET.Tables(Lview.SubItems(2).Text).Select("Code<>'0'")
                        Dr.Delete()
                    Next
                Else
                    For Each Dr As DataRow In DSET.Tables(Lview.SubItems(2).Text).Rows
                        Dr.Delete()
                    Next
                End If

            End If
        Next
        DSET.AcceptChanges()


        Dim NullCnt As Int16 = 0
        Dim mytext1 As String = vbNullString

        Dim xlApp As Excel.Application = New Excel.Application ' CreateObject("Excel.Application")
        Dim xlBook As Excel.Workbook = xlApp.Workbooks.Open(Me.tb_file.Text)
        Dim xlSheet1 As Excel.Worksheet = xlBook.Worksheets(1)  '//첫번째 시트로고정

        Dim Rng As Excel.Range = xlSheet1.Range("A1")   '//Offset으로사용할것이므로 0,0셀을 선택한다

        '//줄과 열의 갯수를 가져옵니다.
        Dim rowCount As String = Rng.Offset(0, 4).Value  '//줄수를 입력(나중에 불러올때 가져올범위를 대략 판단)
        Dim ColCount As String = Rng.Offset(0, 5).Value

        If rowCount.Trim = "" OrElse rowCount = "0" Then
            Dim V As String = InputBox("전체줄의 갯수를 입력하세요", "데이터갯수입력", 1000)
            If V = "" Then
                MsgBox("불러올 줄수를 확인할 수 없습니다", MsgBoxStyle.Critical, "확인")
                Return
            End If
            If IsNumeric(V) = False Then
                MsgBox("숫자만 입력가능합니다", MsgBoxStyle.Critical, "확인")
                Return
            End If
            rowCount = V
        End If
        If rowCount.Trim = "" OrElse rowCount = "0" Then
            ColCount = 60  '//60개를 넘지는 않으므로
        End If

        '//범위가 지정되었으므로 데이터를 확인한다.
        '//데이터의 시작줄은 항상 5번이다.
        Dim Tabname As String = ""
        Dim lv As ListViewItem
        Dim 열 As New ArrayList
        Dim z_cnt As Integer = 0
        For r As Integer = 5 To rowCount
            Try
                Tabname = Rng.Offset(r, 0).Value.ToString '//0번째열은 항상 테이블 이름이 오도록한다
            Catch ex As Exception
                Tabname = ""
            End Try

            If Tabname.Trim.ToUpper = "TABLE" Then  '//이건 테이블을 제목줄이다
                열 = New ArrayList  '//열제목을 이곳에 모두 넣는다.
                For c As Integer = 1 To ColCount
                    Try
                        If Rng.Offset(r, c).Value.ToString.Trim <> "" AndAlso Rng.Offset(r, c).Value.ToString.Trim <> "0" Then  '//열제목이있다
                            열.Add(Rng.Offset(r, c).Value.ToString.Trim)
                        End If
                    Catch ex As Exception

                    End Try

                Next
            Else
                If 열.Count > 0 AndAlso Tabname.Trim <> "" Then    '//열제목이 설정되어잇어야 돌아가도록
                    lv = Get_Listview(Tabname)
                    If lv.Checked Then   '//가져오기설정과 테이블이름이 잇어야함
                        Work_msg("가져오는중(" & Tabname & ")")
                        Dim Dr As DataRow = DSET.Tables(Tabname).NewRow
                        Dr("code") = Format(DSET.Tables(Tabname).Rows.Count, "0000")
                        For c As Integer = 1 To 열.Count
                            Try
                                Dr(열.Item(c - 1).ToString) = Rng.Offset(r, c).Value
                            Catch ex As Exception
                                Dr(열.Item(c - 1).ToString) = DBNull.Value
                            End Try

                        Next
                        Try
                            DSET.Tables(Tabname).Rows.Add(Dr)
                            DSET.Tables(Tabname).AcceptChanges()
                            z_cnt += 1
                        Catch ex As Exception

                        End Try
                    End If
                End If
            End If
        Next
        Work_msg("")

        '//Dispos Excel Object
        xlBook.Close()
        xlApp.Application.Quit()
        xlApp = Nothing
        xlBook = Nothing
        xlSheet1 = Nothing

        MsgBox("가져오기완료", MsgBoxStyle.Information, "확인")
        Me.Close()

        ' Process.Start(Me.tb_file.Text)
        'Shell(Me.tb_file.Text, AppWinStyle.NormalFocus)
    End Sub
    Private Sub INput() '//텍스트파일 가져오기
        Dim BUf As String = My.Computer.FileSystem.ReadAllText(Me.tb_file.Text, System.Text.Encoding.Default)
        Dim Table As String


        Dim 열 As String = ""
     
        For Each Lview As ListViewItem In Me.ListView1.Items
            If Lview.Checked And Me.chk_deldata.Checked Then   '   '//삭제명령이 잇엇다면 삭제를 한다.
                If DSET.Tables(0).Columns.IndexOf("code") <> -1 Then    '//코드가잇는 개체라면 코드빼고 삭제
                    For Each Dr As DataRow In DSET.Tables(Lview.SubItems(2).Text).Select("Code<>'0'")
                        Dr.Delete()
                    Next
                Else
                    For Each Dr As DataRow In DSET.Tables(Lview.SubItems(2).Text).Rows
                        Dr.Delete()
                    Next
                End If
            End If
        Next
        DSET.AcceptChanges()

        Dim Lv As ListViewItem
        Dim z_cnt As Integer = 0
        Dim e_cnt As Integer = 0
        For Each B As String In BUf.Split(CChar(vbCrLf))    '//데이터를 실제로 가져온다
            If B.Trim <> "" AndAlso B.Trim.Substring(0, 1) <> "#" Then  '//주석데이터도제거
                Table = B.Split(CChar(vbTab))(0).Trim
                Select Case Table.ToUpper
                    Case "TABLE"
                        열 = B.Trim
                    Case Else
                        '//해당테이블명이 리뷰에 있는지확인하고 그것의 사용체크가되어있는지확인
                        Lv = Get_Listview(Table)
                        If Not Lv Is Nothing Then
                            If Lv.Checked Then  '//사용여부확인
                                Dim Dr As DataRow = DSET.Tables(Table).NewRow
                                Dr("code") = Format(DSET.Tables(Table).Rows.Count, "0000")
                                For i As Integer = 1 To B.Split(CChar(vbTab)).GetUpperBound(0)
                                    Try
                                        Dr(열.Split(CChar(vbTab))(i)) = B.Split(CChar(vbTab))(i)
                                    Catch ex As Exception
                                        Dr(열.Split(CChar(vbTab))(i)) = DBNull.Value
                                    End Try
                                Next
                                Try
                                    DSET.Tables(Table).Rows.Add(Dr)
                                    DSET.Tables(Table).AcceptChanges()
                                    z_cnt += 1
                                    'Catch sqlex As SqlClient.SqlException
                                    '    MsgBox(sqlex.ErrorCode)
                                Catch ex As Exception
                                    ' MsgBox(ex.Message.ToString)

                                    If ex.Message.ToString.IndexOf("고유 값") <> -1 Then
                                        z_cnt += 1
                                    Else
                                        e_cnt += 1
                                    End If
                                End Try
                            End If
                        End If

                End Select
            End If
        Next

        DSET.AcceptChanges()
        '  MsgBox(DSET.tbl_zone.Rows.Count)

        Dim M As New System.Text.StringBuilder
        M.AppendLine(z_cnt & "개의 데이터가 추가됨")
        M.AppendLine(e_cnt & "개의 오류발생")
        MsgBox(M.ToString, MsgBoxStyle.Information, "OK")
    End Sub


    '//텍스트파일 내보내기
    Private Sub OutPut()

        Work_msg("기본정보생성중...")

        Dim Wb As New System.Text.StringBuilder
        Wb.AppendLine("#### ECO2 Data ####")
        Wb.AppendLine("#### Export Time : " & Format(Now, "yyyy-MM-dd HH:mm:ss") & " ####")
        Wb.AppendLine("#### Export by Eco2 ####")


        Dim Tabname As String = ""
        For Each lv As ListViewItem In Me.ListView1.Items
            If lv.Checked Then  '//체크된것만 내보낸다
                Tabname = lv.SubItems(2).Text
                Work_msg("내보내는중(" & Tabname & ")")

                Wb.Append("table") '///////////////////////
                For Each C As System.Data.DataColumn In DSET.Tables(Tabname).Columns
                    Wb.Append(vbTab & C.ColumnName)
                Next
                Wb.Append(vbCrLf)
                For Each C As DataRow In DSET.Tables(Tabname).Rows
                    Wb.Append(Tabname)
                    For i As Integer = 0 To DSET.Tables(Tabname).Columns.Count - 1

                        'If (Tabname.ToUpper = "TBL_MYOUN") Then
                        '    If DSET.Tables(Tabname).Columns(i).ColumnName.ToUpper.Trim = "열관류율" Then
                        '        If CSng(C(i)) = 0 Then  '//링크값사용
                        '            Dim Dr As DS.tbl_ykRow = DSET.tbl_yk.Select("code='" & C("열관류율2") & "'")(0)
                        '            Wb.Append(vbTab & Dr("열관류율").ToString)
                        '        Else    '//입력값사용
                        '            Wb.Append(vbTab & C(i).ToString)
                        '        End If
                        '    Else
                        '        Wb.Append(vbTab & C(i).ToString)
                        '    End If
                        'Else
                        '    Wb.Append(vbTab & C(i).ToString)
                        'End If
                        Wb.Append(vbTab & C(i).ToString)

                    Next
                    Wb.Append(vbCrLf)
                Next
            End If
        Next
        Work_msg("")
        My.Computer.FileSystem.WriteAllText(Me.tb_file.Text, Wb.ToString, False, System.Text.Encoding.Default)
        If MsgBox("내보내기 완료" & vbCrLf & "파일을 확인하시겠습니까?", _
        vbInformation Or vbOK, "확인") = MsgBoxResult.Ok Then
            Process.Start(Me.tb_file.Text)
        End If
        Me.Close()

    End Sub

    Private Sub OutputXLS()

        Dim NullCnt As Int16 = 0
        Dim mytext1 As String = vbNullString

        Dim xlApp As Excel.Application = New Excel.Application ' CreateObject("Excel.Application")
        xlApp.DisplayAlerts = False
        xlApp.Visible = False
        xlApp.ReferenceStyle = Excel.XlReferenceStyle.xlA1
        Dim xlBook As Excel.Workbook = xlApp.Workbooks.Add
        Dim xlSheet1 As Excel.Worksheet = xlBook.Worksheets.Add


        Work_msg("데이터수 확인중...")
        '//전체데이터갯수를 가지고 배열을 정의한다.
        Dim Rcnt As Integer = 0
        Dim Ccnt As Integer = 0
        Dim TabName As String = ""
        For Each Lv As ListViewItem In Me.ListView1.Items
            If Lv.Checked Then
                TabName = Lv.SubItems(2).Text
                Rcnt += DSET.Tables(TabName).Rows.Count
                If DSET.Tables(TabName).Columns.Count > Ccnt Then Ccnt = DSET.Tables(TabName).Columns.Count
            End If
        Next
       
        '//제목줄 5줄을포함
        'If Ccnt < 5 Then Ccnt = 5
        Work_msg("저장영역할당....")
        Dim saRet(Rcnt + 50, 52) As String    '//여유잇게 할당
        For i As Integer = 0 To Rcnt + 10 - 1
            For j As Integer = 0 To Ccnt + 5 - 1
                saRet(i, j) = ""
            Next
        Next

        Dim Rng As Excel.Range = xlSheet1.Range("A1")   '//Offset으로사용할것이므로 0,0셀을 선택한다


        Work_msg("기본정보를 내보냅니다....")
        '//export title (제목내보내기)
        'Rng.Offset(0, 0).Value = "#### ECO2 Data ####"
        'Rng.Offset(1, 0).Value = ("#### Export Time : " & Format(Now, "yyyy-MM-dd HH:mm:ss") & " ####")
        'Rng.Offset(2, 0).Value = ("#### Export by Eco2 ####")

        saRet(0, 0) = "#### ECO2 Data ####"
        saRet(1, 0) = ("#### Export Time : " & Format(Now, "yyyy-MM-dd HH:mm:ss") & " ####")
        saRet(2, 0) = ("#### Export by Eco2 ####")


        '//Export SUbTitle(부가적인정보내보내기)
        'Rng.Offset(0, 4).Value = Rcnt + 4  '//줄수를 입력(나중에 불러올때 가져올범위를 대략 판단)
        'Rng.Offset(0, 5).Value = Ccnt - 1  '//열수를 입력

        saRet(0, 4) = Rcnt + 4  '//줄수를 입력(나중에 불러올때 가져올범위를 대략 판단)
        saRet(0, 5) = Ccnt - 1  '//열수를 입력

        Dim RowIndex As Integer = 5
        Dim ColIndex As Integer = 0

        For Each Lv As ListViewItem In Me.ListView1.Items
            If Lv.Checked Then
                TabName = Lv.SubItems(2).Text
                Work_msg("내보내는중(" & TabName & ")")
                'Set_ArrayData(Rng, RowIndex, DSET.Tables(TabName))
                Set_ArrayData(saRet, RowIndex, DSET.Tables(TabName))
            End If
        Next

        Try
            My.Computer.FileSystem.DeleteFile(Me.tb_file.Text)  '//대상파일이 존재하면 삭제해버림
        Catch ex As Exception
        End Try

        xlSheet1.Range("A1:AZ" & CStr(Rcnt + 10).ToString).Value = saRet
        'xlApp.Visible = True

        xlBook.Saved = True
        'xlSheet1.SaveAs(Me.tb_file.Text)
        xlSheet1.SaveAs(Me.tb_file.Text, _
                        Excel.XlFileFormat.xlExcel7, _
                      Nothing, Nothing, Nothing, Nothing, _
                      Nothing, Nothing, Nothing, Nothing)
        xlBook.Close()
        xlApp.Application.Quit()
        xlApp = Nothing
        xlBook = Nothing
        xlSheet1 = Nothing

        Work_msg("")
        If MsgBox("내보내기 완료" & vbCrLf & "파일을 확인하시겠습니까?", _
        vbInformation Or vbOK, "확인") = MsgBoxResult.Ok Then
            Process.Start(Me.tb_file.Text)
        End If
        Me.Close()

        ' Process.Start(Me.tb_file.Text)
        'Shell(Me.tb_file.Text, AppWinStyle.NormalFocus)
    End Sub

    Private Sub Set_ArrayData(ByRef Rng As Excel.Range, ByRef RowIndex As Integer, ByVal Dt As DataTable)
        RowIndex += 1
        Rng.Offset(RowIndex, 0).Value = "table" '//테이블컬럼명을 내보낸다
        '//Export ColumnData
        For i As Integer = 0 To Dt.Columns.Count - 1
            Rng.Offset(RowIndex, i + 1).Value2 = Dt.Columns(i).ColumnName.ToString
        Next
        '//Export RowData
        For Each C As DataRow In Dt.Rows
            RowIndex += 1 ': ReDim Preserve saRet(RowIndex, 255)
            Rng.Offset(RowIndex, 0).Value = Dt.TableName
            For i As Integer = 0 To Dt.Columns.Count - 1
                Rng.Offset(RowIndex, i + 1).Value2 = C(i).ToString
            Next
        Next
    End Sub
    Private Sub Set_ArrayData(ByRef Rng(,) As String, ByRef RowIndex As Integer, ByVal Dt As DataTable)
        RowIndex += 1
        Rng(RowIndex, 0) = "table" '//테이블컬럼명을 내보낸다
        '//Export ColumnData
        For i As Integer = 0 To Dt.Columns.Count - 1
            Rng(RowIndex, i + 1) = Dt.Columns(i).ColumnName.ToString
        Next
        '//Export RowData
        For Each C As DataRow In Dt.Rows
            RowIndex += 1 ': ReDim Preserve saRet(RowIndex, 255)
            Rng(RowIndex, 0) = Dt.TableName
            For i As Integer = 0 To Dt.Columns.Count - 1
                Rng(RowIndex, i + 1) = C(i).ToString
            Next
        Next
    End Sub


    Private Sub Label3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        '//d요부분만 추가됫어요
        Me.Close()
    End Sub

    Private Sub Panel1_Paint(ByVal sender As System.Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles Panel1.Paint
        Dim a As Graphics = e.Graphics
        Dim b As New Drawing2D.LinearGradientBrush(New Rectangle(0, 0, Me.Width, 500), Color.WhiteSmoke, _
                                                   Color.DarkGray, Drawing2D.LinearGradientMode.Vertical)
        a.FillRectangle(b, 0, 0, Me.Width, Me.Height)
        'a.FillRectangle(b, Me.Width, 0, Me.Width, 30)
        b.Dispose()
        a.Dispose()
    End Sub

   
    Private Sub PictureBox1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PictureBox1.Click
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet1 As Excel.Worksheet

       
        xlApp = New Excel.Application ' CreateObject("Excel.Application")
        xlBook = xlApp.Workbooks.Open("c:\out.xls")
        xlSheet1 = xlBook.Worksheets(1)  '//첫번째 시트로고정

        Dim Rng As Excel.Range = xlSheet1.Range("A1")   '//Offset으로사용할것이므로 0,0셀을 선택한다
        xlApp.Visible = True
        MsgBox("가져오기완료", MsgBoxStyle.Information, "확인")

        '//Dispos Excel Object
        'xlBook.displayalerts = False
        xlBook.Saved = True
        xlBook.Close()
        xlApp.Application.Quit()
        xlApp = Nothing
        xlBook = Nothing
        xlSheet1 = Nothing


        Me.Close()

        ' Process.Start(M
    End Sub
End Class