Created Excel file is missing something

[Matthew Busse]

Hello Java geniuses,

I'm using Apache POI to work with excel files. I'm trying to read an excel file (xlsx) and copy it to a new file (also xlsx) with a few rows and extra information added. This new file is then loaded into another web app in development by other people. The new file that is created is not being loaded by the web app, and it seems like the new file is missing something. For one thing, the file size is 8 kb, whereas the file size of the starting file is 53 kb, so I'm guessing when I'm writing the new file, I'm not adding some sort of information that is normally present in excel files. Any ideas?

Here's the code:

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
		ArrayList<String> dataTypes = this.getDataTypes(sourceWb, sourceSheet);
		
		//create the new, formatted workbook
		Workbook destWb = new XSSFWorkbook();
		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 ArrayList<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);
		ArrayList<String> dataTypes = new ArrayList<String>();
		
		for (Cell cell: dataTypeRow) {
			CellValue sourceCellValue = evaluator.evaluate(cell);
			switch (sourceCellValue.getCellType()){
			case Cell.CELL_TYPE_STRING:
				dataTypes.add("text");
				break;
			case Cell.CELL_TYPE_NUMERIC:
				dataTypes.add("float");
				break;
			default:
				break;
			}
		}
		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, ArrayList<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);
			}
		}
		
	}
	

}

[Paul Clapham]

First step: see if you can open the output of that code in Excel. Then use the eyeball check to see if it contains the right information, comparing it to the spreadsheet you used as the input.

[Matthew Busse]

Hello,

Thanks for your response. It turns out the file is fine. My mistake was assuming the other application took a .xlsx file, when it actually only takes .xls.

Thanks anyway!