项目依赖包
<dependency> <groupId>xml-apis</groupId> <artifactId>xml-apis</artifactId> <version>1.4.01</version> </dependency> <dependency> <groupId>com.google.guava</groupId> <artifactId>guava</artifactId> <version>18.0</version> </dependency> <dependency> <groupId>org.wuwz</groupId> <artifactId>ExcelKit</artifactId> <version>2.0</version> </dependency> <dependency> <groupId>net.sourceforge.jexcelapi</groupId> <artifactId>jxl</artifactId> <version>2.6.12</version> </dependency>
使用注解的Bean，序号标识该字段对应Excel的第几列
/** * Created by user on 2017/4/20. */ public class PostLogisticImport { @ExcelAnnotion(name = "记录序号",order = 0) private String id; @ExcelAnnotion(name = "用户自编号",order = 2) private String uid; @ExcelAnnotion(name = "寄达局邮编",order = 1) private String sid; @ExcelAnnotion(name = "寄达局名称",order = 3) private String sname; @ExcelAnnotion(name = "收件人名称",order = 4) private String receiver; @ExcelAnnotion(name = "收件人地址",order = 6) private String telephone; @ExcelAnnotion(name = "收件人电话",order = 5) private String receiveAddress; @ExcelAnnotion(name = "邮件重量",order = 7) private String receiveUnit; @ExcelAnnotion(name = "邮件号码",order = 8) private String receiveCode; @ExcelAnnotion(name = "备注",order = 9) private String weight; @ExcelAnnotion(name = "收件人单位",order = 10) private String logisticId; @ExcelAnnotion(name = "收件人邮编",order = 11) private String remark; @ExcelAnnotion(name = "内件详情",order = 12) private String innerDetail; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getUid() { return uid; } public void setUid(String uid) { this.uid = uid; } public String getSid() { return sid; } public void setSid(String sid) { this.sid = sid; } public String getSname() { return sname; } public void setSname(String sname) { this.sname = sname; } public String getReceiver() { return receiver; } public void setReceiver(String receiver) { this.receiver = receiver; } public String getTelephone() { return telephone; } public void setTelephone(String telephone) { this.telephone = telephone; } public String getReceiveAddress() { return receiveAddress; } public void setReceiveAddress(String receiveAddress) { this.receiveAddress = receiveAddress; } public String getReceiveUnit() { return receiveUnit; } public void setReceiveUnit(String receiveUnit) { this.receiveUnit = receiveUnit; } public String getReceiveCode() { return receiveCode; } public void setReceiveCode(String receiveCode) { this.receiveCode = receiveCode; } public String getWeight() { return weight; } public void setWeight(String weight) { this.weight = weight; } public String getLogisticId() { return logisticId; } public void setLogisticId(String logisticId) { this.logisticId = logisticId; } public String getRemark() { return remark; } public void setRemark(String remark) { this.remark = remark; } public String getInnerDetail() { return innerDetail; } public void setInnerDetail(String innerDetail) { this.innerDetail = innerDetail; } @Override public String toString() { return "PostLogisticImport{" + "id='" + id + '\'' + ", uid='" + uid + '\'' + ", sid='" + sid + '\'' + ", sname='" + sname + '\'' + ", receiver='" + receiver + '\'' + ", telephone='" + telephone + '\'' + ", receiveAddress='" + receiveAddress + '\'' + ", receiveUnit='" + receiveUnit + '\'' + ", receiveCode='" + receiveCode + '\'' + ", weight='" + weight + '\'' + ", logisticId='" + logisticId + '\'' + ", remark='" + remark + '\'' + ", innerDetail='" + innerDetail + '\'' + '}'; } }读取Excel 工具类
/** * Created by user on 2017/4/20. */ import cn.soqi.mp.qihome.annotion.ExcelAnnotion; import cn.soqi.mp.qihome.po.PostLogisticImport; import jxl.Sheet; import jxl.Workbook; import jxl.read.biff.BiffException; 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 org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.*; import java.lang.annotation.Annotation; import java.lang.reflect.Field; import java.util.ArrayList; import java.util.Iterator; import java.util.LinkedList; import java.util.List; /** * Excel模板转Bean读取工具 * * @author brozer * @email 798121446@qq.com * @create 2017-04-20 10:07 */ public class ExcelUtil { /** * 读取XLS文件 * @param file * @param pointXY 从第几行，第几列开始读取 * @throws IOException */ public static List<Object> readXLSFile(File file,Class cs,Integer...pointXY) throws IOException, IllegalAccessException { List<Object> objectList=new LinkedList<>(); InputStream ExcelFileToRead = new FileInputStream(file.getAbsoluteFile()); HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; int pointY=0,pointX=0; if(pointXY.length>1&&pointXY[0]!=null){//从第几行开始读(默认从0开始) pointY=pointXY[0]; } if(pointXY.length>1&&pointXY[1]!=null){//从第几列开始读（默认从0开始） pointX=pointXY[1]; } Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { row = (HSSFRow) rows.next(); if(pointY>0){ pointY--; continue; } Object object=null; //反射机制 try { object=cs.newInstance(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } Iterator cells = row.cellIterator(); int templateX=pointX; while (cells.hasNext()) { cell = (HSSFCell) cells.next(); if(templateX>0){ templateX--; continue; } Field[] fields=cs.getDeclaredFields(); if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING||cell.getCellType() ==HSSFCell.CELL_TYPE_NUMERIC){ int i=cell.getColumnIndex(); //第几列（跟字段的注解映射） for (int k=0;k<fields.length;k++){ Field field=fields[k]; Annotation[] annotations=field.getAnnotations(); ExcelAnnotion excelAnnotion= (ExcelAnnotion) annotations[0]; if(excelAnnotion.order()==i){ String value=""; if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING) value=cell.getStringCellValue()+""; if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) value=cell.getNumericCellValue()+""; field.setAccessible(true); field.set(object,value); break; } } } } objectList.add(object); } return objectList; } /** * 待开发中 * @param file * @throws IOException */ public static void writeXLSFile(File file) throws IOException { String excelFileName = file.getAbsolutePath();// name of excel file String sheetName = "Sheet1";// name of sheet HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(sheetName); // iterating r number of rows for (int r = 0; r < 5; r++) { HSSFRow row = sheet.createRow(r); // iterating c number of columns for (int c = 0; c < 5; c++) { HSSFCell cell = row.createCell(c); cell.setCellValue("Cell " + r + " " + c); } } FileOutputStream fileOut = new FileOutputStream(excelFileName); // write this workbook to an Outputstream. wb.write(fileOut); fileOut.flush(); fileOut.close(); } /** * 读取XLSX文件 * @param file 文件名 * @param pointXY 从第几行，第几列开始读取 * @throws IOException */ public static List<Object> readXLSXFile(File file, Class cs, Integer...pointXY) throws IOException, IllegalAccessException { List<Object> objectList=new LinkedList<>(); InputStream ExcelFileToRead = new FileInputStream(file.getAbsoluteFile()); XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead); XSSFWorkbook test = new XSSFWorkbook(); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; int pointY=0,pointX=0; if(pointXY.length>1&&pointXY[0]!=null){//从第几行开始读(默认从0开始) pointY=pointXY[0]; } if(pointXY.length>1&&pointXY[1]!=null){//从第几列开始读（默认从0开始） pointX=pointXY[1]; } Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { row = (XSSFRow) rows.next(); if(pointY>0){ pointY--; continue; } Object object=null; //反射机制 try { object=cs.newInstance(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } Iterator cells = row.cellIterator(); int templateX=pointX; while (cells.hasNext()) { cell = (XSSFCell) cells.next(); if(templateX>0){ templateX--; continue; } Field[] fields=cs.getDeclaredFields(); if(cell.getCellType() == XSSFCell.CELL_TYPE_STRING||cell.getCellType() ==XSSFCell.CELL_TYPE_NUMERIC){ int i=cell.getColumnIndex(); //第几列（跟字段的注解映射） for (int k=0;k<fields.length;k++){ Field field=fields[k]; Annotation[] annotations=field.getAnnotations(); ExcelAnnotion excelAnnotion= (ExcelAnnotion) annotations[0]; if(excelAnnotion.order()==i){ /*System.out.print(excelAnnotion.order()); System.out.print(excelAnnotion.name());*/ String value=""; if(cell.getCellType() == XSSFCell.CELL_TYPE_STRING) value=cell.getStringCellValue()+""; if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) value=cell.getNumericCellValue()+""; field.setAccessible(true); field.set(object,value); break; } } } } objectList.add(object); } return objectList; } /** * 待开发中 * @throws IOException */ public static void writeXLSXFile() throws IOException { String excelFileName = "C:/Test.xlsx";// name of excel file String sheetName = "Sheet1";// name of sheet XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(sheetName); // iterating r number of rows for (int r = 0; r < 5; r++) { XSSFRow row = sheet.createRow(r); // iterating c number of columns for (int c = 0; c < 5; c++) { XSSFCell cell = row.createCell(c); cell.setCellValue("Cell " + r + " " + c); } } FileOutputStream fileOut = new FileOutputStream(excelFileName); // write this workbook to an Outputstream. wb.write(fileOut); fileOut.flush(); fileOut.close(); } public static List<Object> readExcel(File file,Class cs) throws IOException, IllegalAccessException { List<Object> objectList; try { objectList=readXLSXFile(file,cs); }catch (Exception ex){ objectList=readXLSFile(file,cs); } return objectList; } }