c#导出至excel(分月按天存储数据）

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using CpmBigDataImportService;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;
namespace HCFX
{
    /// <summary>
    /// 导出数据 2018.8.22 19:10
    /// </summary>
    public class ExportToExcel
    {
        //public ExportToExcel() { }

        /// <summary>
        /// 工作表最大记录数据
        /// </summary>
        private const int MaxLinesCountOfSheet = 10000 * 80;
        /// <summary>
        /// 工作表最大工作表数量
        /// </summary>
        private const int MaxSheetsCountOfWorkbook = 26;
        /// <summary>
        /// 工作簿最大行数
        /// </summary>
        private const int MaxLinesCountOfWorkbook = 10000 * 1000;

        /// <summary>
        /// sql语句
        /// </summary>
        private string sql;

        /// <summary>
        /// 数据表，本次设计得到内存表立即写到Excel里
        /// </summary>
        private DataTable dt;

        /// <summary>
        /// sql模板 0 rq 1 yf 2yjh ????需要整成变量 配合 object[]完成
        /// </summary>
        private string sqlModal = "SELECT OBJECT_ID OID,''''+YDH YDH,BBH,HCLX,QD,FKFS,FKWD,SJRQ,PJRQ,YJDWDDM,MDDWDDM,HCSJ,HCRYGH,''''+YJZH YJZH,YYJD,XYJD,YMDD,XMDD,YCPDM,XCPDM,YSXLX,XSXLX,YQYLX,XQYLX,YYWLX,XYWLX,YJFZL,XJFZL,YFKFS,XFKFS,YYF,XYF,CE,YYJZH,XYJZH,YBZF,XBZF,YBJ,XBJ,YWTJ,XWTJ,YDLPYJ,XDLPYJ,YTSBJ,XTSBJ,YTSPS,XTSPS,YDSHKFW,XDSHKFW,YSXFW,XSXFW,YCCCZ,XCCCZ,YTZBZ,XTZBZ,YJJRFWF,XJJRFWF,YBDPS,XBDPS,YZZFJF,XZZFJF,YDSHK,XDSHK,YZJTH,XZJTH,YXGPYJ,XXGPYJ,YPYFJF,XPYFJF,YGGFKFSFJF,XGGFKFSFJF,YPJDZBGFW,XPJDZBGFW,YHWBGFW,XHWBGFW,YSHSLFW,XSHSLFW from HCYSJ{0} WHERE OBJECT_ID IN (SELECT oid FROM HCFX{1}.dbo.ZHHB{0} WHERE yjh='{2}')";

        public string SqlModal
        {
            get { return sqlModal; }
            set { sqlModal = value; }
        }

        private SqlConnectConfig sqc = new SqlConnectConfig(".\\sqlexpress", "hc", "sa", "szsf");


        /// <summary>
        /// 月份
        /// </summary>
        public string[] Yfs { set; get; }

        /// <summary>
        /// 生成月结号数组
        /// </summary>
        /// <param name="txtYjh"></param>
        /// <returns></returns>
        private string[] GetYjhs(string txtYjh)
        {
            return txtYjh.Split(';');
        }

        /// <summary>
        /// 两个月份之间月份数组,包含首尾月份
        /// </summary>
        /// <param name="startYf"></param>
        /// <param name="endYf"></param>
        /// <returns></returns>
        private string[] GetYfs(string startYf, string endYf)
        {
            DateTime d1 = DateTime.ParseExact(startYf + "01", "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);
            DateTime d2 = DateTime.ParseExact(endYf + "01", "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);
            int months = 12 * (d2.Year - d1.Year) + (d2.Month - d1.Month);

            List<string> ls = new List<string>();
            for (int i = 0; i <= months; i++)
            {
                ls.Add(d1.AddMonths(i).ToString("yyyyMM"));
            }
            return ls.ToArray();
        }

        /// <summary>
        /// 辅助，数组转字符串
        /// </summary>
        /// <param name="objs"></param>
        /// <param name="separator"></param>
        /// <returns></returns>
        public string Array2String(object[] objs, string separator = "\r\n")
        {
            return string.Join(separator, objs.ToArray());
        }

        /// <summary>
        /// 获取某月份内日期数组
        /// </summary>
        /// <param name="yf"></param>
        /// <returns></returns>
        private string[] GetRqs(string yf)
        {
            List<string> rqs = new List<string>();
            int lastDay = (DateTime.ParseExact(yf + "01", "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture)).AddMonths(1).AddDays(-1).Day;
            for (int i = 1; i <= lastDay; i++)
                rqs.Add(yf + i.ToString("00"));
            return rqs.ToArray();
        }

        /// <summary>
        /// 根据导入配置文件生成标题数据，含objectid
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private string[] GetTitle(string tableName = "HCYSJ")
        {
            CpmVars myVars = new CpmVars(tableName, AppDomain.CurrentDomain.BaseDirectory);
            List<string> ls = new List<string>(); ls.Add("OID");
            for (int i = 0; i < myVars.Cols.Count; i++)
            {
                ls.Add(myVars.Cols[i].Prompt);
            }
            return ls.ToArray();
        }

        public ExportToExcel() { }

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="startYf"></param>
        /// <param name="endYf"></param>
        /// <param name="txtYjh"></param>
        public ExportToExcel(string startYf, string endYf)
        {
            this.Yfs = GetYfs(startYf, endYf);

        }

        /// <summary>
        /// 计数器 工作表记录数
        /// </summary>
        private int linesCountOfWorkbook = 0;

        /// <summary>
        /// 计数器 工作表记录数
        /// </summary>
        private int linesCountOfWorksheet = 0;

        /// <summary>
        /// 计数器 工作簿数
        /// </summary>
        private int wbCount = 0;

        /// <summary>
        /// 计数器 工作表记录数
        /// </summary>
        private int shtCount = 0;

        Excel.Application app = null;
        Excel.Workbook wb = null;
        Excel.Worksheet sht = null;


        /// <summary>
        /// 保存工作簿
        /// </summary>
        /// <param name="partName"></param>
        /// <param name="createNext"></param>
        private void WorkbookSave(string partName = "", bool isCloseCur = true, bool createNext = true)
        {
            if (string.IsNullOrEmpty(partName))
                partName = "新工作簿" + wbCount.ToString("000");

            string wbFullName = string.Format("{0}\\{1}.xlsx"
                    , Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory)
                    , partName);
            wb.SaveAs(Filename: wbFullName);
            if (isCloseCur)
                wb.Close();
            if (createNext)
                wb = app.Workbooks.Add();
            wbCount += 1;
            linesCountOfWorkbook = 0;
            linesCountOfWorksheet = 0;

        }

        /// <summary>
        /// 工作表开始行号
        /// </summary>
        private int worksheetStartLine = 1;

        /// <summary>
        /// 工作表是否包含标题，这是工作表的属性
        /// </summary>
        private bool worksheetContainTitle = true;


        /// <summary>
        /// 分次写入worksheet
        /// </summary>
        /// <param name="sht"></param>
        /// <param name="dtMothod"></param>
        /// <param name="hasTitle"></param>
        private void FillDtToWorksheetBatched(Excel.Worksheet sht, DataTable dtMothod, int outStartLine, bool hasTitle = true, string[] title = null)
        {
            DataTable dtTemp = null;
            int startLine = outStartLine;
            string address = "A" + startLine.ToString();

            if (hasTitle)
            {
                object[,] header = new object[1, dtMothod.Columns.Count];
                if (null == title)
                    for (int i = 0; i < dtMothod.Columns.Count; i++)
                        header[0, i] = dtMothod.Columns[i].ColumnName;
                else
                    for (int i = 0; i < dtMothod.Columns.Count; i++)
                        header[0, i] = title[i];

                sht.Range[address].get_Resize(1, dtMothod.Columns.Count).Value2 = header;

                startLine += 1;
                address = "A" + startLine.ToString();
            }

            int total = dtMothod.Rows.Count;
            const int batchCount = 10000;

            int startRow = 0;
            int endRow = (total < batchCount) ? total - 1 : batchCount - 1;


            while (total > 0)
            {
                dtTemp = dtMothod.Clone();
                for (int i = startRow; i <= endRow; i++)
                {
                    object[] content = dtMothod.Rows[i].ItemArray;
                    DataRow dr = dtTemp.NewRow(); dr.ItemArray = content; dtTemp.Rows.Add(dr);
                }
                sht.Range[address].get_Resize(endRow - startRow + 1, dtMothod.Columns.Count).Value2 = MyCommon.ToObjMartrixNoTitle(dtTemp);

                total -= batchCount;

                startLine += batchCount;
                address = "A" + startLine.ToString();

                startRow += batchCount;
                endRow = (total < batchCount) ? startRow - 1 + total : startRow + batchCount - 1;
            }
        }




        private bool isNewWorksheet = true;

        /// <summary>
        /// 输出到Excel
        /// </summary>
        public void OutPut()
        {
            app = new Excel.Application();
            app.Visible = true;
            wb = app.Workbooks.Add();
            sht = wb.Worksheets.Add();

            string[] title = null;// new string[] { "rq", "yjh", "A2B", "s2y", "y2s" };

            int testCount = 0; bool abortFlag = false;  //testing 2018.8.23 15:59
            foreach (var yf in Yfs) //月份
            {
                foreach (var rq in GetRqs(yf)) //月内日期,
                {
                    //0 yf 1 rq
                    //sql = string.Format(GetSql(), yf, rq);

                    sql = string.Format(SqlModal,rq,yf);
                    dt = MyCommon.GetDt(sql, sqc.ConnectString);
                    if (0 == dt.Rows.Count)
                        continue;

                    FillDtToWorksheetBatched(sht, dt, worksheetStartLine, isNewWorksheet, title);

                    if (isNewWorksheet)
                    {
                        isNewWorksheet = false;
                        worksheetStartLine += dt.Rows.Count + 1;
                    }
                    else
                    {
                        worksheetStartLine += dt.Rows.Count;
                    }


                    linesCountOfWorksheet += dt.Rows.Count;
                    linesCountOfWorkbook += dt.Rows.Count;


                    //超过工作表最大行数 新建一个工作表
                    if (linesCountOfWorksheet > MaxLinesCountOfSheet)
                    {
                        sht = wb.Worksheets.Add();
                        isNewWorksheet = true;
                        shtCount++;
                        linesCountOfWorksheet = 0;
                        worksheetStartLine = 1;
                    }

                    //超过工作簿最大记录数工作簿最大工作表数
                    if (linesCountOfWorkbook > MaxLinesCountOfWorkbook || wbCount > MaxSheetsCountOfWorkbook)
                    {
                        WorkbookSave();
                        isNewWorksheet = true;
                        worksheetStartLine = 1;
                    }

                    MyCommon.WriteLog(string.Format("输出{0}数据{1}条", rq, dt.Rows.Count
                        ));

                    testCount++;
                    if (-1 == testCount)
                    {
                        return;
                        abortFlag = true; break;
                    }


                }

                if (abortFlag)
                    break;
            }
            if (linesCountOfWorkbook > 0)
            {
                WorkbookSave("", false, false);
            }

            //app.Quit();

        }

        public string GenSelect(string tableName, string configPath = "")
        {
            if (string.IsNullOrEmpty(configPath))
                configPath = AppDomain.CurrentDomain.BaseDirectory;
            CpmVars myVar = new CpmVars(tableName, configPath);
            List<string> ls = new List<string>(); ls.Add("OID");
            for (int i = 0; i < myVar.Cols.Count; i++)
                ls.Add(myVar.Cols[i].FieldName);
            return string.Format("select {0} from {1} ", string.Join(",", ls.ToArray()), tableName);
        }


    }
}