Removing decimal value from a column if Exists

Aspose.Cells Product Family
1

Hi,

I have an Excel sheet generated with columns. I need to open this sheet and for some columns I need to check whether the value contains decimal places. If so, I need to remove decimal and display only the whole value.

How do I achieve this. Plz guide me using Aspose.Cells version 7.2

2

Hi,


Please see the following sample code for your needs, it will find out if the cell has decimal value while splitting it, then it will input the number part to the cell (removing the decimal value part).

Sample code:

Workbook workbook = new Workbook(“e:\test2\RemoveDecimal.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
string [] words;
//Browse all the cells in the sheet.
foreach (Aspose.Cells.Cell cell in cells)
{
if (cell.Type == CellValueType.IsNumeric)
{
string text_value = cell.StringValue;
// Split string based on dot
words = text_value.Split(new char[] { ‘.’ });

//if array contain decimal
if (words.Length > 1)
{
cell.PutValue(words[0].ToString(), true);

}

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

Thank you.
3

Thanks for your reply.

But my excel having some twenty column. Only some two columns I need to remove decimal points and remaining column I dont want to remove decimal places.

Please find the attached sample excel sheet. In that I need to remove decimal points only for the column contract Length and ContractualTermsID. For other columns I dont want to remove decimal places.

How this can be done. Please need your help on this.

4

Hi,


Well, you have to use your own code to remove the decimal places in those columns. You may loop through in those columns for your need. Also, your columns “Contract Length and ContractualTermsID” are already rendered without decimals.

I have pasted a sample piece of code that iterate though the “Fixed Fee” column and removes the decimals places.

//Find the Fixed Fee column’s indexed number.
Aspose.Cells.Cell foundCell = cells.FindString(“Fixed Fee”, null);
int colNumber = foundCell.Column;
string [] words;
//Browse all the cells in the sheet.
foreach (Aspose.Cells.Cell cell in cells)
{
if (cell.Type == CellValueType.IsNumeric)
{

if (cell.Column == colNumber)
{
string text_value = cell.StringValue;
// Split string based on dot
words = text_value.Split(new char[] { ‘.’ });

//if array contain decimal
if (words.Length > 1)
{
cell.PutValue(words[0].ToString(), true);


}

}
}
}

Once more thing, your so called template XLS file is actually a corrupted Spreadsheet ML file that cannot be opened fine in MS Excel either, MS Excel with give you error message, so does Aspose.Cells for .NET.

Thank you.
5

Hi Amjad,

Thank you so much.

Even I found the error while opening the excel file.

Let me see how I can overcome that. Thanks for pointing me out.

The logic which you have given for removing decimals goes well. Thanks

6

Hi,

Thanks for your feedback.

If you still face any other issue, please feel free to let us know, we will be glad to help you asap.