Java之解析Excel设计详解
- 2019 年 10 月 8 日
- 筆記
“ 优雅的代码，会让你的工作事半功倍。 ” —— 23号老板
0
1
引入
原创：爱吃回锅肉的瘦子、凌空夜望
相关文章系列：
前面几篇我们简单介绍了解析excel，这回是来讲解如何设计一个关于Excel操作的简单工具类，并将每一行数据传化成javabean的形式来进行使用。
在写代码之前，我们必须得先仔细分析Excel的属性，因为代码毕竟是为功能实现服务，而功能实现是基于事物本身客观存在的属性。
Excel的文件格式有xls、xlsx、xlsm、xltx、xlsb、xltx、xlam。常用的是xls，xlsx。我们目前只在这两格式进行解析。
如何判断他是什么格式；简单的方式利用文件名的后缀名判断，需要使用到api为String.endwith()。这个方法简单，性能相对好些。那么后缀名不能获取怎么办？是否可以根据文件流的方式知道excel格式呢？
这时候我们需要去查阅poi官方文档，发现有两种方式：
POI官网：
http://poi.apache.org/components/spreadsheet/quick-guide.html
1、抽象工厂模式 ：WorkbookFactory. Create(Fileinputstream)方法，这样无论什么格式都能基于接口形式进行解析；另外一种使用两个工具类来进行判断：
2、POIFSFileSystem.hasPOIFSHeader()，POIXMLDocument.hasOOXMLHeader()；分别判断是xls，xlsx。
完成了excel格式问题现在目前已经解决，然后我们在看看poi文档发现xls、xlsx的类都是实现了wookbook，在观察里面的接口方法都有我们解析需要的方法。也就是说我们在解析时候，我们可以使用wookbook接口的方式进行，以此减少我们代码量，也就是设计模式原则中的里氏替换原则。
02
理解
在一个excel里有多个sheet，每个sheet都有row，每个row里有cell；这里都可以看成对象，然后对象中有对此子属性进行依赖；现在我们得进入我们实现功能（解析成Javabean）结合属性进行分析：我们要解析成javabean必须得有字段名与值，多个sheet意味着可能有两种情况，一种是每个sheet的表头都是一样的，这样只需要一个model就可以了。另外存在两种不同的sheet，这时候我们必须传多个model进去，进行赋值。但由于篇幅有限，我现在先讲解最简单的，每个sheet都一样表头且表头设置在第一行。接下来是row，Row里有cell，cell里有值，而值有不同格式，文本格式，数字格式等等。
现在我们总结了一下 excel属性 有多种文件格式，里面有sheet，sheet有row，row有cell，也就是说我们至少要内嵌3个循环体才能到达我们要的值，每个值根据格式不同，还得不断判断。而且对于表头我们还需进行翻译，以及使用键值对的方式，所以我们在三个循环体外围，得有一个一个循环体专门用来翻译表头成英文;
现在我们理清了整个流程，现在有个问题是针对不同excel上传上来，我们要如何翻译表头：我的想法是单独写一个翻译类。对于数据校验，我目前想到两种实现方式；一种是使用泛型，然后泛型限制成必须实现一个父类model，model规定了一个抽象方法，在解析的时候使用，也就是模板设计模式；另外一种就是存储在map中，利用beanutil进行转换成bean，然后bean写校验方法。这两种中，综合来看第一种就是时间复杂度会相对较低，缺点必须model实现接口或父类。第二种呢通用性较好，理解起来简单。但综合考虑，我更倾向于用泛型的方式进行，一对于以后的增加功能更好修改，二减少了客户端的代码量，维护容易些。
0
3
实现
假设我们有一个excel，表头有姓名，年龄，证件号码，证件类型。相应的我们的字段命名则应该为name，age，certificatetype, certificateno;
对于证件类型为了方便枚举或者数据库存储的时候减少储存，我们采取字段翻译，例如身份证用01表示，驾驶证有02表示；对于字段的翻译我们可以另外封装一个类进行翻译，我这边采取的是map形式。
那么字段翻译在什么时候做呢？显然表头翻译就是在遍历的时候，而对于model在set时候进行。
那么接下来代码就清晰了：
首先定义一个model接口：一个方法进行空校验（或者其他必填校验），第二个就是存储数据位置
public interface NameToField extends Serializable { //存储该数据在第几行，可以提示使用者哪行出现错误数据 public void setRow(int row); public boolean isEmpty(); }
定义一个model， 实现接口NameToField ：
public class peopleInfo implements NameToField{ private Integer row; private String name; private Integer age; private String certificateno; //setter、getter private String certificatetype; public String getCertificatetype() { return certificatetype; } //这边我使用的是org.apache.commons.beanutils.BeanUtils map传bean，他的赋值是使用set方式，所以在此处进行字段翻译 public void setCertificatetype(String certificatetype) { this.certificatetype = TranslationField.getCertificatetypes().get(certificatetype); } @Override public void setRow(int row) { this.row=row; } //此处只是简单的校验 @Override public boolean isEmpty() { return StringUtils.isBlank(this.name) &&StringUtils.isBlank(this.certificateno) &&StringUtils.isBlank(this.certificatetype); } }
字段翻译类
public final class TranslationField { //数据里的字段翻译 private static final Map<String, String> certificatetypes; //表头字段翻译 private static final Map<String, String> fieldMap; static { //此处有线程危险问题，所以这些map是不对外开放操作的 certificatetypes=new HashMap<String, String>(); fieldMap=new HashMap<>(); fieldMap.put("姓名","name"); fieldMap.put("证件类型","certificatetype"); fieldMap.put("证件号码","certificateno"); fieldMap.put("年龄","age"); certificatetypes.put("身份证","01"); certificatetypes.put("驾驶证","02"); } public static Map<String, String> getCertificatetypes() { return certificatetypes; } public static Map<String, String> getfieldMap() { return fieldMap; } }
定义工具类：
/暂时只支持转一个model public class ExcelUtil<T extends NameToField> { private String filePath; private static final String EXCEL_2003 = ".xls"; private static final String EXCEL_2007 = ".xlsx"; private Workbook workbook; private List<Sheet> sheets; private List<Row> rows; private List<Cell> cells; private List<T> models; private Class<T> t; /** * @param filePath 文件名称 * @param inputStream 文件流 * @param t excel转Model类型 * @throws IOException */ public ExcelUtil(String filePath, InputStream inputStream, Class<T> t) throws Exception { this.filePath = filePath; workbook = initWorkBook(filePath, inputStream); models = new ArrayList<T>(); this.t=t; init(); } public Workbook initWorkBook(String filePath, InputStream inputStream) throws IOException, InvalidFormatException { Workbook workbook = null; if (filePath.endsWith(EXCEL_2003)) { workbook = new HSSFWorkbook(inputStream); } else if (filePath.endsWith(EXCEL_2007)) { workbook = new XSSFWorkbook(inputStream); }else{ //此处只需要这个方法就可以了 workbook= WorkbookFactory.create(inputStream); } return workbook; } public void init() throws Exception { sheets = new ArrayList<Sheet>(); rows = new ArrayList<Row>(); cells = new ArrayList<Cell>(); for (int i = 0, length = workbook.getNumberOfSheets(); i < length; ++i) { Sheet sheet = workbook.getSheetAt(i); sheets.add(sheet); Row firstRow = sheet.getRow(sheet.getFirstRowNum()); //获取首行标题，并翻译成字段； String name ; for (int j = sheet.getFirstRowNum() + 1; j <= sheet.getLastRowNum(); j++) { Row row = sheet.getRow(j); rows.add(row); //存储每一行的信息 Map<String, Object> map = new HashMap<String, Object>(); for (int k = firstRow.getFirstCellNum(); k < firstRow.getLastCellNum(); k++) { if (row==null)continue; Cell cell = row.getCell(k); cells.add(cell); name = TranslationField.getfieldMap().get(firstRow.getCell(k).getStringCellValue()); if (name != null) map.put(name, getCellValue(cell)); } T ta = (T) t.newInstance(); //用来存储行列信息 ta.setRow(j+1); BeanUtils.populate(ta, map); //对于excel可能出现""类似于需要对他进行非空校验，如果有其他特殊比如邮箱正则 if (ta!=null&&!ta.isEmpty()){ models.add(ta); } } } } public Workbook getWorkbook() { return workbook; } public List<Sheet> getSheets() { return sheets; } public List<Row> getRows() { return rows; } public List<Cell> getCells() { return cells; } public List<T> getModels() { return models; } //由于使用yyyyMMdd时间格式是方便转成int类型进行大小判断 private static final DateFormat df = new SimpleDateFormat("yyyyMMdd"); private static final DecimalFormat decimalFormat = new DecimalFormat("#.#"); /** * 根据Cell源码及官方文档可知每个单元格的值都是根据Cell里的枚举 * @param cell * @return */ private static Object getCellValue(Cell cell) { if (cell == null || (cell.getCellType() == Cell.CELL_TYPE_STRING && StringUtils.isBlank(cell .getStringCellValue()))) { return null; } int cellType = cell.getCellType(); switch (cellType) { case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue(); case Cell.CELL_TYPE_ERROR: return cell.getErrorCellValue(); case Cell.CELL_TYPE_FORMULA: return cell.getCellFormula(); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); return df.format(date); } else { return decimalFormat.format(cell.getNumericCellValue()); } case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); default: return null; } } }
0
4
测试
客户端校验：
public static void main(String[] args) throws Exception { Gson gson=new Gson(); FileInputStream in=new FileInputStream("C:\bboyHan\a.xlsx"); ExcelUtil excelUtil=new ExcelUtil<>("a.xlsx",in, PeopleInfo.class); System.out.println(gson.toJson(excelUtil.getModels())); }
结果：
0
5
小结
文章重点讲解如何进行一个工具类的设计以及他的一些思维方式；纯属个人的一些浅见，还望有朋友一起交流学习。
对于poi解析excel是非常吃内存的，所以读者有时间的话可以了解poi中的sax，或者基于poi的开源工具easyExcel。
