Free Support Forum - aspose.com

Aspose cell hangs for large data table

Hello

I am using Aspose Cell GridWeb to create an grid spreadsheet to save to Excel. I am using a data table to input into Aspose Cell as a data source.

If the data table contains small about of records, the Aspose Cell GridWeb generates the grid and shows the save icon. When the data table contains over 50,000 or larger records, the Aspose Cell GridWeb does not come back.

The grid spreadsheet code is as below.What I found with debugging using Visual Studio is the code perform rather fast but once it exist the Page Load, the grid nevers displayed and control is does not come back to internet explore. I get a emply page.

Thank you

Kevin

Imports System

Imports System.Data

Imports System.Configuration

Imports System.Collections

Imports System.Web

Imports System.Web.Security

Imports System.Web.UI

Imports System.Web.UI.WebControls

Imports System.Web.UI.WebControls.WebParts

Imports System.Web.UI.HtmlControls

Imports System.Data.OleDb

Imports Aspose.Cells.GridWeb.Data

Imports System.Drawing

Partial Class QueryExcel

Inherits BasePage

Protected Shadows Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim dtQutput As New DataTable

Dim _queryData As New QueryData

Dim queryEnvironment As String = Session("QueryEnvironment")

Dim sqlText As String

If (Not IsPostBack) Then

GridWeb1.WebWorksheets.Clear()

GridWeb1.WebWorksheets.Add()

'set sheets selectedIndex to 0

GridWeb1.WebWorksheets.ActiveSheetIndex = 0

sqlText = Session("QueryExcelSql")

dtQutput.Reset()

dtQutput = _queryData.QueryAsposeCell(queryEnvironment, sqlText)

' Clears datasheets first.

GridWeb1.WebWorksheets.Clear()

GridWeb1.EnableDoubleClickEvent = True

' Imports data from dataview object.

GridWeb1.WebWorksheets.ImportDataView(dtQutput.DefaultView, Nothing, Nothing)

Dim sheet As WebWorksheet = GridWeb1.WebWorksheets(0)

sheet.AutoFitColumns()

sheet.AutoFitRows()

GridWeb1.ShowTabBar = False

GridWeb1.ShowHeaderBar = True

GridWeb1.ShowBottomBar = True

GridWeb1.ShowSaveButton = True

GridWeb1.ShowSubmitButton = False

GridWeb1.ShowUndoButton = False

GridWeb1.EditMode = True

Dim style As New TableItemStyle()

style.HorizontalAlign = HorizontalAlign.Left

style.BorderStyle = BorderStyle.Solid

style.BorderColor = Color.Black

style.BorderWidth = 1

Dim col As Integer = 0

For i As Integer = 1 To GridWeb1.WebWorksheets(0).Cells.MaxRow

For j As Integer = 0 To GridWeb1.WebWorksheets(0).Cells.MaxColumn

Dim cell As WebCell = GridWeb1.WebWorksheets(0).Cells(i, j)

cell.Column.ToString.Trim()

cell.Style.CopyFrom(style)

Next j

Next i

End If

End Sub

Protected Sub GridWeb1_SaveCommand(ByVal sender As Object, ByVal e As System.EventArgs)

' Generates a temporary file name.

Dim filename As String = System.IO.Path.GetTempPath() + Session.SessionID & ".xls"

' Saves to the file.

Me.GridWeb1.WebWorksheets.SaveToExcelFile(filename)

' Sents the file to browser.

Response.ContentType = "application/vnd.ms-excel"

'Adds header.

Response.AddHeader("content-disposition", "attachment; filename=book1.xls")

' Writes file content to the response stream.

Response.WriteFile(filename)

' OK.

Response.End()

End Sub

End Class

Hi,

I have tested the scenario a bit using the following test code with the attached (latest) version/fix and it works fine although the process does take some time and consume certain amount of memory.

If (Not IsPostBack) Then

Dim dt As System.Data.DataTable = New DataTable(“Table1”)
dt.Columns.Add(“Customer Identification Number”, GetType(String))
dt.Columns.Add(“Customer Name”, GetType(String))
dt.Columns.Add(“Description Text”, GetType(String))
For i As Integer = 1 To 50000
Dim dr As System.Data.DataRow = dt.NewRow()
dr(“Customer Identification Number”) = i.ToString()
dr(“Customer Name”) = "Name " & i
dr(“Description Text”) = "Description " & i
dt.Rows.Add(dr)
Next i




GridWeb1.WebWorksheets.Clear()
GridWeb1.EnableDoubleClickEvent = True
’ Imports data from dataview object.
GridWeb1.WebWorksheets.ImportDataView(dt.DefaultView, Nothing, Nothing)
Dim sheet As WebWorksheet = GridWeb1.WebWorksheets(0)
sheet.AutoFitColumns()
sheet.AutoFitRows()
GridWeb1.ShowTabBar = False
GridWeb1.ShowHeaderBar = True
GridWeb1.ShowBottomBar = True
GridWeb1.ShowSaveButton = True
GridWeb1.ShowSubmitButton = False
GridWeb1.ShowUndoButton = False
GridWeb1.EditMode = True
Dim style As New Aspose.Cells.GridWeb.TableItemStyle()
style.HorizontalAlign = HorizontalAlign.Left
style.BorderStyle = BorderStyle.Solid
style.BorderColor = System.Drawing.Color.Black
style.BorderWidth = 1
Dim col As Integer = 0
For i As Integer = 1 To GridWeb1.WebWorksheets(0).Cells.MaxRow
For j As Integer = 0 To GridWeb1.WebWorksheets(0).Cells.MaxColumn
Dim cell As WebCell = GridWeb1.WebWorksheets(0).Cells(i, j)
cell.Column.ToString().Trim()
cell.Style.CopyFrom(style)

Next j

Next i

End If


For your sample code the extra time is consumed loading big data table, auto-fitting rows/columns operation and instantiating each and every cell in the worksheet to apply the style formatting etc.
The process would take extra memory and does take some time to be completed.

Could you try the attached version and let us know if it works fine a bit.

If you still fine some issue, kindly create a test code to paste it here, we will further look into the issue soon.

Thank you.