使用工具類 使用poi匯入匯出excel報表

昨晚找到一個很使用的匯入匯出excel的工具類，很多系統都會用到匯入匯入excel的功能。看官網的api都是沒有封裝，不過有樣式設定的演示，如果有需要具體樣式的設定，還是需要去查api滴。

這裡找到的一個前輩封裝好的類，可以直接拿來當作常用的工具類使用。原文出處：http://www.blogjava.net/caihualin/archive/2008/05/12/164724.html

首先要引入poi的jar包，自己去網上下吧。

匯出excel的類

public class ExecelOutputTest {

	// 設定cell編碼解決中文高位位元組截斷
	// private static short XLS_ENCODING = HSSFCell.ENCODING_UTF_16;

	// 定製浮點數格式
	private static String NUMBER_FORMAT = "#,##0.00";

	// 定製日期格式
	private static String DATE_FORMAT = "yyyy-mm-dd hh:mm:ss"; // "m/d/yy h:mm"
	private OutputStream out = null;
	private HSSFWorkbook workbook = null;
	private HSSFSheet sheet = null;
	private HSSFRow row = null;

	public ExecelOutputTest() {
	}

	/**
	 * 初始化Excel
	 * 
	 */
	public ExecelOutputTest(OutputStream out) {
		this.out = out;
		this.workbook = new HSSFWorkbook();
		this.sheet = workbook.createSheet();
	}

	/**
	 * 匯出Excel檔案
	 * 
	 * @throws IOException
	 */
	public void export() throws FileNotFoundException, IOException {
		try {
			workbook.write(out);
			out.flush();
			out.close();
		} catch (FileNotFoundException e) {
			throw new IOException(" 生成匯出Excel檔案出錯! ", e);
		} catch (IOException e) {
			throw new IOException(" 寫入Excel檔案出錯! ", e);
		}

	}

	/**
	 * 增加一行
	 * 
	 * @param index
	 *            行號
	 */
	public void createRow(int index) {
		this.row = this.sheet.createRow(index);
	}

	/**
	 * 獲取單元格的值
	 * 
	 * @param index
	 *            列號
	 */
	public String getCell(int index) {
		@SuppressWarnings("deprecation")
		HSSFCell cell = this.row.getCell((short) index);
		String strExcelCell = "";
		if (cell != null) { // add this condition
			// judge
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_FORMULA:
				strExcelCell = "FORMULA ";
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				strExcelCell = String.valueOf(cell.getNumericCellValue());
				break;
			case HSSFCell.CELL_TYPE_STRING:
				strExcelCell = cell.getStringCellValue();
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				strExcelCell = "";
				break;
			default:
				strExcelCell = "";
				break;
			}
		}
		return strExcelCell;
	}

	/**
	 * 設定單元格
	 * 
	 * @param index
	 *            列號
	 * @param value
	 *            單元格填充值
	 */
	public void setCell(int index, int value) {
		@SuppressWarnings("deprecation")
		HSSFCell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
	}

	/**
	 * 設定單元格
	 * 
	 * @param index
	 *            列號
	 * @param value
	 *            單元格填充值
	 */
	@SuppressWarnings("deprecation")
	public void setCell(int index, double value) {
		HSSFCell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
		HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell樣式
		HSSFDataFormat format = workbook.createDataFormat();
		cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 設定cell樣式為定製的浮點數格式
		cell.setCellStyle(cellStyle); // 設定該cell浮點數的顯示格式
	}

	/**
	 * 設定單元格
	 * 
	 * @param index
	 *            列號
	 * @param value
	 *            單元格填充值
	 */
	public void setCell(int index, String value) {
		@SuppressWarnings("deprecation")
		HSSFCell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		// cell.setEncoding(XLS_ENCODING);
		cell.setCellValue(value);
	}

	/**
	 * 設定單元格
	 * 
	 * @param index
	 *            列號
	 * @param value
	 *            單元格填充值
	 */
	public void setCell(int index, Calendar value) {
		@SuppressWarnings("deprecation")
		HSSFCell cell = this.row.createCell((short) index);
		// cell.setEncoding(XLS_ENCODING);
		cell.setCellValue(value.getTime());
		HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell樣式
		cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); // 設定cell樣式為定製的日期格式
		cell.setCellStyle(cellStyle); // 設定該cell日期的顯示格式
	}

	public static void main(String[] args) {
		List<PersonInfo> personInfos = new ArrayList<PersonInfo>();
		for (int i = 1; i <= 10; i++) {
			PersonInfo p = new PersonInfo();
			p.setId(i);
			p.setName("yang" + i);
			p.setAge(i + 20);
			p.setBirthday(new Date());
			if (i % 2 == 0) {
				p.setSex("male");
			} else {
				p.setSex("female");
			}
			personInfos.add(p);
		}

		System.out.println(" 開始匯出Excel檔案 ");
		File f = new File("F:\\Download\\workbook.xls");
		ExecelOutputTest e = new ExecelOutputTest();

		try {
			// 傳一個輸出流給建構函式
			e = new ExecelOutputTest(new FileOutputStream(f));
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		}

		e.createRow(0);
		e.setCell(0, "編號 ");
		e.setCell(1, "姓名");
		e.setCell(2, "年齡");
		e.setCell(3, "性別");
		e.setCell(4, "出生日期");

		for (int i = 1; i <= personInfos.size(); i++) {
			e.createRow(i);
			e.setCell(0, personInfos.get(i - 1).getId());
			e.setCell(1, personInfos.get(i - 1).getName());
			e.setCell(2, personInfos.get(i - 1).getAge());
			e.setCell(3, personInfos.get(i - 1).getSex());
			SimpleDateFormat sdf = new SimpleDateFormat(
					"yyyy年MM月dd日 HH時mm分ss秒 E");
			String date = sdf.format(personInfos.get(i - 1).getBirthday());
			e.setCell(4, date);
		}

		try {
			e.export();
			System.out.println(" 匯出Excel檔案[成功] ");
		} catch (IOException ex) {
			System.out.println(" 匯出Excel檔案[失敗] ");
			ex.printStackTrace();
		}
	}
}

這是匯出檔案的效果：

匯入excel的類

public class ExcelInputTest {

	private HSSFWorkbook wb = null;// book [includes sheet]

	private HSSFSheet sheet = null;

	private HSSFRow row = null;

	private int sheetNum = 0; // 第sheetnum個工作表

	private int rowNum = 0;

	private FileInputStream fis = null;

	private File file = null;

	public ExcelInputTest() {
	}

	public ExcelInputTest(File file) {
		this.file = file;
	}

	public void setRowNum(int rowNum) {
		this.rowNum = rowNum;
	}

	public void setSheetNum(int sheetNum) {
		this.sheetNum = sheetNum;
	}

	public void setFile(File file) {
		this.file = file;
	}

	/**
	 * 讀取excel檔案獲得HSSFWorkbook物件
	 */
	public void open() throws IOException {
		fis = new FileInputStream(file);
		wb = new HSSFWorkbook(new POIFSFileSystem(fis));
		fis.close();
	}

	/**
	 * 返回sheet表數目
	 * 
	 * @return int
	 */
	public int getSheetCount() {
		int sheetCount = -1;
		sheetCount = wb.getNumberOfSheets();
		return sheetCount;
	}

	/**
	 * sheetNum下的記錄行數
	 * 
	 * @return int
	 */
	public int getRowCount() {
		if (wb == null)
			System.out.println("=============>WorkBook為空");
		HSSFSheet sheet = wb.getSheetAt(this.sheetNum);
		int rowCount = -1;
		rowCount = sheet.getLastRowNum();
		return rowCount;
	}

	/**
	 * 讀取指定sheetNum的rowCount
	 * 
	 * @param sheetNum
	 * @return int
	 */
	public int getRowCount(int sheetNum) {
		HSSFSheet sheet = wb.getSheetAt(sheetNum);
		int rowCount = -1;
		rowCount = sheet.getLastRowNum();
		return rowCount;
	}

	/**
	 * 得到指定行的內容
	 * 
	 * @param lineNum
	 * @return String[]
	 */
	public String[] readExcelLine(int lineNum) {
		return readExcelLine(this.sheetNum, lineNum);
	}

	/**
	 * 指定工作表和行數的內容
	 * 
	 * @param sheetNum
	 * @param lineNum
	 * @return String[]
	 */
	public String[] readExcelLine(int sheetNum, int lineNum) {
		if (sheetNum < 0 || lineNum < 0)
			return null;
		String[] strExcelLine = null;
		try {
			sheet = wb.getSheetAt(sheetNum);
			row = sheet.getRow(lineNum);

			int cellCount = row.getLastCellNum();
			strExcelLine = new String[cellCount + 1];
			for (int i = 0; i <= cellCount; i++) {
				strExcelLine[i] = readStringExcelCell(lineNum, i);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return strExcelLine;
	}

	/**
	 * 讀取指定列的內容
	 * 
	 * @param cellNum
	 * @return String
	 */
	public String readStringExcelCell(int cellNum) {
		return readStringExcelCell(this.rowNum, cellNum);
	}

	/**
	 * 指定行和列編號的內容
	 * 
	 * @param rowNum
	 * @param cellNum
	 * @return String
	 */
	public String readStringExcelCell(int rowNum, int cellNum) {
		return readStringExcelCell(this.sheetNum, rowNum, cellNum);
	}

	/**
	 * 指定工作表、行、列下的內容
	 * 
	 * @param sheetNum
	 * @param rowNum
	 * @param cellNum
	 * @return String
	 */
	@SuppressWarnings("deprecation")
	public String readStringExcelCell(int sheetNum, int rowNum, int cellNum) {
		if (sheetNum < 0 || rowNum < 0)
			return "";
		String strExcelCell = "";
		try {
			sheet = wb.getSheetAt(sheetNum);
			row = sheet.getRow(rowNum);

			if (row.getCell((short) cellNum) != null) {
				// 判斷cell的型別並統一轉換為string型別
				switch (row.getCell((short) cellNum).getCellType()) {
				case HSSFCell.CELL_TYPE_FORMULA:
					strExcelCell = "FORMULA ";
					break;
				case HSSFCell.CELL_TYPE_NUMERIC: {
					strExcelCell = String.valueOf(row.getCell((short) cellNum)
							.getNumericCellValue());
				}
					break;
				case HSSFCell.CELL_TYPE_STRING:
					strExcelCell = row.getCell((short) cellNum)
							.getStringCellValue();
					break;
				case HSSFCell.CELL_TYPE_BLANK:
					strExcelCell = "";
					break;
				case HSSFCell.CELL_TYPE_BOOLEAN:
					strExcelCell = String.valueOf(row.getCell((short) cellNum));
				default:
					strExcelCell = "";
					break;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return strExcelCell;
	}

	public static void main(String args[]) {
		File file = new File("F:\\Download\\workbook.xls");
		ExcelInputTest readExcel = new ExcelInputTest(file);
		try {
			readExcel.open();
		} catch (IOException e) {
			e.printStackTrace();
		}
		readExcel.setSheetNum(0); // 設定讀取索引為0的工作表
		// 總行數
		int count = readExcel.getRowCount();
		for (int i = 0; i <= count; i++) {
			String[] rows = readExcel.readExcelLine(i);
			for (int j = 0; j < rows.length; j++) {
				System.out.print(rows[j] + "   ");
			}
			System.out.print("\n");
		}
	}

}

這是匯入後列印出的資料：