Java Code Examples for org.apache.poi.ss.util.CellReference#convertNumToColString()

public String getKyokaAvgFormula(Point point) {

            // レコード名が平均のときのみ数式を出力する
            if(!name.equals("平均")) {
                return null;
            }

            // レコードのサイズ（平均用のレコード行を覗いた値）
            final int dataSize = parent.getRecords().size() -1;

            // 列名
            final String colAlpha = CellReference.convertNumToColString(point.x);

            // 平均値の開始/終了の行番号
            final int startRowNumber = point.y - dataSize+1;
            final int endRowNumber = point.y;

            return String.format("AVERAGE(%s%d:%s%d)", colAlpha, startRowNumber, colAlpha, endRowNumber);
        }
 

/**
 * create restrictions on the data cells
 * 
 * @param mainsheet sheet with data
 * @param restrictionsheet sheet with restriction values
 * @param column index of column (starting with zero)
 * @param nbofchoices number of choices (starting with zero)
 * @param nbofrows number of rows (starting with zero)
 */
public static  void setRestrictionsOnCell(Sheet mainsheet,Sheet restrictionsheet,int column,int nbofchoices,int nbofrows) {
	DataValidationHelper validationHelper = new XSSFDataValidationHelper((XSSFSheet)mainsheet);
	String columnletter =  CellReference.convertNumToColString(column);
	String formula = "'"+restrictionsheet.getSheetName()+ "'!$"+columnletter+"$"+1+":$"+columnletter+"$"+nbofchoices;
	DataValidationConstraint constraint = validationHelper.createFormulaListConstraint(formula);
	CellRangeAddressList addressList = new CellRangeAddressList(1,nbofrows,column,column);
	
	DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);
	dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
	dataValidation.setSuppressDropDownArrow(true);
	mainsheet.addValidationData(dataValidation);
}
 

/**
 * CellAddressのインスタンスを作成する。
 *
 * @param row 行番号 (0から始まる)
 * @param column 列番号 (0から始まる)
 * @throws IllegalArgumentException {@literal row < 0 || column < 0}
 */
private CellPosition(int row, int column) {
    ArgUtils.notMin(row, 0, "row");
    ArgUtils.notMin(column, 0, "column");
    this.row = row;
    this.column = column;
    this.toStringText = CellReference.convertNumToColString(column) + (row + 1);
}
 

private Map<String, Integer> toColNamesMap(Row headerRow) {
  if (headerRow == null) return null;

  Map<String, Integer> columnIdx = new LinkedHashMap<>();
  int i = 0;
  for (Iterator<Cell> it = headerRow.cellIterator(); it.hasNext(); ) {
    try {
      String header =
          AbstractCellProcessor.processCell(ExcelUtils.toValue(it.next()), true, cellProcessors);
      if (header != null) {
        if (columnIdx.containsKey(header)) {
          throw new MolgenisDataException(
              format(
                  "Duplicate column header '%s' in sheet '%s' not allowed",
                  header, headerRow.getSheet().getSheetName()));
        }
        columnIdx.put(header, i++);
      }
    } catch (final IllegalStateException ex) {
      final int row = headerRow.getRowNum();
      final String column = CellReference.convertNumToColString(i);
      throw new IllegalStateException(
          "Invalid value at [" + sheet.getSheetName() + "] " + column + row + 1, ex);
    }
  }
  return columnIdx;
}
 

public static String getColumnReference(int columnIndex) {
	if (columnIndex == INFINITY_COORDINATE) {
		return INFINITY_COORDINATE_STRING;
	}
	return CellReference.convertNumToColString(columnIndex);
}
 

public static String getCellAddress(int row, int col) {
    return CellReference.convertNumToColString(col) + (row+1);
}
 

public static String getCellAddress(int col, int row) {
    return CellReference.convertNumToColString(col) + (row+1);
}
 

/**
 * 列番号を英字名に変換します。
 * @param column 列番号(1から始まる)
 * @return 列の英字名
 */
public static String colToAlpha(final int column) {
    return CellReference.convertNumToColString(column-1);
}
 

/**
 * 座標をExcelのアドレス形式'A1'などに変換する
 * @param rowIndex 行インデックス
 * @param colIndex 列インデックス
 * @return
 */
public static String formatCellAddress(final int rowIndex, final int colIndex) {
    return CellReference.convertNumToColString(colIndex) + String.valueOf(rowIndex+1);
}
 

/**
 * セルのアドレス'A1'を取得する。
 * @param cell セル情報
 * @return IllegalArgumentException cell == null.
 */
public static String formatCellAddress(final Cell cell) {
    ArgUtils.notNull(cell, "cell");
    return CellReference.convertNumToColString(cell.getColumnIndex()) + String.valueOf(cell.getRowIndex()+1);
}