将DataTable对象输出到新建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;

namespace ToExcel
{
    public partial class Form4 : Form
    {
        public Form4()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (this.CreateExcelFileForDataTable(this.GetData(), "c:\\test2.xls", null))
            {
                MessageBox.Show("成功!");
            }
        }



        将DataTable对象输出到新建EXCEL文档中#region 将DataTable对象输出到新建EXCEL文档中

        /**//// <summary>
        /// 将DataTable对象输出到新建EXCEL文档中
        /// </summary>
        /// <param name="table">DataTable对象</param>
        /// <param name="strFullFilePath">要创建的xls文件的完整路径</param>
        /// <param name="title">一个标题</param>
        /// <returns></returns>
        public bool CreateExcelFileForDataTable(System.Data.DataTable table, string strFullFilePath, string title)
        {
            //文件存在时先删除文件后再进行下一步操作
            FileInfo file = new FileInfo(strFullFilePath);
            if (file.Exists)
            {
                file.Delete();
            }
            int rowIndex = 3;      //开始写入数据的单元格行
            int colIndex = 0;      //开始写入数据的单元格列
            System.Reflection.Missing miss = System.Reflection.Missing.Value;
            Excel.ApplicationClass mExcel = new Excel.ApplicationClass();//创建一个Excel对象
            mExcel.Visible = false;
            Excel.Workbooks mBooks = (Excel.Workbooks)mExcel.Workbooks;
            Excel.Workbook mBook = (Excel.Workbook)(mBooks.Add(miss));
            Excel.Worksheet mSheet = (Excel.Worksheet)mBook.ActiveSheet;
            Excel.Range er = mSheet.get_Range((object)"A1", System.Reflection.Missing.Value); //向Excel文件中写入标题文本
            er.Value2 = title;
            try
            {
                foreach (DataColumn col in table.Columns)    //将所得到的表的列名,赋值给单元格
                {
                    colIndex++;
                    mSheet.Cells[3, colIndex] = col.ColumnName;
                }
                foreach (DataRow row in table.Rows)    //同样方法处理数据
                {
                    rowIndex++;
                    colIndex = 0;
                    foreach (DataColumn col in table.Columns)
                    {
                        colIndex++;
                        if (colIndex == 2)
                        {
                            mSheet.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();//第二行数据为银行帐号信息转换为字符防止首位0丢失
                        }
                        else
                        {
                            mSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        }
                    }
                }
                //保存工作已写入数据的工作表
                mBook.SaveAs(strFullFilePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
                return true;
            }
            catch (Exception ee)
            {
                throw new Exception(ee.Message);
            }
            finally    //finally中的代码主要用来释放内存和中止进程()
            {
                mBook.Close(false, miss, miss);
                mBooks.Close();
                mExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(er);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(mSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(mBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(mBooks);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(mExcel);
                GC.Collect();
            }
            return false;
        }

        DataTable实例#region DataTable实例
        /**//// <summary>
        /// DataTable实例
        /// </summary>
        /// <returns></returns>
        public System.Data.DataTable GetData()
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            DataColumn dc1 = new DataColumn("Name");
            DataColumn dc2 = new DataColumn("Age");
            dt.Columns.Add(dc1);
            dt.Columns.Add(dc2);

            DataRow dr1 = dt.NewRow();
            dr1[0] = "Lily";
            dr1[1] = "10";
            DataRow dr2 = dt.NewRow();
            dr2[0] = "Lucy";
            dr2[1] = "9";
            dt.Rows.Add(dr1);
            dt.Rows.Add(dr2);
            return dt;
        }
        #endregion

        #endregion

    }
}