导出_导出excel - CSDN
精华内容
参与话题
  • java导出excel的两种方式

    万次阅读 多人点赞 2019-04-05 10:24:31
    一、在后台实现,利用java的poi 1、导入jar包,需要导入lib文件夹下如下包: poi-3.11-20141221.jar poi-ooxml.jar poi-ooxml-schemas.jar 2、在util下写一个公共类,该类主要利用Jakarta POI HSSF API组件(用于...

    在这里插入图片描述
    一、在后台实现,利用java的poi
    1、导入jar包,需要导入lib文件夹下如下包:
    poi-3.11-20141221.jar
    poi-ooxml.jar
    poi-ooxml-schemas.jar
    2、在util下写一个公共类,该类主要利用Jakarta POI HSSF API组件(用于操作Excel的组件),主要部分包括Excel对象,样式和格式,还有辅助操作。

     常用组件:
        HSSFWorkbook        excel的文档对象
        HSSFSheet           excel的表单
        HSSFRow             excel的行
        HSSFCell            excel的格子单元
        HSSFFont            excel字体
        HSSFDataFormat      日期格式
        HSSFHeader         sheet头
        HSSFFooter         sheet尾(只有打印的时候才能看到效果)
    
        样式:
        HSSFCellStyle                       cell样式
        辅助操作包括:
        HSSFDateUtil                        日期
        HSSFPrintSetup                      打印
        HSSFErrorConstants                  错误信息表
    

    3、该类的操作代码如下:

    package com.bdqn.util;
    
    import java.io.OutputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFRichTextString;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.util.CellRangeAddress;
    
    public class ExportExcel {
    
      // 显示的导出表的标题
      private String title;
      // 导出表的列名
      private String[] rowName;
      private List<Object[]> dataList = new ArrayList<Object[]>();
    
      // 构造函数,传入要导出的数据
      public ExportExcel(String title, String[] rowName, List<Object[]> dataList) {
        this.dataList = dataList;
        this.rowName = rowName;
        this.title = title;
      }
    
      // 导出数据
      public void export(OutputStream out) throws Exception {
        try {
          HSSFWorkbook workbook = new HSSFWorkbook();
          HSSFSheet sheet = workbook.createSheet(title);
    
          // 产生表格标题行
          HSSFRow rowm = sheet.createRow(0);
          HSSFCell cellTitle = rowm.createCell(0);
          
          
          //sheet样式定义【】
          HSSFCellStyle columnTopStyle=this.getColumnTopStyle(workbook);
          HSSFCellStyle style=this.getStyle(workbook);
          sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length - 1)));
          cellTitle.setCellStyle(columnTopStyle);
          cellTitle.setCellValue(title);
    
          // 定义所需列数
          int columnNum = rowName.length;
          HSSFRow rowRowName = sheet.createRow(2);
    
          // 将列头设置到sheet的单元格中
          for (int n = 0; n < columnNum; n++) {
            HSSFCell cellRowName = rowRowName.createCell(n);
            cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
            HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
            cellRowName.setCellValue(text);
            cellRowName.setCellStyle(columnTopStyle);
    
          }
          // 将查询到的数据设置到sheet对应的单元格中
          for (int i = 0; i < dataList.size(); i++) {
            Object[] obj = dataList.get(i);// 遍历每个对象
            HSSFRow row = sheet.createRow(i + 3);// 创建所需的行数
    
            for (int j = 0; j < obj.length; j++) {
              HSSFCell cell = null;
              if (j == 0) {
                cell = row.createCell(j, HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(i + 1);
              } else {
                cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                if (!"".equals(obj[j]) && obj[j] != null) {
                  cell.setCellValue(obj[j].toString());
                }
              }
              cell.setCellStyle(style);
    
            }
    
          }
    
          // 让列宽随着导出的列长自动适应
          for (int colNum = 0; colNum < columnNum; colNum++) {
            int columnWidth = sheet.getColumnWidth(colNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
              HSSFRow currentRow;
              if (sheet.getRow(rowNum) == null) {
                currentRow = sheet.createRow(rowNum);
              } else {
                currentRow = sheet.getRow(rowNum);
              }
              if (currentRow.getCell(colNum) != null) {
                HSSFCell currentCell = currentRow.getCell(colNum);
                if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                  int length = currentCell.getStringCellValue().getBytes().length;
                  if (columnWidth < length) {
                    columnWidth = length;
                  }
                }
              }
            }
            if (colNum == 0) {
              sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
            } else {
              sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
            }
          }
    
          if (workbook != null) {
            try {
    
              workbook.write(out);
    
            } catch (Exception e) {
              e.printStackTrace();
            }
          }
    
        } catch (Exception e) {
    
        }
      }
    

    4、单元格样式:

    /*
       * 列头单元格样式
       */
      public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
    
        // 设置字体大小
        font.setFontHeightInPoints((short) 11);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置低边框
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置低边框颜色
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置右边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框颜色
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式中应用设置的字体
        style.setFont(font);
        // 设置自动换行
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    
      }
    
      public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 10);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 在样式用应用设置的字体;
        style.setFont(font);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
      }
    

    5、前台主要代码如下:
    5.1 第一种,我们没有传入页面的查询参数,到后台我们无论怎么查询,都是把整个数据库不分条件的全部导出。不能够满足用户需求,体验不好,不推荐。

      <button type="button" class="btn btn-success" id="delPro" "dao()">导出Excel</button>
    function dao() {
      location.href="MedicineAdminServlet?a=dao&pageon="+${page.pageon};
    }
    

    5、2第二种,我们往后台传入时,把查询的参数一并传入后台,后台接收后作为条件去数据库找符合条件的,查到符合条件的导出,比较人性化。

    <a href="SendMedicineServlet?a=dao&patientid=${param.patientid }&prescriptionid=${param.prescriptionid}&patientname=${param.patientname }&starttime=${param.starttime }&endtime=${param.endtime }">
              <button type="button" class="btn btn-success" id="delPro">导出Excel</button>
              </a>
    

    6、为了方便读者理解,前端页面如下:查询条件为病历号,姓名等,会根据曾经的查询条件导出。

    在这里插入图片描述

    7、后台servlet操作主要代码如下:(传入当前页将会只打印当前页)

    /**
         * 导出excel文件
         */
    
    else if (a.equals("dao")) {
      String patientid = request.getParameter("patientid");
      int patientid3 = 0;
      if (patientid != null && !patientid.equals("")) {
        patientid3 = Integer.parseInt(patientid);
      }
    
      String dname = request.getParameter("dname");
      String subjectroom = request.getParameter("subjectroom");
      String starttime = request.getParameter("starttime");
      String endtime = request.getParameter("endtime");
    
      // 页码
      int pageon = 1;
      String pageon1 = request.getParameter("pageon");
      if (pageon1 != null && !pageon1.equals("")) {
        pageon = Integer.parseInt(pageon1);
      }
      // 操作导出excel
      List<RegRum> selectlists = rs.querySelect2(patientid3, dname, subjectroom, starttime, endtime);
     //excel标题
      String title = "挂号信息表";
      //excel列头信息
      String[] rowsName = new String[] { "门诊编号", "主治医师", "挂号时间", "挂号科室", "状态" };
      List<Object[]> dataList = new ArrayList<Object[]>();
      Object[] objs = null;
      for (int i = 0; i < selectlists.size(); i++) {
        RegRum regRum = selectlists.get(i);
        objs = new Object[rowsName.length];
        objs[0] = regRum.getPatientid();
        objs[1] = regRum.getDoctor().getDname();
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        objs[2] = regRum.getDate();
        objs[3] = regRum.getDoctor().getSubroomname();
        objs[4] = regRum.getStatus();
        dataList.add(objs);
    
      }
    

    8、下面这部分代码主要是把数据传给浏览器,前面部分告诉浏览器该数据流是什么类型的,本例传的是excel格式的,浏览器会自动判定为excel,提示是否保存。

    //给文件命名。随机命名
    String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
         //告诉浏览器数据格式,将头和数据传到前台
          String headStr = "attachment; filename=\"" + fileName + "\"";
          response.setContentType("APPLICATION/OCTET-STREAM");
          response.setHeader("Content-Disposition", headStr);
          OutputStream out = response.getOutputStream();
    
    //调用poi的工具类
          ExportExcel ex = new ExportExcel(title, rowsName, dataList);
          try {
            ex.export(out);
          } catch (Exception e) {
            e.printStackTrace();
          }
          out.flush();
          out.close();
          return;
        }
    

    9、注意:如果从数据库查到的是数字,比如0代表男,1代表女,不加处理,会导出数字,处理方法如下:(主要代码),下面代码又没有的当前页的限制,根据条件查到多少打印多少。

    String patientid1 = request.getParameter("patientid");
          int patientid = 0;
          if (patientid1 != null && !patientid1.equals("")) {
            patientid = Integer.parseInt(patientid1);
          }
          String prescriptionid1 = request.getParameter("prescriptionid");
          int prescriptionid = 0;
          if (prescriptionid1 != null && !prescriptionid1.equals("")) {
            prescriptionid = Integer.parseInt(prescriptionid1);
          }
          String patientname = request.getParameter("patientname");
          String starttime = request.getParameter("starttime");
          String endtime = request.getParameter("endtime");
          List<Prescription> prescriptions = new ArrayList<Prescription>();
          prescriptions = sm.queryDao(prescriptionid, patientid, patientname, starttime, endtime);
          String title = "发药信息表";
          String[] rowsName = new String[] { "处方号", "病历号", "姓名", "日期", "状态" };
          List<Object[]> dataList = new ArrayList<Object[]>();
          //导出excel
          Object[] objs = null;
          for (int i = 0; i < prescriptions.size(); i++) {
            Prescription regRum = prescriptions.get(i);
            objs = new Object[rowsName.length];
            objs[0] = regRum.getPrescriptionid();
            objs[1] = regRum.getPatientid();
            objs[2] = regRum.getName();
            objs[3] = regRum.getPrescriptiondate();
            //对数字的操作
            if(regRum.getStatus().equals("1")) {
              objs[4] = "已结算";
            }
            else {
              objs[4]="已发药";
            }
            dataList.add(objs);
          }
          //下面代码主要跟上面一致
    

    10.数据库操作代码如下:(比较简单,只是把满足条件的list集合传到servlet,转变为数组,方便调用poi类)

    @Override
      public List<Prescription> query(int prescriptionid,int patientid, String patientname, String starttime, String endtime, int pageon) {
        Connection con = Jndi.getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sql = "select p.patientid,p.prescriptionid,r.patientname, p.diagnose ,p.prescriptiondate,sum(chargemoney*number),p.`pstatus` \r\n"
            + "from prescription p,regnum r,chargeitem c,prescriptioncharge pc  \r\n" + "\r\n"
            + " where  p.patientid=r.patientid and pc.prescriptionid=p.prescriptionid\r\n" + "\r\n" + "\r\n"
            + " and c.chargeid=pc.chargeid   ";
        if (patientid != 0) {
          sql = sql + " and p.patientid=" + patientid;
        }
        if (prescriptionid != 0) {
          sql = sql + " and p.prescriptionid=" + prescriptionid;
        }
        if (patientname != null && !patientname.equals("")) {
          sql = sql + " and r.patientname like '%" + patientname + "%'";
        }
        if (starttime != null && !starttime.equals("")) {
          sql = sql + " and p.prescriptiondate>'" + starttime + "'";
        }
        if (endtime != null && !endtime.equals("")) {
          sql = sql + " and p.prescriptiondate<'" + endtime + "'";
        }
        
    
    sql = sql + "  GROUP BY p.prescriptionid";
    if (pageon != 0) {
      sql = sql + " limit " + (pageon - 1) * 4 + ",4";
    }
    
    List<Prescription> listregs = new ArrayList<Prescription>();
    try {
      ps = con.prepareStatement(sql);
      rs = ps.executeQuery();
    
      while (rs.next()) {
        Prescription prescription = new Prescription();
        prescription.setPatientid(rs.getInt("patientid"));
        prescription.setPrescriptionid(rs.getInt("p.prescriptionid"));
        prescription.setDiagnose(rs.getString("diagnose"));
        prescription.setPrescriptiondate(rs.getString("prescriptiondate"));
        prescription.setStatus(rs.getString("pstatus"));
        prescription.setSummoney(rs.getDouble(6));
        prescription.setName(rs.getString(3));
        listregs.add(prescription);
      }
    
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      Jndi.close(rs, ps, con);
    }
    return listregs;
    

    第一种比较复杂,如果想很快搞定,可以用纯js在前端导出table表格,需要导入四个js文件。引入进去。

    在这里插入图片描述
    二、纯js实现前台导出excel。
    1、导入js文件,可能也需要导入jquery文件,自行尝试。所需js文件地址。
    链接:https://pan.baidu.com/s/14-riXUTElxWaLHRHLe04SA
    提取码:fpnp

    <script type="text/javascript" src="<%=path %>/Js/jszip.min.js"></script>
    <script type="text/javascript" src="<%=path %>/Js/demo.page.js"></script>
    <script type="text/javascript" src="<%=path %>/Js/excel-gen.js"></script>
    <script type="text/javascript" src="<%=path %>/Js/FileSaver.js"></script>
    

    2、js代码如下

    <script type="text/javascript">
      $(document).ready(function(){
        alert()
        excel = new ExcelGen({
          "src_id":"test_table",//table的id
          "show_header":true
        });
        $("#generate-excel").click(function () {
          excel.generate();//执行导入包中的方法。
        })
      });
    </script>
    

    3、body中主要代码

    <table class="table table-bordered table-hover definewidth m10" id="test_table">
    //id在table中写
    //调用方法名在button写
    <button type="button" class="btn btn-success" id="generate-excel">导出Excel</button>
    

    注意:第二种方式,只能导出当前页的信息,它是根据table里面tr的数量导的,不会打印下一页。如果希望打印下一页的内容,提供一种思路,可以在按钮上添加跳转页面,跳到另一个页面,让另一个页面返回数据库全查需要的数据,写入新建页的table中,打印新table,有兴趣的可以尝试。 到这儿,可能读累了吧,听懂的给个赞,thanks.

    在这里插入图片描述

    展开全文
  • 导出数据到excel文件

    2020-03-02 11:20:48
    1、绑定事件,参数为模糊查询中使用ng-model绑定的数据 ...// 导出excel 全部 exportHrSalaryZZ $scope.exportDatas = function(searchWhere) { $http( { url : Constants.API.INTERFACE_URL + “a/xf4g/xf...

    1、绑定事件,参数为模糊查询中使用ng-model绑定的数据

    <a class="button_type1 addTabPage" title="导出"
    				ng-click="exportDatas(searchWhere)"><i class="iconfont">&#xe628;</i>导出</a>
    

    2、使用$scope.参数 = {键:值,键:值,键:值···}
    $scope.searchWhere = {
    name : “”,
    loginName : “”,
    passWord : “”,
    teamName : “”,
    schoolName : “”,
    dyfield5 : “”,
    loginName : “”,
    projectType : “”
    };
    3、

    // 导出excel  全部  exportHrSalaryZZ
    	$scope.exportDatas = function(searchWhere) {
    		$http(
    				{
    					url : Constants.API.INTERFACE_URL
    							+ "a/xf4g/xfScore/exportXfScoreData",
    					method : "POST",
    					data : {
    						examName:searchWhere.examName,//绑定参数
    						projectType:searchWhere.projectType,
    			    		teamName:searchWhere.teamName
    					},
    					headers : {//定义类型
    						'Content-type' : 'application/json'
    					},//响应类型
    					responseType : 'arraybuffer'
    				})
    				.success(
    						function(data, status, headers,
    								config) {
    							var blob = new Blob(
    									[ data ],
    									{
    										type : "application/vnd.ms-excel"
    									});
    							var objectUrl = URL
    									.createObjectURL(blob);
    							var a = document
    									.createElement('a');
    							document.body.appendChild(a);
    							a.setAttribute('style',
    									'display:none');
    							a.setAttribute('href',
    									objectUrl);
    							var filename = "5G技术及应用大赛评分汇总表.xlsx";//导出的文件名
    							a.setAttribute('download',//绑定属性
    									filename);
    							a.click();
    							URL.revokeObjectURL(objectUrl);
    						}).error(
    						function(data, status, headers,
    								config) {
    						});
    	}
    

    3.1
    @CrossOrigin(origins = “*”, maxAge = 3600)
    @RequestMapping(value = “exportXfUserData”, method = RequestMethod.POST)
    @ResponseBody
    public String exportXfUserData(@RequestBody XfUser xfUser, HttpServletRequest request, HttpServletResponse response,
    RedirectAttributes redirectAttributes) throws IOException {
    Page page = new Page();
    // 设置导出的名字
    String fileName = “5G技术及应用大赛用户汇总表” + DateUtils.getDate(“yyyyMMddHHmmss”) + “.xlsx”;
    // 合同管理列表信息
    List list = xfUserService.exportXfUserData(xfUser);
    page.setList(list);
    new ExportExcel(“5G技术及应用大赛用户汇总表”, XfUser.class).setDataList(page.getList()).write(response, fileName).dispose();
    return “5G技术及应用大赛用户汇总表!”;
    }
    4、写SQL查询语句
    5.在实体类中需要写入excel的字段的get方法上添加注解@ExcelField(title = “字段标题”, align = 2, sort = 列号)
    String title():导出字段标题(需要添加批注请用“”分隔,标题批注,仅对导出模板有效)
    String value() default “”:默认调用当前字段的“get”方法,如指定导出字段为对象,请填写“对象名.对象属性”,例:“area.name”、“office.name”
    int type() default 0:字段类型:0:导出导入;1:仅导出;2:仅导入
    int align() default 0;:字段对齐方式:0:自动;1:靠左;2:居中;3:靠右
    int sort() default 0:导出字段字段排序(升序)
    String dictType() default “”:如果是字典类型,请设置字典的type值
    Class<?> fieldType() default Class.class:反射类型
    int[] groups() default {}:字段归属组(根据分组导出导入)

    展开全文
  • excel 导出

    2019-01-29 14:58:24
    excel 导出,在项目使用中十分广泛,本文讲解如何导出 excel 本文使用 springboot + mybatis 框架 yml 配置文件如下 server: port: 9001 spring: datasource: url: jdbc:mysql://105.42.21.92:3306/orcl?...

    excel 导出,在项目使用中十分广泛,本文讲解如何导出 excel

    本文使用 springboot + mybatis 框架

    yml 配置文件如下

    server:
      port:  9001
      
    spring:
      datasource:
        url:  jdbc:mysql://105.42.21.92:3306/orcl?useUnicode=true&characterEncoding=utf-8&useSSL=false
        driver-class-name:  com.mysql.jdbc.Driver  
        username:  root
        password:  123456 
        
    mybatis:
      mapper-locations:  classpath:mapper/*Mapper.xml
      config-location:  classpath:mapper/config/sqlMapConfig.xml     

     

    1、首先在项目中引入 poi jar包,本文使用 poi 解析2003以下版本

    <!--poi对excel2003以下版本的支持-->  
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
    </dependency>

    2、数据库表结构及表中数据如下

    建表及添加数据 sql 语句

    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for user
    -- ----------------------------
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user` (
      `user_id` double DEFAULT NULL,
      `user_name` varchar(64) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `user_addr` varchar(64) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of user
    -- ----------------------------
    INSERT INTO `user` VALUES ('1', '贾宝玉', '16', '怡红院');
    INSERT INTO `user` VALUES ('2', '林黛玉', '16', '潇湘馆');
    INSERT INTO `user` VALUES ('3', '薛宝钗', '17', '衡芜院');
    INSERT INTO `user` VALUES ('4', '妙玉', '18', '栊翠庵');
    INSERT INTO `user` VALUES ('5', '史湘云', '16', '藕香榭');
    INSERT INTO `user` VALUES ('1', '唐三藏', '26', '长安');
    INSERT INTO `user` VALUES ('2', '孙悟空', '526', '花果山');
    INSERT INTO `user` VALUES ('3', '猪八戒', '700', '高老庄');
    INSERT INTO `user` VALUES ('4', '沙悟净', '800', '流沙河');
    INSERT INTO `user` VALUES ('5', '黑熊精', '1000', '黑风洞');
    INSERT INTO `user` VALUES ('6', '金角', null, '金山');
    INSERT INTO `user` VALUES ('7', null, '1000', '金山');

     

    3、实体类如下

    package com.demo.bean;
    
    public class User {
    	
    	private double userId;
    	
    	private String userName;
    	
    	private Integer age;
    	
    	private String userAddr;
    
    	public double getUserId() {
    		return userId;
    	}
    
    	public void setUserId(double userId) {
    		this.userId = userId;
    	}
    
    	public String getUserName() {
    		return userName;
    	}
    
    	public void setUserName(String userName) {
    		this.userName = userName;
    	}
    
    	public Integer getAge() {
    		return age;
    	}
    
    	public void setAge(Integer age) {
    		this.age = age;
    	}
    
    	public String getUserAddr() {
    		return userAddr;
    	}
    
    	public void setUserAddr(String userAddr) {
    		this.userAddr = userAddr;
    	}
    
    	@Override
    	public String toString() {
    		return "User [userId=" + userId + ", userName=" + userName + ", age=" + age + ", userAddr=" + userAddr + "]";
    	}
    }
    

    4、mapper 层如下

    package com.demo.mapper;
    
    import java.util.List;
    import org.apache.ibatis.annotations.Mapper;
    import com.demo.bean.User;
    
    @Mapper
    public interface UserMapper {
    	
    	//批量将数据添加到数据库
    	int insertForeach(List<User> list);
    	
    	List<User> getAllUserList();
    
    }
    

    5、mapper.xml 文件如下

    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
    <mapper namespace="com.demo.mapper.UserMapper">
    
        <insert id="insertForeach" parameterType="java.util.List" useGeneratedKeys="false">
            insert into user ( user_id, user_name, age, user_addr) values
            <foreach collection="list" item="item" index="index" separator=",">
                (#{item.userId}, #{item.userName}, #{item.age}, #{item.userAddr})
            </foreach>		
        </insert>   
        
        <resultMap id="ResultMap" type="com.demo.bean.User">
            <id column="user_id" property="userId" jdbcType="DOUBLE" />
            <result column="user_name" property="userName" jdbcType="VARCHAR" />
            <result column="age" property="age" jdbcType="INTEGER" />
            <result column="user_addr" property="userAddr" jdbcType="VARCHAR" />
        </resultMap>
        
        <select id="getAllUserList" resultMap="ResultMap">
            select * from user
        </select> 
    </mapper>
    

    6、controller 层代码如下

    package com.demo.controller;
    
    import java.io.IOException;
    import java.io.UnsupportedEncodingException;
    import java.net.URLEncoder;
    import java.util.List;
    import javax.servlet.http.HttpServletResponse;
    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.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.servlet.ModelAndView;
    import com.demo.bean.User;
    import com.demo.mapper.UserMapper;
    
    @Controller
    public class ExcelExportController {
    	
    	@Autowired
    	private UserMapper userMapper;
    	
    	@RequestMapping("/export")
    	public ModelAndView exportIndex() {
    		ModelAndView mav = new ModelAndView();
    		mav.setViewName("export");
    		return mav;
    	}
    	
    	@RequestMapping("/exportExcel")
    	public void exportExcel(String fileName, HttpServletResponse response) {
    		List<User> list = userMapper.getAllUserList();
    		
    		Workbook wb = new HSSFWorkbook();  //定义一个新的工作簿
    		Sheet sheet = wb.createSheet("sheet的名字");  //设置sheet名称
    		
    		//设置表头
    		Row header = sheet.createRow(0);
    		for(int k=0; k<4; k++) {
    			Cell cell = header.createCell(k);
    			if(k == 0) {
    				cell.setCellValue("ID");
    			}
    			if(k == 1) {
    				cell.setCellValue("姓名");
    			}
    			if(k == 2) {
    				cell.setCellValue("年龄");
    			}
    			if(k == 3) {
    				cell.setCellValue("地址");
    			}
    		}
    		
    		
    		for(int i=0; i<list.size(); i++) {
    			Row row = sheet.createRow(i+1);  //i+1 是从表头的下一行开始
    			
    			for(int j=0; j<4; j++) {
    				Cell cell = row.createCell(j);
    				
    				if(j == 0) {
    					cell.setCellValue(list.get(i).getUserId());
    				}
    				if(j == 1) {
    					//判断 list.get(i).getUserName() 为空的情况
    					if("".equals(list.get(i).getUserName())) {
    						//如果为空,设置默认值
    						cell.setCellValue("");
    					}else {
    						cell.setCellValue(list.get(i).getUserName());
    					}
    				}
    				if(j == 2) {
    					//判断 list.get(i).getAge() 为空的情况
    					if(null == list.get(i).getAge()) {
    						//如果为空,设置默认值
    						cell.setCellValue(0);
    					}else {
    						cell.setCellValue(list.get(i).getAge());
    					}
    					
    				}
    				if(j == 3) {
    					cell.setCellValue(list.get(i).getUserAddr());
    				}
    			}
    		}
    		
    		String webFileName;
    		try {
    			webFileName = URLEncoder.encode(fileName, "UTF-8");//解决浏览器下载中文乱码问题
    			response.setContentType("application/octet-stream");
    		    response.setHeader("Content-disposition", "attachment;filename="+webFileName+".xls");//Excel文件名
    			
    		} catch (UnsupportedEncodingException e1) {
    			e1.printStackTrace();
    		}
    		
    	    try {
    			wb.write(response.getOutputStream());
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    	}
    }
    

    7、创建 export.ftl 页面文件

    <!DOCTYPE html>
    <html>
    <head>
    <meta charset="UTF-8">
    <title>excel导出</title>
    </head>
    <body>
    
    	<form action="/exportExcel">
    	     <input type="text" name="fileName" />
    	     <input type="submit" value="下载" />
    	</form>
         
    </body>
    </html>

    8、测试

    浏览器访问  http://localhost:9001/export

    随意输入 excel 的文件名,点击下载

    打开 excel 文件

     

     

     

    如果要解析 excel2007以上版本示例如下

    先引入 jar 包

    <!--poi对excel2007以上版本的支持-->  
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.17</version>
    </dependency>

    controller 层代码如下

    package com.demo.controller;
    
    import java.io.IOException;
    import java.io.UnsupportedEncodingException;
    import java.net.URLEncoder;
    import java.util.List;
    import javax.servlet.http.HttpServletResponse;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.servlet.ModelAndView;
    import com.demo.bean.User;
    import com.demo.mapper.UserMapper;
    
    @Controller
    public class Excel2007Controller {
    	
    	@Autowired
    	private UserMapper userMapper;
    	
    	@RequestMapping("/excel2007")
    	public ModelAndView excel2007() {
    		ModelAndView mav = new ModelAndView();
    		mav.setViewName("excel2007");
    		return mav;
    	}
    	
    	@RequestMapping("/exportExcel2007")
    	public void exportExcel2007(String fileName, HttpServletResponse response) {
    		List<User> list = userMapper.getAllUserList();
    		
    		XSSFWorkbook xwb = new XSSFWorkbook();
    		XSSFSheet xsheet = xwb.createSheet("自定义sheet名称");
    		
    		XSSFRow xheader = xsheet.createRow(0);
    		for(int i=0; i<4; i++) {
    			XSSFCell xcell = xheader.createCell(i);
    			if(i == 0) {
    				xcell.setCellValue("ID");
    			}
    			if(i == 1) {
    				xcell.setCellValue("姓名");
    			}
    			if(i == 2) {
    				xcell.setCellValue("年龄");
    			}
    			if(i == 3) {
    				xcell.setCellValue("地址");
    			}
    		}
    		
    		for(int i=0; i<list.size(); i++) {
    			XSSFRow xrow = xsheet.createRow(i+1);
    			
    			
    			for(int j=0; j<4; j++) {
    				XSSFCell xcell = xrow.createCell(j);
    				
    				if(j == 0) {
    					xcell.setCellValue(list.get(i).getUserId());
    				}
    				
    				if(j == 1) {
    					//判断 list.get(i).getUserName() 为空的情况
    					if("".equals(list.get(i).getUserName())) {
    						//如果为空,设置默认值
    						xcell.setCellValue("");
    					}else {
    						xcell.setCellValue(list.get(i).getUserName());
    					}
    				}
    				
    				if(j == 2) {
    					//判断 list.get(i).getAge() 为空的情况
    					if(null == list.get(i).getAge()) {
    						//如果为空,设置默认值
    						xcell.setCellValue(0);
    					}else {
    						xcell.setCellValue(list.get(i).getAge());
    					}
    				}
    				
    				if(j == 3) {
    					xcell.setCellValue(list.get(i).getUserAddr());
    				}
    			}
    		}
    		
    		String webFileName;
    		try {
    			webFileName = URLEncoder.encode(fileName, "UTF-8");//解决浏览器下载中文乱码问题
    			response.setContentType("application/octet-stream");
    		    response.setHeader("Content-disposition", "attachment;filename="+webFileName+".xlsx");//Excel文件名
    			
    		} catch (UnsupportedEncodingException e1) {
    			e1.printStackTrace();
    		}
    		
    	    try {
    			xwb.write(response.getOutputStream());
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    		
    	}
    
    }
    

    ftl 页面如下

    <!DOCTYPE html>
    <html>
    <head>
    <meta charset="UTF-8">
    <title>excel2007导出</title>
    </head>
    <body>
    	 <form action="/exportExcel2007">
    	     <input type="text" name="fileName" />
    	     <input type="submit" value="下载" />
    	 </form>
    </body>
    </html>

     

    展开全文
  • 1.导入问题cnpm install --save xlsx-style import XLSX from "xlsx-style"报错:This relative module was not found: ./cptable in ./node_modules/xlsx-style@0.8.13@xlsx-style/dist/cpexcel.js ...

    1.导入

    npm install file-saver -S 
    npm install xlsx -S
    npm install -D script-loader
    

    (如果后续有问题提示Can‘t resolve ‘***‘ in,请移步点我一下
    2.在一个空白文件夹下创建两个文件
    在这里插入图片描述
    Blob.js

    /* eslint-disable */
    /* Blob.js*/
     
    /*global self, unescape */
    /*jslint bitwise: true, regexp: true, confusion: true, es5: true, vars: true, white: true,
      plusplus: true */
     
    /*! @source http://purl.eligrey.com/github/Blob.js/blob/master/Blob.js */
     
    (function (view) {
        "use strict";
     
        view.URL = view.URL || view.webkitURL;
     
        if (view.Blob && view.URL) {
            try {
                new Blob;
                return;
            } catch (e) {
            }
        }
     
        // Internally we use a BlobBuilder implementation to base Blob off of
        // in order to support older browsers that only have BlobBuilder
        var BlobBuilder = view.BlobBuilder || view.WebKitBlobBuilder || view.MozBlobBuilder || (function (view) {
            var
                get_class = function (object) {
                    return Object.prototype.toString.call(object).match(/^\[object\s(.*)\]$/)[1];
                }
                , FakeBlobBuilder = function BlobBuilder() {
                    this.data = [];
                }
                , FakeBlob = function Blob(data, type, encoding) {
                    this.data = data;
                    this.size = data.length;
                    this.type = type;
                    this.encoding = encoding;
                }
                , FBB_proto = FakeBlobBuilder.prototype
                , FB_proto = FakeBlob.prototype
                , FileReaderSync = view.FileReaderSync
                , FileException = function (type) {
                    this.code = this[this.name = type];
                }
                , file_ex_codes = (
                    "NOT_FOUND_ERR SECURITY_ERR ABORT_ERR NOT_READABLE_ERR ENCODING_ERR "
                    + "NO_MODIFICATION_ALLOWED_ERR INVALID_STATE_ERR SYNTAX_ERR"
                ).split(" ")
                , file_ex_code = file_ex_codes.length
                , real_URL = view.URL || view.webkitURL || view
                , real_create_object_URL = real_URL.createObjectURL
                , real_revoke_object_URL = real_URL.revokeObjectURL
                , URL = real_URL
                , btoa = view.btoa
                , atob = view.atob
     
                , ArrayBuffer = view.ArrayBuffer
                , Uint8Array = view.Uint8Array
     
                , origin = /^[\w-]+:\/*\[?[\w\.:-]+\]?(?::[0-9]+)?/
            ;
            FakeBlob.fake = FB_proto.fake = true;
            while (file_ex_code--) {
                FileException.prototype[file_ex_codes[file_ex_code]] = file_ex_code + 1;
            }
            // Polyfill URL
            if (!real_URL.createObjectURL) {
                URL = view.URL = function (uri) {
                    var
                        uri_info = document.createElementNS("http://www.w3.org/1999/xhtml", "a")
                        , uri_origin
                    ;
                    uri_info.href = uri;
                    if (!("origin" in uri_info)) {
                        if (uri_info.protocol.toLowerCase() === "data:") {
                            uri_info.origin = null;
                        } else {
                            uri_origin = uri.match(origin);
                            uri_info.origin = uri_origin && uri_origin[1];
                        }
                    }
                    return uri_info;
                };
            }
            URL.createObjectURL = function (blob) {
                var
                    type = blob.type
                    , data_URI_header
                ;
                if (type === null) {
                    type = "application/octet-stream";
                }
                if (blob instanceof FakeBlob) {
                    data_URI_header = "data:" + type;
                    if (blob.encoding === "base64") {
                        return data_URI_header + ";base64," + blob.data;
                    } else if (blob.encoding === "URI") {
                        return data_URI_header + "," + decodeURIComponent(blob.data);
                    }
                    if (btoa) {
                        return data_URI_header + ";base64," + btoa(blob.data);
                    } else {
                        return data_URI_header + "," + encodeURIComponent(blob.data);
                    }
                } else if (real_create_object_URL) {
                    return real_create_object_URL.call(real_URL, blob);
                }
            };
            URL.revokeObjectURL = function (object_URL) {
                if (object_URL.substring(0, 5) !== "data:" && real_revoke_object_URL) {
                    real_revoke_object_URL.call(real_URL, object_URL);
                }
            };
            FBB_proto.append = function (data/*, endings*/) {
                var bb = this.data;
                // decode data to a binary string
                if (Uint8Array && (data instanceof ArrayBuffer || data instanceof Uint8Array)) {
                    var
                        str = ""
                        , buf = new Uint8Array(data)
                        , i = 0
                        , buf_len = buf.length
                    ;
                    for (; i < buf_len; i++) {
                        str += String.fromCharCode(buf[i]);
                    }
                    bb.push(str);
                } else if (get_class(data) === "Blob" || get_class(data) === "File") {
                    if (FileReaderSync) {
                        var fr = new FileReaderSync;
                        bb.push(fr.readAsBinaryString(data));
                    } else {
                        // async FileReader won't work as BlobBuilder is sync
                        throw new FileException("NOT_READABLE_ERR");
                    }
                } else if (data instanceof FakeBlob) {
                    if (data.encoding === "base64" && atob) {
                        bb.push(atob(data.data));
                    } else if (data.encoding === "URI") {
                        bb.push(decodeURIComponent(data.data));
                    } else if (data.encoding === "raw") {
                        bb.push(data.data);
                    }
                } else {
                    if (typeof data !== "string") {
                        data += ""; // convert unsupported types to strings
                    }
                    // decode UTF-16 to binary string
                    bb.push(unescape(encodeURIComponent(data)));
                }
            };
            FBB_proto.getBlob = function (type) {
                if (!arguments.length) {
                    type = null;
                }
                return new FakeBlob(this.data.join(""), type, "raw");
            };
            FBB_proto.toString = function () {
                return "[object BlobBuilder]";
            };
            FB_proto.slice = function (start, end, type) {
                var args = arguments.length;
                if (args < 3) {
                    type = null;
                }
                return new FakeBlob(
                    this.data.slice(start, args > 1 ? end : this.data.length)
                    , type
                    , this.encoding
                );
            };
            FB_proto.toString = function () {
                return "[object Blob]";
            };
            FB_proto.close = function () {
                this.size = 0;
                delete this.data;
            };
            return FakeBlobBuilder;
        }(view));
     
        view.Blob = function (blobParts, options) {
            var type = options ? (options.type || "") : "";
            var builder = new BlobBuilder();
            if (blobParts) {
                for (var i = 0, len = blobParts.length; i < len; i++) {
                    if (Uint8Array && blobParts[i] instanceof Uint8Array) {
                        builder.append(blobParts[i].buffer);
                    }
                    else {
                        builder.append(blobParts[i]);
                    }
                }
            }
            var blob = builder.getBlob(type);
            if (!blob.slice && blob.webkitSlice) {
                blob.slice = blob.webkitSlice;
            }
            return blob;
        };
     
        var getPrototypeOf = Object.getPrototypeOf || function (object) {
            return object.__proto__;
        };
        view.Blob.prototype = getPrototypeOf(new view.Blob());
    }(
        typeof self !== "undefined" && self
        || typeof window !== "undefined" && window
        || this
    ));
    

    Export2Excel.js

    /* eslint-disable */
    import { saveAs } from 'file-saver'
    import XLSX from 'xlsx'
    
    function generateArray(table) {
        var out = [];
        var rows = table.querySelectorAll('tr');
        var ranges = [];
        for (var R = 0; R < rows.length; ++R) {
            var outRow = [];
            var row = rows[R];
            var columns = row.querySelectorAll('td');
            for (var C = 0; C < columns.length; ++C) {
                var cell = columns[C];
                var colspan = cell.getAttribute('colspan');
                var rowspan = cell.getAttribute('rowspan');
                var cellValue = cell.innerText;
                if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;
    
                //Skip ranges
                ranges.forEach(function(range) {
                    if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
                        for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
                    }
                });
    
                //Handle Row Span
                if (rowspan || colspan) {
                    rowspan = rowspan || 1;
                    colspan = colspan || 1;
                    ranges.push({
                        s: {
                            r: R,
                            c: outRow.length
                        },
                        e: {
                            r: R + rowspan - 1,
                            c: outRow.length + colspan - 1
                        }
                    });
                };
    
                //Handle Value
                outRow.push(cellValue !== "" ? cellValue : null);
    
                //Handle Colspan
                if (colspan)
                    for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
            }
            out.push(outRow);
        }
        return [out, ranges];
    };
    
    function datenum(v, date1904) {
        if (date1904) v += 1462;
        var epoch = Date.parse(v);
        return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
    }
    
    function sheet_from_array_of_arrays(data, opts) {
        var ws = {};
        var range = {
            s: {
                c: 10000000,
                r: 10000000
            },
            e: {
                c: 0,
                r: 0
            }
        };
        for (var R = 0; R != data.length; ++R) {
            for (var C = 0; C != data[R].length; ++C) {
                if (range.s.r > R) range.s.r = R;
                if (range.s.c > C) range.s.c = C;
                if (range.e.r < R) range.e.r = R;
                if (range.e.c < C) range.e.c = C;
                var cell = {
                    v: data[R][C]
                };
                if (cell.v == null) continue;
                var cell_ref = XLSX.utils.encode_cell({
                    c: C,
                    r: R
                });
    
                if (typeof cell.v === 'number') cell.t = 'n';
                else if (typeof cell.v === 'boolean') cell.t = 'b';
                else if (cell.v instanceof Date) {
                    cell.t = 'n';
                    cell.z = XLSX.SSF._table[14];
                    cell.v = datenum(cell.v);
                } else cell.t = 's';
                ws[cell_ref] = cell;
            }
        }
        if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
        return ws;
    }
    
    function Workbook() {
        if (!(this instanceof Workbook)) return new Workbook();
        this.SheetNames = [];
        this.Sheets = {};
    }
    
    function s2ab(s) {
        var buf = new ArrayBuffer(s.length);
        var view = new Uint8Array(buf);
        for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
        return buf;
    }
    
    export function export_table_to_excel(id) {
        var theTable = document.getElementById(id);
        var oo = generateArray(theTable);
        var ranges = oo[1];
    
        /* original data */
        var data = oo[0];
        var ws_name = "SheetJS";
    
        var wb = new Workbook(),
            ws = sheet_from_array_of_arrays(data);
    
        /* add ranges to worksheet */
        // ws['!cols'] = ['apple', 'banan'];
        ws['!merges'] = ranges;
    
        /* add worksheet to workbook */
        wb.SheetNames.push(ws_name);
        wb.Sheets[ws_name] = ws;
    
        var wbout = XLSX.write(wb, {
            bookType: 'xlsx',
            bookSST: false,
            type: 'binary'
        });
    
        saveAs(new Blob([s2ab(wbout)], {
            type: "application/octet-stream"
        }), "test.xlsx")
    }
    // 使用复杂表头的关键是,对此方法进行修改,如下:
    export function export_json_to_excel({
        multiHeader2 = [], // 第一行表头
        multiHeader = [], // 第二行表头
        header, // 第三行表头
        data,
        filename, //文件名
        merges = [], // 合并
        autoWidth = true,
        bookType = 'xlsx'
    } = {}) {
        /* original data */
        filename = filename || '列表';
        data = [...data]
        data.unshift(header);
    
        for (let i = multiHeader2.length - 1; i > -1; i--) {
            data.unshift(multiHeader2[i])
        }
    
        for (let i = multiHeader.length - 1; i > -1; i--) {
            data.unshift(multiHeader[i])
        }
    
        var ws_name = "SheetJS";
        var wb = new Workbook(),
            ws = sheet_from_array_of_arrays(data);
    
        if (merges.length > 0) {
            if (!ws['!merges']) ws['!merges'] = [];
            merges.forEach(item => {
                ws['!merges'].push(XLSX.utils.decode_range(item))
            })
        }
    
        if (autoWidth) {
            /*设置worksheet每列的最大宽度*/
            const colWidth = data.map(row => row.map(val => {
                    /*先判断是否为null/undefined*/
                    if (val == null) {
                        return {
                            'wch': 10
                        };
                    }
                    /*再判断是否为中文*/
                    else if (val.toString().charCodeAt(0) > 255) {
                        return {
                            'wch': val.toString().length * 2
                        };
                    } else {
                        return {
                            'wch': val.toString().length
                        };
                    }
                }))
                /*以第一行为初始值*/
            let result = colWidth[0];
            for (let i = 1; i < colWidth.length; i++) {
                for (let j = 0; j < colWidth[i].length; j++) {
                    if (result[j]['wch'] < colWidth[i][j]['wch']) {
                        result[j]['wch'] = colWidth[i][j]['wch'];
                    }
                }
            }
            ws['!cols'] = result;
        }
    
        /* add worksheet to workbook */
        wb.SheetNames.push(ws_name);
        wb.Sheets[ws_name] = ws;
    
        var wbout = XLSX.write(wb, {
            bookType: bookType,
            bookSST: false,
            type: 'binary'
        });
        saveAs(new Blob([s2ab(wbout)], {
            type: "application/octet-stream"
        }), `${filename}.${bookType}`);
    }
    

    3.main.js下引入两个文件

     import Blob from '../../static/excel/js/Blob.js'
     import Export2Excel from '../../static/excel/js/Export2Excel.js'
    

    4.导出数据----页面button

    <el-button type="primary" @click="exportData">导出数据</el-button>
    

    5.在页面上引入需要的组件

    import { saveAs } from "file-saver";
    import XLSX from "xlsx";
    

    6.对应的方法exportData

     // 导出数据
        exportData() {
          let that = this;
          // 导出
          // 懒加载,@/common/vendor/Export2Excel 此路径为本地路径
          import("../../../../static/excel/js/Export2Excel.js").then((excel) => {
            const multiHeader = [
              [
                "成绩输出数据列表",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
              ],
            ];
            const multiHeader2 = [
              [
                "用户名",
                "登录名",
                "战队",
                "试题1",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "试题2",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "试题3",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "试题4",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
                "",
              ],
            ];
            const tHeader = [
              "",
              "",
              "",
              "竞赛",
              "试题",
              "设备配置项得分",
              "数据配置项得分",
              "小区得分",
              "小区切换得分",
              "小区漫游得分",
              "客观分数",
              "主观分数",
              "竞赛",
              "试题",
              "设备配置项得分",
              "数据配置项得分",
              "小区得分",
              "小区切换得分",
              "小区漫游得分",
              "客观分数",
              "主观分数",
              "竞赛",
              "试题",
              "设备配置项得分",
              "数据配置项得分",
              "小区得分",
              "小区切换得分",
              "小区漫游得分",
              "客观分数",
              "主观分数",
              "竞赛",
              "试题",
              "设备配置项得分",
              "数据配置项得分",
              "小区得分",
              "小区切换得分",
              "小区漫游得分",
              "客观分数",
              "主观分数",
            ];
            const filterVal = [
              "userName",
              "loginName",
              "teamName",
              "compName1",
              "e1Name",
              "d1eqScore",
              "d1dataScore",
              "d1villScore",
              "d1switchScore",
              "d1roamScore",
              "d1Score",
              "d1Dyfield1",
              "compName2",
              "e2Name",
              "d2eqScore",
              "d2dataScore",
              "d2villScore",
              "d2switchScore",
              "d2roamScore",
              "d2Score",
              "d2Dyfield1",
              "compName3",
              "e3Name",
              "d3eqScore",
              "d3dataScore",
              "d3villScore",
              "d3switchScore",
              "d3roamScore",
              "d3Score",
              "d3Dyfield1",
              "compName4",
              "e4Name",
              "d4eqScore",
              "d4dataScore",
              "d4villScore",
              "d4switchScore",
              "d4roamScore",
              "d4Score",
              "d4Dyfield1",
            ]; // 表头所对应的字段,这里未填写
            let officeId = "";
            if (that.officeId != "187" && that.officeId != "") {
              officeId = that.officeId;
            }
            let params = {
              loginName: that.loginName,
              examName: that.examName,
              officeId: officeId,
            };
            api
              .exportXfScoreList(params) //请求成绩列表的api
              .then(function (res) {
                let jsonData = res;
                const data = that.formatJson(filterVal, jsonData);
                // 进行所有表头的单元格合并
                const merges = [
                  "A1:AM1",
                  "A2:A3",
                  "B2:B3",
                  "C2:C3",
                  "D2:L2",
                  "M2:U2",
                  "V2:AD2",
                  "AE2:AM2",
                  "D3:D3",
                  "E3:E3",
                  "F3:F3",
                  "G3:G3",
                  "H3:H3",
                  "I3:I3",
                  "J3:J3",
                  "K3:K3",
                  "L3:L3",
                  "M3:M3",
                  "N3:N3",
                  "O3:O3",
                  "P3:P3",
                  "Q3:Q3",
                  "R3:R3",
                  "S3:S3",
                  "T3:T3",
                  "U3:U3",
                  "V3:V3",
                  "W3:W3",
                  "X3:X3",
                  "Y3:Y3",
                  "Z3:Z3",
                  "AA3:AA3",
                  "AB3:AB3",
                  "AC3:AC3",
                  "AD3:AD3",
                  "AE3:AE3",
                  "AF3:AF3",
                  "AG3:AG3",
                  "AH3:AH3",
                  "AI3:AI3",
                  "AJ3:AJ3",
                  "AK3:AK3",
                  "AL3:AL3",
                  "AM3:AM3",
                ];
                excel.export_json_to_excel({
                  multiHeader, // 这里是第一行的表头
                  multiHeader2, // 这里是第二行的表头
                  header: tHeader, // 这里是第三行的表头
                  data,
                  filename: "成绩输出数据列表",
                  merges,
                });
              })
              .catch(function (err) {
                console.log(JSON.stringify(err));
              });
          });
        },
        formatJson(filterVal, jsonData) {
          jsonData.map((row, index) => {
            row.index = index + 1;
          });
          return jsonData.map((v) =>
            filterVal.map((j) => {
              return v[j];
            })
          );
        },
    

    万水千山总是情,给个点赞行不行!

    展开全文
  • 数据库的导入与导出

    万次阅读 2018-07-13 02:24:48
    1、首先linux 下查看mysql相关目录whereis mysqlmysql: /usr/bin/mysql---- mysql的运行路径 /etc/mysql /usr/lib/mysql----- mysql的安装路径/usr/bin/X11/mysql /usr/share/mysql/usr/share/man/man1/mysql.1.gz...
  • 子图,生成子图和导出子图

    万次阅读 2016-10-08 23:46:43
    设V1是V的一个非空子集,以V1为顶点集,以两端点均在V1中的边的全体为边集的子图称为G的导出子图,记作G[V1]。导出子图G[V\V1]记为G-V1,它是从G中删去V1中的顶点以及与这些顶点相关的边所得到的子图。若V1={v},则...
  • exp/expdp 与 imp/impdp命令导入导出数据库详解 一、exp命令导出数据库 如何使exp的帮助以不同的字符集显示:set nls_lang=simplified chinese_china.zhs16gbk,通过设置环境变量,可以让exp的帮助以中文显示,...
  • expdp/impdp 数据泵导入导出

    万次阅读 多人点赞 2019-01-09 18:37:21
    EXPDP数据导出 请自行修改目录路径和自定义的表名,否则出现错误很难查找~ 一般expdp流程:   一、新建逻辑目录 最好以system等管理员创建逻辑目录,Oracle不会自动创建实际的物理目录“...
  • Android导出已安装应用程序apk文件的两种方案 如果已经在Android手机上安装了App应用程序,那么Android系统会保留应用程序的apk安装副本。如果要导出这些apk文件,有以下两种方案:第一种方案:命令行模式。 先...
  • Java 导出Excel利用模版导出

    万次阅读 2018-07-31 15:09:08
    Java导出Excel和word的方式大体相同 1、Excel模版导出到页面下载 首先,导入依赖在pom.xml中我选择的事1.03的版本  第二、在项目或是自己需要的地方建立个文件夹放导出文件的模版,并且配好模版    第三...
  • 订单导出(淘宝天猫)

    万次阅读 热门讨论 2019-08-14 14:28:43
    最新版: ... 在多语言Windows XP/Vista/7/8/10上测试通过,中英日韩 ...淘宝天猫买家卖家订单导出,可视化方案直观呈现所有订单的数据 注意:只能导出自己已登录账号下的订单数据,而不是采集任意其他用...
  • Java导出数据到Excel

    万次阅读 多人点赞 2017-07-18 11:56:05
    Java操作Excel数据表,导出工具类,让导出更便捷!
  • 如何进行数据的导出

    千次阅读 2019-05-28 07:42:51
    如何进行数据的导出? 以前有进行过填写资料并且把资料导出Excel表格,当时就觉得好神奇,又想知道这样的功能是怎么实现的,可是那时的头脑简单,没有知道答案,可是现在我终于知道为什么可以直接导出数据了,其实也...
  • js 实现纯前端将数据导出excel两种方式,亲测有效

    万次阅读 多人点赞 2018-05-23 14:33:27
    由于项目需要,需要在不调用后台接口的情况下,将json数据导出到excel表格,参考了好多资料以及很多大佬写的博客终于实现,兼容chrome没问题,其他还没有测试过,这边介绍两种实现方式,并附上代码和gif动图,博主...
  • export 命令导出变量

    万次阅读 2020-04-10 15:26:42
    方法一: export var firstName ='Michael' export var lastName = 'Jackson' export var year = 1958; 方法二: var firstName ='Michael'; var lastName = 'Jackson' ...export {firstName, lastName,...
  • oracle11g数据库导入导出方法教程

    万次阅读 多人点赞 2018-03-04 15:03:09
    oracle11g数据库导入导出: ①:传统方式——exp(导出)和(imp)导入: ②:数据泵方式——expdp导出和(impdp)导入; ③:第三方工具——PL/sql Develpoer;一、什么是数据库导入导出? oracle11g数据库的导入/导出,...
  • 使用easy poi快速导入导出

    万次阅读 2019-06-28 17:56:40
    easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员就可以方便的写出Excel导入,导出,通过简单的注解和模板语言(熟悉的表达式语法),完成以前复杂的写法。 集成 pom 中引入依赖即可 <!--...
  • Git 历史提交日志导出到文件中

    万次阅读 2018-09-03 14:56:59
    git 的历史提交记录导出 如何把 git 的历史提交记录导出来,下面的这个命令就可以做到。 在项目根目录下执行命令,导出 git 提交记录到桌面 git log –pretty=format:”%ai , %an: %s” –since=”100 day ...
  • PowerDesigner导出word模版

    千次下载 热门讨论 2013-05-20 00:05:11
    PowerDesigner导出word通用模版,导出表清单和表列清单
  • Oracle中用exp/imp命令快速导入导出数据 打开支付宝首页搜索“520218643”,即可领红包,帮忙领个红包还可以抵扣哦 【用 exp 数 据 导 出】: 1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu....
1 2 3 4 5 ... 20
收藏数 839,516
精华内容 335,806
关键字:

导出