Exporting 1 million records to Excel

Aspose.Cells Product Family
1

Hi Team,

I have datatable with 1 million records and I want to export the data to excel. I am using below line to do this.

Worksheet.cells.importdatatable(dtExport,False,0,0)

It throws System out of memory Exception.

Do you have any solution for this issue?

Note: This is working fine for 2-3 lakhs records.

Regards,

Niveditha

2
Hi,

Well, if you are filling large volume of data (e.g 1 million rows etc.), it would demand more and more memory to process the big task. It would be needed least 10 times or more RAM of the size of the final file. It is not final and it can depend on the complexity of your workbook and its data etc. So, I am afraid, we have no better way for ImportDataTable method when you are using it for your requirements (probably a 64 bit machine with a good amount of RAM might process the task fine finally and without producing the out of memory error). We have LightCells API and if you are creating the workbook (from the scratch) on the fly, it may suit your needs which is a more efficient approach to accomplish your task. I have written a sample .NET program for your requirement using the LightCells APIs, please refer to it and you may try to use this approach if it works fine for your needs. I have filled 1 million records to fill into the worksheet from the datatable (which I have already filled this long list of dataset in it).

Sample code:

using System;
using System.Collections.Generic;
using System.Text;
using Aspose.Cells;
using System.Data;

namespace LightOoxmlTest
{
class Program
{
public static void Main(string[] args)
{
//Specify your desired matrix
int rowsCount = 1000000;
int colsCount = 3;

var workbook = new Workbook();
var ooxmlSaveOptions = new OoxmlSaveOptions();

ooxmlSaveOptions.LightCellsDataProvider = new TestDataProvider(workbook, rowsCount, colsCount);

workbook.Save("e:\\test2\\myoutput_test1.xlsx", ooxmlSaveOptions);
}
}

class TestDataProvider : LightCellsDataProvider
{
DataTable dt;
public void FillTable()
{
dt = new DataTable();
dt.Columns.Add("Column1", typeof(int));
dt.Columns.Add("Column2", typeof(string));
dt.Columns.Add("Column3", typeof(int));
for (int i = 0; i < maxRows; i++)
{
dt.Rows.Add(i / 10 + 1, String.Format("Item {0}", i), i);
}
}
private int _row = -1;
private int _column = -1;

private int maxRows;
private int maxColumns;

private Workbook _workbook;
public TestDataProvider(Workbook workbook, int maxRows, int maxColumns)
{
this._workbook = workbook;
this.maxRows = maxRows;
this.maxColumns = maxColumns;
FillTable();
}

#region LightCellsDataProvider Members

public bool IsGatherString()
{
return false;
}

public int NextCell()
{
++_column;
if (_column < this.maxColumns)
return _column;
else
{
_column = -1;
return -1;
}
}
public int NextRow()
{
++_row;
if (_row < this.maxRows)
{
_column = -1;
return _row;
}
else
return -1;
}

public void StartCell(Cell cell)
{
cell.PutValue(dt.Rows[_row][_column]);//_row + _column);
}

public void StartRow(Row row)
{
}

public bool StartSheet(int sheetIndex)
{
if (sheetIndex == 0)
{
return true;
}
else
return false;
}

#endregion
}
}



Thanks,
3

Do i have to add any other dll to use this LightCells API ?

I am trying to place this in my code and i am facing error :

Error 2 'LightCellsDataProvider' is not a member of 'Aspose.Cells.OoxmlSaveOptions'.

Thanks for the help!

Regards,

Niveditha

4

Hi,


You might be using wrong Aspose.Cells.Dll version or your project / solution target framework might be client profile version etc. Anyways, I have attached the project here for your reference.

Thank you.
5

Hi Team,

I used your dll and i am now facing the below exception.

ex = {"The subscription included in this license allows free upgrades until 15 Jun 2012, but this version of the product was released on 31 May 2013. Please renew the subscription or use a previous version of the product."}

6

Hi,


As your subscription is already expired on June 15, 2012, so you cannot use any version that is released after your subscription. I think you may try to use any version of the product that is released before your subscription expiry date.

Alternatively, you may upgrade your subscription, you may post a query into Aspose.Purchase forum for more details and for your help.

Thank you.
7

I would like to test this dll before i purchase Lisence. Do you have trial dll that i can use for testing purpose?

8

Hi,


You may either comment out the licensing code or apply for a 30-days temporary license to get here:
http://www.aspose.com/corporate/purchase/temporary-license.aspx

Thank you.

9

Can you send me code sample in VB.NET?

10

Hi,


Sure see the sample code below for reference:

Sample code:

Imports System
Imports System.Collections.Generic
Imports System.Text
Imports Aspose.Cells
Imports System.Data

Namespace LightOoxmlTest
Friend Class Program

Public Shared Sub Main(ByVal args() As String)
'Specify your desired matrix
Dim rowsCount As Integer = 1000000
Dim colsCount As Integer = 3

Dim workbook = New Workbook()
Dim ooxmlSaveOptions = New OoxmlSaveOptions()

ooxmlSaveOptions.LightCellsDataProvider = New TestDataProvider(workbook, rowsCount, colsCount)

workbook.Save(“e:\test2\myoutput_test1.xlsx”, ooxmlSaveOptions)
End Sub
End Class

Friend Class TestDataProvider
Inherits 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 maxRows As Integer
Private maxColumns As Integer

Private _workbook As Workbook
Public Sub New(ByVal workbook As Workbook, ByVal maxRows As Integer, ByVal maxColumns As Integer)
Me._workbook = workbook
Me.maxRows = maxRows
Me.maxColumns = maxColumns
FillTable()
End Sub

#Region “LightCellsDataProvider Members”

Public Function IsGatherString() As Boolean
Return False
End Function

Public Function NextCell() As Integer
_column += 1
If _column < Me.maxColumns Then
Return _column
Else
_column = -1
Return -1
End If
End Function
Public Function NextRow() As Integer
_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)
cell.PutValue(dt.Rows(_row)(_column)) '_row + _column);

End Sub

Public Sub StartRow(ByVal row As Row)
End Sub

Public Function StartSheet(ByVal sheetIndex As Integer) As Boolean
If sheetIndex = 0 Then
Return True
Else
Return False
End If
End Function

#End Region
End Class
End Namespace

11

When i tried to test this functionality in my code, I am facing error at Friend Class TestDataProvider
Inherits LightCellsDataProvider

Friend Class TestDataProvider

Inherits LightCellsDataProvider

"Classes can inherit only from other classes"

My Other class is Public Class a()

12

Hi,


Sorry for error in the codes. I have now written complete console application in VB.NET, I have pasted the complete runnable code here for your reference:

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 workbook = New Workbook()
Dim ooxmlSaveOptions = New OoxmlSaveOptions()

ooxmlSaveOptions.LightCellsDataProvider = New TestDataProvider(workbook, rowsCount, colsCount)

workbook.Save(“e:\test2\myoutput_test2.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 maxRows As Integer
Private maxColumns As Integer

Private _workbook As Workbook
Public Sub New(ByVal workbook As Workbook, ByVal maxRows As Integer, ByVal maxColumns As Integer)
Me._workbook = workbook
Me.maxRows = maxRows
Me.maxColumns = maxColumns
FillTable()
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 = 0 Then
Return True
Else
Return False
End If
End Function

End Class


Hope, this helps.

Thank you.
13

This works grt!

But, I am unable to use Response object while saving the report.

workbook.Save(

"ImportingData.xls", FileFormatType.Excel97To2003, SaveType.OpenInExcel, Me.Response)

Please code in Vb.net if you have someother alternative for this

14

Hi,



Well, in the new versions, you will use the following line to response the Excel file on the client end in a web project.

workbook.Save(Me.Response, “ImportingData.xls”, ContentDisposition.Attachment, New XlsSaveOptions())

Also, please check the document for your complete reference on how to use Workbook.Save
for saving Excel workbooks to different file formats and for different scenarios.

Thank you.
15

did you attach any document?

16

Hi,


Sorry for missing the link, here is the document link, please see the document for your reference:
http://www.aspose.com/docs/display/cellsnet/Saving+Files

Thank you.

17

I am aware of this link. But in the new version this is throwing error.

18

I am now using your latest code in vb. I have workbook template with many worksheets and each sheet needs to be dumped with 1000000 data. I have modified your code and passing the required datatable to the constructor. However, it is not dumping the data properly . It is missing some of the columns. If i am using your code as is, it is working good.

Please respond as soon as possible as we need to take decision on buying new License for Aspose latest version.

Thanks in advance.

19

Hi,


Just for your information, currently in LightCells mode, you cannot open/read template files and fill the data into it, you can only create the files from the scratch in LightCells mode using LightCells APIs as per my sample example code (runnable). If you are creating new Excel workbook from scratch and filling the data into it using LightCells APis and still finds the issue, please create a sample console application (runanble), zip it and post it here, we will check your issue soon.

Thank you.
20

What if i want to create a excel from scratch but want the data to be in multiple sheets? if we have this functionality, please send me sample code.