Take arbitrary CSV data and transform it into a "real" Excel worksheet using Apache POI. Why? Because sometimes you just can't lose leading zero's on a field that looks like a number.

CsvToExcelTransformer.java

// Gradle dep's
// compile 'org.apache.poi:poi:3.12'
//compile 'com.opencsv:opencsv:3.5'
//compile 'log4j:log4j:1.2.14'
package util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.StringReader;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.WorkbookUtil;
import org.springframework.util.Assert;

import com.opencsv.CSVReader;

/**
 * Take free-form CSV text and transform to an Excel spreadsheet. This would all
 * seem pretty pointless until you consider that we have numeric values that
 * need to retain their leading zeroes in the final report (e.g. client
 * numbers). To do that we need to force the Excel cell format to TEXT for all
 * the cells.
 * 
 * Currently this class works with concrete java.io.File objects instead of
 * streams, but we may want to look into changing that depending on report
 * sizes.
 * 
 * @author ranichol
 *
 */
public class CsvToExcelTransformer {

	protected static final String GENERATED_REPORT = "Generated Report";

	private static final Logger _log = Logger
			.getLogger(CsvToExcelTransformer.class);

  // just pulled a number out of my *** that is bigger than any report my app produces. 
	private final static int NUM_COLUMNS_TO_AUTOSIZE = 60;

	/**
	 * Take CSV [comma-separated values] as a String of text, and return a
	 * Microsoft Excel file.
	 * 
	 * @param csvText
	 * @return Excel worksheet (.XLS) file
	 * @throws IOException
	 */
	public File transform(String csvText) throws IOException {
		Assert.hasText(csvText);
		if (_log.isDebugEnabled()) {
			_log.debug(String
					.format("Received CSV text to transform into an Excel spreadsheet: %s",
							csvText));
		}
		File xlFile = null;
		FileOutputStream fileOut = null;
		Workbook wb = new HSSFWorkbook();
		Sheet sheet1 = wb.createSheet(WorkbookUtil
				.createSafeSheetName(GENERATED_REPORT));

		CSVReader reader = new CSVReader(new StringReader(csvText));

		String[] nextLine;
		int rr = 0;
		try {
			while ((nextLine = reader.readNext()) != null) {
				Row row = sheet1.createRow(rr);
				for (int col = 0; col < nextLine.length; col++) {
					Cell cell0 = row.createCell(col, Cell.CELL_TYPE_STRING);
					cell0.setCellValue(nextLine[col]);
				}
				++rr;
			}
			// make sure each column is sized to the longest value
			for (int col = 0; col < NUM_COLUMNS_TO_AUTOSIZE; col++) {
				sheet1.autoSizeColumn(col);
			}
			xlFile = File.createTempFile("generatedReport_", ".xls");
			fileOut = new FileOutputStream(xlFile);
			wb.write(fileOut);
		} finally {
			try {
				wb.close();
			} catch (Throwable t) {
				_log.warn("Failed to close (Apache POI) Excel Workbook.", t);
			}
			try {
				reader.close();
			} catch (Throwable t) {
				_log.warn("Failed to close CSV Reader.", t);
			}
			try {
				fileOut.close();
			} catch (Throwable t) {
				_log.warn("Failed to close FileOutputStream for Excel workbook", t);
			}
		}
		return xlFile;
	}
}