哪家可以做网站,忘了网站链接怎么做,做一个网站需要多少钱 怎么做,网站设计与建设论文本文仅针对文字相关的合并做了处理 #xff0c;图片合并及保存需要另做处理#xff01;#xff01; 目标#xff1a;Excel合并行内容的导入 结果#xff1a; 1. ExcelUtil.java 类#xff0c;新增方法#xff1a;判断是否是合并行 /*** 新增 合并行相关代码#xff1a;…
本文仅针对文字相关的合并做了处理 图片合并及保存需要另做处理 目标Excel合并行内容的导入 结果 1. ExcelUtil.java 类新增方法判断是否是合并行 /*** 新增 合并行相关代码判断是否是合并行** param sheet* param row* param column* return*/private boolean isMergedRow(Sheet sheet, int row, int column) {int sheetMergeCount sheet.getNumMergedRegions();//当前表格(sheet)中所有合单元格总数//遍历所有合并单元格for (int i 0; i sheetMergeCount; i) {CellRangeAddress cellAddresses sheet.getMergedRegion(i);//当前合并单元格对象int firstColumn cellAddresses.getFirstColumn();//当前合并单元格的首列int lastColumn cellAddresses.getLastColumn();//当前合并单元格的末列int firstRow cellAddresses.getFirstRow();//当前合并单元格的首行int lastRow cellAddresses.getLastRow();//当前合并单元格的末行//指定的行 包含在合并单元格中if (row firstRow row lastRow) {//指定的列 包含在合并单元格中if (column firstColumn column lastColumn) {return true;}}}return false;} 2. ExcelUtil.java 类新增方法获取合并行单元格数据
思路
POI对于Excel的处理合并行中 只有首列能获取到值 非首列的其他列数据默认是空。由于是合并行则可以认为 合并行内所有列的数据 都与合并行的首列数据相同。
所以以下代码中判断该列如果包含在合并行内则该列值默认赋值为 所在合并行的首列值。 图1 Excel中的2/3/4行内的合并行内容相同 POI对Excel处理时默认获取到的值大概如图2下面代码则在图2逻辑上 对3/4行的空内容做赋值操作 /*** 新增 合并行相关代码获取合并行的数据** param sheet* param row* param column* return*/private String getMergedRegionValue(Sheet sheet, int row, int column) {// 获得该sheet所有合并单元格数量int sheetMergeCount sheet.getNumMergedRegions();for (int i 0; i sheetMergeCount; i) {// 获得合并区域CellRangeAddress cellAddresses sheet.getMergedRegion(i);int firstColumn cellAddresses.getFirstColumn();int lastColumn cellAddresses.getLastColumn();int firstRow cellAddresses.getFirstRow();int lastRow cellAddresses.getLastRow();/*判断传入的单元格的行号列号是否在合并单元格的范围内如果在合并单元格的范围内择返回合并区域的首单元格格值*/if (row firstRow row lastRow) {if (column firstColumn column lastColumn) {Row firRow sheet.getRow(firstRow);/* 合并行使用的获取单元格内容方法Cell firCell firRow.getCell(firstColumn);return getCellValue(firCell);*/return getCellValue(firRow, firstColumn).toString();//改为使用 若依自带获取单元格内容方法}}}// 如果该单元格行号列号不在任何一个合并区域则返回nullreturn null;} 3. ExcelUtil.java的importExcel()方法中 遍历行内所有单元格内容下新增 合并行的判断 和 其内单元格内容获取 // 新增 合并行相关代码块 // 判断是否合并行 isMergedRow(sheet, rowNum, column)是合并行则获取合并行数据 重新赋值给当前列
if (isMergedRow(sheet, i, entry.getKey())) val getMergedRegionValue(sheet, i, entry.getKey());// 新增 合并行相关代码块
importExcel()方法完整代码 /*** 对excel表单指定表格索引名转换成list* * param sheetName 表格索引名* param titleNum 标题占用行数* param is 输入流* return 转换后集合*/public ListT importExcel(String sheetName, InputStream is, int titleNum) throws Exception{this.type Type.IMPORT;this.wb WorkbookFactory.create(is);ListT list new ArrayListT();// 如果指定sheet名,则取指定sheet中的内容 否则默认指向第1个sheetSheet sheet StringUtils.isNotEmpty(sheetName) ? wb.getSheet(sheetName) : wb.getSheetAt(0);if (sheet null){throw new IOException(文件sheet不存在);}boolean isXSSFWorkbook !(wb instanceof HSSFWorkbook);//获取文档指定sheet中所有图片图片位置、文件流MapString, PictureData pictures;if (isXSSFWorkbook){pictures getSheetPictures07((XSSFSheet) sheet, (XSSFWorkbook) wb);}else{pictures getSheetPictures03((HSSFSheet) sheet, (HSSFWorkbook) wb);}// 获取最后一个非空行的行下标比如总行数为n则返回的为n-1int rows sheet.getLastRowNum();if (rows 0){// 定义一个map用于存放excel列的序号和field.MapString, Integer cellMap new HashMapString, Integer();// 获取表头Row heard sheet.getRow(titleNum);for (int i 0; i heard.getPhysicalNumberOfCells(); i){Cell cell heard.getCell(i);if (StringUtils.isNotNull(cell)){String value this.getCellValue(heard, i).toString();cellMap.put(value, i);}else{cellMap.put(null, i);}}// 有数据时才处理 得到类的所有field.ListObject[] fields this.getFields();MapInteger, Object[] fieldsMap new HashMapInteger, Object[]();for (Object[] objects : fields){Excel attr (Excel) objects[1];Integer column cellMap.get(attr.name());if (column ! null){fieldsMap.put(column, objects);}}//遍历每一行for (int i titleNum 1; i rows; i){// 从第2行开始取数据,默认第一行是表头.Row row sheet.getRow(i);// 判断当前行是否是空行if (isRowEmpty(row)){continue;}T entity null;//遍历当前行所有列 逐列字段值获取并赋值给entity对象的各属性for (Map.EntryInteger, Object[] entry : fieldsMap.entrySet()){Object val this.getCellValue(row, entry.getKey());//获取当前列 默认值// 新增 合并行相关代码块 // 判断是否合并行 isMergedRow(sheet, rowNum, column)是合并行则获取合并行首列数据 重新赋值给当前列if (isMergedRow(sheet, i, entry.getKey())) val getMergedRegionValue(sheet, i, entry.getKey());// 新增 合并行相关代码块 // 如果不存在实例则新建.entity (entity null ? clazz.newInstance() : entity);// 从map中得到对应列的field.Field field (Field) entry.getValue()[0];Excel attr (Excel) entry.getValue()[1];// 取得类型,并根据对象类型设置值.Class? fieldType field.getType();if (String.class fieldType){String s Convert.toStr(val);if (StringUtils.endsWith(s, .0)){val StringUtils.substringBefore(s, .0);}else{String dateFormat field.getAnnotation(Excel.class).dateFormat();if (StringUtils.isNotEmpty(dateFormat)){val parseDateToStr(dateFormat, val);}else{val Convert.toStr(val);}}}else if ((Integer.TYPE fieldType || Integer.class fieldType) StringUtils.isNumeric(Convert.toStr(val))){val Convert.toInt(val);}else if ((Long.TYPE fieldType || Long.class fieldType) StringUtils.isNumeric(Convert.toStr(val))){val Convert.toLong(val);}else if (Double.TYPE fieldType || Double.class fieldType){val Convert.toDouble(val);}else if (Float.TYPE fieldType || Float.class fieldType){val Convert.toFloat(val);}else if (BigDecimal.class fieldType){val Convert.toBigDecimal(val);}else if (Date.class fieldType){if (val instanceof String){val DateUtils.parseDate(val);}else if (val instanceof Double){val DateUtil.getJavaDate((Double) val);}}else if (Boolean.TYPE fieldType || Boolean.class fieldType){val Convert.toBool(val, false);}if (StringUtils.isNotNull(fieldType)){String propertyName field.getName();if (StringUtils.isNotEmpty(attr.targetAttr())){propertyName field.getName() . attr.targetAttr();}if (StringUtils.isNotEmpty(attr.readConverterExp())){val reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());}else if (StringUtils.isNotEmpty(attr.dictType())){val reverseDictByExp(Convert.toStr(val), attr.dictType(), attr.separator());}else if (!attr.handler().equals(ExcelHandlerAdapter.class)){val dataFormatHandlerAdapter(val, attr, null);}else if (ColumnType.IMAGE attr.cellType() StringUtils.isNotEmpty(pictures)){//获取指定行指定列中的图片文件从获取的文件集合中获取PictureData image pictures.get(row.getRowNum() _ entry.getKey());if (image null){val ;}else{byte[] data image.getData();val FileUtils.writeImportBytes(data);//图片文件写入到 application.yml文件 ruoyi.profile属性对应的文件目录下}}ReflectUtils.invokeSetter(entity, propertyName, val);}}list.add(entity);}}return list;} 最终工具类 ExcelUtil.java
package com.ruoyi.common.utils.poi;import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;
import java.util.stream.Collectors;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.RegExUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.ruoyi.common.annotation.Excel;
import com.ruoyi.common.annotation.Excel.ColumnType;
import com.ruoyi.common.annotation.Excel.Type;
import com.ruoyi.common.annotation.Excels;
import com.ruoyi.common.config.RuoYiConfig;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.core.text.Convert;
import com.ruoyi.common.exception.UtilException;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.DictUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.file.FileTypeUtils;
import com.ruoyi.common.utils.file.FileUtils;
import com.ruoyi.common.utils.file.ImageUtils;
import com.ruoyi.common.utils.reflect.ReflectUtils;/*** Excel相关处理* * author ruoyi*/
public class ExcelUtilT
{private static final Logger log LoggerFactory.getLogger(ExcelUtil.class);public static final String FORMULA_REGEX_STR |-|\\|;public static final String[] FORMULA_STR { , -, , };/*** 用于dictType属性数据存储避免重复查缓存*/public MapString, String sysDictMap new HashMapString, String();/*** Excel sheet最大行数默认65536*/public static final int sheetSize 65536;/*** 工作表名称*/private String sheetName;/*** 导出类型EXPORT:导出数据IMPORT导入模板*/private Type type;/*** 工作薄对象*/private Workbook wb;/*** 工作表对象*/private Sheet sheet;/*** 样式列表*/private MapString, CellStyle styles;/*** 导入导出数据列表*/private ListT list;/*** 注解列表*/private ListObject[] fields;/*** 当前行号*/private int rownum;/*** 标题*/private String title;/*** 最大高度*/private short maxHeight;/*** 合并后最后行数*/private int subMergedLastRowNum 0;/*** 合并后开始行数*/private int subMergedFirstRowNum 1;/*** 对象的子列表方法*/private Method subMethod;/*** 对象的子列表属性*/private ListField subFields;/*** 统计列表*/private MapInteger, Double statistics new HashMapInteger, Double();/*** 数字格式*/private static final DecimalFormat DOUBLE_FORMAT new DecimalFormat(######0.00);/*** 实体对象*/public ClassT clazz;/*** 需要排除列属性*/public String[] excludeFields;public ExcelUtil(ClassT clazz){this.clazz clazz;}/*** 隐藏Excel中列属性** param fields 列属性名 示例[单个name/多个id,name]* throws Exception*/public void hideColumn(String... fields){this.excludeFields fields;}public void init(ListT list, String sheetName, String title, Type type){if (list null){list new ArrayListT();}this.list list;this.sheetName sheetName;this.type type;this.title title;createExcelField();createWorkbook();createTitle();createSubHead();}/*** 创建excel第一行标题*/public void createTitle(){if (StringUtils.isNotEmpty(title)){subMergedFirstRowNum;subMergedLastRowNum;int titleLastCol this.fields.size() - 1;if (isSubList()){titleLastCol titleLastCol subFields.size() - 1;}Row titleRow sheet.createRow(rownum 0 ? rownum : 0);titleRow.setHeightInPoints(30);Cell titleCell titleRow.createCell(0);titleCell.setCellStyle(styles.get(title));titleCell.setCellValue(title);sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(), titleLastCol));}}/*** 创建对象的子列表名称*/public void createSubHead(){if (isSubList()){subMergedFirstRowNum;subMergedLastRowNum;Row subRow sheet.createRow(rownum);int excelNum 0;for (Object[] objects : fields){Excel attr (Excel) objects[1];Cell headCell1 subRow.createCell(excelNum);headCell1.setCellValue(attr.name());headCell1.setCellStyle(styles.get(StringUtils.format(header_{}_{}, attr.headerColor(), attr.headerBackgroundColor())));excelNum;}int headFirstRow excelNum - 1;int headLastRow headFirstRow subFields.size() - 1;if (headLastRow headFirstRow){sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, headFirstRow, headLastRow));}rownum;}}/*** 对excel表单默认第一个索引名转换成list* * param is 输入流* return 转换后集合*/public ListT importExcel(InputStream is){ListT list null;try{list importExcel(is, 0);}catch (Exception e){log.error(导入Excel异常{}, e.getMessage());throw new UtilException(e.getMessage());}finally{IOUtils.closeQuietly(is);}return list;}/*** 对excel表单默认第一个索引名转换成list* * param is 输入流* param titleNum 标题占用行数* return 转换后集合*/public ListT importExcel(InputStream is, int titleNum) throws Exception{return importExcel(StringUtils.EMPTY, is, titleNum);}/*** 对excel表单指定表格索引名转换成list* * param sheetName 表格索引名* param titleNum 标题占用行数* param is 输入流* return 转换后集合*/public ListT importExcel(String sheetName, InputStream is, int titleNum) throws Exception{this.type Type.IMPORT;this.wb WorkbookFactory.create(is);ListT list new ArrayListT();// 如果指定sheet名,则取指定sheet中的内容 否则默认指向第1个sheetSheet sheet StringUtils.isNotEmpty(sheetName) ? wb.getSheet(sheetName) : wb.getSheetAt(0);if (sheet null){throw new IOException(文件sheet不存在);}boolean isXSSFWorkbook !(wb instanceof HSSFWorkbook);//获取文档指定sheet中所有图片图片位置、文件流MapString, PictureData pictures;if (isXSSFWorkbook){pictures getSheetPictures07((XSSFSheet) sheet, (XSSFWorkbook) wb);}else{pictures getSheetPictures03((HSSFSheet) sheet, (HSSFWorkbook) wb);}// 获取最后一个非空行的行下标比如总行数为n则返回的为n-1int rows sheet.getLastRowNum();if (rows 0){// 定义一个map用于存放excel列的序号和field.MapString, Integer cellMap new HashMapString, Integer();// 获取表头Row heard sheet.getRow(titleNum);for (int i 0; i heard.getPhysicalNumberOfCells(); i){Cell cell heard.getCell(i);if (StringUtils.isNotNull(cell)){String value this.getCellValue(heard, i).toString();cellMap.put(value, i);}else{cellMap.put(null, i);}}// 有数据时才处理 得到类的所有field.ListObject[] fields this.getFields();MapInteger, Object[] fieldsMap new HashMapInteger, Object[]();for (Object[] objects : fields){Excel attr (Excel) objects[1];Integer column cellMap.get(attr.name());if (column ! null){fieldsMap.put(column, objects);}}for (int i titleNum 1; i rows; i){// 从第2行开始取数据,默认第一行是表头.Row row sheet.getRow(i);// 判断当前行是否是空行if (isRowEmpty(row)){continue;}T entity null;//遍历所有列 逐列字段值获取并赋值给entity对象的各属性for (Map.EntryInteger, Object[] entry : fieldsMap.entrySet()){Object val this.getCellValue(row, entry.getKey());//获取当前列 默认值// 新增 合并行相关代码块 // 判断是否合并行 isMergedRow(sheet, rowNum, column)是合并行则获取合并行数据 重新赋值给当前列if (isMergedRow(sheet, i, entry.getKey())) val getMergedRegionValue(sheet, i, entry.getKey());// 新增 合并行相关代码块 // 如果不存在实例则新建.entity (entity null ? clazz.newInstance() : entity);// 从map中得到对应列的field.Field field (Field) entry.getValue()[0];Excel attr (Excel) entry.getValue()[1];// 取得类型,并根据对象类型设置值.Class? fieldType field.getType();if (String.class fieldType){String s Convert.toStr(val);if (StringUtils.endsWith(s, .0)){val StringUtils.substringBefore(s, .0);}else{String dateFormat field.getAnnotation(Excel.class).dateFormat();if (StringUtils.isNotEmpty(dateFormat)){val parseDateToStr(dateFormat, val);}else{val Convert.toStr(val);}}}else if ((Integer.TYPE fieldType || Integer.class fieldType) StringUtils.isNumeric(Convert.toStr(val))){val Convert.toInt(val);}else if ((Long.TYPE fieldType || Long.class fieldType) StringUtils.isNumeric(Convert.toStr(val))){val Convert.toLong(val);}else if (Double.TYPE fieldType || Double.class fieldType){val Convert.toDouble(val);}else if (Float.TYPE fieldType || Float.class fieldType){val Convert.toFloat(val);}else if (BigDecimal.class fieldType){val Convert.toBigDecimal(val);}else if (Date.class fieldType){if (val instanceof String){val DateUtils.parseDate(val);}else if (val instanceof Double){val DateUtil.getJavaDate((Double) val);}}else if (Boolean.TYPE fieldType || Boolean.class fieldType){val Convert.toBool(val, false);}if (StringUtils.isNotNull(fieldType)){String propertyName field.getName();if (StringUtils.isNotEmpty(attr.targetAttr())){propertyName field.getName() . attr.targetAttr();}if (StringUtils.isNotEmpty(attr.readConverterExp())){val reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());}else if (StringUtils.isNotEmpty(attr.dictType())){val reverseDictByExp(Convert.toStr(val), attr.dictType(), attr.separator());}else if (!attr.handler().equals(ExcelHandlerAdapter.class)){val dataFormatHandlerAdapter(val, attr, null);}else if (ColumnType.IMAGE attr.cellType() StringUtils.isNotEmpty(pictures)){PictureData image pictures.get(row.getRowNum() _ entry.getKey());if (image null){val ;}else{byte[] data image.getData();val FileUtils.writeImportBytes(data);//图片文件写入到 application.yml文件 ruoyi.profile属性对应的文件目录下}}ReflectUtils.invokeSetter(entity, propertyName, val);}}list.add(entity);}}return list;}/*** 新增 合并行相关代码判断是否是合并行** param sheet* param row* param column* return*/private boolean isMergedRow(Sheet sheet, int row, int column) {int sheetMergeCount sheet.getNumMergedRegions();for (int i 0; i sheetMergeCount; i) {CellRangeAddress cellAddresses sheet.getMergedRegion(i);int firstColumn cellAddresses.getFirstColumn();int lastColumn cellAddresses.getLastColumn();int firstRow cellAddresses.getFirstRow();int lastRow cellAddresses.getLastRow();if (row firstRow row lastRow) {if (column firstColumn column lastColumn) {return true;}}}return false;}/*** 新增 合并行相关代码获取合并行的数据** param sheet* param row* param column* return*/private String getMergedRegionValue(Sheet sheet, int row, int column) {// 获得该sheet所有合并单元格数量int sheetMergeCount sheet.getNumMergedRegions();for (int i 0; i sheetMergeCount; i) {// 获得合并区域CellRangeAddress cellAddresses sheet.getMergedRegion(i);int firstColumn cellAddresses.getFirstColumn();int lastColumn cellAddresses.getLastColumn();int firstRow cellAddresses.getFirstRow();int lastRow cellAddresses.getLastRow();/*判断传入的单元格的行号列号是否在合并单元格的范围内如果在合并单元格的范围内择返回合并区域的首单元格格值*/if (row firstRow row lastRow) {if (column firstColumn column lastColumn) {Row firRow sheet.getRow(firstRow);/* 合并行使用的获取单元格内容方法Cell firCell firRow.getCell(firstColumn);return getCellValue(firCell);*/return getCellValue(firRow, firstColumn).toString();//改为使用 若依自带获取单元格内容方法}}}// 如果该单元格行号列号不在任何一个合并区域则返回nullreturn null;}/*** 对list数据源将其里面的数据导入到excel表单* * param list 导出数据集合* param sheetName 工作表的名称* return 结果*/public AjaxResult exportExcel(ListT list, String sheetName){return exportExcel(list, sheetName, StringUtils.EMPTY);}/*** 对list数据源将其里面的数据导入到excel表单* * param list 导出数据集合* param sheetName 工作表的名称* param title 标题* return 结果*/public AjaxResult exportExcel(ListT list, String sheetName, String title){this.init(list, sheetName, title, Type.EXPORT);return exportExcel();}/*** 对list数据源将其里面的数据导入到excel表单* * param response 返回数据* param list 导出数据集合* param sheetName 工作表的名称* return 结果*/public void exportExcel(HttpServletResponse response, ListT list, String sheetName){exportExcel(response, list, sheetName, StringUtils.EMPTY);}/*** 对list数据源将其里面的数据导入到excel表单* * param response 返回数据* param list 导出数据集合* param sheetName 工作表的名称* param title 标题* return 结果*/public void exportExcel(HttpServletResponse response, ListT list, String sheetName, String title){response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);response.setCharacterEncoding(utf-8);this.init(list, sheetName, title, Type.EXPORT);exportExcel(response);}/*** 对list数据源将其里面的数据导入到excel表单* * param sheetName 工作表的名称* return 结果*/public AjaxResult importTemplateExcel(String sheetName){return importTemplateExcel(sheetName, StringUtils.EMPTY);}/*** 对list数据源将其里面的数据导入到excel表单* * param sheetName 工作表的名称* param title 标题* return 结果*/public AjaxResult importTemplateExcel(String sheetName, String title){this.init(null, sheetName, title, Type.IMPORT);return exportExcel();}/*** 对list数据源将其里面的数据导入到excel表单* * param sheetName 工作表的名称* return 结果*/public void importTemplateExcel(HttpServletResponse response, String sheetName){importTemplateExcel(response, sheetName, StringUtils.EMPTY);}/*** 对list数据源将其里面的数据导入到excel表单* * param sheetName 工作表的名称* param title 标题* return 结果*/public void importTemplateExcel(HttpServletResponse response, String sheetName, String title){response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);response.setCharacterEncoding(utf-8);this.init(null, sheetName, title, Type.IMPORT);exportExcel(response);}/*** 对list数据源将其里面的数据导入到excel表单* * return 结果*/public void exportExcel(HttpServletResponse response){try{writeSheet();wb.write(response.getOutputStream());}catch (Exception e){log.error(导出Excel异常{}, e.getMessage());}finally{IOUtils.closeQuietly(wb);}}/*** 对list数据源将其里面的数据导入到excel表单* * return 结果*/public AjaxResult exportExcel(){OutputStream out null;try{writeSheet();String filename encodingFilename(sheetName);out new FileOutputStream(getAbsoluteFile(filename));wb.write(out);return AjaxResult.success(filename);}catch (Exception e){log.error(导出Excel异常{}, e.getMessage());throw new UtilException(导出Excel失败请联系网站管理员);}finally{IOUtils.closeQuietly(wb);IOUtils.closeQuietly(out);}}/*** 创建写入数据到Sheet*/public void writeSheet(){// 取出一共有多少个sheet.int sheetNo Math.max(1, (int) Math.ceil(list.size() * 1.0 / sheetSize));for (int index 0; index sheetNo; index){createSheet(sheetNo, index);// 产生一行Row row sheet.createRow(rownum);int column 0;// 写入各个字段的列头名称for (Object[] os : fields){Field field (Field) os[0];Excel excel (Excel) os[1];if (Collection.class.isAssignableFrom(field.getType())){for (Field subField : subFields){Excel subExcel subField.getAnnotation(Excel.class);this.createHeadCell(subExcel, row, column);}}else{this.createHeadCell(excel, row, column);}}if (Type.EXPORT.equals(type)){fillExcelData(index, row);addStatisticsRow();}}}/*** 填充excel数据* * param index 序号* param row 单元格行*/SuppressWarnings(unchecked)public void fillExcelData(int index, Row row){int startNo index * sheetSize;int endNo Math.min(startNo sheetSize, list.size());int rowNo (1 rownum) - startNo;for (int i startNo; i endNo; i){rowNo isSubList() ? (i 1 ? rowNo 1 : rowNo i) : i 1 rownum - startNo;row sheet.createRow(rowNo);// 得到导出对象.T vo (T) list.get(i);Collection? subList null;if (isSubList()){if (isSubListValue(vo)){subList getListCellValue(vo);subMergedLastRowNum subMergedLastRowNum subList.size();}else{subMergedFirstRowNum;subMergedLastRowNum;}}int column 0;for (Object[] os : fields){Field field (Field) os[0];Excel excel (Excel) os[1];if (Collection.class.isAssignableFrom(field.getType()) StringUtils.isNotNull(subList)){boolean subFirst false;for (Object obj : subList){if (subFirst){rowNo;row sheet.createRow(rowNo);}ListField subFields FieldUtils.getFieldsListWithAnnotation(obj.getClass(), Excel.class);int subIndex 0;for (Field subField : subFields){if (subField.isAnnotationPresent(Excel.class)){subField.setAccessible(true);Excel attr subField.getAnnotation(Excel.class);this.addCell(attr, row, (T) obj, subField, column subIndex);}subIndex;}subFirst true;}this.subMergedFirstRowNum this.subMergedFirstRowNum subList.size();}else{this.addCell(excel, row, vo, field, column);}}}}/*** 创建表格样式* * param wb 工作薄对象* return 样式列表*/private MapString, CellStyle createStyles(Workbook wb){// 写入各条记录,每条记录对应excel表中的一行MapString, CellStyle styles new HashMapString, CellStyle();CellStyle style wb.createCellStyle();style.setAlignment(HorizontalAlignment.CENTER);style.setVerticalAlignment(VerticalAlignment.CENTER);Font titleFont wb.createFont();titleFont.setFontName(Arial);titleFont.setFontHeightInPoints((short) 16);titleFont.setBold(true);style.setFont(titleFont);styles.put(title, style);style wb.createCellStyle();style.setAlignment(HorizontalAlignment.CENTER);style.setVerticalAlignment(VerticalAlignment.CENTER);style.setBorderRight(BorderStyle.THIN);style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());style.setBorderLeft(BorderStyle.THIN);style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());style.setBorderTop(BorderStyle.THIN);style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());style.setBorderBottom(BorderStyle.THIN);style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());Font dataFont wb.createFont();dataFont.setFontName(Arial);dataFont.setFontHeightInPoints((short) 10);style.setFont(dataFont);styles.put(data, style);style wb.createCellStyle();style.setAlignment(HorizontalAlignment.CENTER);style.setVerticalAlignment(VerticalAlignment.CENTER);Font totalFont wb.createFont();totalFont.setFontName(Arial);totalFont.setFontHeightInPoints((short) 10);style.setFont(totalFont);styles.put(total, style);styles.putAll(annotationHeaderStyles(wb, styles));styles.putAll(annotationDataStyles(wb));return styles;}/*** 根据Excel注解创建表格头样式* * param wb 工作薄对象* return 自定义样式列表*/private MapString, CellStyle annotationHeaderStyles(Workbook wb, MapString, CellStyle styles){MapString, CellStyle headerStyles new HashMapString, CellStyle();for (Object[] os : fields){Excel excel (Excel) os[1];String key StringUtils.format(header_{}_{}, excel.headerColor(), excel.headerBackgroundColor());if (!headerStyles.containsKey(key)){CellStyle style wb.createCellStyle();style.cloneStyleFrom(styles.get(data));style.setAlignment(HorizontalAlignment.CENTER);style.setVerticalAlignment(VerticalAlignment.CENTER);style.setFillForegroundColor(excel.headerBackgroundColor().index);style.setFillPattern(FillPatternType.SOLID_FOREGROUND);Font headerFont wb.createFont();headerFont.setFontName(Arial);headerFont.setFontHeightInPoints((short) 10);headerFont.setBold(true);headerFont.setColor(excel.headerColor().index);style.setFont(headerFont);headerStyles.put(key, style);}}return headerStyles;}/*** 根据Excel注解创建表格列样式* * param wb 工作薄对象* return 自定义样式列表*/private MapString, CellStyle annotationDataStyles(Workbook wb){MapString, CellStyle styles new HashMapString, CellStyle();for (Object[] os : fields){Excel excel (Excel) os[1];String key StringUtils.format(data_{}_{}_{}, excel.align(), excel.color(), excel.backgroundColor());if (!styles.containsKey(key)){CellStyle style wb.createCellStyle();style.setAlignment(excel.align());style.setVerticalAlignment(VerticalAlignment.CENTER);style.setBorderRight(BorderStyle.THIN);style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());style.setBorderLeft(BorderStyle.THIN);style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());style.setBorderTop(BorderStyle.THIN);style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());style.setBorderBottom(BorderStyle.THIN);style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());style.setFillPattern(FillPatternType.SOLID_FOREGROUND);style.setFillForegroundColor(excel.backgroundColor().getIndex());Font dataFont wb.createFont();dataFont.setFontName(Arial);dataFont.setFontHeightInPoints((short) 10);dataFont.setColor(excel.color().index);style.setFont(dataFont);styles.put(key, style);}}return styles;}/*** 创建单元格*/public Cell createHeadCell(Excel attr, Row row, int column){// 创建列Cell cell row.createCell(column);// 写入列信息cell.setCellValue(attr.name());setDataValidation(attr, row, column);cell.setCellStyle(styles.get(StringUtils.format(header_{}_{}, attr.headerColor(), attr.headerBackgroundColor())));if (isSubList()){// 填充默认样式防止合并单元格样式失效sheet.setDefaultColumnStyle(column, styles.get(StringUtils.format(data_{}_{}_{}, attr.align(), attr.color(), attr.backgroundColor())));if (attr.needMerge()){sheet.addMergedRegion(new CellRangeAddress(rownum - 1, rownum, column, column));}}return cell;}/*** 设置单元格信息* * param value 单元格值* param attr 注解相关* param cell 单元格信息*/public void setCellVo(Object value, Excel attr, Cell cell){if (ColumnType.STRING attr.cellType()){String cellValue Convert.toStr(value);// 对于任何以表达式触发字符 -开头的单元格直接使用tab字符作为前缀防止CSV注入。if (StringUtils.startsWithAny(cellValue, FORMULA_STR)){cellValue RegExUtils.replaceFirst(cellValue, FORMULA_REGEX_STR, \t$0);}if (value instanceof Collection StringUtils.equals([], cellValue)){cellValue StringUtils.EMPTY;}cell.setCellValue(StringUtils.isNull(cellValue) ? attr.defaultValue() : cellValue attr.suffix());}else if (ColumnType.NUMERIC attr.cellType()){if (StringUtils.isNotNull(value)){cell.setCellValue(StringUtils.contains(Convert.toStr(value), .) ? Convert.toDouble(value) : Convert.toInt(value));}}else if (ColumnType.IMAGE attr.cellType()){ClientAnchor anchor new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() 1), cell.getRow().getRowNum() 1);String imagePath Convert.toStr(value);if (StringUtils.isNotEmpty(imagePath)){byte[] data ImageUtils.getImage(imagePath);getDrawingPatriarch(cell.getSheet()).createPicture(anchor,cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));}}}/*** 获取画布*/public static Drawing? getDrawingPatriarch(Sheet sheet){if (sheet.getDrawingPatriarch() null){sheet.createDrawingPatriarch();}return sheet.getDrawingPatriarch();}/*** 获取图片类型,设置图片插入类型*/public int getImageType(byte[] value){String type FileTypeUtils.getFileExtendName(value);if (JPG.equalsIgnoreCase(type)){return Workbook.PICTURE_TYPE_JPEG;}else if (PNG.equalsIgnoreCase(type)){return Workbook.PICTURE_TYPE_PNG;}return Workbook.PICTURE_TYPE_JPEG;}/*** 创建表格样式*/public void setDataValidation(Excel attr, Row row, int column){if (attr.name().indexOf(注) 0){sheet.setColumnWidth(column, 6000);}else{// 设置列宽sheet.setColumnWidth(column, (int) ((attr.width() 0.72) * 256));}if (StringUtils.isNotEmpty(attr.prompt()) || attr.combo().length 0){if (attr.combo().length 15 || StringUtils.join(attr.combo()).length() 255){// 如果下拉数大于15或字符串长度大于255则使用一个新sheet存储避免生成的模板下拉值获取不到setXSSFValidationWithHidden(sheet, attr.combo(), attr.prompt(), 1, 100, column, column);}else{// 提示信息或只能选择不能输入的列内容.setPromptOrValidation(sheet, attr.combo(), attr.prompt(), 1, 100, column, column);}}}/*** 添加单元格*/public Cell addCell(Excel attr, Row row, T vo, Field field, int column){Cell cell null;try{// 设置行高row.setHeight(maxHeight);// 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.if (attr.isExport()){// 创建cellcell row.createCell(column);if (isSubListValue(vo) getListCellValue(vo).size() 1 attr.needMerge()){CellRangeAddress cellAddress new CellRangeAddress(subMergedFirstRowNum, subMergedLastRowNum, column, column);sheet.addMergedRegion(cellAddress);}cell.setCellStyle(styles.get(StringUtils.format(data_{}_{}_{}, attr.align(), attr.color(), attr.backgroundColor())));// 用于读取对象中的属性Object value getTargetValue(vo, field, attr);String dateFormat attr.dateFormat();String readConverterExp attr.readConverterExp();String separator attr.separator();String dictType attr.dictType();if (StringUtils.isNotEmpty(dateFormat) StringUtils.isNotNull(value)){cell.setCellValue(parseDateToStr(dateFormat, value));}else if (StringUtils.isNotEmpty(readConverterExp) StringUtils.isNotNull(value)){cell.setCellValue(convertByExp(Convert.toStr(value), readConverterExp, separator));}else if (StringUtils.isNotEmpty(dictType) StringUtils.isNotNull(value)){if (!sysDictMap.containsKey(dictType value)){String lable convertDictByExp(Convert.toStr(value), dictType, separator);sysDictMap.put(dictType value, lable);}cell.setCellValue(sysDictMap.get(dictType value));}else if (value instanceof BigDecimal -1 ! attr.scale()){cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).doubleValue());}else if (!attr.handler().equals(ExcelHandlerAdapter.class)){cell.setCellValue(dataFormatHandlerAdapter(value, attr, cell));}else{// 设置列类型setCellVo(value, attr, cell);}addStatisticsData(column, Convert.toStr(value), attr);}}catch (Exception e){log.error(导出Excel失败{}, e);}return cell;}/*** 设置 POI XSSFSheet 单元格提示或选择框* * param sheet 表单* param textlist 下拉框显示的内容* param promptContent 提示内容* param firstRow 开始行* param endRow 结束行* param firstCol 开始列* param endCol 结束列*/public void setPromptOrValidation(Sheet sheet, String[] textlist, String promptContent, int firstRow, int endRow,int firstCol, int endCol){DataValidationHelper helper sheet.getDataValidationHelper();DataValidationConstraint constraint textlist.length 0 ? helper.createExplicitListConstraint(textlist) : helper.createCustomConstraint(DD1);CellRangeAddressList regions new CellRangeAddressList(firstRow, endRow, firstCol, endCol);DataValidation dataValidation helper.createValidation(constraint, regions);if (StringUtils.isNotEmpty(promptContent)){// 如果设置了提示信息则鼠标放上去提示dataValidation.createPromptBox(, promptContent);dataValidation.setShowPromptBox(true);}// 处理Excel兼容性问题if (dataValidation instanceof XSSFDataValidation){dataValidation.setSuppressDropDownArrow(true);dataValidation.setShowErrorBox(true);}else{dataValidation.setSuppressDropDownArrow(false);}sheet.addValidationData(dataValidation);}/*** 设置某些列的值只能输入预制的数据,显示下拉框兼容超出一定数量的下拉框.* * param sheet 要设置的sheet.* param textlist 下拉框显示的内容* param promptContent 提示内容* param firstRow 开始行* param endRow 结束行* param firstCol 开始列* param endCol 结束列*/public void setXSSFValidationWithHidden(Sheet sheet, String[] textlist, String promptContent, int firstRow, int endRow, int firstCol, int endCol){String hideSheetName combo_ firstCol _ endCol;Sheet hideSheet wb.createSheet(hideSheetName); // 用于存储 下拉菜单数据for (int i 0; i textlist.length; i){hideSheet.createRow(i).createCell(0).setCellValue(textlist[i]);}// 创建名称可被其他单元格引用Name name wb.createName();name.setNameName(hideSheetName _data);name.setRefersToFormula(hideSheetName !$A$1:$A$ textlist.length);DataValidationHelper helper sheet.getDataValidationHelper();// 加载下拉列表内容DataValidationConstraint constraint helper.createFormulaListConstraint(hideSheetName _data);// 设置数据有效性加载在哪个单元格上,四个参数分别是起始行、终止行、起始列、终止列CellRangeAddressList regions new CellRangeAddressList(firstRow, endRow, firstCol, endCol);// 数据有效性对象DataValidation dataValidation helper.createValidation(constraint, regions);if (StringUtils.isNotEmpty(promptContent)){// 如果设置了提示信息则鼠标放上去提示dataValidation.createPromptBox(, promptContent);dataValidation.setShowPromptBox(true);}// 处理Excel兼容性问题if (dataValidation instanceof XSSFDataValidation){dataValidation.setSuppressDropDownArrow(true);dataValidation.setShowErrorBox(true);}else{dataValidation.setSuppressDropDownArrow(false);}sheet.addValidationData(dataValidation);// 设置hiddenSheet隐藏wb.setSheetHidden(wb.getSheetIndex(hideSheet), true);}/*** 解析导出值 0男,1女,2未知* * param propertyValue 参数值* param converterExp 翻译注解* param separator 分隔符* return 解析后值*/public static String convertByExp(String propertyValue, String converterExp, String separator){StringBuilder propertyString new StringBuilder();String[] convertSource converterExp.split(,);for (String item : convertSource){String[] itemArray item.split();if (StringUtils.containsAny(propertyValue, separator)){for (String value : propertyValue.split(separator)){if (itemArray[0].equals(value)){propertyString.append(itemArray[1] separator);break;}}}else{if (itemArray[0].equals(propertyValue)){return itemArray[1];}}}return StringUtils.stripEnd(propertyString.toString(), separator);}/*** 反向解析值 男0,女1,未知2* * param propertyValue 参数值* param converterExp 翻译注解* param separator 分隔符* return 解析后值*/public static String reverseByExp(String propertyValue, String converterExp, String separator){StringBuilder propertyString new StringBuilder();String[] convertSource converterExp.split(,);for (String item : convertSource){String[] itemArray item.split();if (StringUtils.containsAny(propertyValue, separator)){for (String value : propertyValue.split(separator)){if (itemArray[1].equals(value)){propertyString.append(itemArray[0] separator);break;}}}else{if (itemArray[1].equals(propertyValue)){return itemArray[0];}}}return StringUtils.stripEnd(propertyString.toString(), separator);}/*** 解析字典值* * param dictValue 字典值* param dictType 字典类型* param separator 分隔符* return 字典标签*/public static String convertDictByExp(String dictValue, String dictType, String separator){return DictUtils.getDictLabel(dictType, dictValue, separator);}/*** 反向解析值字典值* * param dictLabel 字典标签* param dictType 字典类型* param separator 分隔符* return 字典值*/public static String reverseDictByExp(String dictLabel, String dictType, String separator){return DictUtils.getDictValue(dictType, dictLabel, separator);}/*** 数据处理器* * param value 数据值* param excel 数据注解* return*/public String dataFormatHandlerAdapter(Object value, Excel excel, Cell cell){try{Object instance excel.handler().newInstance();Method formatMethod excel.handler().getMethod(format, new Class[] { Object.class, String[].class, Cell.class, Workbook.class });value formatMethod.invoke(instance, value, excel.args(), cell, this.wb);}catch (Exception e){log.error(不能格式化数据 excel.handler(), e.getMessage());}return Convert.toStr(value);}/*** 合计统计信息*/private void addStatisticsData(Integer index, String text, Excel entity){if (entity ! null entity.isStatistics()){Double temp 0D;if (!statistics.containsKey(index)){statistics.put(index, temp);}try{temp Double.valueOf(text);}catch (NumberFormatException e){}statistics.put(index, statistics.get(index) temp);}}/*** 创建统计行*/public void addStatisticsRow(){if (statistics.size() 0){Row row sheet.createRow(sheet.getLastRowNum() 1);SetInteger keys statistics.keySet();Cell cell row.createCell(0);cell.setCellStyle(styles.get(total));cell.setCellValue(合计);for (Integer key : keys){cell row.createCell(key);cell.setCellStyle(styles.get(total));cell.setCellValue(DOUBLE_FORMAT.format(statistics.get(key)));}statistics.clear();}}/*** 编码文件名*/public String encodingFilename(String filename){filename UUID.randomUUID() _ filename .xlsx;return filename;}/*** 获取下载路径* * param filename 文件名称*/public String getAbsoluteFile(String filename){String downloadPath RuoYiConfig.getDownloadPath() filename;File desc new File(downloadPath);if (!desc.getParentFile().exists()){desc.getParentFile().mkdirs();}return downloadPath;}/*** 获取bean中的属性值* * param vo 实体对象* param field 字段* param excel 注解* return 最终的属性值* throws Exception*/private Object getTargetValue(T vo, Field field, Excel excel) throws Exception{Object o field.get(vo);if (StringUtils.isNotEmpty(excel.targetAttr())){String target excel.targetAttr();if (target.contains(.)){String[] targets target.split([.]);for (String name : targets){o getValue(o, name);}}else{o getValue(o, target);}}return o;}/*** 以类的属性的get方法方法形式获取值* * param o* param name* return value* throws Exception*/private Object getValue(Object o, String name) throws Exception{if (StringUtils.isNotNull(o) StringUtils.isNotEmpty(name)){Class? clazz o.getClass();Field field clazz.getDeclaredField(name);field.setAccessible(true);o field.get(o);}return o;}/*** 得到所有定义字段*/private void createExcelField(){this.fields getFields();this.fields this.fields.stream().sorted(Comparator.comparing(objects - ((Excel) objects[1]).sort())).collect(Collectors.toList());this.maxHeight getRowHeight();}/*** 获取字段注解信息*/public ListObject[] getFields(){ListObject[] fields new ArrayListObject[]();ListField tempFields new ArrayList();tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));for (Field field : tempFields){if (!ArrayUtils.contains(this.excludeFields, field.getName())){// 单注解if (field.isAnnotationPresent(Excel.class)){Excel attr field.getAnnotation(Excel.class);if (attr ! null (attr.type() Type.ALL || attr.type() type)){field.setAccessible(true);fields.add(new Object[] { field, attr });}if (Collection.class.isAssignableFrom(field.getType())){subMethod getSubMethod(field.getName(), clazz);ParameterizedType pt (ParameterizedType) field.getGenericType();Class? subClass (Class?) pt.getActualTypeArguments()[0];this.subFields FieldUtils.getFieldsListWithAnnotation(subClass, Excel.class);}}// 多注解if (field.isAnnotationPresent(Excels.class)){Excels attrs field.getAnnotation(Excels.class);Excel[] excels attrs.value();for (Excel attr : excels){if (!ArrayUtils.contains(this.excludeFields, field.getName() . attr.targetAttr()) (attr ! null (attr.type() Type.ALL || attr.type() type))){field.setAccessible(true);fields.add(new Object[] { field, attr });}}}}}return fields;}/*** 根据注解获取最大行高*/public short getRowHeight(){double maxHeight 0;for (Object[] os : this.fields){Excel excel (Excel) os[1];maxHeight Math.max(maxHeight, excel.height());}return (short) (maxHeight * 20);}/*** 创建一个工作簿*/public void createWorkbook(){this.wb new SXSSFWorkbook(500);this.sheet wb.createSheet();wb.setSheetName(0, sheetName);this.styles createStyles(wb);}/*** 创建工作表* * param sheetNo sheet数量* param index 序号*/public void createSheet(int sheetNo, int index){// 设置工作表的名称.if (sheetNo 1 index 0){this.sheet wb.createSheet();this.createTitle();wb.setSheetName(index, sheetName index);}}/*** 获取单元格值* * param row 获取的行* param column 获取单元格列号* return 单元格值*/public Object getCellValue(Row row, int column){if (row null){return row;}Object val ;try{Cell cell row.getCell(column);if (StringUtils.isNotNull(cell)){if (cell.getCellType() CellType.NUMERIC || cell.getCellType() CellType.FORMULA){val cell.getNumericCellValue();if (DateUtil.isCellDateFormatted(cell)){val DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换}else{if ((Double) val % 1 ! 0){val new BigDecimal(val.toString());}else{val new DecimalFormat(0).format(val);}}}else if (cell.getCellType() CellType.STRING){val cell.getStringCellValue();}else if (cell.getCellType() CellType.BOOLEAN){val cell.getBooleanCellValue();}else if (cell.getCellType() CellType.ERROR){val cell.getErrorCellValue();}}}catch (Exception e){return val;}return val;}/*** 判断是否是空行* * param row 判断的行* return*/private boolean isRowEmpty(Row row){if (row null){return true;}for (int i row.getFirstCellNum(); i row.getLastCellNum(); i){Cell cell row.getCell(i);if (cell ! null cell.getCellType() ! CellType.BLANK){return false;}}return true;}/*** 获取Excel2003图片** param sheet 当前sheet对象* param workbook 工作簿对象* return Map key:图片单元格索引1_1Stringvalue:图片流PictureData*/public static MapString, PictureData getSheetPictures03(HSSFSheet sheet, HSSFWorkbook workbook){MapString, PictureData sheetIndexPicMap new HashMapString, PictureData();ListHSSFPictureData pictures workbook.getAllPictures();if (!pictures.isEmpty()){for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()){HSSFClientAnchor anchor (HSSFClientAnchor) shape.getAnchor();if (shape instanceof HSSFPicture){HSSFPicture pic (HSSFPicture) shape;int pictureIndex pic.getPictureIndex() - 1;HSSFPictureData picData pictures.get(pictureIndex);String picIndex anchor.getRow1() _ anchor.getCol1();sheetIndexPicMap.put(picIndex, picData);}}return sheetIndexPicMap;}else{return sheetIndexPicMap;}}/*** 获取Excel2007图片** param sheet 当前sheet对象* param workbook 工作簿对象* return Map key:图片单元格索引1_1Stringvalue:图片流PictureData*/public static MapString, PictureData getSheetPictures07(XSSFSheet sheet, XSSFWorkbook workbook){MapString, PictureData sheetIndexPicMap new HashMapString, PictureData();for (POIXMLDocumentPart dr : sheet.getRelations()){if (dr instanceof XSSFDrawing){XSSFDrawing drawing (XSSFDrawing) dr;ListXSSFShape shapes drawing.getShapes();for (XSSFShape shape : shapes){if (shape instanceof XSSFPicture){XSSFPicture pic (XSSFPicture) shape;XSSFClientAnchor anchor pic.getPreferredSize();CTMarker ctMarker anchor.getFrom();String picIndex ctMarker.getRow() _ ctMarker.getCol();sheetIndexPicMap.put(picIndex, pic.getPictureData());}}}}return sheetIndexPicMap;}/*** 格式化不同类型的日期对象* * param dateFormat 日期格式* param val 被格式化的日期对象* return 格式化后的日期字符*/public String parseDateToStr(String dateFormat, Object val){if (val null){return ;}String str;if (val instanceof Date){str DateUtils.parseDateToStr(dateFormat, (Date) val);}else if (val instanceof LocalDateTime){str DateUtils.parseDateToStr(dateFormat, DateUtils.toDate((LocalDateTime) val));}else if (val instanceof LocalDate){str DateUtils.parseDateToStr(dateFormat, DateUtils.toDate((LocalDate) val));}else{str val.toString();}return str;}/*** 是否有对象的子列表*/public boolean isSubList(){return StringUtils.isNotNull(subFields) subFields.size() 0;}/*** 是否有对象的子列表集合不为空*/public boolean isSubListValue(T vo){return StringUtils.isNotNull(subFields) subFields.size() 0 StringUtils.isNotNull(getListCellValue(vo)) getListCellValue(vo).size() 0;}/*** 获取集合的值*/public Collection? getListCellValue(Object obj){Object value;try{value subMethod.invoke(obj, new Object[] {});}catch (Exception e){return new ArrayListObject();}return (Collection?) value;}/*** 获取对象的子列表方法* * param name 名称* param pojoClass 类对象* return 子列表方法*/public Method getSubMethod(String name, Class? pojoClass){StringBuffer getMethodName new StringBuffer(get);getMethodName.append(name.substring(0, 1).toUpperCase());getMethodName.append(name.substring(1));Method method null;try{method pojoClass.getMethod(getMethodName.toString(), new Class[] {});}catch (Exception e){log.error(获取对象异常{}, e.getMessage());}return method;}
}参考自
https://www.iteye.com/blog/357029540-2438298
下面工具类为该文下的POI处理表格数据的源码
package com.chinamobile.util;import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;/*** author liaoyubo* version 1.0* date 2019/2/27* description*/
Slf4j
public class POIUtils {private final static String XLS xls;private final static String XLSX xlsx;private static SimpleDateFormat sdf;/*** 读入excel文件解析后返回** param file* throws IOException*/public static ListString[] readExcel(MultipartFile file) throws IOException {//检查文件checkFile(file);//获得Workbook工作薄对象Workbook workbook getWorkBook(file);//创建返回对象把每行中的值作为一个数组所有行作为一个集合返回ListString[] list new ArrayList();if (workbook ! null) {for (int sheetNum 0; sheetNum workbook.getNumberOfSheets(); sheetNum) {//获得当前sheet工作表Sheet sheet workbook.getSheetAt(sheetNum);if (sheet null) {continue;}//获得当前sheet的开始行int firstRowNum sheet.getFirstRowNum();//获得当前sheet的结束行int lastRowNum sheet.getLastRowNum();//循环除了第一行的所有行list rowList(sheet,firstRowNum 1,lastRowNum);}workbook.close();}return list;}public static ListString[] readExcel(MultipartFile file,int sheetNum,int startRow) throws IOException {//检查文件checkFile(file);//获得Workbook工作薄对象Workbook workbook getWorkBook(file);//创建返回对象把每行中的值作为一个数组所有行作为一个集合返回ListString[] list new ArrayList();if (workbook ! null) {//获得当前sheet工作表Sheet sheet workbook.getSheetAt(sheetNum);if (!Optional.ofNullable(sheet).isPresent()) {return null;}//获得当前sheet的结束行int lastRowNum sheet.getLastRowNum();//循环指定的所有行list rowList(sheet,startRow,lastRowNum);workbook.close();}return list;}/*** 检查文件是否正确** param file* throws IOException*/private static void checkFile(MultipartFile file) throws IOException {//判断文件是否存在if (null file) {log.error(文件不存在);throw new FileNotFoundException(文件不存在);}//获得文件名String fileName file.getOriginalFilename();//判断文件是否是excel文件if (!fileName.endsWith(XLS) !fileName.endsWith(XLSX)) {log.error(fileName 不是excel文件);throw new IOException(fileName 不是excel文件);}}/*** 对文件解析** param file* return*/private static Workbook getWorkBook(MultipartFile file) {//获得文件名String fileName file.getOriginalFilename();//创建Workbook工作薄对象表示整个excelWorkbook workbook null;try {//获取excel文件的io流InputStream is file.getInputStream();//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象if (fileName.endsWith(XLS)) {//2003workbook new HSSFWorkbook(is);} else if (fileName.endsWith(XLSX)) {//2007workbook new XSSFWorkbook(is);}} catch (IOException e) {log.info(e.getMessage());}return workbook;}/*** 判断是否是合并行** param sheet* param row* param column* return*/private static boolean isMergedRow(Sheet sheet, int row, int column) {int sheetMergeCount sheet.getNumMergedRegions();for (int i 0; i sheetMergeCount; i) {CellRangeAddress cellAddresses sheet.getMergedRegion(i);int firstColumn cellAddresses.getFirstColumn();int lastColumn cellAddresses.getLastColumn();int firstRow cellAddresses.getFirstRow();int lastRow cellAddresses.getLastRow();if (row firstRow row lastRow) {if (column firstColumn column lastColumn) {return true;}}}return false;}/*** 获取合并行的数据** param sheet* param row* param column* return*/private static String getMergedRegionValue(Sheet sheet, int row, int column) {// 获得该sheet所有合并单元格数量int sheetMergeCount sheet.getNumMergedRegions();for (int i 0; i sheetMergeCount; i) {// 获得合并区域CellRangeAddress cellAddresses sheet.getMergedRegion(i);int firstColumn cellAddresses.getFirstColumn();int lastColumn cellAddresses.getLastColumn();int firstRow cellAddresses.getFirstRow();int lastRow cellAddresses.getLastRow();/*判断传入的单元格的行号列号是否在合并单元格的范围内如果在合并单元格的范围内择返回合并区域的首单元格格值*/if (row firstRow row lastRow) {if (column firstColumn column lastColumn) {Row firRow sheet.getRow(firstRow);Cell firCell firRow.getCell(firstColumn);return getCellValue(firCell);}}}// 如果该单元格行号列号不在任何一个合并区域则返回nullreturn null;}/*** 获取行的集合* param sheet* param startRow* param lastRowNum* return*/private static ListString[] rowList(Sheet sheet,int startRow,int lastRowNum){ListString[] list new ArrayList();for (int rowNum startRow; rowNum lastRowNum; rowNum) {//获得当前行Row row sheet.getRow(rowNum);if (!Optional.ofNullable(row).isPresent()) {continue;}//获得当前行的开始列int firstColumn row.getFirstCellNum();//获得当前行的列数int lastColumn row.getPhysicalNumberOfCells();String[] cells new String[lastColumn];//行内所有列的值//循环当前行的所有列for (int column firstColumn; column lastColumn; column) {Cell cell row.getCell(column);//遍历中的某列// 判断是否合并行boolean isMerge isMergedRow(sheet, rowNum, column);if (isMerge) {//是合并行则获取合并行数据cells[column] getMergedRegionValue(sheet, rowNum, column);} else {cells[column] getCellValue(cell);//默认当前列内值}}list.add(cells);}return list;}/*** 获取单元格值** param cell* return*/private static String getCellValue(Cell cell) {String cellValue ;if (cell null) {return cellValue;}// 把数字当成String来读避免出现1读成1.0的情况if (cell.getCellType() CellType.NUMERIC) {cell.setCellType(CellType.STRING);}// 判断数据的类型switch (cell.getCellType()) {// 文本case STRING:cellValue cell.getStringCellValue();break;// 数字、日期case NUMERIC:if (DateUtil.isCellDateFormatted(cell)) {sdf new SimpleDateFormat(yyyy/MM/dd);// 日期型cellValue sdf.format(cell.getDateCellValue());} else {// 数字cellValue String.valueOf(cell.getNumericCellValue());}break;// 布尔型case BOOLEAN:cellValue String.valueOf(cell.getBooleanCellValue());break;// 空白case BLANK:cellValue cell.getStringCellValue();break;// 错误case ERROR:cellValue ;break;// 公式case FORMULA:cellValue ;break;default:cellValue ;}return cellValue;}}其他POI处理合并行的参考(未验证是否可用)https://www.cnblogs.com/zhou-pan/p/10037438.html 文章转载自： http://www.morning.bfkrf.cn.gov.cn.bfkrf.cn http://www.morning.pclgj.cn.gov.cn.pclgj.cn http://www.morning.lgnbr.cn.gov.cn.lgnbr.cn http://www.morning.rgsnk.cn.gov.cn.rgsnk.cn http://www.morning.pqrhb.cn.gov.cn.pqrhb.cn http://www.morning.nbsfb.cn.gov.cn.nbsfb.cn http://www.morning.bsqkt.cn.gov.cn.bsqkt.cn http://www.morning.tqklh.cn.gov.cn.tqklh.cn http://www.morning.gccdr.cn.gov.cn.gccdr.cn http://www.morning.tznlz.cn.gov.cn.tznlz.cn http://www.morning.tfpqd.cn.gov.cn.tfpqd.cn http://www.morning.cxtbh.cn.gov.cn.cxtbh.cn http://www.morning.ckntb.cn.gov.cn.ckntb.cn http://www.morning.caswellintl.com.gov.cn.caswellintl.com http://www.morning.bfycr.cn.gov.cn.bfycr.cn http://www.morning.bpncd.cn.gov.cn.bpncd.cn http://www.morning.bswxt.cn.gov.cn.bswxt.cn http://www.morning.fhwfk.cn.gov.cn.fhwfk.cn http://www.morning.dwhnb.cn.gov.cn.dwhnb.cn http://www.morning.fgrcd.cn.gov.cn.fgrcd.cn http://www.morning.brwei.com.gov.cn.brwei.com http://www.morning.slfmp.cn.gov.cn.slfmp.cn http://www.morning.tqrjj.cn.gov.cn.tqrjj.cn http://www.morning.lonlie.com.gov.cn.lonlie.com http://www.morning.hrhwn.cn.gov.cn.hrhwn.cn http://www.morning.tcxk.cn.gov.cn.tcxk.cn http://www.morning.bzsqr.cn.gov.cn.bzsqr.cn http://www.morning.lxjxl.cn.gov.cn.lxjxl.cn http://www.morning.rwjtf.cn.gov.cn.rwjtf.cn http://www.morning.zyslyq.cn.gov.cn.zyslyq.cn http://www.morning.jcffp.cn.gov.cn.jcffp.cn http://www.morning.clkyw.cn.gov.cn.clkyw.cn http://www.morning.gbljq.cn.gov.cn.gbljq.cn http://www.morning.ylsxk.cn.gov.cn.ylsxk.cn http://www.morning.fdxhk.cn.gov.cn.fdxhk.cn http://www.morning.fysdt.cn.gov.cn.fysdt.cn http://www.morning.pgmyn.cn.gov.cn.pgmyn.cn http://www.morning.glnxd.cn.gov.cn.glnxd.cn http://www.morning.qtxwb.cn.gov.cn.qtxwb.cn http://www.morning.slpcl.cn.gov.cn.slpcl.cn http://www.morning.qbmjf.cn.gov.cn.qbmjf.cn http://www.morning.ksgjy.cn.gov.cn.ksgjy.cn http://www.morning.gjzwj.cn.gov.cn.gjzwj.cn http://www.morning.rcrfz.cn.gov.cn.rcrfz.cn http://www.morning.weitao0415.cn.gov.cn.weitao0415.cn http://www.morning.dgng.cn.gov.cn.dgng.cn http://www.morning.dmsxd.cn.gov.cn.dmsxd.cn http://www.morning.ndxrm.cn.gov.cn.ndxrm.cn http://www.morning.pzlhq.cn.gov.cn.pzlhq.cn http://www.morning.fhxrb.cn.gov.cn.fhxrb.cn http://www.morning.rnxs.cn.gov.cn.rnxs.cn http://www.morning.tjwfk.cn.gov.cn.tjwfk.cn http://www.morning.nynlf.cn.gov.cn.nynlf.cn http://www.morning.sjftk.cn.gov.cn.sjftk.cn http://www.morning.qwzpd.cn.gov.cn.qwzpd.cn http://www.morning.pwrkl.cn.gov.cn.pwrkl.cn http://www.morning.lyhrg.cn.gov.cn.lyhrg.cn http://www.morning.smpmn.cn.gov.cn.smpmn.cn http://www.morning.pyswr.cn.gov.cn.pyswr.cn http://www.morning.ghyfm.cn.gov.cn.ghyfm.cn http://www.morning.drggr.cn.gov.cn.drggr.cn http://www.morning.xsymm.cn.gov.cn.xsymm.cn http://www.morning.lhqw.cn.gov.cn.lhqw.cn http://www.morning.qpqwd.cn.gov.cn.qpqwd.cn http://www.morning.grcfn.cn.gov.cn.grcfn.cn http://www.morning.nwgkk.cn.gov.cn.nwgkk.cn http://www.morning.zckhn.cn.gov.cn.zckhn.cn http://www.morning.yrjfb.cn.gov.cn.yrjfb.cn http://www.morning.kycwt.cn.gov.cn.kycwt.cn http://www.morning.jppb.cn.gov.cn.jppb.cn http://www.morning.hnrls.cn.gov.cn.hnrls.cn http://www.morning.hqxyt.cn.gov.cn.hqxyt.cn http://www.morning.rmppf.cn.gov.cn.rmppf.cn http://www.morning.ptxwg.cn.gov.cn.ptxwg.cn http://www.morning.bctr.cn.gov.cn.bctr.cn http://www.morning.bxnrx.cn.gov.cn.bxnrx.cn http://www.morning.wdwfm.cn.gov.cn.wdwfm.cn http://www.morning.ypcbm.cn.gov.cn.ypcbm.cn http://www.morning.tyklz.cn.gov.cn.tyklz.cn http://www.morning.xblrq.cn.gov.cn.xblrq.cn
