导出 excel表格（数据、echarts图片）

不念不忘少年蓝@ 2022-06-16 08:13 275阅读 0赞

/\*\*  
 \* @Description：导出  
 \* @author: liuc  
 \* @since: 2016年4月14日 上午10:36:39  
 \*/  
 @RequestMapping("/exportTotalData")  
 public void exportTotalDataList(HttpServletRequest request,HttpServletResponse response) throws Exception \{   
  
  
 String\[\] sheetNames = \{ "total" \};  
 String\[\] excleTitleMsg = \{   
 "custId:客户ID",  
 "custName:客户名称",  
 "productName:产品名称",  
 "cnt:数量",   
 "orderPrice:消费金额",  
 "rechargeDate:充值日期",\};  
 Map<String,Object> paramMap = getParameterMap(request);  
 String filter = assemblyDataViewSql(  
 request, Code.TYPE\_STATISTIC, "CUSTOM\_MANAGER\_CUST\_ID", "ORG\_CODE");  
 /\*  String likecustName=request.getParameter("likecustName");  
 likecustName=java.net.URLDecoder.decode(likecustName, "UTF-8");  
 paramMap.put("likecustName", likecustName);\*/  
 paramMap.put("filter", filter);  
 List<CustDayCheckBill>  list = custDayCheckBillService.find(1, 9999, paramMap).getList();  
 String filename = "total.xls";  
 response.reset();  
 request.setCharacterEncoding("UTF-8");  
 response.setCharacterEncoding("UTF-8");  
 response.setContentType("application/vnd.ms-excel");  
 response.addHeader("Content-Disposition", "attachment; filename=\\"" + filename + "\\"");

ExportExcelProvider.exportExcelByRslt(sheetNames, excleTitleMsg, list, response, null, null);

//封装类

package com.liuliangqianbao.common.exportExcel;  
  
  
import [Java][].io.ByteArrayOutputStream;  
import java.io.InputStream;  
import java.io.OutputStream;  
import java.lang.reflect.Method;  
import java.util.ArrayList;  
import java.util.HashMap;  
import java.util.List;  
import java.util.Map;  
  
  
import javax.servlet.http.HttpServletResponse;  
import javax.swing.table.DefaultTableModel;  
import javax.swing.table.TableModel;  
  
/\*\*     
\*      
\* 项目名称：sms-platform     
\* 类名称：ExportExcelProvider     
\* 类描述： 提供 导出excel的方法  exportExcel（）  
\* 创建人：zhangwei     
\* 创建时间：2014-9-26 下午03:48:03     
\* 修改人：zhangwei  
\* 修改时间：2014-9-26 下午03:48:03     
\* 修改备注：     
\* @version      
\*      
\*/   
public class ExportExcelProvider \{  
    /\*\*  
     \*   
     \* @param sheetNames：sheet的名称 数组  
     \* @param excleTitleMsg： 数组元素格式 -非模板类型：bean的Field或者Map的key | 类型(String) ：title名称    模板类型：bean的Field或者Map的key：none  
     \* @param datalist:结果集List， 元素为Bean 或者是Map   
     \* @param response: HttpServletResponse   
     \* @param templetInputIo:Excel固定表头的模板流    
     \* @param imageSheets :  类型Map  用来在excel中显示图片，图片以ByteArrayOutputStream输出。  
    \*/  
  
    public static void exportExcelByRslt(String\[\] sheetNames ,String\[\] excleTitleMsg,List datalist,HttpServletResponse response,InputStream templetInputIo,Map imageSheets ) throws Exception\{  
    TableModel data = new DefaultTableModel(datalist.size()+1,excleTitleMsg.length);  
    List<String>  colFieldNamelist = new ArrayList<String>();  
    //获取 结果集中对象的属性名和标题 添加到TableModel中     
    if(excleTitleMsg.length>0)\{  // --判断是否是模板文件  
    for(int i=0;i<excleTitleMsg.length;i++)\{  
    String\[\] temp = excleTitleMsg\[i\].split(":");  
    colFieldNamelist.add(temp\[0\]);  
    data.setValueAt(temp\[1\], 0, i);  
    \}  
    \}  
    //添加结果集中的数据到TableModel中  
    for(int rowIndex=0;rowIndex<datalist.size();rowIndex++)\{  
    Object object=datalist.get(rowIndex);  
    int temprowindex=rowIndex+1;  
    for(int colIndex=0;colIndex<colFieldNamelist.size();colIndex++)\{  
    String\[\] colFieldName=colFieldNamelist.get(colIndex).split("\\\\|");  
    String fieldName = colFieldName\[0\];  
    String fieldType = "";  
    if(colFieldName.length>1)\{  
    fieldType = colFieldName\[1\];  
    \}  
    if(object instanceof HashMap)\{  
    if(fieldType.equals("String"))\{  
    data.setValueAt(String.valueOf(((HashMap) object).get(fieldName)), temprowindex, colIndex);  
    \}else\{  
    data.setValueAt(((HashMap) object).get(fieldName), temprowindex, colIndex);  
    \}  
    \}else\{//List中的元素是Bean  
    if(fieldType.equals("String"))\{  
        Object valueObject=getFieldValueByName(fieldName,object);  
        data.setValueAt(String.valueOf(valueObject), temprowindex, colIndex);  
    \}else\{  
           Object valueObject=getFieldValueByName(fieldName,object);  
        data.setValueAt(valueObject, temprowindex, colIndex);  
    \}  
    \}  
      
    \}  
    \}  
    ByteArrayOutputStream bos = null;  
    InputStream fis = templetInputIo;  
OutputStream fos = null;  
try \{  
fos =response.getOutputStream();  
bos = new ByteArrayOutputStream();  
TableModel\[\] tables = \{data\};  
//ExcelWriter.write(fis,bos,sheetNames,tables);  
ExcelWriter.write(fis, bos, sheetNames, tables, null, null, false, 2, imageSheets);  
bos.writeTo(fos);  
bos.flush();  
\} catch (Exception e1) \{  
e1.printStackTrace();  
\}finally\{  
if(bos != null)\{  
try\{bos.close();\}catch(Exception e)\{\}  
\}  
//关闭模板流  
if(fis != null)\{  
try\{fis.close();\}catch(Exception e)\{\}  
\}  
//关闭response提供的响应流  
if(fos != null)\{  
try\{fos.close();\}catch(Exception e)\{\}  
\}  
\}  
     \}  
           
     /\*\*  
      \* Description: 根据属性名获取属性值  
      \* @param fieldName  
      \* @param   
      \* @return 属性值  
     \*/  
     protected static Object getFieldValueByName(String fieldName, Object o) \{  
       try \{   
           String firstLetter = fieldName.substring(0, 1).toUpperCase();   
           String getter = "get" + firstLetter + fieldName.substring(1);   
           Method method = o.getClass().getMethod(getter, new Class\[\] \{\});   
           Object value = method.invoke(o, new Object\[\] \{\});   
           return value;   
       \} catch (Exception e) \{  
         e.printStackTrace();  
           return null;   
       \}   
   \}   
\}

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

//导出echarts图片及列表数据至excel表格

\-------------------------------------------------------------------------------------------------------------------JSP页面--------------------------------------------------------------------------------------------------------

<%@ page contentType="text/html; charset=UTF-8"%>  
<%@ taglib prefix="q" uri="/query-tags"%>  
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>  
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>  
<%@taglib uri="http://www.springframework.org/tags" prefix="[spring][]"%>   
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">  
<html xmlns="http://www.w3.org/1999/xhtml">  
<head>  
<title></title>  
  
<link rel="stylesheet"  
href="$\{pageContext.request.contextPath\}/css/style.css" type="text/css">  
<link rel="stylesheet"  
href="$\{pageContext.request.contextPath\}/css/admin.css" type="text/css">  
<script type="text/[JavaScript][]"  
src="$\{pageContext.request.contextPath\}/js/[jQuery][]\-1.7.2.min.js"></script>  
<script type="text/javascript"  
src="$\{pageContext.request.contextPath\}/js/pageset.js"></script>  
<script type="text/javascript"  
src="$\{pageContext.request.contextPath\}/js/checkboxjs.js"></script>  
<script type="text/javascript"  
src="$\{pageContext.request.contextPath\}/js/postSubimt.js"></script>  
<script type="text/javascript"  
src="$\{pageContext.request.contextPath\}/js/fromToJosn.js"></script>  
<script type="text/javascript"   
src="$\{pageContext.request.contextPath\}/js/My97DatePicker/WdatePicker.js"></script>  
  
<script type="text/javascript" src="$\{pageContext.request.contextPath\}/js/selectUI/jquery-ui.min.js"></script>  
<script type="text/javascript" src="$\{pageContext.request.contextPath\}/js/selectUI/jquery.multiselect.js"></script>  
<script type="text/javascript"  src="$\{pageContext.request.contextPath\}/js/selectUI/jquery.multiselect.zh-cn.js"></script>   
<script type="text/javascript" src="$\{pageContext.request.contextPath\}/js/echarts/echarts.js"></script>  
  
  
<link rel="stylesheet" href="$\{pageContext.request.contextPath\}/js/selectUI/jquery.multiselect.css" type="text/css">  
<link rel="stylesheet" href="$\{pageContext.request.contextPath\}/js/selectUI/jquery-ui.css" type="text/css">  
  
  
  
<script type="text/javascript" src="$\{pageContext.request.contextPath \}/js/EasyuiUtils.js"></script>  
<script type="text/javascript" src="$\{pageContext.request.contextPath \}/js/easyui/jquery.easyui.min.js"></script>  
<script type="text/javascript" src="$\{pageContext.request.contextPath\}/js/fromToJosn.js"></script>  
<link attr-id="easyuiTheme" rel="stylesheet" type="text/css" href="$\{pageContext.request.contextPath \}/js/easyui/themes/$\{themeStyle\}/easyui.css">  
<link rel="stylesheet" type="text/css" href="$\{pageContext.request.contextPath \}/js/easyui/themes/icon.css">  
<script type="text/javascript" src="$\{pageContext.request.contextPath \}/js/easyui/plugins/jquery.panel.js"></script>  
<script type="text/javascript" src="$\{pageContext.request.contextPath \}/js/easyui/plugins/jquery.combo.js"></script>  
<script type="text/javascript" src="$\{pageContext.request.contextPath \}/js/easyui/plugins/jquery.combobox.js"></script>  
<script type="text/javascript" src="$\{pageContext.request.contextPath \}/js/easyui/plugins/jquery.tabs.js"></script>  
<script type="text/javascript" src="$\{pageContext.request.contextPath \}/js/easyui/plugins/jquery.datagrid.js"></script>  
<script type="text/javascript" src="$\{pageContext.request.contextPath \}/js/easyui/plugins/jquery.tooltip.js"></script>  
<script type="text/javascript" src="$\{pageContext.request.contextPath \}/js/easyui/plugins/jquery.dialog.js"></script>  
</head>  
  
  
  
  
<script type="text/javascript">  
var dataX=\[\];// x轴数据  
var dataYOne=\[\];// y轴数据 销售额  
var dataYTwo=\[\];// y轴数据 盈利  
var dataYThree=\[\];// y轴数据 数量  
//以下代码初始化执行  
$(document).ready(function()\{  
  
//初始化表格样式  
$('\#dg').datagrid(\{    
rowStyler: function(index,row)\{  
//表格字体  
return 'font-size:50px;';  
\},  
onLoadSuccess:function(data)\{  
var row=data.rows;  
if(row!=""&&row!=null)\{  
for(var i in row)\{   
if(row\[i\].provName!=null && row\[i\].provName!="")\{  
dataX.push(row\[i\].provName);  
\}else\{  
dataX.push("省份(未知)");  
\}  
  
if(row\[i\].sales==null || row\[i\].sales=="")\{  
dataYOne.push(0);  
\}else\{  
dataYOne.push(row\[i\].sales);  
\}  
if(row\[i\].profit==null || row\[i\].profit=="")\{  
dataYTwo.push(0);  
\}else\{  
dataYTwo.push(row\[i\].profit);  
\}   
if(row\[i\].cnt==null || row\[i\].cnt=="")\{  
dataYThree.push(0);  
\}else\{  
dataYThree.push(row\[i\].cnt);  
\}  
\}  
\}  
test();  
\}  
\});   
  
$("\#supplierCode").combobox(\{  
onChange: function (n,o) \{  
var supplierCodes= n.join(",");  
$.ajax(\{  
type:"post",  
url:'$\{pageContext.request.contextPath \}/supplierMonthlySummaryTable/supplier',  
data:\{'supplierCodes':supplierCodes\},  
success:function(data)\{  
if(data!=null&&data!='')\{  
$('\#OperatorSid').combobox(\{       
   valueField:'channelCode',      
   textField:'operatorChannelName',  
   data:data  
\});   
\}  
\}  
\});  
  
  
\}  
  
  
\});  
\});  
  
  
//搜索加载数据  
function doSearch(formId, tableId)\{  
dataX=\[\];// x轴数据  
dataYOne=\[\];// y轴数据 销售额  
dataYTwo=\[\];// y轴数据 盈利  
dataYThree=\[\];// y轴数据 数量  
var userId = $('\#userId').combobox('getValues');  
var userIds=\[\];  
var o=0;  
  
if(userId.length>0)\{  
for(var i=0;i<userId.length;i++)\{  
  
if(userId\[i\]!='')\{  
userIds\[o\]=userId\[i\];  
o = o+1;  
\}  
\}  
\}  
$('\#userIds').val(userIds.join(',') );  
var supplierCode = $('\#supplierCode').combobox('getValues').join(',');  
$('\#supplierCodes').val(supplierCode);  
var OperatorSid = $('\#OperatorSid').combobox('getValues');  
$('\#OperatorSids').val(OperatorSid.join(','));  
  
var operatorCode=$("\#OperatorCode").combobox('getValues');  
if(OperatorSid.length>1 && operatorCode!='')\{  
EasyuiUtils.alertMsg("运营商和渠道信息不能同时选择！","提示");  
return;  
\}  
if(OperatorSid.length==1 && OperatorSid != '' && operatorCode != '')\{  
EasyuiUtils.alertMsg("运营商和渠道信息不能同时选择！","提示");  
return;  
\}  
  
var data = $("\#"+formId).serializeJson();  
var startTime=data.startTime;  
var endTime=data.endTime;  
if(startTime==''&&endTime!='')\{  
EasyuiUtils.alertMsg("开始时间不能为空！ ");  
return;  
\}  
if(endTime==''&&startTime!='')\{  
EasyuiUtils.alertMsg("结束时间不能为空！ ");  
return;  
\}  
var startNum = $.fn.datebox.defaults.parser(startTime);   
var endNum = $.fn.datebox.defaults.parser(endTime);   
    if (startNum > endNum) \{  
        EasyuiUtils.alertMsg("结束时间不能在开始时间之前！ ");  
return;   
    \}else\{  
   var day=DateDiff(endTime,startTime);  
   if(day>31)\{  
   EasyuiUtils.alertMsg("时间范围不能大于1个月 ！ ");  
   return;   
   \}  
    \}  
$('\#'+tableId).datagrid('load', data);  
\}  
  
  
//计算两个日期天数差的函数，通用  
  
function DateDiff(sDate1, sDate2) \{  //sDate1和sDate2是yyyy-MM-dd格式  
  
  
var aDate, oDate1, oDate2, iDays;  
aDate = sDate1.split("-");  
oDate1 = new Date(aDate\[1\] + '-' + aDate\[2\] + '-' + aDate\[0\]);  //转换为yyyy-MM-dd格式  
aDate = sDate2.split("-");  
oDate2 = new Date(aDate\[1\] + '-' + aDate\[2\] + '-' + aDate\[0\]);  
iDays = parseInt(Math.abs(oDate1 - oDate2) / 1000 / 60 / 60 / 24); //把相差的毫秒数转换为天数  
  
return iDays;  //返回相差天数  
\}  
  
  
$.extend($.fn.combobox.methods, \{  
   yearandmonth: function (jq) \{  
       return jq.each(function () \{  
           var obj = $(this).combobox();  
           var date = new Date();  
           var year = date.getFullYear();  
           var month = date.getMonth() + 1;  
           var table = $('<table>');  
           var tr1 = $('<tr>');  
           var tr1td1 = $('<td>', \{  
               text: '-',  
               click: function () \{  
                   var y = $(this).next().html();  
                   y = parseInt(y) - 1;  
                   $(this).next().html(y);  
               \}  
           \});  
           tr1td1.appendTo(tr1);  
           var tr1td2 = $('<td>', \{  
               text: year  
           \}).appendTo(tr1);  
  
  
           var tr1td3 = $('<td>', \{  
               text: '+',  
               click: function () \{  
                   var y = $(this).prev().html();  
                   y = parseInt(y) + 1;  
                   $(this).prev().html(y);  
               \}  
           \}).appendTo(tr1);  
           tr1.appendTo(table);  
  
  
           var n = 1;  
           for (var i = 1; i <= 4; i++) \{  
               var tr = $('<tr>');  
               for (var m = 1; m <= 3; m++) \{  
                   var td = $('<td>', \{  
                       text: n,  
                       click: function () \{  
                           var yyyy = $(table).find("tr:first>td:eq(1)").html();  
                           var cell = $(this).html();  
                           //var v = yyyy + '-' + (cell.length < 2 ? '0' + cell : cell);  
                           var v = yyyy +(cell.length < 2 ? '0' + cell : cell);  
                           obj.combobox('setValue', v).combobox('hidePanel');  
  
  
                       \}  
                   \});  
                   if (n == month) \{  
                       td.addClass('tdbackground');  
                   \}  
                   td.appendTo(tr);  
                   n++;  
               \}  
               tr.appendTo(table);  
           \}  
           table.addClass('mytable cursor');  
           table.find('td').hover(function () \{  
               $(this).addClass('tdbackground');  
           \}, function () \{  
               $(this).removeClass('tdbackground');  
           \});  
           table.appendTo(obj.combobox("panel"));  
  
  
       \});  
   \}  
\});  
  
  
  
  
//选中组织机构  
function  loadDep(node)\{  
$("\#orgCode").val(node.orgCode);  
\}  
  
  
var Common = \{  
  
  
   //EasyUI用DataGrid用日期格式化  
  DateFormatter: function (value, rec, index) \{  
   if (value == undefined) \{  
       return "";  
   \}  
   /\*json格式时间转js时间格式\*/  
   value = value.substr(1, value.length - 2);  
   var obj = eval('(' + "\{Date: new " + value + "\}" + ')');  
   var dateValue = obj\["rechargeDate"\];  
  \}  
\}  
   
//为Date类型拓展一个format方法，用于格式化日期  
Date.prototype.format = function (format) //author: meizz   
\{  
var o = \{  
    "M+": this.getMonth() + 1, //month   
    "d+": this.getDate(),    //day   
    "h+": this.getHours(),   //hour   
    "m+": this.getMinutes(), //minute   
    "s+": this.getSeconds(), //second   
    "q+": Math.floor((this.getMonth() + 3) / 3),  //quarter   
    "S": this.getMilliseconds() //millisecond   
\};  
if (/(y+)/.test(format))  
    format = format.replace(RegExp.$1,  
            (this.getFullYear() + "").substr(4 - RegExp.$1.length));  
for (var k in o)  
    if (new RegExp("(" + k + ")").test(format))  
        format = format.replace(RegExp.$1,  
                RegExp.$1.length == 1 ? o\[k\] :  
                    ("00" + o\[k\]).substr(("" + o\[k\]).length));  
return format;  
\};  
  
  
  
  
//easyUI datebox 格式化日期（含时间）  年-月-日  
$.fn.datebox.defaults.formatter = function (date) \{  
var y = date.getFullYear();  
var m = date.getMonth()+1;  
var d = date.getDate();  
return y + '-' + ( m<10 ? ('0'+m) : m) + '-' + ( d<10 ? ('0'+d) : d);  
\};   
$.fn.datebox.defaults.parser = function (s)\{  
if (!s) return new Date();  
var ss = (s.split('-'));  
var y = parseInt(ss\[0\],10);  
var m = parseInt(ss\[1\],10);  
var d = parseInt(ss\[2\],10);  
if (!isNaN(y) && !isNaN(m) && !isNaN(d))\{  
    return new Date(y,m-1,d);  
\} else \{  
    return new Date();  
\}  
\}   
//EasyUi datebox 汉化  
$.fn.datebox.defaults.currentText = '今天';    
$.fn.datebox.defaults.closeText = '关闭';     
$.fn.calendar.defaults.weeks = \['日','一','二','三','四','五','六'\];  
$.fn.calendar.defaults.months = \['一月','二月','三月','四月','五月','六月','七月','八月','九月','十月','十一月','十二月'\];  
  
//导出  
function exportExcel() \{  
//echarts图片导出  
var imgUrl=myChart.getDataURL("png");  
var userId = $('\#userId').combobox('getValues').join(',');  
$('\#userIds').val(userId);  
var supplierCode = $('\#supplierCode').combobox('getValues').join(',');  
$('\#supplierCodes').val(supplierCode);  
var OperatorSid = $('\#OperatorSid').combobox('getValues').join(',');  
$('\#OperatorSids').val(OperatorSid);  
var OperatorCode = $('\#OperatorCode').combobox('getValues');  
var data = $("\#frm").serializeJson();  
var startTime=data.startTime;  
var endTime=data.endTime;  
if(startTime==''&&endTime!='')\{  
EasyuiUtils.alertMsg("开始时间不能为空！ ");  
return;  
\}  
if(endTime==''&&startTime!='')\{  
EasyuiUtils.alertMsg("结束时间不能为空！ ");  
return;  
\}  
var startNum = $.fn.datebox.defaults.parser(startTime);   
var endNum = $.fn.datebox.defaults.parser(endTime);   
    if (startNum > endNum) \{  
        EasyuiUtils.alertMsg("结束时间不能在开始时间之前！ ");  
return;   
    \}else\{  
   var day=DateDiff(endTime,startTime);  
   if(day>31)\{  
   EasyuiUtils.alertMsg("时间范围不能大于一个月 ！ ");  
   return;   
   \}  
    \}  
/\* $('\#frm').attr("action","$\{pageContext.request.contextPath\}/belongingTopStatistics/exportTotalData");  
$("\#frm").submit(); \*/  
    $("\#h1").val(startTime);  
$("\#h2").val(endTime);  
$("\#h3").val(OperatorCode);  
$("\#h4").val(supplierCode);  
$("\#h5").val(OperatorSid);  
$("\#h6").val(imgUrl);  
$("\#h7").val(userId);  
document.exportReport.submit();  
\}  
  
//重置  
function reset()\{  
$('\#frm').form('clear');  
\}  
  
//得到当前日期  
formatterDate = function(date,type) \{  
var day = date.getDate() > 9 ? date.getDate() : "0" + date.getDate();  
var month = (date.getMonth() + 1) > 9 ? (date.getMonth() + 1) : "0"  
\+ (date.getMonth() + 1);  
  
if(type=='start')\{  
return date.getFullYear() + '-' + month + '-01';  
\}  
if(type=='end')\{  
return date.getFullYear() + '-' + month + '-' + day;  
\}  
  
  
\};  
//初始化查询条件中的日期  
window.onload = function() \{  
$('\#startTime').datebox('setValue', formatterDate(new Date(),"start"));  
$('\#endTime').datebox('setValue', formatterDate(new Date(),"end"));  
\};  
  
//初始化查询时    没有数据时的提示  
var myview = $.extend(\{\},$.fn.datagrid.defaults.view,\{  
   onAfterRender:function(target)\{  
       $.fn.datagrid.defaults.view.onAfterRender.call(this,target);  
       var opts = $(target).datagrid('options');  
       var vc = $(target).datagrid('getPanel').children('div.datagrid-view');  
       vc.children('div.datagrid-empty').remove();  
       if (!$(target).datagrid('getRows').length)\{  
           var d = $('<div class="datagrid-empty" style="font-size:15px;color:red"></div>').html(opts.emptyMsg || '当前条件下，未查询到数据！').appendTo(vc);  
           d.css(\{  
               position:'absolute',  
               left:0,  
               top:25,  
               width:'100%',  
               textAlign:'center'  
           \});  
       \}  
   \}  
   \});  
  
</script>  
<div class="easyui-layout" style="width:100%;height:50%" fit="false">  
<div data-options="region:'center'">  
  
<div id="tb" style="padding:10px;">  
<form action="$\{pageContext.request.contextPath\}/belongingTopStatistics/exportTotalData" method="post" name=exportReport style="display:none">  
<input id="h1" type='hidden' name='startTime'/>  
<input id="h2" type='hidden' name='endTime'/>  
<input id="h3" type='hidden' name='operatorCode'/>;  
<input id="h4" type='hidden' name='supplierCodes'/>;  
<input id="h5" type='hidden' name='OperatorSids'/>;  
<input id="h6" type='hidden' name='imgUrl'/>;  
<input id="h7" type="hidden" name="userIds">  
</form>  
  
<form id="frm">  
<div>  
<input type="hidden"  id="userIds" name="userIds">  
<input type="hidden"  id="supplierCodes" name="supplierCodes">  
<input type="hidden"  id="OperatorSids" name="OperatorSids">  
<span style="padding-left:5px;">&nbsp;&nbsp;&nbsp;&nbsp;客户信息:</span>  
<select class="easyui-combobox" id="userId" name="userId"   
style="width:150px;height:26px;" panelHeight="100px" data-options="editable:false, multiple:true">  
   <option value="">-- 请选择 --</option>  
   <c:forEach items="$\{platformUserInfo\}" var="gs">  
<option value="$\{gs.custId\}">$\{gs.customerName\}</option>  
</c:forEach>  
</select>  
<span style="padding-left:5px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;运营商:</span>  
<select class="easyui-combobox" id="OperatorCode" name="OperatorCode"   
class="easyui-combobox" style="width:150px;height:26px;" panelHeight="100px" data-options="editable:false">  
   <option value="">-- 请选择 --</option>  
   <c:forEach items="$\{Operator\}" var="gs">  
<option value="$\{gs.key\}">$\{gs.value\}</option>  
</c:forEach>  
</select>  
<span style="padding-left:5px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;供应商:</span>  
<select class="easyui-combobox" id="supplierCode" name="supplierCode"   
style="width:150px;height:26px;" panelHeight="100px" data-options="editable:false, multiple:true">  
   <option value="">-- 请选择 --</option>  
   <c:forEach items="$\{SupplierInfo\}" var="gs">  
<option value="$\{gs.sid\}">$\{gs.supplierName\}</option>  
</c:forEach>  
</select>  
<span style="padding-left:5px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;渠道信息:</span>  
<select class="easyui-combobox" id="OperatorSid" name="OperatorSid"   
style="width:150px;height:26px;" panelHeight="100px" data-options="editable:false, multiple:true">  
   <option value="">-- 请选择 --</option>  
   <c:forEach items="$\{OperatorChannel\}" var="gs">  
<option value="$\{gs.channelCode\}">$\{gs.operatorChannelName\}</option>  
</c:forEach>  
</select>  
  
</div>  
<div style="padding-top:5px;">  
<span style="padding-left:5px;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;日期 :&nbsp </span>  
<input type="text" name="startTime" id="startTime" class="easyui-datebox" />  
<span style="padding-left:5px;">至&nbsp</span>  
<input type="text" name="endTime" id="endTime"  class="easyui-datebox"/>  
  
<span style="padding-left:40%;">  
<span style="padding-left:100px;">  
<a href="javascript:void(0);" οnclick="doSearch('frm', 'dg');" class="easyui-linkbutton" iconCls="icon-search">查询</a>  
</span>  
<span style="padding-left:5px;">  
<a href="javascript:void(0);" οnclick="reset();" class="easyui-linkbutton" iconCls="icon-search">重置</a>  
</span>    
<span style="padding-left:50px;">  
<a href="javascript:void(0);" οnclick="exportExcel();" class="easyui-linkbutton" iconCls="icon-search">导出</a>  
</span>    
</span>    
</div>  
</div>  
  
  
  
<table id="dg" toolbar="\#tb"   
data-options="  title: '归属地充值统计',  
method: 'post',  
url: '$\{pageContext.request.contextPath \}/belongingTopStatistics/findList',  
pagination: 'true',  
pagePosition: 'bottom',  
pageNumber: 1,  
pageSize: '10',  
rownumbers: 'true',  
fit: 'true',  
view:myview,  
fitColumns: 'true',  
fixed: 'true',  
loadMsg: '加载中，请稍后..'">  
<thead>  
<th data-options="width:160, align:'center', field:'provName'">充值省份</th>  
<th data-options="width:160, align:'center', field:'cnt'">充值数量</th>  
<th data-options="width:160, align:'center', field:'sales'">销售额(元)</th>  
<th data-options="width:160, align:'center', field:'profit'">盈利额(元)</th>  
</thead>  
</table>  
</div>  
</div>  
  
<div id="childDiv" style="width: 100%; height: 50%;">加载echarts中...</div>  
<script type="text/javascript">  
function test()\{  
var myChart;  
//配置echarts路径  
require.config(\{    
   paths:\{     
       'echarts' : '$\{pageContext.request.contextPath\}/js/echarts'   
   \}    
\});  
//使用echarts加载输在柱状图   
   require(    
   \[    
       'echarts',    
       'echarts/theme/helianthus',  
       'echarts/chart/bar' // 使用柱状图就加载bar模块，按需加载    
   \],  
   loadEcharts  
);    
\}  
  
    function loadEcharts(ec,theme) \{    
    // 基于准备好的dom，初始化echarts图表    
        myChart= ec.init(document.getElementById("childDiv"),theme);  
          
     // 过渡  
        /\* myChart.showLoading(\{  
            text: '正在努力的读取数据中...',  
        \}); \*/  
        var option = \{  
        toolbox: \{  
                    show : true,  
                    feature : \{  
                        dataView : \{show: true, readOnly: false\},  
                        restore : \{show: true\},  
                        saveAsImage : \{show: true\}  
                    \}  
                \},  
                tooltip: \{  
                    show: true  
                \},  
                legend: \{  
                    data:\['销售额',"盈利",'数量'\]  
                \},  
                xAxis : \[  
                    \{  
                        type : 'category',  
                        data : dataX  
                         //data : \["衬衫","羊毛衫","雪纺衫","裤子","高跟鞋","袜子"\]  
                    \}  
                \],  
                yAxis : \[  
                    \{  
                        type : 'value'  
                    \}  
                \],  
                series : \[  
                    \{  
                        "name":"销售额",  
                        "type":"bar",  
                        "data":dataYOne  
                        //"data":\[5, 20, 40, 10, 10, 20\]  
                    \},  
                    \{  
                        "name":"盈利",  
                        "type":"bar",  
                        "data":dataYTwo  
                        //"data":\[25, 5, 10, 40, 20, 10\]  
                    \},  
                    \{  
                        "name":"数量",  
                        "type":"bar",  
                        "data":dataYThree  
                        //"data":\[25, 5, 10, 40, 20, 10\]  
                    \}  
                \]  
            \};  
     // 为echarts对象加载数据    
     myChart.clear();  
        myChart.setOption(option);     
    \}  
</script>

\--------------------------------------------------------------------------------------------------------------后台java代码-------------------------------------------------------------------------------------------------------

/\*\*  
\* @Description：导出  
\* @author: yys  
\* @since: 2016年10月26日 上午10:36:39  
\*/  
@RequestMapping("/exportTotalData")  
public void exportTotalDataList(HttpServletRequest request,HttpServletResponse response) throws Exception \{  
  
  
String\[\] sheetNames = \{ "belongingReport" \};  
String\[\] excleTitleMsg = \{  
"provName:充值省份",  
"cnt:充值数量(笔)",  
"sales:销售额(元)",  
"profit:盈利额(元)",  
\};  
Map<String,Object> paramsMap = getParameterMap(request);  
  
Object startTime=paramsMap.get("startTime");  
Object endTime=paramsMap.get("endTime");  
Object userIds=paramsMap.get("userIds");  
Object supplierCodes=paramsMap.get("supplierCodes");  
Object OperatorSids=paramsMap.get("OperatorSids");  
if(userIds!=null)\{  
userIds=userIds.toString().split(",");  
paramsMap.put("userIds", userIds);  
\}if(supplierCodes!=null)\{  
supplierCodes=supplierCodes.toString().split(",");  
paramsMap.put("supplierCodes", supplierCodes);  
\}if(OperatorSids!=null)\{  
OperatorSids=OperatorSids.toString().split(",");  
paramsMap.put("OperatorSids", OperatorSids);  
\}  
String nowTime="";  
if((startTime==null || startTime=="")&&(endTime==null || endTime==""))\{  
  
Date dt=new Date();//如果不需要格式,可直接用dt,dt就是当前系统时间  
DateFormat df = new SimpleDateFormat("yyyyMMdd");//设置显示格式  
nowTime= df.format(dt);  
String st=nowTime.substring(0, nowTime.length()-2);  
paramsMap.put("startTime", st+"01");  
paramsMap.put("endTime", nowTime);  
\}else\{  
paramsMap.put("startTime", startTime);  
paramsMap.put("endTime", endTime);  
\}  
String filter = assemblyDataViewSql(  
request, Code.TYPE\_STATISTIC, "CUSTOM\_MANAGER\_CUST\_ID", "ORG\_CODE");  
paramsMap.put("filter", filter);  
List<SupplierMonthlySummary>  list = supplierInfoService.findBelongingList(1, 9999, paramsMap).getList();  
  
if(list!=null&&list.size()>0)\{  
String OperatorSid\[\] = new String\[list.size()\];  
String provCode\[\] = new String\[list.size()\];  
for(int i=0;i<list.size();i++)\{  
OperatorSid\[i\] = list.get(i).getOperatorSid();  
provCode\[i\]=list.get(i).getProvCode();  
\}  
Map<String, Object> param=new HashMap<String, Object>();  
param.put("channelCodes", OperatorSid);  
param.put("provCodes", provCode);  
List<VirtualOperatorChannel> platformUserInfo=null;  
List<Province> province=null;  
if(OperatorSid.length>0||provCode.length>0)\{  
//获取客户信息  
try \{  
if(OperatorSid.length>0)\{  
platformUserInfo=virtualOperatorChannelService.findOperatorChannelName(param);  
for(VirtualOperatorChannel age:platformUserInfo)\{  
String cust=age.getChannelCode().toString();  
for(SupplierMonthlySummary li:list)\{  
if(cust.equals(li.getOperatorSid()))\{  
li.setOperatorName(age.getOperatorChannelName());  
\}  
\}  
  
\}  
\}  
if(provCode.length>0)\{  
province=provinceService.findProvName(param);  
for(Province age:province)\{  
String cust=age.getProvCode().toString();  
for(SupplierMonthlySummary li:list)\{  
if(cust.equals(li.getProvCode()))\{  
li.setProvName(age.getCnName());  
\}  
\}  
  
\}  
\}  
\} catch (Exception e) \{  
// TODO Auto-generated catch block  
e.printStackTrace();  
\}  
\}  
\}  
double sale=0;double profi=0;double cnt=0;  
for(SupplierMonthlySummary lis:list)\{  
if(lis.getSales()==null)\{  
lis.setSales(new BigDecimal(0.00));  
\}if(lis.getProfit()==null)\{  
lis.setProfit(new BigDecimal(0.00));  
\}if(lis.getCnt()==null)\{  
lis.setCnt("0.00");  
\}  
double nn=lis.getSales().doubleValue();  
double nb=lis.getProfit().doubleValue();  
double cn=Double.parseDouble(lis.getCnt());  
profi=profi+nb;  
sale=sale+nn;  
cnt=cnt+cn;  
\}  
SupplierMonthlySummary Summary=new SupplierMonthlySummary();  
Summary.setProvName("合计：");  
Summary.setSales(new BigDecimal(new java.text.DecimalFormat("\#.000").format(sale)));  
Summary.setProfit(new BigDecimal(new java.text.DecimalFormat("\#.000").format(profi)));  
Summary.setCnt(Double.toString(cnt));  
list.add(Summary);  
//echarce图片  
String imgUrl=(String) paramsMap.get("imgUrl");  
String fileName=request.getSession().getServletContext().getRealPath("/")+"echarts"+System.currentTimeMillis()+".png";  
System.out.println("呵呵"+fileName);  
Map<String,Object> imageSheets=new HashMap<String,Object>();  
System.out.println("标识:"+imgUrl);  
if(imgUrl!=null&&imgUrl!="")\{  
try \{  
       String\[\] url = imgUrl.split(",");  
       String u = url\[1\];  
       // Base64解码  
       byte\[\] b = new BASE64Decoder().decodeBuffer(u);  
       // 生成图片  
       OutputStream out = new FileOutputStream(new File(fileName));  
       out.write(b);  
       out.flush();  
       out.close();  
\} catch (Exception e) \{  
e.printStackTrace();  
\}  
\}  
  
ByteArrayOutputStream outStream = new ByteArrayOutputStream(); // 将图片写入流中  
BufferedImage bufferImg = ImageIO.read(new File(fileName));  
   ImageIO.write(bufferImg, "PNG", outStream); // 利用HSSFPatriarch将图片写入EXCEL  
    
   imageSheets.put("echarts", outStream);  
     
  
String filename = "belongingReport.xls";  
response.reset();  
request.setCharacterEncoding("UTF-8");  
response.setCharacterEncoding("UTF-8");  
response.setContentType("application/vnd.ms-excel");  
response.addHeader("Content-Disposition", "attachment; filename=\\"" + filename + "\\"");  
ExportExcelProvider.exportExcelByRslt(sheetNames, excleTitleMsg, list, response, null, imageSheets);  
\}

[Java]: http://lib.csdn.net/base/javase
[spring]: http://lib.csdn.net/base/javaee
[JavaScript]: http://lib.csdn.net/base/javascript
[jQuery]: http://lib.csdn.net/base/jquery