使用POI操作Excel的代码(一)

经常会碰到把数据存到excel的需求，虽然使用POI写起来确实不算复杂，但每次都写近乎一样的代码有点烦躁，下面是我经常用到的代码，满足一般的要求，高级的excel操作没有用到，不过应该已经满足了大部分的要求。

需引入的jar包如下，

poi-2.5.1-final-20040804.jar

poi-contrib-2.5.1-final-20040804.jar

poi-scratchpad-2.5.1-final-20040804.jar

1. Excel类，代表一个Excel对象，里面可以包含很多sheet, 已经相关必要方法，

package stony.zhang.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class Excel {

private boolean override;
private String file;
HSSFWorkbook wb = new HSSFWorkbook();
List sheets = new ArrayList();

private HSSFCellStyle titleStyle;
/**
* file, the file with the full path, If cant find,then new one.
*
* @param file
*/
public Excel(String file) {
this(file, false);
}

public Excel(String file, boolean override) {
this.file = file;
this.override = override;
File f=new File(file);
if (override) {
// delete the exsited one
}
try {
if(f.exists()){
wb= new HSSFWorkbook(new FileInputStream(file));
}else{
wb= new HSSFWorkbook();
}
titleStyle = wb.createCellStyle();
HSSFFont titleFont = wb.createFont();
// titleFont.setColor(HSSFFont.COLOR_RED);
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleStyle.setFont(titleFont);

// create the Excel file.
int num=wb.getNumberOfSheets();
for (int i = 0; i < num; i++) {
HSSFSheet sheet = wb.getSheetAt(i);
String name=wb.getSheetName(i);
sheets.add(new ExcelSheet(name,sheet,titleStyle));
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}

/**
* @author Stony Zhang
* @date Feb 23, 2009
* @param sheetName
* If cant find the sheet, new one.
* @return
*/
public ExcelSheet getSheet(String sheetName) {
for (ExcelSheet esh : this.sheets) {
if (esh.getName().equalsIgnoreCase(sheetName)) {
return esh;
}
}
HSSFSheet sheet = wb.createSheet(sheetName);
return new ExcelSheet(sheetName, sheet,titleStyle);
}

public void save() {
try {
FileOutputStream fileOut = new FileOutputStream(file);
wb.write(fileOut);
fileOut.close();
} catch (Exception e) {
// TODO: handle exception
}
}

public static void main(String[] argv){
Excel ex=new Excel("E:/test.xls");
ExcelSheet esh=ex.getSheet("log");
esh.setHeader(new String[]{"User","Table Name","Database","Action Type","Opration Time"});
esh.addRecord(new String[]{"aa","bb","cc","dd","ee"});
ex.save();
}

}

2.ExcelSheet类，代表一个具体sheet.

package stony.zhang.excel;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;

/**
* @author Stony Zhang
* @date Feb 23, 2009
* @return
*/
public class ExcelSheet {
private HSSFSheet sheet;

private String name;

private String[] header;

private HSSFCellStyle titleStyle;

public ExcelSheet(String sh