@(文章目錄) 提示：本文僅供學習交流，請勿用於非法活動！ 前言 本文大概內容： 在官網展現圖表及報表（含導出） 一、使用highcharts前後端交互展示圖表，及使用報表導出 如下圖，我們在首頁如何將折線圖、柱狀圖結合報表，並實現根據不同的倉庫實時刷新不同的數據，最後可以選擇導出圖表及報表。 二、 ...
@
目錄提示：本文僅供學習交流，請勿用於非法活動！
前言
本文大概內容：
在官網展現圖表及報表（含導出）
一、使用highcharts前後端交互展示圖表，及使用報表導出
如下圖，我們在首頁如何將折線圖、柱狀圖結合報表，並實現根據不同的倉庫實時刷新不同的數據，最後可以選擇導出圖表及報表。
二、實現思路
1.圖表可以選擇echarts、highcharts，我們這裡選擇highcharts（echarts使用差不多），使用很簡潔方便，並自帶導出圖表、列印等功能。
2.報表導出我們選擇apache的HSSFWorkbook進行報表導出。
三、實現步驟
1.ElementUI代碼
我們使用
代碼如下：
// 前面公司的搜索框省略....
<el-row>
<el-col :span="24">
<div class="grid-content bg-purple-dark">
<div style="height: 270px" id="container11"></div>
</div>
</el-col>
</el-row>
<el-row :gutter="20">
<el-col :span="16">
<div class="grid-content bg-purple-light">
<div style="height: 300px" id="container13"></div>
</div>
</el-col>
<el-col :span="1"><div> </div></el-col>
<el-col :span="6">
<div class="grid-content bg-purple-light">
<div style="height: 320px">
<el-table ref="table" :data="tableData" border size="mini"
:summary-method="getSummaries" show-summary>
<el-table-column prop="twoTypeName" label="菜類名稱" width="125px" height="10px"></el-table-column>
<el-table-column prop="quantity" label="數量" width="145px" height="10px"></el-table-column>
<el-table-column prop="tickets" label="小票數" width="130px" height="10px"></el-table-column>
</el-table>
</div>
</div>
</el-col>
<el-col :span="1"><div> </div></el-col>
</el-row>
2.js代碼：
2.1 首先要使用mounted鉤子函數掛載調用圖表及報表方法：
mounted() {
this.moreChart1();
this.moreChart3();
this.getDayTicketsQuantity();
},
2.2 然後要創建methods調用圖表方法及報表方法
methods: {
// 折線圖方法
moreChart1(warehouseid) {
// 這裡帶上倉庫id，到時就可以根據不同倉庫切換數據
var param = {"warehouseid":warehouseid};
axios.get("url1",{params:param})
.then(function (r) {
var list = r.data.data;
console.log(list);
let titles = [];
let values = [];
// 將後端獲得的數據放入折線圖的下麵X、Y軸定義的變數中
for(let i=0;i<list.length;i++){
titles[i] = list[i].createDay;
values[i] = list[i].totalDayTickets;
}
var chart = Highcharts.chart('container11', {
chart: {
type: 'line'
},
title: {
text: '每日小票總數'
},
subtitle: {
text: '每日小票總數'
},
xAxis: {
categories: titles
},
yAxis: {
title: {
text: '小票數(張)'
}
},
plotOptions: {
line: {
dataLabels: {
// 開啟數據標簽
enabled: true
},
// 關閉滑鼠跟蹤，對應的提示框、點擊事件會失效
enableMouseTracking: true
}
},
series: [{
name: '',
data: values
},]
});
})
},
// 柱狀圖方法
moreChart3(warehouseid) {
var param = {"warehouseid":warehouseid};
axios.get("url2",{params:param})
.then(r=>{
var list = r.data.data;
console.log(list);
let titles = [];
let values = [];
for(let i=0; i<list.length;i++){
titles[i] = list[i].twoTypeName;
values[i] = list[i].tickets;
}
var chart = Highcharts.chart('container13',{
chart: {
type: 'column'
},
title: {
text: '今日小票數'
},
subtitle: {
text: '今日小票數'
},
xAxis: {
categories: titles,
crosshair: true
},
yAxis: {
min: 0,
title: {
text: '小票數(張)'
}
},
tooltip: {
// head + 每個 point + footer 拼接成完整的 table
headerFormat: '<span style="font-size:10px">{point.key}</span><table>',
pointFormat: '<tr><td style="color:{series.color};padding:0">{series.name}: </td>' +
'<td style="padding:0"><b>{point.y:1f}</b></td></tr>',
footerFormat: '</table>',
shared: true,
useHTML: true
},
plotOptions: {
column: {
borderWidth: 0
}
},
series: [{
name: '',
data: values
}]
});
})
},
// 報表方法
getDayTicketsQuantity(warehouseid){
this.doGetData(url3,{warehouseid:warehouseid},function (r) {
if(r.success){
this.tableData = r.data;
}
})
},
// 這裡會根據頁面上選擇不同的倉庫後觸發該方法，圖表和報表數據就會跟著改變
getChangeData(warehouseid){
this.moreChart1(warehouseid);
this.moreChart3(warehouseid);
this.getDayTicketsQuantity(warehouseid);
},
}
});
2.4 著重要講一點：下麵是直接在報表多生成一行並計算總票數
getSummaries: function (param) {
const { columns, data } = param;
const sums = [];
columns.forEach((column, index) => {
var flag = false;
if(index === 0){
sums[index] = '合計';
return;
}
if( index != 2){
sums[index] = '';
return;
}
const values = data.map(item => Number(item[column.property]));
if(!values.every(value => isNaN(value))){
sums[index] = values.reduce((prev,curr) => {
const value = Number(curr);
if(!isNaN(value)){
return prev + curr;
} else {
return prev;
}
}, 0);
}
if(index === 2){
sums[index] = sums[index].toFixed(0);
}
});
return sums;
},
2.5 導出報表方法
function ticketsQuantityExportExcel(){
var str="";
if(app.filterParams.warehouseid!=null){
str+="warehouseid="+app.filterParams.warehouseid;
}
window.location.href = "url3"+str;
}
3.後端HSSFWorkbook的java代碼
@RequestMapping("/ticketsQuantityExportExcel")
@ResponseBody
public void ticketsQuantityExportExcel(HttpServletResponse response,String warehouseid){
try{
String docName = "今日小票及數量";
OutputStream outputStream = response.getOutputStream();
response.setContentType("application/vnd_ms-excel");
response.setHeader("Content-disposition","attachment;filename="
+new String(docName.getBytes("gb2312"),"ISO8859-1")+".xls");
List<DayTicketsQuantityDto> list = reportService.listDayTicketsQuantity(warehouseid);
ticketsQuantityExcelOutPut(list,outputStream,docName);
outputStream.flush();
outputStream.close();
} catch (Exception e) {
logger.error("導出失敗！params:" + warehouseid, e);
}
}
public void ticketsQuantityExcelOutPut(List<DayTicketsQuantityDto> list, OutputStream ouputStream,
String docName) throws IOException {
// 初始化表格及字體邊框
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(docName);
HSSFRow row = null;
HSSFCell cell = null;
sheet.setDefaultRowHeightInPoints(20);
row = sheet.createRow((int) 0);
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font1 = wb.createFont();
font1.setFontName("宋體");
font1.setFontHeightInPoints((short) 14);
font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下邊框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左邊框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上邊框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右邊框
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
style.setFont(font1);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font2 = wb.createFont();
font2.setFontName("宋體");
font2.setFontHeightInPoints((short) 12);
HSSFCellStyle ji = wb.createCellStyle();
ji.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
ji.setFillForegroundColor(IndexedColors.WHITE.getIndex());
ji.setAlignment(HSSFCellStyle.ALIGN_CENTER);
ji.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
ji.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下邊框
ji.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左邊框
ji.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上邊框
ji.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右邊框
ji.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
ji.setFont(font2);
// 增加表格表頭倉庫名稱及報表日期（報表裡面沒有）
DayTicketsQuantityDto dto = list.get(0);
if(dto!=null){
row = sheet.createRow((int)0);
cell = row.createCell(0);
cell.setCellValue("倉庫名稱");
cell.setCellStyle(style);
sheet.autoSizeColumn(0);
sheet.setColumnWidth(0,20*356);
cell = row.createCell(1);
cell.setCellValue(dto.getWarehouseName());
cell.setCellStyle(style);
sheet.autoSizeColumn(0);
sheet.setColumnWidth(0, 20 * 356);
row = sheet.createRow((int) 1);
cell = row.createCell(0);
cell.setCellValue("報表日期");
cell.setCellStyle(style);
sheet.autoSizeColumn(0);
sheet.setColumnWidth(0, 20 * 356);
cell = row.createCell(1);
cell.setCellValue(dto.getCreateDay());
cell.setCellStyle(style);
sheet.autoSizeColumn(0);
sheet.setColumnWidth(0, 20 * 356);
}
// 正式構建表格正文表格並將查詢的數據填充進去
String[] excelHeader = {"菜類名稱","數量","小票數"};
row = sheet.createRow((int)2);
for (int i = 0; i < excelHeader.length; i++) {
cell = row.createCell(i);
cell.setCellValue(excelHeader[i]);
cell.setCellStyle(style);
sheet.autoSizeColumn(i);
sheet.setColumnWidth(i, 20 * 356);
}
if (list != null && list.size() > 0) {
Integer totalTickets = 0;
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i + 3);
DayTicketsQuantityDto trn = list.get(i);
if (trn != null) {
HSSFCell cell1 = row.createCell(0);
if (trn.getTwoTypeName() != null) {
cell1.setCellValue(trn.getTwoTypeName());
}
HSSFCell cell2 = row.createCell(1);
if (trn.getQuantity() != null) {
cell2.setCellValue(Integer.valueOf(trn.getQuantity()));
}
HSSFCell cell3 = row.createCell(2);
if (trn.getTickets() != null) {
cell3.setCellValue(Integer.valueOf(trn.getTickets()));
totalTickets += Integer.valueOf(trn.getTickets());
}
cell1.setCellStyle(ji);
cell2.setCellStyle(ji);
cell3.setCellStyle(ji);
}
}
// 增加末行合計一行
row = sheet.createRow(list.size() + 3);
HSSFCell cell1 = row.createCell(0);
cell1.setCellValue("合計");
HSSFCell cell2 = row.createCell(1);
cell2.setCellValue("");
HSSFCell cell3 = row.createCell(2);
cell3.setCellValue(totalTickets);
cell1.setCellStyle(ji);
cell2.setCellStyle(ji);
cell3.setCellStyle(ji);
}
wb.write(ouputStream);
}
}
四、最後的頁面如下：
隨心所往，看見未來。Follow your heart，see night！
歡迎點贊、關註、留言，一起學習、交流！
