Java Code Examples for org.apache.poi.ss.usermodel.Cell#getCellType()
The following examples show how to use
org.apache.poi.ss.usermodel.Cell#getCellType() .
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: PersonSheetConfigurator.java From o2oa with GNU Affero General Public License v3.0 | 7 votes |
|
public String getCellStringValue(Cell cell) {
if (null != cell) {
switch (cell.getCellType()) {
case BLANK:
return "";
case BOOLEAN:
return BooleanUtils.toString(cell.getBooleanCellValue(), "true", "false", "false");
case ERROR:
return "";
case FORMULA:
return "";
case NUMERIC:
Double d = cell.getNumericCellValue();
Long l = d.longValue();
if (l.doubleValue() == d) {
return l.toString();
} else {
return d.toString();
}
default:
return cell.getStringCellValue();
}
}
return "";
}
Example 2
| Source File: GenerateDoc.java From danyuan-application with Apache License 2.0 | 6 votes |
|
@SuppressWarnings("deprecation")
private static XSSFSheet copySheet(XSSFSheet sheetFrom, XSSFSheet sheetTo) {
// 初期化
CellRangeAddress region = null;
Row rowFrom = null;
Row rowTo = null;
Cell cellFrom = null;
Cell cellTo = null;
// セル結合のコピー
for (int i = 0; i < sheetFrom.getNumMergedRegions(); i++) {
region = sheetFrom.getMergedRegion(i);
if ((region.getFirstColumn() >= sheetFrom.getFirstRowNum()) && (region.getLastRow() <= sheetFrom.getLastRowNum())) {
sheetTo.addMergedRegion(region);
}
}
// セルのコピー
for (int intRow = sheetFrom.getFirstRowNum(); intRow <= sheetFrom.getLastRowNum(); intRow++) {
rowFrom = sheetFrom.getRow(intRow);
rowTo = sheetTo.createRow(intRow);
if (null == rowFrom) {
continue;
}
rowTo.setHeight(rowFrom.getHeight());
for (int intCol = 0; intCol < rowFrom.getLastCellNum(); intCol++) {
// セル幅のコピー
sheetTo.setDefaultColumnStyle(intCol, sheetFrom.getColumnStyle(intCol));
sheetTo.setColumnWidth(intCol, sheetFrom.getColumnWidth(intCol));
cellFrom = rowFrom.getCell(intCol);
cellTo = rowTo.createCell(intCol);
if (null == cellFrom) {
continue;
}
// セルスタイルとタイプのコピー
cellTo.setCellStyle(cellFrom.getCellStyle());
cellTo.setCellType(cellFrom.getCellType());
// タイトル内容のコピー
// 不同数据类型处理
int cellFromType = cellFrom.getCellType();
cellTo.setCellType(cellFromType);
if (cellFromType == HSSFCell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cellFrom)) {
cellTo.setCellValue(cellFrom.getDateCellValue());
} else {
cellTo.setCellValue(cellFrom.getNumericCellValue());
}
} else if (cellFromType == HSSFCell.CELL_TYPE_STRING) {
cellTo.setCellValue(cellFrom.getRichStringCellValue());
} else if (cellFromType == HSSFCell.CELL_TYPE_BLANK) {
// nothing21
} else if (cellFromType == HSSFCell.CELL_TYPE_BOOLEAN) {
cellTo.setCellValue(cellFrom.getBooleanCellValue());
} else if (cellFromType == HSSFCell.CELL_TYPE_ERROR) {
cellTo.setCellErrorValue(cellFrom.getErrorCellValue());
} else if (cellFromType == HSSFCell.CELL_TYPE_FORMULA) {
cellTo.setCellFormula(cellFrom.getCellFormula());
} else { // nothing29
}
}
}
// 枠線の設定
sheetTo.setDisplayGridlines(false);
// sheetTo.setDisplayGuts(true);
// sheetTo.setDisplayRowColHeadings(true);
// 剪切
// sheetTo.shiftRows(13, 15, 31, false, false, false);
// Excelのズーム設定
sheetTo.setZoom(85, 100);
// シートを戻る。
return sheetTo;
}
Example 3
| Source File: ImportDemoDataServiceImpl.java From axelor-open-suite with GNU Affero General Public License v3.0 | 6 votes |
|
private boolean validateHeader(Sheet sheet, StringBuilder errorList) throws IOException {
boolean flag = true;
Row headerRow = sheet.getRow(3);
if (headerRow != null) {
for (int cell = 1; cell < headerRow.getLastCellNum(); cell++) {
Cell headerCell = headerRow.getCell(cell);
if (headerCell == null || headerCell.getCellType() != Cell.CELL_TYPE_STRING) {
errorList.append("\n" + I18n.get(IExceptionMessage.INVALID_HEADER));
flag = false;
}
}
} else {
errorList.append("\n" + I18n.get(IExceptionMessage.INVALID_HEADER));
flag = false;
}
return flag;
}
Example 4
| Source File: TemplateExcelParseServiceImpl.java From qconfig with MIT License | 6 votes |
|
private String readCellAsString(final Cell cell) {
if (cell == null) {
return "";
}
switch (cell.getCellType()) {
case CELL_TYPE_STRING:
return cell.getStringCellValue();
case CELL_TYPE_BLANK:
return "";
case CELL_TYPE_BOOLEAN:
return Boolean.toString(cell.getBooleanCellValue());
case CELL_TYPE_NUMERIC:
final DataFormatter formatter = new DataFormatter();
return formatter.formatCellValue(cell);
default:
throw new RuntimeException("unknown cell type " + cell.getCellType());
}
}
Example 5
| 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 6
| Source File: PoiSheetUtility.java From jeasypoi with Apache License 2.0 | 5 votes |
|
private static void cloneCell(Cell cNew, Cell cOld) {
cNew.setCellComment(cOld.getCellComment());
cNew.setCellStyle(cOld.getCellStyle());
switch (cNew.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN: {
cNew.setCellValue(cOld.getBooleanCellValue());
break;
}
case Cell.CELL_TYPE_NUMERIC: {
cNew.setCellValue(cOld.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_STRING: {
cNew.setCellValue(cOld.getStringCellValue());
break;
}
case Cell.CELL_TYPE_ERROR: {
cNew.setCellValue(cOld.getErrorCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA: {
cNew.setCellFormula(cOld.getCellFormula());
break;
}
}
}
Example 7
| 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 8
| Source File: ExcelExportOfTemplateUtil.java From easypoi with Apache License 2.0 | 5 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 9
| 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 10
| Source File: Utils.java From Excel4J with Apache License 2.0 | 5 votes |
|
/**
* 获取单元格内容
*
* @param c 单元格
* @return 单元格内容
*/
public static String getCellValue(Cell c) {
String o;
switch (c.getCellType()) {
case BLANK:
o = "";
break;
case BOOLEAN:
o = String.valueOf(c.getBooleanCellValue());
break;
case FORMULA:
o = calculationFormula(c);
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(c)) {
o = DateUtils.date2Str(c.getDateCellValue());
} else {
o = String.valueOf(c.getNumericCellValue());
o = matchDoneBigDecimal(o);
o = RegularUtils.converNumByReg(o);
}
break;
case STRING:
o = c.getStringCellValue();
break;
default:
o = null;
break;
}
return o;
}
Example 11
| Source File: CellValueHelper.java From autopoi 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 12
| Source File: SpreadsheetData.java From Selenium-WebDriver-3-Practical-Guide-Second-Edition with MIT License | 5 votes |
|
public String[][] getCellData(String path) throws InvalidFormatException, IOException {
System.out.println(path);
FileInputStream stream = new FileInputStream(path);
Workbook workbook = WorkbookFactory.create(stream);
System.out.println(workbook);
Sheet s = workbook.getSheetAt(0);
System.out.println(s);
int rowcount = s.getLastRowNum();
int cellcount = s.getRow(0).getLastCellNum();
System.out.println(rowcount);
String data[][] = new String[rowcount][cellcount];
for (int rowCnt = 1; rowCnt <= rowcount; rowCnt++) {
Row row = s.getRow(rowCnt);
for (int colCnt = 0; colCnt < cellcount; colCnt++) {
Cell cell = row.getCell(colCnt);
try {
if (cell.getCellType() == cell.CELL_TYPE_STRING) {
data[rowCnt - 1][colCnt] = cell.getStringCellValue();
} else {
data[rowCnt - 1][colCnt] = String.valueOf(cell.getNumericCellValue());
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
return data;
}
Example 13
| Source File: ImportDemoDataServiceImpl.java From axelor-open-suite with GNU Affero General Public License v3.0 | 5 votes |
|
private boolean validateCell(Cell cell, StringBuilder errorList, String cellName)
throws IOException {
if (cell == null || cell.getCellType() != Cell.CELL_TYPE_STRING) {
errorList.append(String.format("\n" + I18n.get(IExceptionMessage.CELL_NOT_VALID), cellName));
return false;
}
return true;
}
Example 14
| 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 15
| Source File: ExcelUtils.java From components with Apache License 2.0 | 5 votes |
|
public static boolean isEmptyRow4Stream(Row row) {
if (row == null) {
return true;
}
for (Cell cell : row) {
if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK && StringUtils.isNotBlank(cell.toString())) {
return false;
}
}
return true;
}
Example 16
| Source File: ExcelQuery.java From micro-integrator with Apache License 2.0 | 5 votes |
|
private String[] extractRowData(Row row) {
if (row == null || row.getLastCellNum() == -1) {
return null;
}
String[] data = new String[row.getLastCellNum()];
Cell cell;
for (int i = 0; i < data.length; i++) {
cell = row.getCell(i);
if (cell == null) {
data[i] = "";
continue;
}
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
data[i] = cell.getRichStringCellValue().getString();
break;
case HSSFCell.CELL_TYPE_BLANK:
data[i] = "";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
data[i] = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
data[i] = "{formula}";
break;
case HSSFCell.CELL_TYPE_NUMERIC:
data[i] = processNumericValue(cell.getNumericCellValue());
break;
}
}
return data;
}
Example 17
| Source File: AbstractExcelExtractor.java From wandora with GNU General Public License v3.0 | 5 votes |
|
public Topic getCellTypeTopic(Cell cell, TopicMap tm) throws TopicMapException {
int type = cell.getCellType();
String typeStr = "string";
switch(type) {
case Cell.CELL_TYPE_BLANK: {
typeStr = "blank";
break;
}
case Cell.CELL_TYPE_BOOLEAN: {
typeStr = "boolean";
break;
}
case Cell.CELL_TYPE_ERROR: {
typeStr = "error";
break;
}
case Cell.CELL_TYPE_FORMULA: {
typeStr = "formula";
break;
}
case Cell.CELL_TYPE_NUMERIC: {
typeStr = "numeric";
break;
}
case Cell.CELL_TYPE_STRING: {
typeStr = "string";
break;
}
}
Topic t = getOrCreateTopic(tm, EXCEL_CELL_TYPE_SI_PREFIX+"/"+typeStr, "Excel cell type "+typeStr);
t.addType(getCellTypeTypeTopic(tm));
return t;
}
Example 18
| 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 19
| Source File: OptionsFileConverterUtil.java From sakai with Educational Community License v2.0 | 4 votes |
|
public static List<String> convertInputStreamToOptionList(InputStream in) throws IOException {
List<String> optionsList = new ArrayList<String>();
try (BufferedInputStream bufferedInputStream = new BufferedInputStream(in)) {
Iterator<Row> iterator;
switch(FileMagic.valueOf(bufferedInputStream)) {
case OOXML:
log.debug("Input file detected as OOXML.");
XSSFWorkbook workbook = new XSSFWorkbook(bufferedInputStream);
XSSFSheet datatypeSheet = workbook.getSheetAt(0);
iterator = datatypeSheet.iterator();
break;
case OLE2:
log.debug("Input file detected as OLE2.");
HSSFWorkbook lagacyWorkbook = new HSSFWorkbook(bufferedInputStream);
HSSFSheet legacyDatatypeSheet = lagacyWorkbook.getSheetAt(0);
iterator = legacyDatatypeSheet.iterator();
break;
default:
log.debug("Input file detected as UNKNOWN, try to open it as text and ignore if it's not ASCII text.");
try(Scanner scanner = new Scanner(bufferedInputStream).useDelimiter("\\r\\n")) {
while(scanner.hasNext()){
String inputString = HtmlUtils.htmlEscape(scanner.next(), "UTF-8");
if(StringUtils.isNotBlank(inputString)){
optionsList.add(inputString);
}
}
} catch(Exception ex){
throw new IOException("Error processing the file as text type.", ex);
}
return optionsList;
}
while (iterator.hasNext()) {
Row currentRow = iterator.next();
Iterator<Cell> cellIterator = currentRow.iterator();
if(cellIterator.hasNext()) {
Cell currentCell = cellIterator.next();
switch(currentCell.getCellType()) {
case STRING:
if (StringUtils.isNotBlank(currentCell.getStringCellValue())) {
optionsList.add(HtmlUtils.htmlEscape(currentCell.getStringCellValue(), "UTF-8"));
}
break;
case NUMERIC:
optionsList.add(String.valueOf(currentCell.getNumericCellValue()));
break;
case BOOLEAN:
optionsList.add(currentCell.getBooleanCellValue() ? "1" : "0");
break;
case FORMULA:
case BLANK:
case _NONE:
case ERROR:
default:
break;
}
}
}
} catch (Exception e) {
throw new IOException("Error converting the file to options list.");
}
return optionsList;
}
Example 20
| Source File: ExcelUtil.java From util with Apache License 2.0 | 4 votes |
|
/**
* 读取某个工作簿上的所有单元格的值。
* 作者：朱志杰
* July 30, 2013 3:27:08 PM
* @param sheetOrder 工作簿序号，从0开始。
* @return List<Object[]> 所有单元格的值。
* @throws IOException 加载excel文件IO异常。
* @throws FileNotFoundException excel文件没有找到异常。
* @throws InvalidFormatException
*/
public List<Object[]> read(int sheetOrder) throws FileNotFoundException, IOException, InvalidFormatException{
FileInputStream fis = new FileInputStream(path);
Workbook workbook = WorkbookFactory.create(fis);
if(fis != null) {
fis.close();
}
Sheet sheet = workbook.getSheetAt(sheetOrder);
//用来记录excel值
List<Object[]> valueList=new LinkedList<Object[]>();
//循环遍历每一行、每一列。
for(Row row : sheet){
//每一行
Object[] rowObject=null;
for(Cell cell : row){
//cell.getCellType是获得cell里面保存的值的type
switch(cell.getCellType()){
case Cell.CELL_TYPE_BOOLEAN:
//得到Boolean对象的方法
rowObject=CollectionUtil.addObjectToArray(rowObject, cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
//先看是否是日期格式
if(DateUtil.isCellDateFormatted(cell)){
//读取日期格式
rowObject=CollectionUtil.addObjectToArray(rowObject, cell.getDateCellValue());
}else{
DecimalFormat df = new DecimalFormat();
//单元格的值,替换掉,
String value=df.format(cell.getNumericCellValue()).replace(",","");
//读取数字
rowObject=CollectionUtil.addObjectToArray(rowObject, value);
}
break;
case Cell.CELL_TYPE_FORMULA:
//读取公式
rowObject=CollectionUtil.addObjectToArray(rowObject, cell.getCellFormula());
break;
case Cell.CELL_TYPE_STRING:
//读取String
rowObject=CollectionUtil.addObjectToArray(rowObject, cell.getRichStringCellValue().toString());
break;
}
}
//将这行添加到list。
valueList.add(rowObject);
}
return valueList;
}
