Springboot實現導入導出Excel的方法
<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屬性標題、位置等)
package com.cloud.core.annotation;import java.lang.annotation.*;/** * 自定義實體類所需要的bean(Excel屬性標題、位置等) * 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標題 * * @return * @author Rex */ String value() default ''; /** * Excel從左往右排列位置，第一個是0 * * @return * @author Rex */ int col() default 0;}三、CustomExcelUtils編寫
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;/** * 自定義導入導出Excel文件類 * 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 為了測試文件用，實際為空 * @param cls 類 * @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) {//類映射 注解 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是相同的 語法 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);//默認讀取第一個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); //數字類型} 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(); //寫入頭部 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()); }}); });}//凍結窗格wb.getSheet('Sheet1').createFreezePane(0, 1, 0, 1);//瀏覽器下載excelbuildExcelDocument('導出數據.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();} }}四、定義導出實體類
主要是使用這里的@ExcelColumn注解，其中的col從0開始的。
package com.cloud.library.model.role;import com.cloud.core.annotation.ExcelColumn;import lombok.Data;/** * 導入角色用 * 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對應的模板參考
//region 導入數據 /** * 導入數據 * * @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
這里發現了，這個saveBatch可以直接使用雪花的id來保存數據，因為這里用的是mybatis-plus，單條數據保存使用的是它的配置。然后試了下，批量導入也是可以的，另外，這個批量保存，理論上沒有條數限制，這個還等待后續測試。
到此這篇關于Springboot實現導入導出Excel的方法的文章就介紹到這了,更多相關Springboot導入導出Excel內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！
相關文章：
網公網安備