Java Code Examples for org.apache.poi.ss.usermodel.Cell#CELL_TYPE_STRING
The following examples show how to use
org.apache.poi.ss.usermodel.Cell#CELL_TYPE_STRING .
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: ExcelExportOfTemplateUtil.java From jeasypoi with Apache License 2.0 | 7 votes |
|
/**
* 先判断删除,省得影响效率
*
* @param sheet
* @param map
* @throws Exception
*/
private void deleteCell(Sheet sheet, Map<String, Object> map) throws Exception {
Row row = null;
Cell cell = null;
int index = 0;
while (index <= sheet.getLastRowNum()) {
row = sheet.getRow(index++);
if (row == null) {
continue;
}
for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
cell = row.getCell(i);
if (row.getCell(i) != null && (cell.getCellType() == Cell.CELL_TYPE_STRING || cell.getCellType() == Cell.CELL_TYPE_NUMERIC)) {
cell.setCellType(Cell.CELL_TYPE_STRING);
String text = cell.getStringCellValue();
if (text.contains(IF_DELETE)) {
if (Boolean.valueOf(eval(text.substring(text.indexOf(START_STR) + 2, text.indexOf(END_STR)).trim(), map).toString())) {
PoiSheetUtility.deleteColumn(sheet, i);
}
cell.setCellValue("");
}
}
}
}
}
Example 2
| Source File: ConvertDemoDataFileServiceImpl.java From axelor-open-suite with GNU Affero General Public License v3.0 | 6 votes |
|
private String getFileNameFromSheet(Sheet sheet) throws AxelorException {
String fileName = "";
Row fileNameRow = sheet.getRow(1);
if (fileNameRow != null) {
Cell fileNameCell = fileNameRow.getCell(0);
if (fileNameCell != null
&& fileNameCell.getCellType() != Cell.CELL_TYPE_BLANK
&& fileNameCell.getCellType() == Cell.CELL_TYPE_STRING) {
fileName = fileNameCell.getStringCellValue() + ".csv";
} else {
throw new AxelorException(
TraceBackRepository.CATEGORY_MISSING_FIELD,
I18n.get(IExceptionMessage.CSV_FILE_NAME_NOT_EXISTS));
}
} else {
throw new AxelorException(
TraceBackRepository.CATEGORY_INCONSISTENCY,
I18n.get(IExceptionMessage.EXCEL_FILE_FORMAT_ERROR));
}
return fileName;
}
Example 3
| Source File: WorkbookReaderFactory.java From onetwo with Apache License 2.0 | 6 votes |
|
@Override
public Date doConvert(Cell cell) {
int type = cell.getCellType();
Date value = null;
if(Cell.CELL_TYPE_STRING==type){
value = TheFunction.getInstance().parseDateTime(getStringValue(cell));
}else if(Cell.CELL_TYPE_NUMERIC==type){
value = cell.getDateCellValue();
}else if(Cell.CELL_TYPE_FORMULA==type){
CellValue cv = ExcelUtils.getFormulaCellValue(cell);
value = cv==null?defaultValue:TheFunction.getInstance().parseDateTime(cv.getStringValue());//Types.convertValue(cv.getStringValue(), Date.class);
}else {
String strValue = getAsString(cell);
if(StringUtils.isBlank(strValue))
return defaultValue;
value = TheFunction.getInstance().parseDateTime(strValue);
}
return value;
}
Example 4
| Source File: POIUtils.java From ermasterr with Apache License 2.0 | 6 votes |
|
public static Integer findMatchColumn(final HSSFRow row, final String str) {
for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {
final HSSFCell cell = row.getCell(colNum);
if (cell == null) {
continue;
}
if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
continue;
}
final HSSFRichTextString cellValue = cell.getRichStringCellValue();
if (cellValue.getString().matches(str)) {
return Integer.valueOf(colNum);
}
}
return null;
}
Example 5
| Source File: ExcelImportServer.java From jeasypoi with Apache License 2.0 | 6 votes |
|
/**
* 获取key的值,针对不同类型获取不同的值
*
* @Author JueYue
* @date 2013-11-21
* @param cell
* @return
*/
private String getKeyValue(Cell cell) {
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: 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 7
| Source File: ExcelImportServer.java From easypoi with Apache License 2.0 | 6 votes |
|
/**
* 获取key的值,针对不同类型获取不同的值
*
* @Author JueYue
* @date 2013-11-21
* @param cell
* @return
*/
private String getKeyValue(Cell cell) {
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 8
| 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 9
| Source File: readExcel.java From Selenium with The Unlicense | 5 votes |
|
private static String cellToString(HSSFCell cell) {
Object result;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
result = cell.getNumericCellValue();
break;
case Cell.CELL_TYPE_STRING:
result = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
result = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
result = cell.getCellFormula();
break;
default:
throw new RuntimeException("Unknown Cell Type");
}
return result.toString();
}
Example 10
| Source File: Issue27.java From birt with Eclipse Public License 1.0 | 5 votes |
|
@Test
public void testRowSpanXls() throws BirtException, IOException {
debug = false;
InputStream inputStream = runAndRenderReport("Issue27.rptdesign", "xls");
assertNotNull(inputStream);
try {
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
assertNotNull(workbook);
Sheet sheet = workbook.getSheetAt(0);
int rangesValidated = 0;
for( Row row : sheet ) {
for( Cell cell : row ) {
if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
String cellValue = cell.getStringCellValue();
Matcher matcher = pattern.matcher(cellValue);
if( matcher.matches() ) {
validateCellRange( matcher, cell );
++rangesValidated;
}
}
}
}
assertEquals( 12, rangesValidated );
} finally {
inputStream.close();
}
}
Example 11
| Source File: ExcelExportOfTemplateUtil.java From easypoi with Apache License 2.0 | 5 votes |
|
/**
* 给每个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 12
| Source File: CellValueHelper.java From jeasypoi with Apache License 2.0 | 5 votes |
|
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 13
| Source File: Ssio.java From sep4j with Apache License 2.0 | 5 votes |
|
/**
* read the cell. it only supports: boolean, numeric, date(numeric cell type
* + date cell format) and string.
*
* @param cell
* the cell to read
* @return the date if it is a date cell, or else the string value (will be
* trimmed to null) . <br/>
*
*
*/
static Object readCellAsStringOrDate(Cell cell) {
if (cell == null) {
return null;
}
if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
return null;
}
if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
}
if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
return null;
}
if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
return null;
}
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
} else {
double v = cell.getNumericCellValue();
return String.valueOf(v);
}
}
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
String s = cell.getStringCellValue();
return StringUtils.trimToNull(s);
}
return null;
}
Example 14
| Source File: ExcelTempletService.java From jeecg with Apache License 2.0 | 5 votes |
|
/**
* 得到某个格子的值 已经对过时方法进行更新
*
* @param cell
* 格子对象
* @return 格子的值
*/
public static String getCellValueString(Cell cell) {
if (cell == null) {
return null;
}
// 时间对象 特殊处理
int dataFormat = cell.getCellStyle().getDataFormat();
if (dataFormat == 14 || dataFormat == 178 || dataFormat == 180 || dataFormat == 181
|| dataFormat == 182) {
return getDateValue(cell);
}
String value = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC :
value = new DecimalFormat("0.##########").format(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING :
// value = cell.getStringCellValue();
value = cell.getRichStringCellValue().toString();
break;
case Cell.CELL_TYPE_FORMULA :
value = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK :
// value = String.valueOf(cell.getStringCellValue());
value = String.valueOf(cell.getRichStringCellValue().toString());
break;
case Cell.CELL_TYPE_BOOLEAN :
value = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR :
value = String.valueOf(cell.getErrorCellValue());
break;
}
return value;
}
Example 15
| Source File: ExcelTempletService.java From jeewx with Apache License 2.0 | 5 votes |
|
/**
* 得到某个格子的值 已经对过时方法进行更新
*
* @param cell
* 格子对象
* @return 格子的值
*/
public static String getCellValueString(Cell cell) {
if (cell == null) {
return null;
}
// 时间对象 特殊处理
int dataFormat = cell.getCellStyle().getDataFormat();
if (dataFormat == 14 || dataFormat == 178 || dataFormat == 180 || dataFormat == 181
|| dataFormat == 182) {
return getDateValue(cell);
}
String value = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC :
value = new DecimalFormat("0.##########").format(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING :
// value = cell.getStringCellValue();
value = cell.getRichStringCellValue().toString();
break;
case Cell.CELL_TYPE_FORMULA :
value = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK :
// value = String.valueOf(cell.getStringCellValue());
value = String.valueOf(cell.getRichStringCellValue().toString());
break;
case Cell.CELL_TYPE_BOOLEAN :
value = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR :
value = String.valueOf(cell.getErrorCellValue());
break;
}
return value;
}
Example 16
| Source File: SpreadsheetFindCell.java From openbd-core with GNU General Public License v3.0 | 5 votes |
|
public cfData execute( cfSession _session, List<cfData> parameters ) throws cfmRunTimeException {
cfSpreadSheetData spreadsheet = (cfSpreadSheetData)parameters.get(1);
Pattern pattern = Pattern.compile( parameters.get(0).getString() );
cfArrayData arr = cfArrayData.createArray(1);
Iterator<Row> rowIT = spreadsheet.getActiveSheet().rowIterator();
while ( rowIT.hasNext() ){
Row row = rowIT.next();
Iterator<Cell> cellIT = row.cellIterator();
while ( cellIT.hasNext() ){
Cell cell = cellIT.next();
String cellValue = null;
if ( cell.getCellType() == Cell.CELL_TYPE_STRING )
cellValue = cell.getStringCellValue();
else if ( cell.getCellType() == Cell.CELL_TYPE_NUMERIC )
cellValue = String.valueOf( cell.getNumericCellValue() );
else
cellValue = cell.toString();
if ( pattern.matcher( cellValue ).find() ){
cfStructData s = new cfStructData();
s.setData( "row", new cfNumberData( cell.getRowIndex() + 1 ) );
s.setData( "column", new cfNumberData( cell.getColumnIndex() + 1 ) );
s.setData( "value", new cfStringData( cellValue ) );
arr.addElement( s );
}
}
}
return arr;
}
Example 17
| Source File: AbstractExcelExtractor.java From wandora with GNU General Public License v3.0 | 5 votes |
|
protected String getCellValueAsString(Cell cell, int type) {
if(cell != null) {
switch(type) {
case Cell.CELL_TYPE_ERROR: {
return "ERROR"+cell.getErrorCellValue();
}
case Cell.CELL_TYPE_BOOLEAN: {
return ""+cell.getBooleanCellValue();
}
case Cell.CELL_TYPE_NUMERIC: {
if(DateUtil.isCellDateFormatted(cell)) {
return dateFormat.format(cell.getDateCellValue());
}
else {
double value = cell.getNumericCellValue();
String formatString = cell.getCellStyle().getDataFormatString();
int formatIndex = cell.getCellStyle().getDataFormat();
return formatter.formatRawCellContents(value, formatIndex, formatString);
}
}
case Cell.CELL_TYPE_STRING: {
return cell.getRichStringCellValue().getString();
}
}
}
return null;
}
Example 18
| Source File: HyperlinksTest.java From birt with Eclipse Public License 1.0 | 4 votes |
|
@Test
public void testBookmarksXlsx() throws BirtException, IOException {
debug = false;
InputStream inputStream = runAndRenderReport("Bookmarks.rptdesign", "xlsx");
assertNotNull(inputStream);
try {
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
assertNotNull(workbook);
Sheet sheet = workbook.getSheetAt(0);
int rangesValidated = 0;
for( Row row : sheet ) {
for( Cell cell : row ) {
if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
String cellValue = cell.getStringCellValue();
Matcher matcher = pattern.matcher(cellValue);
if( matcher.matches() ) {
validateCellRange( matcher, cell );
++rangesValidated;
}
}
}
}
assertEquals( 7, rangesValidated );
assertEquals( 18, workbook.getNumberOfNames() );
int index = 0;
validateNamedRange( workbook, index++, "DataItemOne", -1, 1, 0, 1, 0 );
validateNamedRange( workbook, index++, "DataItem2", -1, 1, 1, 1, 1 );
validateNamedRange( workbook, index++, "Row0", -1, 1, 0, 1, 2 );
validateNamedRange( workbook, index++, "_recreated__bookmark__1", -1, 2, 0, 2, 0 );
validateNamedRange( workbook, index++, "DataItem4", -1, 2, 1, 2, 1 );
validateNamedRange( workbook, index++, "_Row1", -1, 2, 0, 2, 2 );
validateNamedRange( workbook, index++, "_recreated__bookmark__2", -1, 3, 0, 3, 0 );
validateNamedRange( workbook, index++, "DataItem6", -1, 3, 1, 3, 1 );
validateNamedRange( workbook, index++, "_Row2", -1, 3, 0, 3, 2 );
validateNamedRange( workbook, index++, "Table_1", -1, 0, 0, 3, 2 );
validateNamedRange( workbook, index++, "R5C1_R5C1", -1, 4, 0, 4, 0 );
validateNamedRange( workbook, index++, "R6C1_R6C2", -1, 5, 0, 5, 0 );
validateNamedRange( workbook, index++, "R7C1_R7C3", -1, 6, 0, 6, 0 );
validateNamedRange( workbook, index++, "R8C1_R9C1", -1, 7, 0, 7, 0 );
validateNamedRange( workbook, index++, "R10C1_R12C1", -1, 9, 0, 9, 0 );
validateNamedRange( workbook, index++, "R13C1_R14C2", -1, 12, 0, 12, 0 );
validateNamedRange( workbook, index++, "R15C1_R17C3", -1, 14, 0, 14, 0 );
validateNamedRange( workbook, index++, "Grid_1", -1, 4, 0, 15, 2 );
} finally {
inputStream.close();
}
}
Example 19
| Source File: ExcelOOXMLDocument.java From olat with Apache License 2.0 | 4 votes |
|
private void extractContent(final StringBuilder buffy, final XSSFWorkbook document) {
for (int i = 0; i < document.getNumberOfSheets(); i++) {
final XSSFSheet sheet = document.getSheetAt(i);
buffy.append(document.getSheetName(i)).append(' ');
// Header(s), if present
extractHeaderFooter(buffy, sheet.getFirstHeader());
extractHeaderFooter(buffy, sheet.getOddHeader());
extractHeaderFooter(buffy, sheet.getEvenHeader());
// Rows and cells
for (final Object rawR : sheet) {
final Row row = (Row) rawR;
for (final Iterator<Cell> ri = row.cellIterator(); ri.hasNext();) {
final Cell cell = ri.next();
if (cell.getCellType() == Cell.CELL_TYPE_FORMULA || cell.getCellType() == Cell.CELL_TYPE_STRING) {
buffy.append(cell.getRichStringCellValue().getString()).append(' ');
} else {
final XSSFCell xc = (XSSFCell) cell;
final String rawValue = xc.getRawValue();
if (rawValue != null) {
buffy.append(rawValue).append(' ');
}
}
// Output the comment in the same cell as the content
final Comment comment = cell.getCellComment();
if (comment != null) {
buffy.append(comment.getString().getString()).append(' ');
}
}
}
// Finally footer(s), if present
extractHeaderFooter(buffy, sheet.getFirstFooter());
extractHeaderFooter(buffy, sheet.getOddFooter());
extractHeaderFooter(buffy, sheet.getEvenFooter());
}
}
Example 20
| Source File: ExcelUtils.java From ssm-Online_Examination with Apache License 2.0 | 4 votes |
|
/**
* 描述：对表格中数值进行格式化
* @param cell
* @return
*/
//解决excel类型问题，获得数值
public String getValue(Cell cell) {
String value = "";
if(null==cell){
return value;
}
switch (cell.getCellType()) {
//数值型
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
//如果是date类型则 ，获取该cell的date值
Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
value = format.format(date);;
}else {// 纯数字
BigDecimal big=new BigDecimal(cell.getNumericCellValue());
value = big.toString();
//解决1234.0 去掉后面的.0
if(null!=value&&!"".equals(value.trim())){
String[] item = value.split("[.]");
if(1<item.length&&"0".equals(item[1])){
value=item[0];
}
}
}
break;
//字符串类型
case Cell.CELL_TYPE_STRING:
value = cell.getStringCellValue().toString();
break;
// 公式类型
case Cell.CELL_TYPE_FORMULA:
//读公式计算值
value = String.valueOf(cell.getNumericCellValue());
if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
value = cell.getStringCellValue().toString();
}
break;
// 布尔类型
case Cell.CELL_TYPE_BOOLEAN:
value = " "+ cell.getBooleanCellValue();
break;
default:
value = cell.getStringCellValue().toString();
}
if("null".endsWith(value.trim())){
value="";
}
return value;
}
