最近有一个需求，就是把excel中的内容，解析成Json对象格式的文件输出。

然后就上网找了一波资料，大神们都说用POI来做。但是我看了一下POI的解析过程，但是为了秉着高效的原则，花最少的时间去实现功能。

又让我搜到了easy poi，特此标记。晚点把学习过程记录下来。

　　官网传送门---> 官网地址

　　easy poi的理念就是为了让没有接触过POI的开发小伙伴（没错就是你），可以很方便的导入导出Excel

　　Easy POI提供了很多中导入导出的注解，以及还提供按照模板excel导出

　　当然，这里博主就只简单的介绍一下@Excel

　　@Excel中有两个重要的属性

• name : 和excel中的列名一致

• orderNum: 列的排序

  @Excel(name="Last Name", orderNum = "0")
  @JSONField(name = "First Name",ordinal = 0)
  private String firstName;
  @Excel(name="First Name", orderNum = "1")
  @JSONField(name = "Last Name", ordinal = 1)
  private String lastName;
  @Excel(name="Email Address", orderNum = "2")
  @JSONField(name="Email Address", ordinal = 2)
  private String emailAddress;

  　　要快速的导入导出，还需要用到Easy poi中的两个工具类

  　　ExcelImportUtil和ExcelExportUtil

package cn.afterturn.easypoi.excel;

import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.imports.ExcelImportServer;
import cn.afterturn.easypoi.excel.imports.sax.SaxReadExcel;
import cn.afterturn.easypoi.excel.imports.sax.parse.ISaxRowRead;
import cn.afterturn.easypoi.exception.excel.ExcelImportException;
import cn.afterturn.easypoi.handler.inter.IExcelReadRowHanlder;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.List;
import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ExcelImportUtil {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelImportUtil.class);

private ExcelImportUtil() {  
}

public static <T> List<T> importExcel(File file, Class<?> pojoClass, ImportParams params) {  
    FileInputStream in = null;

    List var4;  
    try {  
        in = new FileInputStream(file);  
        var4 = (new ExcelImportServer()).importExcelByIs(in, pojoClass, params).getList();  
    } catch (ExcelImportException var9) {  
        throw new ExcelImportException(var9.getType(), var9);  
    } catch (Exception var10) {  
        LOGGER.error(var10.getMessage(), var10);  
        throw new ExcelImportException(var10.getMessage(), var10);  
    } finally {  
        IOUtils.closeQuietly(in);  
    }

    return var4;  
}

public static <T> List<T> importExcel(InputStream inputstream, Class<?> pojoClass, ImportParams params) throws Exception {  
    return (new ExcelImportServer()).importExcelByIs(inputstream, pojoClass, params).getList();  
}

public static <T> ExcelImportResult<T> importExcelMore(InputStream inputstream, Class<?> pojoClass, ImportParams params) throws Exception {  
    return (new ExcelImportServer()).importExcelByIs(inputstream, pojoClass, params);  
}

public static <T> ExcelImportResult<T> importExcelMore(File file, Class<?> pojoClass, ImportParams params) {  
    FileInputStream in = null;

    ExcelImportResult var4;  
    try {  
        in = new FileInputStream(file);  
        var4 = (new ExcelImportServer()).importExcelByIs(in, pojoClass, params);  
    } catch (ExcelImportException var9) {  
        throw new ExcelImportException(var9.getType(), var9);  
    } catch (Exception var10) {  
        LOGGER.error(var10.getMessage(), var10);  
        throw new ExcelImportException(var10.getMessage(), var10);  
    } finally {  
        IOUtils.closeQuietly(in);  
    }

    return var4;  
}

public static <T> List<T> importExcelBySax(InputStream inputstream, Class<?> pojoClass, ImportParams params) {  
    return (new SaxReadExcel()).readExcel(inputstream, pojoClass, params, (ISaxRowRead)null, (IExcelReadRowHanlder)null);  
}

public static void importExcelBySax(InputStream inputstream, Class<?> pojoClass, ImportParams params, IExcelReadRowHanlder hanlder) {  
    (new SaxReadExcel()).readExcel(inputstream, pojoClass, params, (ISaxRowRead)null, hanlder);  
}

public static <T> List<T> importExcelBySax(InputStream inputstream, ISaxRowRead rowRead) {  
    return (new SaxReadExcel()).readExcel(inputstream, (Class)null, (ImportParams)null, rowRead, (IExcelReadRowHanlder)null);  
}  

}

ExcelImportUtil

package cn.afterturn.easypoi.excel;

import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.ExcelBatchExportServer;
import cn.afterturn.easypoi.excel.export.ExcelExportServer;
import cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelExportUtil {
private ExcelExportUtil() {
}

public static Workbook exportBigExcel(ExportParams entity, Class<?> pojoClass, Collection<?> dataSet) {  
    ExcelBatchExportServer batachServer = ExcelBatchExportServer.getExcelBatchExportServer(entity, pojoClass);  
    return batachServer.appendData(dataSet);  
}

public static void closeExportBigExcel() {  
    ExcelBatchExportServer batachServer = ExcelBatchExportServer.getExcelBatchExportServer((ExportParams)null, (Class)null);  
    batachServer.closeExportBigExcel();  
}

public static Workbook exportExcel(ExportParams entity, Class<?> pojoClass, Collection<?> dataSet) {  
    Workbook workbook = getWorkbook(entity.getType(), dataSet.size());  
    (new ExcelExportServer()).createSheet(workbook, entity, pojoClass, dataSet);  
    return workbook;  
}

private static Workbook getWorkbook(ExcelType type, int size) {  
    if (ExcelType.HSSF.equals(type)) {  
        return new HSSFWorkbook();  
    } else {  
        return (Workbook)(size < 100000 ? new XSSFWorkbook() : new SXSSFWorkbook());  
    }  
}

public static Workbook exportExcel(ExportParams entity, List<ExcelExportEntity> entityList, Collection<? extends Map<?, ?>> dataSet) {  
    Workbook workbook = getWorkbook(entity.getType(), dataSet.size());  
    (new ExcelExportServer()).createSheetForMap(workbook, entity, entityList, dataSet);  
    return workbook;  
}

public static Workbook exportExcel(List<Map<String, Object>> list, ExcelType type) {  
    Workbook workbook = getWorkbook(type, 0);  
    Iterator var3 = list.iterator();

    while(var3.hasNext()) {  
        Map<String, Object> map = (Map)var3.next();  
        ExcelExportServer server = new ExcelExportServer();  
        server.createSheet(workbook, (ExportParams)map.get("title"), (Class)map.get("entity"), (Collection)map.get("data"));  
    }

    return workbook;  
}

/\*\* @deprecated \*/  
@Deprecated  
public static Workbook exportExcel(TemplateExportParams params, Class<?> pojoClass, Collection<?> dataSet, Map<String, Object> map) {  
    return (new ExcelExportOfTemplateUtil()).createExcleByTemplate(params, pojoClass, dataSet, map);  
}

public static Workbook exportExcel(TemplateExportParams params, Map<String, Object> map) {  
    return (new ExcelExportOfTemplateUtil()).createExcleByTemplate(params, (Class)null, (Collection)null, map);  
}

public static Workbook exportExcel(Map<Integer, Map<String, Object>> map, TemplateExportParams params) {  
    return (new ExcelExportOfTemplateUtil()).createExcleByTemplate(params, map);  
}  

}

ExcelExportUtil

这个两个工具类提供了ImportExcel 和ExportExcel方法.我们导入导出excel主要就是通过这两个方法完成功能

直接上代码把，首先准备一个pojo类,声明属性。

@Excel注解用来和Excel表中列对应。

@JSONField是阿里的fastjson，用来把对象转化成Json字符串

package com.amber.data;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.alibaba.fastjson.annotation.JSONField;

public class User {
@Excel(name="Last Name", orderNum = "0")
@JSONField(name = "First Name",ordinal = 0)
private String firstName;
@Excel(name="First Name", orderNum = "1")
@JSONField(name = "Last Name", ordinal = 1)
private String lastName;
@Excel(name="Email Address", orderNum = "2")
@JSONField(name="Email Address", ordinal = 2)
private String emailAddress;

public User() {

}  
public User(String firstName, String lastName, String emailAddress) {  
    this.firstName = firstName;  
    this.lastName = lastName;  
    this.emailAddress = emailAddress;  
}

public String getFirstName() {  
    return firstName;  
}

public void setFirstName(String firstName) {  
    this.firstName = firstName;  
}

public String getLastName() {  
    return lastName;  
}

public void setLastName(String lastName) {  
    this.lastName = lastName;  
}

public String getEmailAddress() {  
    return emailAddress;  
}

public void setEmailAddress(String emailAddress) {  
    this.emailAddress = emailAddress;  
}  

}

import:

public void importUsers(String userFilePath) {  
    try {  
        ImportParams params = new ImportParams();  

　　　　　　　//调用导入方法，并且制定按照use对象格式导入
List users = ExcelImportUtil.importExcel(new File(userFilePath), User.class, params);
File file = new File(userFilePath.replace(".xls", ".json"));
if(!file.exists()){
file.createNewFile();
}
　　　　　　　　//这里是把Json字符串按照格式输出到文件夹里。如果只需要从excel中获取对象，那么下面都不需要看了。
String jsonString = JsonFormatTool.formatJson(JSON.toJSONString(users));
Writer write = new OutputStreamWriter(new FileOutputStream(file), "UTF-8");
write.write(jsonString);
write.flush();
write.close();
} catch (Exception e) {
e.printStackTrace();
}
}

Export:

public void exportUsers() throws Exception{  
    List<User> users = new ArrayList<User>();  
    users.add(new User("amber", "lei", "amberlei@123.com"));  
    users.add(new User("amber", "lei", "amberlei@123.com"));  
    users.add(new User("amber", "lei", "amberlei@123.com"));  

　　　　　　//调用exportExcel获得Workbook，然后输出到指定文件
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("user information","users"),User.class, users);
workbook.write(new FileOutputStream("C:\\Users\\amber.lei\\Documents\\Learning\\ParseExcel\\src\\main\\resources\\export_user.xls"));
}

工具类，把Json字符串按照格式输出到文件。网上随便找的，网上有很多类似的工具类，感兴趣可以直接搜搜

package com.amber.utils;

public class JsonFormatTool {
/**
* 单位缩进字符串。
*/
private static String SPACE = " ";

/\*\*  
 \* 返回格式化JSON字符串。  
 \*  
 \* @param json 未格式化的JSON字符串。  
 \* @return 格式化的JSON字符串。  
 \*/  
public static String formatJson(String json) {  
    StringBuffer result = new StringBuffer();

    int length = json.length();  
    int number = 0;  
    char key = 0;

    // 遍历输入字符串。  
    for (int i = 0; i < length; i++) {  
        // 1、获取当前字符。  
        key = json.charAt(i);

        // 2、如果当前字符是前方括号、前花括号做如下处理：  
        if ((key == '\[') || (key == '{')) {  
            // （1）如果前面还有字符，并且字符为“：”，打印：换行和缩进字符字符串。  
            if ((i - 1 > 0) && (json.charAt(i - 1) == ':')) {  
                result.append('\\n');  
                result.append(indent(number));  
            }

            // （2）打印：当前字符。  
            result.append(key);

            // （3）前方括号、前花括号，的后面必须换行。打印：换行。  
            result.append('\\n');

            // （4）每出现一次前方括号、前花括号；缩进次数增加一次。打印：新行缩进。  
            number++;  
            result.append(indent(number));

            // （5）进行下一次循环。  
            continue;  
        }

        // 3、如果当前字符是后方括号、后花括号做如下处理：  
        if ((key == '\]') || (key == '}')) {  
            // （1）后方括号、后花括号，的前面必须换行。打印：换行。  
            result.append('\\n');

            // （2）每出现一次后方括号、后花括号；缩进次数减少一次。打印：缩进。  
            number--;  
            result.append(indent(number));

            // （3）打印：当前字符。  
            result.append(key);

            // （4）如果当前字符后面还有字符，并且字符不为“，”，打印：换行。  
            if (((i + 1) < length) && (json.charAt(i + 1) != ',')) {  
                result.append('\\n');  
            }

            // （5）继续下一次循环。  
            continue;  
        }

        // 4、如果当前字符是逗号。逗号后面换行，并缩进，不改变缩进次数。  
        if ((key == ',')) {  
            result.append(key);  
            result.append('\\n');  
            result.append(indent(number));  
            continue;  
        }

        // 5、打印：当前字符。  
        result.append(key);  
    }

    return result.toString();  
}

/\*\*  
 \* 返回指定次数的缩进字符串。每一次缩进三个空格，即SPACE。  
 \*  
 \* @param number 缩进次数。  
 \* @return 指定缩进次数的字符串。  
 \*/  
private static String indent(int number) {  
    StringBuffer result = new StringBuffer();  
    for (int i = 0; i < number; i++) {  
        result.append(SPACE);  
    }  
    return result.toString();  
}  

}

完整代码地址：https://github.com/AmberBar/Learning/tree/master/ParseExcel