Free Support Forum - aspose.com

Custom formatting of null values

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

Hi Lisa,

You can try:

if(isNull)

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

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

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

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

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");