Custom formatting of null values

Aspose.Cells Product Family
1

Hello. I am exporting a column to an excel spreadsheet. This column is for currency and if it is null, I want it to export at N/A, otherwise I want the value formatted as style 5. I have the following in my code:

Column expenditureColumn = worksheet.Cells.Columns[7];

expenditureColumn.Style.Number = 5;

expenditureColumn.Style.HorizontalAlignment = TextAlignmentType.Right;

worksheet.Cells.ImportDataColumn(dataSource.Occurrence, false, 11, 7, 13, false); // Expenditures

This works fine to handle the case where there is a value, but I can't figure out how to tell it if it's null, write out N/A. I thought about using a custom style similar to those that have the text colored red for negative values, but couldn't seem to get that working.

Any help would be greatly appreciated.

Thanks,

Lisa

2

Hi Lisa,

You can try:

if(isNull)

cell.PutValue("#N/A");

3

Laurence,

Thanks for the idea, but that doesn't seem to work with the worksheet.Cells.ImportDataColumn way that we're getting the data to Excel.

Is there a way to use a custom format? I was trying to get one working, but wasn't having much luck with that either.

Lisa

4

Hi Lisa,

You can try the following code to put "N/A" value into cells of the column having null values:

for(int i =0;i<=worksheet.Cells.MaxDataRow;i++)

{

if(worksheet.Cells[i,7].Value ==null)

{

worksheet.Cells[i,7].PutValue("N/A");

}

Regards

Amjad Sahi

Aspose Nanjing Team

5

Thanks, that worked. I was hoping to avoid looping through all the rows, though. In future releases, will there be a way to do that check as the data is being written out? Like have the ImportDataColumn return something and they can be checked and overwritten.

Lisa

6

Hi Lisa,

Please try this attached version. I added a new ImportDataColumn method to serve your need. Please try this piece of code:

worksheet.Cells.ImportDataColumn(dataSource.Occurrence, false, 11, 7, 13, false, "#N/A");