Hi,
I have updated my previous sample example codes (
VB.NET) and enhanced it to accomplish your task. Now, it will fill the huge list of data (1000000 * 3 matrix) into multiple worksheets (3 sheets), each sheet will have this long list of data. By the way, when you add multiple sheets with that kind of huge list of data, the output file would be huge sized and when you will open the output file into MS Excel, it will surely take some time and resources to open the file into it.Sample code:
…
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports Aspose.Cells
Imports System.Data
Module Module1
Sub Main()
'Specify your desired matrix
Dim rowsCount As Integer = 1000000
Dim colsCount As Integer = 3
Dim sheetsCount As Integer = 3
Dim workbook = New Workbook()
For k As Integer = 0 To sheetsCount - 1
Dim sheet As Worksheet = Nothing
If k = 0 Then
sheet = workbook.Worksheets(k)
sheet.Name = “test”
Else
Dim sheetIndex As Integer = workbook.Worksheets.Add()
sheet = workbook.Worksheets(sheetIndex)
sheet.Name = “test” & sheetIndex
End If
Next k
Dim ooxmlSaveOptions = New OoxmlSaveOptions()
ooxmlSaveOptions.LightCellsDataProvider = New TestDataProvider(workbook, sheetsCount, rowsCount, colsCount)
workbook.Save(“e:\test2\myoutput_testnew2.xlsx”, ooxmlSaveOptions)
End Sub
End Module
Public Class TestDataProvider
Implements LightCellsDataProvider
Private dt As DataTable
Public Sub FillTable()
dt = New DataTable()
dt.Columns.Add(“Column1”, GetType(Integer))
dt.Columns.Add(“Column2”, GetType(String))
dt.Columns.Add(“Column3”, GetType(Integer))
For i As Integer = 0 To maxRows - 1
dt.Rows.Add(i \ 10 + 1, String.Format(“Item {0}”, i), i)
Next i
End Sub
Private _row As Integer = -1
Private _column As Integer = -1
Private sheeCount As Integer
Private maxRows As Integer
Private maxColumns As Integer
Private _workbook As Workbook
Public Sub New(ByVal workbook As Workbook, ByVal sheetCount As Integer, ByVal maxRows As Integer, ByVal maxColumns As Integer)
Me._workbook = workbook
Me.maxRows = maxRows
Me.maxColumns = maxColumns
FillTable()
Me.sheeCount = sheetCount
End Sub
Public Function IsGatherString() As Boolean Implements LightCellsDataProvider.IsGatherString
Return False
End Function
Public Function NextCell() As Integer Implements LightCellsDataProvider.NextCell
_column += 1
If _column < Me.maxColumns Then
Return _column
Else
_column = -1
Return -1
End If
End Function
Public Function NextRow() As Integer Implements LightCellsDataProvider.NextRow
_row += 1
If _row < Me.maxRows Then
_column = -1
Return _row
Else
Return -1
End If
End Function
Public Sub StartCell(ByVal cell As Cell) Implements LightCellsDataProvider.StartCell
cell.PutValue(dt.Rows(_row)(_column)) '_row + _column);
End Sub
Public Sub StartRow(ByVal row As Row) Implements LightCellsDataProvider.StartRow
End Sub
Public Function StartSheet(ByVal sheetIndex As Integer) As Boolean Implements LightCellsDataProvider.StartSheet
If sheetIndex < sheeCount Then
'reset row/column index
_row = -1
_column = -1
Return True
End If
Return False
End Function
End Class
Thank you.