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();

                }

            }

        }

    }

}
巴特西

WinForm导出DataSet到Excel

最新文章

热门文章
