本篇内容介绍了“怎么使用Java+element实现excel导入和导出”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
本项目是前端vue3，后端springboot开发 需求为：前端导入表格，后端处理表格存储数据，点击按钮可以导出表格。
上传效果：前端点击上传按钮，会跳出选择文件框，选择文件，点击上传。
导出效果：前端点击导出按钮，会跳出下载框，选择位置自动下载。
上传效果图：
下载效果图：
一、上传excel前端代码
<el-upload ref="file" class="upload-demo" :limit="1" accept=".xlsx, .xls" action="http://localhost:8081/admin/perform/importexcel" auto-upload="false" > <template #trigger> <el-button type="primary">选择文件</el-button> </template> <el-button class="ml-3" type="success" @click="submitUpload" > 上传文件 </el-button> 仅允许导入xls、xlsx格式文件。 </el-upload>import { ref, reactive, computed } from "vue"import { ElMessage, UploadInstance } from "element-plus"const file = ref<UploadInstance>()const submitUpload = () => { file.value!.submit() ElMessage({ message: "上传成功", type: "success", }) window.location.reload()}效果图
二、上传excel后端代码
Controller层
@PostMapping("/importexcel") public Result importData(MultipartFile file) throws Exception { return performService.importData(file.getInputStream()); }Service层
@Override public Result importData(InputStream inputStream) throws IOException { // Perform根据自己表格的表头创建的实体，要意义对应 List<Perform> res = new ArrayList<>(); try { ins = (FileInputStream) inputStream; //true xls文件，false xlsx文件 Workbook workbook = null; // XSSFWorkbook instance of HSSFWorkbook 所以通用 workbook = new XSSFWorkbook(ins); //获取工作表 Sheet sheet = workbook.getSheetAt(0); //获取表头 Row rowHead = sheet.getRow(0); //判断表头是否正确 if (rowHead.getPhysicalNumberOfCells() < 1) { return Result.error("表头错误"); } //获取数据 for (int i = 1; i <= sheet.getLastRowNum(); i++) { //获取第一行的用户信息 Row row = sheet.getRow(i); String tId; if (row.getCell(0) == null) { tId = ""; row.createCell(0).setCellValue(tId); } else { //先设置为字符串再作为数字读出来 row.getCell(0).setCellType(CellType.STRING); tId = row.getCell(0).getStringCellValue(); } String tName; if (row.getCell(1) == null) { tName = ""; row.createCell(1).setCellValue(tName); } else { tName = row.getCell(1).getStringCellValue(); } String tDept; if (row.getCell(2) == null) { tDept = ""; row.createCell(2).setCellValue(tDept); } else { tDept = row.getCell(2).getStringCellValue(); }.................... Perorm perform=new Perform() xxxset创建实体 System.out.println(perform); res.add(perform); } } catch (IOException e) { e.printStackTrace(); } finally { if (ins != null) { try { ins.close(); } catch (IOException e) { e.printStackTrace(); } } if (out != null) { try { out.close(); } catch (IOException e) { e.printStackTrace(); } } } new Thread(() -> { //批处理比较快 batchInsert(res); }).start(); return Result.success(res); } private void batchInsert(List<Perform> performList) { SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false); performList.stream().forEach(perform -> { performMapper.insert(perform); }); sqlSession.commit(); sqlSession.clearCache(); }三、下载excel前端代码
<el-button type="warning" @click="exportInfo()" > <a href="http://localhost:8081/admin/perform/exportexcel" rel="external nofollow" >导出</a > </el-button>const exportInfo = () => { ElMessage({ message: "请稍等", type: "warning", })}四、下载excel后端代码
Controller层
@GetMapping("/exportexcel") public void exportExcel(HttpServletResponse response) throws Exception { performService.exportExcel(response); }Service层
@Override public void exportExcel(HttpServletResponse response) throws IOException { System.out.println("导出表格"); List<Perform> list = performMapper.selectList(new QueryWrapper<>()); String sheetName = "教师业绩表"; Map<String, String> titleMap = new LinkedHashMap<>(); titleMap.put("tId", "教师工号"); titleMap.put("tName", "教师姓名");.....根据自己的表头来 ExportExcel.excelExport(response, list, titleMap, sheetName); }ExportExcel类：
package com.performance.back.common.utils;import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;import com.performance.back.admin.dao.entity.Perform;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.io.OutputStream;import java.lang.reflect.Method;import java.text.SimpleDateFormat;import java.util.Date;import java.util.List;import java.util.Map;public class ExportExcel { private ExportExcel() { } private static HSSFWorkbook workbook; private static HSSFSheet sheet; private static final int TITLE_START_POSITION = 0; private static final int DATEHEAD_START_POSITION = 1; private static final int HEAD_START_POSITION = 0; private static final int CONTENT_START_POSITION = 1; private static void initHSSFWorkbook(String sheetName) { workbook = new HSSFWorkbook(); sheet = workbook.createSheet(sheetName); sheet.setDefaultColumnWidth(15); } private static void createTitleRow(Map<String, String> titleMap, String sheetName) { CellRangeAddress titleRange = new CellRangeAddress(0, 0, 0, titleMap.size() - 1); sheet.addMergedRegion(titleRange); HSSFRow titleRow = sheet.createRow(TITLE_START_POSITION); HSSFCell titleCell = titleRow.createCell(0); titleCell.setCellValue(sheetName); } private static void createDateHeadRow(Map<String, String> titleMap) { CellRangeAddress dateRange = new CellRangeAddress(1, 1, 0, titleMap.size() - 1); sheet.addMergedRegion(dateRange); HSSFRow dateRow = sheet.createRow(DATEHEAD_START_POSITION); HSSFCell dateCell = dateRow.createCell(0); dateCell.setCellValue(new SimpleDateFormat("yyyy年MM月dd日").format(new Date())); } private static void createHeadRow(Map<String, String> titleMap) { // 第1行创建 HSSFRow headRow = sheet.createRow(HEAD_START_POSITION); headRow.setHeight((short) 900); int i = 0; for (String entry : titleMap.keySet()) { // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setAlignment(HorizontalAlignment.CENTER);//水平居中 style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 // 设置边框 style.setBorderBottom(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); style.setBorderTop(BorderStyle.THIN); // 自动换行 style.setWrapText(true); // 生成一个字体 HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); font.setColor(IndexedColors.WHITE.index); font.setBold(false); font.setFontName("宋体"); // 把字体 应用到当前样式 style.setFont(font); //style设置好后，为cell设置样式 HSSFCell headCell = headRow.createCell(i); headCell.setCellValue(titleMap.get(entry)); if (i > 14) { // 背景色 style.setFillForegroundColor(IndexedColors.BLUE.index); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFillBackgroundColor(IndexedColors.BLUE.index); } else if (i > 10) { style.setFillForegroundColor(IndexedColors.BLACK.index); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFillBackgroundColor(IndexedColors.BLACK.index); } else if (i > 7) { style.setFillForegroundColor(IndexedColors.BLUE.index); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFillBackgroundColor(IndexedColors.BLUE.index); } else if (i >4) { style.setFillForegroundColor(IndexedColors.RED.index); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFillBackgroundColor(IndexedColors.RED.index); } else { style.setFillForegroundColor(IndexedColors.GREEN.index); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFillBackgroundColor(IndexedColors.GREEN.index); } headCell.setCellStyle(style); i++; } } private static void createContentRow(List<?> dataList, Map<String, String> titleMap) { try { int i = 0; // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setAlignment(HorizontalAlignment.CENTER);//水平居中 style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 // 设置边框 style.setBorderBottom(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); style.setBorderTop(BorderStyle.THIN); // 自动换行 style.setWrapText(true); // 生成一个字体 HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 10); font.setColor(IndexedColors.BLACK.index); font.setBold(false); font.setFontName("宋体"); // 把字体 应用到当前样式 style.setFont(font); //style设置好后，为cell设置样式 for (Object obj : dataList) { HSSFRow textRow = sheet.createRow(CONTENT_START_POSITION + i); int j = 0; for (String entry : titleMap.keySet()) { //属性名驼峰式 String method = "get" + entry.substring(0, 1).toUpperCase() + entry.substring(1);// System.out.println("调用" + method + "方法"); //反射调用 Method m = obj.getClass().getMethod(method, null); Object value = m.invoke(obj, null); HSSFCell textcell = textRow.createCell(j); if (ObjectUtils.isNotEmpty(value)) { textcell.setCellValue(value.toString()); } else { textcell.setCellValue(""); } textcell.setCellStyle(style); j++; } i++; } } catch (Exception e) { e.printStackTrace(); } } private static void autoSizeColumn(Integer size) { for (int j = 0; j < size; j++) { sheet.autoSizeColumn(j); } } public static void excelExport( HttpServletResponse response, List<Perform> list, Map<String, String> titleMap, String sheetName) throws IOException { //生成表格的不可重复名 Date date = new Date(); // 初始化workbook initHSSFWorkbook(sheetName); // 表头行 createHeadRow(titleMap); // 文本行 createContentRow(list, titleMap); //输出Excel文件 OutputStream output=response.getOutputStream(); response.reset(); //设置响应头， response.setHeader("Content-disposition", "attachment; filename=teacher.xls"); response.setContentType("application/msexcel"); workbook.write(output); output.close(); }}“怎么使用Java+element实现excel导入和导出”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注编程网网站，小编将为大家输出更多高质量的实用文章！
