/**
* @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">
<!-- for web2.0 using EasyUI -->
<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;"> 客户信息:</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;"> 运营商:</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;"> 供应商:</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;"> 渠道信息:</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;"> 日期 : </span>
<input type="text" name="startTime" id="startTime" class="easyui-datebox" />
<span style="padding-left:5px;">至 </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>
<!-- echarts图表 -->
<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);
}