Problem

Some blank cells in OOXML file formats return an empty string, while other blank cells return null.  The same file in BIFF format returns only null values.

This problem occurs because OOXML (.xlsx /.xlsm) preserves any empty strings in cells when the file is saved.  When saving a file in BIFF (.xls) any cells containing an empty string will be set to a null value.

This can be a concern for users switching to the OOXML file formats. Code that checks worksheets for null values may need to be amended to account for the change in behavior.

Example 

The following code illustrates how this discrepancy might affect users:

           ExcelApplication xlapp = new ExcelApplication();
		   Workbook wb = xlapp.Open("SampleFile.xlsx")  
		   Worksheet ws = wb[0];

		   //Trim a range of cells. 
		   //Because A1 is empty, its value will be 'empty string' after trimming.
		   for(int i = 0; i<= 20; i++)
           {
		   	  for(int j = 0; j<= 20; i++)
              {
                  string value = (string)ws.Cells[i, j].Value;
				  ws.Cells[i, j].Value = value.Trim();
              }
           }

 		   //Save and reopen the file in the OOXML format
           xlapp.Save(wb, Directory + "SampleFile.xlsx");
           wb = xlapp.Open(Directory + "SampleFile.xlsx");
           ws = wb.[0];
           if (ws.Cells[0, 0].Value == null)
            {
               // This code would be reached in .xls
			   // but not in .xlsx  
            }

Users who currently check for cells containing null values may find that their code produces unexpected results when switching to OOXML files.

Solution

In order to ensure that code will return correct results when dealing with blank cells, it is important to use logic that handles all possible values.  There are 2 ways that we suggest implementing this logic.

Solution 1 – Use IsNullOrEmpty when checking for blank cells

Use logic that checks whether a cell is either null or an empty string.

Example:

            if(string.IsNullOrEmpty((string)ws.Cells[0, 0].Value))
            {
               //With this new logic, it could be either file format
                ws.Cells[1, 1].Value = "This is a .xls or an .xlsx file";   
            }

Solution 2 – Keep empty string values from being assigned to cells

Check if a cell is null before performing any operations that would assign an empty string value to a null cell.

Example:

           //Check that the cell is not empty before trimming
           if( ws.Cells[0,0].Value != null)
           {
                  string value = (string)ws.Cells[0, 0].Value;
				  ws.Cells[0, 0].Value = value.Trim();
           }

Related Information:

If your worksheet contains formulas, best practice would be to check the cell for formulas as well. More information on how to check for empty cells can be found here.