使用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<ExcelSheet> sheets = new ArrayList<ExcelSheet>();
 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 sheetName, HSSFSheet sh) {
  this.name = sheetName;
  this.sheet = sh;
  sheet.setDisplayGridlines(true);
 }
 
 public ExcelSheet(String sheetName, HSSFSheet sh,HSSFCellStyle titleStyle) {
  this.name = sheetName;
  this.sheet = sh;
  this.titleStyle=titleStyle;
 }
 // public ExcelSheet(){
 // this(name);
 // }
 public void addRecord(String[] record) {
  if(header!=null){
   if(header.length!=record.length){
    return;
   }
  }
  
  fillContent(record,sheet.getLastRowNum()+1,null);
  
 }
 public String[] getHeader() {
  return this.header;
 }
 public void setHeader(String[] header) {
  this.header=header;
  fillContent(header,0,this.titleStyle);
 }
 private void fillContent(String[] crow, int rowNum, HSSFCellStyle style) {
  HSSFRow row = sheet.createRow((short) rowNum);
         
  for (int i = 0; i < crow.length; i++) {
   String s = crow[i];
   HSSFCell cell = row.createCell((short) i);
   if(style!=null){
    cell.setCellStyle(style);
   }
   cell.setEncoding(HSSFCell.ENCODING_UTF_16);
   cell.setCellValue(s);
  }
 }
 
 public void setValue(int rowNum,int colNum,String value){
  HSSFRow row=this.sheet.getRow(rowNum);
  HSSFCell cell=row.getCell((short)colNum);
  if (cell==null){
    cell = row.createCell((short) colNum);
  }
  cell.setEncoding(HSSFCell.ENCODING_UTF_16);
  cell.setCellValue(value);
 }
 public String getName() {
  // TODO Auto-generated method stub
  return this.name;
 }
 public void addRecord(ArrayList<String[]> arr) {
  for (String[] row : arr) {
   this.addRecord(row);
  }
  
 }
 
 public List<String[]> getRecords(){
  ArrayList<String[]> vs=new ArrayList<String[]>();
  for (int j = 0; j < this.sheet.getLastRowNum(); j++) {
   HSSFRow row=this.sheet.getRow(j);
   ArrayList<String> cellsStr = new ArrayList<String>();
   for (short k = 0; k < row.getLastCellNum(); k++) {
    HSSFCell cell=row.getCell(k);
    if(cell!=null){
     cellsStr.add(cell.getStringCellValue());
    }
   }
   System.out.println("row=

补充：软件开发 , Java ,