Clear Error in cell

Aspose.Cells Product Family
1

Hi,

We are facing problem when there is an error in cell. We have code as below to resolve the problem

If (cell.IsErrorValue) Then

cell.putvalu("0")

End If

However with this, formula in cell is getting overwrite. Please help ASAP.

Thanks,

Yogita

This message was posted using Aspose.Live 2 Forum
2

Hi Yogita,

Please provide us some sample application and your sample input Excel file to look into this issue.

3

Hi,

Sorry for the confusion.

Well, it is not an issue of Aspose.Cells by any means, so no need to provide us the sample project as it is not an issue rather MS Excel behavior.
For your information, either we can set value or set formula to the cells. If we set formula the value would be overwritten, when a valid formula would be calculated, it may provide you the calculated resultant value else error value. Similarly if we set the value, the existing formula would be overwritten. This behavior is same as MS Excel, you may confirm it.

Thanks for your understanding!


4

Okay. Thanks.

Is there any alternative like we can hide the error without using cell.putvalue method?

Thanks,

Yogita.

5

Is there any equivalent code in ASPOSE for following which is in VB -

With objExcel.Application

.Worksheets(SheetName).Activate
.Selection.SpecialCells(xlFormulas, 16).ClearContents
End With

Please help ASAP.

Thanks,

Yogita.

6

Hi,

1) “Is there any alternative like we can hide the error without using cell.putvalue method?”
How could you do this in Ms Excel, if MS Excel provides this feasibility we can do otherwise we cannot.

2)
To activate a worksheet or cells, see the document:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/activating-sheets-and-making-an-active-cell-in-the-worksheet.html

Also, I am not sure about line of code that what does this line do, you may explain us if you have any doubt:
.Selection.SpecialCells(xlFormulas, 16).ClearContents

Thank you.

7

Hi

The line of code indicates that if the cell contains errror value then it clears the error.

We are using template which contains some cells with divide by zero error to generate report. At runtime we are putting data in cells. For those error cells we are using cell.putvalue(0) method to remove error. But if the cell conatins the value then also the cell is putting value as 0. Means the IsError() property is returning true.

Please help ASAP.

Thanks,

Yogita.

8

Hi,

Well, you may do your task and the code should be very simple, see the attached input file first. I will paste the sample code here for your reference. You may modify/update my code segment accordingly too. The output file is also attached.

Sample code:

Workbook workbook = new Workbook(“e:\test2\ClearDivError.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;

//Clear all the cells including every type of data.
//cells.Clear();
//Or you may use the following is you want to clear everything in a range:
//cells.ClearContents(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1);

workbook.CalculateFormula();

//To clear only the errors in the cells
foreach (Aspose.Cells.Cell cell in cells)
{
if (cell.IsErrorValue)
{
MessageBox.Show(cell.StringValue); // Check #DIV/0! error
//Clear the error if you want
//cell.PutValue(null);
//Or put “0” as value
cell.PutValue(0);
}

}

workbook.Save(“e:\test2\outclearadfdfdf.xlsx”);

Thank you.

9

Hi,

My previous sample code is in C#, I will paste the equivalent VB.NET code for your reference:

Dim workbook As New Workbook(“e:\test2\ClearDivError.xlsx”)
Dim worksheet As Worksheet = workbook.Worksheets(0)
Dim cells As Cells = worksheet.Cells
'Clear all the cells including every type of data.
'cells.Clear();
'Or you may use the following is you want to clear everything in a range:
'cells.ClearContents(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1);

workbook.CalculateFormula()

'To clear only the errors in the cells
For Each cell As Aspose.Cells.Cell In cells
If cell.IsErrorValue Then
MessageBox.Show(cell.StringValue) ’ Check #DIV/0! error
'Clear the error
'cell.PutValue(null);
'Or put “0” as value
cell.PutValue(0)
End If

Next cell

workbook.Save(“e:\test2\outclearadfdfdf.xlsx”)


Thank you.