My data has 8288 rows and 314 columns. I need to CONCATENATE columns 7 through 314 for each row. I realize the limitations for a formula is 256 arguments so I have two CONCATENATE fields. Columns B and C. Column B has no issues and I am able to populate all 8288 rows Concatenating columns G through HZ. When I try to CONCATENATE columns IA through LB I get the System.ArgumentOutOfRangeException error. If I change it from a formula to a value I do not get this error.
I have also tried using =CONCAT(G[row] : LB[row]) in my code but the excel document gets =CONCAT@(G[row] : LB[row]), not sure where the @ is coming from.
I appreciate your help in resolving this issue.
@bbeale,
Please share your sample file and code snippet with us for our testing. We will reproduce the problem and provide our feedback after analysis.
ahsaniqbalsidiqui,
The attachment is the outputBulk_Category_Upload_12-23-2020_14-15-7.zip (3.9 MB)
Dim book As New Workbook
Dim sheets As WorksheetCollection
sheets = book.Worksheets
Dim sheet As Worksheet = sheets(0)
sheet.Name = “Categories”
Dim originalValuesSheet As Worksheet = sheets.Add(“Original”)
Dim dt As DataTable = BuildQuery()
myReport.CreateExcelWorksheet(dt, sheet)
myReport.CreateExcelWorksheet(dt, originalValuesSheet)
originalValuesSheet.IsVisible = False
Dim rows = sheet.Cells.Rows.Count
Dim numberOfColumns = sheet.Cells.Columns.Count
Dim columns = GetNumerOfColumnsInExcelDoc(numberOfColumns)
For row As Integer = 2 To rows
Dim concatFunction As String = String.Empty
Dim compareFunction As String = String.Empty
Dim argumantsSetOne As String = String.Empty
Dim argumantsSetTwo As String = String.Empty
For column As Integer = 7 To numberOfColumns
Dim currentColumnLetter = GetNumerOfColumnsInExcelDoc(column)
If column <= 234 Then
argumantsSetOne = argumantsSetOne + currentColumnLetter + row.ToString() + If(column = 234, "", ",")
Else
argumantsSetTwo = argumantsSetTwo + currentColumnLetter + row.ToString() + If(column = numberOfColumns, "", ",")
End If
Next
'If I change the following two rows to<a class="attachment" href="/uploads/default/45816">Bulk_Category_Upload_12-23-2020_14-15-7.zip</a> (3.9 MB)
.Formula from .Value I get an ArgumentOutOfRangeException, see below
sheet.Cells(“C” + row.ToString).Value = “=CONCATENATE(” + argumantsSetTwo + “)”
originalValuesSheet.Cells(“C” + row.ToString()).Value = “=CONCATENATE(” + argumantsSetTwo + “)”
sheet.Cells("B" + row.ToString).Formula = "=CONCATENATE(" + argumantsSetOne + ")"
originalValuesSheet.Cells("B" + row.ToString()).Formula = "=CONCATENATE(" + argumantsSetOne + ")"
sheet.Cells("A" + row.ToString).Formula = "=IF(B" + row.ToString + " <> Original!B" + row.ToString + ",""X"","""")"
Next
sheet.FreezePanes(1, 6, 1, 6)
' 'ERROR
' at System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource)
' at ..(StringBuilder , Byte[] , Int32 )
' at ..(Int32 , Int32 , Byte[] , Int32 , Int32 , Boolean )
' at ..(Cell )
' at Aspose.Cells.Cell.()
' at ..( )
' at ..Write( )
' at ..( )
' at ..( )
' at ..()
' at ..Write()
' at ..(Workbook , Stream , FileFormatType , SaveOptions )
' at Aspose.Cells.Workbook.Save(Stream stream, SaveOptions saveOptions)
' at Aspose.Cells.Workbook.Save(Stream stream, SaveFormat saveFormat)
' at ApparelSoft.ReportTemp.DownloadExcelFile(Workbook Book, String FileName, SaveFormat FileFormat) in E:\ApparelSoft\Applications\sbackstrom\Repos\ApparelSoft\ApparelSoft\ReportTemp.vb:line 196
' at Products_EditCategoriesBulk.zWeeksSupplyBTN_Click(Object sender, EventArgs e) in E:\ApparelSoft\Applications\sbackstrom\Repos\ApparelSoft\CorporateERP\Products\EditCategoriesBulk.aspx.vb:line 80
' at DevExpress.Web.ASPxButton.OnClick(EventArgs e)
' at DevExpress.Web.ASPxButton.RaisePostBackEvent(String eventArgument)
' at DevExpress.Web.ASPxWebControl.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
' at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
' at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
' at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
@bbeale,
Could you please share complete runnable project as there are some undeclared methods and properties like GetNumerOfColumnsInExcelDoc. We will analyze it and provide our feedback accordingly.
Build Query is the sql that gets the data in the table attached.
Private Function GetNumerOfColumnsInExcelDoc(ByVal columns As Integer) As String
Dim columnName As String
Dim modulo As Integer
While columns > 0
modulo = (columns - 1) Mod 26
columnName = Convert.ToChar(65 + modulo).ToString() + columnName
columns = (columns - modulo) / 26
End While
Return columnName
End Function@bbeale,
I am afraid that this code is still not useful for reproducing the issue. You may share a console application based complete runnable solution which can be used without any modification/addition like code to fill the DataTable “dt” should be working. Similarly “myReport.CreateExcelWorksheet” should be compilable also. It will help us to observe the problem here and share our feedback.
I created a console application, however I get a System.NullReferenceException: when trying to set the license. I have looked through your site and this seems to be a common issue without any real solution.
I have attached the console app ASPOSE_Issue.zip (3.5 MB)
@bbeale,
We have logged the issue as “CELLSNET-47796” in our database for investigations. Once we will have some news for you, we will update you in this topic.
@bbeale,
You are using a quite old version of our component(8.0). We have tested your project with latest version/fixes 20.12.x and the project works fine. It should be a bug of old versions and you should upgrade the component to the latest version.