org.apache.poi.ss.usermodel.BuiltinFormats Java Examples

/**
 * @param cellStyleId
 *            the cell style ID stored within the XLSX worksheet cell tag. <code>null</code> is
 *            allowed and will return <code>false</code>
 * @return <code>true</code> in case it is a date, <code>false</code> otherwise
 */
public boolean isDateFormatStyle(String cellStyleId) {
	if (cellStyleId == null) {
		return false;
	}

	/*
	 * Cell styles references are stored within the cell tag as a 0 based index of the cell
	 * formats.
	 */
	int numberFormatId = cellNumberFormatIds[Integer.parseInt(cellStyleId)];
	Boolean isNumberFormat = isDateFormatCache.get(numberFormatId);
	if (isNumberFormat == null) {
		// Check builtin formats if custom date format cache does not contain a hit
		String builtinFormat = BuiltinFormats.getBuiltinFormat(numberFormatId);
		if (builtinFormat != null) {
			isNumberFormat = checkForDateFormat(numberFormatId, builtinFormat);
			isDateFormatCache.put(numberFormatId, isNumberFormat);
		} else {
			// It is neither a custom nor a a built-in format -> probably not a date format
			isNumberFormat = false;
		}
	}
	return isNumberFormat;
}
 

/**
 * Read the numeric format string out of the styles table for this cell. Stores the result in the Cell.
 *
 * @param startElement
 * @param cell
 */
void setFormatString(StartElement startElement, StreamingCell cell) {
    Attribute cellStyle = startElement.getAttributeByName(new QName("s"));
    String cellStyleString = (cellStyle != null) ? cellStyle.getValue() : null;
    XSSFCellStyle style = null;

    if (cellStyleString != null) {
        style = stylesTable.getStyleAt(Integer.parseInt(cellStyleString));
    } else if (stylesTable.getNumCellStyles() > 0) {
        style = stylesTable.getStyleAt(0);
    }

    if (style != null) {
        cell.setNumericFormatIndex(style.getDataFormat());
        String formatString = style.getDataFormatString();

        if (formatString != null) {
            cell.setNumericFormat(formatString);
        } else {
            cell.setNumericFormat(BuiltinFormats.getBuiltinFormat(cell.getNumericFormatIndex()));
        }
    } else {
        cell.setNumericFormatIndex(null);
        cell.setNumericFormat(null);
    }
}
 

/**
 * Read the numeric format string out of the styles table for this cell. Stores the result in the Cell.
 *
 * @param startElement
 * @param cell
 */
void setFormatString(StartElement startElement, StreamingCell cell) {
    Attribute cellStyle = startElement.getAttributeByName(new QName("s"));
    String cellStyleString = (cellStyle != null) ? cellStyle.getValue() : null;
    XSSFCellStyle style = null;

    if (cellStyleString != null) {
        style = stylesTable.getStyleAt(Integer.parseInt(cellStyleString));
    } else if (stylesTable.getNumCellStyles() > 0) {
        style = stylesTable.getStyleAt(0);
    }

    if (style != null) {
        cell.setNumericFormatIndex(style.getDataFormat());
        String formatString = style.getDataFormatString();

        if (formatString != null) {
            cell.setNumericFormat(formatString);
        } else {
            cell.setNumericFormat(BuiltinFormats.getBuiltinFormat(cell.getNumericFormatIndex()));
        }
    } else {
        cell.setNumericFormatIndex(null);
        cell.setNumericFormat(null);
    }
}
 

/**
 * Read the numeric format string out of the styles table for this cell. Stores
 * the result in the Cell.
 *
 * @param startElement
 * @param cell
 */
void setFormatString(StartElement startElement, StreamingCell cell) {
  Attribute cellStyle = startElement.getAttributeByName(new QName("s"));
  String cellStyleString = (cellStyle != null) ? cellStyle.getValue() : null;
  XSSFCellStyle style = null;

  if(cellStyleString != null) {
    style = stylesTable.getStyleAt(Integer.parseInt(cellStyleString));
  } else if(stylesTable.getNumCellStyles() > 0) {
    style = stylesTable.getStyleAt(0);
  }

  if(style != null) {
    cell.setNumericFormatIndex(style.getDataFormat());
    String formatString = style.getDataFormatString();

    if(formatString != null) {
      cell.setNumericFormat(formatString);
    } else {
      cell.setNumericFormat(BuiltinFormats.getBuiltinFormat(cell.getNumericFormatIndex()));
    }
  } else {
    cell.setNumericFormatIndex(null);
    cell.setNumericFormat(null);
  }
}
 

/**
 * 处理数据类型
 * 
 * @param attributes
 */
private void setNextDataType(Attributes attributes) {
	nextDataType = CellDataType.NUMBER;
	formatIndex = -1;
	formatString = null;
	String cellType = attributes.getValue("t");
	String cellStyleStr = attributes.getValue("s");
	String columData = attributes.getValue("r");

	if ("b".equals(cellType)) {
		nextDataType = CellDataType.BOOL;
	} else if ("e".equals(cellType)) {
		nextDataType = CellDataType.ERROR;
	} else if ("inlineStr".equals(cellType)) {
		nextDataType = CellDataType.INLINESTR;
	} else if ("s".equals(cellType)) {
		nextDataType = CellDataType.SSTINDEX;
	} else if ("str".equals(cellType)) {
		nextDataType = CellDataType.FORMULA;
	}

	if (cellStyleStr != null) {
		int styleIndex = Integer.parseInt(cellStyleStr);
		XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
		formatIndex = style.getDataFormat();
		formatString = style.getDataFormatString();

		if ("m/d/yy" == formatString) {
			nextDataType = CellDataType.DATE;
			formatString = "yyyy-MM-dd hh:mm:ss.SSS";
		}

		if (formatString == null) {
			nextDataType = CellDataType.NULL;
			formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
		}
	}
}
 

@Test
public void testBuiltinFormats() throws Exception {
    System.out.println(BuiltinFormats.getBuiltinFormat(48));
    System.out.println(BuiltinFormats.getBuiltinFormat(57));
    System.out.println(BuiltinFormats.getBuiltinFormat(28));

}
 

/**
 * Creates a FormatRecord object
 * @param id    the number of the format record to create (meaning its position in
 *        a file as M$ Excel would create it.)
 */
private static FormatRecord createFormat(int id) {
    // we'll need multiple editions for the different formats
    final int mappings[] = { 5, 6, 7, 8, 0x2a, 0x29, 0x2c, 0x2b };
    if (id < 0 || id >= mappings.length) {
        throw new  IllegalArgumentException("Unexpected id " + id);
    }
    return new FormatRecord(mappings[id], BuiltinFormats.getBuiltinFormat(mappings[id]));
}
 

public void startElement(String uri, String localName, String name,
                         Attributes attributes) throws SAXException {

    if ("inlineStr".equals(name) || "v".equals(name)) {
        vIsOpen = true;
        // Clear contents cache
        value.setLength(0);
    }
    // c => cell
    else if ("c".equals(name)) {
        // Get the cell reference
        String r = attributes.getValue("r");
        int firstDigit = -1;
        for (int c = 0; c < r.length(); ++c) {
            if (Character.isDigit(r.charAt(c))) {
                firstDigit = c;
                break;
            }
        }
        thisColumn = nameToColumn(r.substring(0, firstDigit));

        // Set up defaults.
        this.nextDataType = xssfDataType.NUMBER;
        this.formatIndex = -1;
        this.formatString = null;
        String cellType = attributes.getValue("t");
        String cellStyleStr = attributes.getValue("s");
        if ("b".equals(cellType))
            nextDataType = xssfDataType.BOOL;
        else if ("e".equals(cellType))
            nextDataType = xssfDataType.ERROR;
        else if ("inlineStr".equals(cellType))
            nextDataType = xssfDataType.INLINESTR;
        else if ("s".equals(cellType))
            nextDataType = xssfDataType.SSTINDEX;
        else if ("str".equals(cellType))
            nextDataType = xssfDataType.FORMULA;
        else if (cellStyleStr != null) {
            // It's a number, but almost certainly one
            // with a special style or format
            int styleIndex = Integer.parseInt(cellStyleStr);
            XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
            this.formatIndex = style.getDataFormat();
            this.formatString = style.getDataFormatString();
            if (this.formatString == null)
                this.formatString = BuiltinFormats
                        .getBuiltinFormat(this.formatIndex);
        }
    }

}
 

protected short getBuiltinFormat (String formatStr) {
	short format = (short) BuiltinFormats.getBuiltinFormat(formatStr); 
	return format;
}
 

public void startElement(String uri, String localName, String name,
        Attributes attributes) throws SAXException {

    if ("inlineStr".equals(name) || "v".equals(name)) {
    	context.setvIsOpen(true);
        context.clearContents();
    } else if ("c".equals(name)) {
        String r = attributes.getValue("r");
        int firstDigit = -1;
        for (int c = 0; c < r.length(); ++c) {
            if (Character.isDigit(r.charAt(c))) {
                firstDigit = c;
                break;
            }
        }
        int col = nameToColumn(r.substring(0, firstDigit));
        int row = Integer.parseInt(r.substring(firstDigit));
        context.setCurrentCell(new Cell(row, col));
        context.setNextDataType(XSSFDataType.NUMBER);
        context.setFormatIndex((short) -1);
        context.setFormatString(null);

        String cellType = attributes.getValue("t");
        String cellStyleStr = attributes.getValue("s");
        if ("b".equals(cellType))
            context.setNextDataType(XSSFDataType.BOOL);
        else if ("e".equals(cellType))
            context.setNextDataType(XSSFDataType.ERROR);
        else if ("inlineStr".equals(cellType))
        	context.setNextDataType(XSSFDataType.INLINESTR);
        else if ("s".equals(cellType))
        	context.setNextDataType(XSSFDataType.SSTINDEX);
        else if ("str".equals(cellType))
        	context.setNextDataType(XSSFDataType.FORMULA);
        else if (cellStyleStr != null) {
            int styleIndex = Integer.parseInt(cellStyleStr);
            XSSFCellStyle style = context.getStyles().getStyleAt(styleIndex);
            context.setFormatIndex(style.getDataFormat());
            String formatString = style.getDataFormatString();
            if (formatString == null) {
            	formatString = BuiltinFormats.getBuiltinFormat(context.getFormatIndex());
            }
            context.setFormatString(formatString);
        }
    }

}
 

public void startElement(String uri, String localName, String name,
        Attributes attributes) throws SAXException {

    if ("inlineStr".equals(name) || "v".equals(name)) {
        vIsOpen = true;
        // Clear contents cache
        value.setLength(0);
    }
    // c => cell
    else if ("c".equals(name)) {
        // Get the cell reference
        String r = attributes.getValue("r");
        int firstDigit = -1;
        for (int c = 0; c < r.length(); ++c) {
            if (Character.isDigit(r.charAt(c))) {
                firstDigit = c;
                break;
            }
        }
        thisColumn = nameToColumn(r.substring(0, firstDigit));

        // Set up defaults.
        this.nextDataType = xssfDataType.NUMBER;
        this.formatIndex = -1;
        this.formatString = null;
        String cellType = attributes.getValue("t");
        String cellStyleStr = attributes.getValue("s");
        if ("b".equals(cellType))
            nextDataType = xssfDataType.BOOL;
        else if ("e".equals(cellType))
            nextDataType = xssfDataType.ERROR;
        else if ("inlineStr".equals(cellType))
            nextDataType = xssfDataType.INLINESTR;
        else if ("s".equals(cellType))
            nextDataType = xssfDataType.SSTINDEX;
        else if ("str".equals(cellType))
            nextDataType = xssfDataType.FORMULA;
        else if (cellStyleStr != null) {
            // It's a number, but almost certainly one
            // with a special style or format
            int styleIndex = Integer.parseInt(cellStyleStr);
            XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
            this.formatIndex = style.getDataFormat();
            this.formatString = style.getDataFormatString();
            if (this.formatString == null)
                this.formatString = BuiltinFormats
                        .getBuiltinFormat(this.formatIndex);
        }
    }

}
 

private Map<String, CellStyle> createStyles(Workbook wb){
    Map<String, CellStyle> styles = new HashMap<>();
    CellStyle style;

    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short)12);
    titleFont.setBold(true);
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFillForegroundColor( IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setFont(titleFont);
    style.setWrapText(false);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
    styles.put("header", style);

    Font cellFont = wb.createFont();
    cellFont.setFontHeightInPoints((short)10);
    cellFont.setBold(true);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.RIGHT);
    style.setVerticalAlignment(VerticalAlignment.BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat().getFormat( BuiltinFormats.getBuiltinFormat( 3 )));
    styles.put("integer_number_cell", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.RIGHT);
    style.setVerticalAlignment(VerticalAlignment.BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(4)));
    styles.put("decimal_number_cell", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat( (short) BuiltinFormats.getBuiltinFormat("text") );
    styles.put(TEXT_CELL, style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat().getFormat( DateFormatConverter.convert( Locale.getDefault(), dateFormatPattern )));
    styles.put("date_cell", style);
    return styles;
}
 

public void startElement(String uri, String localName, String qName,
		Attributes attributes) throws SAXException {
	if (qName.equals("c")) {
		String vCellType = attributes.getValue("t");
		String cellS = attributes.getValue("s");
		if ("b".equals(vCellType))
			cellDataType = cDataType.BOOL;
		else if ("e".equals(vCellType))
			cellDataType = cDataType.FORMULA;
		else if ("s".equals(vCellType))
			cellDataType = cDataType.SSTINDEX;
		else if("str".equals(vCellType))
			cellDataType =  cDataType.STATIC;
        else if (cellS != null) {
              //number with formatting or date
            int styleIndex = Integer.parseInt(cellS);
            XSSFCellStyle style = st.getStyleAt(styleIndex);
            short formatIndex = style.getDataFormat();
            String formatString = style.getDataFormatString();

            if (formatString == null)
                   formatString = BuiltinFormats.getBuiltinFormat(formatIndex);

			if (org.apache.poi.ss.usermodel.DateUtil.isADateFormat(
					formatIndex, formatString) ) {
            	cellDataType =  cDataType.DATETIME;
            }else{
            	cellDataType = cDataType.NUMBER;
            }
        }
		else
			cellDataType = cDataType.NUMBER;

              String r = attributes.getValue("r");

              currentColumn = getColumnNumber( r );
              //expand the number of columns if needed in existing rows
              if( currentColumn+1 > columnCount){
              	callback.columnExpansion(currentColumn+1);
              	
              	//clean up current row
              	int newvals = (currentColumn+1) - columnCount;
              	for( int ii=0; ii<newvals;ii++){
              		values.add(ExcelODAConstants.EMPTY_STRING);
              	}               	
              	
          		columnCount = currentColumn+1;
              }

	}

	//empty cells are not in the xml so we have
	//create them in the row
	if (qName.equals("row")) {
		for( int i=0;i<columnCount; i++){
			values.add(i, ExcelODAConstants.EMPTY_STRING);
		}
	}
	lastContents = ExcelODAConstants.EMPTY_STRING;
}
 

@Override
public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {

    super.startElement(uri, localName, qName, attributes);

    if (this.cellFormat == null) {
        return;
    }
    if (uri != null && !uri.equals(NS_SPREADSHEETML)) {
        return;
    }

    if ("c".equals(localName)) {
        // Set up defaults.
        String cellRef = attributes.getValue("r");
        String cellType = attributes.getValue("t");
        String cellStyleStr = attributes.getValue("s");
        CellAddress cellAddress = new CellAddress(cellRef);
        if ("b".equals(cellType) || "e".equals(cellType) || "inlineStr".equals(cellType)) {
            this.cellFormat.addFormat(cellAddress, (short) 0, null, cellType, cellStyleStr);
            return;
        }
        if ("s".equals(cellType) || "str".equals(cellType)) {
            this.cellFormat.addFormat(cellAddress, (short) 0, null, cellType, cellStyleStr);
            return;
        }
        // Number, but almost certainly with a special style or format
        XSSFCellStyle style = null;
        if (stylesTable != null) {
            if (cellStyleStr != null) {
                int styleIndex = Integer.parseInt(cellStyleStr);
                style = stylesTable.getStyleAt(styleIndex);
            } else if (stylesTable.getNumCellStyles() > 0) {
                style = stylesTable.getStyleAt(0);
            }
        }
        if (style != null) {
            short formatIndex = style.getDataFormat();
            String formatString = style.getDataFormatString();
            if (formatString == null)
                formatString = BuiltinFormats.getBuiltinFormat(formatIndex);

            this.cellFormat.addFormat(cellAddress, formatIndex, formatString, cellType, cellStyleStr);
        }

    }
}
 

/**
 * get the format index that matches the given format string<p>
 * Automatically converts "text" to excel's format string to represent text.
 * @param format string matching a built in format
 * @return index of format or -1 if undefined.
 */
public static short getBuiltinFormat(String format) {
	return (short) BuiltinFormats.getBuiltinFormat(format);
}
 

/**
 * get the format string that matches the given format index
 * @param index of a built in format
 * @return string represented at index of format or null if there is not a builtin format at that index
 */
public static String getBuiltinFormat(short index) {
	return BuiltinFormats.getBuiltinFormat(index);
}