论坛网站开发开题报告,图片网站用什么主机,企业营销推广怎么做,seo培训机构哪家好Background 要读写如下图所示的excel#xff0c;符号和单位中包含上下标#xff0c;在读写时需要特殊处理#xff1b;取值列中是科学计数法#xff0c;读写时需要特殊处理#xff1b;excel中包含多个sheet#xff0c;读的时候把所有sheet的数据读出来#xff0c;写的时候…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 MapString, Class? EXCEL_SHEET_OBJ new HashMapString, 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;MapString, ListObject 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 MapString, ListObject readFromSheet(String filename) {MapString, ListObject res new HashMap();try (FileInputStream in new FileInputStream(filename);XSSFWorkbook workbook new XSSFWorkbook(in);) {IteratorSheet sheetIterator workbook.sheetIterator();while (sheetIterator.hasNext()) {ListObject objects new ArrayList();int startRowIndex 3;Sheet sheet sheetIterator.next();String sheetName sheet.getSheetName();Class? tClass Const.EXCEL_SHEET_OBJ.get(sheetName);MapInteger, 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, MapString, ListObject 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;ListObject values sheetValues.get(sheetName);if (values.size() 0) {Class? tClass values.get(0).getClass();MapInteger, 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;}ListListint[] 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 MapInteger, Field getFieldMap(Class? tClass) {MapInteger, 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, ListListint[] tagIndexArr) {XSSFRichTextString richTextString new XSSFRichTextString(str);Listint[] subs tagIndexArr.get(0);Listint[] 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进来方法结束的时候会存储好标签位置信息。* brtagIndexList.get(0)存放的sub* brtagIndexList.get(1)存放的是sup* return 返回sub、sup处理完之后的字符串*/private static String getIndexes(String str, ListListint[] tagIndexList) {Listint[] 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;ListInteger 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;}}
