使用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 can't 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 can't 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=" + j + " values=" + cellsStr.toArray(new String[0]));
			vs.add(cellsStr.toArray(new String[0]));
		}
		
		return vs;
	}

	public List<String> getAllValuesOfColum(short i) {
		ArrayList<String> vs=new ArrayList<String>();
		for (int j = 1; j <= this.sheet.getLastRowNum(); j++) {
			HSSFRow row=this.sheet.getRow(j);
			HSSFCell cell=row.getCell(i);
			vs.add(cell.getStringCellValue());
		}
		return vs;
	}
	
	
}

 

 

   使用Excel类中Main方法可以测试一下，各个方法就不介绍了，都很简单。欢迎各位提出改进意见。