自己写的Excel
来源:互联网 发布:淘宝1元门 编辑:程序博客网 时间:2024/05/06 05:56
- #Region "エクセルを出力"
- Private beforeTime As DateTime
- Private afterTime As DateTime
-
-
-
-
-
-
-
- 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.Item("sheet1")
- excelHashTbale.Add("App", Application)
- excelHashTbale.Add("workbook", newWorkbook)
- excelHashTbale.Add("worksheet", newWorksheet)
- Return excelHashTbale
- End Function
-
-
-
-
-
-
-
- Private Sub saveExcel(ByVal newWorkbook As Excel.Workbook, ByVal strPath As String, _
- ByVal strExcelName As String)
- newWorkbook.SaveAs(strPath & strExcelName)
- End Sub
-
-
-
-
-
-
- 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
-
-
-
-
-
-
- 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
-
-
-
-
-
-
-
-
- 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