java使用poi读写excel（处理上下标和科学计数法）

Background

• 要读写如下图所示的excel，符号和单位中包含上下标，在读写时需要特殊处理；
• 取值列中是科学计数法，读写时需要特殊处理；
• excel中包含多个sheet，读的时候把所有sheet的数据读出来，写的时候把所有sheet的数据写进去；

1、读取所有sheet数据

readFromSheet方法，使用对象接收每个sheet的数据，返回每个sheet对应的数据集合

2、写入所有sheet数据

write2Sheet方法，传入每个sheet对应的数据集合，把所有sheet的数据写入到excel中，并且是基于模板写入

3、写数据时处理上下标

richTextString方法，写数据时把有上下标信息字符串处理成富文本

4、读数据时处理上下标

getCellValue方法，读数据时给有上下标信息字符串添加tag信息

5、数值科学计数法处理

scientificNotationString方法，返回处理后的科学计数法字符串

源码

• Const.java

package com.yunlu.groundwater.constants;

import com.yunlu.groundwater.gwParameters.entities.*;

import java.util.HashMap;
import java.util.Map;

public class Const {

    // 模型参数文件导入路径
    public static final String IMPORT_MODEL_PARAM_FILEPATH = "excel-import/inputTable.xlsx";
    // 模型参数模板文件路径
    public static final String TPL_MODEL_PARAM_FILEPATH = "model/tpl/inputTable-tpl.xlsx";
    // 模型计算时输入模型参数文件路径
    public static final String INPUT_MODEL_PARAM_FILEPATH = "model/input/inputTable.xlsx";

    // excel模板解析跳过行数
    public static final Map<String, Class<?>> EXCEL_SHEET_OBJ = new HashMap<String, Class<?>>() {{
        put("3_地下水理化毒性报表", GWBPhysicalChemicalToxicity.class);
        put("4_受体暴露参数", GWBReceptorExpose.class);
        put("5_土壤性质参数", GWBSoilNature.class);
        put("6_地下水性质参数", GWBWaterNature.class);
        put("7_建筑物特征参数", GWBBuildingFeature.class);
        put("8_空气特征参数", GWBAirFeature.class);
        put("9_离场迁移参数", GWBFieldMoving.class);
    }};

    // 字符串
    public static final String S_UID = "serialVersionUID";
    // 上标
    public static final String TAG_SUB_START = "<sub>";
    public static final String TAG_SUB_END = "</sub>";
    // 下标
    public static final String TAG_SUP_START = "<sup>";
    public static final String TAG_SUP_END = "</sup>";

    // punctuation[ptn][标点]
    public static final String PTN_EMPTY = "";
    public static final String PTN_BAR_MID = "-";

    // tpl
    public static final String TPL_TAG = "%s%s%s";
    public static final String TPL_E1 = "%s+%s";

    // fmt
    public static final String FMT_DOUBLE = "0.00E00";
}

• ExcelCol.java

package com.yunlu.groundwater.resume.mapper;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelCol {
    int index() default 0;
    boolean scientificNotation() default false;
}

• GWBSoilNature.java

package com.yunlu.groundwater.gwParameters.entities;

import com.yunlu.groundwater.resume.mapper.ExcelCol;
import lombok.Data;

@Data
public class GWBSoilNature {
	
	private static final long serialVersionUID = 1L;
	
    @ExcelCol(index = 0)
    private String paramName;

    @ExcelCol(index = 1)
    private String sign;
	
    @ExcelCol(index = 2)
    private String unit;
	
    @ExcelCol(index = 3)
    private Double value;
}

• ExcelHandler.java

package com.yunlu.groundwater.resume.controller;

import com.yunlu.groundwater.constants.Const;
import com.yunlu.groundwater.resume.mapper.ExcelCol;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
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.xssf.usermodel.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.util.*;

@Slf4j
public class ExcelHandler {

    public static void main(String[] args) throws Exception {
        // 从excel读取数据
        String file = Const.IMPORT_MODEL_PARAM_FILEPATH;
        Map<String, List<Object>> sheetValues = readFromSheet(file);
        for (String sheetName : sheetValues.keySet()) {
            System.out.println(sheetName);
            for (Object o : sheetValues.get(sheetName)) {
                System.out.println(o);
            }
            System.out.println();
        }

        // 向excel写入数据
        file = Const.INPUT_MODEL_PARAM_FILEPATH;
        write2Sheet(file, sheetValues);
    }

    /**
     * 从excel文件读取所有sheet数据（有上下标信息字符串自动处理成富文本）
     *
     * @param filename 文件名称
     * @return 返回所有sheet数据对象集合
     */
    public static Map<String, List<Object>> readFromSheet(String filename) {
        Map<String, List<Object>> res = new HashMap<>();
        try (
                FileInputStream in = new FileInputStream(filename);
                XSSFWorkbook workbook = new XSSFWorkbook(in);
        ) {
            Iterator<Sheet> sheetIterator = workbook.sheetIterator();
            while (sheetIterator.hasNext()) {
                List<Object> objects = new ArrayList<>();
                int startRowIndex = 3;
                Sheet sheet = sheetIterator.next();
                String sheetName = sheet.getSheetName();
                Class<?> tClass = Const.EXCEL_SHEET_OBJ.get(sheetName);
                Map<Integer, Field> fieldMap = getFieldMap(tClass);
                for (int rowIndex = startRowIndex; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                    Row row = sheet.getRow(rowIndex);
                    Object t = tClass.newInstance();
                    for (Integer colIndex : fieldMap.keySet()) {
                        Field field = fieldMap.get(colIndex);
                        boolean scientificNotation = getColScientificNotation(field);
                        Cell cell = row.getCell(colIndex);
                        if (cell != null) {
                            String cellValue = getCellValue(cell, scientificNotation, workbook);
                            setFieldValue(field, cellValue, t);
                        }
                    }
                    if (!isAllFieldNull(t)) {
                        objects.add(t);
                    }
                }
                res.put(sheetName, objects);
            }
        } catch (Exception e) {
            log.error("readFromSheet error", e);
            e.printStackTrace();
        }
        return res;
    }

    /**
     * 把数据写入到excel文件中，指定sheet、起始行索引和起始列索引（有上下标信息字符串自动处理成富文本）
     *
     * @param filename    文件名称
     * @param sheetValues <sheet名称,数据>
     */
    public static void write2Sheet(String filename, Map<String, List<Object>> sheetValues) {
        String tplFile = Const.TPL_MODEL_PARAM_FILEPATH;
        try (
                FileOutputStream fos = new FileOutputStream(filename);
                FileInputStream fis = new FileInputStream(tplFile);
                XSSFWorkbook workbook = new XSSFWorkbook(fis);
        ) {
            for (String sheetName : sheetValues.keySet()) {
                int startRowIndex = 3;
                List<Object> values = sheetValues.get(sheetName);
                if (values.size() > 0) {
                    Class<?> tClass = values.get(0).getClass();
                    Map<Integer, Field> fieldMap = getFieldMap(tClass);
                    XSSFSheet sheet = workbook.getSheet(sheetName);
                    for (Object t : values) {
                        XSSFRow row = sheet.getRow(startRowIndex);
                        for (Integer colIndex : fieldMap.keySet()) {
                            Field field = fieldMap.get(colIndex);
                            boolean scientificNotation = getColScientificNotation(field);
                            String content;
                            try {
                                content = fieldMap.get(colIndex).get(t).toString();
                            } catch (Exception e) {
                                content = Const.PTN_EMPTY;
                            }
                            List<List<int[]>> tagIndexArr = new ArrayList<>();
                            if (containTag(content)) {
                                content = getIndexes(content, tagIndexArr);
                            }
                            XSSFCell cell = row.getCell(colIndex);
                            if (null == cell) {
                                cell = row.createCell(colIndex);
                            }
                            if (tagIndexArr.size() > 0) {
                                cell.setCellValue(richTextString(workbook, content, tagIndexArr));
                            } else {
                                if (scientificNotation && !StringUtils.isEmpty(content) && !Const.PTN_BAR_MID.equals(content)) {
                                    cell.setCellValue(Double.parseDouble(content));
                                } else {
                                    cell.setCellValue(content);
                                }
                            }
                        }
                        startRowIndex++;
                    }
                }
            }
            workbook.write(fos);
        } catch (Exception e) {
            log.error("write2Sheet error", e);
            e.printStackTrace();
        }
    }

    /**
     * @param val 数值
     * @return 返回科学计数法字符串
     */
    public static String scientificNotationString(Double val) {
        String res = new DecimalFormat(Const.FMT_DOUBLE).format(val);
        if (val >= 1) {
            int length = res.length();
            String prefix = res.substring(0, length - 2);
            String suffix = res.substring(length - 2, length);
            res = String.format(Const.TPL_E1, prefix, suffix);
        }
        return res;
    }

    /**
     * 获取字段集合信息
     */
    private static Map<Integer, Field> getFieldMap(Class<?> tClass) {
        Map<Integer, Field> fieldMap = new HashMap<>();
        for (Field field : tClass.getDeclaredFields()) {
            ExcelCol col = field.getAnnotation(ExcelCol.class);
            if (null != col) {
                field.setAccessible(true);
                fieldMap.put(col.index(), field);
            }
        }
        return fieldMap;
    }

    /**
     * 获取字段是否需要科学计数法表示
     */
    private static boolean getColScientificNotation(Field field) {
        return field.getAnnotation(ExcelCol.class).scientificNotation();
    }

    /**
     * 判断对象的所有字段值是否为空
     */
    private static <T> boolean isAllFieldNull(T t) {
        boolean res = true;
        Class<?> tClass = t.getClass();
        for (Field field : tClass.getDeclaredFields()) {
            field.setAccessible(true);
            try {
                Object fieldValue = field.get(t);
                if (!Const.S_UID.equals(field.getName()) && null != fieldValue) {
                    res = false;
                }
            } catch (Exception ignored) {
            }
        }
        return res;
    }

    /**
     * 设置字段值
     */
    private static <T> void setFieldValue(Field field, String value, T t) throws Exception {
        if (null != field) {
            String type = field.getType().toString();
            if (StringUtils.isBlank(value)) {
                field.set(t, null);
            } else if (type.endsWith("String")) {
                field.set(t, value);
            } else if (type.endsWith("long") || type.endsWith("Long")) {
                field.set(t, Long.parseLong(value));
            } else if (type.endsWith("double") || type.endsWith("Double")) {
                field.set(t, Double.parseDouble(value));
            } else {
                field.set(t, value);
            }
        }
    }

    /**
     * @param cell cell
     * @return 返回cell内容（有上下标信息字符串自动处理成富文本）
     */
    private static String getCellValue(Cell cell, boolean scientificNotation, XSSFWorkbook workbook) {
        switch (cell.getCellType()) {
            case NUMERIC:
                double cellValue = cell.getNumericCellValue();
                return scientificNotation ? scientificNotationString(cellValue) : String.valueOf(cellValue);
            case STRING:
                XSSFFont font;
                XSSFRichTextString rts = (XSSFRichTextString) cell.getRichStringCellValue();
                StringBuilder value = new StringBuilder();
                if (rts.numFormattingRuns() > 1) {
                    for (int i = 0; i < rts.numFormattingRuns(); i++) {
                        int runLength = rts.getLengthOfFormattingRun(i);
                        int runIndex = rts.getIndexOfFormattingRun(i);
                        String temp = rts.toString().substring(runIndex, (runIndex + runLength));
                        try {
                            font = rts.getFontOfFormattingRun(i);
                            font.getTypeOffset();
                        } catch (NullPointerException e) {
                            font = workbook.getFontAt(XSSFFont.DEFAULT_CHARSET);
                            font.setTypeOffset(XSSFFont.SS_NONE);
                        }
                        temp = addTagInfo(temp, font.getTypeOffset());
                        value.append(temp);
                    }
                } else {
                    value.append(cell.getStringCellValue());
                }
                return value.toString();
            default:
                return Const.PTN_EMPTY;
        }
    }

    /**
     * 处理有上下标的字符串
     */
    private static String addTagInfo(String str, short typeOffset) {
        if (typeOffset == XSSFFont.SS_SUPER) {
            str = String.format(Const.TPL_TAG, Const.TAG_SUP_START, str, Const.TAG_SUP_END);
        }
        if (typeOffset == XSSFFont.SS_SUB) {
            str = String.format(Const.TPL_TAG, Const.TAG_SUB_START, str, Const.TAG_SUB_END);
        }
        return str;
    }

    /**
     * 有上下标信息字符串处理成富文本
     *
     * @param str 字符串
     * @return 处理后的富文本
     */
    private static XSSFRichTextString richTextString(XSSFWorkbook workbook, String str, List<List<int[]>> tagIndexArr) {
        XSSFRichTextString richTextString = new XSSFRichTextString(str);
        List<int[]> subs = tagIndexArr.get(0);
        List<int[]> sups = tagIndexArr.get(1);
        if (subs.size() > 0) {
            XSSFFont font = workbook.createFont();
            font.setTypeOffset(XSSFFont.SS_SUB);
            for (int[] pair : subs) {
                richTextString.applyFont(pair[0], pair[1], font);
            }
        }
        if (sups.size() > 0) {
            XSSFFont font = workbook.createFont();
            font.setTypeOffset(XSSFFont.SS_SUPER);
            for (int[] pair : sups) {
                richTextString.applyFont(pair[0], pair[1], font);
            }
        }
        return richTextString;
    }

    /**
     * 获取下一对标签的index，不存在这些标签就返回null
     *
     * @param str 字符串
     * @param tag SUB_START或者SUP_START
     * @return int[]中有两个元素，第一个是开始标签的index，第二个元素是结束标签的index
     */
    private static int[] getNextTagsIndex(String str, String tag) {
        int firstStart = str.indexOf(tag);
        if (firstStart > -1) {
            int firstEnd = 0;
            if (tag.equals(Const.TAG_SUB_START)) {
                firstEnd = str.indexOf(Const.TAG_SUB_END);
            } else if (tag.equals(Const.TAG_SUP_START)) {
                firstEnd = str.indexOf(Const.TAG_SUP_END);
            }
            if (firstEnd > firstStart) {
                return new int[]{firstStart, firstEnd};
            }
        }
        return new int[]{};
    }

    /**
     * 移除下一对sub或者sup或者u或者strong或者em标签
     *
     * @param str 字符串
     * @param tag SUB_START或者SUP_START
     * @return 返回移除后的字符串
     */
    private static String removeNextTags(String str, String tag) {
        str = str.replaceFirst(tag, Const.PTN_EMPTY);
        if (tag.equals(Const.TAG_SUB_START)) {
            str = str.replaceFirst(Const.TAG_SUB_END, Const.PTN_EMPTY);
        } else if (tag.equals(Const.TAG_SUP_START)) {
            str = str.replaceFirst(Const.TAG_SUP_END, Const.PTN_EMPTY);
        }
        return str;
    }

    /**
     * 判断是不是包含sub、sup标签
     *
     * @param str 字符串
     * @return 返回是否包含
     */
    private static boolean containTag(String str) {
        return (str.contains(Const.TAG_SUB_START) && str.contains(Const.TAG_SUB_END)) || (str.contains(Const.TAG_SUP_START) && str.contains(Const.TAG_SUP_END));
    }

    /**
     * 处理字符串，得到每个sub、sup标签的开始和对应的结束的标签的index，方便后面根据这个标签做字体操作
     *
     * @param str          字符串
     * @param tagIndexList 传一个新建的空list进来，方法结束的时候会存储好标签位置信息。
     *                     <br>tagIndexList.get(0)存放的sub
     *                     <br>tagIndexList.get(1)存放的是sup
     * @return 返回sub、sup处理完之后的字符串
     */
    private static String getIndexes(String str, List<List<int[]>> tagIndexList) {
        List<int[]> subs = new ArrayList<>(), sups = new ArrayList<>();
        while (true) {
            int[] sub_pair = getNextTagsIndex(str, Const.TAG_SUB_START), sup_pair = getNextTagsIndex(str, Const.TAG_SUP_START);
            boolean subFirst = false, supFirst = false;
            List<Integer> a = new ArrayList<>();
            if (sub_pair.length > 0) {
                a.add(sub_pair[0]);
            }
            if (sup_pair.length > 0) {
                a.add(sup_pair[0]);
            }
            Collections.sort(a);
            if (sub_pair.length > 0) {
                if (sub_pair[0] == Integer.parseInt(a.get(0).toString())) {
                    subFirst = true;
                }
            }
            if (sup_pair.length > 0) {
                if (sup_pair[0] == Integer.parseInt(a.get(0).toString())) {
                    supFirst = true;
                }
            }
            if (subFirst) {
                str = removeNextTags(str, Const.TAG_SUB_START);
                // <sub>标签被去掉之后，结束标签需要相应往前移动
                sub_pair[1] = sub_pair[1] - Const.TAG_SUB_START.length();
                subs.add(sub_pair);
                continue;
            }
            if (supFirst) {
                str = removeNextTags(str, Const.TAG_SUP_START);
                // <sup>标签被去掉之后，结束标签需要相应往前移动
                sup_pair[1] = sup_pair[1] - Const.TAG_SUP_START.length();
                sups.add(sup_pair);
                continue;
            }
            if (sub_pair.length == 0 && sup_pair.length == 0) {
                break;
            }
        }
        tagIndexList.add(subs);
        tagIndexList.add(sups);
        return str;
    }

}