POI操作Excel

manager实现方法ExcelFileManagerImpl.java:


package com.tianyi.video.admin.manager.Impl;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;

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

import com.tianyi.video.admin.manager.ExcelFileManager;
import com.tianyi.video.module.util.StringUtil;

public class ExcelFileManagerImpl extends BaseManager implements
ExcelFileManager {

private static final String EXCEL_FILE_ROOT = "D:\\\\work\\";

private String excelFileRoot;

/**
* 解析EXCEL文件
*
* @param fileName
* @return
*/
public HSSFWorkbook getBook(String fileName) {
HSSFWorkbook abook = null;
try {
abook = new HSSFWorkbook(new FileInputStream(this
.getExcelFileRoot()
+ fileName));
} catch (FileNotFoundException e) {
logger.error(e);
} catch (IOException e) {
logger.error(e);
}
return abook;
}

/**
* 得到SHEET文件
*
* @param sheetName
* @param abook
* @return
*/
public HSSFSheet getSheet(String sheetName, HSSFWorkbook abook) {
if (StringUtil.isBlank(sheetName)) {
logger.error("sheetName can't be null!");
}
if (abook == null) {
logger.error("HSSFWorkbook can't be null!");
}
if (abook.getNumberOfSheets() <= 0) {
logger.error("HSSFWorkbook must have a sheet!");
}
HSSFSheet aSheet = null;
aSheet = abook.getSheet(sheetName);
if (aSheet == null) {
aSheet = abook.getSheetAt(0);
}
return aSheet;
}

/**
* 得到单元格
*
* @param row
* @param cell
* @param asheet
* @return
*/
public HSSFCell getCell(int row, short cell, HSSFSheet asheet) {
HSSFCell acell = null;
if (asheet == null) {
logger.error("sheet can't be null!");
}
if (asheet.getLastRowNum() <= row) {
logger.error("out of row num range!");
}
HSSFRow arow = asheet.getRow(row);
if (arow.getLastCellNum() <= cell) {
logger.error("out of cell num range!");
}
acell = arow.getCell(cell);
return acell;
}

/**
* 得到单元值
*
* @param row
* @param cell
* @param asheet
* @return
*/
public String getValue(int row, short cell, HSSFSheet asheet) {
HSSFCell aCell = this.getCell(row, cell, asheet);
return getValue(aCell);
}

/**
* 得到单元值
*
* @param aCell
* @return
*/
public String getValue(HSSFCell aCell) {
String value = "";
if (aCell == null) {
logger.error("cell can't be null!");
}
int cellType = aCell.getCellType();
switch (cellType) {
case 0:// Numeric
DecimalFormat df = new DecimalFormat("#");
String strCell = df.format(aCell.getNumericCellValue());
value = strCell;
break;
case 1:// String
strCell = aCell.getStringCellValue();
value = strCell;
break;
default:
value = "DateFormat error";// 其它格式的数据
}
return value;
}

public String getExcelFileRoot() {
if (StringUtil.isBlank(this.excelFileRoot)) {
this.excelFileRoot = EXCEL_FILE_ROOT;
}
return excelFileRoot;
}

public void setExcelFileRoot(String excelFileRoot) {
this.excelFileRoot = excelFileRoot;
}

/**
* 创建工作文件
*
* @return
*/
public HSSFWorkbook createBook() {
// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
return workbook;
}

/**
* 创建工作表
*
* @param sheetName
* @param aBook
* @return
*/
public HSSFSheet createSheet(String sheetName, HSSFWorkbook aBook) {
// 在Excel工作簿中建一工作表，其名为缺省值
HSSFSheet sheet = null;
if (StringUtil.isNotBlank(sheetName)) {
sheet = aBook.createSheet(sheetName);
} else {
sheet = aBook.createSheet();
}
return sheet;
}

/**
* 写入值
*
* @param aSheet
* @param i
* @param j
* @param value
*/
public HSSFSheet createCell(HSSFSheet aSheet, short i, short j, String value) {
// 在索引0的位置创建行（最顶端的行）
HSSFRow row = aSheet.createRow((short) i);
// 在索引0的位置创建单元格（左上端）
HSSFCell cell = row.createCell((short) j);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 在单元格中输入一些内容
cell.setCellValue(value);
return aSheet;
}

/**
* 写入文件
* @param fileName
* @param aBook
*/
public void createExcel(String fileName, HSSFWorkbook aBook) {
try {
// 新建一输出文件流
FileOutputStream fOut = new FileOutputStream(this
.getExcelFileRoot()
+ fileName);
// 把相应的Excel 工作簿存盘
aBook.write(fOut);
fOut.flush();
// 操作结束，关闭文件
fOut.close();
logger.info("excel file " + fileName + " create success! ");
} catch (Exception e) {
logger.error(e);
}
}

}

单元测试类

package test;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.tianyi.video.admin.manager.ExcelFileManager;

public class ExcelFileManagerTest extends ServiceTestBase {

private ExcelFileManager excelFileManager;

protected void setUp() throws Exception {
super.setUp();
excelFileManager = (ExcelFileManager) appContext
.getBean("excelFileManager");
}

protected void tearDown() throws Exception {
super.tearDown();
}

public void testDumy() {
assertTrue(true);
}

public void testReadExcel() {
String fileName = "upload\\20080614\\1213428729312.xls";

HSSFWorkbook abook = excelFileManager.getBook(fileName);
HSSFSheet aSheet = excelFileManager.getSheet("Users", abook);// 获得一个sheet
System.out.println(excelFileManager.getValue(1, (short) 0, aSheet));
assertEquals("2a", excelFileManager.getValue(1, (short) 0, aSheet));
}

public void testCreateExcel() {
String fileName = "upload\\test.xls";
HSSFWorkbook aBook = excelFileManager.createBook();
HSSFSheet aSheet = excelFileManager.createSheet("test", aBook);
excelFileManager.createCell(aSheet, (short)0, (short)0, "00");
excelFileManager.createCell(aSheet, (short)0, (short)1, "01");
excelFileManager.createCell(aSheet, (short)0, (short)2, "02");
excelFileManager.createExcel(fileName, aBook);
}

}