VERY simplified Java Excel manipulation using "jxl": I found 'jxl' api to be very powerful, but very unintuative and much of it is left for *you* to figure out. For example, there's no simple call to make a cell italic, instead it's up to you to extract the correct sequence of objects, make your change to the correct one then re-assemble (an re-…

gistfile1.java

import jxl.CellType;
import jxl.CellView;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableFont.FontName;
import jxl.write.WritableSheet;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * Enable easier manipulation of Excel (because I found "jxl" api to be very 
 * unintuative).
 * @author Roy Collings
 *
 */
public class RoyExcelUtils {

	/**
	 * Decided to have a single call to this in case jxl changes in some way.
	 * @param $colNum
	 * @param $rowNum
	 * @param $writableSheet
	 * @return A writable cell object with no validation to see if it exists.
	 */
	public static WritableCell getWCell(int $colNum, int $rowNum, WritableSheet $writableSheet){
		 return $writableSheet.getWritableCell($colNum, $rowNum);
	}
	
	/**
	 * Test if a cell is 'there' (without creating it).
	 * @param $writableCell
	 * @return
	 */
	public static boolean isCell(WritableCell $writableCell){
		return ($writableCell.getType() == CellType.LABEL) ? true : false;
	}
	
	/**
	 *  
	 * @param $writableCell
	 * @return The format from another cell (so you can copy it into a new cell for example).
	 */
	public static WritableCellFormat getFormatFrom(WritableCell $writableCell) {
		return new WritableCellFormat($writableCell.getCellFormat());
	}
	
	/**
	 * 
	 * @param $writableCell
	 * @return The font from another cell (so you can copy it into a new cell for example).
	 */
	public static WritableFont getFontFrom(WritableCell $writableCell) {
		return new WritableFont($writableCell.getCellFormat().getFont());
	}
	
	/**
	 * 
	 * @param $colNum
	 * @param $rowNum
	 * @param $writableSheet
	 * @return WritableCell A 'valid' writable cell - it it doesn't exist it will be created - what you get back from here, you can write to.
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	public static WritableCell getValidWritableCell(int $colNum, int $rowNum, WritableSheet $writableSheet) throws RowsExceededException, WriteException{
		WritableCell cell = getWCell($colNum, $rowNum, $writableSheet);
		
		if (! isCell(cell)){
			// Cell doesn't exist (well, it's empty), we need to 'add' the cell and re-reference.
			$writableSheet.addCell(new Label($colNum, $rowNum, "")); 
			cell = getWCell($colNum, $rowNum, $writableSheet);
		}
		
		return cell;
	}

	/**
	 * 
	 * If you format just the column, then the
	 * format info. isn't in the cell, it's in the column 'somehow'. 
	 * This means when you edit the formatting of the cell in any 
	 * way (e.g. just make the font bold), you'll lose all other 
	 * column formatting in this cell (it'll now be bold, but
	 * revert to the default font size / color etc...)!
	 * To correct this, before making any change to the cell formatting,
	 * read the column formatting and overwrite the cell formatting to match it.
	 * @param $colNum
	 * @param $rowNum
	 * @param $writableSheet
	 * @throws WriteException 
	 * @throws RowsExceededException 
	 */
	public static void setCellToColFormat(int $colNum, int $rowNum, WritableSheet $writableSheet) throws RowsExceededException, WriteException {
		WritableCell cell = getValidWritableCell($colNum, $rowNum, $writableSheet);
		
		CellView x = $writableSheet.getColumnView($colNum);
		cell.setCellFormat(x.getFormat());
	}
	
	/**
	 * Change cell background colour.
	 * @param $colNum
	 * @param $rowNum
	 * @param $newColour
	 * @param $writableSheet
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	public static void setCellBackground(int $colNum, int $rowNum, Colour $newColour, WritableSheet $writableSheet) throws RowsExceededException, WriteException {
		
		// Get the cell details.
		WritableCell cell = getValidWritableCell($colNum, $rowNum, $writableSheet);
		
		WritableCellFormat newFormat = new WritableCellFormat(cell.getCellFormat());
		newFormat.setBackground($newColour);
		cell.setCellFormat(newFormat);
	}
	
	/**
	 * Change cell border width and colour.
	 * @param $colNum
	 * @param $rowNum
	 * @param $newColour
	 * @param $BorderlineStyle
	 * @param $Border
	 * @param $writableSheet
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	public static void setCellBorder(int $colNum, int $rowNum, Colour $newColour, BorderLineStyle $BorderlineStyle, Border $Border, WritableSheet $writableSheet) throws RowsExceededException, WriteException {
		WritableCellFormat newFormat = null;
		
		// Get the cell details.
		WritableCell cell = getValidWritableCell($colNum, $rowNum, $writableSheet);
		
		newFormat = new WritableCellFormat(cell.getCellFormat());
		newFormat.setBorder($Border, $BorderlineStyle, $newColour);
		cell.setCellFormat(newFormat);
	}
	
	/**
	 * Change the font in a cell (without resetting all other font settings).
	 * @param $colNum
	 * @param $rowNum
	 * @param $newFontName Annoyingly, needs to be an entire object, like: "new WritableFont(WritableFont.ARIAL)" (or whatever font name you want)!
	 * @param $writableSheet
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	public static void setCellFont(int $colNum, int $rowNum, FontName $newFontName, WritableSheet $writableSheet) throws RowsExceededException, WriteException {

		WritableCell		cell 		= null;
		WritableCellFormat	oldFormat 	= null, 
					newFormat 	= null;
		WritableFont 		oldFont 	= null, 
					newFont 	= null;
		String			cellText	= null;
		
		//
		// Save the 'old' cell details.
		//
		cell 		= getValidWritableCell($colNum, $rowNum, $writableSheet);
		oldFormat	= getFormatFrom(cell);
		oldFont		= getFontFrom(cell);
		cellText	= cell.getContents();
		
		//
		// Create the new font.
		//
		newFont		= new WritableFont($newFontName);
		
		//
		// Set the new font formatting to match the old one (apart from the name).
		//
		newFont.setPointSize(oldFont.getPointSize());
		newFont.setColour(oldFont.getColour());
		newFont.setScriptStyle(oldFont.getScriptStyle());
		newFont.setUnderlineStyle(oldFont.getUnderlineStyle());
		newFont.setItalic(oldFont.isItalic());
		newFont.setStruckout(oldFont.isStruckout());

		// Took a bit of figuring out!
		// ... why isn't there just a ".getBoldStyle()" that returns whatever ".setBoldStyle()" expects? Or
		// better yet, simply have BOLD set the same way you set ITALIC, with a simple boolean tag:
		// "isItalic"/"setItalic" and therefore "isBold"/"setBold"?
		if (oldFont.getBoldWeight() == 700) newFont.setBoldStyle(WritableFont.BOLD);
		if (oldFont.getBoldWeight() == 400) newFont.setBoldStyle(WritableFont.NO_BOLD);
		
		// Recreate the old cell format and attach the new font to it.
		newFormat = oldFormat;
		newFormat.setFont(newFont);

		// Recreate the cell with the new format details.
		Label headerLabel = new Label($colNum, $rowNum, cellText, newFormat);
		$writableSheet.addCell(headerLabel);
	}

	//
	// Part 1 of the complicated juggling act you need to perform to change a
	// format setting on a cell without losing the previous format settings.
	//
	private static WritableFont setCell_part1(int $colNum, int $rowNum, WritableSheet $writableSheet) throws RowsExceededException, WriteException{
		// Get the current cell details.
		WritableCell cell = getValidWritableCell($colNum, $rowNum, $writableSheet);

		// Return the font object from the format object of that cell object.
		return new WritableFont(cell.getCellFormat().getFont());
	}
	
	//
	// Part 2 of the complicated juggling act you need to perform to change a
	// format setting on a cell without losing the previous format settings.
	//
	private static void setCell_part2(WritableFont $newFont, int $colNum, int $rowNum, WritableSheet $writableSheet) throws RowsExceededException, WriteException {
		WritableCell cell = getValidWritableCell($colNum, $rowNum, $writableSheet);
		WritableCellFormat newFormat = null;
		newFormat = new WritableCellFormat(cell.getCellFormat());
		newFormat.setFont($newFont); // add the changed font to this new format.
		cell.setCellFormat(newFormat);
	}
	
	/**
	 * Set the cell font colour for a cell.
	 * @param $colNum
	 * @param $rowNum
	 * @param $newColour
	 * @param $writableSheet
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	public static void setCellFontColour(int $colNum, int $rowNum, Colour $newColour, WritableSheet $writableSheet) throws RowsExceededException, WriteException {
		// Get the current font.
		WritableFont newFont = setCell_part1($colNum, $rowNum, $writableSheet);
		
		// Make the required change.
		newFont.setColour($newColour);
		
		// Write the change to the cell.
		setCell_part2(newFont, $colNum, $rowNum, $writableSheet);
	}
	

	/**
	 * Change the italic setting for a font in a cell.
	 * @param $colNum
	 * @param $rowNum
	 * @param $italic
	 * @param $writableSheet
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	public static void setCellFontItalics(int $colNum, int $rowNum, boolean $italic, WritableSheet $writableSheet) throws RowsExceededException, WriteException {
		// Get the current font.
		WritableFont newFont = setCell_part1($colNum, $rowNum, $writableSheet);
		
		// Make the required change.
		newFont.setItalic($italic);
		
		// Write the change to the cell.
		setCell_part2(newFont, $colNum, $rowNum, $writableSheet);
	}
	
	/**
	 * Change the bold setting for a font in a cell.
	 * @param $colNum
	 * @param $rowNum
	 * @param $bold
	 * @param $writableSheet
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	public static void setCellFontBold(int $colNum, int $rowNum, boolean $bold, WritableSheet $writableSheet) throws RowsExceededException, WriteException {
		// Get the current font.
		WritableFont newFont = setCell_part1($colNum, $rowNum, $writableSheet);
		
		// Make the required change.
		newFont.setBoldStyle($bold ? WritableFont.BOLD : WritableFont.NO_BOLD); //(no "setBold" meythod?)
		
		// Write the change to the cell.
		setCell_part2(newFont, $colNum, $rowNum, $writableSheet);
	}
	
	/**
	 * Set the size of fonts in a cell.
	 * @param $colNum
	 * @param $rowNum
	 * @param $fontSize
	 * @param $writableSheet
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	public static void setCellFontSize(int $colNum, int $rowNum, int $fontSize, WritableSheet $writableSheet) throws RowsExceededException, WriteException {
		// Get the current font.
		WritableFont newFont = setCell_part1($colNum, $rowNum, $writableSheet);
		
		// Make the required change.
		newFont.setPointSize($fontSize);
		
		// Write the change to the cell.
		setCell_part2(newFont, $colNum, $rowNum, $writableSheet);
	}
	
	/**
	 * Set some basic formats to a column.
	 * @param $atColumn
	 * @param $width
	 * @param $font
	 * @param $bold
	 * @param $italic
	 * @param $fontSize
	 * @param $fontColour
	 * @param $vAlignment
	 * @param $hAlignment
	 * @param $writableSheet
	 * @throws WriteException
	 */
	public static void setColumnFormat(int $atColumn, int $width, WritableFont $font, boolean $bold, boolean $italic, int $fontSize, Colour $fontColour, VerticalAlignment $vAlignment, Alignment $hAlignment, WritableSheet $writableSheet) throws WriteException{
		
		// Set the font format.
		WritableFont resFont = new WritableFont($font);
		resFont.setPointSize($fontSize);
		resFont.setColour($fontColour);
		resFont.setBoldStyle($bold ? WritableFont.BOLD : WritableFont.NO_BOLD);
		resFont.setItalic($italic);
		
		// Set the cell format.
		WritableCellFormat resCell = new WritableCellFormat();
		resCell.setFont(resFont);
		resCell.setWrap(true);
		resCell.setAlignment($hAlignment);
		resCell.setVerticalAlignment($vAlignment);
		
		// Do this strange thing to apply these formats to the column.
		CellView cv = new CellView();
		cv.setFormat(resCell);
		cv.setSize($width);
		
		$writableSheet.setColumnView($atColumn, cv);
	}
}