Bug 59132

Can you provide a standalone unit-test for this? This would make it much easier to investigate...

Created attachment 33659 [details]
excel file for testing code

excel file for testing code

I have written a test code for the issue:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellReference;

public class TestCountBlank {

	public static void main(String[] args) {
		

		try {
			File excelFile = new File("D:\\test blank.xlsx");
			
			FileInputStream input;
			
			input = new FileInputStream(excelFile);
			Workbook workbook = WorkbookFactory.create(input);
			Sheet worksheet = workbook.getSheet("sheet1");
			
			CellReference ref = new CellReference("B3");
			
			Row row = worksheet.getRow(ref.getRow());
			Cell cell = row.getCell(ref.getCol());
			
			cell.setCellValue((String)null);
						
			FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
			
			CellReference ref2 = new CellReference("A5");
			
			Row row2 = worksheet.getRow(ref2.getRow());
			Cell cell2 = row2.getCell(ref2.getCol());
			
			evaluator.evaluateFormulaCell(cell2);
				
			FileOutputStream output = new FileOutputStream(excelFile);
						
			workbook.write(output);
			output.flush();
			output.close();
			workbook.close();
			
			
			
		} catch (EncryptedDocumentException | InvalidFormatException | IOException e) {
			
			e.printStackTrace();
		}
		
		

	}

}

The code automatically delete "Alice" at cell B3 and evaluate COUNTBLANK() formula at cell A5, the result of COUNTBLANK() is still zero.

Sorry, the code should be revised as follows:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellReference;

public class TestCountBlank {

	public static void main(String[] args) {
		

		try {
			File excelFile = new File("D:\\test blank.xlsx");
			
			FileInputStream input;
			
			input = new FileInputStream(excelFile);
			Workbook workbook = WorkbookFactory.create(input);
			Sheet worksheet = workbook.getSheet("sheet1");
			
			CellReference ref = new CellReference("B3");
			
			Row row = worksheet.getRow(ref.getRow());
			Cell cell = row.getCell(ref.getCol());
			
			cell.setCellValue((String)null);
						
			FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
			
			CellReference ref2 = new CellReference("A3");
			
			Row row2 = worksheet.getRow(ref2.getRow());
			Cell cell2 = row2.getCell(ref2.getCol());
			
			evaluator.evaluateFormulaCell(cell2);
			
			CellReference ref3 = new CellReference("A5");
			
			Row row3 = worksheet.getRow(ref3.getRow());
			Cell cell3 = row3.getCell(ref3.getCol());
			
			evaluator.evaluateFormulaCell(cell3);
				
			FileOutputStream output = new FileOutputStream(excelFile);
						
			workbook.write(output);
			output.flush();
			output.close();
			workbook.close();
			
			
			
		} catch (EncryptedDocumentException | InvalidFormatException | IOException e) {
			
			e.printStackTrace();
		}
		
		

	}

}

The code deletes "Alice" at cell B3 and evaluate formula at A3 and 
COUNTBLANK() formula at cell A5, the result of COUNTBLANK() is still zero.

This should be fixed via r1737009, we now count empty string cells as blank to be in conformance with the description of COUNTBLANK at https://support.office.com/en-us/article/COUNTBLANK-function-6a92d772-675c-4bee-b346-24af6bd3ac22 and how Excel seems to have it implemented.