WinForm导出DataSet到Excel

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Collections;
using System.Data.SqlClient;
using Microsoft.Office.Core;
using System.Diagnostics;
using System.Reflection;
using Microsoft.Office.Interop.Excel;

//添加引用-->COM-->Microsoft Excel 12.0 Object Library

namespace TestExcel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private static string connStr = "User Id=sa;Password=123456;Data Source=.;Initial Catalog=TDRDB;";

       

        //查询产品
        public static List<Object> getProducList()
        {
            SqlConnection conn = null;
            List<Object> producList = new List<Object>();
            string sqlStr = string.Format("select * from production order by type");
          
            try
            {
                using (conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(sqlStr, conn);
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Production p = new Production();
                        p.Id = int.Parse(Convert.ToString(dr["ID"]));
                        p.Name = Convert.ToString(dr["name"]);
                        p.Type = Convert.ToString(dr["type"]);
                        p.Pic = Convert.ToString(dr["pic"]);
                        p.NetPrice = Double.Parse(Convert.ToString(dr["netPrice"]));
                        p.GrossPrice = Double.Parse(Convert.ToString(dr["grossPrice"]));
                        p.Mark = Convert.ToString(dr["mark"]);
                        producList.Add(p);
                    }

                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return producList;

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

            DataSet ds = new DataSet();
            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Columns.AddRange(new DataColumn[] { new DataColumn("编号"), new DataColumn("名称"), new DataColumn("型号"), new DataColumn("图片"), new DataColumn("净价"), new DataColumn("毛价"), new DataColumn("备注"), });
            for (int i = 0; i < getProducList().Count; i++)
            {
                Production produc=(Production)getProducList()[i];
                String[] rows = new String[] { produc.Id.ToString(), produc.Name, produc.Type, produc.Pic, produc.NetPrice.ToString(), produc.GrossPrice.ToString(), produc.Mark };
                dt.Rows.Add(rows);
            }
            ds.Tables.Add(dt);
            ExportToExcel(ds, "D:\\产品信息");

        }


        public static void ExportToExcel(DataSet dataSet, string fileName)
        {

           
            //Excel程序
            Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application();
            excelApplication.DisplayAlerts = false;
            //工作簿
            Workbook workbook = excelApplication.Workbooks.Add(Missing.Value);
            //上一个工作簿
            Worksheet lastWorksheet = (Worksheet)workbook.Worksheets.get_Item(workbook.Worksheets.Count);
            //空白工作簿
            Worksheet newSheet = (Worksheet)workbook.Worksheets.Add(Type.Missing, lastWorksheet, Type.Missing, Type.Missing);

            foreach (System.Data.DataTable dt in dataSet.Tables)
            {
                newSheet.Name = dt.TableName;

                for (int col = 0; col < dt.Columns.Count; col++)
                {
                    newSheet.Cells[1, col + 1] = dt.Columns[col].ColumnName;
                }
                for (int row = 0; row < dt.Rows.Count; row++)
                {
                    for (int col = 0; col < dt.Columns.Count; col++)
                    {
                        newSheet.Cells[row + 2, col + 1] = dt.Rows[row][col].ToString();
                    }
                }
            }

            try
            {
                newSheet.Cells.Font.Size = 12;
                ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1)).Delete();
                ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1)).Delete();
                ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1)).Delete();
                //((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1)).Activate();
                workbook.Close(true, fileName, System.Reflection.Missing.Value);
                MessageBox.Show("成功导出Excel，请查看！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                excelApplication.Quit();
                Process[] excelProcesses = Process.GetProcessesByName("EXCEL");
                DateTime startTime = new DateTime();

                int processId = 0;
                for (int i = 0; i < excelProcesses.Length; i++)
                {
                    if (startTime < excelProcesses[i].StartTime)
                    {
                        startTime = excelProcesses[i].StartTime;
                        processId = i;
                    }
                }

                if (excelProcesses[processId].HasExited == false)
                {
                    excelProcesses[processId].Kill();
                }
            }

        }


    }
}

评论

2 楼 wei_cao 2012-09-05  

这是咋回事列?

1 楼 wei_cao 2012-09-05  

無法將型別 'System.__ComObject' 的 COM 物件轉換為介面型別 'Microsoft.Office.Interop.Excel.Application'。由於發生下列錯誤，介面 (IID 為 '{000208D5-0000-0000-C000-000000000046}') 之 COM 元件上的 QueryInterface 呼叫失敗而導致作業失敗: 程式庫未登錄。 (發生例外狀況於 HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED))