发布时间：2022-08-10 文章分类：编程知识投稿人：王小丽字号：默认 | 大 | 超大打印

前言
一、使用highcharts前后端交互展示图表，及使用报表导出
二、实现思路
- - 1.图表可以选择echarts、highcharts，我们这里选择highcharts（echarts使用差不多），使用很简洁方便，并自带导出图表、打印等功能。
  - 2.报表导出我们选择apache的HSSFWorkbook进行报表导出。
三、实现步骤
- 1.ElementUI代码
- 2.js代码：
  - - 2.1 首先要使用mounted钩子函数挂载调用图表及报表方法：
    - 2.2 然后要创建methods调用图表方法及报表方法
    - 2.4 着重要讲一点：下面是直接在报表多生成一行并计算总票数
    - 2.5 导出报表方法
- 3.后端HSSFWorkbook的java代码
四、最后的页面如下：

提示：本文仅供学习交流，请勿用于非法活动！

前言

本文大概内容：
在官网展现图表及报表（含导出）

一、使用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 id="container11"></div>
						</div>
					</el-col>
				</el-row>
				<el-row :gutter="20">
					<el-col :span="16">
						<div  class="grid-content bg-purple-light">
							<div id="container13"></div>
						</div>
					</el-col>
					<el-col :span="1"><div>&nbsp;</div></el-col>
					<el-col :span="6">
						<div class="grid-content bg-purple-light">
							<div>
								<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>&nbsp;</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>{point.key}</span><table>',
								pointFormat: '<tr><td>{series.name}: </td>' +
										'<td><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);
	}
}

highcharts图表及报表展示、导出数据

前言