C#導出Excel源碼

C#導出Excel方法有多種﹐常用的按照Microsoft 的方法如下:
1﹑"Use Automation to Transfer Data Cell by Cell "
2﹑"Use Automation to Transfer an Array of Data to a Range on a Worksheet "
3﹑"Use Automation to Transfer an ADO Recordset to a Worksheet Range "
4﹑"Use Automation to Create a QueryTable on a Worksheet"
5﹑"Use the Clipboard"
6﹑"Create a Delimited Text File that Excel Can Parse into Rows and Columns"
7﹑"Transfer Data to a Worksheet Using ADO.NET "
在此本人主要使用第4種方法:即利用Excel的QueryTable導出海量數據。
要利用Excel的QueryTable的方法﹐首先需要引用Excel類庫。這個我就不多說了。
第二步就是新增一個窗體文件﹐為簡單起見﹐只在窗體上加一個button按鈕。在Button Click事件中加入操作的代碼。整個操作﹐我只在一個類中實現﹐源碼附貼如下(office2003)﹕
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace WindowsApplication3
{
    public partial class Form1 : 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.SqlClient.SqlConnection sqlConn = null;
        private string strConnect = "Data Source='192.168.168.253';Password = SHS;User ID=SHS;Initial Catalog=TEST_KM_ERP";
        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

        public Form1()
        {
            InitializeComponent();
        }

        private int GetTotalSize()
        {
            sqlConn = new System.Data.SqlClient.SqlConnection(strConnect);
            sqlCmd = new System.Data.SqlClient.SqlCommand("Select Count(*) From PD_WORKBIL_MST", sqlConn);
            sqlConn.Open();
            dbTotalSize = (int)sqlCmd.ExecuteScalar();
            sqlConn.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));

[1] [2] [3] [4] [5] [6]  下一页