- #Region "エクセルを出力"
- Private beforeTime As DateTime
- Private afterTime As DateTime
- ''' <summary>
- '''新規エクセル
- ''' </summary>
- ''' <param name="Application">エクセルのアプリケーション</param>
- ''' <param name="newWorkbook">エクセルのワークブック</param>
- ''' <param name="newWorksheet">エクセルのシート</param>
- ''' <remarks></remarks>
- Private Function newExcel(ByVal Application As Excel.Application, ByVal newWorkbook As Excel.Workbook, _
- ByVal newWorksheet As Excel.Worksheet) As Hashtable
- Dim excelHashTbale As New Hashtable
- Application = New Excel.Application
- '新規ワークブック
- newWorkbook = Application.Workbooks.Add()
- Application.Visible = False
- Application.DisplayAlerts = False
- '新規シート
- 'newWorksheet = newWorkbook.Worksheets.Add(DBNull.Value, DBNull.Value, 3)
- 'newWorksheet = CType(newWorkbook.Worksheets.Add(), Excel.Worksheet)
- newWorksheet = newWorkbook.Worksheets.Item("sheet1")
- excelHashTbale.Add("App", Application)
- excelHashTbale.Add("workbook", newWorkbook)
- excelHashTbale.Add("worksheet", newWorksheet)
- Return excelHashTbale
- End Function
- ''' <summary>
- ''' 保存エクセル
- ''' </summary>
- ''' <param name="newWorkbook">エクセルのワークブック</param>
- ''' <param name="strPath">エクセル保存のパス</param>
- ''' <param name="strExcelName">エクセルの名称</param>
- ''' <remarks></remarks>
- Private Sub saveExcel(ByVal newWorkbook As Excel.Workbook, ByVal strPath As String, _
- ByVal strExcelName As String)
- newWorkbook.SaveAs(strPath & strExcelName)
- End Sub
- ''' <summary>
- ''' エクセルを閉じる
- ''' </summary>
- ''' <param name="newWorkbook">エクセルのワークブック</param>
- ''' <param name="Application">エクセルのアプリケーション</param>
- ''' <remarks></remarks>
- Private Sub closeExcel(ByVal newWorkbook As Excel.Workbook, ByVal Application As Excel.Application)
- Dim processlist() As Process = Process.GetProcessesByName("EXCEL")
- Dim startTime As Date
- If Not IsNothing(newWorkbook) Then newWorkbook.Close()
- 'プロセスの終了
- If Not IsNothing(Application) Then
- Application.Quit()
- System.Runtime.InteropServices.Marshal.ReleaseComObject(Application)
- Application = Nothing
- End If
- For Each kp As Process In processlist
- startTime = kp.StartTime
- If startTime > beforeTime AndAlso startTime < afterTime Then
- kp.Kill()
- End If
- Next
- GC.Collect()
- End Sub
- ''' <summary>
- ''' エクセルを操作
- ''' </summary>
- ''' <param name="newWorkbook">エクセルのワークブック</param>
- ''' <param name="newWorksheet">エクセルのシート</param>
- ''' <remarks></remarks>
- Private Sub operationExcel(ByVal Application As Excel.Application, ByVal newWorkbook As Excel.Workbook, _
- ByVal newWorksheet As Excel.Worksheet)
- Dim strDefaultPath As String
- Dim intAppReFile As Integer = Application.RecentFiles.Count
- Dim strFileName(intAppReFile) As String
- Dim i As Integer
- newWorksheet.Cells(1, 1) = "Hello Word!"
- 'ワークブックを電子メールの添付ファイルにして発送します。
- newWorkbook.SendMail(Recipients:="someone@example.com", Subject:="July Sales Figures")
- '黙認のパス
- strDefaultPath = Application.DefaultFilePath
- '最近使うワークブックを並べます
- For i = 0 To intAppReFile - 1
- strFileName(i) = Application.RecentFiles(i + 1).Name
- Next
- '
- End Sub
- Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
- Dim Application As Excel.Application = Nothing
- Dim newWorkbook As Excel.Workbook = Nothing
- Dim newWorksheet As Excel.Worksheet = Nothing
- Dim excelHashTable As New Hashtable
- Try
- beforeTime = Date.Now
- '新規
- excelHashTable = newExcel(Application, newWorkbook, newWorksheet)
- afterTime = Date.Now
- 'エクセルを操作
- operationExcel(excelHashTable("App"), excelHashTable("workbook"), excelHashTable("worksheet"))
- '保存エクセル
- saveExcel(excelHashTable("workbook"), "D:/", "New")
- MsgBox("出力成功!")
- Catch ex As Exception
- MsgBox(ex.Message)
- Finally
- 'エクセルを閉じる
- closeExcel(excelHashTable("workbook"), excelHashTable("App"))
- End Try
- End Sub
- #End Region
- #Region "エクセルを読取"
- Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
- Dim aExcel As Excel.Application = Nothing
- Dim newWorkBook As Excel.Workbook = Nothing
- Dim strPath As String = "D:/"
- Dim strFileName As String = "New"
- Try
- beforeTime = Date.Now
- readExcel(aExcel, newWorkBook, strPath, strFileName)
- afterTime = Date.Now
- MsgBox("読取成功!")
- Catch ex As Exception
- MsgBox(ex.Message)
- Finally
- closeExcel(newWorkBook, aExcel)
- End Try
- End Sub
- ''' <summary>
- ''' エクセルを読取
- ''' </summary>
- ''' <param name="aExcel">エクセルのアプリケーション</param>
- ''' <param name="newWorkBook">エクセルのワークブック</param>
- ''' <param name="strPath">エクセル保存のパス</param>
- ''' <param name="strFileName">エクセルの名称</param>
- ''' <remarks></remarks>
- Private Sub readExcel(ByVal aExcel As Excel.Application, ByVal newWorkBook As Excel.Workbook, _
- ByVal strPath As String, ByVal strFileName As String)
- Dim newWorkSheet As Excel.Worksheet
- aExcel = New Excel.Application
- newWorkBook = aExcel.Workbooks.Open(strPath & strFileName)
- newWorkSheet = newWorkBook.Sheets("sheet1")
- Dim s As String = newWorkSheet.Cells(1, 1).value2
- MsgBox(s)
- End Sub
- #End Region
- #Region "XML"
- Dim xmlWriter As New Xml.XmlTextWriter("New", System.Text.Encoding.UTF8)
- Private Sub writeXML()
- Dim xml As New Xml.XmlDocument
- xmlWriter.Formatting = System.Xml.Formatting.Indented
- xmlWriter.Indentation = 2
- xmlWriter.QuoteChar = """"c
- xmlWriter.WriteStartDocument(True)
- xmlWriter.WriteStartElement("localName")
- End Sub
- Public Sub SetXmlElement(ByVal localName As String, ByVal value As String)
- xmlWriter.WriteElementString(localName, value)
- End Sub
- Public Sub SetXmlStartElement(ByVal localName As String)
- xmlWriter.WriteStartElement(localName)
- End Sub
- Public Sub SetXmlAttributeString(ByVal localName As String, ByVal value As String)
- xmlWriter.WriteAttributeString(localName, value)
- End Sub
- Public Sub WriteEndElement()
- xmlWriter.WriteEndElement()
- End Sub
- Public Sub WriteEndDocument()
- xmlWriter.WriteEndDocument()
- End Sub
- Public Sub xmlClose()
- xmlWriter.Close()
- End Sub
- #End Region
自己写的Excel
最新推荐文章于 2024-10-09 16:02:44 发布