目前在市场上有两个最出名的第三方的JAR包：JXL和POI，他们在处理Excel上都有着不俗的表现，但他们有着细微的差别，主要差别如下：
- JXL在处理数据方面速度比较快，而POI相对较慢，当数据较少时，其实并不明显；
- JXL对图片的支持更好，而POI对图片的支持稍弱，但对图片也是支持的；
- JXL对公示的支持能力稍弱，对于复杂的公式显得无能为力，而POI则做得很好，所以如果做财务软件的话，请慎重选择，建议POI，否则很有可能会引起一些意想不到的问题；
- JXL的代码简单，也易于理解，下面是JXL处理Excel的常用方法（将来有可能的话，我会把POI的也贴出来，供大家参考）：
import java.io.File;
import java.io.IOException;
import java.util.Date;
import jxl.CellView;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Blank;
import jxl.write.Boolean;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCell;
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;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class JxlHelper {
private static final Logger LOG = LoggerFactory.getLogger(JxlHelper.class);
private WritableSheet sheet;
public JxlHelper(WritableSheet sheet) {
this.sheet = sheet;
}
public JxlHelper() {
}
public static WritableWorkbook createWritableWorkbook(String filePath) {
WritableWorkbook wwb = null;
try {
wwb = Workbook.createWorkbook(new File(filePath));
} catch (IOException e) {
LOG.error("Create Writable Workbook Failed: IOException", e);
}
return wwb;
}
public static WritableWorkbook createWritableWorkbook(String filePath, Workbook wb) {
WritableWorkbook wwb = null;
try {
wwb = Workbook.createWorkbook(new File(filePath), wb);
} catch (IOException e) {
LOG.error("Create Writable Workbook Failed: IOException", e);
}
return wwb;
}
public static Workbook getWorkbook(String filePath) throws Exception {
Workbook wb = Workbook.getWorkbook(new File(filePath));
return wb;
}
public void createSheet(WritableWorkbook wwb, String sheetName, int index) {
try {
wwb.createSheet(sheetName, index);
} catch (Exception e) {
LOG.error("Create Sheet Failed", e);
}
}
public void copySheet(WritableWorkbook wwb, int copySheetIndex, String pasteSheetName, int pasteSheetIndex) {
try {
wwb.copySheet(copySheetIndex, pasteSheetName, pasteSheetIndex);
} catch (Exception e) {
LOG.error("Copy Sheet Failed", e);
}
}
public void copySheet(WritableWorkbook wwb, String copySheetName, String pasteSheetName, int pasteSheetIndex) {
try {
wwb.copySheet(copySheetName, pasteSheetName, pasteSheetIndex);
} catch (Exception e) {
LOG.error("Copy Sheet Failed", e);
}
}
public void removeSheet(WritableWorkbook wwb, int index) {
try {
wwb.removeSheet(index);
} catch (Exception e) {
LOG.error("Remove Sheet Failed", e);
}
}
public Sheet[] getSheets(WritableWorkbook wwb) {
return wwb.getSheets();
}
public void setString(int column, int row, String value, int fontSize) throws WriteException {
if (value != null) {
sheet.addCell(new Label(column, row, value));
} else {
sheet.addCell(new Blank(column, row));
}
setCellStyle(column, row, fontSize);
}
public void setString(int column, int row, String value, CellFormat st) throws WriteException {
if (value != null) {
sheet.addCell(new Label(column, row, value, st));
} else {
sheet.addCell(new Blank(column, row, st));
}
}
public void setNumber(int column, int row, double value, CellFormat st) throws WriteException {
sheet.addCell(new Number(column, row, value, st));
}
public void setFormatNumber(int column, int row, double value, int fontSize, LocationEnum location, String format)
throws WriteException {
Number numCell = new Number(column, row, value);
sheet.addCell(numCell);
WritableFont font = new WritableFont(WritableFont.createFont(SystemConstant.INVOICE_FONT), fontSize,
WritableFont.NO_BOLD);
NumberFormat numberFormat = null;
WritableCellFormat cellFormat = null;
if (!StringUtil.isNullOrEmpty(format)) {
numberFormat = new NumberFormat(format);
cellFormat = new WritableCellFormat(font, numberFormat);
} else {
cellFormat = new WritableCellFormat(font);
}
try {
cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
if (location.equals(LocationEnum.LEFT)) {
cellFormat.setBorder(Border.LEFT, BorderLineStyle.THICK);
} else if (location.equals(LocationEnum.RIGHT)) {
cellFormat.setBorder(Border.RIGHT, BorderLineStyle.THICK);
}
cellFormat.setAlignment(Alignment.RIGHT);
cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
WritableCell writableCell = sheet.getWritableCell(column, row);
writableCell.setCellFormat(cellFormat);
} catch (WriteException e) {
LOG.error("Set Format Number Failed: WriteException", e);
}
}
public void setBoolean(int column, int row, boolean value) throws WriteException {
Boolean boolCell = new Boolean(column, row, value);
sheet.addCell(boolCell);
}
public void setFormatDateTime(int column, int row, Date value, String format) throws WriteException {
if (value != null) {
WritableCell writableCell = sheet.getWritableCell(column, row);
CellFormat cf = writableCell.getCellFormat();
DateTime cell = new DateTime(column, row, value);
DateFormat dateFormat = new DateFormat(format);
WritableCellFormat wcf = new WritableCellFormat(dateFormat);
wcf.setFont(new WritableFont(WritableFont.ARIAL));
wcf.setBorder(Border.LEFT, cf.getBorderLine(Border.LEFT));
wcf.setBorder(Border.RIGHT, cf.getBorderLine(Border.RIGHT));
wcf.setAlignment(Alignment.CENTRE);
cell.setCellFormat(wcf);
sheet.addCell(cell);
}
}
public void setCellStype2(int column, int row) throws WriteException {
WritableFont font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
WritableCellFormat cellFormat = new WritableCellFormat(font);
cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
cellFormat.setAlignment(Alignment.CENTRE);
cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
WritableCell writableCell = sheet.getWritableCell(column, row);
writableCell.setCellFormat(cellFormat);
}
public void setFormatDateTime(int column, int row, Date value, CellFormat st) throws WriteException {
if (value != null) {
sheet.addCell(new DateTime(column, row, value, st));
} else {
sheet.addCell(new Blank(column, row, st));
}
}
public void setFormula(int column, int row, String formula) {
try {
Formula f = new Formula(column, row, formula);
sheet.addCell(f);
WritableCellFormat cellFormat = new WritableCellFormat();
cellFormat.setAlignment(Alignment.LEFT);
cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
WritableCell writableCell = sheet.getWritableCell(column, row);
writableCell.setCellFormat(cellFormat);
} catch (Exception e) {
LOG.error("Set Formula Failed", e);
}
}
public void setFormula(int column, int row, String formula, CellFormat st) {
try {
sheet.addCell(new Formula(column, row, formula, st));
} catch (Exception e) {
LOG.error("Set Formula Failed", e);
}
}
public void setCellStyle(int column, int row, int fontSize) {
WritableFont font = new WritableFont(WritableFont.ARIAL, fontSize, WritableFont.BOLD);
WritableCellFormat cellFormat = new WritableCellFormat(font);
try {
cellFormat.setAlignment(Alignment.CENTRE);
cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
WritableCell writableCell = sheet.getWritableCell(column, row);
writableCell.setCellFormat(cellFormat);
} catch (WriteException e) {
LOG.error("Set Cell Style Failed: WriteException", e);
}
}
public void setSumCellBorder(int column, int row, LocationEnum location, String format) {
NumberFormat nf = null;
if (StringUtil.isNotNull(format)) {
nf = new NumberFormat(format);
}
WritableCellFormat cellFormat = new WritableCellFormat(nf);
try {
cellFormat.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN);
cellFormat.setBorder(Border.TOP, jxl.format.BorderLineStyle.DOUBLE);
if (location.equals(LocationEnum.RIGHT)) {
cellFormat.setBorder(Border.RIGHT, jxl.format.BorderLineStyle.THICK);
}
cellFormat.setBorder(Border.BOTTOM, jxl.format.BorderLineStyle.THICK);
cellFormat.setAlignment(Alignment.RIGHT);
cellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
WritableCell writableCell = sheet.getWritableCell(column, row);
writableCell.setCellFormat(cellFormat);
} catch (WriteException e) {
LOG.error("Set Sum Cell Border Failed: WriteException", e);
}
}
public void setCellStyle(int column, int row) {
WritableFont font = new WritableFont(WritableFont.ARIAL);
WritableCellFormat cellFormat = new WritableCellFormat(font);
try {
cellFormat.setAlignment(Alignment.LEFT);
cellFormat.setVerticalAlignment(VerticalAlignment.TOP);
} catch (WriteException e) {
LOG.error("Set Cell Style Failed: WriteException", e);
}
WritableCell writableCell = sheet.getWritableCell(column, row);
writableCell.setCellFormat(cellFormat);
}
public void hiddenRow(int row) throws RowsExceededException {
// sheet.setRowView(row, true);
CellView cv = new CellView();
cv.setHidden(true);
sheet.setRowView(row, cv);
}
public void removeRow(int row) {
sheet.removeRow(row);
}
public void insertRow(int row, int height) {
sheet.insertRow(row);
try {
sheet.setRowView(row, height);
} catch (RowsExceededException e) {
LOG.error("Insert Row Failed: RowsExceededException", e);
}
}
public void mergeCells(int columnStart, int rowsStart, int columnEnd, int rowsEnd) {
try {
sheet.mergeCells(columnStart, rowsStart, columnEnd, rowsEnd);
} catch (RowsExceededException e) {
LOG.error("Merge Cells Failed: RowsExceededException", e);
} catch (WriteException e) {
LOG.error("Merge Cells Failed: WriteException", e);
}
}
public static void close(WritableWorkbook wwb, Workbook wb) {
try {
if (null != wwb) {
wwb.close();
}
if (null != wb) {
wb.close();
}
} catch (WriteException e) {
LOG.error("Close WritableWorkbook or Workbook Failed: WriteException", e);
} catch (IOException e) {
LOG.error("Close WritableWorkbook or Workbook Failed: IOException", e);
}
}
public void setBackgroundColour(int column, int row) {
try {
WritableCellFormat cellFormat = new WritableCellFormat();
cellFormat.setBackground(Colour.PALE_BLUE);
cellFormat.setAlignment(Alignment.LEFT);
WritableCell writableCell = sheet.getWritableCell(column, row);
writableCell.setCellFormat(cellFormat);
} catch (WriteException e) {
LOG.error("Set Background Colour Failed: WriteException", e);
}
}
}