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   Author: spdx   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   Author: godcheese   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
Source Project: pentaho-reporting   Author: pentaho   File: Prd4434IT.java    License: GNU Lesser General Public License v2.1 6 votes vote down vote up
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   Author: spdx   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   Author: mygreen   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
Source Project: Open-Lowcode   Author: openlowcode   File: DateDataObjectFieldFlatFileLoader.java    License: Eclipse Public License 2.0 6 votes vote down vote up
@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   Author: igit-cn   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   Author: spdx   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   Author: spdx   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
Source Project: easyexcel   Author: alibaba   File: LongestMatchColumnWidthStyleStrategy.java    License: Apache License 2.0 6 votes vote down vote up
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   Author: zhangdaiscott   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   Author: monitorjbl   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   Author: spdx   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
Source Project: CloverETL-Engine   Author: CloverETL   File: CellValueFormatter.java    License: GNU Lesser General Public License v2.1 6 votes vote down vote up
@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   Author: zhangdaiscott   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   Author: mygreen   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   Author: lamsfoundation   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   Author: Talend   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   Author: lamsfoundation   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   Author: wayshall   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
Source Project: wandora   Author: wandora-team   File: ExcelAdjacencyMatrixExtractor.java    License: GNU General Public License v3.0 5 votes vote down vote up
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   Author: spdx   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   Author: sakaiproject   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   Author: sakaiproject   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   Author: spdx   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   Author: zhangdaiscott   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
Source Project: excel-streaming-reader   Author: monitorjbl   File: StreamingReaderTest.java    License: Apache License 2.0 5 votes vote down vote up
@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: easypoi   Author: xiaolanglang   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 #29
Source Project: sep4j   Author: chenjianjx   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;
}
 
Example #30
Source Project: onetwo   Author: wayshall   File: ExcelUtils.java    License: Apache License 2.0 5 votes vote down vote up
public static CellValue getFormulaCellValue(Cell cell){
		try {
			return cell==null?null:cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator().evaluate(cell);
		} catch (Exception e) {
//			throw new BaseException("get formual cell value["+cell.getRowIndex()+", "+ cell.getColumnIndex()+"] error : " + e.getMessage(), e);
			if(ExcelGenerators.isDevModel()){
				logger.warn("get formual cell value["+cell.getRowIndex()+", "+ cell.getColumnIndex()+"] error : " + e.getMessage());
			}
			return null;
		}
	}