Java导入Excel数据

2023-09-15 13:49| 来源: 网络整理| 查看: 265

例如导入的excel所在目录为D:/cs/test.xls，测试数据如下图所示：

1.创建与excel表头对应的xml模版

姓名性别手机号邮箱住址

2.创建导入数据对应实体类

public class Persion { private String name; private String sex; private String phone; private String email; private String address; public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public void setValues(String[] values) throws Exception{ this.name = values[0] ==null?"":values[0].trim(); this.sex = values[1] ==null?"":values[1].trim(); this.phone = values[2] ==null?"":values[2].trim(); this.email = values[3] ==null?"":values[3].trim(); this.address = values[4] ==null?"":values[4].trim(); } }

3.创建导入数据工具类

import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.UnsupportedEncodingException; import java.net.URLDecoder; import java.text.DateFormat; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Set; import javax.validation.ConstraintViolation; import javax.validation.Validation; import javax.validation.Validator; import javax.validation.ValidatorFactory; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.CellReference; 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; import org.dom4j.Document; import org.dom4j.DocumentException; import org.dom4j.Element; import org.dom4j.io.SAXReader; import org.springframework.expression.EvaluationException; import org.springframework.expression.Expression; import org.springframework.expression.ExpressionParser; import org.springframework.expression.spel.standard.SpelExpressionParser; /** * @Desc 对execel文件的校验并且解析 */ @SuppressWarnings("rawtypes") public class ExecelUtil { Logger logger = Logger.getLogger(this.getClass()); private String description = "";// 如果校验失败，将会给出详细提示信息 private Sheet sheet;// execel 对象 private List fieldList;//从xml读取到的execel表格信息 private int rowIndex = 0;//当前操作行 private Object objectBean;//每一行数据封装 private Cell cellStart;// 数据的开始单元格 private Class clazz; //需要封装的类 private Validator validator; //hibernate 的校验器 private String[] fieldVals ; //从execel读到的某一行的数据 private int fieldSize = 0; //有效数据的列数 private DateFormat format = new SimpleDateFormat("yyyy-MM-dd"); // 针对日期的默认转换形式 private Expression exp ;//EL 解析器 private ExpressionParser parser; private DecimalFormat df = new DecimalFormat("#"); public String getDescription() { return description; } public Object getObjectBean() { return objectBean; } /** * * @param execelFilename * execel文件名 * @param xmlFilename * execel文件所对应的校验文件 * @param calzz 需要封装的类 */ public ExecelUtil(InputStream execelIS,String xmlFilename,Class clazz,String suffix) throws ValidationExeception{ // 打开execel工作簿 Workbook wb = null; try { if(suffix.equals(".xls")){ wb = new HSSFWorkbook(execelIS); }else if(suffix.equals(".xlsx")){ wb = new XSSFWorkbook(execelIS); } execelIS.close(); } catch (IOException e) { logger.error(e); throw new ValidationExeception("","加载文件失败,请确保是否是Execel表格"); } sheet = wb.getSheetAt(0);// 默认取第一个工作簿 //读配置文件，获取所有的属性列描述 fieldList = this.readFieldsFromXML(getAbsolutePath(xmlFilename)); //个数 fieldSize = fieldList.size(); //找到有效数据的开始单元格 cellStart = this.findStartCell(); if(cellStart == null){ throw new ValidationExeception("",this.description); } //每次读取一行execel数据，rowIndex每次增1 rowIndex = cellStart.getRowIndex()+1; //需要封装的对象类 this.clazz = clazz; //初始化校验器 ValidatorFactory factory = Validation.buildDefaultValidatorFactory(); validator = factory.getValidator(); //初始化EL解析器 parser = new SpelExpressionParser(); exp = parser.parseExpression("values"); } //是否还有数据 public boolean hasNext(){ Row row = sheet.getRow(rowIndex++); if(row == null) return false; fieldVals = this.getRowValues(row, cellStart.getColumnIndex()); if(Arrays.asList(fieldVals).indexOf("") != -1){ for(String s :fieldVals)//如果每个字段都是空的，则返回false 否则true if(!s.equals("")) return true; return false; } return true; } //校验 public boolean validate(){ try { objectBean = Class.forName(clazz.getName()).newInstance(); } catch (Exception e) { logger.error(e); } try{ exp.setValue(objectBean, fieldVals);// 给objectBean的属性赋值 }catch(EvaluationException e){//由于所有的数据类型转换都有objectBean里面来处理，故可能有异常，需要进行相应的处理 System.out.println(e); List exList = Arrays.asList("ParseException","NumberFormatException");//一般可能发生的异常 Throwable t = e.getCause(); while(t!=null){ String causeClazz = t.getClass().getSimpleName(); if(exList.contains(causeClazz)){ this.description = "第" +rowIndex+"行，类型转换失败："+t.getMessage(); return false; }else if(causeClazz.equals("ValidationExeception")){//自定义异常 this.description = "第" +rowIndex+"行,"+t.getMessage(); return false; }else t = t.getCause(); } //this.description = parser.parseExpression("sales").getValue(objectBean,String.class); this.description = "数据错误"; return false; } //校验，校验规则是配置在objectBean对象里面 Set constraintViolations = validator.validate(objectBean); if(constraintViolations.size() >0){//校验失败时，提示相应信息 this.description = "第" +rowIndex+"行，校验出错："; for (ConstraintViolation vl : constraintViolations) { this.description = this.description + vl.getMessage()+" ; "; } return false; } return true; } private String[] getRowValues(Row row,int columnStartIndex){ String[] values = new String[fieldSize]; for(int j = columnStartIndex,t=0;t100?100:sheet.getLastRowNum(); for(int i = 0;ir.getLastCellNum()-j){ this.description = "execel表格与所给配置描述不符，请下载模板文件"; return null; } for(int k=j+1,t=1;k100?100:sheet.getLastRowNum(); int endRow = sheet.getLastRowNum(); for(int i=1;i100?100:sheet.getLastRowNum(); int endRow = sheet.getLastRowNum(); for(int i=1;i

【本文地址】

公司简介

联系我们