I applied your suggestion but still my issue is not resolved due to below statement
If objWorkbook.Worksheets(intWS).Cells.IsBlankColumn(intCol) Then
Continue For
End If
In my logic, I dont want to process on Blank columns. so, I wrote above If…Else statement and due to IsBlankColumn statement, I think it again Parse all the formula of the column.
see below entire code:
For intWS As Integer = 0 To objWorkbook.Worksheets.Count - 1
strWSname = objWorkbook.Worksheets(intWS).Name & “”
For intCol As Integer = 0 To objWorkbook.Worksheets(intWS).Cells.MaxDisplayRange.ColumnCount - 1
'==================================
'THIS 'IsBlankColumn' IS CREATING ISSUE
'==================================
If objWorkbook.Worksheets(intWS).Cells.IsBlankColumn(intCol) Then
Continue For
End If
For intRow As Integer = 0 To objWorkbook.Worksheets(intWS).Cells.MaxDisplayRange.RowCount - 1
objCell = objWorkbook.Worksheets(intWS).Cells(intRow, intCol)
If IsNothing(objCell) = False Then
arrData(0) = strWSname
arrData(1) = objCell.Name & ""
arrData(2) = objCell.Formula & ""
arrData(3) = objCell.Value & ""
mdtData.Rows.Add(arrData)
End If
Next
Next
mdtData.AcceptChanges()
Next
I can understand if you change any thing in ObjCell.Formula, It will be impact a lot at your side. So, What I want is new Property like ObjCell.FormlaOriginal
When you open the workbook, just fill FormlaOriginal property from Excel file.
If you give this new property my code will be like this:
For intRow As Integer = 0 To objWorkbook.Worksheets(intWS).Cells.MaxDisplayRange.RowCount - 1
objCell = objWorkbook.Worksheets(intWS).Cells(intRow, intCol)
If IsNothing(objCell) = False Then
arrData(0) = strWSname
arrData(1) = objCell.Name & ""
arrData(2) = objCell.FormulaOriginal & ""
arrData(3) = objCell.Value & ""
mdtData.Rows.Add(arrData)
End If
Next
@harshCIPL22,
From your code, we found the issue was caused by Cells.IsBlankColumn() method which will trigger the formula parser. We will provide fix for it soon.
However, you should try to access cells “row by row” rather than “column by column” for as most situations as possible. Performance of accessing cells in row is always better than accessing them in column. If you have to access them in column, such as your logic in the sample code, you should just use the Range and IEnumerator directly. Checking the blank column and EndCell is redundant operation which will iterate those cells more than one time. So the checking is not needed and may cause performance issue for large dataset.
@harshCIPL22,
Supporting such a property is not needed. The formula is being changed because other operations trigger the parsing process. Just like our code, if you do not use those operations (in your code it is Cells.IsBlankColumn), you may get the original formula string.
We will provide a fix in which we will remove the trigger of parsing formula from Cells.IsBlankColumn() method because it is unnecessary.
I would like to share another same issue with the file whose extension is .xls.
I am not able to get the formula as It is in this file with the same code that we modified.
The Code is:
For Each objWorksheet In objWorkbook.Worksheets
If Not IsNothing(objWorksheet) Then
strWSname = objWorksheet.Name & ""
Dim lstRanges As New List(Of Range)
lstRanges.Add(objWorksheet.Cells.MaxDisplayRange)
If Not IsNothing(lstRanges) Then
For Each objAnalysisRange In lstRanges
For intCol As Integer = objAnalysisRange.FirstColumn To objAnalysisRange.FirstColumn + objAnalysisRange.ColumnCount - 1
Dim strColumnName As String = String.Empty
strColumnName = CellsHelper.ColumnIndexToName(intCol)
If objWorksheet.Cells.IsBlankColumn(intCol) Then
Continue For
End If
Dim objEndCell As Cell = objWorksheet.Cells.EndCellInColumn(intCol)
If IsNothing(objEndCell) Then
Continue For
End If
Dim lngMaxDataRow As Long = objEndCell.Row
Dim objRange As Range = objWorksheet.Cells.CreateRange(strColumnName & (objWorksheet.Cells.MinDataRow + 1) & ":" & strColumnName & (lngMaxDataRow + 1))
For Each objCell In objRange
If IsNothing(objCell) = False Then
arrData(0) = strWSname
arrData(1) = objCell.Name & ""
arrData(2) = objCell.Formula & ""
arrData(3) = objCell.Value & ""
mdtData.Rows.Add(arrData)
End If
Next
mdtData.AcceptChanges()
Next
Next
End If
End If
Next
Please note, in the new fix, we disabled the formula parser in Cells.IsBlankColumn(). Now you previous code should be able to get the original formula expressions as what saved in the template file.
However, as we told you before, we think you need not to check the blank column or end of column. Using the IEnumerator got from the column range directly should be able to give better performance, no matter if it is blank or not. So, could you update or accommodate your code segment for the newly shared issue. Moreover, it is always better to create new thread for newer issue. It will help us to manage the issues and to consequently figure it out soon.
Though I am using the below simple code Its not able to get that formulas.
For Each objWorksheet In objWorkbook.Worksheets
For Each objCell In objWorksheet.Cells.MaxDisplayRange
If IsNothing(objCell) = False Then
arrData(0) = strWSname
arrData(1) = objCell.Name & ""
arrData(2) = objCell.Formula & ""
arrData(3) = objCell.Value & ""
mdtData.Rows.Add(arrData)
End If
Next
mdtData.AcceptChanges()
Next
In the above attached Project.
You can see the two file formats with just simple code.
you can test both the files. You’ll get the formula as its is in .xlsx format but not in .xls.
@harshCIPL22,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSNET-47162 – Spaces are lost from the formula when accessed from XLS
Ok thanks for reopening the issue.
I would like to draw your attention towards the issue that you should check it with all the supported file formats.
It may also having issue with other file formats too.
We evaluated your issue further. Please note, in XLS file, there is no original string expression but only parsed binary data for formulas. As, we told you, for parsed formula data we cannot keep the spaces in the re-built formula string. The space token in formula is a bit complex and we need to make further investigations. Maybe we cannot support is in short time.
The issues you have found earlier (filed as CELLSNET-47125) have been fixed in Aspose.Cells for .NET v20.2. This message was posted using Bugs notification tool by Amjad_Sahi
But I am still having the issue with .xls file.
Its still trimming the spaces from Formula though I am using
LoadOptions.ParsingFormulaOnOpen = False.
It works fine with .xlsx file not in .xls files
The issues you have found earlier (filed as CELLSNET-47162) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi