Jean 筆記本

using System.Data.SqlClient;

using Microsoft.Office.Interop.Excel;

using System.IO;

           #region "產生EXECL套表"

            try

            {

                Microsoft.Office.Interop.Excel.Application m_objExcel = null;

                this.Cursor = Cursors.WaitCursor;

                SqlCommand com = new SqlCommand("SP_NCCC_TOT", cConn.connACQ);

                com.CommandType = System.Data.CommandType.StoredProcedure;

                com.Parameters.AddWithValue("@SETTLE_DATE_BEG", dtpSettle_Date_BGN.Value.ToString("yyyy/MM/dd"));

                com.CommandType = System.Data.CommandType.StoredProcedure;

                com.Parameters.AddWithValue("@SETTLE_DATE_END", dtpSettle_Date_END.Value.ToString("yyyy/MM/dd"));

                SqlParameter NONUS_V_SAMT = com.Parameters.AddWithValue("@NONUS_V_SAMT", 0);

                NONUS_V_SAMT.Direction = System.Data.ParameterDirection.Output;

                SqlParameter NONUS_M_SAMT = com.Parameters.Add("@NONUS_M_SAMT", SqlDbType.Decimal, 18);

                NONUS_M_SAMT.Direction = System.Data.ParameterDirection.Output;

                SqlParameter NONUS_J_SAMT = com.Parameters.Add("@NONUS_J_SAMT", SqlDbType.Decimal, 18);

                NONUS_J_SAMT.Direction = System.Data.ParameterDirection.Output;

                SqlParameter NONUS_V_BAMT = com.Parameters.Add("@NONUS_V_BAMT", SqlDbType.Decimal, 18);

                NONUS_V_BAMT.Direction = System.Data.ParameterDirection.Output;

                SqlParameter NONUS_M_BAMT = com.Parameters.Add("@NONUS_M_BAMT", SqlDbType.Decimal,18 );

                NONUS_M_BAMT.Direction = System.Data.ParameterDirection.Output;

                SqlParameter NONUS_J_BAMT = com.Parameters.Add("@NONUS_J_BAMT", SqlDbType.Decimal,18 );

                NONUS_J_BAMT.Direction = System.Data.ParameterDirection.Output;

                SqlDataReader reader = com.ExecuteReader();

                System.Data.DataTable dtQryData = new System.Data.DataTable();

                dtQryData.Load(reader);

                reader.Close();

                if (dtQryData.Rows.Count == 0)

                {

                    MessageBox.Show("查無資料!!", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);

                    return;

                }

                m_objExcel = new Microsoft.Office.Interop.Excel.Application();

                Workbook m_objBooks = m_objExcel.Workbooks.Open(strReportPath + "\\Report\\NCCC_Total.xls", Type.Missing, Type.Missing, Type.Missing

                                                                , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing

                                                                , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                Sheets m_objSheets = m_objBooks.Worksheets;

                Worksheet m_objWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objSheets.get_Item(1);

                m_objExcel.DisplayAlerts = false; m_objExcel.Visible = false; m_objExcel.UserControl = false;

                //產生中文年月

                string strCMonth = Convert.ToString(dtpSettle_Date_END.Value.Year - 1911) + "年" + dtpSettle_Date_END.Value.ToString("MM月dd日");

                //表頭

                m_objWorkSheet.Cells[1, 1] = strCMonth + "信用卡收單交易統計表";

                m_objWorkSheet.Cells[2, 1] = "請款日期:" + dtpSettle_Date_BGN.Value.ToString("yyyy/MM/dd") + "~" + dtpSettle_Date_END.Value.ToString("yyyy/MM/dd");

                //將整個DataTable貼到EXECL

                for (int i = 0; i < dtQryData.Rows.Count; i++)

                {

                    for (int j = 0; j < dtQryData.Columns.Count; j++)

                    { m_objWorkSheet.Cells[i + 7, j + 1] = dtQryData.Rows[i][j].ToString(); }

                }

                m_objWorkSheet.Cells[41, 18] = NONUS_V_SAMT.Value.ToString();

                m_objWorkSheet.Cells[41, 19] = NONUS_M_SAMT.Value.ToString();

                m_objWorkSheet.Cells[41, 20] = NONUS_J_SAMT.Value.ToString();

                m_objWorkSheet.Cells[42, 18] = NONUS_V_BAMT.Value.ToString();

                m_objWorkSheet.Cells[42, 19] = NONUS_M_BAMT.Value.ToString();

                m_objWorkSheet.Cells[42, 20] = NONUS_J_BAMT.Value.ToString();

                //把sheet存成另一個檔案

                if (!Directory.Exists(strReportPath + "\\Excel報表"))

                {

                    DirectoryInfo di = Directory.CreateDirectory(strReportPath + "\\Excel報表");

                }

                //另存檔名路徑

                string TmpFileName = strReportPath + "\\Excel報表\\收單交易統計表" + dtpSettle_Date_BGN.Value.ToString("yyyyMMdd") + "_" + dtpSettle_Date_END.Value.ToString("yyyyMMdd") + ".xls";

                m_objBooks.SaveAs(TmpFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange

                                , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                m_objBooks.Close(false, Type.Missing, Type.Missing);

                m_objExcel.Quit();

                MessageBox.Show("資料轉出成 Execl \n\r 路徑：[ " + TmpFileName + " ]", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);

            }

            finally

            {

                this.Cursor = Cursors.Default;

            }

            #endregion