?using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Excel=Microsoft.Office.Interop.Excel;
using System.Diagnostics;

namespace ExportToExcel
{

    public class ExcelManager : IDisposable
    {  
        Excel.Range m_objRange = null;
        Excel.Application m_objExcel = null;
        Excel.Workbooks m_objBooks = null;
        Excel._Workbook m_objBook = null;
        Excel.Sheets m_objSheets = null;
        Excel._Worksheet m_objSheet = null;
        Excel.QueryTable m_objQryTable = null;
        object m_objOpt = System.Reflection.Missing.Value;
        //DataBase-used variable
        private System.Data.SqlClient.SqlConnection sqlConn = null;
        private string strConnect = string.Empty;
        private System.Data.SqlClient.SqlCommand sqlCmd = null;

        //Sheets variable
        private double dbSheetSize = 65535;//the hight limit number in one sheet
        private int intSheetTotalSize = 0;//total record can divied sheet number
        private double dbTotalSize = 0;//record total number

        /// <summary>
        /// 建構函數
        /// </summary>
        public ExcelManager() { }

        /// <summary>
        /// 建構函數
        /// </summary>
        /// <param name="dbHL">一個Excel表格的最大記錄數</param>
        /// <param name="dbTotal">該數據庫表共查詢出多少條記錄</param>
        /// <param name="intDivide">查詢出的記錄可分成幾個Excel</param>
        /// <param name="conn">sqlConnection</param>
        public ExcelManager(Double dbHL, Double dbTotal, int intDivide, SqlConnection conn)
        {
            dbSheetSize = dbHL;
            intSheetTotalSize = intDivide;
            dbTotalSize = dbTotal;
            sqlConn = conn;
        }
        /// <summary>
        /// 建構函數
        /// </summary>
        /// <param name="dbHL">一個Excel表格的最大記錄數</param>
        /// <param name="strTableName">需查詢的數據庫的表名</param>
        /// <param name="conn">sqlConnection</param>
        public ExcelManager(Double dbHL, string strTableName, SqlConnection conn)
        {
            dbSheetSize = dbHL;
            sqlConn = conn;
            intSheetTotalSize = GetTotalSize(strTableName, sqlConn);
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        private void KillProcess(string processName)
        {
            System.Diagnostics.Process myproc = new System.Diagnostics.Process();
            //得到所有打開的進程 
            try
            {
                foreach (Process thisproc in Process.GetProcessesByName(processName))
                {
                    if (!thisproc.CloseMainWindow())
                    {
                        thisproc.Kill();
                    }
                }
            }
            catch (Exception Exc)
            {
                throw new Exception("", Exc);
            }
        }

        public void KillExcel()
        {
            KillProcess("Excel");
        }
        private void Dispose(bool disposing)
        {
            if (disposing)
            {
                // Dispose managed resources.
                //Marshal.FinalReleaseComObject(m_objExcel);
                m_objRange = null;
                m_objSheet = null;
                m_objSheets = null;
                m_objBooks = null;
                m_objBook = null;
                m_objExcel = null;
            }
        }
        /// <summary>
        /// 取得總記錄數跟可分成幾個Excel sheet.
        /// </summary>
        /// <param name="strTableName">被查詢的數據庫的表名</param>
        /// <param name="sqlConn">sqlConnection</param>
        /// <returns>可分成Excel Sheet的個數</returns>
        private int GetTotalSize(string strTableName, SqlConnection sqlConn)
        {
            //sqlConn = new System.Data.SqlClient.SqlConnection(strConnect);
            sqlCmd = new System.Data.SqlClient.SqlCommand("Select Count(*) From " + strTableName, sqlConn);
            if (this.sqlConn.State == ConnectionState.Closed) sqlConn.Open();
            dbTotalSize = (int)sqlCmd.ExecuteScalar();
            sqlConn.Close();
            return (int)Math.Ceiling(dbTotalSize / this.dbSheetSize);
        }

        /// <summary>
        /// 新建一個Excel實例
        /// </summary>
        /// <param name="strTitle">Excel表頭上的文字</param>
        public void DeclareExcelApp(string[] strTitle, string strSql, string strTableName, string strMastTitle)
        {
            m_objExcel = new Excel.ApplicationClass();
            m_objExcel.Visible = false;
            m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
            m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
            m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
            if (intSheetTotalSize <= 3)
            {
                if (this.dbTotalSize <= this.dbSheetSize)
                {
                    this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
                    return;
                }
                else if (this.dbTotalSize <= this.dbSheetSize * 2)
                {
                    this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
                    this.ExportDataByQueryTable(2, true, strTitle, strSql, strTableName, strMastTitle);
                    return;
                }
                else
                {
                    this.ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
                    this.ExportDataByQueryTable(2, true, strTitle, strSql, strTableName, strMastTitle);
                    this.ExportDataByQueryTable(3, true, strTitle, strSql, strTableName, strMastTitle);
                    return;
                }
            }
            for (int i = 3; i < intSheetTotalSize; i++)
            {
                m_objSheets.Add(m_objOpt, m_objSheets.get_Item(i), m_objOpt, m_objOpt);
            }
            ExportDataByQueryTable(1, false, strTitle, strSql, strTableName, strMastTitle);
            for (int i = 2; i <= m_objSheets.Count; i++)
            {
                ExportDataByQueryTable(i, true, strTitle, strSql, strTableName, strMastTitle);
            }
        }
        /// <summary>
        /// 以用戶輸入的文件名保存文件
        /// </summary>
        public void SaveExcelApp(string excelFileName)//
        {
            //string excelFileName = string.Empty;
            //SaveFileDialog sf = new SaveFileDialog();


            //sf.Filter = "*.xls|*.*";
            //if (sf.ShowDialog() == DialogResult.OK)
            //{
            //    excelFileName = sf.FileName;
            //}
            //else
            //{
            //    return;
            //}
            try
            {
                m_objBook.SaveAs(excelFileName, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
                    Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
                if (m_objExcel != null)
                    m_objExcel = null;
            }
            catch (Exception e)
            {
                string ssss;
                ssss = e.Message + "||" + e.Data;
            }
        }
        /// <summary>
        /// 利用Excel的QueryTable導出數據
        /// </summary>
        /// <param name="intSheetNumber">導出第幾個sheet</param>
        /// <param name="blIsMoreThan">余下的數據是否大于指定的每個Sheet的最大記錄數</param>
        /// <param name="strTitle">表頭,需與查詢sql語句對齊一致。</param>
        /// <param name="strSql">查詢的sql語句，表頭的文字需與該sql語句對齊一致。</param>
        /// <param name="strTablName">查詢的表名</param>    
        /// <param name="strMastTitle">主標題</param>
        /// </summary>
        public void ExportDataByQueryTable(int intSheetNumber, bool blIsMoreThan, string[] strTitle, string strSql, string strTablName, string strMastTitle)
        {
            string strQuery = string.Empty;
            if (blIsMoreThan)
            {
                strQuery = "Select Top " +
                    this.dbSheetSize + strSql + "  From " + strTablName + " Where Not  OrderID In (Select Top " +
                    dbSheetSize * (intSheetNumber - 1) + "  OrderID From " + strTablName + ")";
            }
            else
            {
                strQuery = "Select Top " + this.dbSheetSize + strSql + "  From " + strTablName;

            }
            m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(intSheetNumber));

            m_objSheet.Cells[1, 1] = strMastTitle;
            m_objSheet.Cells[2, 1] = "打印日期" + DateTime.Now.ToString(); //DateTime.Now.ToShortDateString();
            for (int i = 1; i <= strTitle.Length; i++)
            {
                m_objSheet.Cells[4, i] = strTitle[i - 1].ToString();
            }
            m_objRange = m_objSheet.get_Range("A5", m_objOpt);
            m_objQryTable = m_objSheet.QueryTables.Add("OLEDB;Provider=SQLOLEDB.1;" + sqlConn.ConnectionString, m_objRange, strQuery);
            m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
            m_objQryTable.FieldNames = false;
            m_objQryTable.Refresh(false);
        }
    }
}