以下是在VB.NET中使用Microsoft.Office.Interop.Excel库来分割工作表的示例代码:
</>code
- Imports Microsoft.Office.Interop
- Public Class ExcelHelper
- Public Shared Sub SplitWorksheet(filePath As String, maxRowsPerSheet As Integer)
- ' 创建Excel应用程序对象
- Dim excelApp As New Excel.Application()
- ' 打开工作簿
- Dim workbook As Excel.Workbook = excelApp.Workbooks.Open(filePath)
- ' 获取第一个工作表
- Dim worksheet As Excel.Worksheet = workbook.Sheets(1)
- ' 获取工作表的总行数
- Dim totalRows As Integer = worksheet.UsedRange.Rows.Count
- ' 计算需要创建的工作表数量
- Dim sheetCount As Integer = Math.Ceiling(totalRows / maxRowsPerSheet)
- ' 循环创建工作表
- For i As Integer = 1 To sheetCount
- ' 创建一个新的工作表
- Dim newWorksheet As Excel.Worksheet = workbook.Sheets.Add(After:=workbook.Sheets(workbook.Sheets.Count))
- ' 获取当前工作表的起始行和结束行
- Dim startRow As Integer = (i - 1) * maxRowsPerSheet + 1
- Dim endRow As Integer = Math.Min(i * maxRowsPerSheet, totalRows)
- ' 将原工作表中的数据复制到新工作表中
- Dim rangeToCopy As Excel.Range = worksheet.Range("A" & startRow.ToString(), "Z" & endRow.ToString())
- rangeToCopy.Copy(newWorksheet.Range("A1"))
- ' 对新工作表进行必要的操作,例如设置标题等
- newWorksheet.Cells(1, 1).Value = "Sheet " & i.ToString()
- ' 保存新工作表
- newWorksheet.SaveAs(filePath.Replace(".xlsx", "_" & i.ToString() & ".xlsx"))
- ' 释放资源
- System.Runtime.InteropServices.Marshal.ReleaseComObject(newWorksheet)
- Next
- ' 关闭工作簿
- workbook.Close()
- ' 释放资源
- System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
- System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet)
- ' 关闭Excel应用程序
- excelApp.Quit()
- ' 释放资源
- System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
- End Sub
- End Class
使用示例:
</>code
- Dim filePath As String = "C:\path\to\your\file.xlsx"
- Dim maxRowsPerSheet As Integer = 65536 ' 每个工作表的最大行数
- ExcelHelper.SplitWorksheet(filePath, maxRowsPerSheet)
此代码将打开给定的Excel文件,将第一个工作表分割成不超过65536行的多个工作表,并将其保存为单独的文件。每个新工作表的标题将设置为"Sheet 1"、"Sheet 2"等。请确保在使用Microsoft.Office.Interop.Excel库之前已将其添加到项目引用中。
如对本文有疑问,请提交到交流论坛,广大热心网友会为你解答!! 点击进入论坛