util|POI兼容读取Excel2003和Excel2007 框架|java|其他

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 = "https://www.it610.com/article/uploadUser", results = { @Result(type = "json") }) public String analizeExcel() { if (xlsBook != null) { initType(); InputStream is = null; List 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 readUser(Sheet sheet) { List userList = new ArrayList(); 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-----------------"); } }

JavaBean类：

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方法 }