layui前端导入导出

2023-03-27 06:45| 来源: 网络整理| 查看: 265

方式一:layui签到导入导出layui第三方插件地址:https://fly.layui.com/jie/47273/ http://excel.wj2015.com/_book/

jsp页面:jquery-1.11.3.min.jslayui.jsexcel.js

excel导入导出 $(function () { layui.config({ base: 'layui_exts/' }).extend({ excel: 'excel' }); // LAY_EXCEL.exportExcel([[1, 2, 3]], '表格导出.xlsx', 'xlsx'); layui.use(['jquery', 'excel', 'layer'], function () { var $ = layui.jquery; var excel = layui.excel; $.ajax({ url: '/path/to/get/data', dataType: 'json', success: function (res) { // 假如返回的 res.data 是需要导出的列表数据 console.log(res.data);// [{name: 'wang', age: 18, sex: '男'}, {name: 'layui', age: 3, sex: '女'}] // 1. 数组头部新增表头 res.data.unshift({name: '用户名', sex: '男', age: '年龄'}); // 2. 如果需要调整顺序，请执行梳理函数 var data = excel.filterExportData(res.data, [ 'name', 'sex', 'age', ]); // 3. 执行导出函数，系统会弹出弹框 excel.exportExcel({ sheet1: data }, '导出接口数据.xlsx', 'xlsx'); } }); // 监听上传文件的事件 $('#LAY-excel-import-excel').change(function (e) { var files = e.target.files; try { // 方式一：先读取数据，后梳理数据 excel.importExcel(files, {}, function (data) { console.log(data); data = excel.filterImportData(data, { 'id': 'A' , 'username': 'B' , 'experience': 'C' , 'sex': 'D' , 'score': 'E' , 'city': 'F' , 'classify': 'G' , 'wealth': 'H' , 'sign': 'I' }) console.log(data); }); // 方式二：可以在读取过程中梳理数据 excel.importExcel(files, { fields: { 'id': 'A' , 'username': 'B' , 'experience': 'C' , 'sex': 'D' , 'score': 'E' , 'city': 'F' , 'classify': 'G' , 'wealth': 'H' , 'sign': 'I' } }, function (data) { console.log(data); }); } catch (e) { layer.alert(e.message); } }); }); });

方式二:poi后端导入,layui前端导出**前台jsp:jquery-1.11.3.min.jspublic.js

后台实现excel导入操作 function submit3() { // ajax里面将参数自动转换为json格式,所以后台接收必须用"@RequestBody"接收 doUploadFile("TestExcel/addExcel","myForm", function (data) { console.log(data); }); } 请选择上传的excel文件: 额外参数:

后端java:控制层

package jxxdxy.controller;import jxxdxy.model.Back;import jxxdxy.model.Result;import jxxdxy.model.TestFile;import jxxdxy.util.ExcelUtil;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.ResponseBody;import org.springframework.web.multipart.MultipartFile;import org.springframework.web.multipart.MultipartHttpServletRequest;import javax.servlet.http.HttpServletRequest;import java.io.InputStream;import java.util.List;@Controller@RequestMapping("TestExcel")public class TestExcel { @RequestMapping("addExcel") @ResponseBody public Back addFile(TestFile file, HttpServletRequest request){ System.out.println("接收的对象:"+file); System.out.println(file.getFile().getOriginalFilename()+"--"+file.getFile().getSize()); Back back = new Back(); MultipartFile excl = file.getFile(); if (request instanceof MultipartHttpServletRequest) { //说明文件不为空 if (!excl.isEmpty()) { try { String fileName = excl.getOriginalFilename(); //转化为流的形式 InputStream is = excl.getInputStream(); List list = ExcelUtil.getExcelRead(fileName, is, true); //首先是读取行也就是一行一行读，然后在取到列，遍历行里面的行，根据行得到列的值 for (Row row : list) { Cell cell_0 = row.getCell(0); System.out.println(ExcelUtil.getValue(cell_0)); //得到每个元素的值start// Cell cell_0 = row.getCell(0);// Cell cell_1 = row.getCell(1);// Cell cell_2 = row.getCell(2);// Cell cell_3 = row.getCell(3);// Cell cell_4 = row.getCell(4);// //得到列的值，也就是你需要解析的字段的值// String name = ExcelUtil.getValue(cell_0);// String nickName = ExcelUtil.getValue(cell_1);// String phone = ExcelUtil.getValue(cell_2);// String actorName = ExcelUtil.getValue(cell_3);// String status = ExcelUtil.getValue(cell_4);// String regex = "^1[3|4|5|8][0-9]\\d{8}$";// if ("".equals(name)){// result.setSuccess(false);// result.setErrorMessage("第"+(row.getRowNum())+"行第"+1+"列的姓名不能为空!");// return result;// } else if (!(phone.matches(regex))){// result.setSuccess(false);// result.setErrorMessage("第"+(row.getRowNum())+"行第" +3+ "列的手机号码格式不正确");// return result;// } else if("".equals(actorName)){// result.setSuccess(false);// result.setErrorMessage("第"+(row.getRowNum())+"行第"+5+"列的角色名不能为空!");// return result;// } else if("".equals(status)) {// result.setSuccess(false);// result.setErrorMessage("第"+(row.getRowNum())+"行第" + 6 + "列的状态不能为空!");// return result;// }else if (!("启用".equals(status)) || ("禁用".equals(status))){// result.setSuccess(false);// result.setErrorMessage("第"+(row.getRowNum())+"行第" + 6 + "列的状态只能为启用或禁用");// return result;// } } System.out.println("预览成功"); back.setCode(0); back.setMsg("表格数据预览"); } catch (Exception e) { e.printStackTrace(); System.out.println("预览出现异常"); back.setCode(0); back.setMsg("预览失败,预览出现异常"); } } else { System.out.println("预览失败,文件为空"); back.setCode(0); back.setMsg("预览失败,文件为空"); } } return back; }}

模型层

package jxxdxy.model;import org.springframework.web.multipart.MultipartFile;import java.util.Arrays;public class TestFile { private String name;//额外参数 private MultipartFile file;//上传文件 private MultipartFile[] files;//上传多个文件 @Override public String toString() { return "TestFile{" + "name='" + name + '\'' + ", file=" + file + ", files=" + Arrays.toString(files) + '}'; } public String getName() { return name; } public void setName(String name) { this.name = name; } public MultipartFile getFile() { return file; } public void setFile(MultipartFile file) { this.file = file; } public MultipartFile[] getFiles() { return files; } public void setFiles(MultipartFile[] files) { this.files = files; }}

帮助类

package jxxdxy.util;import org.apache.poi.hssf.usermodel.HSSFWorkbook;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.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.InputStream;import java.math.BigDecimal;import java.util.ArrayList;import java.util.List;/** * @author huwei */public class ExcelUtil { /** * 获取解析文件行数据 * @param fileName : 文件地址 * @param isTitle : 是否过滤第一行解析 * @return * @throws Exception */ public static List getExcelRead(String fileName, InputStream is, boolean isTitle) throws Exception{ try { //判断其兼容版本调用了判断版本的方法 Workbook workbook = getWorkbook(fileName,is); Sheet sheet = workbook.getSheetAt(0); int count = 0; List list = new ArrayList(); for (Row row : sheet) { // 跳过第一行的目录 if (count == 0 && isTitle) { count++; continue; } list.add(row); } return list; } catch (Exception e) { throw e; } }/**判断版本的方法*/ public static Workbook getWorkbook(String fileName,InputStream is) throws Exception{ Workbook workbook = null; try { /** 判断文件的类型，是2003还是2007 */ boolean isExcel2003 = true; if (WDWUtil.isExcel2007(fileName)) { isExcel2003 = false; } if (isExcel2003) { workbook = new HSSFWorkbook(is); } else { workbook = new XSSFWorkbook(is); } } catch (Exception e) { throw e; } return workbook; } /**得到celL值的方法：*/ public static String getValue(Cell cell){ if (cell == null || cell.equals("")) { return String.valueOf(""); } else { switch (cell.getCellTypeEnum()) { case BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case NUMERIC: double value = cell.getNumericCellValue(); return new BigDecimal(value).toString(); case STRING: return String.valueOf(cell.getStringCellValue()); default: return String.valueOf(cell.getStringCellValue()); } } }}

pom.xml文件

4.0.0 com.jxxdxy wlms 1.0-SNAPSHOT war wlms Maven Webapp http://www.example.com UTF-8 1.7 1.7 3.2.8.RELEASE 8.0.12 2.7 1.7.21 4.1.2 1.7 maven2-release oss https://oss.sonatype.org/content/repositories/snapshots/ oss https://oss.sonatype.org/service/local/staging/deploy/maven2/ com.yunpian.sdk yunpian-java-sdk 1.2.7 com.alipay.sdk alipay-sdk-java 3.7.4.ALL org.slf4j slf4j-api ${slf4j.version} org.slf4j slf4j-simple ${slf4j.version} test org.slf4j slf4j-nop 1.7.2 com.google.code.gson gson 2.8.0 org.apache.httpcomponents httpasyncclient ${httpasyncclient.version} org.apache.httpcomponents httpasyncclient-cache ${httpasyncclient.version} org.apache.httpcomponents httpmime 4.5.2 com.alibaba druid 1.0.9 org.aspectj aspectjweaver 1.8.8 log4j log4j 1.2.17 org.codehaus.xfire xfire-spring 1.2.6 org.springframework spring org.apache.poi poi-ooxml 3.17 org.apache.poi poi 3.17 org.apache.poi poi-ooxml-schemas 3.17 junit junit 4.11 test commons-dbcp commons-dbcp 1.4 commons-pool commons-pool 1.6 org.springframework spring-core ${spring-version} org.springframework spring-test ${spring-version} org.springframework spring-context ${spring-version} org.springframework spring-tx ${spring-version} org.springframework spring-context-support ${spring-version} org.springframework spring-jdbc ${spring-version} org.springframework spring-aop ${spring-version} org.springframework spring-beans ${spring-version} org.springframework spring-webmvc ${spring-version} org.springframework spring-aspects ${spring-version} org.springframework spring-jms ${spring-version} org.mybatis mybatis 3.3.0 org.mybatis mybatis-spring 1.2.2 mysql mysql-connector-java ${jdbc.version} com.alibaba fastjson 1.2.31 org.apache.logging.log4j log4j-core ${log4j.version} commons-fileupload commons-fileupload 1.3.2 commons-io commons-io 2.5 javax.servlet servlet-api 2.5 org.json json 20160810 src/main/java **/*.properties **/*.xml src/main/resources **/*.properties **/*.xml org.apache.maven.plugins maven-compiler-plugin 3.6.0 ${jdk.version} ${jdk.version} ${project.build.sourceEncoding} org.apache.maven.plugins maven-resources-plugin 3.0.1 compile ${project.build.sourceEncoding} org.apache.maven.plugins maven-surefire-plugin 2.19.1 true maven-assembly-plugin package single jar-with-dependencies wlms maven-clean-plugin 3.0.0 maven-resources-plugin 3.0.2 maven-compiler-plugin 3.7.0 maven-surefire-plugin 2.20.1 maven-war-plugin 3.2.0 maven-install-plugin 2.5.2 maven-deploy-plugin 2.8.2

【本文地址】

layui前端导入导出

layui前端导入导出

今日新闻

推荐新闻