前提：maven构建
poi针对excel的不同版本需要不同的处理，即.xls和.xlsx文件类型在解析时需要不同的处理：
-- 关键代码
private static Sheet getDefaultSheet(MultipartFile file) throws Exception {
String fileName = file.getOriginalFilename();
Sheet defaultSheet = null;
try (InputStream inputStream = file.getInputStream()) {
if (fileName.endsWith(".xls")) {
defaultSheet = new HSSFWorkbook(inputStream).getSheetAt(0);
} else if (fileName.endsWith(".xlsx")) {
defaultSheet = new XSSFWorkbook(inputStream).getSheetAt(0);
} else {
throw new Exception("CHECK_ME: unknown file type! this file is not excel.");
}
} catch (IOException e) {
e.printStackTrace();
logger.error("IOException", e);
}
return defaultSheet;
}
1、pom.xml文件
<!--excel导出，解析-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
2、创建导入导出工具类（导入导出字段均可灵活配置）
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
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.XSSFWorkbook;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import javax.validation.constraints.NotNull;
import java.io.IOException;
import java.io.InputStream;
import java.lang.invoke.MethodHandles;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.math.BigDecimal;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;
/**
* @Author: geyingke
* @Date: 2020/5/13
* @Class: ExcelUtil
* @Discription: TODO
**/
public class ExcelUtil {
private final static String GET_ITEM = "get";
private final static String SET_ITEM = "set";
private static Logger logger = LogManager.getLogger(MethodHandles.lookup().lookupClass());
//普通文件导出方法，标题和字段分开处理
public static <T extends Object> HSSFWorkbook createExcelFile(String sheetName, String[] titles, Collection<T> contentBeans, List<String> needFields, HSSFWorkbook workbook) {
if (workbook == null) {
workbook = new HSSFWorkbook();
}
//创建sheet
HSSFSheet sheet = workbook.createSheet(sheetName);
sheet.setDefaultColumnWidth((short) 15);
HSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.WHITE.index);
//标题行
AtomicReference<HSSFRow> row = new AtomicReference<>(sheet.createRow(0));
for (int i = 0; i < titles.length; i++) {
HSSFCell titleCell = row.get().createCell(i);
HSSFRichTextString text = new HSSFRichTextString(titles[i]);
titleCell.setCellValue(text);
}
AtomicInteger index = new AtomicInteger(0);
contentBeans.stream().forEach(t -> {
Class tClass = t.getClass();
int rowIndex = index.addAndGet(1);
row.set(sheet.createRow(rowIndex));
Field[] fields = tClass.getDeclaredFields();
AtomicInteger cellIndex = new AtomicInteger(0);
//过滤final字段，并遍历剩下的字段创建表格行
List<Field> fieldList = Arrays.stream(fields).filter(f -> {
if (needFields != null) {
if (!Modifier.isFinal(f.getModifiers()) && needFields.contains((String) f.getName())) {
return true;
}
} else {
if (!Modifier.isFinal(f.getModifiers())) {
return true;
}
}
return false;
}).collect(Collectors.toList());
if (needFields != null) {
for (String fieldName : needFields) {
HSSFCell rowCell = row.get().createCell(cellIndex.get());
rowCell.setCellStyle(style);
String getMethodName = generateGetMethodName(fieldName);
try {
Method getMethod = tClass.getMethod(getMethodName, new Class[]{});
Object value = getMethod.invoke(t, new Object[]{});
if (value instanceof BigDecimal) {
double val = ((BigDecimal) value).doubleValue();
rowCell.setCellValue(val);
} else if (value instanceof Integer) {
Integer integer = (Integer) value;
rowCell.setCellValue(Double.valueOf(integer.toString()));
} else if (value instanceof String) {
HSSFRichTextString textString = new HSSFRichTextString((String) value);
rowCell.setCellValue(textString);
} else if (value instanceof Long) {
double val = Double.parseDouble(value.toString());
rowCell.setCellValue(val);
} else {
if (value == null) {
HSSFRichTextString textString = new HSSFRichTextString("");
rowCell.setCellValue(textString);
} else {
logger.info("CHECKME: unknown class type of value." + value.toString());
}
}
} catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
e.printStackTrace();
logger.error("FIXME: unExcepted exception!", e);
} finally {
cellIndex.addAndGet(1);
}
}
} else {
fieldList.forEach(field -> {
HSSFCell rowCell = row.get().createCell(cellIndex.get());
rowCell.setCellStyle(style);
String fieldName = field.getName();
String getMethodName = generateGetMethodName(fieldName);
try {
Method getMethod = tClass.getMethod(getMethodName, new Class[]{});
Object value = getMethod.invoke(t, new Object[]{});
if (value instanceof BigDecimal) {
double val = ((BigDecimal) value).doubleValue();
rowCell.setCellValue(val);
} else if (value instanceof Integer) {
Integer integer = (Integer) value;
rowCell.setCellValue(Double.valueOf(integer.toString()));
} else if (value instanceof String) {
HSSFRichTextString textString = new HSSFRichTextString((String) value);
rowCell.setCellValue(textString);
} else if (value instanceof Long) {
double val = Double.parseDouble(value.toString());
rowCell.setCellValue(val);
} else {
if (value == null) {
HSSFRichTextString textString = new HSSFRichTextString("");
rowCell.setCellValue(textString);
} else {
logger.info("CHECKME: unknown class type of value." + value.toString());
}
}
} catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
e.printStackTrace();
logger.error("FIXME: unExcepted exception!", e);
} finally {
cellIndex.addAndGet(1);
}
});
}
});
return workbook;
}
//配置型excel文件导出方法，fieldMap为配置的标题和字段【格式为：key-标题名称；value-字段。
//字段可以配置多个（当前处理为，按配置顺序取到有值的字段）】
public static <T extends Object> HSSFWorkbook createExcelFileUseFieldMap(String sheetName, Collection<T> contentBeans, @NotNull Map<String, List<String>> fieldMap, HSSFWorkbook workbook) {
Assert.notNull(fieldMap, "field map can not be null");
if (workbook == null) {
workbook = new HSSFWorkbook();
}
//创建sheet
HSSFSheet sheet = workbook.createSheet(sheetName);
sheet.setDefaultColumnWidth((short) 15);
HSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.WHITE.index);
//标题行
AtomicReference<HSSFRow> row = new AtomicReference<>(sheet.createRow(0));
Object[] titles = fieldMap.keySet().toArray();
for (int i = 0; i < titles.length; i++) {
HSSFCell titleCell = row.get().createCell(i);
HSSFRichTextString text = new HSSFRichTextString(String.valueOf(titles[i]));
titleCell.setCellValue(text);
}
AtomicInteger index = new AtomicInteger(0);
contentBeans.stream().forEach(t -> {
Class tClass = t.getClass();
int rowIndex = index.addAndGet(1);
row.set(sheet.createRow(rowIndex));
Field[] fields = tClass.getDeclaredFields();
AtomicInteger cellIndex = new AtomicInteger(0);
//过滤final字段，并遍历剩下的字段创建表格行
Collection<List<String>> fieldsCollection = fieldMap.values();
List<Field> fieldList = Arrays.stream(fields).filter(f -> {
if (fieldsCollection != null) {
if (!Modifier.isFinal(f.getModifiers())) {
for (List<String> fieldLis : fieldsCollection) {
if (null != fieldLis && fieldLis.contains(String.valueOf(f.getName()))) {
return true;
}
}
return false;
}
} else {
if (!Modifier.isFinal(f.getModifiers())) {
return true;
}
}
return false;
}).collect(Collectors.toList());
if (fieldsCollection != null) {
fieldsCollectionLoop:
for (List<String> fieldNames : fieldsCollection) {
try {
HSSFCell rowCell = row.get().createCell(cellIndex.get());
rowCell.setCellStyle(style);
fieldNameLoop:
for (int i = 0; i < fieldNames.size(); i++) {
String fieldName = fieldNames.get(i);
String getMethodName = generateGetMethodName(fieldName);
Method getMethod = tClass.getMethod(getMethodName, new Class[]{});
Object value = getMethod.invoke(t, new Object[]{});
if (null != value) {
if (value instanceof BigDecimal) {
double val = ((BigDecimal) value).doubleValue();
rowCell.setCellValue(val);
} else if (value instanceof Integer) {
Integer integer = (Integer) value;
rowCell.setCellValue(Double.valueOf(integer.toString()));
} else if (value instanceof String) {
HSSFRichTextString textString = new HSSFRichTextString((String) value);
rowCell.setCellValue(textString);
} else if (value instanceof Long) {
double val = Double.parseDouble(value.toString());
rowCell.setCellValue(val);
} else if (value instanceof Boolean) {
//TODO：布尔类型字段的特殊处理
} else {
logger.info("CHECKME: unknown class type of value." + value.toString());
}
continue fieldsCollectionLoop;
} else {
if (i == fieldNames.size() - 1) {
HSSFRichTextString textString = new HSSFRichTextString("");
rowCell.setCellValue(textString);
}
continue fieldNameLoop;
}
}
} catch (Exception e) {
logger.error("FIXME: unExcepted exception!", e);
} finally {
cellIndex.addAndGet(1);
}
}
} else {
logger.error("CHECK_ME: illegal field collection,please check");
}
});
return workbook;
}
/**
* 导入excel文件，并转换成指定类型的List集合
*
* @param file
* @param fieldMap -- 标题和字段的映射，key为标题名称，value为标题字段
* @param tClass
* @param <T>
* @return
* @throws Exception
*/
public static <T extends Object> List<T> commonImportExcel(MultipartFile file, Map<String, String> fieldMap, Class<T> tClass, int titleRowIndex) throws Exception {
List<T> result = new ArrayList<>();
//获取默认表
Sheet defaultSheet = getDefaultSheet(file);
if (defaultSheet != null) {
if (defaultSheet.getLastRowNum() <= 1) {
throw new Exception("CHECK_ME: file is empty.");
}
//获取标题行,并创建标题的列映射
Map<Integer, String> title2ColumnMap = new HashMap<>();
Row titleRow = defaultSheet.getRow(titleRowIndex);
for (int index = 0; ; index++) {
Cell cell = titleRow.getCell(index);
if (null != cell) {
String title = cell.getStringCellValue();
if (StringUtils.isEmpty(title)) {
break;
} else {
String[] strings = fieldMap.keySet().toArray(new String[]{});
for (int idx = 0; idx < strings.length; idx++) {
String key = strings[idx];
if (title.contains(key)) {
title2ColumnMap.put(index, fieldMap.get(key));
break;
} else {
if (idx == strings.length) {
//fieldMap配置有不正确的，终止导入操作
throw new Exception("CHECK_ME: unknown field! please check configuration of field map");
}
}
}
}
} else {
break;
}
}
//开始遍历除标题之外的行
for (Row row : defaultSheet) {
int rowIndex = row.getRowNum();
if (rowIndex < titleRowIndex + 1) {
//越过标题行,及标题行之前的所有内容
continue;
}
T tInstance = tClass.newInstance();
for (Integer keyIndex : title2ColumnMap.keySet()) {
Cell cell = row.getCell(keyIndex);
if (null != cell) {
String cellVal = getStringCellValue(cell);
//越过值为空的字段
if (StringUtils.isEmpty(cellVal)) {
break;
}
String fieldName = title2ColumnMap.get(keyIndex);
Field field = getBeanInnerField(tClass, fieldName);
Class fieldType = field.getType();
Method method = getBeanInnerFieldMethod(tClass, fieldName, fieldType);
setFieldValue(method, tInstance, fieldType, cellVal);
} else {
break;
}
}
result.add(tInstance);
}
}
return result.size() > 0 ? result : null;
}
/**
* 导入excel文件，并转换成指定类型的List集合
*
* @param file
* @param fieldMap -- 标题和字段的映射，key为类中字段 |
* value为标题字段匹配规则集合（支持正则）（可以存在多个）
* @param tClass
* @param <T>
* @return
* @throws Exception
*/
public static <T extends Object> List<T> importExcel(MultipartFile file, Map<String, List<String>> fieldMap, Class<T> tClass, int titleRowIndex) throws Exception {
List<T> result = new ArrayList<>();
//获取默认表
Sheet defaultSheet = getDefaultSheet(file);
if (defaultSheet != null) {
if (defaultSheet.getLastRowNum() <= 1) {
throw new Exception("CHECK_ME: file is empty.");
}
//获取标题行,并创建标题的列映射
Map<String, List<Integer>> beanField2TitleMap = getBeanField2TitleMap(defaultSheet.getRow(titleRowIndex), fieldMap);
//开始遍历除标题之外的行
for (Row row : defaultSheet) {
int rowIndex = row.getRowNum();
if (rowIndex < titleRowIndex + 1) {
//越过标题行,及标题行之前的所有内容
continue;
}
T tInstance = tClass.newInstance();
for (Map.Entry<String, List<Integer>> entry : beanField2TitleMap.entrySet()) {
final String fieldName = entry.getKey();
final List<Integer> cellIndexList = entry.getValue();
Field field = getBeanInnerField(tClass, fieldName);
Class fieldType = field.getType();
checkFieldTypeAndSetVal(row, fieldType, cellIndexList, fieldName, tClass, tInstance);
}
result.add(tInstance);
}
}
return result.size() > 0 ? result : null;
}
/**
* 字段的特殊处理，留待后续扩充使用
* @param row
* @param fieldType
* @param cellIndexList
* @param fieldName
* @param tClass
* @param tInstance
* @param <T>
* @throws Exception
*/
private static <T extends Object> void checkFieldTypeAndSetVal(Row row, Class fieldType, List<Integer> cellIndexList, String fieldName, Class<T> tClass, T tInstance) throws Exception {
if (null != cellIndexList && cellIndexList.size() > 0) {
if (fieldType.equals(List.class)) {
List<String> cellValLis = new ArrayList<>();
for (Integer cellIndex : cellIndexList) {
Cell cell = row.getCell(cellIndex);
if (cell != null) {
String cellVal = getStringCellValue(cell);
//越过值为空的字段
if (StringUtils.isEmpty(cellVal)) {
break;
}
cellValLis.add(cellVal);
}
}
Method method = getBeanInnerFieldMethod(tClass, fieldName, fieldType);
setFieldValue(method, tInstance, fieldType, cellValLis);
} else {
for (Integer cellIndex : cellIndexList) {
Cell cell = row.getCell(cellIndex);
if (cell != null) {
String cellVal = getStringCellValue(cell);
//越过值为空的字段
if (!StringUtils.isEmpty(cellVal)) {
Method method = getBeanInnerFieldMethod(tClass, fieldName, fieldType);
setFieldValue(method, tInstance, fieldType, cellVal);
}
}
}
}
}
}
/**
* 获取类中字段与标题的映射关系
* （字段：标题==》1：n）
*
* @param titleRow
* @param fieldMap
* @return
*/
private static Map<String, List<Integer>> getBeanField2TitleMap(Row titleRow, Map<String, List<String>> fieldMap) {
Map<String, List<Integer>> beanField2TitleMap = new HashMap<>();
String[] fieldArr = fieldMap.keySet().toArray(new String[]{});
for (String key : fieldArr) {
final List<String> titleList = fieldMap.get(key);
List<Integer> titleIndexList = new ArrayList<>();
for (String titleName : titleList) {
for (int index = 0; ; index++) {
Cell cell = titleRow.getCell(index);
if (null != cell) {
String title = cell.getStringCellValue();
if (StringUtils.isEmpty(title)) {
break;
} else {
if (title.matches(titleName)) {
titleIndexList.add(index);
}
}
} else {
break;
}
}
}
beanField2TitleMap.put(key, titleIndexList);
}
return beanField2TitleMap;
}
/**
* 获取默认的数据表
*
* @param file
* @return
* @throws Exception
*/
private static Sheet getDefaultSheet(MultipartFile file) throws Exception {
String fileName = file.getOriginalFilename();
Sheet defaultSheet = null;
try (InputStream inputStream = file.getInputStream()) {
if (fileName.endsWith(".xls")) {
defaultSheet = new HSSFWorkbook(inputStream).getSheetAt(0);
} else if (fileName.endsWith(".xlsx")) {
defaultSheet = new XSSFWorkbook(inputStream).getSheetAt(0);
} else {
throw new Exception("CHECK_ME: unknown file type! this file is not excel.");
}
} catch (IOException e) {
e.printStackTrace();
logger.error("IOException", e);
}
return defaultSheet;
}
/**
* 设置类field的值
*
* @param method
* @param tInstance
* @param fieldType
* @param cellVal
* @param <T>
* @throws Exception
*/
private static <T extends Object> void setFieldValue(Method method, T tInstance, Class fieldType, Object cellVal) throws Exception {
if (fieldType.equals(String.class)) {
method.invoke(tInstance, cellVal);
} else if (fieldType.equals(int.class) || fieldType.equals(Integer.class)) {
method.invoke(tInstance, new Double((String) cellVal).intValue());
} else if (fieldType.equals(long.class) || fieldType.equals(Long.class)) {
method.invoke(tInstance, new Double((String) cellVal).longValue());
} else if (fieldType.equals(List.class)) {
method.invoke(tInstance, cellVal);
} else if (fieldType.equals(Object.class)) {
method.invoke(tInstance, cellVal);
} else {
throw new Exception("FIX_ME: field type miss match.");
}
}
/**
* 从当前类和父类，获取类中feild的set方法
*
* @param tClass
* @param fieldName
* @param fieldType
* @param <T>
* @return
* @throws NoSuchMethodException
*/
private static <T extends Object> Method getBeanInnerFieldMethod(Class<T> tClass, String fieldName, Class fieldType) throws NoSuchMethodException {
Method method = null;
try {
method = tClass.getDeclaredMethod(generateSetMethodName(fieldName), fieldType);
} catch (NoSuchMethodException e) {
method = tClass.getSuperclass().getDeclaredMethod(generateSetMethodName(fieldName), fieldType);
}
return method;
}
/**
* 从当前类和父类，获取类中的feild
*
* @param tClass
* @param fieldName
* @param <T>
* @return
* @throws NoSuchFieldException
*/
private static <T extends Object> Field getBeanInnerField(Class<T> tClass, String fieldName) throws NoSuchFieldException {
Field field = null;
try {
field = tClass.getDeclaredField(fieldName);
} catch (NoSuchFieldException e) {
field = tClass.getSuperclass().getDeclaredField(fieldName);
}
return field;
}
private static String getStringCellValue(Cell cell) {
String res = "";
switch (cell.getCellTypeEnum()) {
case _NONE:
res = null;
break;
case STRING:
res += cell.getStringCellValue();
break;
case NUMERIC:
res += cell.getNumericCellValue();
break;
default:
res = null;
break;
}
return res;
}
/**
* 生成get方法名称
*
* @param fieldName
* @return
*/
public static String generateGetMethodName(String fieldName) {
StringBuffer stringBuffer = new StringBuffer(GET_ITEM);
return stringBuffer.append(fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1)).toString();
}
/**
* 生成set方法
*
* @param fieldName
* @return
*/
public static String generateSetMethodName(String fieldName) {
StringBuffer stringBuffer = new StringBuffer(SET_ITEM);
return stringBuffer.append(fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1)).toString();
}
}
3、导入应用
-- 关键代码
public xxx import(MultipartFile file, @RequestParam String id) throws Exception {
logger.info("request param --> id:" + id);
//标签类型 项目名称 编号 标准问句 答案顺序 答案 答案是否可修改 一级业务分类 二级业务分类 补充说明 链接
//配置标题字段映射
Map<String, List<String>> fieldMap = new HashMap<String, List<String>>() {{
put("knowledgeProjectName", new ArrayList<String>() {{
add("项目名称");
}});
put("knowledgeCode", new ArrayList<String>() {{
add("编号");
}});
put("question", new ArrayList<String>() {{
add("标准问句");
}});
put("answerOrder", new ArrayList<String>() {{
add("答案顺序");
}});
put("answer", new ArrayList<String>() {{
add("答案");
}});
put("answerStatus", new ArrayList<String>() {{
add("答案是否可修改");
}});
put("serviceTypeName", new ArrayList<String>() {{
add("一级业务分类");
add("二级业务分类");
}});
put("customField", new ArrayList<String>() {{
add("补充说明");
}});
put("linkUrl", new ArrayList<String>() {{
add("链接");
}});
put("similarQuestion", new ArrayList<String>() {{
add("相似问句[0-9]");
}});
}};
//导入excel转换成实体类集合
List<Bean> beans= ExcelUtil.importExcel(file, fieldMap, Bean.class, 0);
}
4、导出应用
-- 关键代码
String sheetName = "日志" + System.currentTimeMillis();
HSSFWorkbook excelFile = ExcelUtil.createExcelFileUseFieldMap(sheetName, faultLogList, faultLogExportFieldMap, null);
String excelName = String.valueOf(sheetName + ".xls");
Map<String, Object> result = new HashMap<>();
result.put("url", outServer + requestPath + excelName);
FileOutputStream fileOutputStream = new FileOutputStream(path + excelName);
excelFile.write(fileOutputStream);
-- 使用springboot自带tomcat实现文件导出到本地配置目录，并支持远程下载
-- yml文件配置文件输出路径，及外部访问路径
out:
server: 11.33.7.*:6070/*项目路径*/
request-path: /excel/
resource:
path: D:/static/excel/
-- 配置spring web路径映射
@Configuration
public class MyWebConfigurer extends WebMvcConfigurerAdapter{
//WebMvcConfigurerAdapter类当前高版本springboot有些不支持，可继承WebMvcConfigurationSupport实现相同功能
@Value("${out.resource.path}")
String path;
@Value("${out.request-path}")
String requestPath;
@Override
public void addResourceHandlers(ResourceHandlerRegistry registry) {
registry.addResourceHandler("/**").addResourceLocations("classpath:/static");
registry.addResourceHandler("/webjars/**").addResourceLocations("classpath:/META-INF/resources/webjars/");
registry.addResourceHandler(requestPath + "**").addResourceLocations("file:" + path);
super.addResourceHandlers(registry);
}
@Override
public void configureDefaultServletHandling(DefaultServletHandlerConfigurer configurer) {
configurer.enable();
}
//取消excel请求路径拦截，不进行权限校验
@Override
public void addInterceptors(InterceptorRegistry registry) {
//设定excel导出请求路径不做拦截
registry.addInterceptor(new PermissionInterceptor()).excludePathPatterns("/excel/**");
}
}
