Public Class Frm_Import

   
    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 DSET1.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 DSET1.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()
        DSET1.AcceptChanges()
        DSETR1.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 DSET1.Tables(dr("name")).Rows.Count > 0 Then
                Lv = Me.ListView1.Items.Add(dr("desc"))  '//테이블설명
                Lv.SubItems.Add(DSET1.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 DSET1.Tables(Lview.SubItems(2).Text).Columns.IndexOf("code") <> -1 Then    '//코드가잇는 개체라면 코드빼고 삭제
                    For Each Dr As DataRow In DSET1.Tables(Lview.SubItems(2).Text).Select("Code<>'0'")
                        Dr.Delete()
                    Next
                Else
                    For Each Dr As DataRow In DSET1.Tables(Lview.SubItems(2).Text).Rows
                        Dr.Delete()
                    Next
                End If

            End If
        Next
        DSET1.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 = DSET1.Tables(Tabname).NewRow
                        Dr("code") = Format(DSET1.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
                            DSET1.Tables(Tabname).Rows.Add(Dr)
                            DSET1.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   '   '//삭제명령이 잇엇다면 삭제를 한다.


                Dim TAblename As String = Lview.SubItems(2).Text
                If DSET1.Tables(TAblename).Columns.IndexOf("code") <> -1 Then    '//코드가잇는 개체라면 코드빼고 삭제
                    For Each Dr As DataRow In DSET1.Tables(Lview.SubItems(2).Text).Select("Code<>'0'")
                        Dr.Delete()
                    Next
                Else
                    For Each Dr As DataRow In DSET1.Tables(Lview.SubItems(2).Text).Rows
                        Dr.Delete()
                    Next
                End If
            End If
        Next
        DSET1.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 = DSET1.Tables(Table).NewRow
                                Dr("code") = Format(DSET1.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
                                    DSET1.Tables(Table).Rows.Add(Dr)
                                    DSET1.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

        DSET1.AcceptChanges()
        '  MsgBox(DSET1.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 DSET1.Tables(Tabname).Columns
                    Wb.Append(vbTab & C.ColumnName)
                Next
                Wb.Append(vbCrLf)
                For Each C As DataRow In DSET1.Tables(Tabname).Rows
                    Wb.Append(Tabname)
                    For i As Integer = 0 To DSET1.Tables(Tabname).Columns.Count - 1

                        'If (Tabname.ToUpper = "TBL_MYOUN") Then
                        '    If DSET1.Tables(Tabname).Columns(i).ColumnName.ToUpper.Trim = "열관류율" Then
                        '        If CSng(C(i)) = 0 Then  '//링크값사용
                        '            Dim Dr As DS.tbl_ykRow = DSET1.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 += DSET1.Tables(TabName).Rows.Count
                If DSET1.Tables(TabName).Columns.Count > Ccnt Then Ccnt = DSET1.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, DSET1.Tables(TabName))
                Set_ArrayData(saRet, RowIndex, DSET1.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