目錄一、添加poi的maven依賴(lài)二、自定義注解(Excel屬性標(biāo)題、位置等)三、CustomExcelUtils編寫(xiě)四、定義導(dǎo)出實(shí)體類(lèi)五、Controller層代碼編寫(xiě)一、添加poi的maven依賴(lài)

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.13</version></dependency><dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.13</version></dependency>二、自定義注解(Excel屬性標(biāo)題、位置等)

package com.cloud.core.annotation;import java.lang.annotation.*;/** * 自定義實(shí)體類(lèi)所需要的bean(Excel屬性標(biāo)題、位置等) * Copyright: Copyright (C) 2021 DLANGEL, Inc. All rights reserved. * Company: 大連安琪科技有限公司 * * @author Rex * @since 2021/5/19 9:30 */@Target({ElementType.FIELD})@Retention(RetentionPolicy.RUNTIME)@Documentedpublic @interface ExcelColumn { /** * Excel標(biāo)題 * * @return * @author Rex */ String value() default ''; /** * Excel從左往右排列位置，第一個(gè)是0 * * @return * @author Rex */ int col() default 0;}三、CustomExcelUtils編寫(xiě)

package com.cloud.core.utils;import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;import com.cloud.core.annotation.ExcelColumn;import com.cloud.core.common.CommonConst;import org.apache.commons.lang.BooleanUtils;import org.apache.commons.lang.CharUtils;import org.apache.commons.lang.StringUtils;import org.apache.commons.lang.math.NumberUtils;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.http.MediaType;import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Constructor;import java.lang.reflect.Field;import java.math.BigDecimal;import java.net.URLEncoder;import java.util.*;import java.util.concurrent.atomic.AtomicInteger;import java.util.stream.Collectors;import java.util.stream.Stream;/** * 自定義導(dǎo)入導(dǎo)出Excel文件類(lèi) * Copyright: Copyright (C) 2021 DLANGEL, Inc. All rights reserved. * Company: 大連安琪科技有限公司 * * @author Rex * @since 2021/5/19 9:31 */public class CustomExcelUtils { private final static Logger log = LoggerFactory.getLogger(CustomExcelUtils.class); private final static String EXCEL2003 = 'xls'; private final static String EXCEL2007 = 'xlsx'; /** * 讀取Excel * * @param path 為了測(cè)試文件用，實(shí)際為空 * @param cls 類(lèi) * @param startRow 起始行 * @param file 文件 * @return * @author Rex */ public static <T> List<T> readExcel(String path, Class<T> cls, int startRow, MultipartFile file) {String fileName = file.getOriginalFilename();if (!fileName.matches(CommonConst.Regex.FILE_EXT_XLS) && !fileName.matches(CommonConst.Regex.FILE_EXT_XLSX)) { log.error('上傳文件格式不正確');}List<T> dataList = new ArrayList<>();Workbook workbook = null;try { InputStream is = file.getInputStream(); if (fileName.endsWith(EXCEL2007)) {//FileInputStream is = new FileInputStream(new File(path));workbook = new XSSFWorkbook(is); } if (fileName.endsWith(EXCEL2003)) {//FileInputStream is = new FileInputStream(new File(path));workbook = new HSSFWorkbook(is); } if (workbook != null) {//類(lèi)映射 注解 value-->bean columnsMap<String, List<Field>> classMap = new HashMap<>();List<Field> fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList());fields.forEach(field -> { ExcelColumn annotation = field.getAnnotation(ExcelColumn.class); if (annotation != null) {String value = annotation.value();if (StringUtils.isBlank(value)) { // return起到的作用和continue是相同的 語(yǔ)法 return;}if (!classMap.containsKey(value)) { classMap.put(value, new ArrayList<>());}field.setAccessible(true);classMap.get(value).add(field); }});//索引-->columnsMap<Integer, List<Field>> reflectionMap = new HashMap<>(16);//默認(rèn)讀取第一個(gè)sheetSheet sheet = workbook.getSheetAt(0);boolean firstRow = true;for (int i = startRow; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); // 提取注解 if (firstRow) {for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { Cell cell = row.getCell(j); String cellValue = getCellValue(cell); if (classMap.containsKey(cellValue)) {reflectionMap.put(j, classMap.get(cellValue)); }}if (reflectionMap.size() > 0) { firstRow = false;} } else {//忽略空白行if (row == null) { continue;}try { T t = cls.newInstance(); //判斷是否為空白行 boolean allBlank = true; for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {if (reflectionMap.containsKey(j)) { Cell cell = row.getCell(j); String cellValue = getCellValue(cell); if (StringUtils.isNotBlank(cellValue)) {allBlank = false; } List<Field> fieldList = reflectionMap.get(j); fieldList.forEach( x -> {try {handleField(t, cellValue, x);} catch (Exception e) {log.error(String.format('reflect field:%s value:%s exception!', x.getName(), cellValue), e);} } );} } if (!allBlank) {dataList.add(t); } else {log.warn(String.format('row:%s is blank ignore!', i)); }} catch (Exception e) { log.error(String.format('parse row:%s exception!', i), e);} }} }} catch (Exception e) { log.error(String.format('parse excel exception!'), e);} finally { if (workbook != null) {try { workbook.close();} catch (Exception e) { log.error(String.format('parse excel exception!'), e);} }}return dataList; } private static <T> void handleField(T t, String value, Field field) throws Exception {Class<?> type = field.getType();if (type == null || type == void.class || StringUtils.isBlank(value)) { return;}if (type == Object.class) { field.set(t, value); //數(shù)字類(lèi)型} else if (type.getSuperclass() == null || type.getSuperclass() == Number.class) { if (type == int.class || type == Integer.class) {field.set(t, NumberUtils.toInt(value)); } else if (type == long.class || type == Long.class) {field.set(t, NumberUtils.toLong(value)); } else if (type == byte.class || type == Byte.class) {field.set(t, NumberUtils.toByte(value)); } else if (type == short.class || type == Short.class) {field.set(t, NumberUtils.toShort(value)); } else if (type == double.class || type == Double.class) {field.set(t, NumberUtils.toDouble(value)); } else if (type == float.class || type == Float.class) {field.set(t, NumberUtils.toFloat(value)); } else if (type == char.class || type == Character.class) {field.set(t, CharUtils.toChar(value)); } else if (type == boolean.class) {field.set(t, BooleanUtils.toBoolean(value)); } else if (type == BigDecimal.class) {field.set(t, new BigDecimal(value)); }} else if (type == Boolean.class) { field.set(t, BooleanUtils.toBoolean(value));} else if (type == Date.class) { // field.set(t, value);} else if (type == String.class) { field.set(t, value);} else { Constructor<?> constructor = type.getConstructor(String.class); field.set(t, constructor.newInstance(value));} } private static String getCellValue(Cell cell) {if (cell == null) { return '';}if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { if (HSSFDateUtil.isCellDateFormatted(cell)) {return HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString(); } else {return new BigDecimal(cell.getNumericCellValue()).toString(); }} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { return StringUtils.trimToEmpty(cell.getStringCellValue());} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { return StringUtils.trimToEmpty(cell.getCellFormula());} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { return '';} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { return String.valueOf(cell.getBooleanCellValue());} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) { return 'ERROR';} else { return cell.toString().trim();} } public static <T> void writeExcel(HttpServletResponse response, List<T> dataList, Class<T> cls) {Field[] fields = cls.getDeclaredFields();List<Field> fieldList = Arrays.stream(fields).filter(field -> { ExcelColumn annotation = field.getAnnotation(ExcelColumn.class); if (annotation != null && annotation.col() > 0) {field.setAccessible(true);return true; } return false;}).sorted(Comparator.comparing(field -> { int col = 0; ExcelColumn annotation = field.getAnnotation(ExcelColumn.class); if (annotation != null) {col = annotation.col(); } return col;})).collect(Collectors.toList());Workbook wb = new XSSFWorkbook();Sheet sheet = wb.createSheet('Sheet1');AtomicInteger ai = new AtomicInteger();{ Row row = sheet.createRow(ai.getAndIncrement()); AtomicInteger aj = new AtomicInteger(); //寫(xiě)入頭部 fieldList.forEach(field -> {ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);String columnName = '';if (annotation != null) { columnName = annotation.value();}Cell cell = row.createCell(aj.getAndIncrement());CellStyle cellStyle = wb.createCellStyle();cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);cellStyle.setAlignment(CellStyle.ALIGN_CENTER);Font font = wb.createFont();font.setBoldweight(Font.BOLDWEIGHT_NORMAL);cellStyle.setFont(font);cell.setCellStyle(cellStyle);cell.setCellValue(columnName); });}if (CollectionUtils.isNotEmpty(dataList)) { dataList.forEach(t -> {Row row1 = sheet.createRow(ai.getAndIncrement());AtomicInteger aj = new AtomicInteger();fieldList.forEach(field -> { Class<?> type = field.getType(); Object value = ''; try {value = field.get(t); } catch (Exception e) {e.printStackTrace(); } Cell cell = row1.createCell(aj.getAndIncrement()); if (value != null) {if (type == Date.class) { cell.setCellValue(value.toString());} else { cell.setCellValue(value.toString());}cell.setCellValue(value.toString()); }}); });}//凍結(jié)窗格wb.getSheet('Sheet1').createFreezePane(0, 1, 0, 1);//瀏覽器下載excelbuildExcelDocument('導(dǎo)出數(shù)據(jù).xlsx', wb, response);//生成excel文件//buildExcelFile('.default.xlsx', wb); } /** * 瀏覽器下載excel * * @param fileName * @param wb * @param response */ private static void buildExcelDocument(String fileName, Workbook wb, HttpServletResponse response) {try { response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE); response.setHeader('Content-Disposition', 'attachment;filename=' + URLEncoder.encode(fileName, 'utf-8')); response.flushBuffer(); wb.write(response.getOutputStream());} catch (IOException e) { e.printStackTrace();} } /** * 生成excel文件 * * @param path 生成excel路徑 * @param wb */ private static void buildExcelFile(String path, Workbook wb) {File file = new File(path);if (file.exists()) { file.delete();}try { wb.write(new FileOutputStream(file));} catch (Exception e) { e.printStackTrace();} }}四、定義導(dǎo)出實(shí)體類(lèi)

主要是使用這里的@ExcelColumn注解，其中的col從0開(kāi)始的。

package com.cloud.library.model.role;import com.cloud.core.annotation.ExcelColumn;import lombok.Data;/** * 導(dǎo)入角色用 * Copyright: Copyright (C) 2021 DLANGEL, Inc. All rights reserved. * Company: 大連安琪科技有限公司 * * @author Rex * @since 2021/5/19 16:13 */@Datapublic class SysRoleExcel { @ExcelColumn(value = '姓名', col = 1) private String name; @ExcelColumn(value = '描述', col = 2) private String description;}

Excel對(duì)應(yīng)的模板參考

五、Controller層代碼編寫(xiě)

//region 導(dǎo)入數(shù)據(jù) /** * 導(dǎo)入數(shù)據(jù) * * @param file * @return * @author Rex */ @RequestMapping(value = '/readExcel', method = RequestMethod.POST) public void readExcel(@RequestParam(value = 'uploadFile', required = false) MultipartFile file) {List<SysRoleExcel> list = CustomExcelUtils.readExcel('', SysRoleExcel.class, 0, file);List<SysRole> sysRoleList = new ArrayList<>();list.forEach(e -> { SysRole sysRole = new SysRole(); BeanUtils.copyProperties(e, sysRole); sysRoleList.add(sysRole);});sysRoleService.saveBatch(sysRoleList); } // endregion

這里發(fā)現(xiàn)了，這個(gè)saveBatch可以直接使用雪花的id來(lái)保存數(shù)據(jù)，因?yàn)檫@里用的是mybatis-plus，單條數(shù)據(jù)保存使用的是它的配置。然后試了下，批量導(dǎo)入也是可以的，另外，這個(gè)批量保存，理論上沒(méi)有條數(shù)限制，這個(gè)還等待后續(xù)測(cè)試。

到此這篇關(guān)于Springboot實(shí)現(xiàn)導(dǎo)入導(dǎo)出Excel的方法的文章就介紹到這了,更多相關(guān)Springboot導(dǎo)入導(dǎo)出Excel內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！