(1)POI处理
对Excel的读取,主要涉及工作薄、工作薄、行数据、单元格等的处理,POI对97-2003和2007+两个版本的处理采用不同的类,如下图所示。![](http://dl.iteye.com/upload/attachment/0068/0888/ca38c634-198b-3a03-8a4e-89dda244db1c.jpg)
其中:
a)Workbook、Sheet、Row、Cell等为接口;
b)HSSFWorkbook、HSSFSheet、HSSFRow、HSSFCell为97-2003版本对应的处理实现类;
c)XSSFWorkbook、XSSFSheet、XSSFRow、XSSFCell为2007+版本对应的处理实现类;
(2)实现的例子
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
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.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.convention.annotation.Action;
import org.apache.struts2.convention.annotation.Namespace;
import org.apache.struts2.convention.annotation.ParentPackage;
import org.apache.struts2.convention.annotation.Result;
import org.apache.struts2.json.annotations.JSON;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import com.csair.smms.inituserinfo.dto.UserInsert;
import com.csair.smms.inituserinfo.service.InitUserInfoService;
import com.csair.smms.preventive.service.PreventiveInfoService;
@ParentPackage("json-default")
@Namespace("/upload")
@Controller
public class UploadUserAction {
private static int version2003 = 2003;
private static int version2007 = 2007;
private static int version = version2003;
private File xlsBook;
private String xlsBookFileName;
private String xlsBookContentType;
private static Logger logger = Logger.getLogger(UploadUserAction.class);
@Autowired
private InitUserInfoService initUserInfoService;
@Autowired
private PreventiveInfoService preventiveInfoService;
@JSON(serialize = false)
public File getXlsBook() {
return xlsBook;
}
public void setXlsBook(File xlsBook) {
this.xlsBook = xlsBook;
}
@JSON(serialize = false)
public String getXlsBookFileName() {
return xlsBookFileName;
}
public void setXlsBookFileName(String xlsBookFileName) {
this.xlsBookFileName = xlsBookFileName;
}
@JSON(serialize = false)
public String getXlsBookContentType() {
return xlsBookContentType;
}
public void setXlsBookContentType(String xlsBookContentType) {
this.xlsBookContentType = xlsBookContentType;
}
//此方法判别Excel2003和Excel2007
public void initType() {
String name = getXlsBookFileName();
if (name != null) {
int index = getXlsBookFileName().indexOf(".");
String suffex = name.substring(index);
if ("xls".equals(suffex)) {
version = version2003;
} else if ("xlsx".equals(suffex)) {
version = version2007;
}
}
}
@Action(value = "uploadUser", results = { @Result(type = "json") })
public String analizeExcel() {
if (xlsBook != null) {
initType();
InputStream is = null;
List<UserInsert> userList = null;
try {
is = new FileInputStream(xlsBook);
version = (xlsBookFileName.endsWith(".xls") ? version2003
: version2007);
if (version == 2003) {// 2003
POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
userList = readUser(sheet);
} else if (version == 2007) {// 2007
XSSFWorkbook xwb = new XSSFWorkbook(is);
XSSFSheet sheet = xwb.getSheetAt(0);
userList = readUser(sheet);
}
} catch (FileNotFoundException e) {
} catch (IOException e) {
}
// 保存javabean逻辑
//自己的逻辑代码
}
return com.opensymphony.xwork2.Action.SUCCESS;
}
//此方法为读取表格核心方法
public List<UserInsert> readUser(Sheet sheet) {
List<UserInsert> userList = new ArrayList<UserInsert>();
int rowNum = sheet.getPhysicalNumberOfRows();
UserInsert ui = null;
for (int i = 1; i < rowNum; i++) {
Row row = sheet.getRow(i);
Cell c = row.getCell(0);
ui = new UserInsert();
if (c != null) {
if (c.getCellType() == Cell.CELL_TYPE_NUMERIC) {
long id = (long) row.getCell(0).getNumericCellValue();
ui.setId(id + "");
} else {
ui.setId(c.getStringCellValue());
}
c = row.getCell(1);
if (c != null) {
ui.setName(row.getCell(1).getStringCellValue());
} else {
ui.setName("");
}
c = row.getCell(2);
if (c != null) {
ui.setSex(row.getCell(2).getStringCellValue());
} else {
ui.setSex("");
}
c = row.getCell(3);
if (c != null) {
ui.setBase(row.getCell(3).getStringCellValue());
} else {
ui.setBase("");
}
c = row.getCell(4);
if (c != null) {
ui.setBaseCode(row.getCell(4).getStringCellValue());
} else {
ui.setBaseCode("");
}
c = row.getCell(5);
if (c != null) {
ui.setDepartment(row.getCell(5).getStringCellValue());
} else {
ui.setDepartment("");
}
c = row.getCell(6);
if (c != null) {
ui.setPosition(row.getCell(6).getStringCellValue());
} else {
ui.setPosition("");
}
c = row.getCell(7);
if (c != null) {
ui.setRole(row.getCell(7).getStringCellValue());
} else {
ui.setRole("");
}
c = row.getCell(8);
if (c != null) {
if (c.getCellType() == Cell.CELL_TYPE_NUMERIC) {
long m = (long) row.getCell(8).getNumericCellValue();
ui.setMobile(m + "");
} else {
ui.setMobile(c.getStringCellValue());
}
} else {
ui.setMobile("");
}
c = row.getCell(9);
if (c != null) {
if (c.getCellType() == Cell.CELL_TYPE_NUMERIC) {
long t = (long) row.getCell(9).getNumericCellValue();
ui.setTelephone(t + "");
} else {
ui.setTelephone(c.getStringCellValue());
}
} else {
ui.setTelephone("");
}
c = row.getCell(10);
if (c != null) {
ui.setEmail(row.getCell(10).getStringCellValue());
} else {
ui.setEmail("");
}
userList.add(ui);
}
}
return userList;
}
public static void main(String[] args) {
UploadUserAction uua = new UploadUserAction();
uua.analizeExcel();
System.out.println("end-----------------");
}
}
javabeen类:
public class UserInsert implements Serializable{
private static final long serialVersionUID = -796538816605301094L;
private String id;// 员工号
private String name;// 姓名
private String sex;// 性别
private String base;// 所属基地
private String baseCode;// 基地三字码
private String department;// 部门
private String position;// 职务
private String role;// 系统角色
private String mobile;// 手机
private String telephone;// 办公电话
private String email;// 邮箱
//此处省略各属性的setter和getter方法
}
不过要注意的是这里不仅仅要导入poi-3.8-20120326.jar及其以后版本的包,而且要导入 还需要
xmlbeans-2.3.0.jar
poi-ooxml-schemas-3.8-beta5-20111217.jar
dom4j-1.6.1.jar等相关的Jar包
另外,还可以参考这个帖子做更细致的设置:http://cgs1999.iteye.com/blog/1525665