Java 实现后缀xls文件读取
一、开发环境
poi采用的3.9版本 + JDK1.6 + Myeclipse
二,JAR包
三、实现代码
实体类:UserRoleBean
package nc.xyzq.uuib.bean; /** * ClassName:UserRoleBean * @author lizm * @since JDK 1.6 * */ public class UserRoleBean { private String userCode ; private String roleid; private String isflag; public String getUserCode() { return userCode; } public void setUserCode(String userCode) { this.userCode = userCode; } public String getRoleid() { return roleid; } public void setRoleid(String roleid) { this.roleid = roleid; } public String getIsflag() { return isflag; } public void setIsflag(String isflag) { this.isflag = isflag; } }
实现读取xls信息类:Client
package nc.xyzq.client; import java.io.File; import java.io.FileInputStream; import java.util.ArrayList; import java.util.List; import java.util.logging.Logger; import nc.xyzq.pub.Pub; import nc.xyzq.uuib.service.impl.UserRoleBean; import org.apache.poi.hssf.usermodel.HSSFWorkbook; 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.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * ClassName:Client * Function: TODO * Reason: TODO * Date: 2018年11月20日 下午15:59:31 * @author lizm * @since JDK 1.6 * */ public class Client { private static final Logger logger = (Logger) Logger.getLogger(Client.class.getName()); private List<UserRoleBean> getInfoFromExcle(){ List<UserRoleBean> list = new ArrayList(); //excel文件路径 String excelPath = Pub.getPropertiesValue("relationship", "path");//读取配置文件,例如 D://data/relationship.xls try { //String encoding = "GBK"; File excel = new File(excelPath); if (excel.isFile() && excel.exists()) { //判断文件是否存在 String[] split = excel.getName().split("\\."); //.是特殊字符,需要转义!!!!! Workbook wb; //根据文件后缀(xls/xlsx)进行判断 if ( "xls".equals(split[1])){ FileInputStream fis = new FileInputStream(excel); //文件流对象 wb = new HSSFWorkbook(fis); } /** else if ("xlsx".equals(split[1])){ FileInputStream fis = new FileInputStream(excel); wb = new XSSFWorkbook(fis); } */ else { //System.out.println("文件类型错误!"); logger.info("文件类型错误!"); return list; } //开始解析 Sheet sheet = wb.getSheetAt(0); //读取sheet 0 int firstRowIndex = sheet.getFirstRowNum()+1; //第一行是列名,所以不读 int lastRowIndex = sheet.getLastRowNum(); //System.out.println("firstRowIndex: "+firstRowIndex); logger.info("firstRowIndex: "+firstRowIndex); //System.out.println("lastRowIndex: "+lastRowIndex); logger.info("lastRowIndex: "+lastRowIndex); //System.out.println("执行进行中......"); logger.info("执行进行中......"); //遍历行 for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) { //System.out.println("rIndex: " + rIndex); Row row = sheet.getRow(rIndex); if (row != null) { //行中第一个值所在的列序号 int firstCellIndex = row.getFirstCellNum(); //行中最后一个值所在的列序号 int lastCellIndex = row.getLastCellNum(); UserRoleBean userRoleBean = new UserRoleBean(); //遍历列 //for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) { for (int cIndex = 0; cIndex < 3; cIndex++) { Cell cell = row.getCell(cIndex); if (cell != null) { //将表格内容强制转换为字符型 cell.setCellType(Cell.CELL_TYPE_STRING); //System.out.println(cell.toString()); if(cIndex == 0){ userRoleBean.setUserCode(cell.toString()); }else if(cIndex == 1){ userRoleBean.setRoleid(cell.toString()); }else if(cIndex == 2){ userRoleBean.setIsflag(cell.toString()); } } } if(!("".equals(userRoleBean.getUserCode())) && !("".equals(userRoleBean.getRoleid()))){ //System.out.println(">>>0000000>>:"+userRoleBean.getRoleid()); list.add(userRoleBean); } } } } else { //System.out.println("找不到指定的文件"); logger.info("找不到指定的文件"); } } catch (Exception e) { e.printStackTrace(); } return list; } public static void main(String[] args) { List<UserRoleBean> list = new ArrayList(); Client client = new Client(); list = client.getInfoFromExcle(); } }