-The tools neededMySQL DatabaseConnection Database module MySQL Basic package: http://blog.csdn.net/zzwwjjdj1/article/details/51991348Auto-run module Node-schedule basic use: http://blog.csdn.net/zzwwjjdj1/article/details/51898257
Generate Excel Table Node-xlsxSend mail Nodemailer basic use: http://blog.csdn.net/zzwwjjdj1/article/details/51878392Async Process Control Module Async BASIC use: http://blog.csdn.net/zzwwjjdj1/article/details/51857959--Installation moduleI set up a folder on the F drive: Automationnpm install MySQLnpm Install Node-schedulenpm Install node-xlsxnpm Install Nodemailernpm Install async--3 methods of encapsulation1. Accessing the database2. Send mail3. Format Time
Mysql.js
/* Tool Class August 5, 2016 16:47:04 */var db = {}; var mysql = require (' mysql '); var Nodemailer = require (' Nodemailer '); var pool = Mysql.createpool ({connectionlimit:10, host: ' Local Host ', User: ' Root ', password: ' 123456 ', database: ' Nodejs '}); Executes the SQL statement and returns the result db.query = function (sql, callback) {if (!sql) {callback (); Return } pool.query (SQL, function (err, rows, fields) {if (err) {Console.log (err); Callback (err, NULL); Return }; Callback (null, rows, fields); }); };//Send mail with attachment Db.sendmail = function (Xlsxname, callback) {var transporter = Nodemailer.createtransport ({service: ' QQ ') , auth: {User: ' [email protected] ', pass: ' Drckgvaniifuxxxx '}}); var mailoptions = {from: ' [email protected] ',//Your mailbox to: ' [email protected] ',//your boss's mailbox subject: ' Boss, you want to Excel comes, format yourself under the handle! ', HTML: ' --TestAutomation.jsvar async = require (' async '); var xlsx = require (' node-xlsx '); var schedule = require ("Node-schedule"); var mysql = require (' ./mysql '); var fs = require (' FS '); var auto = function () {//query data and convert to format required to generate xlsx var task1 = function (callback) {Mysql.query (' SELECT * from Users ', function (err, rows, fields) {if (err) {callback (err,null); return;} var datas = [];rows.foreach (function (row) {var newRow = [];for (var key in row) {Newrow.push (Row[key]);} Datas.push (NewRow);}) Callback (null, datas);})} Generate xlsx file var task2 = function (Datas, callback) {var buffer = Xlsx.build ([{name: "Today's revenue", Data:datas}]); var xlsxname = ' ${ Mysql.nowdate (). Split (') [0]}.xlsx '; Fs.writefile (xlsxname, buffer, ' binary ', function (err) {if (err) {callback (ERR), null); return;} Callback (null, xlsxname);})} Send message, return information var task3 = function (Xlsxname, callback) {Mysql.sendmail (xlsxname, function (err, info) {if (err) {callback ( err, null); return;} Callback (null, info);})} Async.waterfall ([Task1, Task2, Task3], function (err, result) {if (err) {Console.log (ERR); return;} Console.log (result);})} var rule = new schedule. Recurrencerule (); var times = [1,6,11,16,21,26,31,36,41,46,51,56];rule.second = times;schedule.schedulejob (rule, func tion () {Auto ();});
folder structure:
To see the effect, the automatic execution is set to 5 seconds to execute once.
--run--------Mail------
-----Specific content------
--------Printing Information--------
-------Database-------
---------Excel Content--------say 6 not 6 ...
Nodejs implementation, automatic daily reading of database data-Generate Excel form-Send to Boss mailbox