Java Code Examples for org.apache.poi.ss.usermodel.Cell

The following examples show how to use org.apache.poi.ss.usermodel.Cell. These examples are extracted from open source projects. 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 Project: tools   Source File: DocumentSheet.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * @param comparer
 * @throws SpdxCompareException 
 * @throws InvalidSPDXAnalysisException 
 */
private void importLicenseListVersions(SpdxComparer comparer) throws SpdxCompareException, InvalidSPDXAnalysisException {
	Cell cell = sheet.getRow(getFirstDataRow()).createCell(LICENSE_LIST_VERSION_COL);
	if (comparer.isLicenseListVersionEqual()) {
		setCellEqualValue(cell);
	} else {
		setCellDifferentValue(cell);
	}
	// data rows
	for (int i = 0; i < comparer.getNumSpdxDocs(); i++) {
		cell = sheet.getRow(getFirstDataRow()+i+1).createCell(LICENSE_LIST_VERSION_COL);
		if (comparer.getSpdxDoc(i).getCreationInfo().getLicenseListVersion() != null) {
			cell.setCellValue(comparer.getSpdxDoc(i).getCreationInfo().getLicenseListVersion());
		}
	}
}
 
Example 2
Source Project: nimrod   Source File: DictionaryServiceImpl.java    License: MIT License 6 votes vote down vote up
private List<Map<Integer, Cell>> uploadAndReadExcel(MultipartFile multipartFile) throws IOException {
    List<Map<Integer, Cell>> list = new ArrayList<>();
    Workbook workbook = ExcelUtil.getWorkbook(Objects.requireNonNull(multipartFile.getOriginalFilename()), multipartFile.getInputStream());
    if (workbook != null) {
        Sheet sheet = workbook.getSheetAt(0);
        int rowIndex;
        for (rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            int cellIndex;
            Map<Integer, Cell> map = new HashMap<>(1);
            for (cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                map.put(cellIndex, row.getCell(cellIndex));
            }
            list.add(map);
        }
    }
    return list.isEmpty() ? null : list;
}
 
Example 3
public void testExcelExport() throws Exception {
  final MasterReport report = DebugReportRunner.parseGoldenSampleReport( "Prd-3625.prpt" );

  final ByteArrayOutputStream bout = new ByteArrayOutputStream();
  ExcelReportUtil.createXLS( report, bout );

  final HSSFWorkbook wb = new HSSFWorkbook( new ByteArrayInputStream( bout.toByteArray() ) );
  final HSSFSheet sheetAt = wb.getSheetAt( 0 );
  final HSSFRow row = sheetAt.getRow( 0 );
  final HSSFCell cell0 = row.getCell( 0 );
  assertEquals( Cell.CELL_TYPE_NUMERIC, cell0.getCellType() );
  assertEquals( "yyyy-MM-dd", cell0.getCellStyle().getDataFormatString() );
  final HSSFCell cell1 = row.getCell( 1 );
  assertEquals( Cell.CELL_TYPE_NUMERIC, cell1.getCellType() );
  assertEquals( "#,###.00;(#,###.00)", cell1.getCellStyle().getDataFormatString() );
}
 
Example 4
Source Project: tools   Source File: PackageInfoSheetV2d1.java    License: Apache License 2.0 6 votes vote down vote up
public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		sheet.setDefaultColumnStyle(i, defaultStyle);
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 
Example 5
Source Project: xlsmapper   Source File: BaseCellConverter.java    License: Apache License 2.0 6 votes vote down vote up
@Override
public T toObject(final Cell cell) throws XlsMapperException {

    final ProcessCase processCase = ProcessCase.Load;
    final String formattedValue = Utils.trim(configuration.getCellFormatter().format(cell), trimmed);

    // デフォルト値の設定
    if(isEmptyCell(formattedValue, cell) && defaultValue.isPresent(processCase)) {
        return defaultValue.get(processCase);
    }

    // 数式のセルの場合、予め評価しておく
    final Cell evaluatedCell;
    if(cell.getCellTypeEnum().equals(CellType.FORMULA)) {
        final Workbook workbook = cell.getSheet().getWorkbook();
        final CreationHelper helper = workbook.getCreationHelper();
        final FormulaEvaluator evaluator = helper.createFormulaEvaluator();

        evaluatedCell = evaluator.evaluateInCell(cell);
    } else {
        evaluatedCell = cell;
    }

    return parseCell(evaluatedCell, formattedValue);
}
 
Example 6
@SuppressWarnings("unchecked")
@Override
protected boolean putContentInCell(E currentobject, Cell cell, String context) {
	DataObjectField<?, E> field = currentobject.payload.lookupSimpleFieldOnName(name);
	if (field == null)
		throw new RuntimeException("field " + name + " could not be looked-up on " + currentobject.getName());
	if (!(field instanceof DateDataObjectField))
		throw new RuntimeException("Expected field " + name
				+ " would be of type DateDataObjectField but in reality, it is " + field.getClass().toString());
	DateDataObjectField<E> datefield = (DateDataObjectField<E>) currentobject.payload.lookupSimpleFieldOnName(name);

	if (cellStyle == null)
		cellStyle = FlatFileExtractor.createDateStyle(cell.getSheet().getWorkbook(), this.formatasstring);

	cell.setCellValue((datefield.getValue()));
	cell.setCellStyle(cellStyle);
	return true;
}
 
Example 7
Source Project: base   Source File: ExcelExportUtil.java    License: MIT License 6 votes vote down vote up
private static void createRow(Sheet sheet, Map<String, Object> rowData, List<String> mapping,
		List<CellStyle> styles, int startRowNum) {
	Row row = sheet.createRow(startRowNum);
	for (int i = 0; i < mapping.size(); i++) {
		String name = mapping.get(i);
		Object obj = rowData.get(name);
		Cell newCell = row.createCell(i);
		CellStyle style = styles.get(i);
		newCell.setCellStyle(style);
		if (obj != null) {
			if (obj instanceof Date) {
				newCell.setCellValue((Date) obj);
			} else if (obj instanceof BigDecimal) {
				double dd = ((BigDecimal) obj).doubleValue();
				newCell.setCellValue(dd);
			} else {
				newCell.setCellValue(obj.toString());
			}
		}
	}
}
 
Example 8
Source Project: tools   Source File: ReviewersSheet.java    License: Apache License 2.0 6 votes vote down vote up
public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
	CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		if (LEFT_WRAP[i]) {
			sheet.setDefaultColumnStyle(i, wrapStyle);
		} else if (CENTER_NOWRAP[i]) {
			sheet.setDefaultColumnStyle(i, centerStyle);
		}
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 
Example 9
Source Project: tools   Source File: PerFileSheetV1d1.java    License: Apache License 2.0 6 votes vote down vote up
public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);	
	CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
	CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		if (LEFT_WRAP[i]) {
			sheet.setDefaultColumnStyle(i, wrapStyle);
		} else if (CENTER_NOWRAP[i]) {
			sheet.setDefaultColumnStyle(i, centerStyle);
		}
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 
Example 10
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
    if (isHead) {
        return cell.getStringCellValue().getBytes().length;
    }
    CellData cellData = cellDataList.get(0);
    CellDataTypeEnum type = cellData.getType();
    if (type == null) {
        return -1;
    }
    switch (type) {
        case STRING:
            return cellData.getStringValue().getBytes().length;
        case BOOLEAN:
            return cellData.getBooleanValue().toString().getBytes().length;
        case NUMBER:
            return cellData.getNumberValue().toString().getBytes().length;
        default:
            return -1;
    }
}
 
Example 11
Source Project: jeasypoi   Source File: ExcelExportOfTemplateUtil.java    License: Apache License 2.0 6 votes vote down vote up
private void setForEeachCellValue(boolean isCreate, Row row, int columnIndex, Object t, List<ExcelTemplateParams> columns, Map<String, Object> map) throws Exception {
	for (int i = 0, max = columnIndex + columns.size(); i < max; i++) {
		if (row.getCell(i) == null)
			row.createCell(i);
	}
	for (int i = 0, max = columns.size(); i < max; i++) {
		boolean isNumber = false;
		String tempStr = new String(columns.get(i).getName());
		if (isNumber(tempStr)) {
			isNumber = true;
			tempStr = tempStr.replace(NUMBER_SYMBOL, "");
		}
		map.put(teplateParams.getTempParams(), t);
		String val = eval(tempStr, map).toString();
		if (isNumber && StringUtils.isNotEmpty(val)) {
			row.getCell(i + columnIndex).setCellValue(Double.parseDouble(val));
			row.getCell(i + columnIndex).setCellType(Cell.CELL_TYPE_NUMERIC);
		} else {
			row.getCell(i + columnIndex).setCellValue(val);
		}
		row.getCell(i + columnIndex).setCellStyle(columns.get(i).getCellStyle());
		tempCreateCellSet.add(row.getRowNum() + "_" + (i + columnIndex));
	}

}
 
Example 12
Source Project: excel-streaming-reader   Source File: PerformanceTest.java    License: Apache License 2.0 6 votes vote down vote up
public static void main(String[] args) throws IOException {
  for(int i = 0; i < 10; i++) {
    long start = System.currentTimeMillis();
    InputStream is = new FileInputStream(new File("/Users/thundermoose/Downloads/SampleXLSFile_6800kb.xlsx"));
    try(Workbook workbook = StreamingReader.builder()
        .rowCacheSize(100)    // number of rows to keep in memory (defaults to 10)
        .bufferSize(4096)     // buffer size to use when reading InputStream to file (defaults to 1024)
        .open(is)) {

      for(Row r : workbook.getSheet("test")) {
        for(Cell c : r) {
          //do nothing
        }
      }
    }

    long end = System.currentTimeMillis();
    System.out.println("Time: " + (end - start) + "ms");
  }
}
 
Example 13
Source Project: tools   Source File: PerFileSheet.java    License: Apache License 2.0 6 votes vote down vote up
public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
	CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		if (LEFT_WRAP[i]) {
			sheet.setDefaultColumnStyle(i, wrapStyle);
		} else if (CENTER_NOWRAP[i]) {
			sheet.setDefaultColumnStyle(i, centerStyle);
		}
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 
Example 14
@Override
public String formatCellValue(Cell cell, FormulaEvaluator evaluator) {

    if (cell == null) {
        return "";
    }

    int cellType = cell.getCellType();
    if (cellType == Cell.CELL_TYPE_FORMULA) {
        if (evaluator == null) {
            return cell.getCellFormula();
        }
        cellType = evaluator.evaluateFormulaCell(cell);
    }
    switch (cellType) {
        case Cell.CELL_TYPE_NUMERIC :
            return getFormattedNumberString(cell);
        case Cell.CELL_TYPE_STRING :
            return cell.getRichStringCellValue().getString();
        case Cell.CELL_TYPE_BOOLEAN :
            return String.valueOf(cell.getBooleanCellValue());
        case Cell.CELL_TYPE_BLANK :
            return "";
    }
    throw new RuntimeException("Unexpected celltype (" + cellType + ")");
}
 
Example 15
Source Project: autopoi   Source File: CellValueServer.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 获取cell的值
 * 
 * @param object
 * @param excelParams
 * @param cell
 * @param titleString
 */
public Object getValue(IExcelDataHandler dataHanlder, Object object, Cell cell, Map<String, ExcelImportEntity> excelParams, String titleString) throws Exception {
	ExcelImportEntity entity = excelParams.get(titleString);
	String xclass = "class java.lang.Object";
	if (!(object instanceof Map)) {
		Method setMethod = entity.getMethods() != null && entity.getMethods().size() > 0 ? entity.getMethods().get(entity.getMethods().size() - 1) : entity.getMethod();
		Type[] ts = setMethod.getGenericParameterTypes();
		xclass = ts[0].toString();
	}
	Object result = getCellValue(xclass, cell, entity);
	if (entity != null) {
		result = hanlderSuffix(entity.getSuffix(), result);
		//update-begin-author:taoYan date:20180807 for:多值替换
		result = replaceValue(entity.getReplace(), result,entity.isMultiReplace());
		//update-end-author:taoYan date:20180807 for:多值替换
	}
	result = hanlderValue(dataHanlder, object, result, titleString);
	return getValueByType(xclass, result, entity);
}
 
Example 16
Source Project: xlsmapper   Source File: CellFinderTest.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * セルが見つからない場合
 */
@Test
public void testFind_whenNotFoundException() {
    
    {
        Cell cell = CellFinder.query(sheet, "テスト", config).findWhenNotFoundException();
        
        assertThat(cell).isNotNull();
        assertThat(CellPosition.of(cell).toString()).isEqualTo("B4");
        assertThat(getCellContents(cell)).isEqualTo("テスト");
    }
    
    {
        // セルが見つからない場合
        assertThatThrownBy(() -> CellFinder.query(sheet, "あいう", config).findWhenNotFoundException())
            .isInstanceOf(CellNotFoundException.class);
    }
    
}
 
Example 17
Source Project: lams   Source File: BaseFormulaEvaluator.java    License: GNU General Public License v2.0 6 votes vote down vote up
protected static void setCellType(Cell cell, CellValue cv) {
    CellType cellType = cv.getCellTypeEnum();
    switch (cellType) {
        case BOOLEAN:
        case ERROR:
        case NUMERIC:
        case STRING:
            cell.setCellType(cellType);
            return;
        case BLANK:
            // never happens - blanks eventually get translated to zero
            throw new IllegalArgumentException("This should never happen. Blanks eventually get translated to zero.");
        case FORMULA:
            // this will never happen, we have already evaluated the formula
            throw new IllegalArgumentException("This should never happen. Formulas should have already been evaluated.");
        default:
            throw new IllegalStateException("Unexpected cell value type (" + cellType + ")");
    }
}
 
Example 18
Source Project: components   Source File: Excel2007FileRecordReader.java    License: Apache License 2.0 6 votes vote down vote up
private int getCellNumber(Row headerRow) {
  Map<Integer, Cell> cellMap = ((StreamingRow)headerRow).getCellMap();
  
  int max = 0;
  boolean emptyRow = true;
  for(Map.Entry<Integer, Cell> entry : cellMap.entrySet()) {
    Integer key = entry.getKey();
    if(key != null) {
      emptyRow = false;
      max = Math.max(max, key);
    }
  }
  
  if(emptyRow) {
    return 0;
  }
  
  return max + 1;
}
 
Example 19
Source Project: lams   Source File: PropertyTemplate.java    License: GNU General Public License v2.0 6 votes vote down vote up
/**
 * Applies the drawn borders to a Sheet. The borders that are applied are
 * the ones that have been drawn by the {@link #drawBorders} and
 * {@link #drawBorderColors} methods.
 *
 * @param sheet
 *            - {@link Sheet} on which to apply borders
 */
public void applyBorders(Sheet sheet) {
    Workbook wb = sheet.getWorkbook();
    for (Map.Entry<CellAddress, Map<String, Object>> entry : _propertyTemplate
            .entrySet()) {
        CellAddress cellAddress = entry.getKey();
        if (cellAddress.getRow() < wb.getSpreadsheetVersion().getMaxRows()
                && cellAddress.getColumn() < wb.getSpreadsheetVersion()
                        .getMaxColumns()) {
            Map<String, Object> properties = entry.getValue();
            Row row = CellUtil.getRow(cellAddress.getRow(), sheet);
            Cell cell = CellUtil.getCell(row, cellAddress.getColumn());
            CellUtil.setCellStyleProperties(cell, properties);
        }
    }
}
 
Example 20
Source Project: onetwo   Source File: SmartIteratorRowProcessor.java    License: Apache License 2.0 5 votes vote down vote up
private Cell createSingleCell(CellContextData cellContext, int rowCount, int cellIndex, Object cellValue){
	Cell cell = null;
	if(cellContext==null)
		throw new ExcelException("the cell of row has not created yet : " + cellContext.getFieldModel().getName());

	CellContextData subCellContext = createCellContext(cellContext.getObjectValue(), rowCount, cellContext.getRowContext(), cellContext.getFieldModel(), cellIndex);
	cell = createCell(subCellContext);
	
	setCellValue(cellContext.getFieldModel(), cell, cellValue);
	
	return cell;
}
 
Example 21
private Topic getAssociationTypeTopic(Cell cell, TopicMap tm) throws TopicMapException {
    if(USE_SHEET_AS_ASSOCIATION_TYPE) {
        return getDefaultAssociationTypeTopic(tm);
    }
    else {
        return getSheetTopic(cell, tm);
    }
}
 
Example 22
Source Project: tools   Source File: ReviewersSheet.java    License: Apache License 2.0 5 votes vote down vote up
public Date getReviewerTimestamp(int rowNum) {
	Row row = sheet.getRow(rowNum);
	if (row == null) {
		return null;
	}
	Cell tsCell = row.getCell(TIMESTAMP_COL);
	if (tsCell == null) {
		return null;
	}
	return tsCell.getDateCellValue();
}
 
Example 23
Source Project: sakai   Source File: QuestionPoolBean.java    License: Educational Community License v2.0 5 votes vote down vote up
/**
 * 
 * @param spreadsheetData
 * @return
 */
protected Workbook getAsWorkbookTest(List<List<Object>> spreadsheetData) {
	Workbook wb = new HSSFWorkbook();
	Sheet sheet = wb.createSheet();
	
	short rowPos = 0;
	for( List<Object> rowData: spreadsheetData ) {
		short cellPos = 0;
		if (rowPos == 0) {
			// By convention, the first list in the list contains column headers.
			Row headerRow = sheet.createRow(rowPos++);
			for( Object header: rowData ) {
				createCell(headerRow, cellPos++, null).setCellValue(header.toString());
			}
		}
		else {
			Row row = sheet.createRow(rowPos++);
			for ( Object data : rowData ) {
				Cell cell = createCell(row, cellPos++, null);
				if (data != null) {
					if (data instanceof Double) {
						cell.setCellValue(((Double)data).doubleValue());
					} 
					else {
						cell.setCellValue(data.toString());
					}
				}
			}
		}
	}

	return wb;
}
 
Example 24
Source Project: sakai   Source File: ExportResponsesBean.java    License: Educational Community License v2.0 5 votes vote down vote up
private Cell createCell(Row row, short column, CellStyle cellStyle) {
	Cell cell = row.createCell(column);
	//cell.setEncoding(HSSFCell.ENCODING_UTF_16);	
	if (cellStyle != null) {
		cell.setCellStyle(cellStyle);
	}
	
	return cell;
}
 
Example 25
Source Project: tools   Source File: PackageInfoSheetV2d0.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public String verify() {
	try {
		if (sheet == null) {
			return "Worksheet for SPDX Package Info does not exist";
		}
		if (!SPDXSpreadsheet.verifyVersion(version)) {
			return "Unsupported version "+version;
		}
		Row firstRow = sheet.getRow(firstRowNum);
		for (int i = 0; i < NUM_COLS - 1; i++) {
			Cell cell = firstRow.getCell(i+firstCellNum);
			if (cell == null || 
					cell.getStringCellValue() == null ||
					!cell.getStringCellValue().equals(HEADER_TITLES[i])) {
				return "Column "+HEADER_TITLES[i]+" missing for SPDX Package Info worksheet";
			}
		}
		// validate rows
		boolean done = false;
		int rowNum = firstRowNum + 1;
		while (!done) {
			Row row = sheet.getRow(rowNum);
			if (row == null || row.getCell(firstCellNum) == null) {
				done = true;
			} else {
				String error = validateRow(row);
				if (error != null) {
					return error;
				}
				rowNum++;
			}
		}
		return null;
	} catch (Exception ex) {
		return "Unexpected error in verifying SPDX Package Info work sheet: "+ex.getMessage();
	}
}
 
Example 26
Source Project: autopoi   Source File: CellValueHelper.java    License: Apache License 2.0 5 votes vote down vote up
public String getHtmlValue(Cell cell) {
	if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType() || Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
		cell.setCellType(Cell.CELL_TYPE_STRING);
		return cell.getStringCellValue();
	} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
		if (cell.getRichStringCellValue().numFormattingRuns() == 0) {
			return XmlEscapers.xmlContentEscaper().escape(cell.getStringCellValue());
		} else if (is07) {
			return getXSSFRichString((XSSFRichTextString) cell.getRichStringCellValue());
		} else {
			return getHSSFRichString((HSSFRichTextString) cell.getRichStringCellValue());
		}
	}
	return "";
}
 
Example 27
@Test
public void testSpecialStyles() throws Exception {
  File f = new File("src/test/resources/special_types.xlsx");

  Map<Integer, List<Cell>> contents = new HashMap<>();
  try(Workbook wb = StreamingReader.builder().open(f)) {
    for(Row row : wb.getSheetAt(0)) {
      contents.put(row.getRowNum(), new ArrayList<Cell>());
      for(Cell c : row) {
        if(c.getColumnIndex() > 0) {
          contents.get(row.getRowNum()).add(c);
        }
      }
    }
  }

  SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");

  assertThat(contents.size(), equalTo(2));
  assertThat(contents.get(0).size(), equalTo(4));
  assertThat(contents.get(0).get(0).getStringCellValue(), equalTo("Thu\", \"Dec 25\", \"14"));
  assertThat(contents.get(0).get(0).getDateCellValue(), equalTo(df.parse("25/12/2014")));
  assertThat(contents.get(0).get(1).getStringCellValue(), equalTo("02/04/15"));
  assertThat(contents.get(0).get(1).getDateCellValue(), equalTo(df.parse("04/02/2015")));
  assertThat(contents.get(0).get(2).getStringCellValue(), equalTo("14\". \"Mar\". \"2015"));
  assertThat(contents.get(0).get(2).getDateCellValue(), equalTo(df.parse("14/03/2015")));
  assertThat(contents.get(0).get(3).getStringCellValue(), equalTo("2015-05-05"));
  assertThat(contents.get(0).get(3).getDateCellValue(), equalTo(df.parse("05/05/2015")));

  assertThat(contents.get(1).size(), equalTo(4));
  assertThat(contents.get(1).get(0).getStringCellValue(), equalTo("3.12"));
  assertThat(contents.get(1).get(0).getNumericCellValue(), equalTo(3.12312312312));
  assertThat(contents.get(1).get(1).getStringCellValue(), equalTo("1,023,042"));
  assertThat(contents.get(1).get(1).getNumericCellValue(), equalTo(1023042.0));
  assertThat(contents.get(1).get(2).getStringCellValue(), equalTo("-312,231.12"));
  assertThat(contents.get(1).get(2).getNumericCellValue(), equalTo(-312231.12123145));
  assertThat(contents.get(1).get(3).getStringCellValue(), equalTo("(132)"));
  assertThat(contents.get(1).get(3).getNumericCellValue(), equalTo(-132.0));
}
 
Example 28
Source Project: poi   Source File: TestWriteExcelDemo.java    License: Apache License 2.0 5 votes vote down vote up
public static void main(String[] args) {
	// Blank workbook
	XSSFWorkbook workbook = new XSSFWorkbook();
	// Create a blank sheet
	XSSFSheet sheet = workbook.createSheet("Employee Data");
	// This data needs to be written (Object[])
	Map<String, Object[]> data = new TreeMap<String, Object[]>();
	data.put("1", new Object[] { "ID", "NAME", "LASTNAME" });
	data.put("2", new Object[] { 1, "Amit", "Shukla" });
	data.put("3", new Object[] { 2, "Lokesh", "Gupta" });
	data.put("4", new Object[] { 3, "John", "Adwards" });
	data.put("5", new Object[] { 4, "Brian", "Schultz" });
	// Iterate over data and write to sheet
	Set<String> keyset = data.keySet();
	int rownum = 0;
	for (String key : keyset) {
		Row row = sheet.createRow(rownum++);
		Object[] objArr = data.get(key);
		int cellnum = 0;
		for (Object obj : objArr) {
			Cell cell = row.createCell(cellnum++);
			if (obj instanceof String)
				cell.setCellValue((String) obj);
			else if (obj instanceof Integer)
				cell.setCellValue((Integer) obj);
		}
	}
	try {
		// Write the workbook in file system
		FileOutputStream out = new FileOutputStream(new File(
				TestUtil.DOC_PATH + File.separator + EXCEL_NAME
						+ Globals.SUFFIX_XLSX));
		workbook.write(out);
		out.close();
		System.out.println(EXCEL_NAME + Globals.SUFFIX_XLSX
				+ TestUtil.SUCCESS);
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 
Example 29
Source Project: easypoi   Source File: ExcelExportOfTemplateUtil.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 给每个Cell通过解析方式set值
 * 
 * @param cell
 * @param map
 */
private void setValueForCellByMap(Cell cell, Map<String, Object> map) throws Exception {
    int cellType = cell.getCellType();
    if (cellType != Cell.CELL_TYPE_STRING && cellType != Cell.CELL_TYPE_NUMERIC) {
        return;
    }
    String oldString;
    cell.setCellType(Cell.CELL_TYPE_STRING);
    oldString = cell.getStringCellValue();
    if (oldString != null && oldString.indexOf(START_STR) != -1 && !oldString.contains(FOREACH)) {
        // step 2. 判断是否含有解析函数
        String params = null;
        boolean isNumber = false;
        if (isNumber(oldString)) {
            isNumber = true;
            oldString = oldString.replace(NUMBER_SYMBOL, "");
        }
        while (oldString.indexOf(START_STR) != -1) {
            params = oldString.substring(oldString.indexOf(START_STR) + 2,
                oldString.indexOf(END_STR));

            oldString = oldString.replace(START_STR + params + END_STR, eval(params, map)
                .toString());
        }
        //如何是数值 类型,就按照数值类型进行设置
        if (isNumber && StringUtils.isNotBlank(oldString)) {
            cell.setCellValue(Double.parseDouble(oldString));
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        } else {
            cell.setCellValue(oldString);
        }
    }
    //判断foreach 这种方法
    if (oldString != null && oldString.contains(FOREACH)) {
        addListDataToExcel(cell, map, oldString.trim());
    }

}
 
Example 30
Source Project: sep4j   Source File: SsioIntegrationTest.java    License: Apache License 2.0 5 votes vote down vote up
private List<Cell> getAllCells(Row row) {
	List<Cell> cells = new ArrayList<>();
	Iterator<Cell> it = row.cellIterator();
	while (it.hasNext()) {
		Cell cell = it.next();
		cells.add(cell);
	}
	return cells;
}