C# WinForm导出Excel

本文是仿照改的，应为本公式要用到此功能，
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Drawing.Printing;
using System.Diagnostics;
 

namespace EPPALR05
{


        
    public partial class EPPALR05 : Form
    {


        private Microsoft.Office.Interop.Excel.Application m_objExcel = null;
        private Microsoft.Office.Interop.Excel.Workbooks m_objBooks = null;
        private Microsoft.Office.Interop.Excel._Workbook m_objBook = null;
        private Microsoft.Office.Interop.Excel.Sheets m_objSheets = null;
        private Microsoft.Office.Interop.Excel._Worksheet m_objSheet = null;
        private Microsoft.Office.Interop.Excel.Range m_objRange = null;
        //private Microsoft.Office.Interop.Excel.Font m_objFont = null;
        //private Microsoft.Office.Interop.Excel.QueryTables m_objQryTables = null;
        private Microsoft.Office.Interop.Excel._QueryTable m_objQryTable = null;
        // Frequenty-used variable for optional arguments.
        private object m_objOpt = System.Reflection.Missing.Value;
     
        //DataBase-used variable
        private System.Data.OracleClient.OracleConnection OracleConn = null;
       // private string strConnect = "Data Source=.;Password =123456 ;User ID=sa;database=estan";
       // private string strConnect = "Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.41.21.21)(PORT = 1521))(CONNECT_DATA = (SID = CMSDEV)));Persist Security Info=True;User Id=cms;Password =cms";

        private string strConnect = "Data Source=WZS_DEV;User Id=cms;Password =cms";
      
        private System.Data.OracleClient.OracleCommand OracleCmd = 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

        public EPPALR05()
        {
            InitializeComponent();
        }

        
        private void button2_Click(object sender, EventArgs e)
        {
            Close();
         
        }

        private void Form1_Load(object sender, EventArgs e)
        {
        }


        private int GetTotalSize()
        {
            OracleConn = new System.Data.OracleClient.OracleConnection(strConnect);
            OracleCmd = new System.Data.OracleClient.OracleCommand("SELECT Count(*) FROM cmpps021 c021,cmpps071 c071 "+
                                                                   "WHERE c021.ncmp = c071.ncmp "+
                                                                   "AND c021.orno = c071.orno "+
                                                                   "AND c071.sorn IS NOT NULL", OracleConn);
            OracleConn.Open();

            dbTotalSize = Convert.ToInt32(OracleCmd.ExecuteScalar());

            OracleConn.Close();
            return (int)Math.Ceiling(dbTotalSize / this.dbSheetSize);
        }
        private void DeclareExcelApp()
        {
            m_objExcel = new Microsoft.Office.Interop.Excel.Application();
            m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;
            m_objBook = (Microsoft.Office.Interop.Excel._Workbook)(m_objBooks.Add(m_objOpt));
            m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;

            intSheetTotalSize = GetTotalSize();
            if (intSheetTotalSize <= 3)
            {
                if (this.dbTotalSize <= this.dbSheetSize)
                {
                    this.ExportDataByQueryTable(1, false);
                    return;
                }
                else if (this.dbTotalSize <= this.dbSheetSize * 2)
                {
                    this.ExportDataByQueryTable(1, false);
                    this.ExportDataByQueryTable(2, true);
                    return;
                }
                else
                {
                    this.ExportDataByQueryTable(1, false);
                    this.ExportDataByQueryTable(2, true);
                    this.ExportDataByQueryTable(3, true);
                    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);
            for (int i = 2; i <= m_objSheets.Count; i++)
            {
                ExportDataByQueryTable(i, true);
            }
           
            m_objExcel.Quit();
        }
        private void SaveExcelApp()
        {
            string excelFileName = string.Empty;
            SaveFileDialog sf = new SaveFileDialog();
            sf.Filter = "*.xls|*.*";
            if (sf.ShowDialog() == DialogResult.OK)
            {
                excelFileName = sf.FileName;
            }
            else
            {
                return;
            }
            m_objBook.SaveAs(excelFileName, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
            Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
            m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);         
           
        }
        private void ExportDataByQueryTable(int intSheetNumber, bool blIsMoreThan)
        {
            string strQuery = string.Empty;

             //提示这里是写分页，我这里不需要，在这里我就没有用到intSheetNumber 参数了。。。
            if (RdoPacking.Checked == true)
            {
                strQuery =
                       " SELECT" +
                       "   C021.NCMP, C021.HCUNO" +
                       " FROM" +
                       "   CMPPS021 C021, CMPPS071 C071" +
                       " WHERE" +
                       "   C021.NCMP = C071.NCMP" +
                       "   AND C021.ORNO = C071.ORNO" +
                       "   AND C071.SORN IS NOT NULL" + 
                       "   AND C021.EPS_PAKNO = '" + txteps_pakno.Text.Trim() + "'";
            }
            else if (RdoContract.Checked == true)
            {            
                strQuery =
                       " SELECT" +
                       "   C021.NCMP, C021.HCUNO" +
                       " FROM" +
                       "   CMPPS021 C021, CMPPS071 C071, EPPALINV EINV, EPPALCON ECON" +
                       " WHERE" +
                       "   C021.NCMP = C071.NCMP" +
                       "   AND C021.ORNO = C071.ORNO" +
                       "   AND C071.SORN IS NOT NULL" +
                       "   AND C021.NCMP = EINV.COMPNO" +
                       "   AND TO_CHAR (C021.PINO) = EINV.INVNO" +
                       "   AND ECON.PACKLST = EINV.PACKLST" +
                       "   AND ECON.CONTNO ='" + txteps_pakno.Text.Trim() + "'";
            }        
           
            m_objSheet = (Microsoft.Office.Interop.Excel._Worksheet)(m_objSheets.get_Item(intSheetNumber));

            m_objSheet.get_Range("A1", m_objOpt).set_Value(m_objOpt, "一");

            m_objSheet.get_Range("B1", m_objOpt).set_Value(m_objOpt, "二");
 
            m_objSheet.get_Range("C1", m_objOpt).set_Value(m_objOpt, "三");

            m_objSheet.get_Range("D1", m_objOpt).set_Value(m_objOpt, "四");

            m_objSheet.get_Range("E1", m_objOpt).set_Value(m_objOpt, "五");

           
            m_objRange = m_objSheet.get_Range("A2", m_objOpt);
           
            m_objQryTable = m_objSheet.QueryTables.Add("OLEDB;Provider=OracleOLEDB.1;" + strConnect, m_objRange, strQuery);

            m_objQryTable.RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertEntireRows;
            m_objQryTable.FieldNames = false;
            m_objQryTable.Refresh(false); //重新从数据源中读取当条记录
            
           
        }


        private void button1_Click(object sender, EventArgs e)
        {
            DeclareExcelApp();
            SaveExcelApp();  
        }

    }

}