BridgeLi's Blog

目前在市场上有两个最出名的第三方的JAR包：JXL和POI，他们在处理Excel上都有着不俗的表现，但他们有着细微的差别，主要差别如下：

1. JXL在处理数据方面速度比较快，而POI相对较慢，当数据较少时，其实并不明显；
2. JXL对图片的支持更好，而POI对图片的支持稍弱，但对图片也是支持的；
3. JXL对公示的支持能力稍弱，对于复杂的公式显得无能为力，而POI则做得很好，所以如果做财务软件的话，请慎重选择，建议POI，否则很有可能会引起一些意想不到的问题；
4. 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);
}
}

}