JExcel API's are used to create and read Excel files. Please go through the documentation's of JXL to find out more in detail. This API is very easy to use. I present a Utility below which does some basic operations using JXL API's. I do not like the checked exceptions that cant be handled, so, this utility wraps the checked exceptions in unchecked exception and throws it. A sample Client program is also given to show the usage of the utility class. The utility class handles the below features of the Excel file writings. I will update the Util as and when i find more features to be used.
Features:
1. Create a workbook.
2. Create a worksheet with given name.
3. Create Settings with US locale.
4. Create formatted cell with given font, point size.
5. Create formatted cell with bold, italic, Underline styles.
6. Create formatted cell with border and border style.
7. Create formatted cell with alignment(left, top, bottom etc).
8. Add label to sheet with or without formatting.
9. Add Integer to sheet with or without formatting.
10. Add formula to sheet.
11. Merge cells.
12. Find cells on specific data.
13. Get cell contents.
14. Wrap text data in Cell.
Below is the util class. JavaDocs does the needed explanations.
I am using JXL 2.6.8 release with JDK 5 update 15.
#################################################################
package com.ssb.jxl.util;
import java.io.File;
import java.io.IOException;
import java.util.Locale;
import jxl.Cell;
import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.UnderlineStyle;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class JxlUtil {
/**
* Class to define a formula.
* @author sacrosanctblood
*
*/
public static class Formulae{
private String formulae;
public String getFormulae() {
return formulae;
}
public void setFormulae(String formulae) {
this.formulae = formulae;
}
}
/**
* RuntimeException to wrap all the checked exceptions of JXL
*
* @author sacrosanctblood
*
*/
public static class JxlUtilException extends RuntimeException{
private static final long serialVersionUID = -1189965636139763776L;
private Exception _ex;
public JxlUtilException(Exception ex){
super(ex);
this._ex = ex;
}
public Exception getActualJExcelException(){
return _ex;
}
}
/**
* Creates a WritableWorkBook from the file given.
* @param name
* @return WritableWorkbook
*/
public WritableWorkbook createWorkBook(File name){
WritableWorkbook workbook;
try {
workbook = Workbook.createWorkbook(name);
} catch (IOException e) {
throw new JxlUtilException(e);
}
return workbook;
}
/**
* Creates a WritableWorkbook with a sheet given by sheet name.
* @param name
* @param sheetName
* @return WritableWorkbook
*/
public WritableWorkbook createWorkBook(File name,String sheetName){
WritableWorkbook workBook;
try {
workBook = Workbook.createWorkbook(name);
} catch (IOException e) {
throw new JxlUtilException(e);
}
createSheet(sheetName, workBook);
return workBook;
}
/**
* Creates a WritableWorkbook from file, with the settings given.
* @param name
* @param setting
* @return WritableWorkbook
*/
public WritableWorkbook createWorkBook(File name,WorkbookSettings setting){
WritableWorkbook workbook;
try {
workbook = Workbook.createWorkbook(name,setting);
} catch (IOException e) {
throw new JxlUtilException(e);
}
return workbook;
}
/**
* Creates a WritableWorkbook from file, with the settings given and sheetname given.
* @param name
* @param sheetName
* @param setting
* @return WritableWorkbook
*/
public WritableWorkbook createWorkBook(File name,String sheetName,WorkbookSettings setting){
WritableWorkbook workBook;
try {
workBook = Workbook.createWorkbook(name,setting);
} catch (IOException e) {
throw new JxlUtilException(e);
}
createSheet(sheetName, workBook);
return workBook;
}
/**
* Creates a WritableWorkbook from file name, with the settings given.
* @param name
* @param setting
* @return WritableWorkbook
*/
public WritableWorkbook createWorkBook(String name,WorkbookSettings setting){
WritableWorkbook workbook;
try {
workbook = Workbook.createWorkbook(new File(name),setting);
} catch (IOException e) {
throw new JxlUtilException(e);
}
return workbook;
}
/**
* Creates a WritableWorkbook from file name, with the settings given and sheetname given.
* @param name
* @param sheetName
* @param setting
* @return WritableWorkbook
*/
public WritableWorkbook createWorkBook(String name,String sheetName,WorkbookSettings setting){
WritableWorkbook workbook;
try {
workbook = Workbook.createWorkbook(new File(name),setting);
} catch (IOException e) {
throw new JxlUtilException(e);
}
createSheet(sheetName, workbook);
return workbook;
}
/**
* Creates a WritableWorkbook from file name and sheet name given.
* @param name
* @param sheetName
* @return WritableWorkbook
*/
public WritableWorkbook createWorkBook(String name,String sheetName){
WritableWorkbook workbook;
try {
workbook = Workbook.createWorkbook(new File(name));
} catch (IOException e) {
throw new JxlUtilException(e);
}
createSheet(sheetName, workbook);
return workbook;
}
/**
* Creates settings with US locale.
* @return WorkbookSettings
*/
public WorkbookSettings createSettings(){
WorkbookSettings wbSettings = new WorkbookSettings();
Locale locale = Locale.US;
wbSettings.setLocale(locale);
return wbSettings;
}
/**
* Creates sheet in the workbook with given name.
* @param sheetName
* @param workBook
*/
public void createSheet(String sheetName,WritableWorkbook workBook){
workBook.createSheet(sheetName, workBook.getNumberOfSheets());
}
/**
* Creates a cell format.
* @param pointSize
* @param fontName Can be null, default : Times
* @param isBold
* @param italic
* @param underLineStyle Can be null, default : No Underline
* @return WritableCellFormat
*/
public WritableCellFormat createFormattedCell(int pointSize, jxl.write.WritableFont.FontName fontName, boolean isBold, boolean italic, UnderlineStyle underLineStyle){
WritableFont font = new WritableFont(null!=fontName?fontName:jxl.write.WritableFont.TIMES,
pointSize,
isBold?WritableFont.BOLD:WritableFont.NO_BOLD,
italic,
null!=underLineStyle?underLineStyle:UnderlineStyle.NO_UNDERLINE
);
WritableCellFormat writableCellFormat = new WritableCellFormat(font);
return writableCellFormat;
}
/**
* Creates a cell format.
* @param pointSize
* @param fontName Can be null, default : Times
* @param isBold
* @param italic
* @param underLineStyle Can be null, default : No Underline
* @param border Can be null, default : ALL
* @param lineStyle Can be null, default : THICK
* @return WritableCellFormat
*/
public WritableCellFormat createFormattedCell(int pointSize,jxl.write.WritableFont.FontName fontName, boolean isBold, boolean italic, UnderlineStyle underLineStyle, Border border, BorderLineStyle lineStyle){
WritableFont font = new WritableFont(null!=fontName?fontName:jxl.write.WritableFont.TIMES, pointSize, isBold?WritableFont.BOLD:WritableFont.NO_BOLD, italic,null!=underLineStyle?underLineStyle:UnderlineStyle.NO_UNDERLINE);
WritableCellFormat writableCellFormat = new WritableCellFormat(font);
if(null == lineStyle){
lineStyle = BorderLineStyle.THICK;
}
if(null == border){
border = Border.ALL;
}
try {
writableCellFormat.setBorder(border, lineStyle);
} catch (WriteException e) {
throw new JxlUtilException(e);
}
return writableCellFormat;
}
/**
* Creates a cell format.
* @param pointSize
* @param fontName Can be null, default : Times
* @param isBold
* @param italic
* @param underLineStyle Can be null, default : No Underline
* @param border Can be null, default : ALL
* @param lineStyle Can be null, default : THICK
* @param alignment Can be null, default : CENTRE
* @return WritableCellFormat
*/
public WritableCellFormat createFormattedCell(int pointSize,jxl.write.WritableFont.FontName fontName, boolean isBold, boolean italic, UnderlineStyle underLineStyle, Border border, BorderLineStyle lineStyle, Alignment alignment){
WritableFont font = new WritableFont(null!=fontName?fontName:jxl.write.WritableFont.TIMES, pointSize, isBold?WritableFont.BOLD:WritableFont.NO_BOLD, italic,null!=underLineStyle?underLineStyle:UnderlineStyle.NO_UNDERLINE);
WritableCellFormat writableCellFormat = new WritableCellFormat(font);
if(null == lineStyle){
lineStyle = BorderLineStyle.THICK;
}
if(null == border){
border = Border.ALL;
}
if(null == alignment){
alignment = Alignment.CENTRE;
}
try {
writableCellFormat.setBorder(border, lineStyle);
writableCellFormat.setAlignment(alignment);
} catch (WriteException e) {
throw new JxlUtilException(e);
}
return writableCellFormat;
}
/**
* Adds the Label cell to sheet at given row and column.
*
* @param column
* @param row
* @param data
* @param format Can be null
* @param sheet
*/
public void addCellToSheet(int column, int row, String data,WritableCellFormat format,WritableSheet sheet){
try {
if(null != format){
sheet.addCell(new Label(column, row, data, format));
}else{
sheet.addCell(new Label(column, row, data));
}
} catch (RowsExceededException e) {
throw new JxlUtilException(e);
} catch (WriteException e) {
throw new JxlUtilException(e);
}
}
/**
* Adds Integer cell to sheet at given row and column.
*
* @param column
* @param row
* @param data
* @param format Can be null.
* @param sheet
*/
public void addCellToSheet(int column, int row, Integer data,WritableCellFormat format,WritableSheet sheet){
try {
if(null != format){
sheet.addCell(new jxl.write.Number(column, row, data, format));
}else{
sheet.addCell(new jxl.write.Number(column, row, data));
}
} catch (RowsExceededException e) {
throw new JxlUtilException(e);
} catch (WriteException e) {
throw new JxlUtilException(e);
}
}
/**
* Adds a formula to sheet at given row and column.
*
* @param column
* @param row
* @param data
* @param format Can be null.
* @param sheet
*/
public void addCellToSheet(int column, int row, Formulae data,WritableCellFormat format,WritableSheet sheet){
try {
if(null != format){
sheet.addCell(new Formula(column, row, data.getFormulae(), format));
}else{
sheet.addCell(new Formula(column, row, data.getFormulae()));
}
} catch (RowsExceededException e) {
throw new JxlUtilException(e);
} catch (WriteException e) {
throw new JxlUtilException(e);
}
}
/**
* Merges cell between two ranges([col1,row1] to [col2,row2])
*
* @param sheet
* @param col1
* @param row1
* @param col2
* @param row2
* @return Range
*/
public Range mergeCells(WritableSheet sheet,int col1, int row1, int col2, int row2){
try {
return sheet.mergeCells(col1, row1, col2, row2);
} catch (RowsExceededException e) {
throw new JxlUtilException(e);
} catch (WriteException e) {
throw new JxlUtilException(e);
}
}
/**
* Finder on data to retrieve a cell.
*
* @param sheet
* @param data
* @param isLabelCell
* @return Cell
*/
public Cell find(Sheet sheet, String data,boolean isLabelCell){
return isLabelCell?sheet.findLabelCell(data):sheet.findCell(data);
}
/**
* Gets the content of Cell a given row and column.
*
* @param sheet
* @param col
* @param row
* @return String
*/
public String getCellContents(Sheet sheet, int col, int row){
Cell componentCell = sheet.getCell(col, row);
return componentCell.getContents();
}
/**
* Set the Wrap property to true.
*
* @param format
*/
public void setWrapTrue(WritableCellFormat format){
try {
format.setWrap(true);
} catch (WriteException e) {
throw new JxlUtilException(e);
}
}
/**
* Set the Wrap property to false.
*
* @param format
*/
public void setWrapFalse(WritableCellFormat format){
try {
format.setWrap(false);
} catch (WriteException e) {
throw new JxlUtilException(e);
}
}
/**
* Flushes the buffer, by writing the data to file and closing the workbook.
*
* @param book
*/
public void flush(WritableWorkbook book){
try {
book.write();
} catch (IOException e) {
throw new JxlUtilException(e);
}
try {
book.close();
} catch (WriteException e) {
throw new JxlUtilException(e);
} catch (IOException e) {
throw new JxlUtilException(e);
}
}
}
#####################################################
JxlClient .java
package com.ssb.jxl.client;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.write.WritableCellFormat;
import jxl.write.WritableWorkbook;
import com.ssb.jxl.util.JxlUtil;
public class JxlClient {
private static final JxlUtil util = new JxlUtil();
public static void main(String[] args) {
if(args.length == 0){
args = new String[2];
args[0] = "default.xls";
args[1] = "defaultSheet";
}
if(args.length == 1){
String[] tempArgs = new String[2];
tempArgs[0] = args[0];
tempArgs[1] = "defaultSheet";
args = tempArgs;
}
//Create workbook and worksheet with default settings
WritableWorkbook workBook = util.createWorkBook(args[0], args[1]);
util.mergeCells(workBook.getSheet(args[1]), 0, 1, 4, 1);
WritableCellFormat centerAlignedAllBoldformattedCell = util.createFormattedCell(10, null, true, false, null, null, null, Alignment.CENTRE);
WritableCellFormat bottomBoldFormatedCell = util.createFormattedCell(6, null, false, false, null, Border.BOTTOM,null);
//Header
util.addCellToSheet(0, 1, "Header", centerAlignedAllBoldformattedCell, workBook.getSheet(args[1]));
//Column 1,2,3,4,5
util.addCellToSheet(0, 2, "Subtitle1", bottomBoldFormatedCell, workBook.getSheet(args[1]));
util.addCellToSheet(1, 2, "Subtitle2", bottomBoldFormatedCell, workBook.getSheet(args[1]));
util.addCellToSheet(2, 2, "Subtitle3", bottomBoldFormatedCell, workBook.getSheet(args[1]));
util.addCellToSheet(3, 2, "Subtitle4", bottomBoldFormatedCell, workBook.getSheet(args[1]));
util.addCellToSheet(4, 2, "Subtitle5", bottomBoldFormatedCell, workBook.getSheet(args[1]));
util.flush(workBook);
}
}
Posts
No comments:
Post a Comment