• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号
登陆 注册

C#导出Excel源码(WinForm)

原作者: [db:作者] 来自: [db:来源] 收藏 邀请
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 = @"server=.\SQLExpress;database=Northwind;user=sa;pwd=sa";        
        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 Products", 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));
            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);
            }
        }
        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;
            if (blIsMoreThan)
            {
                strQuery = "Select Top " +
                this.dbSheetSize + " * From Products Where Not  ProductID In (Select Top " +
                dbSheetSize * (intSheetNumber - 1) + "  ProductID From Products)";
            }
            else
            {
                strQuery = "Select Top " + this.dbSheetSize + " * From Products";

            }
            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=SQLOLEDB.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();
        }
    }
}

使用office2000的話﹐好像類庫有些不同﹐為給大家一個比較﹐也將源碼附貼如下﹕

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace WindowsApplication2
{
    public partial class Form1 : Form
    {
        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 = @"server=.\SQLExpress;database=Northwind;user=sa;pwd=sa";
        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 Products", sqlConn);
            sqlConn.Open();
            dbTotalSize = (int) sqlCmd.ExecuteScalar();
            sqlConn.Close();
            return (int) Math.Ceiling(dbTotalSize / this.dbSheetSize);
        }
        private void DeclareExcelApp()
        {
            m_objExcel = new Excel.ApplicationClass();
            m_objBooks = (Excel.Workbooks) m_objExcel.Workbooks;
            m_objBook = (Excel._Workbook) (m_objBooks.Add(m_objOpt));
            m_objSheets = (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);
            }
        }
        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,
                Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
        }
        private void ExportDataByQueryTable(int intSheetNumber, bool blIsMoreThan)
        {
            string strQuery = string.Empty;
            if (blIsMoreThan)
            {
                strQuery = "Select Top " +
                this.dbSheetSize + " * From Products Where Not  ProductID In (Select Top " +
                dbSheetSize * (intSheetNumber - 1) + "  ProductID From Products)";
            }
            else
            {
                strQuery = "Select Top " + this.dbSheetSize + " * From Products";

            }
            m_objSheet = (Excel._Worksheet) (m_objSheets.get_Item(intSheetNumber));
            m_objSheet.Cells[1, 1] = "中文測試一";
            m_objSheet.Cells[1, 2] = "中文測試二";
            m_objSheet.Cells[1, 3] = "中文測試三";
            m_objSheet.Cells[1, 4] = "中文測試四";
            m_objSheet.Cells[1, 5] = "中文測試五";
            m_objRange = m_objSheet.get_Range("A2", m_objOpt);
            m_objQryTable = m_objSheet.QueryTables.Add("OLEDB;Provider=SQLOLEDB.1;" + strConnect, m_objRange, strQuery);
            m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
            m_objQryTable.FieldNames = false;
            m_objQryTable.Refresh(false);
        }

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

        }

    }
}

在VS2008下测试通过！2008年12月29日18:09:14

鲜花
握手
雷人
路过
鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
More+
上一篇：
C#GetHashCode、Equals函数和键值对集合的关系发布时间：2022-07-13
下一篇：
C#加密解密方法(转)发布时间：2022-07-13
热门推荐
More+
热门话题
More+
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服（服务时间 9:00～18:00）

在线QQ客服
地址：深圳市南山区西丽大学城创智工业园
电邮：jeky_zhao#qq.com
移动电话：139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap