poi

note

This article is a simple wrapper using org.apache.poi that applies to most Excel import and export functions. Reflection may be used in this process, so if you have an extreme obsessive about performance, check it out.

sequence

Since POI itself is only a toolkit for Excel and other Office software, it is necessary to do a simplified encapsulation to simplify code coupling in some routine Excel import and export.

A, the present situation

I have experienced several companies code packaging, import and export generally exist the following situation.

1.1 the import

  1. Pass in the file address, return the Sheet object, loop through the business code, do the corresponding type conversion, business processing (code framework for the year 2000)
  2. Pass in the file address, return the List

    Object, external directly strong
    ,>
  3. Pass in the file address, return the List

    object, externally convert the String object to the corresponding type
    ,>

Conclusion: If there are only the above options, I prefer the second option, after all, I am very friendly to the external layer

1.2 export

  1. Iterate over the wrap sheet directly in logical code, passing it into the method that generates file (code framework for the year 2000)
  2. We loop through the List object, convert it to a List
    > object, pass it in with fieldName to a method that encapsulates excel’s generation, and use map.get() internally

  3. Pass the List object directly into the fieldName method that encapsulates excel’s generation, internally convert the Model object to a JSONObject, and then use the jsonobj.get () method
  4. We first convert the List to a JSONArray, pass it in with fieldName to a method that encapsulates excel’s generation, internally convert the Model object to a JSONObject, and then use the jsonobj.get () method. (use this kind of practice, should be according to the analysis in order to perform jsonConfig. RegisterJsonValueProcessor (Date. Class, new JsonDateValueProcessor (” MM – dd yyyy – HH: MM: ss “)); This line of code, probably to solve the date type formatting problem)

Conclusion: If there are only the above options, I prefer the third option, the third option is only traversed once, and the external processing is not done. However, according to the fourth mode, the third mode still has the date format problem, which we will analyze how to deal with later.

Second, import,

2.1 Method Definition

/** * Excel import *@paramKeys array of field names such as [" ID ", "name",... *@paramFilePath Specifies the physical address of a file@return 
* @author yzChen
* @dateDecember 18, 2016 2:46:51 PM */
public static List<Map<String, Object>> imp(String filePath, String[] keys)
    throws Exception {}
Copy the code

2.2 Cyclic processing module

// Iterate over all columns of the row
for (short j = 0; j < cols; j++) {
    cell = row.getCell(j);
    if(null == cell) continue;	// if null, the next column
    
    // According to the type returned by poI, do corresponding GET processing
    if(Cell.CELL_TYPE_STRING == cell.getCellType()) {
        value = cell.getStringCellValue();
    } else if(Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
        value = cell.getNumericCellValue();
        
        // Since the date-type format is also considered numeric, it is checked if it is a date-type format, and if so, it is read as a date-type format
        if(cell.getCellStyle().getDataFormat() > 0) { value = cell.getDateCellValue(); }}else if(Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
        value = cell.getBooleanCellValue();
    } else if(Cell.CELL_TYPE_BLANK == cell.getCellType()) {
        value = cell.getDateCellValue();
    } else {
        throw new Exception("At row: %s, col: %s, can not discriminate type!");
    }
    
    map.put(keys[j], value);
}
Copy the code

2.3 the use of

String filePath = "E:/order.xls";
String[] keys = new String[]{"id"."brand"};

List<Map<String, Object>> impList;
try {
    impList = ExcelUtil.imp(filePath, keys);
    
    for (Map<String, Object> map : impList) {
        System.out.println(map.get("brand")); }}catch (Exception e) {
    e.printStackTrace();
}
Copy the code

2.4 analysis

  1. The entry only needs to pass in the file name and the external key that needs to be read
  2. Internal processing, for the numeric type, date type, string type has done corresponding processing, external directly to the corresponding type can be strong

Third, export

3.1 Method Definition

/** * Excel export *@paramFileNamePath Name of the file to be exported *@paramSheetName Name of the exported sheet *@paramList data set *@paramFirst line of titles header *@paramFieldNames array of fieldNames *@return
* @throws Exception    
* @author yzChen
* @dateMay 6, 2017 3:53:47 PM */
public static <T> File export(String fileNamePath, String sheetName, List
       
         list, String[] titles, String[] fieldNames)
        throws Exception {}
Copy the code

3.2 Cycle processing module

// Iterate over the generated rows, reflecting the get method for the fields
for (int i = 0; i < list.size(); i++) {
    t = list.get(i);
    HSSFRow row = sheet.createRow(i+1);
    Class<? extends Object> clazz = t.getClass();
    for(int j = 0; j < fieldNames.length; j++){
        methodName = "get" + capitalize(fieldNames[j]);
        try {
            method = clazz.getDeclaredMethod(methodName);
        } catch (java.lang.NoSuchMethodException e) {	// If the method does not exist, check whether the parent class exists. Only one level of parent class is supported here. To support more, a while loop is recommended
            if(null != clazz.getSuperclass()) {
                method = clazz.getSuperclass().getDeclaredMethod(methodName);
            }
        }
        if(null == method) {
            throw new Exception(clazz.getName() + " don't have menthod --> " + methodName);
        }
        ret = null == method.invoke(t) ? null : method.invoke(t) + "";
        setCellGBKValue(row.createCell(j), ret + ""); }}Copy the code

3.3 the use of

String[] titles = new String[]{"Id"."Brand"};
String[] fieldNames = new String[]{"id"."brand"};
List<Order> expList = new ArrayList<Order>();
Order order = new Order();
order.setId(1L);
order.setBrand("Third party manual valve");
expList.add(order);
order = new Order();
order.setId(2L);
order.setBrand("scsdsad");
expList.add(order);

String fileNamePath = "E:/order.xls";
try {
    ExcelUtil.export(fileNamePath, "Order", expList, titles, fieldNames);
} catch (Exception e) {
    e.printStackTrace();
}
Copy the code

3.4 summarize

  1. The entry basically requires passing in the List data set, along with the fieldNames field name
  2. Internal processing, is directly through reflection to get the return value of the get method, strong conversion to a string for export
  3. In order to be compatible with the design of some common fields that inherit from the parent class, a layer of method reading from the parent class is added

Fourth, the date type export processing

1.1 Date Field Exports content in the specified format

  1. It is recommended that you add an extension field to the Model class and wrap a GET method that simply transforms the original field, and that fieldName pass the extension field when exported. For example createTime, the following is an example:
private Date createTime;
private String createTimeStr;	// Extend the field

public Date getCreateTime(a) {
    return createTime;
}

public void setCreateTime(Date createTime) {
    this.createTime = createTime;
}

public String getCreateTimeStr(a) {
    createTimeStr = DateUtil.formatDatetime(this.createTime);
    return createTimeStr;
}

Copy the code

My Blog

blog.guijianpan.com

Technical communication