/**
* Creates a non shared formula from the shared formula counterpart
*
* @param si Shared Group Index
* @return non shared formula created for the given shared formula and this cell
*/
private String convertSharedFormula(int si) {
XSSFSheet sheet = getSheet();
CTCellFormula f = sheet.getSharedFormula(si);
if (f == null)
throw new IllegalStateException(
"Master cell of a shared formula with sid=" + si + " was not found");
String sharedFormula = f.getStringValue();
// Range of cells which the shared formula applies to
String sharedFormulaRange = f.getRef();
CellRangeAddress ref = CellRangeAddress.valueOf(sharedFormulaRange);
int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet);
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(sheet.getWorkbook());
SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL2007);
Ptg[] ptgs = FormulaParser.parse(sharedFormula, fpb, FormulaType.CELL, sheetIndex);
Ptg[] fmla =
sf.convertSharedFormulas(
ptgs, getRowIndex() - ref.getFirstRow(), getColumnIndex() - ref.getFirstColumn());
return FormulaRenderer.toFormulaString(fpb, fmla);
}
/**
* セルに設定された計算式を評価して値を取得する
*
* @param nRow 行番号
* @param nColumn 列番号
* @return セルの値
*/
public static Object getDataByEvaluateFormula(XSSFSheet sheet, int nRow, int nColumn) {
assert sheet != null;
XSSFRow row = getRowAnyway(sheet, nRow);
if (row != null) {
XSSFCell cell = row.getCell(nColumn);
if (cell != null) {
FormulaEvaluator eval = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
if (eval != null) {
CellValue value = eval.evaluate(cell);
if (value != null) {
return value.getNumberValue();
}
}
}
}
return null;
}
/**
* ハイパーリンクの設定
*
* @param sheet シート
* @param nRow 対象行番号
* @param nColumn 対象列番号
* @param value ハイパーリンクテキスト
* @param url ハイパーリンク先URL
*/
public static void setHyperLink(
XSSFSheet sheet, int nRow, int nColumn, String value, String url) {
assert sheet != null;
XSSFWorkbook workbook = sheet.getWorkbook();
CreationHelper helper = workbook.getCreationHelper();
Hyperlink hyperlink = helper.createHyperlink(Hyperlink.LINK_URL);
hyperlink.setAddress(url);
XSSFRow row = getRowAnyway(sheet, nRow);
XSSFCell cell = getCellAnyway(row, nColumn);
cell.setCellValue(value);
cell.setHyperlink(hyperlink);
// ハイパーリンクテキストの装飾
XSSFFont font = workbook.createFont();
XSSFCellStyle style = workbook.createCellStyle();
// font.setColor(new XSSFColor(new Color(0, 0, 255)));
font.setUnderline(XSSFFont.U_SINGLE);
style.setFont(font);
cell.setCellStyle(style);
}
/**
* update cell references when shifting rows
*
* @param n the number of rows to move
*/
protected void shift(int n) {
int rownum = getRowNum() + n;
CalculationChain calcChain = _sheet.getWorkbook().getCalculationChain();
int sheetId = (int) _sheet.sheet.getSheetId();
String msg =
"Row[rownum="
+ getRowNum()
+ "] contains cell(s) included in a multi-cell array formula. "
+ "You cannot change part of an array.";
for (Cell c : this) {
XSSFCell cell = (XSSFCell) c;
if (cell.isPartOfArrayFormulaGroup()) {
cell.notifyArrayFormulaChanging(msg);
}
// remove the reference in the calculation chain
if (calcChain != null) calcChain.removeItem(sheetId, cell.getReference());
CTCell ctCell = cell.getCTCell();
String r = new CellReference(rownum, cell.getColumnIndex()).formatAsString();
ctCell.setR(r);
}
setRowNum(rownum);
}
/**
* Returns the cell at the given (0 based) index, with the {@link
* org.apache.poi.ss.usermodel.Row.MissingCellPolicy} from the parent Workbook.
*
* @return the cell at the given (0 based) index
*/
public XSSFCell getCell(int cellnum) {
return getCell(cellnum, _sheet.getWorkbook().getMissingCellPolicy());
}