Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
------------------------------------------------------------
''' <summary>
''' EXCELファイルにテンプレートシートをコピーする''' </summary>
''' <param name="sheetCnt">コピーシート数</param>
''' <remarks></remarks>
Private Sub CopyExcelSheet(ByVal sheetCnt As Integer)
Dim xlApp As Object
xlApp = CreateObject("Excel.Application")
Dim xlBooks As Object = xlApp.Workbooks
' fn:excel文件路径c:\xxx\aaa.xls
Dim xlSheet As Object = xlBook.Sheets
Try
xlBook = xlApp.Workbooks.Open(fn)
xlApp.Visible = True
xlApp.Displayalerts = False
'xlSheet = xlBook.Worksheets(1)
' シートコピー
Dim indexNew As Integer = 0
Dim sheet2Index As Integer = 0
For index As Integer = 0 To sheetCnt - 1
xlSheet = xlBook.Worksheets(1)
If index = 0 Then
indexNew = index
End If
If indexNew <= arr.Count - 1 Then
' シートコピー1から
If index = 0 Then
xlSheet.Copy(After:=xlBook.Worksheets(2 + index))
Else
'xlSheet.Copy(After:=xlBook.Worksheets(2 + index + 1))
xlSheet.Copy(After:=xlBook.Worksheets(sheet2Index))
End If
' シート選択0から
If index = 0 Then
xlSheet = xlBook.Worksheets(3 + index)
Else
xlSheet = xlBook.Worksheets(sheet2Index + 1)
End If
' セル書く
Dim lineData As String() = arr(indexNew).ToString.Split(",")
' 郵便番号
xlSheet.Cells(5, 41) = lineData(0)
' 住所
xlSheet.Cells(7, 41) = lineData(1)
' 氏名
xlSheet.Cells(3, 41) = lineData(2)
If indexNew <= arr.Count - 2 Then
Dim lineData2 As String() = arr(indexNew + 1).ToString.Split(",")
' 郵便番号
xlSheet.Cells(35, 41) = lineData2(0)
' 住所
xlSheet.Cells(37, 41) = lineData2(1)
' 氏名
xlSheet.Cells(33, 41) = lineData2(2)
End If
' シート2をコピー
xlSheet = xlBook.Worksheets(2)
If index = 0 Then
xlSheet.Copy(After:=xlBook.Worksheets(2 + index + 1))
sheet2Index = 2 + index + 1
Else
'xlSheet.Copy(After:=xlBook.Worksheets(2 + index + 2))
sheet2Index = sheet2Index + 1
xlSheet.Copy(After:=xlBook.Worksheets(sheet2Index))
End If
' シート2にデータを書くTODO...
End If
'sheet2Index = 2 + index + 2
sheet2Index = sheet2Index + 1
indexNew = indexNew + 2
Next
'' テンプレートシートを削除
'TODO
Catch ex As Exception
labOperStates.Visible = False
MessageBox.Show("シートコピー中、エラー...")
Finally
If Not xlSheet Is Nothing Then
Try
Finally
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet)
End Try
End If
If Not xlBook Is Nothing Then
Try
xlBook.Close()
Finally
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook)
End Try
End If
If Not xlBooks Is Nothing Then
Try
Finally
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBooks)
End Try
End If
If Not xlApp Is Nothing Then
Try
xlApp.Quit()
Finally
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
End Try
End If
'System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet)
'System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBooks)
'System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
'xlBook.Close(True)
'xlApp.Quit()
'xlApp = Nothing
End Try
End Sub