org.apache.poi.hssf.util.CellReference Java Examples

/**
 * Generate fields names per column in text file.
 * Read first line and count columns and return fields names like excel sheet.
 * A, B, C and so on.
 *
 * @return field name strings, null if no records found in text file.
 */
private String[] generateColumnNames() throws SchemaChangeException, IOException, ExecutionSetupException {
  assert (settings.isAutoGenerateColumnNames());

  final boolean shouldSkipFirstLine = settings.isSkipFirstLine();
  settings.setSkipFirstLine(false);
  final String[] columns = readFirstLineForColumnNames();
  settings.setSkipFirstLine(shouldSkipFirstLine);
  if (columns != null && columns.length > 0) {
    String[] fieldNames = new String[columns.length];
    for (int i = 0; i < columns.length; ++i) {
      fieldNames[i] = CellReference.convertNumToColString(i);
    }
    return fieldNames;
  } else {
    return null;
  }
}
 

public static String[] validateColumnNames(String[] fieldNames) {
  final Map<String, Integer> uniqueFieldNames = Maps.newHashMap();
  if (fieldNames != null) {
    for (int i = 0; i < fieldNames.length; ++i) {
      if (fieldNames[i].isEmpty()) {
        fieldNames[i] = CellReference.convertNumToColString(i);
      }
      // If we have seen this column name before, add a suffix
      final Integer count = uniqueFieldNames.get(fieldNames[i]);
      if (count != null) {
        uniqueFieldNames.put(fieldNames[i], count + 1);
        fieldNames[i] = fieldNames[i] + count;
      } else {
        uniqueFieldNames.put(fieldNames[i], 0);
      }
    }
    return fieldNames;
  }
  return null;
}
 

/**
 * check if any shapes contain wrong data
 * At now(13.08.2010) check if patriarch contains 2 or more comments with same coordinates
 */
protected void preSerialize(){
    Map<Integer, NoteRecord> tailRecords = _boundAggregate.getTailRecords();
    /**
     * contains coordinates of comments we iterate over
     */
    Set<String> coordinates = new HashSet<String>(tailRecords.size());
    for(NoteRecord rec : tailRecords.values()){
        String noteRef = new CellReference(rec.getRow(),
                rec.getColumn()).formatAsString(); // A1-style notation
        if(coordinates.contains(noteRef )){
            throw new IllegalStateException("found multiple cell comments for cell " + noteRef );
        } else {
            coordinates.add(noteRef);
        }
    }
}
 

public void createCell(int columnIndex, double value, int styleIndex)
		throws IOException {
	String ref = new CellReference(_rownum, columnIndex)
			.formatAsString();
	_out.write( "<c r=\"" + ref + "\" t=\"n\"" );
	if (styleIndex != -1)
		_out.write( " s=\"" + styleIndex + "\"" );
	_out.write( ">" );
	_out.write( "<v>" + value + "</v>" );
	_out.write( "</c>" );
}
 

/**
 * Retrieves the column name associated with a given column index.<br>
 * If no column name is associated, generates a new column name using
 * {@link CellReference#convertNumToColString(int)} and caches that name

 * @param colIndex 0-based column index
 * @return column name
 */
public String getColumnName(Integer colIndex) {
  if (headersMap.containsKey(colIndex)) {
    return headersMap.get(colIndex);
  } else {
    final String columnName = CellReference.convertNumToColString(colIndex);
    return setColumnName(colIndex, columnName);
  }
}
 

public String toString() {
	StringBuffer buffer = new StringBuffer();
	buffer.append("[TABLE]\n");
	buffer.append("    .range    = ").append(getRange()).append("\n");
	buffer.append("    .flags    = ") .append(HexDump.byteToHex(field_5_flags)).append("\n");
	buffer.append("    .alwaysClc= ").append(isAlwaysCalc()).append("\n");
	buffer.append("    .reserved = ").append(HexDump.intToHex(field_6_res)).append("\n");
	CellReference crRowInput = cr(field_7_rowInputRow, field_8_colInputRow);
	CellReference crColInput = cr(field_9_rowInputCol, field_10_colInputCol);
	buffer.append("    .rowInput = ").append(crRowInput.formatAsString()).append("\n");
	buffer.append("    .colInput = ").append(crColInput.formatAsString()).append("\n");
	buffer.append("[/TABLE]\n");
	return buffer.toString();
}
 

public void createCell(int columnIndex, double value, int styleIndex)
		throws IOException {
	String ref = new CellReference(_rownum, columnIndex)
			.formatAsString();
	_out.write("<c r=\"" + ref + "\" t=\"n\"");
	if (styleIndex != -1)
		_out.write(" s=\"" + styleIndex + "\"");
	_out.write(">");
	_out.write("<v>" + value + "</v>");
	_out.write("</c>");
}
 

/**
 * 插入新列
 * @param columnIndex
 * @param value
 * @param styleIndex
 * @throws IOException
 */
public void createCell(int columnIndex, String value, int styleIndex)
		throws IOException {
	String ref = new CellReference(_rownum, columnIndex)
			.formatAsString();
	_out.write("<c r=\"" + ref + "\" t=\"inlineStr\"");
	if (styleIndex != -1)
		_out.write(" s=\"" + styleIndex + "\"");
	_out.write(">");
	_out.write("<is><t>"+XMLEncoder.encode(value)+"</t></is>");
	_out.write("</c>");
}
 

public void createCell(int columnIndex, double value, int styleIndex)
		throws IOException {
	String ref = new CellReference(_rownum, columnIndex)
			.formatAsString();
	_out.write("<c r=\"" + ref + "\" t=\"n\"");
	if (styleIndex != -1)
		_out.write(" s=\"" + styleIndex + "\"");
	_out.write(">");
	_out.write("<v>" + value + "</v>");
	_out.write("</c>");
}
 

/**
 * 插入新列
 * @param columnIndex
 * @param value
 * @param styleIndex
 * @throws IOException
 */
public void createCell(int columnIndex, String value, int styleIndex)
		throws IOException {
	String ref = new CellReference(_rownum, columnIndex)
			.formatAsString();
	_out.write("<c r=\"" + ref + "\" t=\"inlineStr\"");
	if (styleIndex != -1)
		_out.write(" s=\"" + styleIndex + "\"");
	_out.write(">");
	_out.write("<is><t>"+XMLEncoder.encode(value)+"</t></is>");
	_out.write("</c>");
}
 

/**
 * 插入新列
 * 
 * @param columnIndex
 * @param value
 * @param styleIndex
 * @throws IOException
 */
public void createCell(int columnIndex, String value, int styleIndex)
		throws IOException {
	String ref = new CellReference(_rownum, columnIndex)
			.formatAsString();
	_out.write("<c r=\"" + ref + "\" t=\"inlineStr\"");
	if (styleIndex != -1) {
		_out.write(" s=\"" + styleIndex + "\"");
	}
	_out.write(">");
	_out.write("<is><t>" + encoderXML(value) + "</t></is>");
	_out.write("</c>");
}
 

/**
 * 插入新列
 * @param columnIndex
 * @param value
 * @param styleIndex
 * @throws IOException
 */
public void createCell(int columnIndex, String value, int styleIndex)
		throws IOException {
	String ref = new CellReference(_rownum, columnIndex)
			.formatAsString();
	_out.write( "<c r=\"" + ref + "\" t=\"inlineStr\"" );
	if (styleIndex != -1)
		_out.write( " s=\"" + styleIndex + "\"" );
	_out.write( ">" );
	_out.write( "<is><t>"+XMLEncoder.encode(value)+"</t></is>" );
	_out.write( "</c>" );
}
 

public void createCell(int columnIndex, double value, int styleIndex)
		throws IOException {
	String ref = new CellReference(_rownum, columnIndex)
			.formatAsString();
	_out.write( "<c r=\"" + ref + "\" t=\"n\"" );
	if (styleIndex != -1)
		_out.write( " s=\"" + styleIndex + "\"" );
	_out.write( ">" );
	_out.write( "<v>" + value + "</v>" );
	_out.write( "</c>" );
}
 

/**
 * 插入新列
 * @param columnIndex
 * @param value
 * @param styleIndex
 * @throws IOException
 */
public void createCell(int columnIndex, String value, int styleIndex)
		throws IOException {
	String ref = new CellReference(_rownum, columnIndex)
			.formatAsString();
	_out.write( "<c r=\"" + ref + "\" t=\"inlineStr\"" );
	if (styleIndex != -1)
		_out.write( " s=\"" + styleIndex + "\"" );
	_out.write( ">" );
	_out.write( "<is><t>"+XMLEncoder.encode(value)+"</t></is>" );
	_out.write( "</c>" );
}
 

public void createCell(int columnIndex, double value, int styleIndex)
		throws IOException {
	String ref = new CellReference(_rownum, columnIndex)
			.formatAsString();
	_out.write( "<c r=\"" + ref + "\" t=\"n\"" );
	if (styleIndex != -1)
		_out.write( " s=\"" + styleIndex + "\"" );
	_out.write( ">" );
	_out.write( "<v>" + value + "</v>" );
	_out.write( "</c>" );
}
 

/**
 * 插入新列
 * @param columnIndex
 * @param value
 * @param styleIndex
 * @throws IOException
 */
public void createCell(int columnIndex, String value, int styleIndex)
		throws IOException {
	String ref = new CellReference(_rownum, columnIndex)
			.formatAsString();
	_out.write( "<c r=\"" + ref + "\" t=\"inlineStr\"" );
	if (styleIndex != -1)
		_out.write( " s=\"" + styleIndex + "\"" );
	_out.write( ">" );
	_out.write( "<is><t>"+XMLEncoder.encode(value)+"</t></is>" );
	_out.write( "</c>" );
}
 

public void createCell(int columnIndex, double value, int styleIndex)
		throws IOException {
	String ref = new CellReference(_rownum, columnIndex)
			.formatAsString();
	_out.write( "<c r=\"" + ref + "\" t=\"n\"" );
	if (styleIndex != -1)
		_out.write( " s=\"" + styleIndex + "\"" );
	_out.write( ">" );
	_out.write( "<v>" + value + "</v>" );
	_out.write( "</c>" );
}
 

/**
 * 插入新列
 * @param columnIndex
 * @param value
 * @param styleIndex
 * @throws IOException
 */
public void createCell(int columnIndex, String value, int styleIndex)
		throws IOException {
	String ref = new CellReference(_rownum, columnIndex)
			.formatAsString();
	_out.write( "<c r=\"" + ref + "\" t=\"inlineStr\"" );
	if (styleIndex != -1)
		_out.write( " s=\"" + styleIndex + "\"" );
	_out.write( ">" );
	_out.write( "<is><t>"+XMLEncoder.encode(value)+"</t></is>" );
	_out.write( "</c>" );
}
 

/**
 * 刷新图表和表格的数据.
 *
 * @param sheet      the sheet
 * @param category   the category
 * @param val        the val
 * @param seriesData the series data
 * @param colNum     the col num
 * @since nile -cmszbs-szcst 0.1.0
 */
private static void refreshSeriesData(Sheet sheet, CTSerTx tx, CTAxDataSource category, CTNumDataSource val,
                                      ChartSeries seriesData, int colNum) {
    //更新系列名称
    tx.getStrRef().getStrCache().getPtArray(0).setV(seriesData.getSeriesName());

    //绑定系列名为excel对应的列名
    String titleRef = new CellReference(sheet.getSheetName(), 0, colNum, true, true).formatAsString();
    tx.getStrRef().setF(titleRef);

    //类别数据
    CTStrData ctStrData = category.getStrRef().getStrCache();
    //系列值
    CTNumData ctNumData = val.getNumRef().getNumCache();

    ctStrData.setPtArray((CTStrVal[]) null);
    ctNumData.setPtArray((CTNumVal[]) null);

    //更新图表
    refreshCategoryData(ctStrData, ctNumData, seriesData);

    //更新Excel表格每一列的数据
    refreshExcelData(colNum, sheet, seriesData);

    //图数据绑定EXCEL表格数据
    ctNumData.getPtCount().setVal(seriesData.getChartCategoryList().size() - 1);
    ctStrData.getPtCount().setVal(seriesData.getChartCategoryList().size() - 1);

    String numDataRange = new CellRangeAddress(1, seriesData.getChartCategoryList().size(), colNum, colNum)
            .formatAsString(sheet.getSheetName(), true);
    val.getNumRef().setF(numDataRange);
    String axisDataRange = new CellRangeAddress(1, seriesData.getChartCategoryList().size(), 0, 0)
            .formatAsString(sheet.getSheetName(), true);
    category.getStrRef().setF(axisDataRange);
}
 

public void createCell(int columnIndex, double value, int styleIndex)
		throws IOException {
	String ref = new CellReference(_rownum, columnIndex)
			.formatAsString();
	_out.write("<c r=\"" + ref + "\" t=\"n\"");
	if (styleIndex != -1) {
		_out.write(" s=\"" + styleIndex + "\"");
	}
	_out.write(">");
	_out.write("<v>" + value + "</v>");
	_out.write("</c>");
}
 

private static CellReference cr(int rowIx, int colIxAndFlags) {
	int colIx = colIxAndFlags & 0x00FF;
	boolean isRowAbs = (colIxAndFlags & 0x8000) == 0;
	boolean isColAbs = (colIxAndFlags & 0x4000) == 0;
	return new CellReference(rowIx, colIx, isRowAbs, isColAbs);
}
 

@Override
public void cell(String cellReference, final String formattedValue,
    final XSSFComment xssfComment) {
  if (java.util.Objects.isNull(cur)) return;
  // Gracefully handle missing CellRef here in a similar way as XSSFCell does
  if(cellReference == null) {
    cellReference = new CellAddress(currentRow, currentCol).formatAsString();
  }
  int column = new CellReference(cellReference).getCol();
  currentCol = column;
  if (COL_0 == column) {
    assertColumnName("ID", formattedValue);
    //Handle any exceptions thrown by the converter - this stops execution of the whole process;
    try {
      cur.setId(new com.creditdatamw.zerocell.example.IdPrefixingConverter().convert(formattedValue, "ID", currentRow));
    } catch(Exception e) {
      throw new ZeroCellException("com.creditdatamw.zerocell.example.IdPrefixingConverter threw an exception while trying to convert value " + formattedValue, e);
    }
    return;
  }
  if (COL_1 == column) {
    assertColumnName("FIRST_NAME", formattedValue);
    cur.setFirstName(noop.convert(formattedValue, "FIRST_NAME", currentRow));
    return;
  }
  if (COL_2 == column) {
    assertColumnName("MIDDLE_NAME", formattedValue);
    cur.setMiddleName(noop.convert(formattedValue, "MIDDLE_NAME", currentRow));
    return;
  }
  if (COL_3 == column) {
    assertColumnName("LAST_NAME", formattedValue);
    cur.setLastName(noop.convert(formattedValue, "LAST_NAME", currentRow));
    return;
  }
  if (COL_4 == column) {
    assertColumnName("DATE_OF_BIRTH", formattedValue);
    cur.setDateOfBirth(toLocalDate.convert(formattedValue, "DATE_OF_BIRTH", currentRow));
    return;
  }
  if (COL_6 == column) {
    assertColumnName("DATE_REGISTERED", formattedValue);
    cur.setDateOfRegistration(toLocalDate.convert(formattedValue, "DATE_REGISTERED", currentRow));
    return;
  }
  if (COL_5 == column) {
    assertColumnName("FAV_NUMBER", formattedValue);
    cur.setFavouriteNumber(toInteger.convert(formattedValue, "FAV_NUMBER", currentRow));
    return;
  }
}
 

@Test
public void testc() throws Exception {
    LOGGER.info("reslut:{}", JSON.toJSONString(new CellReference("B3")));
}