注意:1、示例仅供参考,若是和实际场景不相同,有可能需要自行进一步调整代码。
2、如果图片显示边框丢失,请在电子表格报表丢失的边框附近单元格(一般是在最右侧以及最下侧的单元格中添加个空白格)添加个空白格以规避该问题。
场景: 报表导出为excel附件,且正文中嵌入导出的报表图片都通过邮件发送。
实现思路:通过计划中的【定制】写JAVA代码实现。
效果如下:
操作步骤:
1、在Excel中创建电子表格报表。
2、在【定制管理】--》【计划任务】创建任务,【任务类型】选择:“定制”
JAVA代码如下:
importPackage(Packages.smartbi.sdk.service.spreadsheetreport); importPackage(Packages.smartbi.scheduletask.task); importPackage(Packages.smartbi.sdk.service.systemconfig); importPackage(Packages.java.lang); importPackage(Packages.java.util); importPackage(Packages.java.text); importPackage(Packages.java.io); importPackage(Packages.org.apache.commons.lang); importPackage(Packages.org.apache.commons.mail); importPackage(Packages.smartbi.scheduletask.component); //报表ID var reportID = 'I4028818a015fde20de20c929015fe2d219171df8'; //收件人邮箱,多个时,用英文分号隔开(;) var mailList = "test@smartbi.com.cn;test11@smartbi.com.cn"; //邮件的内容 var mailText = "这是一个系统自动发送的邮件,所含附件是您订阅的报表"; var report = null; //定义email对象,初始化参数 var multiPartEmail = new SmartbiMultiPartEmail(); var systemConfigService = new SystemConfigService(connector); var configList = systemConfigService.getSystemConfigs(); var mailServer = null; var fromAddress = null; var userName = null; var password = null; var emailSSLEnabled = null; var emailTLSEnabled = null; var port = ""; for (var i = 0; i < configList.size(); i++) { var config = configList.get(i); if (config!=null) { if (config.getKey().equals("EMAIL_SMTP_SERVER")) { mailServer = config.getValue(); } else if (config.getKey().equals("EMAIL_USER_NAME")) { userName = config.getValue(); } else if (config.getKey().equals("EMAIL_USER_PASSWORD")) { password= config.getValue(); } else if (config.getKey().equals("EMAIL_SSL_ENABLED")) { if (config.getValue().equals("true")) { emailSSLEnabled = true; } } else if (config.getKey().equals("EMAIL_TLS_ENABLED")) { if (config.getValue().equals("true")) { emailTLSEnabled = true; } }else if (config.getKey().equals("EMAIL_SMTP_PORT")) { //端口 port = config.getValue().trim(); } } } multiPartEmail.setHostName(mailServer); multiPartEmail.setAuthentication(userName, password); multiPartEmail.setFrom(fromAddress); if(emailSSLEnabled){ multiPartEmail.setSSL(true); if(port != ""){ multiPartEmail.setSslSmtpPort(port); } } if(emailTLSEnabled){ multiPartEmail.setTLS(true); } if(port != "" && !emailSSLEnabled){ multiPartEmail.setSmtpPort(port); } multiPartEmail.setCharset("GBK");//邮件内容字符集 multiPartEmail.setSubject("测试邮件");//邮个把标题 report = new SSReport(connector); report.open(reportID); //报表资源ID //报表参数设置 var reportParamSetting = [ //{id:"OutputParameter.I2c94ea86296db80801296e0be950015a.用户组",value:"DEPARTMENT",displayValue:"根组"}, //{id:"OutputParameter.I2c94ea86296db80801296e0be950015a.用户",value:"ADMIN",displayValue:"管理员"} ]; //report.setParamValue("OutputParameter.I2c94ea86296db80801296e0be950015a.用户组", "DEPARTMENT", "根组");//设置报表的参数默认值 //report.setParamValue("OutputParameter.I2c94ea86296db80801296e0be950015a.用户", "ADMIN", "管理员");//设置报表的参数默认值 var pngFile = File.createTempFile("emailtask",".png"); var os = new FileOutputStream(pngFile); report.doExport("PNG","","",os,"","",""); os.flush(); os.close(); report.close(); var openReportOutput = execute('openResource', { connector: connector, reportId: reportID }); var enumerateParamValuesOutput = execute('enumerateParamValues', { resourceHandle: openReportOutput.resourceHandle, byEveryParam: false, paramsSetting: reportParamSetting }); var exportReportOutput = execute('exportResource', { connector: connector, resourceHandle: openReportOutput.resourceHandle, paramSettingIterator: enumerateParamValuesOutput.getParamValues(), exportSetting: {"delimiter":"","pathId":"","fileNameSufParams":true}, taskName: taskName, exportType: "EXCEL2007" }); openReportOutput.resourceHandle.close(); var outputFile = exportReportOutput.file; var sendToMailOutput = execute('sendToMail', { connector: connector, taskName: taskName, paramValueMap: null, files: [outputFile,pngFile], sendSetting: {"mailList":mailList,"doZip":false,"title":"%TASKNAME_%DATE%TIME","text":mailText,"picInMail":true} });
注:设置参数默认值可参考以下方式: