|
分類:[C#]
コードを載せます。
やったことはtry-finalyの使用、毎回nothingにする、object型を使わない
です。
よろしくお願いします。＊killp は苦肉の策です。
Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim filepath As String = Application.StartupPath & "＼test.xls"
Dim objExcelApp As Excel.Application
Dim objBooks As Excel.Workbooks
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Dim objSheets As Excel.Sheets
Dim xlRng As Excel.Range
Dim xlCells As Excel.Range
objExcelApp = DirectCast(CreateObject("Excel.Application"), Excel.Application)
Dim dr As DataRow
Dim k As Long 'ループカウンタ
Dim drlength As Long = DS().Rows.Count() - 1 'ループカウンタ終点
Dim d As Long = 3
Try
With objExcelApp
.Visible = True
.DisplayAlerts = False
objBooks = .Workbooks
objBook = objBooks.Open(filepath)
'objBook = objBooks.Add
objSheets = objBook.Sheets
objSheet = DirectCast(objSheets("test1"),excel.Worksheet)
'objSheet = objSheets.Item(1)
With objSheet
For k = 0 To drlength
dr = DS().Rows(k)
xlCells = objSheet.Cells
xlRng = xlCells(d, 2)
xlRng.Value = dr.Item("testID")
MarshalOBJ(xlRng)
xlRng = xlCells(d, 4)
xlRng.Value = dr.Item("testtext")
MarshalOBJ(xlRng)
xlRng = xlCells(d, 6)
xlRng.Value = dr.Item("createdt")
MarshalOBJ(xlRng)
MarshalOBJ(xlCells)
'.Cells(d, 2) = dr.Item("testID")
'.Cells(d, 4) = dr.Item("testtext")
'.Cells(d, 6) = dr.Item("createdt")
d = d + 1
Next
.SaveAs(fileName:="C:＼" & Me.Name & ".xls")
End With
.DisplayAlerts = True
End With
Finally
If Not xlRng Is Nothing Then
MarshalOBJ(xlRng)
End If
If Not xlCells Is Nothing Then
MarshalOBJ(xlCells)
End If
Try
objExcelApp.Quit()
Marshal.ReleaseComObject(objExcelApp)
objBook.Close(False)
MarshalOBJ(objBook)
objBooks.Close()
MarshalOBJ(objBooks)
MarshalOBJ(objSheet)
MarshalOBJ(objSheets)
Catch
'killp()
Finally
GC.Collect()
End Try
MessageBox.Show("ファイルを作成し、保存しました。")
End Sub
'データテーブル作成
Function DS() As DataTable
Dim mysql As String
Dim mydataset As DataSet
mysql = "select testID,testtext,createdt,updatedt,userID from tabletest100"
mydataset = websvc1.TestDataSet(mysql)
Return mydataset.Tables("table")
End Function
'COMオブジェクト解放
Private Sub MarshalOBJ(ByRef objCom As Object)
Try
Marshal.ReleaseComObject(objCom)
Finally
objCom = Nothing
End Try
End Sub
'プロセス強制終了
Sub killp()
Dim localByName As Process() = Process.GetProcessesByName("Excel")
Dim p As Process
Dim fn As String = ""
For Each p In localByName
If System.String.Compare(p.MainWindowTitle, fn) = 0 Then
p.Kill()
End If
Next
End Sub
|
