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 = "-";// tplpublic static final String TPL_TAG = "%s%s%s";public static final String TPL_E1 = "%s+%s";// fmtpublic 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;}}