Imports System.IO
Imports System.Linq
Imports System.Threading
Imports Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Imports System.ComponentModel
''' <summary>
''' シート分離処理フォーム
''' </summary>
''' <remarks></remarks>
Public Class frm_SpreadSheet
#Region "メンバー変数"
Dim excelApplication As ApplicationClass = New ApplicationClass()
Dim excelWorkBook As Workbook = Nothing
Dim targetSheet As Worksheet = Nothing
Dim chartObjects As ChartObjects = Nothing
Dim existingChartObject As ChartObject = Nothing
Dim paramWorkFolder As String
Dim stringArray As New ArrayList
Dim PathArray As New ArrayList
'フォルダツリーを作成
Dim ObjectPathArray As New ArrayList
'シート名格納
Dim NameArray As New ArrayList
'削除シート名格納
Dim DObjectnamesArray As New ArrayList
'実行フラグ
Dim Delflg As Boolean = False
Dim SubPath1 As String = "FD"
Dim SubPath2 As String = "DD"
'対象シート名が含むファイル
Dim SampleFile As String = String.Empty
'対象シート名を格納する配列
Dim SampleSheetNameArray As ArrayList
Dim SpreadSheet As Thread
Private callingThread As Integer
Dim ResultIndex As Integer = 0
Dim ResultString As String = String.Empty
#End Region
#Region "イベント"
''' <summary>
''' グレードビュー空白セルを削除
''' </summary>
''' <param name="sender">イベント発生元</param>
''' <param name="e">イベント補足情報</param>
''' <remarks></remarks>
Private Sub dgv_SpreadObject_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dgv_SpreadObject.Leave, dgv_Result.Leave
'編集状態のセルをコミットする
CType(sender, DataGridView).CommitEdit(DataGridViewDataErrorContexts.CurrentCellChange)
For Each i As DataGridViewRow In CType(sender, DataGridView).Rows
If i.Cells(0).Value = "" Then
Try
CType(sender, DataGridView).Rows.Remove(i)
Catch ex As Exception
End Try
End If
Next
End Sub
Private Sub btn_process_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_process.Click
'基本チェック
If Not check() Then
Exit Sub
End If
Me.SpreadSheet = New Thread(New ThreadStart(AddressOf SpreadSheets))
Me.SpreadSheet.Start()
End Sub
''' <summary>
''' フォルダを生成する
''' </summary>
''' <param name="OraginalPath">元パス</param>
''' <param name="ObjectPath">目標パス</param>
''' <returns>成功:TRUE 失敗:FALSE</returns>
''' <remarks></remarks>
Private Function CanCreateFolderTree(ByVal OraginalPath As String, ByVal ObjectPath As String) As Boolean
'ファイルパス配列に元ファイルパスを格納する
PathArray.Add(OraginalPath)
'元ファイルパス配列のサブフォルダを取得する
GetSubfolders(OraginalPath, PathArray)
For Each Subpath As String In PathArray
'目標ファイルパス配列を生成する
Me.ObjectPathArray.Add(ObjectPath & Subpath.Substring(OraginalPath.Length))
'目標ファイルパスツリーを生成する
CanCreateFolder(ObjectPathArray)
Next
Return False
End Function
''' <summary>
'''
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub btn_ObjectFolder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_ObjectFolder.Click
Dim dr As DialogResult = Me.fbd_filesfolder.ShowDialog()
'フォルダブラウザでファイルパスを取得する
If dr = DialogResult.OK Then
Me.txt_ObjectFolder.Text = Me.fbd_filesfolder.SelectedPath
End If
End Sub
Private Sub btn_SaveFolderA_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_SaveFolderA.Click
Dim dr As DialogResult = Me.fbd_filesfolder.ShowDialog()
'フォルダブラウザでファイルパスを取得する
If dr = DialogResult.OK Then
Me.txt_SaveFolderA.Text = Me.fbd_filesfolder.SelectedPath
End If
End Sub
Private Sub btn_SaveFolderB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_SaveFolderB.Click
Dim dr As DialogResult = Me.fbd_filesfolder.ShowDialog()
'フォルダブラウザでファイルパスを取得する
If dr = DialogResult.OK Then
Me.txt_SaveFolderB.Text = Me.fbd_filesfolder.SelectedPath
End If
End Sub
''' <summary>
''' 資源解放
''' </summary>
''' <param name="sender">イベント発生元</param>
''' <param name="e">イベント補足情報</param>
''' <remarks></remarks>
Private Sub frm_SpreadSheet_Disposed(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Disposed
CollectExcel()
End Sub
''' <summary>
''' 資源解放
''' </summary>
''' <remarks></remarks>
Private Sub CollectExcel()
'オブジェクト解放する
If Not targetSheet Is Nothing Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(targetSheet)
excelWorkBook = Nothing
End If
'オブジェクト解放する
If Not excelWorkBook Is Nothing Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkBook)
excelWorkBook = Nothing
End If
'オブジェクト回収する
If Not excelApplication Is Nothing Then
'プロセスを終了する
excelApplication.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplication)
excelApplication = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()
End If
End Sub
''' <summary>
'''
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
''' <remarks></remarks>
Private Sub btn_close_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_close.Click
Me.Close()
End Sub
Private Sub dgv_SpreadObject_ColumnHeaderMouseDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles dgv_SpreadObject.ColumnHeaderMouseDoubleClick
'編集状態のセルをコミットする
Me.dgv_SpreadObject.CommitEdit(DataGridViewDataErrorContexts.CurrentCellChange)
Dim dr As DialogResult = Me.ofd_files.ShowDialog()
'フォルダブラウザでファイルパスを取得する
If dr = DialogResult.OK Then
'ファイルが存在しない場合、処理しない
If Not File.Exists(Me.ofd_files.FileName) Then
DispMsg(Msg006, Me.Text)
Exit Sub
End If
Me.dgv_SpreadObject.Rows.Clear()
If excelApplication Is Nothing Then
excelApplication = New ApplicationClass()
End If
'提示を出さない
excelApplication.DisplayAlerts() = False
'Me.SampleFile = Me.ofd_files.FileName
excelWorkBook = excelApplication.Workbooks.Open(Me.ofd_files.FileName)
For Each sheet As Microsoft.Office.Interop.Excel.Worksheet In excelWorkBook.Worksheets
Me.dgv_SpreadObject.Rows.Add(sheet.Name)
Next
excelWorkBook.Close(False)
'CollectExcel()
End If
End Sub
''' <summary>
''' DeleteKey押下より、行を削除する
''' </summary>
''' <param name="sender">イベント発生元(データグレードビュー)</param>
''' <param name="e">イベント情報(キーコード)</param>
''' <remarks></remarks>
Private Sub dgv_SpreadObject_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgv_SpreadObject.KeyDown, dgv_Result.KeyDown
If e.KeyCode = Keys.Delete Then
'選択した行を削除する
For Each row As DataGridViewRow In CType(sender, DataGridView).SelectedRows()
Try
CType(sender, DataGridView).Rows.Remove(row)
Catch ex As Exception
MsgBox(Msg000)
Exit For
End Try
Next
End If
End Sub
#End Region
#Region "関数"
''' <summary>
''' 簡単なチェック
''' </summary>
''' <remarks></remarks>
Private Function check() As Boolean
'フォルダの有効性をチェックする
Try
If Not Directory.Exists(Me.txt_ObjectFolder.Text) Then
Throw New Exception
End If
If Not Directory.Exists(Me.txt_SaveFolderA.Text) Then
Throw New Exception
End If
If Not Directory.Exists(Me.txt_SaveFolderB.Text) Then
Throw New Exception
End If
If Me.dgv_SpreadObject.RowCount = 1 Then
DispMsg(Msg000, Me.Text)
Return False
Exit Function
End If
Return True
Catch ex As Exception
DispMsg(Msg005, Me.Text)
Return False
End Try
End Function
Private Sub SpreadSheets()
'削除対象名
Dim DelNamesArray As New ArrayList
'一時変数
Dim tempString As String = String.Empty
'Me.btn_process.Enabled = False
Call ResetControlStatusToDisable()
If excelApplication Is Nothing Then
excelApplication = New ApplicationClass()
End If
'提示を出さない
excelApplication.DisplayAlerts() = False
'画面より取得した削除対象名を格納
For Each d As DataGridViewRow In Me.dgv_SpreadObject.Rows
DelNamesArray.Add(d.Cells(0).Value)
Next
CanCreateFolderTree(Me.txt_ObjectFolder.Text, Me.txt_SaveFolderA.Text)
CanCreateFolderTree(Me.txt_ObjectFolder.Text, Me.txt_SaveFolderB.Text)
'ファイル名配列を取得
For Each FilePath As String In PathArray
For Each files As String In Directory.GetFiles(FilePath)
stringArray.Add(files)
Next
Next
Try
For Each likeMe As String In stringArray
frm_Parent.ToolStripStatusLabel1.Text = likeMe
If likeMe.Contains(".xls") Then
'読み取り専用ファイルを書き込み可にする↓
If True Then
If (System.IO.File.GetAttributes(likeMe) And FileAttributes.ReadOnly) > 0 Then
System.IO.File.SetAttributes(likeMe, FileAttributes.Normal)
End If
End If
'読み取り専用ファイルを書き込み可にする↑
excelWorkBook = excelApplication.Workbooks.Open(likeMe)
DObjectnamesArray.Clear()
For Each easy As Microsoft.Office.Interop.Excel.Worksheet In excelWorkBook.Worksheets
'End If
NameArray.Add(easy.Name)
For Each dname As String In DelNamesArray
If dname Is Nothing Then
Else
If easy.Name.Contains(dname) Then
DObjectnamesArray.Add(easy.Name)
Delflg = True
Exit For
Else
Delflg = False
End If
End If
Next
Next easy
'グレードビューに結果を表示する↓
Me.ResultIndex = Me.ResultIndex + 1
Me.ResultString = excelApplication.ActiveWorkbook.Name
Call ShowGridView()
'グレードビューに結果を表示する↑
'セーブ先のファイル名
tempString = txt_SaveFolderB.Text & likeMe.Substring(Me.txt_ObjectFolder.Text.Length)
'シートを新たなブックに移動する
excelApplication.ActiveWorkbook.Sheets(DObjectnamesArray.ToArray()).move()
'ブックをセーブする
excelApplication.ActiveWorkbook.Sheets(1).select()
excelApplication.ActiveWorkbook.SaveAs(tempString.Substring(0, tempString.Length - excelWorkBook.Name.Length) & "FD" & Mid(excelWorkBook.Name, 3))
'グレードビューに結果を表示する↓
Me.ResultIndex = Me.ResultIndex + 1
Me.ResultString = "┣" & tempString.Substring(0, tempString.Length - excelWorkBook.Name.Length) & "FD" & Mid(excelWorkBook.Name, 3)
Call ShowGridView()
'グレードビューに結果を表示する↑
'該当ブックを閉じる
excelApplication.ActiveWorkbook.Close()
'System.Console.WriteLine(excelApplication.ActiveWorkbook.Name & "AfterClose")
'移動後のブックのコピーを保存する
excelWorkBook.Sheets(1).select()
excelWorkBook.SaveCopyAs(Me.txt_SaveFolderA.Text & likeMe.Substring(Me.txt_ObjectFolder.Text.Length))
'グレードビューに結果を表示する↓
Me.ResultIndex = Me.ResultIndex + 1
Me.ResultString = "┗" & Me.txt_SaveFolderA.Text & likeMe.Substring(Me.txt_ObjectFolder.Text.Length)
Call ShowGridView()
'グレードビューに結果を表示する↑
'元のブックをバックアップとして、保存する
excelWorkBook.Close()
End If
Next
Catch ex As Exception
CollectExcel()
DispMsg(Msg000, Me.Text)
Call ResetControlStatusToEnable()
'Me.btn_process.Enabled = True
Me.SpreadSheet.Abort()
End Try
CollectExcel()
DispMsg(Msg004, Me.Text)
Call ResetControlStatusToEnable()
'Me.btn_process.Enabled = True
Me.SpreadSheet.Abort()
End Sub
''' <summary>
''' グレードビュー再表示
''' </summary>
''' <remarks></remarks>
Private Sub ResetControlStatusToDisable()
' If InvokeRequired is true then the call is being made on a thread other
' than the UI thread.
If Me.InvokeRequired Then
' Call UpdateUI by invoking a delegate with the UI control
callingThread = System.Threading.Thread.CurrentThread.ManagedThreadId()
Dim newDelegate As New UIDelegate(AddressOf ResetControlStatusToDisable)
Me.Invoke(newDelegate)
Else
''画面制御
'For Each lControl As Control In Me.Controls
' If TypeOf lControl Is TextBox Then
' lControl.Enabled = False
' ElseIf TypeOf lControl Is Button Then
' If lControl.Name <> "btn_close" Then
' lControl.Enabled = False
' End If
' End If
'Next
Me.txt_ObjectFolder.Enabled = False
Me.txt_SaveFolderA.Enabled = False
Me.txt_SaveFolderB.Enabled = False
Me.btn_process.Enabled = False
Me.btn_SaveFolderA.Enabled = False
Me.btn_SaveFolderB.Enabled = False
Me.btn_ObjectFolder.Enabled = False
Me.dgv_Result.Enabled = False
Me.dgv_SpreadObject.Enabled = False
End If
End Sub
''' <summary>
''' グレードビュー再表示
''' </summary>
''' <remarks></remarks>
Private Sub ResetControlStatusToEnable()
' If InvokeRequired is true then the call is being made on a thread other
' than the UI thread.
If Me.InvokeRequired Then
' Call UpdateUI by invoking a delegate with the UI control
callingThread = System.Threading.Thread.CurrentThread.ManagedThreadId()
Dim newDelegate As New UIDelegate(AddressOf ResetControlStatusToEnable)
Me.Invoke(newDelegate)
Else
''画面制御
'For Each lControl As Control In Me.Controls
' If TypeOf lControl Is TextBox Then
' lControl.Enabled = True
' ElseIf TypeOf lControl Is Button Then
' If lControl.Name <> "btn_close" Then
' lControl.Enabled = True
' End If
' End If
'Next
Me.txt_ObjectFolder.Enabled = True
Me.txt_SaveFolderA.Enabled = True
Me.txt_SaveFolderB.Enabled = True
Me.btn_process.Enabled = True
Me.btn_SaveFolderA.Enabled = True
Me.btn_SaveFolderB.Enabled = True
Me.btn_ObjectFolder.Enabled = True
Me.dgv_Result.Enabled = True
Me.dgv_SpreadObject.Enabled = True
End If
End Sub
''' <summary>
''' グレードビュー表示設定
''' </summary>
''' <remarks></remarks>
Private Sub ShowGridView()
' If InvokeRequired is true then the call is being made on a thread other
' than the UI thread.
If Me.InvokeRequired Then
' Call UpdateUI by invoking a delegate with the UI control
callingThread = System.Threading.Thread.CurrentThread.ManagedThreadId()
Dim newDelegate As New UIDelegate(AddressOf ShowGridView)
Me.Invoke(newDelegate)
Else
Try
Me.dgv_Result.Rows.Add(Me.ResultString, Me.ResultIndex)
Catch ex As Exception
End Try
End If
End Sub
''' <summary>
''' グレードビュー再表示
''' </summary>
''' <remarks></remarks>
Private Sub RefreshControl()
' If InvokeRequired is true then the call is being made on a thread other
' than the UI thread.
If frm_Parent.InvokeRequired Then
' Call UpdateUI by invoking a delegate with the UI control
callingThread = System.Threading.Thread.CurrentThread.ManagedThreadId()
Dim newDelegate As New UIDelegate(AddressOf RefreshControl)
Me.Invoke(newDelegate)
Else
frm_Parent.Refresh()
End If
End Sub
''' <summary>
'''
''' </summary>
''' <remarks></remarks>
Delegate Sub UIDelegate()
#End Region
End Class