当我超出工作表的限制时，如何在新工作表中拆分行？

Question

我正在使用Epplus将vb.net中的数据导出到.xlsx文件，但当行数超过当前工作表的限制时，我该怎么办？如何停止导出过程以创建新表单？

这是我出口的代码：

专用功能GeneraExcel（BYVAL NombreArchivo作为字符串）作为布尔

Dim file As New System.IO.FileInfo(NombreArchivo) 
    Dim Paquete As New OfficeOpenXml.ExcelPackage(file) 
    Dim Hoja As OfficeOpenXml.ExcelWorksheet 
    Dim columna As Infragistics.Win.UltraWinGrid.UltraGridColumn 
    'numero de columnas de la grilla 
    Dim numColumnas As Integer = -1 
    For Each columna In grdDatos.Rows.Band.Columns 
     If columna.Hidden = False Then 
      numColumnas = numColumnas + 1 
     End If 
    Next 

    Hoja = Paquete.Workbook.Worksheets.Add(Globales.TITULO_APLICACION) 
    'convirtiendo grilla a datatable 
    Dim dtDatos As New DataTable 
    dtDatos = TryCast(grdDatos.DataSource, DataTable) 
    For i As Integer = 0 To numColumnas 
     dtDatos.Columns(i).ColumnName = grdDatos.Rows.Band.Columns(i).Header.Caption 
    Next 
    'imprimiendo en excel 
    Try 
     'Insertando título 
     Hoja.SelectedRange(4, 2, 4, numColumnas + 1).Merge = True 
     Hoja.Cells("B4").Value = "CONSULTA DE IMPORTES" 
     Hoja.Cells("B4").Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center 
     Hoja.Cells("B4").Style.Font.Bold = True 
     'Ingresando data con cabeceras 
     Hoja.Cells("B6").LoadFromDataTable(dtDatos, True) 

     'Colocando formatos 
     Dim colNumber As Integer = 1 
     For Each col As Data.DataColumn In dtDatos.Columns 
      colNumber += 1 
      If (col.DataType) Is GetType(DateTime) Then 
       Hoja.Column(colNumber).Style.Numberformat.Format = "dd/mm/yyyy" 
      End If 
     Next 
     Hoja.Cells(Hoja.Dimension.Address).AutoFitColumns() 
     Hoja.Row(6).Style.Font.Bold = True 
     Hoja.Cells(Hoja.Dimension.Address).Style.Font.Name = "Arial" 
     Hoja.Cells(Hoja.Dimension.Address).Style.Font.Size = 8 
     'Guardando archivo 
     Paquete.SaveAs(file) 
     Return True 
    Catch ex As Exception 
     Return False 
    End Try 
End Function 

Answer 1

这将做你想做的。只要采取的相关部分作为该方法是从你自己的略有不同....

'All lines that begin with an apostrophe (') are remarks and are not 
    'required for the macro to run. 
    Sub LargeFileImport() 

     'Dimension Variables 
     Dim ResultStr As String 
     Dim FileName As String 
     Dim FileNum As Integer 
     Dim Counter As Double 
     'Ask User for File's Name 
     FileName = InputBox("Please enter the Text File's name, e.g. test.txt") 
     'Check for no entry 
     If FileName = "" Then End 
     'Get Next Available File Handle Number 
     FileNum = FreeFile() 
     'Open Text File For Input 
     Open FileName For Input As #FileNum 
     'Turn Screen Updating Off 
     Application.ScreenUpdating = False 
     'Create A New WorkBook With One Worksheet In It 
     Workbooks.Add template:=xlWorksheet 
     'Set The Counter to 1 
     Counter = 1 
     'Loop Until the End Of File Is Reached 
     Do While Seek(FileNum) <= LOF(FileNum) 
     'Display Importing Row Number On Status Bar 
      Application.StatusBar = "Importing Row " & _ 
      Counter & " of text file " & FileName 
      'Store One Line Of Text From File To Variable 
      Line Input #FileNum, ResultStr 
      'Store Variable Data Into Active Cell 
      If Left(ResultStr, 1) = "=" Then 
      ActiveCell.Value = "'" & ResultStr 
      Else 
      ActiveCell.Value = ResultStr 
      End If 

      'For xl97 and later change 16384 to 65536 
      If ActiveCell.Row = 15000 Then 
      'If On The Last Row Then Add A New Sheet 
      ActiveWorkbook.Sheets.Add 
      Else 
      'If Not The Last Row Then Go One Cell Down 
      ActiveCell.Offset(1, 0).Select 
      End If 
      'Increment the Counter By 1 
      Counter = Counter + 1 
     'Start Again At Top Of 'Do While' Statement 
     Loop 
     'Close The Open Text File 
     Close 
     'Remove Message From Status Bar 
     Application.StatusBar = False 

    End Sub