欢迎使用Auto email
关于Auto email
Auto email是一个 使用Java 实现的自动化读取MySQL数据表单发送电子邮件的工具。你正在阅读的是开发版本的文档。
用户指南
此处暂略:
- 项目依赖jar下载。
- 新建maven项目,配置Spring等。
- …
快速开始
此处提供了一个很好的 Auto email 样例介绍,并假定你已经下载好了相关依赖,并搭建一个简单的Maven Spring项目。
最简单的一个邮件
1.项目pom文件中的依赖部分:
<!-- 引入下载到本地的依赖jar包 -->
<dependency>
<groupId>com.autoemail.dmdsp</groupId>
<artifactId>mail</artifactId>
<version>1.0.0</version>
</dependency>
<dependency>
<groupId>com.autoemail.dmdsp</groupId>
<artifactId>common</artifactId>
<version>1.0.0</version>
</dependency>
<dependency>
<groupId>com.autoemail.dmdsp</groupId>
<artifactId>chart</artifactId>
<version>1.0.0</version>
</dependency>
<dependency>
<groupId>com.autoemail.dmdsp</groupId>
<artifactId>service</artifactId>
<version>1.0.0</version>
</dependency>
<dependency>
<groupId>com.autoemail.dmdsp</groupId>
<artifactId>excel</artifactId>
<version>1.0.0</version>
</dependency>
2.配置文件application.properties内容:
邮件标题、邮件收件人列表(多个收件人使用“;”分隔)、抄送列表、测试收件人列表、邮件正文html模板文件、数据表单查询sql(多个表单使用“|”分隔;查询sql可接收传递日期参数,日期格式为yyyy-MM-dd,日期值为main函数传递值,如:select * from table where createtime = ?;):
mail.title=test
mail.list=1145840118@qq.com;1145840118@qq.com
mail.cc.list=1145840118@qq.com
mail.test.list=1145840118@qq.com
mail.template.path=mail_template.html
query.sql=select '1'\\, '2'\\, '3' union all select '4'\\, '5'\\, '6';
3.邮件模板mail_template.html文件内容:
新版本1.0.1
模板中aData、bData、cData…对应query.sql中查询数据(多个查询SQL使用“|”分隔)
<div>各位好:</div>
<div>@procDate 这是一个测试邮件,数据皆为测试数据:</div>
<div class="data">
<div>
<table border="1" cellspacing="0" cellpadding="0" style="width:300px;">
<thead>
<tr>
<th colspan="1" rowspan="1" style="width:100px">列1</th>
<th colspan="1" rowspan="1" style="width:100px">列2</th>
<th colspan="1" rowspan="1" style="width:100px">列3</th>
</tr>
</thead>
<tbody>
{% for value1 in aData %}
<tr>
{% for value2 in value1 %}
<td> {{ value2 }} </td>
{% endfor %}
</tr>
{% endfor %}
</tbody>
</table>
</div>
</div>
老版本1.0.0:
<div>各位好:</div>
<div>@procDate 这是一个测试邮件,数据皆为测试数据:</div>
<div class="data">
<div>
<table border="1" cellspacing="0" cellpadding="0" style="width:300px;">
<thead>
<tr>
<td colspan="1" rowspan="2" style="width:100px">列1</td>
<td colspan="1" rowspan="2" style="width:100px">列2</td>
<td colspan="1" rowspan="2" style="width:100px">列3</td>
</tr>
</thead>
<!-- 此处为数据替换符,@aData、@bData、@cData.. 对应配置文件中查询sql表单数据-->
@aData
</table>
</div>
<div class="clear"></div>
</div>
4.applicationContext-spring.xml配置内容(数据库连接的配置):
<context:component-scan base-package="com.autoemail.bigdata.common.mysql.impl"/>
<!-- 配置属性加载器 -->
<bean id="propertyConfigurer"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<list>
<value>classpath:application.properties</value>
<!-- 数据库连接配置,自定义文件路径 -->
<value>file:C:/xxx/testdb.properties</value>
</list>
</property>
</bean>
<!-- 数据源配置, 使用应用中的DBCP数据库连接池 -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<!-- Connection Info -->
<property name="driverClassName" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<!-- Connection Pooling Info -->
<property name="maxActive" value="${dbcp.maxActive}"/>
<property name="maxIdle" value="${dbcp.maxIdle}"/>
<property name="defaultAutoCommit" value="true"/>
<!-- 连接Idle一个小时后超时 -->
<property name="timeBetweenEvictionRunsMillis" value="3600000"/>
<property name="minEvictableIdleTimeMillis" value="3600000"/>
</bean>
5.实现代码样例:
邮件类(需重写isReady方法,判断MySQL依赖数据是否到位,默认return true):
新版本1.0.1:
public class YourMailTableService extends JtwigMailTableService {
private static final Logger logger = Logger.getLogger(JtwigTestMailTableService.class);
public JtwigTestMailTableService(String procDate, String receiverGroup) {
this.procDate = new DateTime(procDate);
this.receiverGroup = receiverGroup;
}
//判断数据是否到位,是否可以发送报表
@Override
public boolean isReady() {
/*String proc = procDate.toString("yyyy-MM-dd");
boolean rs = mailStatDao.queryDateByDay("SELECT COUNT(1) " +
"FROM data_table WHERE date = ? ", proc) > 0;
if(rs){
logger.info("数据已到位!");
}
else {
logger.error("data_table 表数据未到齐!");
exit(1);
}
return rs;*/
return true;
}
}
老版本1.0.0:
public class YourMailTableService extends MailTableService {
private static final Logger logger = Logger.getLogger(YourMailTableService.class);
public YourMailTableService(String procDate, String receiverGroup) {
this.procDate = new DateTime(procDate);
this.receiverGroup = receiverGroup;
}
//判断数据是否到位,是否可以发送报表
@Override
public boolean isReady() {
String proc = procDate.toString("yyyy-MM-dd");
boolean rs = mailStatDao.queryDateByDay("SELECT COUNT(1) " +
"FROM test_table WHERE date = ? ", proc) > 0;
if(rs){
logger.info("数据已到位!");
}
else {
logger.error("test_table表数据未到齐!");
}
return rs;
}
主函数:
public static void main(String[] args) {
logger.info("main函数开始。。。");
String procDate = null;
String receiverGroup = null;
if(args.length == 2){
procDate = args[0];
receiverGroup = args[1];
logger.info(String.format("配置参数:procDate = %s receiverGroup = %s", procDate, receiverGroup));
}
else {
logger.error("参数个数不对,应传入两个参数(执行日期,收件组类型)!");
exit(1);
}
YourMailTableService mailTableService = new YourMailTableService(procDate, receiverGroup);
//邮件发送(展示)
boolean rs = mailTableService.doDisplay();
StringBuilder errorLogs = mailTableService.getErrorLogs();
if(errorLogs.length() > 0){
//存在异常错误信息
System.out.print(errorLogs.toString());
exit(1);
}
System.out.print(rs);
}
6.主函数执行效果(接收到一封邮件):
main函数中传入两个参数,第一个参数为邮件发送日期,第二个参数为收件箱配置(正式收件人+抄送 或 测试收件箱)
邮件正文的渲染(1.0.0)
配置文件中SQL语句的配置:
注:
配置文件中“,”需要转义;
多个表单查询sql使用“|”分隔;
*查询表单中有需要颜色渲染时(大于0渲染红色,小于0渲染绿色),需要加上“color”标识,sql语句以“[colordata]”开头,并且在需要渲染颜色数据上拼接“color:”关键字。
query.sql=select '1'\\, '2'\\, '3' union all select '4'\\, '5'\\, '6';\
|select 'xiaoming'\\, '100'\\, '100' union all select 'xiaohong'\\, '99'\\, '99';\
|[colordata]select '2019-08-01'\\, '100'\\, 'color:10%' union all select '2019-08-02'\\, '110'\\, 'color:10%' union all select '2019-08-03'\\, '100'\\, 'color:-9.1%';
正文模板HTML文件的编写:
编写文字表述以及table标题、表头,表单数据替换符(替换符为:@aData、@bData、@cData对应查询sql依序的查询表单,其中定义@aColorData中“Color”关键字表示该表单中有数据需要颜色渲染,sql查询语句也会有“Color”关键字体现)
注:新版本使Jtwig模板引擎,相对Html模板配置更为灵活,此处不再重复介绍,可自助学习Jtwig模板引擎
<div>各位好:</div>
<div>@procDate 这是一个测试邮件,数据皆为测试数据:</div>
<div class="data">
<div>
<table border="1" cellspacing="0" cellpadding="0" style="width:300px;">
<thead>
<tr>
<td colspan="1" rowspan="2" style="width:100px">列1</td>
<td colspan="1" rowspan="2" style="width:100px">列2</td>
<td colspan="1" rowspan="2" style="width:100px">列3</td>
</tr>
</thead>
@aData
</table>
</div>
<div class="clear"></div>
</div>
<div>表2:</div>
<div class="data">
<div>
<table border="1" cellspacing="0" cellpadding="0" style="width:300px;">
<thead>
<tr>
<td colspan="1" rowspan="2" style="width:100px">姓名</td>
<td colspan="1" rowspan="2" style="width:100px">数学成绩</td>
<td colspan="1" rowspan="2" style="width:100px">语文成绩</td>
</tr>
</thead>
@bData
</table>
</div>
<div class="clear"></div>
</div>
<div>表3:</div>
<div class="data">
<div>
<table border="1" cellspacing="0" cellpadding="0" style="width:300px;">
<thead>
<tr>
<td colspan="1" rowspan="2" style="width:100px">日期</td>
<td colspan="1" rowspan="2" style="width:100px">平均分</td>
<td colspan="1" rowspan="2" style="width:100px">较昨日同比</td>
</tr>
</thead>
@cColorData
</table>
</div>
<div class="clear"></div>
</div>
示例效果:
另外,可自定义编写正文渲染模板代码:
在YourMailTableService中重写getDataContext方法(类比重写isReady方法)
@Override
public String getDataContent(){
logger.info("设置邮件的标题...");
this.setMailMessage();
String path = null;
try {
path = URLDecoder.decode(ClassLoader.getSystemResource(this.HTML_TEMPLATE_NAME).getPath(), "UTF-8");
} catch (UnsupportedEncodingException var13) {
logger.error("邮件html模板读取失败!\n" + ExceptionUtils.getStackTrace(var13));
System.exit(1);
}
logger.info("读取邮件正文html模板...");
StringBuilder template = this.getHTMLTemplate(path);
String current = this.procDate.toString("yyyy-MM-dd");
logger.info("读取邮件正文的查询sql语句...");
//重写该方法是,配置文件中sql预计分隔符“|”可以自定义
String[] tempQuerySqlList = this.querySql.split("\\|", -1);
String content = template.toString();
logger.info("渲染邮件正文sql表格数据...");
//ToDo:
//此处sql查询数据表单自定义渲染至正文content中
return content;
}
邮件附加excel附件(新版本1.0.1)
相比1.0.0版本,1.0.1版本附件模块:
附件的配置更为简单灵活,表头(thead)数据与表体(tbody)数据都直接在html模板文件中自由配置;
无需excel模板文件,但每个sheet需要对应的html模板文件进行渲染;
无法在excel模板文件中初始配置函数、插入图表。
1.配置文件相关配置:
excel编号(自定义整数编号),多个excel附件,使用“|”分隔;
生成的附件临时保存路径(最终在此目录下再生成一级名同“excel名称”目录,生成的数据附件会放在对应目录下);
2.excel配置json文件(默认名称为excel_config.json):
excel_name:excel名称
sheet_info:表单信息
sheet_name:表单名称
template_file:html模板文件(每个sheet对应一个html文件,套用模板引擎生成table数据写入excel)
sql_list:数据查询sql(有序sql列表,对应模板文件中aData、bData…)
样例:
{
"1": {
"excel_name": "excel1.xlsx",
"sheet_info": [
{
"sheet_name": "分渠道明细数据1",
"template_file": "excel_template1.html",
"sql_list": [
"select date, channel_id, users from test_table where date <= ? order by date, channel_id;",
"select distinct date from test_table where date <= ? order by date;",
"select distinct channel_id from test_table where date <= ? order by channel_id;"
]
},
{
"sheet_name": "分渠道明细数据2",
"template_file": "excel_template2.html",
"sql_list": [
"select date, channel_id, users from test_table where date <= ? order by date, channel_id;",
"select distinct date from test_table where date <= ? order by date;",
"select distinct channel_id from test_table where date <= ? order by channel_id;"
]
}
]
}
}
Html模板文件样例:
分渠道明细数据1——excel_template1.html
<!DOCTYPE html>
<table border="1" cellspacing="0" cellpadding="0" style="width:300px;">
<thead>
<tr>
<th colspan="1" rowspan="1" style="width:100px">列1</th>
<th colspan="1" rowspan="1" style="width:100px">列2</th>
<th colspan="1" rowspan="1" style="width:100px">列3</th>
</tr>
</thead>
<tbody>
{% for value1 in aData %}
<tr>
{% for value2 in value1 %}
<td> {{ value2 }} </td>
{% endfor %}
</tr>
{% endfor %}
</tbody>
</table>
分渠道明细数据2——excel_template2.html
<!DOCTYPE html>
<table border="1" cellspacing="0" cellpadding="0" style="width:300px;">
<thead>
<tr>
<th colspan="1" rowspan="1" style="width:100px">日期</th>
{% for channel in cData %}
<th colspan="1" rowspan="1" style="width:100px"> 渠道:{{ channel[0] }} </th>
{% endfor %}
</tr>
</thead>
<tbody>
{% for date in bData %}
<tr>
{% set dd = date[0] %}
<td> {{ dd }} </td>
{% for channel in cData %}
{% set cc = channel[0] %}
{% set temp = 0 %}
{% for value1 in aData %}
{% if (value1[0] == dd) and (value1[1] == cc) and (temp == 0) %}
<td> {{ value1[2] }} </td>
{% set temp = 1 %}
{% endif %}
{% endfor %}
{% if temp == 0 %}
<td>0</td>
{% set temp = 1 %}
{% endif %}
{% endfor %}
</tr>
{% endfor %}
</tbody>
</table>
效果:
此处sheet2(分渠道明细数据2),表头渠道数据根据数据动态生成,每天sheet中的渠道数量根据实际数据展示。
邮件附加excel附件(老版本1.0.0)
最简单的一个附件样例
1.配置文件相关配置:
excel编号(自定义整数编号),多个excel附件,使用“|”分隔;
excel标题,excel.title.+编号;
生成的附件临时保存路径(最终在此目录下再生成一级名同“excel标题”目录,生成的数据附件会放在对应目录下);
excel附件模板文件。
excel.id=1
excel.title.1=测试数据附件
excel.location=C:/yourpath/
excel.template.path.1=C:/yourpath/test_template.xlsx
2.excel配置json文件(默认名称为sheet_config.json):
sheet_name 附件表单名(需要与excel模板文件中sheet名称保持一致);
start_x/start_y 表示数据填充开始位置;
date_format 每个填充步骤的查询日期配置
date_type “D”单日数据查询(日期为main函数接收参数);“M”当前月整个月数据查询;“CM”当前月截止到当前日期的数据查询;“2D”( 近2天)、“3D”(近3天)、“%dD”(%d天,“%d”为整数)数据查询(这里建议使用“D”类型查询方式,再使用sql语句中时间函数进行处理);
row_title 首列是否填充日期,以及填充日期的格式(格式:{yyyy-MM-dd},若查询数据如果已附带日期,则填空)
data_render 数据渲染部分,row_step步骤(相对行,与date_format中row_step保持一致),col_step步骤(相对列);
step_name 步骤名称(做日志记录,无数据实际意义)
sql查询语句(查询sql可接收传递日期参数,日期格式为yyyy-MM-dd,日期值为main函数传递值,如:select * from table where createtime = ?;)。
样例:
{
"1": [
{
"sheet_name": "分渠道明细数据",
"start_x": 0,
"start_y": 1,
"date_format": [
{
"row_step": 1,
"date_type": "D",
"row_title": ""
}
],
"data_render": [
{
"row_step": 1,
"col_step": 1,
"step_name": "分渠道明细数据-渠道001",
"sql": "select '2019-08-01', '001', '100' union all select '2019-08-02', '001', '100';"
}
]
}
]
}
3.excel附件模板test_template.xlsx:
4.示例结果:
邮件:
附件内容:
excel附件各类配置
此处进一步介绍多sql查询结果拼接数据生成附件,如果你的附件查询数据比较单一(单句sql即可解决),可忽略这部分的介绍。
为方便样例演示,先建立一张测试表:
建表语句:
CREATE TABLE `test_table` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`date` date DEFAULT NULL,
`channel_id` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
`users` bigint(20) DEFAULT NULL,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
多sheet表单
sheet_config.json配置文件信息:
{
"1": [
{
"sheet_name": "分渠道明细数据1",
"start_x": 0,
"start_y": 1,
"date_format": [
{
"row_step": 1,
"date_type": "D",
"row_title": ""
}
],
"data_render": [
{
"row_step": 1,
"col_step": 1,
"step_name": "分渠道明细数据-渠道001",
"sql": "select '2019-08-01', '001', '100' union all select '2019-08-02', '001', '100';"
}
]
},
{
"sheet_name": "分渠道明细数据2",
"start_x": 0,
"start_y": 1,
"date_format": [
{
"row_step": 1,
"date_type": "D",
"row_title": ""
}
],
"data_render": [
{
"row_step": 1,
"col_step": 1,
"step_name": "分渠道明细数据-渠道001",
"sql": "SELECT date, channel_id, users FROM test_table WHERE date = ?;"
}
]
}
]
}
此处附件“1”中配置了两个sheet表单(“分渠道明细数据1”,“分渠道明细数据2”,其中后者传递“D”单日查询类型的sql查询),数据附件结果如下:
表单中date_type配置
“date_type”为“2D”(查询近两天数据,main函数接收日期为“2019-08-02”)
{
"sheet_name": "分渠道明细数据2",
"start_x": 0,
"start_y": 1,
"date_format": [
{
"row_step": 1,
"date_type": "2D",
"row_title": ""
}
],
"data_render": [
{
"row_step": 1,
"col_step": 1,
"step_name": "分渠道明细数据-渠道001",
"sql": "SELECT channel_id, users FROM test_table WHERE date = ?;"
}
]
}
效果:
表单中row_title配置
“date_type”为“2D”(查询近两天数据);
“row_title”为“{yyyy-MM-dd}”;
查询sql中去除“date”日期列;
{
"sheet_name": "分渠道明细数据2",
"start_x": 0,
"start_y": 1,
"date_format": [
{
"row_step": 1,
"date_type": "2D",
"row_title": "{yyyy-MM-dd}"
}
],
"data_render": [
{
"row_step": 1,
"col_step": 1,
"step_name": "分渠道明细数据-渠道001",
"sql": "SELECT channel_id, users FROM test_table WHERE date = ?;"
}
]
}
效果:
表单中多列数据拼接
测试数据:
配置信息:
保持row_step=1,col_step=1渲染渠道001数据,col_step=2渲染渠道002数据。
{
"sheet_name": "分渠道明细数据2",
"start_x": 0,
"start_y": 1,
"date_format": [
{
"row_step": 1,
"date_type": "2D",
"row_title": "{yyyy-MM-dd}"
}
],
"data_render": [
{
"row_step": 1,
"col_step": 1,
"step_name": "分渠道明细数据-渠道001",
"sql": "SELECT channel_id, users FROM test_table WHERE channel_id = '001' and date = ?;"
},
{
"row_step": 1,
"col_step": 2,
"step_name": "分渠道明细数据-渠道002",
"sql": "SELECT channel_id, users FROM test_table WHERE channel_id = '002' and date = ?;"
}
]
}
模板表头调整:
效果:
表单中多行数据拼接
data_format中row_step=1,对应data_render中row_step=1;data_format中row_step=2,对应data_render中row_step=2;两部分数据查询后并拼接在一起。
两个查询步骤:步骤1,查询各个渠道数据;步骤2,查询合计数据(亦可以在excel模板中预置excel自带公式)
配置信息:
{
"sheet_name": "分渠道明细数据2",
"start_x": 0,
"start_y": 1,
"date_format": [
{
"row_step": 1,
"date_type": "D",
"row_title": ""
},
{
"row_step": 2,
"date_type": "D",
"row_title": ""
}
],
"data_render": [
{
"row_step": 1,
"col_step": 1,
"step_name": "分渠道明细数据-渠道001",
"sql": "SELECT date, channel_id, users FROM test_table WHERE date BETWEEN DATE_SUB(?, INTERVAL 1 DAY) AND ?;"
},
{
"row_step": 2,
"col_step": 1,
"step_name": "分渠道明细数据-渠道001",
"sql": "SELECT '合计', '-', SUM(users) FROM test_table WHERE date BETWEEN DATE_SUB(?, INTERVAL 1 DAY) AND ?;"
}
]
}
模板表头调整:
效果:
date_format.row_title自动合并日期单元格
配置信息:
{
"sheet_name": "分渠道明细数据2",
"start_x": 0,
"start_y": 1,
"date_format": [
{
"row_step": 1,
"date_type": "2D",
"row_title": "{yyyy-MM-dd}"
}
],
"data_render": [
{
"row_step": 1,
"col_step": 1,
"step_name": "分渠道明细数据-渠道001",
"sql": "SELECT channel_id, users FROM test_table WHERE date = ?;"
}
]
}
效果:
邮件正文增加图表
application.properties配置文件:
整数的图表编号,多个编号使用“|”分隔。
chart.id=1|2|3|4|5|6
mail_template.html文件添加图表标签:
图片依照chart.id顺序对应替换“@aPngFile”、“@bPngFile”、“@cPngFile”等
<div>
<img src="cid:@aPngFile"/>
</div>
<div>
<img src="cid:@bPngFile"/>
</div>
<div>
<img src="cid:@cPngFile"/>
</div>
<div>
<img src="cid:@dPngFile"/>
</div>
<div>
<img src="cid:@ePngFile"/>
</div>
<div>
<img src="cid:@fPngFile"/>
</div>
新增chart_source.json配置文件(图表使用echarts实现,json配置与echarts官方文档中配置基本一致)
echarts官方样例配置
新增chart_template.html图表模板文件
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>ECharts</title>
<style>*{ margin:0} html,body{ height:100%} .wrapper{ min-height:100%; height:auto !important; height:100%; margin:0 auto -155px} .footer,.push{ height:155px} table.gridtable{ font-family:verdana,arial,sans-serif; font-size:11px; color:#333; border-width:1px; border-color:#666; border-collapse:collapse; margin:5px auto} table.gridtable th{ border-width:1px; padding:8px; border-style:solid; border-color:#666; background-color:#dedede} table.gridtable td{ border-width:1px; padding:8px; border-style:solid; border-color:#666; background-color:#fff} .middle{ text-align:center; margin:0 auto; width:90%; height:auto} .info{ font-size:12px; text-align:center; line-height:20px; padding:40px} .info a{ margin:0 10px; text-decoration:none; color:green}</style>
</head>
<body>
<div class="wrapper">
<div class="middle"><h1 style="padding: 20px 0 20px;">##title##</h1></div>
<div class="left" id="main" style="height:400px"></div>
</div>
</body>
<script type="text/javascript">
!function(t,e){
<!-- 此处需要补充echarts.js内容 -->
}
var myChart = echarts.init(document.getElementById('main'));
var option = ##option##;
myChart.setOption(option);
</script>
</html>
折线图
配置信息:
chart.id=2
{
"2":{
"chartTitle":"活跃趋势折线图-test",
"sql":"SELECT '2019-08-01' AS date, 8 AS orders, '0.21' AS rate UNION ALL SELECT '2019-08-02', 5, '0.13' UNION ALL SELECT '2019-08-03', 4, '0.10' UNION ALL SELECT '2019-08-04', 12, '0.32';",
"htmlTemplate":"chart_template.html",
"tooltip":{
"trigger":"item"
},
"legend":{
"type":"scroll",
"data":[
"活跃人数"
],
"padding":5,
"itemGap":10
},
"grid":{
"left":50,
"right":50,
"bottom":40
},
"xAxis":[
{
"type":"category",
"name":"日期",
"axisLabel":{
"rotate":0
},
"nameGap":10,
"data":[
"0"
]
}
],
"yAxis":[
{
"type":"value",
"name":"人数",
"axisLabel":{
"formatter":"{value}"
}
}
],
"series":[
{
"yAxisIndex":0,
"name":"活跃人数",
"type":"line",
"itemStyle":{
"normal":{
"label":{
"show":true
}
}
},
"data":[
"1"
]
}
]
}
}
其中:
查询SQL(日期参数配置同邮件正文sql、附件sql):SELECT '2019-08-01' AS date, 8 AS orders, '0.21' AS rate UNION ALL SELECT '2019-08-02', 5, '0.13' UNION ALL SELECT '2019-08-03', 4, '0.10' UNION ALL SELECT '2019-08-04', 12, '0.32';
数据查询示例:
x轴与系列数据的“data”填写的为数据编号。
即查询表单数据的列编号,如x轴为日期,这是查询结果的第一列“date”,xAxis.data=[“0”]。
邮件效果:
柱状图
配置信息:
chart.id=3
{
"3":{
"chartTitle":"订购数据柱状图-test",
"sql":"SELECT '2019-08-01' AS date, 8 AS orders, '0.21' AS rate UNION ALL SELECT '2019-08-02', 5, '0.13' UNION ALL SELECT '2019-08-03', 4, '0.10' UNION ALL SELECT '2019-08-04', 12, '0.32'",
"htmlTemplate":"chart_template.html",
"tooltip":{
"trigger":"item"
},
"legend":{
"type":"scroll",
"data":[
"订购人数"
],
"padding":5,
"itemGap":10
},
"grid":{
"left":50,
"right":50,
"bottom":40
},
"xAxis":[
{
"type":"category",
"name":"日期",
"axisLabel":{
"rotate":0
},
"nameGap":10,
"data":[
"0"
]
}
],
"yAxis":[
{
"type":"value",
"name":"订购人数",
"axisLabel":{
"formatter":"{value}"
}
}
],
"series":[
{
"yAxisIndex":0,
"name":"订购人数",
"type":"bar",
"itemStyle":{
"normal":{
"label":{
"show":true
}
}
},
"data":[
"1"
]
}
]
}
}
效果:
组合图
配置信息:
chart.id=1
{
"1":{
"chartTitle":"完整组合图-test",
"sql":"SELECT '2019-08-01' AS date, 8 AS orders, '0.21' AS rate UNION ALL SELECT '2019-08-02', 5, '0.13' UNION ALL SELECT '2019-08-03', 4, '0.10' UNION ALL SELECT '2019-08-04', 12, '0.32'",
"htmlTemplate":"chart_template.html",
"toolbox":{
"feature":{
"dataView":{
"show":true,
"title":"数据视图",
"readOnly":false,
"lang":[
"数据视图",
"关闭",
"刷新"
]
},
"saveAsImage":{
"show":true,
"title":"保存为图片",
"type":"png",
"lang":[
"点击保存"
]
},
"dataZoom":{
"show":true,
"title":{
"dataZoom":"区域缩放",
"dataZoomReset":"区域缩放后退"
}
},
"magicType":{
"show":true,
"title":{
"bar":"柱形图切换",
"stack":"堆积",
"tiled":"平铺",
"line":"折线图切换"
},
"type":[
"bar",
"line",
"stack",
"tiled"
]
}
},
"show":true
},
"tooltip":{
"trigger":"item"
},
"legend":{
"type":"scroll",
"data":[
"订购人数",
"转化率"
],
"padding":5,
"itemGap":10
},
"grid":{
"left":50,
"right":50,
"bottom":40
},
"xAxis":[
{
"type":"category",
"name":"日期",
"axisLabel":{
"rotate":0
},
"nameGap":30,
"data":[
"0"
]
}
],
"yAxis":[
{
"type":"value",
"name":"订购人数",
"axisLabel":{
"formatter":"{value}"
}
},
{
"type":"value",
"name":"转化率",
"axisLabel":{
"formatter":"{value}%"
}
}
],
"series":[
{
"yAxisIndex":0,
"name":"订购人数",
"type":"bar",
"itemStyle":{
"normal":{
"label":{
"show":true
}
}
},
"data":[
"1"
]
},
{
"yAxisIndex":1,
"name":"转化率",
"type":"line",
"itemStyle":{
"normal":{
"label":{
"show":true,
"formatter":"{c}%"
}
}
},
"data":[
"2"
]
}
]
}
}
效果:
漏斗图
查询sql:SELECT '展现', 100 UNION ALL SELECT '访问', 60 UNION ALL SELECT '咨询', 40 UNION ALL SELECT '订单', 20 UNION ALL SELECT '点击', 80;
数据表单:
漏斗图与饼图需要的series.data需要指定“key”与“value”,即填写sql查询数据集的列,此处填写的为[“0”, “1”]。
配置信息:
chart.id=5
{
"5":{
"chartTitle":"漏斗图-test",
"sql":"SELECT '展现', 100 UNION ALL SELECT '访问', 60 UNION ALL SELECT '咨询', 40 UNION ALL SELECT '订单', 20 UNION ALL SELECT '点击', 80;",
"htmlTemplate":"chart_template.html",
"tooltip":{
"trigger":"item",
"formatter":"{a} <br/>{b} : {c}%"
},
"legend":{
"data":[
"展现",
"点击",
"访问",
"咨询",
"订单"
]
},
"calculable":true,
"series":[
{
"name":"漏斗图",
"type":"funnel",
"width":"40%",
"label": {
"normal":{
"show":true,
"position":"inner",
"textStyle" : {
"fontWeight" : 300 ,
"fontSize" : 16
},
"formatter":"{b} : {c}%"
}
},
"data":[0, 1]
},
{
"name":"金字塔",
"type":"funnel",
"x":"50%",
"sort":"ascending",
"label": {
"normal":{
"show":true,
"position":"inner",
"textStyle" : {
"fontWeight" : 300 ,
"fontSize" : 16
},
"formatter":"{b} : {c}%"
}
},
"itemStyle":{
"normal":{
"label":{
"show":true,
"position":"left"
}
}
},
"data":[0, 1]
}
]
}
}
效果:
饼图
配置信息:
chart.id=6
{
"6":{
"chartTitle":"饼图-test",
"sql":"SELECT '展现', 100 UNION ALL SELECT '访问', 60 UNION ALL SELECT '咨询', 40 UNION ALL SELECT '订单', 20 UNION ALL SELECT '点击', 80;",
"htmlTemplate":"chart_template.html",
"tooltip":{
"trigger":"item",
"formatter":"{b} : {c}({d}%)"
},
"legend":{
"data":[
"展现",
"点击",
"访问",
"咨询",
"订单"
]
},
"calculable":true,
"series":[
{
"name":"饼图",
"type":"pie",
"label": {
"normal":{
"show":true,
"position":"inner",
"textStyle" : {
"fontWeight" : 300 ,
"fontSize" : 16
},
"formatter":"{b} : {c}({d}%)"
}
},
"itemStyle":{
"normal":{
"label":{
"show":true
}
}
},
"data":[0, 1]
}
]
}
}
效果:
Auto email UML图
Auto email流程图
注:“数据确认? ” no 到 “结束”;“加载附件?” no 到 “发送邮件”(实现时,无需加载附件或附件加载失败都会发送邮件,其中加载附件失败函数执行最终exit1,并且记录error日志)。