有时候需要将数据库的数据导出至Excel表格表格，以便进行查看和分析，那么如何导出呢？下面用代码来实现。

1、创建一个表格，并插入如下数据。

drop table TABLETESTEXCEL;  create table TABLETESTEXCEL  (    col_id   NUMBER not null,    col_name VARCHAR2(32),    col_age  NUMBER,    col_sex  VARCHAR2(4),    col_work VARCHAR2(32),    col_mony FLOAT  );

数据：

insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  values (1, '吴一', 25, '男', '.NET', 5000);    insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  values (2, '孙二', 24, '男', 'JAVA', 4999);    insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  values (3, '张三', 25, '男', 'PHP', 5001);    insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  values (4, '李四', 26, '男', 'DELPHI', 5002);    insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  values (5, '王五', 27, '男', 'C++', 5003);    insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  values (6, '赵六', 25, '男', 'C', 4008);    insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  values (7, '燕七', 25, '男', '数据库', 4007);    insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  values (8, '胡八', 25, '男', 'JSP', 5005);    insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  values (9, '钱九', 25, '男', 'ASP.NET', 4005);    insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  values (10, '沈十', 25, '男', 'VB', 4000);  commit;

2、C#代码实现

数据库操作的类：

public class DataBaseHelper   {      public static DataTable ExecuterQuery(string connectionString, string commandSql)      {          DataTable dataTable = new DataTable();            try          {            using (OracleConnection oracleConnection =             new OracleConnection(connectionString))            {              oracleConnection.Open();                using (OracleDataAdapter oracleDataAdapter =               new OracleDataAdapter(commandSql,oracleConnection))                  {                     oracleDataAdapter.Fill(dataTable);                  }                    oracleConnection.Close();            }          }          catch          {            return null;          }         return dataTable;      }   }

public class DataBaseDao  {     public static DataTable GetDataBaseTable()     {        string sql = " SELECT * FROM tableTestExcel";          return DataBaseHelper.ExecuterQuery("User ID=downsoft;Password=sys;Data Source=orcl", sql);     }  }

 导出Excel的类：

public class DataChangeExcel  {          /// 
          /// 数据库转为excel表格           /// 
         /// 数据库数据         /// 导出的excel文件          public static void DataSetToExcel(DataTable dataTable, string SaveFile)          {              Excel.Application excel;                Excel._Workbook workBook;                Excel._Worksheet workSheet;                object misValue = System.Reflection.Missing.Value;                excel = new Excel.ApplicationClass();                workBook = excel.Workbooks.Add(misValue);                workSheet = (Excel._Worksheet)workBook.ActiveSheet;                int rowIndex = 1;                int colIndex = 0;                //取得标题              foreach (DataColumn col in dataTable.Columns)              {                  colIndex++;                    excel.Cells[1, colIndex] = col.ColumnName;              }                //取得表格中的数据              foreach (DataRow row in dataTable.Rows)              {                  rowIndex++;                    colIndex = 0;                    foreach (DataColumn col in dataTable.Columns)                  {                     colIndex++;                       excel.Cells[rowIndex, colIndex] =                                                     row[col.ColumnName].ToString().Trim();                        //设置表格内容居中对齐                        workSheet.get_Range(excel.Cells[rowIndex, colIndex],                                                     excel.Cells[rowIndex, colIndex]).HorizontalAlignment =                                                     Excel.XlVAlign.xlVAlignCenter;                  }              }                excel.Visible = false;                workBook.SaveAs(SaveFile, Excel.XlFileFormat.xlWorkbookNormal, misValue,                     misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive,                                     misValue, misValue, misValue, misValue, misValue);                dataTable = null;                workBook.Close(true, misValue, misValue);                excel.Quit();                PublicMethod.Kill(excel);//调用kill当前excel进程                releaseObject(workSheet);                releaseObject(workBook);                releaseObject(excel);            }            private static void releaseObject(object obj)          {              try              {                  System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);                  obj = null;              }              catch              {                  obj = null;              }              finally              {                  GC.Collect();              }          }   }

关闭进程的类:

public class PublicMethod  {     [DllImport("User32.dll", CharSet = CharSet.Auto)]       public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);       public static void Kill(Microsoft.Office.Interop.Excel.Application excel)     {         try         {           IntPtr t = new IntPtr(excel.Hwnd);             int k = 0;             GetWindowThreadProcessId(t, out k);             System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);                            p.Kill();           }          catch          { }     }  }

写好了如上的类，那么开始调用吧，调用：

DataChangeExcel.DataSetToExcel(DataBaseDao.GetDataBaseTable(),   @"F:\outputFormDataBase.xls");

这样成功将数据导出，如图。