点击导出的按钮(超链接)
<a class="link myTopColor" id="exportData" href="javascript:;">导出excel</a>
$(function() {
//导出
$("#exportData").click(function() {
var myqx =document.getElementById("qxId").contentWindow.document.getElementById("myqx");
var myqxD = $(myqx).val();
var id = $("input[name='myHidId']").val();
var url = __ctx+'/xxx/xx/xxx/exportExcelDdrb.ht';
var form = $("<form>");
form.attr("style","display:none");
form.attr("target","_blank");
form.attr("method","post");
form.attr("action",url);
var input1 = $("<input>");
input1.attr("type","hidden");
input1.attr("name","id");
input1.attr("value",id);
var input2 = $("<input>");
input2.attr("type","hidden");
input2.attr("name","myqxD");
input2.attr("value",myqxD);
$("body").append(form);
form.append(input1);
form.append(input2);
form.submit();
form.remove();
/*
//open这种方式有个弊端,就是转的参数值不能太大,所以只能用jquery创建表单form在提交(ajax异步请求不会触发浏览器下载文件的功能)
var userAgent = navigator.userAgent; //取得浏览器的userAgent字符串
if (userAgent.indexOf("compatible") > -1 && userAgent.indexOf("MSIE") > -1) {//判断是否IE浏览器
window.location.href(url);
}else{
window.open(url,"调度日报");
} */
});
});
<%@page language="java" pageEncoding="UTF-8"%>
<%@include file="/commons/include/html_doctype.html"%>
<html>
<head>
<title>曲线</title>
<%@include file="/commons/include/get.jsp"%>
<script type="text/javascript" src="${ctx}/js/echarts/echarts.min.js"></script>
<style type="text/css">
</style>
</head>
<body>
<div id="app" style="width: 600px; height: 400px;"></div>
<script type="text/javascript">
// 基于准备好的dom,初始化echarts实例
var app = echarts.init(document.getElementById('app'));
var dataX = ${dataX}.split(",");
var dataFhygzj = ${dataFhygzj}.split(",");
var dataWgygzj = ${dataWgygzj}.split(",");
option = {
animation: false,
title : {
//text: '折线图堆叠'
text : ''
},
tooltip : {
trigger : 'axis'
},
legend : {
data : [ '全负荷', '网供负荷' ]
},
grid : {
left : '3%',
right : '4%',
bottom : '3%',
containLabel : true
},
toolbox : {
feature : {
saveAsImage : {}
}
},
xAxis : {
type : 'category',
boundaryGap : false,
data : dataX
},
yAxis : {
type : 'value'
},
series : [ {
name : '全负荷',
type : 'line',
stack : '总量1',
data : dataFhygzj
}, {
name : '网供负荷',
type : 'line',
stack : '总量2',
data : dataWgygzj
}
]
};
app.setOption(option);
$(function() {
//获取echarts图片的base64编码字符串 pixelRatio:图片精度 backgroundColor:背景颜色
var imgURL=app.getDataURL({
pixelRatio: 2,
backgroundColor: '#fff',
type: 'png'
});
/* var imgURL=app.getDataURL("png"); */
//alert(imgURL);
$("#myqx").val(imgURL);
});
</script>
<input type="hidden" name="myqx" id="myqx" >
</body>
</html>
@RequestMapping({ "exportExcelDdrb" })
@Action(description = "导出excel")
public void exportExcelDdrb(HttpServletRequest request, HttpServletResponse response) throws Exception {
String id = RequestUtil.getString(request, "id");
String imageMsg = RequestUtil.getString(request, "myqxD");
// 数据格式样例myqxD="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAABLAAAAMgCAYAAAAz4JsCAAAAAXNSR0IArs4c6QAAIABJREFUeF7s3Ql4VNX9xvFf2AImFGTfUdCgqKBAEYlp/WSv=";
//取得实体类数据
Hyddrb yjbb = new Hyddrb();
yjbb = (Hyddrb) hyddrbService.getById(Long.valueOf(Long.parseLong(id)));
String dirPath = FileUtil.getRootPath() + File.separator + "commons" + File.separator + "template" + File.separator + "hydlbbTemplate" + File.separator;
String fileName = "expHyTabDdrb.xls";
// String fileName = "expHyTabDdrb.xlsx";
FileInputStream inStream = new FileInputStream(new File(dirPath + fileName));
//xls2003版
HSSFWorkbook wb = new HSSFWorkbook(inStream);
this.drawImage(wb, imageMsg);
this.initSheetCell(wb, yjbb);
//xlsx2007版
// XSSFWorkbook wbxlsx = new XSSFWorkbook(inStream);
// this.drawImage_xlsx(wbxlsx, imageMsg);
// this.initSheetCell_xlsx(wbxlsx, yjbb);
fileName = "日报_" + new SimpleDateFormat("yyyy-MM-dd").format(yjbb.getRq()) ;
//其实两套代码都可以,最终结果以下面给定的后缀作为生成excel的后缀,即03版还是07版
fileName += ".xls";
// fileName += ".xlsx";
response.setContentType("application/excel");
response.addHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("gb2312"), "ISO8859-1"));
OutputStream out = response.getOutputStream();
wb.write(out);
// wbxlsx.write(out);
out.flush();
out.close();
}
/**
* @param wb 工作薄
* @param imageMsg 图片的base64加密报文,这个由echarts 中的js 获取 app.getDataURL()
* @throws Exception
* void 把页面传进来的 图片 画到excel 中去,这是xls版本,xlsx也差不多
*
*/
public void drawImage(HSSFWorkbook wb,String imageMsg) throws Exception{
HSSFSheet sheet = wb.getSheetAt(0);
// 创建绘图
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
BASE64Decoder base64Decoder = new BASE64Decoder();
ByteArrayOutputStream dataChartStringoutStream = new ByteArrayOutputStream();
ByteArrayInputStream dataChartStringin = new ByteArrayInputStream(base64Decoder.decodeBuffer(imageMsg.substring(22))); // 将picInfoByte作为输入流;
BufferedImage dataChartStringbufferImg = ImageIO.read(dataChartStringin); // 将dataChartStringin作为输入流,读取图片存入image中
ImageIO.write(dataChartStringbufferImg, "png", dataChartStringoutStream); // 利用HSSFPatriarch将图片写入EXCEL
// HSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)
// dx1 dy1 起始单元格中的x,y坐标.(一般设置为0即可)
// dx2 dy2 结束单元格中的x,y坐标(一般设置为0即可)
// col1,row1 指定起始的单元格,下标从0开始
// col2,row2 指定结束的单元格 ,下标从0开始
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 22, 2, (short) 37, 16);
anchor.setAnchorType(3);
patriarch.createPicture(anchor, wb.addPicture(dataChartStringoutStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
}
/**
* @param wb 工作薄
* @param imageMsg 图片的base64加密报文,这个由echarts 中的js 获取 app.getDataURL()
* @throws Exception
* void 把页面传进来的 图片 画到excel 中去,这是xls版本,xlsx也差不多
*
*/
public void drawImage_xlsx(XSSFWorkbook wb,String imageMsg) throws Exception{
XSSFSheet sheet = wb.getSheetAt(0);
//去掉标识22位,解码
byte[] img = Base64.decodeBase64(imageMsg.substring(22).getBytes());
//在工作表中画图
XSSFDrawing xssfDrawing = sheet.createDrawingPatriarch();
//后四个参数是: 前两个表示 左上角 列号、行号, 右下角 列号、行号
XSSFClientAnchor clientAnchor=new XSSFClientAnchor(0,0,0,0,22,2,37,16);
//根据指定位置来画图
xssfDrawing.createPicture(clientAnchor,wb.addPicture(img,XSSFWorkbook.PICTURE_TYPE_PNG));
}
/**
* @param wb 工作薄
* @param Hyddrb yjbb 实体类
* @throws Exception
* void 根据实体类的值,实例化,excel表格中的 数据
*
*/
public void initSheetCell(HSSFWorkbook wb, Hyddrb yjbb) throws Exception{
//获取子表数据
List<Hygxgdl> hygxgdlList = hyddrbService.getHygxgdlList(yjbb.getId());
// 数据不够,凑空的进去,填格式
hygxgdlList = hyddrbService.initGxgdl(hygxgdlList);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFCell cell = null;
// 数字或者字母组成,但是必须以字母开头
String pattern = "([a-zA-z])+(\\d|[a-zA-Z])*";
// 默认需要遍历excel 这么多行
int initTotleNub = 30;
// 预估要从excel插入的行数,比插入行数大就行,多个几十不成问题
// 遍历每行
for (int i = 1; i <= initTotleNub; i++) {
// 遍历每列
for (int j = 0; j <= 25; j++) {
Row row = sheet.getRow(i);
if (row != null) {
cell = (HSSFCell) row.getCell(j);
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
String tmp = cell.getStringCellValue();
boolean isMatch = Pattern.matches(pattern, tmp);
if ("sbr".equals(tmp)) {
cell.setCellValue("值班人员:" + yjbb.getSbr());
} else if ("dw".equals(tmp)) {
if (hygxgdlList != null && hygxgdlList.size() != 0) {
// 产生空行
// ExportExcelUtil.excelInsertRow(wb, sheet, i, hygxgdlList.size() - 1);
// 给空行 赋给子表的值
for (int a = (i + 1); a <= i + hygxgdlList.size(); a++) {// 遍历每行
Hygxgdl e = hygxgdlList.get(a - i - 1);
Row userRow = sheet.getRow(a - 1);
userRow.getCell(1).setCellValue(e.getDw());
userRow.getCell(2).setCellValue(e.getWgdl());
userRow.getCell(4).setCellValue(e.getSddl());
userRow.getCell(6).setCellValue(e.getFddl());
userRow.getCell(8).setCellValue(e.getGfdl());
userRow.getCell(10).setCellValue(e.getDfdygdl());
userRow.getCell(12).setCellValue(e.getSjgdl());
userRow.getCell(14).setCellValue(e.getWgfh20());
userRow.getCell(16).setCellValue(e.getZgwgfh());
userRow.getCell(18).setCellValue(e.getPjwgfh());
userRow.getCell(20).setCellValue(e.getWgfhl());
}
}
} else if (StringUtil.isNotEmpty(tmp) && isMatch&&!"MWH".equals(tmp)&&!"MW".equals(tmp)) {
this.setCellValTwo(yjbb, cell, tmp, wb);
}
}
}
}
}
}
/**
* @param wb 工作薄
* @param Hyddrb yjbb 实体类
* @throws Exception
* void 根据实体类的值,实例化,excel表格中的 数据
*
*/
public void initSheetCell_xlsx(XSSFWorkbook wb, Hyddrb yjbb) throws Exception{
//获取子表数据
List<Hygxgdl> hygxgdlList = hyddrbService.getHygxgdlList(yjbb.getId());
// 数据不够,凑空的进去,填格式
hygxgdlList = hyddrbService.initGxgdl(hygxgdlList);
XSSFSheet sheet = wb.getSheetAt(0);
XSSFCell cell = null;
// 数字或者字母组成,但是必须以字母开头
String pattern = "([a-zA-z])+(\\d|[a-zA-Z])*";
// 默认需要遍历excel 这么多行
int initTotleNub = 30;
// 预估要从excel插入的行数,比插入行数大就行,多个几十不成问题
// 遍历每行
for (int i = 1; i <= initTotleNub; i++) {
// 遍历每列
for (int j = 0; j <= 25; j++) {
Row row = sheet.getRow(i);
if (row != null) {
cell = (XSSFCell) row.getCell(j);
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING);
String tmp = cell.getStringCellValue();
boolean isMatch = Pattern.matches(pattern, tmp);
if ("sbr".equals(tmp)) {
cell.setCellValue("值班人员:" + yjbb.getSbr());
} else if ("dw".equals(tmp)) {
if (hygxgdlList != null && hygxgdlList.size() != 0) {
// 产生空行
// ExportExcelUtil.excelInsertRow(wb, sheet, i, hygxgdlList.size() - 1);
// 给空行 赋给子表的值
for (int a = (i + 1); a <= i + hygxgdlList.size(); a++) {// 遍历每行
Hygxgdl e = hygxgdlList.get(a - i - 1);
Row userRow = sheet.getRow(a - 1);
userRow.getCell(1).setCellValue(e.getDw());
userRow.getCell(2).setCellValue(e.getWgdl());
userRow.getCell(4).setCellValue(e.getSddl());
userRow.getCell(6).setCellValue(e.getFddl());
userRow.getCell(8).setCellValue(e.getGfdl());
userRow.getCell(10).setCellValue(e.getDfdygdl());
userRow.getCell(12).setCellValue(e.getSjgdl());
userRow.getCell(14).setCellValue(e.getWgfh20());
userRow.getCell(16).setCellValue(e.getZgwgfh());
userRow.getCell(18).setCellValue(e.getPjwgfh());
userRow.getCell(20).setCellValue(e.getWgfhl());
}
}
} else if (StringUtil.isNotEmpty(tmp) && isMatch&&!"MWH".equals(tmp)&&!"MW".equals(tmp)) {
this.setCellValTwo_xlsx(yjbb, cell, tmp, wb);
}
}
}
}
}
}
private void setCellValTwo(Hyddrb entity, HSSFCell cell, String tmp,HSSFWorkbook wb ) throws Exception {
// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Method method = entity.getClass().getMethod("get" + StringUtil.makeFirstLetterUpperCase(tmp));
Object o = method.invoke(entity);
String val = null;
if (o == null) {
val = "";
} else if (o instanceof String || o instanceof Long || o instanceof Integer || o instanceof Short) {
val = method.invoke(entity) + "";
} else if (o instanceof Double) {
val = method.invoke(entity) + "";
if (val.endsWith(".0")) {
val = val.replace(".0", "");
}
} else if (o instanceof Date) {
val = sdf.format((Date) method.invoke(entity));
}
cell.setCellValue(val);
cell.getCellStyle().setWrapText(true);// 自动换行,没有效果
}
private void setCellValTwo_xlsx(Hyddrb entity, XSSFCell cell, String tmp,XSSFWorkbook wb ) throws Exception {
// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Method method = entity.getClass().getMethod("get" + StringUtil.makeFirstLetterUpperCase(tmp));
Object o = method.invoke(entity);
String val = null;
if (o == null) {
val = "";
} else if (o instanceof String || o instanceof Long || o instanceof Integer || o instanceof Short) {
val = method.invoke(entity) + "";
} else if (o instanceof Double) {
val = method.invoke(entity) + "";
if (val.endsWith(".0")) {
val = val.replace(".0", "");
}
} else if (o instanceof Date) {
val = sdf.format((Date) method.invoke(entity));
}
cell.setCellValue(val);
cell.getCellStyle().setWrapText(true);// 自动换行,没有效果
}
java代码
模板截图
效果截图
相关说明:
其中excel是自己制作好的 导出模板,上面格子存有java实体类的属性名,读取excel后判断是那些属性,通过反射机制获取值在set到excel格子
echarts前端生成的图片base64加密报文,传到后台 java解密后,把它写进excel
遇到的问题A:
导出的图表仅仅是空的,没有数据
解决方案1:取消 图表里面动画效果,就是在 option 中添加一个 animation: false 属性 (这个方便,我就用这个)
解决方案2:延迟 相机的 拍照时间,即生成的图片要时间,可以使用setTimeout(function () {xxx}, 1000),延迟一下在获取
参考链接:https://blog.csdn.net/WWW_share8/article/details/103679701?spm=1001.2101.3001.4242.4&utm_relevant_index=8
遇到的问题B:
使用windows的open可以打开浏览器下载文件,但是异步的ajax不行
解决方案:使用jq直接生成一个隐藏的form表单,然后添加需要传参的控件,最后提交即可