POI入门

package testpoi;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelExport {
	
	private static ExcelExport instance = null;
	
	private Excel excel = null;
	
	private ExcelExport() {
		excel = new Excel();
	}
	
	public synchronized static ExcelExport getInstance() {
		if (instance == null) {
			instance = new ExcelExport();
		}
		return instance;
	}
	
	private HSSFWorkbook workbook = null;
	
	/**
	 * initExcelInfo 获取excel的初始化信息，文件名，类型，当前行号与sheet号等
	 * @param fileName
	 * @return void
	 */
	public void initExcelInfo(String fileName) throws Exception {
		if (fileName == null || "".equals(fileName.trim())) {
			throw new Exception("initExcelInfo == 文件名为空或null");
		}
		excel.setFileName(fileName);
		excel.setFiletype(fileName.substring(fileName.lastIndexOf(".") + 1));
		excel.setCurrentRow(0);
		excel.setCurrentSheet(0);
		if (excel.getFiletype().equalsIgnoreCase("xls")) {
			workbook = new HSSFWorkbook(new FileInputStream(fileName));
			int totalSheets = workbook.getNumberOfSheets();
			excel.setTotalSheets(totalSheets);
		}
	}
	
	/**
	 * nextSheet 跳转下一个row
	 * @param void
	 * @return void
	 */
	public void nextRow() {
		excel.setCurrentRow(excel.getCurrentRow() + 1);
	}
	
	/**
	 * setRow 指定具体某一行
	 * @param i
	 * @return void
	 */
	public void setRow(int i) {
		excel.setCurrentRow(i);
	}
	
	/**
	 * nextSheet 跳转下一个sheet
	 * @param void
	 * @return void
	 */
	public void nextSheet() {
		excel.setCurrentSheet(excel.getCurrentSheet() + 1);
	}
	
	/**
	 * setSheet 指定具体某一sheet
	 * @param i
	 * @return void
	 */
	public void setSheet(int i) {
		excel.setCurrentSheet(i);
	}
	
	/**
	 * isLastRow 判断当前行是否为最后一行
	 * @param i
	 * @return void
	 */
	public boolean isLastRow() throws FileNotFoundException, IOException {
		boolean flag = false;
		if (workbook == null) {
			workbook = new HSSFWorkbook(new FileInputStream(excel.getFileName()));
		}
		HSSFSheet sheet = workbook.getSheetAt(excel.getCurrentSheet());
		int totalRows = sheet.getLastRowNum();
		if (excel.getCurrentRow() == totalRows) {
			flag = true;
		} else {
			flag = false;
		}
		return flag;
	}
	
	/**
	 * getCurrentRecord 返回excel的当前行信息
	 * @param void
	 * @return list 当前行的所有列的信息保存为list返回
	 */
	public List getCurrentRecord() throws FileNotFoundException, IOException {
		List list = new ArrayList();
		if (workbook == null) {
			workbook = new HSSFWorkbook(new FileInputStream(excel.getFileName()));
		}
		HSSFSheet sheet = workbook.getSheetAt(excel.getCurrentSheet());
		HSSFRow row = sheet.getRow(excel.getCurrentRow());
		int lastColumnIndex = row.getLastCellNum();
		HSSFCell cell = null;
		String cellValue = null;
		for (int i = 0; i < lastColumnIndex; i++) {
			cell = row.getCell((short) i);
			if (cell != null) {
				switch (cell.getCellType()) {
					case HSSFCell.CELL_TYPE_NUMERIC:
						if (HSSFDateUtil.isCellDateFormatted(cell)) {
							Date date = cell.getDateCellValue();
							cellValue = date.toString();
						} else {
							Float temp = new Float((float) cell.getNumericCellValue());
							cellValue = String.valueOf(temp);
						}
						break;
					case HSSFCell.CELL_TYPE_STRING:
						cellValue = cell.getRichStringCellValue().getString().replaceAll("'", "''").trim();
						break;
					default:
						cellValue = cell.getRichStringCellValue().getString().replaceAll("\"", "''").trim();
				}
			} else {
				cellValue = "";
			}
			list.add(cellValue);
		}
		return list;
	}
	
	/**
	 * getAllRecord 返回excel的所有行信息
	 * @param void
	 * @return list 所有行信息保存为list返回
	 */
	public List getAllRecord() throws FileNotFoundException, IOException {
		List list = new ArrayList();
		while (!isLastRow()) {
			list.add(getCurrentRecord());
			nextRow();
		}
		return list;
	}
	
}

 

package testpoi;

public class Excel {
	
	/**
	 * filetype 文件类型
	 */
	private String fileName = null;
	
	/**
	 * filetype 文件类型
	 */
	private String filetype = null;

	/**
	 * currentSheet 当前sheet号
	 */
	private int currentSheet = 0;

	/**
	 * currentRow sheet中的当前行号
	 */
	private int currentRow = 0;

	/**
	 * totalSheets 当前excel中的sheet总个数
	 */
	private int totalSheets = 0;
	
	/**
	 * 黙认构造器
	 * @param void
	 */
	public Excel() {
		
	}
	
	/**
	 * 黙认构造器
	 * @param fileName
	 */
	public Excel(String fileName) {
		this.fileName = fileName;
	}

	/**
	 * getCurrentRow 返回sheet中的当前行号
	 * @param currentRow
	 * @return void
	 */
	public int getCurrentRow() {
		return currentRow;
	}

	/**
	 * setCurrentRow 设置sheet中的当前行号
	 * @param currentRow
	 * @return void
	 */
	public void setCurrentRow(int currentRow) {
		this.currentRow = currentRow;
	}

	/**
	 * getCurrentSheet 返回当前sheet号
	 * @param void
	 * @return currentSheet
	 */
	public int getCurrentSheet() {
		return currentSheet;
	}

	/**
	 * setCurrentSheet 设置当前sheet号
	 * @param currentSheet
	 * @return void
	 */
	public void setCurrentSheet(int currentSheet) {
		this.currentSheet = currentSheet;
	}

	/**
	 * getFiletype 返回当前excel的文件类型(后缀)
	 * @param void
	 * @return filetype
	 */
	public String getFiletype() {
		return filetype;
	}

	/**
	 * setFiletype 设置当前excel的文件类型(后缀)
	 * @param filetype
	 * @return void
	 */
	public void setFiletype(String filetype) {
		this.filetype = filetype;
	}

	/**
	 * getTotalSheets 返回当前excel中的sheet总个数
	 * @param void
	 * @return totalSheets
	 */
	public int getTotalSheets() {
		return totalSheets;
	}

	/**
	 * setTotalSheets 设置当前excel中的sheet总个数
	 * @param totalSheets
	 * @return void
	 */
	public void setTotalSheets(int totalSheets) {
		this.totalSheets = totalSheets;
	}

	/**
	 * getFileName 设置当前excel文件名
	 * @param void
	 * @return fileName
	 */
	public String getFileName() {
		return fileName;
	}

	/**
	 * setFileName 设置当前excel文件名
	 * @param fileName
	 * @return void
	 */
	public void setFileName(String fileName) {
		this.fileName = fileName;
	}
}

 

测试类如下：

package testpoi;

import java.util.List;

public class Test {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		ExcelExport excelImport = ExcelExport.getInstance();
		try {
			String oldPath = "D:\\temp.xls";
			excelImport.initExcelInfo(oldPath);
			List list = excelImport.getAllRecord();
			System.out.println(list);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

 

用POI读取Excel文件时报错。通常的原因是读取的文件头信息不对，可能是类似于将txt文件的后缀名直接改成xls，或者由其他软件导出成的Excel, 需要用Excel打开, 然后另存为一下，就可以读取了。注,需要下载jacob.jar和jacob.dll

（该功能的java实现如下：）

package testpoi;

import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;

public class ExcelChange {
	
	public static void excelSaveAs(String fileName, String saveFileName) {   
		ActiveXComponent activeXComponent = new ActiveXComponent("Excel.Application");
		activeXComponent.setProperty("Visible", new Variant(false));   
        Object objects = activeXComponent.getProperty("Workbooks").toDispatch();   
        Object object = Dispatch.invoke((Dispatch) objects, "Open", Dispatch.Method, new Object[] {fileName, new Variant(false), new Variant(true)}, new int[1]).toDispatch();
        //new Variant(1)
        Dispatch.invoke((Dispatch) object, "SaveAs", Dispatch.Method, new Object[] {saveFileName, new Variant(1)}, new int[1]);             
        Dispatch.call((Dispatch) object, "Close", new Variant(false));   
        activeXComponent.invoke("Quit", new Variant[]{});   
    }   
	
	public static void main(String[] args) throws InterruptedException {
		excelSaveAs("D:\\CSR审批.xls", "D:\\temp.xls");
	}
}