none
vb.net操作excel sheet问题 RRS feed

  • 问题

  • Dear 各位论坛大牛:

            目前coding学习遇到个问题,在Excel的xls文件中有若干sheet,sheet名字及顺序为321 320 380 330,现在希望把sheet重新排序,按照320,321,330,380从小到大,请问通过vb.net该如何实现?

            谢谢各位论坛大牛!

    2015年2月28日 15:23

答案

  • Dear 各位论坛大牛:

            目前coding学习遇到个问题,在Excel的xls文件中有若干sheet,sheet名字及顺序为321 320 380 330,现在希望把sheet重新排序,按照320,321,330,380从小到大,请问通过vb.net该如何实现?

            谢谢各位论坛大牛!

    这里有一个简单的例子可以实现排序效果,部分细节你可以自行修改。

    主要用到了 Worksheet.Move 方法。

      Dim FileName = "D:\TtEST\Book1.xlsx"
            If IO.File.Exists(FileName) Then
                Dim Proceed As Boolean = False
                Dim xlApp As Excel.Application = Nothing
                Dim xlWorkBooks As Excel.Workbooks = Nothing
                Dim xlWorkBook As Excel.Workbook = Nothing
                Dim xlWorkSheet As Excel.Worksheet = Nothing
                Dim xlWorkSheets As Excel.Sheets = Nothing
                Dim xlCells As Excel.Range = Nothing
                xlApp = New Excel.Application
                xlApp.DisplayAlerts = False
                xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlWorkBooks.Open(FileName)
                xlApp.Visible = False
                xlWorkSheets = xlWorkBook.Sheets             
    
                For x As Integer = 1 To xlWorkSheets.Count
                    Dim xlWorkSheetFirst As Excel.Worksheet = CType(xlWorkSheets(x), Excel.Worksheet)
                    For index = x + 1 To xlWorkSheets.Count
                        xlWorkSheet = CType(xlWorkSheets(index), Excel.Worksheet)
                        Dim xlWorkSheetName As Integer = CType(xlWorkSheet.Name, Integer)
                        Dim xlWorkSheetFirstName As Integer = CType(xlWorkSheetFirst.Name, Integer)
                        If Not (xlWorkSheetName > xlWorkSheetFirstName) Then
                            xlWorkSheet.Move(Before:=xlWorkSheetFirst)
                            xlWorkSheetFirst = xlWorkSheet
                        End If
                    Next
                Next
                xlWorkBook.Save()
                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
                MessageBox.Show("sorted")
                xlWorkBook.Close()
                xlApp.UserControl = True
                xlApp.Quit()
            Else
                MessageBox.Show("'" & FileName & "' not located. Try one of the write examples first.")
            End If


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    2015年3月2日 11:16
    版主

全部回复

  • Dear 各位论坛大牛:

            目前coding学习遇到个问题,在Excel的xls文件中有若干sheet,sheet名字及顺序为321 320 380 330,现在希望把sheet重新排序,按照320,321,330,380从小到大,请问通过vb.net该如何实现?

            谢谢各位论坛大牛!

    这里有一个简单的例子可以实现排序效果,部分细节你可以自行修改。

    主要用到了 Worksheet.Move 方法。

      Dim FileName = "D:\TtEST\Book1.xlsx"
            If IO.File.Exists(FileName) Then
                Dim Proceed As Boolean = False
                Dim xlApp As Excel.Application = Nothing
                Dim xlWorkBooks As Excel.Workbooks = Nothing
                Dim xlWorkBook As Excel.Workbook = Nothing
                Dim xlWorkSheet As Excel.Worksheet = Nothing
                Dim xlWorkSheets As Excel.Sheets = Nothing
                Dim xlCells As Excel.Range = Nothing
                xlApp = New Excel.Application
                xlApp.DisplayAlerts = False
                xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlWorkBooks.Open(FileName)
                xlApp.Visible = False
                xlWorkSheets = xlWorkBook.Sheets             
    
                For x As Integer = 1 To xlWorkSheets.Count
                    Dim xlWorkSheetFirst As Excel.Worksheet = CType(xlWorkSheets(x), Excel.Worksheet)
                    For index = x + 1 To xlWorkSheets.Count
                        xlWorkSheet = CType(xlWorkSheets(index), Excel.Worksheet)
                        Dim xlWorkSheetName As Integer = CType(xlWorkSheet.Name, Integer)
                        Dim xlWorkSheetFirstName As Integer = CType(xlWorkSheetFirst.Name, Integer)
                        If Not (xlWorkSheetName > xlWorkSheetFirstName) Then
                            xlWorkSheet.Move(Before:=xlWorkSheetFirst)
                            xlWorkSheetFirst = xlWorkSheet
                        End If
                    Next
                Next
                xlWorkBook.Save()
                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
                MessageBox.Show("sorted")
                xlWorkBook.Close()
                xlApp.UserControl = True
                xlApp.Quit()
            Else
                MessageBox.Show("'" & FileName & "' not located. Try one of the write examples first.")
            End If


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    2015年3月2日 11:16
    版主
  • 高————手————膜————拜!
    2015年3月4日 9:53