DataTables合并单元格(rowspan)的兑现思路(多分组分类的情)纯前端JavaScript实现Excel IO案例分享。

一直上代码,原理之前的随笔已经出口过了。http://www.cnblogs.com/hdwang/p/7115835.html

庄近日使吧某个国企做一个**统计与管制体系,

1.先看看效果

切切实实要求涵盖
 •Excel导入导出
 •因导入的数码开展亮报表
 •图表展示(包括柱状图,折线图,饼图),而且还要求如起动画效果,扁平化风格
 •Excel导出,并设供客户端来管理Excel 文件
 •…
 

图片 1

务求确实多! 

 图片 2

今昔终于是完成了,于是用本人之更分析下。 

 


2.html代码,含js代码

每当周项目架构中,首先将解决Excel导入的问题。 

2.1 common.js

由公司没有团结之框架做Excel IO,就只有通过其他渠道了。 

/**
 * Created by hdwang on 2017/6/23.
 */
var language = { "search": "", "sSearch" : "搜索", "sUrl" : "", "sProcessing" : "正在加载数据...", "sLengthMenu" : "显示_MENU_条 ", "sZeroRecords" : "没有您要搜索的内容",
    "sInfo" : "从_START_ 到 _END_ 条记录——总记录数为 _TOTAL_ 条", "sInfoEmpty" : "记录数为0", "sInfoFiltered" : "(全部记录数 _MAX_  条)", "sInfoPostFix" : "",
    "oPaginate": { "sFirst" : "第一页", "sPrevious" : " 上一页 ", "sNext" : " 下一页 ", "sLast" : " 最后一页 " }
};

/**
 * 将参数对象转换成url查询参数
 * @param params 参数对象
 * @returns {string} url查询参数
 */
function getUrlParams(params) {
    var queryStr = '';
    var isFirstParam = true;
    for(var key in params){
        if(isFirstParam){
            queryStr += key + '=' + params[key];
            isFirstParam = false;
        }else{
            queryStr += '&' + key + '=' + params[key];
        }
    }
    return queryStr;
}

啊,我当github上找到了一个开源库xlsx,通过npm方式来设置。
 npm install xlsx –save
今后,在自己之html文件里添加对js文件的援
 <script
src=”./node_modules/xlsx/dist/jszip.js”></script>
<script src=”./node_modules/xlsx/dist/xlsx.js”></script>
透过FileReader对象将数据因第二前进制字符串的计加载到外存中, 

 

target.addEventListener('drop', function (e) {
 e.preventDefault();
 handleDrop(e.dataTransfer.files[0]);
});
handleDrop = function(){
 var reader = new FileReader();
 reader.onload = function (e) {
 var data = e.target.result;
 ...
 ...
 };
 reader.readAsBinaryString(f);
} 

2.2 home.ftl

下一场我们下去的操作就一旦下仓库对data数据开展操作了。 

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <!-- 上述3个meta标签*必须*放在最前面,任何其他内容都*必须*跟随其后! -->
    <title>cpm system</title>

    <!-- Bootstrap -->
    <link href="/thirdlib/bootstrap/css/bootstrap.min.css" rel="stylesheet">

    <!-- datatables -->
    <link href="/thirdlib/datatables/css/jquery.dataTables.min.css" rel="stylesheet"/>

    <link href="/css/common.css" rel="stylesheet" />
</head>
<body>


<div id="tableArea" style="padding: 100px;">

<div>
    <a href="/home/export">导出</a>
</div>


    <table id="rowspanTable" class="table table-bordered">
        <thead>
            <th>地区</th>
            <th>公司</th>
            <th>部门</th>
            <th>员工姓名</th>
        </thead>
    </table>

</div>

</body>

<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
<script src="/thirdlib/jquery/jquery-2.0.3.min.js"></script>
<!-- Include all compiled plugins (below), or include individual files as needed -->
<script src="/thirdlib/bootstrap/js/bootstrap.min.js"></script>

<!-- datatables -->
<script src="/thirdlib/datatables/js/jquery.dataTables.min.js"></script>

<script src="/js/common.js"></script>

<script type="text/javascript">
    $(function(){

        $('#rowspanTable').dataTable( {
            "paging": true,
            "processing": true,
            "serverSide": true,
            "searching":false, //搜索栏
            "lengthChange" : false, //是否允许改变每页显示的数据条数
            "pageLength": 10, //每行显示记录数
            "info":true, //开启Datatables信息显示(记录数等)
            "ordering":false, //全局定义是否启用排序,优先级比columns.orderable高
            "language": language,
            "ajax": {
                "url": "/home/query",
                "type": "POST"
            },
            "columns": [
                {"data":"area", "orderable": false,"searchable": false},
                { "data": "company", "orderable": false ,"searchable": false},
                { "data": "department", "orderable": false,"searchable": false },
                { "data": "userName", "orderable": false ,"searchable": false}
            ],
            "columnDefs": [{
                targets: [0,1,2], //第1,2,3列
                createdCell: function (td, cellData, rowData, row, col) {
                    var rowspan = 1;
                    if(col == 0){
                        rowspan = rowData.areaRowSpan;
                    }
                    if(col ==1){
                        rowspan = rowData.companyRowSpan;
                    }
                    if(col ==2){
                        rowspan = rowData.departmentRowSpan;
                    }

                    if (rowspan > 1) {
                        $(td).attr('rowspan', rowspan)
                    }
                    if (rowspan == 0) {
                        $(td).remove();
                    }
                }
            }]
        } );


    });

</script>

</html>

其暴露了一个对象XLSX,通过XLSX的read()
方法就是可以数据读也JSON对象了。

 

var workbook = XLSX.read(data, { type: 'binary' });
var sheetName = workbook.SheetNames[0];
var sheet = workbook.Sheets[sheetName]; 

3.继令代码

今后,使用键值对的法再次把数量由sheet中收获出来放到表格中。

3.1 分页参数对象

var table = document.createElement('table');
for (var row = 1; ; row++) {
 if (sheet['A' + row] == null) {
  break;
 }
 var tr = document.createElement('tr');

 for (var col = 65; col <= 90; col++) {
  var c = String.fromCharCode(col);// get 'A', 'B', 'C' ... 
  var key = '' + c + row;
  if (sheet[key] == null) {
   break;
  }
  var td = document.createElement('td');
  td.innerHTML = sheet[key]['w'];
  tr.appendChild(td);
 }
 table.appendChild(tr);
}
document.querySelector('#target').appendChild(table); 
package com.xincheng.cpm.common;

/**
 * Created by hdwang on 2017/6/22.
 * 分页参数
 */
public class PageParam {

    /**
     * 第几次绘画(前端标识)
     */
    private int draw;

    /**
     * 起始记录(从0开始),mysql也是从0开始,吻合,good!
     */
    private int start;

    /**
     * 页大小
     */
    private int length;

    public int getDraw() {
        return draw;
    }

    public void setDraw(int draw) {
        this.draw = draw;
    }

    public int getStart() {
        return start;
    }

    public void setStart(int start) {
        this.start = start;
    }

    public int getLength() {
        return length;
    }

    public void setLength(int length) {
        this.length = length;
    }

    /**
     * 第几页(0-n)
     */
    public int getPage(){
        return this.start/this.length;
    }
}

下是完全代码:
index.html 

 

<!DOCTYPE html>
<html lang="en">
<head>
 <meta charset="UTF-8">
 <title>Document</title>
 <style>
  #target {
   height: 400px;
   width: 700px;
   background-color: #f8f8f8;
   margin: 200px auto;
   overflow:hidden;
   border-radius:5px;
   box-shadow:2px 2px 5px #888;
  } 
  .hover::before {
   content: '请将excel文件拖到这里';
   width: 100%;
   height: 100%;
   display: block;
   text-align: center;
   line-height: 400px;
   font-size: 24px;
   font-family: '微软雅黑';
  }
  #target>table{
   height:250px;
   width:400px;
   border:1px solid #ccc;
   border-radius:3px;
   margin:75px auto;
  }
  #target>table td{
   text-align:center;
   border-top:1px solid #ccc;
   border-left:1px solid #ccc;
  }
   #target>table tr:first-child>td{
    border-top:0px solid #ccc;
   }
   #target>table tr>td:first-child{
    border-left:0px solid #ccc;
   }
 </style>
</head>
<body>
 <div id="target" class="hover">
 </div>

 <script src="./node_modules/xlsx/dist/jszip.js"></script>
 <script src="./node_modules/xlsx/dist/xlsx.js"></script>
 <script src="index.js"></script>
</body>
</html> 

3.2 数据返回对象

脚是完好js代码 
index.js 

package com.xincheng.cpm.common;

import java.util.List;

/**
 * Created by hdwang on 2017/6/22.
 * 表格数据(datatables)
 */
public class TableData<T> {

    /**
     * 第几次绘画(前端标识)
     */
    private int draw;

    /**
     * 行过滤(不知道干嘛的)
     */
    private int recordsFiltered;

    /**
     * 总行数
     */
    private int recordsTotal;

    /**
     * 行数据
     */
    private List<T> data;

    /**
     * 起始记录(用于前端初始化序列号用的)
     */
    private int start;

    /**
     *  错误信息
     */
    private String error;

    public int getDraw() {
        return draw;
    }

    public void setDraw(int draw) {
        this.draw = draw;
    }

    public int getRecordsFiltered() {
        return recordsFiltered;
    }

    public void setRecordsFiltered(int recordsFiltered) {
        this.recordsFiltered = recordsFiltered;
    }

    public int getRecordsTotal() {
        return recordsTotal;
    }

    public void setRecordsTotal(int recordsTotal) {
        this.recordsTotal = recordsTotal;
    }

    public List<T> getData() {
        return data;
    }

    public void setData(List<T> data) {
        this.data = data;
    }

    public int getStart() {
        return start;
    }

    public void setStart(int start) {
        this.start = start;
    }

    public String getError() {
        return error;
    }

    public void setError(String error) {
        this.error = error;
    }
}
window.addEventListener('load', function () {
 var target = document.querySelector('#target');
 target.addEventListener('dragenter', function () {
  this.classList.remove('hover');
 });
 target.addEventListener('dragleave', function () {
  this.classList.add('hover');
 });
 target.addEventListener('dragover', function (e) {
  this.classList.remove('hover');
  e.preventDefault();
 });

 target.addEventListener('drop', function (e) {
  e.preventDefault();
  handleDrop(e.dataTransfer.files[0]);
 });

});
var handleDrop = function (f) {
 var reader = new FileReader(),
  name = f.name;
 reader.onload = function (e) {
  var data = e.target.result,
   workbook = XLSX.read(data, { type: 'binary' }),
   sheetName = workbook.SheetNames[0],
   sheet = workbook.Sheets[sheetName],
   table = document.createElement('table');

  for (var row = 1; ; row++) {
   if (sheet['A' + row] == null) {
    break;
   }
   var tr = document.createElement('tr');

   for (var col = 65; col <= 90; col++) {
    var c = String.fromCharCode(col);// get 'A', 'B', 'C' ... 
    var key = '' + c + row;
    if (sheet[key] == null) {
     break;
    }
    var td = document.createElement('td');
    td.innerHTML = sheet[key]['w'];
    tr.appendChild(td);
   }
   table.appendChild(tr);
  }
  document.querySelector('#target').appendChild(table);
 };
 reader.readAsBinaryString(f);
} 

 

意义如下:

 3.3 数据实体对象

 图片 3

package com.xincheng.cpm.common;

import java.io.Serializable;

/**
 * Created by hdwang on 2017/7/14.
 */
public class Member{

    private String area;
    private String company;
    private String department;
    private String userName;

    private Integer areaRowSpan;
    private Integer companyRowSpan;
    private Integer departmentRowSpan;


    public Member(String area,String company,String department,String userName){
        this.area = area;
        this.company = company;
        this.department = department;
        this.userName = userName;
    }

    public String getArea() {
        return area;
    }

    public void setArea(String area) {
        this.area = area;
    }

    public String getCompany() {
        return company;
    }

    public void setCompany(String company) {
        this.company = company;
    }

    public String getDepartment() {
        return department;
    }

    public void setDepartment(String department) {
        this.department = department;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public Integer getAreaRowSpan() {
        return areaRowSpan;
    }

    public void setAreaRowSpan(Integer areaRowSpan) {
        this.areaRowSpan = areaRowSpan;
    }

    public Integer getCompanyRowSpan() {
        return companyRowSpan;
    }

    public void setCompanyRowSpan(Integer companyRowSpan) {
        this.companyRowSpan = companyRowSpan;
    }

    public Integer getDepartmentRowSpan() {
        return departmentRowSpan;
    }

    public void setDepartmentRowSpan(Integer departmentRowSpan) {
        this.departmentRowSpan = departmentRowSpan;
    }
}

如此做好像中,但是我们飞速就放弃了。 
弊端太多了。
 •这个库现在眼前尚处在开发阶段,在issues里面还有多底Bug被提出。这没办法保证最终网站的康乐。
 •这个库房没有辙导入合并单元格的数量,只能是挺死的按照’A’, ‘B’, ‘C’…
和1, 2, 3 坐标来询问数据,而且它要求中单元格不能够为空。
 •更比较不便利的就是,它从不执行及排的计数的属性。
 •由于这是啊国企做的,所以无法将主要作用依赖让这个star量不是诸多之库房,降低风险,也是为网站的安全性。
 •…

 

 

由此小组探讨,我们决定使用另外一缓前端控件,叫做
Wijmo。 
首先,从网站上下载Wijmo包,这个控件没有提供npm和bower等方式。

图片 4 

接下来将自我得的包导入进 

<script src="./wijmo/dist/controls/wijmo.min.js"></script>
<script src="./wijmo/dist/controls/wijmo.grid.min.js"></script>
<script src="./wijmo/dist/controls/wijmo.grid.detail.min.js"></script>
<script src="./wijmo/dist/controls/wijmo.grid.xlsx.min.js"></script>
<script src="./wijmo/dist/controls/wijmo.xlsx.min.js"></script> 

另外,还有引入一个jszip的保险,是行使js来解压压缩包的一个库。(由于MS的open
xml技术,xlsx文件还足以解压成为xml文件,app.xml 里含有了重在的数额)。
 <script
src=”./jszip.min.js”></script>
读取文件之操作及上面还是同一的 

var handleDrop = function (file) {
 var reader,
  workbook;

 if (file) {
  reader = new FileReader;
  reader.onload = function (e) {
   workbook = new wijmo.xlsx.Workbook(),
    workbook.load(reader.result);
  };
  reader.readAsDataURL(file);
 }
} 

通过 
 workbook = new wijmo.xlsx.Workbook();
 workbook.load(reader.result);
立马简单尽代码将excel文件加载到外存 中之workbook对象。 

打印workbook对象

 图片 5

打印是目标发现,workbook里面包含sheets数组,每个sheet包含rows数组,每个row包含cells数组,每个cell里面vaule属性就是单元格的价。
及时简直太又吓了 

下面实现一个函数 getCollectionView ,以目标往往组的章程来获取数据

 var getCollectionView = function (workbook) {
 var collectionView = [];
 if (workbook) {
  var sheet = workbook.sheets[0],
   header = []; // 列标题数组

  for (var i = 0, length = sheet.rows.length; i < length; i++) {
   var row = sheet.rows[i],
    rowArray = {};
   for (var j = 0, jLength = row.cells.length; j < jLength; j++) {
    var cell = row.cells[j];
    // 如果是第一行数据,那么是作为列标题出现的,就放进标题数组中
    if (i === 0) {
     header.push(cell.value);
    }
    else {
     // 后面的行数组,就作为rowArray对象的属性存储,属性名就是该列的标题。
     rowArray[header[j]] = cell.value;
    }
   }
   if (i !== 0) {
    collectionView.push(rowArray);
   }
  }
 }
 return collectionView;
} 

然后用一个报表将数据显现出,这里我一直利用了Wijmo的FlexGrid表格。 

gridDiv = document.createElement('div');
gridDiv.classList.add('grid');
dataGrid = new wijmo.grid.FlexGrid(gridDiv);// 通过传入容器构造一个FlexGrid表单。
var collectionView = new wijmo.collections.CollectionView(getCollectionView(workbook));
dataGrid.itemsSource = collectionView; 

吓了,经过地方几乎只步骤,导入Excel到表格已经实现了 

当即是共同体的js代码: 

index.js 

(function () {
 var dataGrid = null,
  gridDiv = null,
  workbook = null;
 window.addEventListener('load', function () {
  gridDiv = document.createElement('div');
  gridDiv.classList.add('grid');
  dataGrid = new wijmo.grid.FlexGrid(gridDiv);
  var target = document.querySelector('#target');

  target.addEventListener('dragenter', function (e) {
   e.preventDefault();
   this.classList.remove('hover');

  });
  target.addEventListener('dragleave', function (e) {
   e.preventDefault();
   this.classList.add('hover');
  });
  target.addEventListener('dragover', function (e) {
   e.preventDefault();
   this.classList.remove('hover');
  });

  target.addEventListener('drop', function (e) {
   e.preventDefault();
   handleDrop(e.dataTransfer.files[0]);
   // 将这个表单添加到页面上
   this.appendChild(gridDiv);
  });
 });

 var handleDrop = function (file) {
  var reader;
  var workbook;

  if (file) {
   reader = new FileReader;
   reader.onload = function (e) {
    workbook = new wijmo.xlsx.Workbook();
    workbook.load(reader.result);
    var collectionView = new   wijmo.collections.CollectionView(getCollectionView(workbook));
    dataGrid.itemsSource = collectionView;
    // console.log(dataGrid.collectionView);
   };
   reader.readAsDataURL(file);
  }
 }

 var getCollectionView = function (workbook) {
  var collectionView = [];

  if (workbook) {
   var sheet = workbook.sheets[0];
   var title = [];

   for (var i = 0, length = sheet.rows.length; i < length; i++) {
    var row = sheet.rows[i];
    var rowArray = {};

    for (var j = 0, jLength = row.cells.length; j < jLength; j++) {
     var cell = row.cells[j];
     if (i === 0) {
      header.push(cell.value);
     }
     else {
      rowArray[header[j]] = cell.value;
     }
    }
    if (i !== 0) {
     collectionView.push(rowArray);
    }
   }
  }
  return collectionView;
 }
})(window); 

脚是职能

 图片 6

Excel 导出 

欧了 

简单句子代码实现Excel导出效益 

wijmo.grid.xlsx.FlexGridXlsxConverter.save(dataGrid,
    { includeColumnHeaders: true }, fileName);
此表格还支持过滤,分组,筛选,编辑。 

面积图跟柱状图 

纵使以完成Excel IO
之后,发现这个控件包还可以做面积图,柱状图与其余不少路的图样。
于是于这边就演示一个面积图的与一个柱状图的事例。
第一,要拿确保引进来。 
<script
src=”./wijmo/dist/controls/wijmo.chart.min.js”></script>
下一场通过下面几乎句代码,就可应用于页面被插一个柱状图 

chart = new wijmo.chart.FlexChart('#chart');
chart.initialize({
 itemsSource: collectionView,
 bindingX: 'name',
 options: {
  groupWidth: 15
 },
 series: [
  { name: '年龄', binding: 'age' },
 ]
}); 

下看成效

 图片 7

其间,颜色与柱状图的造型可以调动的。当鼠标移到元素上,还有会略提示。 

当此间,只需要转移一下chart的档次,就可切换为其他门类的图表 

chart.chartType = chart.chartType === wijmo.chart.ChartType.Column ?
 wijmo.chart.ChartType.Area :
 wijmo.chart.ChartType.Column;

有关本篇的代码都达传
http://xiazai.jb51.net/201608/yuanma/js-xlsWijmo-IO(jb51.net).rar

此起彼伏会托管到github.

末段还是比快的就了任务。 

有关这路之Excel IO
就大概介绍及此处,这个类型现一度到位了,后续会享用部分任何的技术细节。 

希可以针对您提供救助。

上述就是本文的全部内容,希望对大家的求学有助,也指望大家多支持脚本的家。

3.4 导出彼此关类

而或许感兴趣之章:

  • js导入导出excel(实例代码)
  • JS
    Excel读取和写入操作(模板操作)实现代码
  • 可以读取EXCEL文件的js代码
  • JavaScript将页面表格导出为Excel的现实贯彻
  • 由此Javascript读取本地Excel文件内容的代码示例
  • Js
    导出table内容到Excel的大概实例
  • javascript Excel操作知识点
  • JS兼容浏览器的导出Excel(CSV)文件之法门
  • JS将表单导出成EXCEL的实例代码
  • JS 巧妙获取剪贴板数据
    Excel数据的糊
package com.xincheng.cpm.common;

/**
 * Created by hdwang on 2017/7/14.
 */
public class ExcelData {
    private String value;//单元格的值
    private int colSpan = 1;//单元格跨几列
    private int rowSpan = 1;//单元格跨几行
    private boolean alignCenter;//单元格是否居中,默认不居中,如果选择是,则水平和上下都居中
    public boolean isAlignCenter() {
        return alignCenter;
    }
    public void setAlignCenter(boolean alignCenter) {
        this.alignCenter = alignCenter;
    }
    public String getValue() {
        return value;
    }
    public void setValue(String value) {
        this.value = value;
    }
    public int getColSpan() {
        return colSpan;
    }
    public void setColSpan(int colSpan) {
        this.colSpan = colSpan;
    }
    public int getRowSpan() {
        return rowSpan;
    }
    public void setRowSpan(int rowSpan) {
        this.rowSpan = rowSpan;
    }
}

package com.xincheng.cpm.common;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.util.List;

/**
 * Created by hdwang on 2017/7/14.
 */
public class ExcelUtil {


    /**
     * 生成excel工作簿
     * @param sheetTitle sheet名称
     * @param titles 标题
     * @param rows 行数据
     * @return 工作簿
     */
    public XSSFWorkbook execute(String sheetTitle,String[] titles,List<List<ExcelData>> rows) {
        //定义工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();

        //th样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setBorderBottom((short) 1);
        titleStyle.setBorderRight((short)1);
        titleStyle.setBorderLeft((short)1);
        titleStyle.setBorderTop((short)1);
        titleStyle.setVerticalAlignment((short)1);
        titleStyle.setAlignment((short)2);
        XSSFFont font = workbook.createFont();
        font.setBold(true);
        titleStyle.setFont(font);
        //td样式
        CellStyle style = workbook.createCellStyle();
        style.setBorderBottom((short)1);
        style.setBorderRight((short)1);
        style.setBorderLeft((short)1);
        style.setBorderTop((short)1);
        style.setVerticalAlignment((short)1);

        //创建工作表
        XSSFSheet sheet = workbook.createSheet(sheetTitle);
        sheet.setDefaultRowHeightInPoints(20.0F);

        //创建标题行
        XSSFRow titleRow = sheet.createRow(0);


        for(int col=0;col<titles.length;col++) { //遍历列
            Cell cell = titleRow.createCell(col);
            cell.setCellStyle(titleStyle);
            cell.setCellValue(titles[col]);

            for(int row=0;row<rows.size();row++){ //遍历行
                int rowIndex = row+1;
                XSSFRow contentRow = sheet.getRow(rowIndex);
                if(contentRow == null){
                    contentRow = sheet.createRow(rowIndex);
                }
                ExcelData data = rows.get(row).get(col);
                Cell contentRowCell = contentRow.createCell(col);
                contentRowCell.setCellStyle(style);
                contentRowCell.setCellValue(data.getValue());
                //合并单元格
                if (data.getColSpan() > 1 || data.getRowSpan() > 1) {
                    CellRangeAddress cra = new CellRangeAddress(rowIndex, rowIndex + data.getRowSpan() - 1, col, col + data.getColSpan() - 1);
                    sheet.addMergedRegion(cra);
                }
            }
        }

        return workbook;
    }
}

 

 

3.5 controller层

package com.xincheng.cpm.controller;

import com.chenrd.common.excel.ExportExcel;
import com.xincheng.cpm.common.*;
import com.xincheng.cpm.entity.cpm.User;
import com.xincheng.cpm.service.UserService;
import com.xincheng.cpm.vo.IncomeDailyVO;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;

/**
 * Created by hdwang on 2017/6/19.
 */
@Controller
@RequestMapping("/home")
public class HomeController {

    @Autowired
    UserService userService;

    @RequestMapping("")
    public String index(HttpSession session, ModelMap map, HttpServletRequest request){
        User user = (User) session.getAttribute("user");
        map.put("user",user);
        return "home";
    }


    @RequestMapping(value="/query",method= RequestMethod.POST)
    @ResponseBody
    public TableData<Member> getUserByPage(PageParam pageParam, User user){
        Page<Member> userPage = this.getMembers(pageParam);
        TableData<Member> datas = new TableData<>();
        datas.setDraw(pageParam.getDraw());
        datas.setStart(pageParam.getStart());
        datas.setData(userPage.getContent());
        datas.setRecordsFiltered((int)userPage.getTotalElements());
        datas.setRecordsTotal((int)userPage.getTotalElements());
        return datas;
    }

    private Page<Member> getMembers(PageParam pageParam) {
        //1.模拟数据库查询
        Pageable pageable = new PageRequest(pageParam.getPage(), pageParam.getLength());
        long count = 6;
        List<Member> members = getMembersFromDb();

        //2.计算rowspan
        this.countRowspan(members);


        Page<Member> memberPage = new PageImpl<Member>(members,pageable,count);
        return memberPage;
    }

    private void countRowspan(List<Member> members) {
        Map<String,Integer> propertyCountMap = this.countPropertyCount(members);
        List<String> hadGetKeys = new ArrayList<>(); //曾经取过的key
        for(Member member:members){
            String areaKey = member.getArea();
            String companyKey = areaKey+member.getCompany();
            String departmentKey = companyKey+ member.getDepartment();

            Integer areaCount = propertyCountMap.get(areaKey);
            if(areaCount == null){
                member.setAreaRowSpan(1);
            }else{
                if(hadGetKeys.contains(areaKey)){
                    member.setAreaRowSpan(0); //曾经取过
                }else{
                    member.setAreaRowSpan(areaCount); //第一次取
                    hadGetKeys.add(areaKey);
                }
            }

            Integer companyCount = propertyCountMap.get(companyKey);
            if(companyCount == null){
                member.setCompanyRowSpan(1);
            }else {
                if(hadGetKeys.contains(companyKey)){
                    member.setCompanyRowSpan(0);
                }else{
                    member.setCompanyRowSpan(companyCount);
                    hadGetKeys.add(companyKey);
                }
            }

            Integer departmentCount = propertyCountMap.get(departmentKey);
            if(companyCount == null){
                member.setDepartmentRowSpan(1);
            }else {
                if(hadGetKeys.contains(departmentKey)){
                    member.setDepartmentRowSpan(0);
                }else{
                    member.setDepartmentRowSpan(departmentCount);
                    hadGetKeys.add(departmentKey);
                }
            }
        }
    }

    private List<Member> getMembersFromDb() {
        Member member1 = new Member("安徽","A","人力资源部"," 小红");
        Member member2 = new Member("安徽","B","人力资源部"," 小明");
        Member member3 = new Member("浙江","C","人力资源部"," 小君");
        Member member4 = new Member("浙江","C","技术部"," 小王");
        Member member5 = new Member("浙江","D","技术部"," 小李");
        Member member6 = new Member("浙江","D","人力资源部"," 小刚");
        List<Member> members = new ArrayList<>();
        members.add(member1);
        members.add(member2);
        members.add(member3);
        members.add(member4);
        members.add(member5);
        members.add(member6);
        return members;
    }

    /**
     * 统计每个字段的每组成员个数
     * @param rows  记录
     * @return 每个字段的每组成员个数
     */
    private Map<String,Integer> countPropertyCount(List<Member> rows){

        Map<String,Integer> propertyCountMap = new HashMap<>();

        for(Member member:rows){
            // "area": 无父级分组
            String area = member.getArea();
            if(propertyCountMap.get(area) == null){
                propertyCountMap.put(area,1);
            }else{
                int count = propertyCountMap.get(area);
                propertyCountMap.put(area,count+1);
            }

            // "company":有area父组
            String company = member.getCompany();
            String uniqueParent = member.getArea();
            String key = uniqueParent + company;
            if(propertyCountMap.get(key) == null){
                propertyCountMap.put(key,1);
            }else{
                int count = propertyCountMap.get(key);
                propertyCountMap.put(key,count+1);
            }

            // "department": 有area,company这两个父组
            String department = member.getDepartment();
            uniqueParent = member.getArea()+member.getCompany();
            key = uniqueParent + department;
            if(propertyCountMap.get(key) == null){
                propertyCountMap.put(key,1);
            }else{
                int count = propertyCountMap.get(key);
                propertyCountMap.put(key,count+1);
            }
        }

        return propertyCountMap;
    }


    @RequestMapping("/export")
    public void export(HttpServletResponse response) throws IOException {
        List<Member> members = this.getMembersFromDb();
        this.countRowspan(members);

        List<List<ExcelData>> rows = new ArrayList<>();
        for(Member member:members){
            List<ExcelData> row = new ArrayList<>();
            ExcelData col1 = new ExcelData();
            col1.setValue(member.getArea());
            col1.setRowSpan(member.getAreaRowSpan());
            row.add(col1);

            ExcelData col2 = new ExcelData();
            col2.setValue(member.getCompany());
            col2.setRowSpan(member.getCompanyRowSpan());
            row.add(col2);

            ExcelData col3 = new ExcelData();
            col3.setValue(member.getDepartment());
            col3.setRowSpan(member.getDepartmentRowSpan());
            row.add(col3);

            ExcelData col4 = new ExcelData();
            col4.setValue(member.getUserName());
            row.add(col4);

            rows.add(row);
        }

        OutputStream outputStream = response.getOutputStream();
        try {
            String filename = URLEncoder.encode("员工" + ".xlsx", "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.addHeader("Content-Disposition", "octet-stream;filename=" + filename);

            ExcelUtil excelUtil = new ExcelUtil();
            XSSFWorkbook workbook = excelUtil.execute("sheet1",new String[]{"地区","公司","部门","员工姓名"},rows);
            workbook.write(outputStream);
        } finally {
            if (outputStream != null) outputStream.close();
        }
    }




}

导出excel功能以poi类库实现。至此,页面显示和导出均OK!

 

admin

网站地图xml地图