Java Code Examples for org.apache.poi.ss.usermodel.CreationHelper#createDataFormat()

The following examples show how to use org.apache.poi.ss.usermodel.CreationHelper#createDataFormat() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: QbeXLSExporter.java    From Knowage-Server with GNU Affero General Public License v3.0 6 votes vote down vote up 
private CellStyle getDecimalNumberFormat(int j, Sheet sheet, CreationHelper createHelper, CellStyle dCellStyle) {

		if (decimalFormats.get(j) != null)
			return decimalFormats.get(j);
		String decimals = "";
		for (int i = 0; i < j; i++) {
			decimals += "0";
		}

		CellStyle cellStyleDoub = this.buildCellStyle(sheet); // cellStyleDoub is the default cell style for doubles
		cellStyleDoub.cloneStyleFrom(dCellStyle);
		DataFormat df = createHelper.createDataFormat();
		String format = "#,##0";
		if (decimals.length() > 0) {
			format += "." + decimals;
		}
		cellStyleDoub.setDataFormat(df.getFormat(format));

		decimalFormats.put(j, cellStyleDoub);
		return cellStyleDoub;
	}
Example 2
Source File: ExcelTestHelper.java    From dremio-oss with Apache License 2.0 5 votes vote down vote up 
ExcelTestHelper(final String parent, boolean generateXls) throws Exception {
  this.xls = generateXls;

  // Create a test Excel sheet with all types of supported data
  Workbook wb = generateXls ? new HSSFWorkbook() : new XSSFWorkbook();

  CreationHelper creationHelper = wb.getCreationHelper();
  DataFormat dataFormat = creationHelper.createDataFormat();
  short fmt = dataFormat.getFormat("yyyy-mm-dd hh:mm:ss");
  CellStyle style = wb.createCellStyle();
  style.setDataFormat(fmt);

  Sheet sheetWithHeader = wb.createSheet("Sheet 1");

  // Create header row
  Row headerRow = sheetWithHeader.createRow((short) 0);
  headerRow.createCell(0).setCellValue("Number");
  headerRow.createCell(1).setCellValue("String1");
  headerRow.createCell(2).setCellValue("String2");
  headerRow.createCell(3).setCellValue("MyTime");
  headerRow.createCell(4).setCellValue("Formula");
  headerRow.createCell(5).setCellValue("Boolean");
  headerRow.createCell(6).setCellValue("Error");
  generateSheetData(sheetWithHeader, style, (short)1);

  Sheet sheetWithoutHeader = wb.createSheet("Sheet 2");
  generateSheetData(sheetWithoutHeader, style, (short)0);

  testFilePath = new File(parent, "excelTestFile").getPath();

  // Write the output to a file
  FileOutputStream fileOut = new FileOutputStream(testFilePath);
  wb.write(fileOut);
  fileOut.close();
}
Example 3
Source File: CrosstabXLSExporter.java    From Knowage-Server with GNU Affero General Public License v3.0 4 votes vote down vote up 
public CellStyle getNumberFormat(int j, Map<Integer, CellStyle> decimalFormats, Sheet sheet, CreationHelper createHelper, CellType celltype) {

		int mapPosition = j;

		if (celltype.equals(CellType.TOTAL)) {
			mapPosition = j + 90000;
		} else if (celltype.equals(CellType.SUBTOTAL)) {
			mapPosition = j + 80000;
		} else if (celltype.equals(CellType.CF)) {
			mapPosition = j + 60000;
		}

		if (decimalFormats.get(mapPosition) != null)
			return decimalFormats.get(mapPosition);

		if (celltype.equals(CellType.CF)) {
			j = this.getCalculatedFieldDecimals();
		}

		String decimals = "";

		for (int i = 0; i < j; i++) {
			decimals += "0";
		}

		CellStyle cellStyle = this.buildDataCellStyle(sheet);
		DataFormat df = createHelper.createDataFormat();
		String format = "#,##0";
		if (decimals.length() > 0) {
			format += "." + decimals;
		}
		cellStyle.setDataFormat(df.getFormat(format));

		if (celltype.equals(CellType.TOTAL)) {
			cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
		}
		if (celltype.equals(CellType.CF)) {
			cellStyle.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex());
		}
		if (celltype.equals(CellType.SUBTOTAL)) {
			cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		}

		decimalFormats.put(mapPosition, cellStyle);
		return cellStyle;
	}