Skip to content

Instantly share code, notes, and snippets.

@dschien
Last active December 19, 2015 18:49
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save dschien/6001918 to your computer and use it in GitHub Desktop.
Save dschien/6001918 to your computer and use it in GitHub Desktop.
Cumbersome but working Apache POI save named cell reference
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 {
@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);
String formulaText = sheet.getSheetName() + "!" + "$" + CellReference.convertNumToColString(cell.getColumnIndex()) + "$" + (cell.getRowIndex() + 1);
// String formulaText = sheet.getSheetName() + "!" + new CellReference(cell).formatAsString();
cellName.setRefersToFormula(formulaText);
return cellName;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment