Java Code Examples for org.apache.poi.ss.usermodel.Cell#getCellFormula()
The following examples show how to use
org.apache.poi.ss.usermodel.Cell#getCellFormula() .
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: PoiUtils.java From easy-excel with MIT License | 6 votes |
|
/**
* 获取指定单元格的值
* @param cell 指定单元格
* @return 值
*/
public static String getColumnValue(Cell cell) {
switch (cell.getCellTypeEnum()) {
case BOOLEAN:
return Boolean.toString(cell.getBooleanCellValue());
case NUMERIC:
return String.valueOf(cell.getNumericCellValue());
case BLANK:
return "";
case FORMULA:
return cell.getCellFormula();
default:
return cell.getStringCellValue();
}
}
Example 2
| Source File: ExcelPOIHelper.java From tutorials with MIT License | 6 votes |
|
private String readCellContent(Cell cell) {
String content;
switch (cell.getCellTypeEnum()) {
case STRING:
content = cell.getStringCellValue();
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
content = cell.getDateCellValue() + "";
} else {
content = cell.getNumericCellValue() + "";
}
break;
case BOOLEAN:
content = cell.getBooleanCellValue() + "";
break;
case FORMULA:
content = cell.getCellFormula() + "";
break;
default:
content = "";
}
return content;
}
Example 3
| Source File: ExcelUtil.java From Leo with Apache License 2.0 | 6 votes |
|
private String getStrFromCell(Cell cell) {
String res = "";
if (null==cell) {
return "";
}
// res=cell.getRichStringCellValue().toString();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: // 数字/日期
if (DateUtil.isCellDateFormatted(cell)){
res=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue());
}else{
BigDecimal value = new BigDecimal(cell.getNumericCellValue());
String str = value.toString();
if(str.contains(".0"))str = str.replace(".0", "");
res=str;
}
break;
case Cell.CELL_TYPE_STRING: // 字符串
res = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN: // 布尔
Boolean booleanValue = cell.getBooleanCellValue();
res = booleanValue.toString();
break;
case Cell.CELL_TYPE_BLANK: // 空值
res = "";
break;
case Cell.CELL_TYPE_FORMULA: // 公式
res = cell.getCellFormula();
break;
case Cell.CELL_TYPE_ERROR: // 故障
res = "";
break;
default:
System.out.println("未知类型");
break;
}
return res;
}
Example 4
| Source File: ExcelOperator.java From minsx-framework with Apache License 2.0 | 6 votes |
|
@SuppressWarnings("deprecation")
private static String getCellData(Cell cell) {
String value = null;
if (cell == null) {
return null;
}
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_FORMULA:
value = cell.getCellFormula();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
value = dataFormatter.formatCellValue(cell);
break;
case HSSFCell.CELL_TYPE_BLANK:
value = null;
break;
case HSSFCell.CELL_TYPE_ERROR:
value = "#ERROR#";
break;
}
return value;
}
Example 5
| Source File: ExcelImportServer.java From autopoi with Apache License 2.0 | 6 votes |
|
/**
* 获取key的值,针对不同类型获取不同的值
*
* @Author JEECG
* @date 2013-11-21
* @param cell
* @return
*/
private String getKeyValue(Cell cell) {
if(cell==null){
return null;
}
Object obj = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
obj = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
obj = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
obj = cell.getNumericCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
obj = cell.getCellFormula();
break;
}
return obj == null ? null : obj.toString().trim();
}
Example 6
| Source File: CellValueFormatter.java From CloverETL-Engine with GNU Lesser General Public License v2.1 | 6 votes |
|
@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 7
| Source File: ExcelUtils.java From onetwo with Apache License 2.0 | 6 votes |
|
public static Object getCellValue(Cell cell){
if(cell==null)
return null;
int type = cell.getCellType();
Object value = null;
if(Cell.CELL_TYPE_STRING==type){
// value = StringUtils.cleanInvisibleUnicode(cell.getStringCellValue().trim());
value = cell.getStringCellValue().trim();
}else if(Cell.CELL_TYPE_NUMERIC==type){
value = cell.getNumericCellValue();
}else if(Cell.CELL_TYPE_FORMULA==type){
value = cell.getCellFormula();
}else if(Cell.CELL_TYPE_BOOLEAN==type){
value = cell.getBooleanCellValue();
}else if(Cell.CELL_TYPE_BLANK==type){
value = "";
}
return value;
}
Example 8
| Source File: ExcelUtil.java From phone with Apache License 2.0 | 6 votes |
|
public static String getStringValue(Cell cell) {
switch (cell.getCellTypeEnum()) {
case BOOLEAN:
return cell.getBooleanCellValue() ? "1" : "0";
case FORMULA:
return cell.getCellFormula();
case NUMERIC:
cell.setCellType(CellType.STRING);
return cell.getStringCellValue();
case STRING:
return cell.getStringCellValue();
default:
return "";
}
}
Example 9
| Source File: ExcelHelper.java From Excel2Entity with MIT License | 5 votes |
|
/**
* 读取每个单元格中的内容
*
* @param cell
* @return
*/
private String _getCellValue(Cell cell) {
// 如果单元格为空的，则返回空字符串
if (cell == null) {
return "";
}
// 根据单元格类型，以不同的方式读取单元格的值
String value = "";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
value = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(cell.getDateCellValue());
} else {
value = (long) cell.getNumericCellValue() + "";
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue() ? "TRUE" : "FALSE";
break;
case Cell.CELL_TYPE_FORMULA:
value = cell.getCellFormula();
break;
default:
}
return value;
}
Example 10
| Source File: ExcelReader.java From azeroth with Apache License 2.0 | 5 votes |
|
/**
* 获取指定单元格的值
*
* @param rowNumber 行数，从1开始
* @param cellNumber 列数，从1开始
* @return 该单元格的值
*/
public String getCellValue(int rowNumber, int cellNumber) {
String result;
checkRowAndCell(rowNumber, cellNumber);
Sheet sheet = this.workbook.getSheet(this.sheetName);
Row row = sheet.getRow(--rowNumber);
Cell cell = row.getCell(--cellNumber);
switch (cell.getCellTypeEnum()) {
case BLANK:
result = cell.getStringCellValue();
break;
case BOOLEAN:
result = String.valueOf(cell.getBooleanCellValue());
break;
case ERROR:
result = String.valueOf(cell.getErrorCellValue());
break;
case FORMULA:
result = cell.getCellFormula();
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
result = format.format(cell.getDateCellValue());
} else {
result = String.valueOf(cell.getNumericCellValue());
}
break;
case STRING:
result = cell.getRichStringCellValue().getString();
break;
default:
result = cell.getStringCellValue();
break;
}
return result;
}
Example 11
| Source File: ExcelUtil.java From javautils with Apache License 2.0 | 5 votes |
|
/**
* 获取cell数据
* @param cell
* @return
*/
private static String getCellValue(Cell cell){
String value = "";
if(cell != null) {
switch (cell.getCellTypeEnum()) {
case FORMULA:
value += cell.getCellFormula();
break;
case NUMERIC:
double cellValue = cell.getNumericCellValue();
if(HSSFDateUtil.isCellDateFormatted(cell)){
Date date = HSSFDateUtil.getJavaDate(cellValue);
value += DateUtil.format(date, DateUtil.DATE_TIME);
}else{
value += cell.getNumericCellValue();
}
break;
case STRING:
value += cell.getStringCellValue();
break;
case BLANK:
break;
case BOOLEAN:
value += cell.getBooleanCellValue();
break;
case ERROR:
break;
default:break;
}
}
return value;
}
Example 12
| Source File: ExcelReader.java From jeesuite-libs with Apache License 2.0 | 5 votes |
|
/**
* 获取指定单元格的值
*
* @param rowNumber 行数，从1开始
* @param cellNumber 列数，从1开始
* @return 该单元格的值
*/
public String getCellValue(int rowNumber, int cellNumber) {
String result;
checkRowAndCell(rowNumber, cellNumber);
Sheet sheet = this.workbook.getSheet(this.sheetName);
Row row = sheet.getRow(--rowNumber);
Cell cell = row.getCell(--cellNumber);
switch (cell.getCellTypeEnum()) {
case BLANK:
result = cell.getStringCellValue();
break;
case BOOLEAN:
result = String.valueOf(cell.getBooleanCellValue());
break;
case ERROR:
result = String.valueOf(cell.getErrorCellValue());
break;
case FORMULA:
result = cell.getCellFormula();
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
result = format.format(cell.getDateCellValue());
} else {
result = String.valueOf(cell.getNumericCellValue());
}
break;
case STRING:
result = cell.getRichStringCellValue().getString();
break;
default:
result = cell.getStringCellValue();
break;
}
return result;
}
Example 13
| Source File: AnnoFormulaTest.java From xlsmapper with Apache License 2.0 | 4 votes |
|
/**
* 正常 - 数式を優先
*/
@Test
public void test_normal_primay() throws Exception {
// テストデータの作成
final FormulaSheet outSheet = new FormulaSheet();
outSheet.c1(12.345d);
// アノテーションの組み立て
AnnotationMappingInfo xmlInfo = createXml()
.classInfo(createClass(FormulaSheet.class)
.field(createField("c1")
.override(true)
.annotation(createAnnotation(XlsFormula.class)
.attribute("value", "SUM(C2:C3)")
.attribute("primary", true)
.buildAnnotation())
.buildField())
.buildClass())
.buildXml();
// ファイルへの書き込み
XlsMapper mapper = new XlsMapper();
mapper.getConfiguration().setContinueTypeBindFailure(true)
.setAnnotationMapping(xmlInfo);;
File outFile = new File(OUT_DIR, outFilename);
try(InputStream template = new FileInputStream(templateFile);
OutputStream out = new FileOutputStream(outFile)) {
mapper.save(template, out, outSheet);
}
// 書き込んだファイルを読み込み値の検証を行う。
try(InputStream in = new FileInputStream(outFile)) {
Workbook book = WorkbookFactory.create(in);
Sheet sheet = book.getSheet("Formula(通常)");
Cell cell = POIUtils.getCell(sheet, CellPosition.of("A2"));
assertThat(cell.getCellTypeEnum(), is(CellType.FORMULA));
String formula = cell.getCellFormula();
CellFormatter formatter = mapper.getConfiguration().getCellFormatter();
String value = formatter.format(cell);
assertThat(formula, is("SUM(C2:C3)"));
assertThat(value, is("17.468"));
}
}
Example 14
| Source File: AnnoFormulaTest.java From xlsmapper with Apache License 2.0 | 4 votes |
|
/**
* 正常 - メソッドで式を組み立て
*/
@Test
public void test_normal_method() throws Exception {
// テストデータの作成
final FormulaSheet outSheet = new FormulaSheet();
// アノテーションの組み立て
AnnotationMappingInfo xmlInfo = createXml()
.classInfo(createClass(FormulaSheet.class)
.field(createField("c1")
.override(true)
.annotation(createAnnotation(XlsFormula.class)
.attribute("methodName", "getC1Formula")
.buildAnnotation())
.buildField())
.buildClass())
.buildXml();
// ファイルへの書き込み
XlsMapper mapper = new XlsMapper();
mapper.getConfiguration().setContinueTypeBindFailure(true)
.setAnnotationMapping(xmlInfo);;
File outFile = new File(OUT_DIR, outFilename);
try(InputStream template = new FileInputStream(templateFile);
OutputStream out = new FileOutputStream(outFile)) {
mapper.save(template, out, outSheet);
}
// 書き込んだファイルを読み込み値の検証を行う。
try(InputStream in = new FileInputStream(outFile)) {
Workbook book = WorkbookFactory.create(in);
Sheet sheet = book.getSheet("Formula(通常)");
Cell cell = POIUtils.getCell(sheet, CellPosition.of("A2"));
String formula = cell.getCellFormula();
CellFormatter formatter = mapper.getConfiguration().getCellFormatter();
String value = formatter.format(cell);
assertThat(formula, is("SUM(D2:D3)"));
assertThat(value, is("579"));
}
}
Example 15
| Source File: ExcelUtil.java From SI with BSD 2-Clause "Simplified" License | 4 votes |
|
/**
* 엑셀파일로부터 데이터를 읽어 리턴한다. *
*/
public static List getData(Workbook wb) {
List excelList = new ArrayList();
int sheetNum = wb.getNumberOfSheets();
for (int k=0; k<sheetNum; k++) {
Sheet sheet = wb.getSheetAt(k);
int rows = sheet.getPhysicalNumberOfRows();
for (int r=0; r<rows; r++) {
Row row = sheet.getRow(r);
if (row != null) {
int cells = row.getPhysicalNumberOfCells();
String[] excelRow = new String[cells];
for(int c=0; c<cells; c++) {
Cell cell = row.getCell(c);
if (cell != null) {
String value = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_FORMULA:
value = cell.getCellFormula();
break;
case Cell.CELL_TYPE_NUMERIC:
value = "" + Integer.parseInt(String.valueOf(Math.round(cell.getNumericCellValue())));
break;
case Cell.CELL_TYPE_STRING:
value = "" + cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "" + cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_ERROR:
value = "" + cell.getErrorCellValue();
break;
default:
}
excelRow[c] = value;
}
}
excelList.add(excelRow);
}
}
}
return excelList;
}
Example 16
| Source File: ExcelUtil.java From SI with BSD 2-Clause "Simplified" License | 4 votes |
|
/**
* 엑셀파일로부터 데이터를 읽어 리턴한다. *
*/
public static List getData(Workbook wb) {
List excelList = new ArrayList();
int sheetNum = wb.getNumberOfSheets();
for (int k=0; k<sheetNum; k++) {
Sheet sheet = wb.getSheetAt(k);
int rows = sheet.getPhysicalNumberOfRows();
for (int r=0; r<rows; r++) {
Row row = sheet.getRow(r);
if (row != null) {
int cells = row.getPhysicalNumberOfCells();
String[] excelRow = new String[cells];
for(int c=0; c<cells; c++) {
Cell cell = row.getCell(c);
if (cell != null) {
String value = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_FORMULA:
value = cell.getCellFormula();
break;
case Cell.CELL_TYPE_NUMERIC:
value = "" + Integer.parseInt(String.valueOf(Math.round(cell.getNumericCellValue())));
break;
case Cell.CELL_TYPE_STRING:
value = "" + cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "" + cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_ERROR:
value = "" + cell.getErrorCellValue();
break;
default:
}
excelRow[c] = value;
}
}
excelList.add(excelRow);
}
}
}
return excelList;
}
Example 17
| Source File: MSExcelParser.java From hadoopoffice with Apache License 2.0 | 4 votes |
|
@Override
public Object[] getNext() {
SpreadSheetCellDAO[] result=null;
// all sheets?
if (this.sheets==null) { // go on with all sheets
if (!nextAllSheets()) {
return result;
}
} else { // go on with specified sheets
if (!nextSpecificSheets()) {
return result;
}
}
// read row from the sheet currently to be processed
Sheet rSheet = this.currentWorkbook.getSheetAt(this.currentSheet);
Row rRow = rSheet.getRow(this.currentRow);
if ((rRow==null) || (rRow.getLastCellNum()<0)) {
this.currentRow++;
return new SpreadSheetCellDAO[0]; // emtpy row
}
result = new SpreadSheetCellDAO[rRow.getLastCellNum()];
for (int i=0;i<rRow.getLastCellNum();i++) {
Cell currentCell=rRow.getCell(i);
if (currentCell==null) {
result[i]=null;
} else {
String formattedValue=useDataFormatter.formatCellValue(currentCell,this.formulaEvaluator);
String formula = "";
if (currentCell.getCellType()==CellType.FORMULA) {
formula = currentCell.getCellFormula();
}
Comment currentCellComment = currentCell.getCellComment();
String comment = "";
if (currentCellComment!=null) {
comment = currentCellComment.getString().getString();
}
String address = currentCell.getAddress().toString();
String sheetName = currentCell.getSheet().getSheetName();
SpreadSheetCellDAO mySpreadSheetCellDAO = new SpreadSheetCellDAO(formattedValue,comment,formula,address,sheetName);
result[i]=mySpreadSheetCellDAO;
}
}
// increase rows
this.currentRow++;
return result;
}
Example 18
| Source File: ExcelUtil.java From jeasypoi with Apache License 2.0 | 4 votes |
|
/**
* 获取单元格的值
* @param cell
* @return
*/
public static String getCellValue(Cell cell){
if(cell == null) return "";
if(cell.getCellType() == Cell.CELL_TYPE_STRING){
return cell.getStringCellValue();
}else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
return String.valueOf(cell.getBooleanCellValue());
}else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
return cell.getCellFormula() ;
}else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
return String.valueOf(cell.getNumericCellValue());
}
return "";
}
Example 19
| Source File: AnnoFormulaTest.java From xlsmapper with Apache License 2.0 | 4 votes |
|
/**
* 正常 - 式を直接指定
*/
@Test
public void test_normal_formula() throws Exception {
// テストデータの作成
final FormulaSheet outSheet = new FormulaSheet();
// アノテーションの組み立て
AnnotationMappingInfo xmlInfo = createXml()
.classInfo(createClass(FormulaSheet.class)
.field(createField("c1")
.override(true)
.annotation(createAnnotation(XlsFormula.class)
.attribute("value", "SUM(C2:${x:colToAlpha(columnNumber+2)}3)")
.buildAnnotation())
.buildField())
.buildClass())
.buildXml();
// ファイルへの書き込み
XlsMapper mapper = new XlsMapper();
mapper.getConfiguration().setContinueTypeBindFailure(true)
.setAnnotationMapping(xmlInfo);
File outFile = new File(OUT_DIR, outFilename);
try(InputStream template = new FileInputStream(templateFile);
OutputStream out = new FileOutputStream(outFile)) {
mapper.save(template, out, outSheet);
}
// 書き込んだファイルを読み込み値の検証を行う。
try(InputStream in = new FileInputStream(outFile)) {
Workbook book = WorkbookFactory.create(in);
Sheet sheet = book.getSheet("Formula(通常)");
Cell cell = POIUtils.getCell(sheet, CellPosition.of("A2"));
String formula = cell.getCellFormula();
CellFormatter formatter = mapper.getConfiguration().getCellFormatter();
String value = formatter.format(cell);
assertThat(formula, is("SUM(C2:C3)"));
assertThat(value, is("17.468"));
}
}
Example 20
| Source File: ExcelUtil.java From autopoi with Apache License 2.0 | 4 votes |
|
/**
* 获取单元格的值
* @param cell
* @return
*/
public static String getCellValue(Cell cell){
if(cell == null) return "";
if(cell.getCellType() == Cell.CELL_TYPE_STRING){
return cell.getStringCellValue();
}else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
return String.valueOf(cell.getBooleanCellValue());
}else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
return cell.getCellFormula() ;
}else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
return String.valueOf(cell.getNumericCellValue());
}
return "";
}
