Broken Apache POI save named reference The following code results in illegal named references that change when the selected cell changes in Excel 2013

TestPOIWriteNamedCell.java

import static org.junit.Assert.*;

import java.awt.Desktop;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Name;
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.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

public class TestPOIWriteNamedCell {

  // from the cookbook - http://poi.apache.org/spreadsheet/quick-guide.html#NamedRanges
  // not working - creates cell references that move when the selected cell changes in the workbook
  @Test
	public void testPOICookBook() throws IOException {
		Workbook wb = new XSSFWorkbook();

		Sheet sheet = wb.createSheet();
		Row row = sheet.createRow(0);

		// setup code
		String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";

		sheet.createRow(0).createCell((short) 0).setCellValue(cvalue);

		// 1. create named range for a single cell using areareference
		Name namedCell = wb.createName();
		namedCell.setNameName(cname);
		String reference = sname + "!A1:A1"; // area reference
		namedCell.setRefersToFormula(reference);
	}

  // not working - 
	@Test
	public void testSingleCell() throws IOException {
		Workbook wb = new XSSFWorkbook();

		Sheet sheet = wb.createSheet();
		Row row = sheet.createRow(0);

		Name cellName = createCell(0, wb, sheet, row, 1, "_A_name");
		Name cellBName = createCell(1, wb, sheet, row, 2, "_B_name");

		String unittestName = "cellName";
		File tmpFile = File.createTempFile(unittestName, ".xlsx");

		wb.write(new FileOutputStream(tmpFile));
		Desktop.getDesktop().open(tmpFile);
	}

	private Name createCell(int colIdx, Workbook wb, Sheet sheet, Row row, Integer VALUE, String name) {
		Cell cell = row.createCell(colIdx);
		cell.setCellValue(VALUE);
		Name cellName = wb.createName();
		cellName.setNameName(name);
    // the culprit - you cannot receive a valid name for a named cell from the CellReference
		String formulaText = sheet.getSheetName() + "!" + new CellReference(cell).formatAsString();
		cellName.setRefersToFormula(formulaText);
		return cellName;
	}

}