Thread: Excel file manipulation simplified (without using jxl directly).

Hi all,

Perhaps I'm just a bit slow, but it took me just 1 day to learn Java enough to start writing some pretty meaty applications, but almost 2 days to get to grips with basic Excel manipulation via jxl! The online api docs are very basic, leaving you to 'guess' ("public int getBoldWeight()" ... "returns the bold weight for this font" ... er ... helpful ?) and lots of the methods have no javadoc comments to help you understand what's required.
Because of this I ended up creating a class that takes the 'learning' out of some of the simple actions I needed to perform again spreadsheets and I thought I'd share it here in case it's helpful to someone else.

Firstly, a couple of 'gotchas' I came across (there's more, but it's the weekend!):

1. WritableWorkbook.setColourRGB(Colour.BLACK, 200, 100, 50); ... will not work for "Colour.BLACK". It won't fail, it'll just not change it, I guess it's a read-only colour type (other colours work fine).
2. if you format a column, the formatting isn't applied automatically cells in that column. They will look correct, but if you change just, say, the font size of a cell, then the cell will revert totally back to workbook defaults (apart from the font size you just set). This isn't the case if you format a cell, so you might need to copy the column format to the cell before making your change.

Okay, so here's my class which I find makes basic tasks a LOT more friendly (if I can figure out how to attach it in a file I will ;o) - it was built using jxl 2.6.12. (for the record, the methods that change bold, italic etc... do so without losing the current formatting). Sorry about the code layout, for some reason all my indenting vanished when I pasted it:

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.
** @author Roy Collings
**
**/
public class RoyExcelUtils {
 
	/**
	 * Just returns a writable cell object with no validation 
	 * (decided to have a single call to this in case jxl changes in some way).
	 * @param $colNum
	 * @param $rowNum
	 * @param $writableSheet
	 * @return
	 */
	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 if the cell does not exist, it is created first.
	 * @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??
		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);
	}
 
	/**
	 * 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 {
 
		WritableCellFormat newFormat = null;
		WritableFont newFont = null;
 
		// Get the cell details.
		WritableCell cell = getValidWritableCell($colNum, $rowNum, $writableSheet);
 
		// Bit of a juggling act to change the cell and font colors!
		newFont = new WritableFont(cell.getCellFormat().getFont());
		newFont.setColour($newColour);
		newFormat = new WritableCellFormat(cell.getCellFormat());
		newFormat.setFont(newFont); // add the changed font to this new format.
		cell.setCellFormat(newFormat);
	}
 
 
	/**
	 * 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 {
 
		WritableCellFormat newFormat = null;
		WritableFont newFont   = null;
 
		// Get the cell details.
		WritableCell cell = getValidWritableCell($colNum, $rowNum, $writableSheet);
 
		// Bit of a juggling act to change the cell and font colors!
		newFont = new WritableFont(cell.getCellFormat().getFont());
		newFont.setItalic($italic);
		newFormat = new WritableCellFormat(cell.getCellFormat());
		newFormat.setFont(newFont); // add the changed font to this new format.
		cell.setCellFormat(newFormat);
	}
 
	/**
	 * 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 {
 
		WritableCellFormat newFormat = null;
		WritableFont newFont   = null;
 
		// Get the cell details.
		WritableCell cell = getValidWritableCell($colNum, $rowNum, $writableSheet);
 
		// Bit of a juggling act to change the cell and font colors!
		newFont = new WritableFont(cell.getCellFormat().getFont());
		newFont.setBoldStyle($bold ? WritableFont.BOLD : WritableFont.NO_BOLD);
		newFormat = new WritableCellFormat(cell.getCellFormat());
		newFormat.setFont(newFont); // add the changed font to this new format.
		cell.setCellFormat(newFormat);
	}
 
	/**
	 * 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 {
 
		WritableCellFormat newFormat = null;
		WritableFont newFont = null;
 
		// Get the cell details.
		WritableCell cell = getValidWritableCell($colNum, $rowNum, $writableSheet);
 
		// Bit of a juggling act to change the cell and font colors!
		newFont = new WritableFont(cell.getCellFormat().getFont());
		newFont.setPointSize($fontSize);
		newFormat = new WritableCellFormat(cell.getCellFormat());
		newFormat.setFont(newFont); // add the changed font to this new format.
		cell.setCellFormat(newFormat);
	}
 
	/**
	 * 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();// _sheetOUT.getColumnView(_putResultsBeforeColumn);
		cv.setFormat(resCell);
		cv.setSize($width);
 
		$writableSheet.setColumnView($atColumn, cv);
	}
}