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;

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))
                {
                    StartExport(content);
                }
                else
                {
                    Thread.Sleep(5000);
                }
            }
        }
        public void StartExport(string 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());

            //创建工作薄
            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");

            //填充数据
            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;
                DataTable dt = CustomerSqlConn.dtable(SqlString + " limit " + skip + "," + pageSize, MysqlConn.connstr);
                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)
                                {
                                    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)
                            {
                                var cell = row.CreateCell(index);
                                string val = dr[dc.ColumnName].ToString();
                                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";
                CustomerSqlConn.op("insert into ExportExcels (CreateDate,FileName,FileUrl,SysId) values ('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + FileName + "','" + FileUrl + "'," + Operater + ")", MysqlConn.connstr);
            }
        }
    }
}