Blank cells in Excel causing problems

[Matthew Busse]

Hello,

I'm trying to use Apache POI to read an excel file, then copy it to a new file with some extra information added, specifically, information about the types of data in the excel sheet. It's almost working, but I'm having problems with blank cells, my program doesn't seem to recognize them. I tried adding a case (line 61) to specifically act on blank cells. And even if that doesn't work, I would expect default to catch anything else, but neither solution is able to recognize a blank cell and insert "unknown" into the HashMap. Any ideas?

Thanks!

public class ExcelHTLFormater {

	public void formatExcel(File testFile, String destFile) throws InvalidFormatException, IOException {
		
		//get the source excel workbook, workbookFactory allows either xls or xlsx files
		FileInputStream fileIn = new FileInputStream(testFile);
		Workbook sourceWb = WorkbookFactory.create(fileIn);
		Sheet sourceSheet = sourceWb.getSheetAt(0);
		
		//first, get data types of the first row of data, store in array
		HashMap<Integer,String> dataTypes = this.getDataTypes(sourceWb, sourceSheet);
		
		//create the new, formatted workbook
		Workbook destWb = new HSSFWorkbook();
		Sheet destSheet = destWb.createSheet("Sheet1");
		
		//copy the first row of headers
		this.copyFirstRow (sourceSheet, destSheet);
		
		//add 3 new rows, the first row contains the datatypes
		this.insertNewRows (destSheet, dataTypes);
		
		this.copyRemainingRows (sourceSheet, destSheet);
		
		FileOutputStream fileOut = new FileOutputStream(destFile);
		destWb.write(fileOut);
		fileOut.close();
		
		
	}

	//loop through 1st row of data (row 2) and get data type
	private HashMap<Integer,String> getDataTypes (Workbook sourceWb, Sheet sourceSheet) {
		FormulaEvaluator evaluator = sourceWb.getCreationHelper().createFormulaEvaluator();
		//check the 1st row of data for the datatype
		Row dataTypeRow = sourceSheet.getRow(1);
		HashMap<Integer,String> dataTypes = new HashMap<Integer, String>();
		
		for (int j = 0; j < dataTypeRow.getLastCellNum(); j++) {
			Cell cell = dataTypeRow.getCell(j);
			CellValue sourceCellValue = evaluator.evaluate(cell);
			if (cell != null) {
				switch (sourceCellValue.getCellType()){
				case Cell.CELL_TYPE_STRING:
					dataTypes.put(j,"text");
					break;
				case Cell.CELL_TYPE_NUMERIC:
					//first check if the cell is a date
					if (DateUtil.isCellDateFormatted(cell)){
						dataTypes.put(j,"date");
					} else {
						//if not date, check if the number is an int or float
						int testInt = (int)sourceCellValue.getNumberValue();
						if (testInt == sourceCellValue.getNumberValue()) {
							dataTypes.put(j,"sint");
						} else {
							dataTypes.put(j,"sfloat");
						}
					}
					break;
				case Cell.CELL_TYPE_BLANK:
					dataTypes.put(j,"unknown");
					break;
				default:
					dataTypes.put(j,"unknown");
					break;
				}
			}
		}
		System.out.println(dataTypes.toString());
		return dataTypes;		
	}
	
	private void copyFirstRow(Sheet sourceSheet, Sheet destSheet) {
		Row destRow = destSheet.createRow(0);
		Row sourceRow = sourceSheet.getRow(0);
		this.copyRow(sourceRow, destRow);
	}
	
	private void copyRow(Row sourceRow, Row destRow) {
		for (int j = sourceRow.getFirstCellNum(); j <= sourceRow.getLastCellNum(); j++) {
			Cell cellToCopy = sourceRow.getCell(j);  
            Cell destCell = destRow.getCell(j);
            if (cellToCopy != null) {  
                if (destCell == null) {  
                    destCell = destRow.createCell(j);  
                }
                this.copyCell(cellToCopy, destCell);
            }
		}
	}

	private void copyCell(Cell cellToCopy, Cell destCell) {
		switch(cellToCopy.getCellType()) {  
        case Cell.CELL_TYPE_STRING:  
            destCell.setCellValue(cellToCopy.getStringCellValue());  
            break;  
        case Cell.CELL_TYPE_NUMERIC:  
        	destCell.setCellValue(cellToCopy.getNumericCellValue());  
            break;  
        case Cell.CELL_TYPE_BLANK:  
        	destCell.setCellType(Cell.CELL_TYPE_BLANK);  
            break;  
        case Cell.CELL_TYPE_BOOLEAN:  
        	destCell.setCellValue(cellToCopy.getBooleanCellValue());  
            break;  
        case Cell.CELL_TYPE_ERROR:  
        	destCell.setCellErrorValue(cellToCopy.getErrorCellValue());  
            break;  
        case Cell.CELL_TYPE_FORMULA:  
        	destCell.setCellFormula(cellToCopy.getCellFormula());  
            break;  
        default:  
            break;  
		}
		
	}
	
	private void insertNewRows(Sheet destSheet, HashMap<Integer,String> dataTypes) {
		Row destRow1 = destSheet.createRow(1);
		for (int j = 0; j < dataTypes.size(); j++ ) {
			Cell destCell = destRow1.createCell(j);
			destCell.setCellValue(dataTypes.get(j));
		}
		
		//create 2 empty rows filled with blank cells, to avoid possible null pointers
		Row destRow2 = destSheet.createRow(2);
		for (int j = 0; j < dataTypes.size(); j++ ) {
			Cell destCell = destRow2.createCell(j);
			destCell.setCellType(Cell.CELL_TYPE_BLANK);
		}
		Row destRow3 = destSheet.createRow(2);
		for (int j = 0; j < dataTypes.size(); j++ ) {
			Cell destCell = destRow3.createCell(j);
			destCell.setCellType(Cell.CELL_TYPE_BLANK);
		}
	}
		

	private void copyRemainingRows(Sheet sourceSheet, Sheet destSheet) {
		//start copying at row 2 (already copied header)
		for (int j = 1; j <= sourceSheet.getLastRowNum(); j++){
			Row sourceRow = sourceSheet.getRow(j);
			//start pasting at row 5, after header and 3 new rows
			Row destRow = destSheet.createRow(j+3);
			if (sourceRow != null) {
				this.copyRow(sourceRow, destRow);
			}
		}
		
	}
	

}

[Maciej Matiaszowski]

I think that a cell is blank only when it's null. Consider using

org.apache.commons.lang.StringUtils.isBlank(cellToCopy.getStringCellValue())

in case of CELL_TYPE_STRING.

[Matthew Busse]

Thanks for your help. Alas, that didn't work. I added a println, to check what's going on, it doesn't seem like the blank cell is being recognized as a string or number case, so I still don't understand why the default isn't catching it?

Thanks!

private HashMap<Integer,String> getDataTypes (Workbook sourceWb, Sheet sourceSheet) {
		FormulaEvaluator evaluator = sourceWb.getCreationHelper().createFormulaEvaluator();
		//check the 1st row of data for the datatype
		Row dataTypeRow = sourceSheet.getRow(1);
		HashMap<Integer,String> dataTypes = new HashMap<Integer, String>();
		
		for (int j = 0; j < dataTypeRow.getLastCellNum(); j++) {
			Cell cell = dataTypeRow.getCell(j);
			CellValue sourceCellValue = evaluator.evaluate(cell);
			if (cell != null) {
				switch (sourceCellValue.getCellType()){
				case Cell.CELL_TYPE_STRING:
					System.out.println(cell.getStringCellValue());
					//first check if the cell is blank
					if (org.apache.commons.lang3.StringUtils.isBlank(cell.getStringCellValue())) {
						
						
						dataTypes.put(j, "unknown");
					} else {					
						dataTypes.put(j,"text");
						break;
					}
				case Cell.CELL_TYPE_NUMERIC:
					System.out.println(sourceCellValue.getNumberValue());
					//first check if the cell is a date
					if (DateUtil.isCellDateFormatted(cell)){
						dataTypes.put(j,"date");
					} else {
						//if not date, check if the number is an int or float
						int testInt = (int)sourceCellValue.getNumberValue();
						if (testInt == sourceCellValue.getNumberValue()) {
							dataTypes.put(j,"sint");
						} else {
							dataTypes.put(j,"sfloat");
						}
					}
					break;
				case Cell.CELL_TYPE_BLANK:
					dataTypes.put(j,"unknown");
					break;
				default:
					dataTypes.put(j,"unknown");
					break;
				}
			}
		}

[Tim Moores]

Just to ask the obvious: you've ascertained that "(cell != null)" is true, in other words, that "cell" is not null? If so, what value does "sourceCellValue.getCellType()" have?

[Matthew Busse]

Tim Moores wrote:Just to ask the obvious: you've ascertained that "(cell != null)" is true, in other words, that "cell" is not null? If so, what value does "sourceCellValue.getCellType()" have?

Aha! That was the problem. The blank cell is null, not Cell_Type_Blank. (Which leads me to wonder when Cell_Type_Blank comes into play. But that's a different question)
I added an else statement to handle the case when cell != null is false.

Thanks!

Here's the revised code, for the benefit of future readers:

private HashMap<Integer,String> getDataTypes (Workbook sourceWb, Sheet sourceSheet) {
		FormulaEvaluator evaluator = sourceWb.getCreationHelper().createFormulaEvaluator();
		//check the 1st row of data for the datatype
		Row dataTypeRow = sourceSheet.getRow(1);
		HashMap<Integer,String> dataTypes = new HashMap<Integer, String>();
		
		for (int j = 0; j < dataTypeRow.getLastCellNum(); j++) {
			Cell cell = dataTypeRow.getCell(j);
			CellValue sourceCellValue = evaluator.evaluate(cell);
			if (cell != null) {
//				System.out.println(sourceCellValue.getCellType());
				switch (sourceCellValue.getCellType()){
				case Cell.CELL_TYPE_STRING:
//					System.out.println(cell.getStringCellValue());
					//first check if the cell is blank
					if (org.apache.commons.lang3.StringUtils.isBlank(cell.getStringCellValue())) {
						
						
						dataTypes.put(j, "unknown");
					} else {					
						dataTypes.put(j,"text");
						break;
					}
				case Cell.CELL_TYPE_NUMERIC:
//					System.out.println(cell.getNumericCellValue());
					//first check if the cell is a date
					if (DateUtil.isCellDateFormatted(cell)){
						dataTypes.put(j,"date");
					} else {
						//if not date, check if the number is an int or float
						int testInt = (int)sourceCellValue.getNumberValue();
						
						if (testInt == sourceCellValue.getNumberValue()) {
							dataTypes.put(j,"sint");
						} else {
							dataTypes.put(j,"sfloat");
						}
					}
					break;
				case Cell.CELL_TYPE_BLANK:
					dataTypes.put(j,"unknown");
					break;
				default:
					dataTypes.put(j,"unknown");
					break;
				}
			} else {
				dataTypes.put(j, "unknown");
//				System.out.println("Cell was null");
			}
		}
//		System.out.println(dataTypes.toString());
		return dataTypes;		
	}