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

using System;

成都创新互联拥有网站维护技术和项目管理团队,建立的售前、实施和售后服务体系,为客户提供定制化的成都做网站、成都网站建设、成都外贸网站建设、网站维护、德阳服务器托管解决方案。为客户网站安全和日常运维提供整体管家式外包优质服务。我们的网站维护服务覆盖集团企业、上市公司、外企网站、商城网站建设、政府网站等各类型客户群体,为全球上1000家企业提供全方位网站维护、服务器维护解决方案。

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

{

    classTools

    {

        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未提供。


文章题目:C#从数据库导出数据[excel]
网页路径:http://cdiso.cn/article/gpsegi.html

其他资讯