Java之解析Excel设计详解

“ 优雅的代码,会让你的工作事半功倍。 ” —— 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。

本文分享自微信公众号 - 23号杂货铺(sharontang0323)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-01-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券