C#从数据库导出数据[excel]

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using MySql.Data.MySqlClient;

using Microsoft.Office.Interop.Excel;

using Excel = Microsoft.Office.Interop.Excel;   //使用命名空间别名

using System.Reflection;

namespace _01

{

    class Tools

    {

        static void Main(string[]args)

        {

            if(ExportDataTableToExcel(SqlHelper.ExecuteTable("SELECT * FROM students", CommandType.Text, null),@"E:\03.xls"))

            {

                Console.WriteLine("OK!");

            }

            else{

                Console.WriteLine("Fail!");

            }

            Console.ReadKey();

        }

        public static boolExportDataTableToExcel(System.Data.DataTable dt, stringfilepath)

         {

            Excel.ApplicationoXL;          //应用

            Excel.Workbook oWB;             //工作簿

            Excel.WorksheetoSheet;         //工作表

            Excel.Range oRange;             //单元格

            try

            {

                // Start Excel and get Application object.

                oXL = new Excel.Application();              //启动并且创建对象

                // Set some properties 

                oXL.Visible = true;

                oXL.DisplayAlerts = false;                  //设置警告信息的默认值

                // Get a new workbook.

                oWB = oXL.Workbooks.Add(Missing.Value);

                // Get the Active sheet

                oSheet = (Excel.Worksheet)oWB.ActiveSheet;

                oSheet.Name = "Data";

                introwCount = 1;

                foreach(DataRow dr in dt.Rows)

                {

                    rowCount += 1;

                    for(int i = 1; i < dt.Columns.Count +1; i++)

                    {

                        // Add the header the first timethrough

                        if (rowCount ==2)

                        {

                            oSheet.Cells[1, i] = dt.Columns[i -1].ColumnName;

                        }

                        oSheet.Cells[rowCount, i] = dr[i - 1].ToString();

                    }

                }

                // Resize the columns

                oRange = oSheet.Range[oSheet.Cells[1,1],

                              oSheet.Cells[rowCount, dt.Columns.Count]];

                oRange.EntireColumn.AutoFit();

                // Save the sheet and close

                oSheet = null;

                oRange = null;

                oWB.SaveAs(filepath, Excel.XlFileFormat.xlWorkbookNormal,

                    Missing.Value, Missing.Value,Missing.Value, Missing.Value,

                    Excel.XlSaveAsAccessMode.xlExclusive,

                    Missing.Value, Missing.Value,Missing.Value,

                    Missing.Value, Missing.Value);

                oWB.Close(Missing.Value,Missing.Value, Missing.Value);

                oWB = null;

                oXL.Quit();

            }

            catch

            {

                throw;

            }

            finally

            {

                // Clean up

                // NOTE: When in release mode, this does thetrick

                GC.WaitForPendingFinalizers();

                GC.Collect();

                GC.WaitForPendingFinalizers();

                GC.Collect();

            }

            returntrue;

        }

    }

}

Tips:SqlHelper未提供。