Imports System Imports System.Drawing Imports System.Collections Imports System.ComponentModel Imports System.Windows.Forms Imports System.Data Imports System.IO Imports System.Xml Imports System.Reflection Public Class Form1 Private nCountElement As Integer 'count of root Element Private nCountData As Integer 'count of root Data Private nCountByte As Integer 'count of root Unicode byte Private sOpenXmlPath As String Private sSaveXmlPath As String Private sSaveExcelPath As String 'the path of excel file which need to save Private sOpenExcelPath As String 'the path of excel file which need to read Public Function OpenExcel() As String 'return the open file path Dim openFileDialog2 As New OpenFileDialog() openFileDialog2.Filter = "电子表格|*.xls" openFileDialog2.FilterIndex = 2 openFileDialog2.RestoreDirectory = True If openFileDialog2.ShowDialog() = Windows.Forms.DialogResult.OK Then 'Text<a href="https://www.jb51.cc/tag/Box/" target="_blank" class="keywords">Box</a>3.Text = openFileDialog2.FileName OpenExcel = openFileDialog2.FileName Else OpenExcel = "" End If End Function Public Function SaveExcel() As String 'Dim myStream As Stream Dim saveFileDialog1 As New SaveFileDialog() saveFileDialog1.Filter = "电子表格|*.xls" saveFileDialog1.FileName = "Xml_To_Excel_" saveFileDialog1.FilterIndex = 1 saveFileDialog1.RestoreDirectory = True If saveFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then SaveExcel = saveFileDialog1.FileName Else SaveExcel = "" End If End Function Private Sub Button1_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles Button1.Click Text<a href="https://www.jb51.cc/tag/Box/" target="_blank" class="keywords">Box</a>1.Text = OpenExcel() End Sub Private Sub Button2_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles Button2.Click Text<a href="https://www.jb51.cc/tag/Box/" target="_blank" class="keywords">Box</a>2.Text = SaveExcel() End Sub Private Sub Button3_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles Button3.Click '<a href="https://www.jb51.cc/tag/xianshi/" target="_blank" class="keywords">显示</a>进度条 ProgressBar1.Visible = True ProgressBar1.Minimum = 0 '1.读取Excel中的<a href="https://www.jb51.cc/tag/neirong/" target="_blank" class="keywords">内容</a> Dim ExcelApp As New Excel.Application() Dim ExcelObj As Object Dim ExcelSheet As Excel.Worksheet If Text<a href="https://www.jb51.cc/tag/Box/" target="_blank" class="keywords">Box</a>1.Text <> "" Then sOpenExcelPath = Text<a href="https://www.jb51.cc/tag/Box/" target="_blank" class="keywords">Box</a>1.Text Else Msg<a href="https://www.jb51.cc/tag/Box/" target="_blank" class="keywords">Box</a>("<a href="https://www.jb51.cc/tag/wenjian/" target="_blank" class="keywords">文件</a>路径选择失败请重新尝试") Exit Sub End If ExcelObj = ExcelApp.Workbooks.Open(sOpenExcelPath) ExcelSheet = ExcelObj.Sheets(1) ProgressBar1.Maximum = ExcelSheet.UsedRange.Rows.Count '设置进度条的最大值 Console.WriteLine("UsedRange.Columns:{0:d}",ExcelSheet.UsedRange.Columns.Count) '竖 Console.WriteLine("UsedRange.Rows:{0:d}",ExcelSheet.UsedRange.Rows.Count) '横 '2.建立要保存的Excel<a href="https://www.jb51.cc/tag/wenjian/" target="_blank" class="keywords">文件</a> Dim myApp As Excel.ApplicationClass = New Excel.ApplicationClass() Dim myBook As Excel.Workbook Dim mySheet As Excel.Worksheet If Text<a href="https://www.jb51.cc/tag/Box/" target="_blank" class="keywords">Box</a>2.Text <> "" Then sSaveExcelPath = Text<a href="https://www.jb51.cc/tag/Box/" target="_blank" class="keywords">Box</a>2.Text Else Msg<a href="https://www.jb51.cc/tag/Box/" target="_blank" class="keywords">Box</a>("<a href="https://www.jb51.cc/tag/wenjian/" target="_blank" class="keywords">文件</a>路径选择失败请重新尝试") Exit Sub End If If sSaveExcelPath <> "" Then myBook = myApp.Workbooks.Add(Missing.Value) myApp.Workbooks.Add(True) myApp.Visible = False mySheet = myBook.Sheets(1) Else Msg<a href="https://www.jb51.cc/tag/Box/" target="_blank" class="keywords">Box</a>("sSaveExcelPath =='NULL' ") Exit Sub End If '开始复制<a href="https://www.jb51.cc/tag/wenjian/" target="_blank" class="keywords">文件</a> 'Dim str1 As String 必须按照下面的<a href="https://www.jb51.cc/tag/fangfa/" target="_blank" class="keywords">方法</a>定义，否则无法<a href="https://www.jb51.cc/tag/huoqu/" target="_blank" class="keywords">获取</a>字符串 For myHeng As Integer = 1 To ExcelSheet.UsedRange.Rows.Count For myShu As Integer = 1 To ExcelSheet.UsedRange.Columns.Count 'read Dim str1 As String = ExcelSheet.Cells(myHeng,myShu).value() If 5 = myShu And myHeng > 1 Then Dim TestArray() As String = Split(str1,".") Console.WriteLine("{0:S}",TestArray(1)) mySheet.Cells(myHeng,1 + myShu) = TestArray(1) Else mySheet.Cells(myHeng,1 + myShu) = str1 End If Next ProgressBar1.Value = myHeng '进度条的进度<a href="https://www.jb51.cc/tag/xianshi/" target="_blank" class="keywords">显示</a> Next myHeng '<a href="https://www.jb51.cc/tag/guanbi/" target="_blank" class="keywords">关闭</a>原始的Excel<a href="https://www.jb51.cc/tag/wenjian/" target="_blank" class="keywords">文件</a> ExcelObj.Close() ExcelSheet = Nothing ExcelObj = Nothing ExcelApp = Nothing '<a href="https://www.jb51.cc/tag/guanbi/" target="_blank" class="keywords">关闭</a><a href="https://www.jb51.cc/tag/xiugai/" target="_blank" class="keywords">修改</a>好的excel<a href="https://www.jb51.cc/tag/wenjian/" target="_blank" class="keywords">文件</a> mySheet.SaveAs(sSaveExcelPath) myApp.Workbooks.Close() myApp.Quit() myApp = Nothing '隐藏进度条 ProgressBar1.Visible = False End Sub End Class