使用C#将数据库中的数据导入Excel2003

 

先添加对Excel的引用。选择项目-〉添加引用-〉COM-〉添加Microsoft Excel 11.0

看下代码吧

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

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

        }
        public void a()
        {

        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: 这行代码将数据加载到表“pubsDataSet.authors”中。您可以根据需要移动或移除它。
            this.authorsTableAdapter.Fill(this.pubsDataSet.authors);

        }


        #region abc

        /// <summary>
        /// DataGridView导出到Excel
        /// 操作步骤:
        /// 1)先添加对Excel的引用。选择项目-〉添加引用-〉COM-〉添加Microsoft Excel 11.0
        ///   (不同的office讲会有不同版本的dll文件，此版本为Ｏffice 2003).
        /// 2)引入using Excel;和using System.Reflection;
        ///
        /// </summary>
        /// <param name="dgv"></param>
        public void DBtoExcel(DataGridView dgv)
        {
            int rowCount = dgv.RowCount;
            int columnCount = dgv.ColumnCount;
            //

            Excel.Application exc = new Excel.Application();
            if (exc == null)
            {
                throw new Exception("Excel无法启动");
            }
            //
            exc.Visible = true;
            //
            Workbooks workbooks = exc.Workbooks;
            //
            _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            //
            Sheets sheets = exc.Sheets;
            _Worksheet worksheet = (_Worksheet)sheets[1];
            if (worksheet == null)
            {
                throw new Exception("Worksheet error");
            }
            //
            Range r = worksheet.get_Range("A1", Missing.Value);
            if (r == null)
            {
                MessageBox.Show("Range无法启动");
                throw new Exception("Range error");
            }


            //以上是一些例行的初始化工作,下面进行具体的信息填充
            //填充标题
            int ColIndex = 1;
            foreach (DataGridViewColumn dHeader in dgv.Columns)
            {
                worksheet.Cells[1, ColIndex++] = dHeader.HeaderText;
            }
            //获取DataGridView中的所有行和列的数值,填充到一个二维数组中.
            object[,] myData = new object[rowCount + 1, columnCount];
            for (int i = 0; i < rowCount; i++)
            {
                for (int j = 0; j < columnCount; j++)
                {
                    myData[i, j] = dgv[j, i].Value;    //这里的获取注意行列次序
                }
            }
            //将填充好的二维数组填充到Excel对象中.
            r = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[rowCount + 1, columnCount]);
            r.Value2 = myData;
        }




       #endregion

        private void button1_Click(object sender, EventArgs e)
        {
            DBtoExcel(this.dataGridView1);
        }
    }
}

就这样　实现了　往Ｅｘｃｅｌ２００３　导入数据