export-server/AppStart/Helper/ExportService.cs

using System;
using System.Collections.Generic;
using Library;
using LitJson;
using System.Data;
using System.Threading;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;
using System.Text.RegularExpressions;

namespace MySystem
{
    public class ExportService
    {
        public readonly static ExportService Instance = new ExportService();
        private ExportService()
        { }




        public void Start()
        {
            Thread th = new Thread(StartDo);
            th.IsBackground = true;
            th.Start();
        }
        public void StartDo()
        {
            while (true)
            {
                string content = RedisDbconn.Instance.RPop<string>("ExportQueue");
                if(!string.IsNullOrEmpty(content))
                {
                    try
                    {
                        JsonData jsonObj = JsonMapper.ToObject(content);
                        string FileName = jsonObj["FileName"].ToString();
                        if(FileName == "执行")
                        {
                            StartExcute(content);
                        }
                        else
                        {
                            StartExport(content);
                        }
                    }
                    catch(Exception ex)
                    {
                        function.WriteLog(ex.ToString() + "\n\n", "导出日志异常");
                    }
                }
                else
                {
                    Thread.Sleep(5000);
                }
            }
        }
        public void StartExcute(string content)
        {
            function.WriteLog(DateTime.Now.ToString() + "\n" + content, "执行日志");
            JsonData jsonObj = JsonMapper.ToObject(content);
            string Operater = jsonObj["Operater"].ToString();
            string SqlString = jsonObj["SqlString"].ToString();
            string FileName = jsonObj["FileName"].ToString();
            int pageSize = int.Parse(jsonObj["MaxCount"].ToString());
            string ConnectStr = "";
            if(content.Contains("\"ConnectStr\""))
            {
                ConnectStr = jsonObj[prop_name: "ConnectStr"].ToString();
            }
            else
            {
                ConnectStr = MysqlConn.connstr;
            }

            //执行
            int page = 0;
            bool op = true;
            while(op)
            {
                int skip = page * pageSize;
                string sql = SqlString + " limit " + skip + "," + pageSize;
                function.WriteLog(sql, "执行日志");
                DataTable dt = CustomerSqlConn.dtable(sql, ConnectStr);
                if(dt.Rows.Count > 0)
                {
                    string dosql = "";
                    foreach (DataRow dr in dt.Rows)
                    {
                        dosql += dr[0].ToString();
                    }
                    CustomerSqlConn.op(dosql, ConnectStr);
                    page += 1;
                }
                else
                {
                    op = false;
                }
            }
        }
        public void StartExport(string content)
        {
            function.WriteLog(DateTime.Now.ToString() + "\n" + content, "导出日志");
            JsonData jsonObj = JsonMapper.ToObject(content);
            string Operater = jsonObj["Operater"].ToString();
            string SqlString = jsonObj["SqlString"].ToString();
            string FileName = jsonObj["FileName"].ToString();
            int MaxCount = int.Parse(jsonObj["MaxCount"].ToString());
            string ConnectStr = "";
            if(content.Contains("\"ConnectStr\""))
            {
                if(!string.IsNullOrEmpty(jsonObj["ConnectStr"].ToString()))
                {
                    ConnectStr = GetConnectStr(jsonObj["ConnectStr"].ToString());
                }
                else
                {
                    ConnectStr = MysqlConn.readconnstr;
                }
            }
            else
            {
                ConnectStr = MysqlConn.readconnstr;
            }
            bool SubSqlFlag = content.Contains("\"SubSqlString\"");

            //创建工作薄
            var workbook = new XSSFWorkbook();

            //单元格样式
            ICellStyle style = workbook.CreateCellStyle();
            style.BorderBottom = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BottomBorderColor = HSSFColor.Black.Index;
            style.TopBorderColor = HSSFColor.Black.Index;
            style.LeftBorderColor = HSSFColor.Black.Index;
            style.RightBorderColor = HSSFColor.Black.Index;

            //创建表
            var table = workbook.CreateSheet("Sheet1");
            string FilePath = function.ReadInstance("/WebRootPath.txt");
            function.WriteLog(FilePath, "导出日志");

            //获取子集字典数据
            Dictionary<string, Dictionary<string, string>> dicValsJson = new Dictionary<string, Dictionary<string, string>>();
            if(SubSqlFlag)
            {
                JsonData subItems = jsonObj["SubSqlString"];
                for (int index = 0; index < subItems.Count; index++)
                {
                    JsonData sub = subItems[index];
                    string DatabaseConnect = sub["DatabaseConnect"].ToString();
                    string SqlContent = sub["SqlContent"].ToString();
                    string DataKey = sub["DataKey"].ToString();
                    string DataText = sub["DataText"].ToString();
                    string Alias = sub["Alias"].ToString();
                    string SubConnectStr = DatabaseConnect;
                    if(!string.IsNullOrEmpty(SubConnectStr))
                    {
                        SubConnectStr = GetConnectStr(DatabaseConnect);
                    }
                    else
                    {
                        SubConnectStr = MysqlConn.readconnstr;
                    }
                    string sqlstr = SqlContent;

                    DataTable dtsub = CustomerSqlConn.dtable(sqlstr, SubConnectStr);
                    if(dtsub.Rows.Count > 0)
                    {
                        if(!string.IsNullOrEmpty(DataKey))
                        {
                            string[] TextFields = DataText.Split(',');
                            foreach(DataRow dr in dtsub.Rows)
                            {
                                Dictionary<string, string> vals = new Dictionary<string, string>();
                                foreach (string key in TextFields)
                                {
                                    vals.Add(key, dr[key].ToString());
                                }
                                dicValsJson.Add(Alias + "." + dr[DataKey].ToString(), vals);
                            }
                        }
                    }
                }
            }

            //填充数据
            int i = 0;
            int page = 0;
            int pageSize = 200;
            bool op = true;
            while(op)
            {
                var fs = System.IO.File.OpenWrite(FilePath + "/exportfile/" + FileName + ".xlsx");
                int skip = page * pageSize;
                string sql = SqlString + " limit " + skip + "," + pageSize;
                function.WriteLog(sql, "导出日志");
                DataTable dt = CustomerSqlConn.dtable(sql, ConnectStr);
                if(dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        i += 1;
                        if(i <= MaxCount || MaxCount == 0)
                        {
                            if(i == 1)
                            {
                                //创建表头
                                var title = table.CreateRow(0);
                                int j = 0;
                                foreach (DataColumn dc in dt.Columns)
                                {
                                    string val = dr[dc.ColumnName].ToString();
                                    Match m = Regex.Match(val, @"\#\{.*?\}\#");
                                    if(m.Success)
                                    {
                                        string fieldName = m.Value.Replace("#{", "").Replace("}#", "");
                                        Dictionary<string, string> fieldVals = dicValsJson[fieldName];
                                        foreach(string field in fieldVals.Keys)
                                        {
                                            var cell = title.CreateCell(j);
                                            cell.CellStyle = style;
                                            cell.SetCellValue(dc.ColumnName + field);
                                            j += 1;
                                        }
                                    }
                                    else
                                    {
                                        var cell = title.CreateCell(j);
                                        cell.CellStyle = style;
                                        cell.SetCellValue(dc.ColumnName);
                                        j += 1;
                                    }
                                }
                            }
                            var row = table.CreateRow(i);
                            int index = 0;
                            foreach (DataColumn dc in dt.Columns)
                            {
                                string val = dr[dc.ColumnName].ToString();
                                Match m = Regex.Match(val, @"\#\{.*?\}\#");
                                if(m.Success)
                                {
                                    string fieldName = m.Value.Replace("#{", "").Replace("}#", "");
                                    Dictionary<string, string> fieldVals = dicValsJson[fieldName];
                                    foreach(string field in fieldVals.Keys)
                                    {
                                        var cell = row.CreateCell(index);
                                        cell.CellStyle = style;
                                        cell.SetCellValue(fieldVals[field]);
                                        index += 1;
                                    }
                                }
                                else
                                {
                                    var cell = row.CreateCell(index);
                                    cell.CellStyle = style;
                                    cell.SetCellValue(val);
                                    index += 1;
                                }
                            }
                        }
                        else
                        {
                            op = false;
                        }
                    }
                    //打开xls文件，如没有则创建，如存在则在创建是不要打开该文件
                    workbook.Write(fs);   //向打开的这个xls文件中写入mySheet表并保存。
                    page += 1;
                }
                else
                {
                    op = false;
                }
                fs.Dispose();
            }
            if(i > 0)
            {
                string FileUrl = MysqlConn.sourceHost + "exportfile/" + FileName + ".xlsx";
                function.WriteLog(FileUrl, "导出日志");
                CustomerSqlConn.op("insert into ExportExcels (CreateDate,FileName,FileUrl,SysId) values ('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + FileName + "','" + FileUrl + "'," + Operater + ")", MysqlConn.connstr);
                function.WriteLog("insert into ExportExcels (CreateDate,FileName,FileUrl,SysId) values ('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + FileName + "','" + FileUrl + "'," + Operater + ")", "导出日志");
                function.WriteLog("end", "导出日志");
            }
        }

        #region 获取链接数据库字符串

        public string GetConnectStr(string key)
        {
            return Library.ConfigurationManager.AppSettings[key].ToString();
        }

        #endregion
    }
}