POI3.8对EXCEL的读写

 

package poi.excel;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelRW {
	/**
	 * 根据提供的文件全路径，加载EXCEL文件( 兼容 xls 和 xlsx )
	 * @param filename
	 * @return
	 * @throws Exception
	 */
	public static Workbook readExcel(String filename) throws Exception{
		Workbook wb = null;
		if(null!=filename&&!"".equals(filename)){
			if(filename.substring(filename.lastIndexOf(".")+1).equals("xls")){
				wb = new HSSFWorkbook(new FileInputStream(filename));
			}else{
				wb = new XSSFWorkbook(new FileInputStream(filename));
			}
		}
		return wb;	
	}
	/**
	 * 根据传入的Workbook对象，进行解析excel
	 * @param wb
	 * @throws Exception
	 */
	public static void parseExcel(Workbook wb) throws Exception{
		
		 Sheet sheet1 = wb.getSheet("Sheet1");	//获取Sheet1
		 Row row = sheet1.getRow(2);//第3行		//获取制定行
		 
		 System.out.println(row.getCell(0)+","+row.getCell(1));	//获取指定的单元格
	}
	public static void wirteExcel(String filename,Workbook wb) throws Exception{
		Sheet sheet1 = wb.getSheet("Sheet1");
		 Row row = sheet1.getRow(2);//第3行
		 Cell c3 = row.getCell(2)==null?row.createCell(2):row.getCell(2);	//如果单元格为NULL，则创建空白单元格
		 Cell d3 = row.getCell(3)==null?row.createCell(3):row.getCell(3);
		 c3.setCellValue("2012-08-17");		//设置字符串
		 d3.setCellValue(10);				//设置int值
		 CellUtil.getCell(sheet1.getRow(0), 3).setCellFormula(CellUtil.getCell(sheet1.getRow(0), 3).getCellFormula());	//当单元格含有计算公式时，通过setvalue方式。公式不会自动进行运算，需要重置一下单元格公式即可。
        if(wb instanceof XSSFWorkbook) filename += "x";		//保存成excel文件，可以自动判断workbook实例，声称对应的文件
        FileOutputStream out = new FileOutputStream(filename);
        wb.write(out);
        out.close();
	}
	public static void main(String[] args) throws Exception {
		Workbook wb = ExcelRW.readExcel("E:/mazq/test.xlsx");
		parseExcel(wb);
		ExcelRW.wirteExcel("E:/mazq/test1.xls",wb);
	}
}