Oracle数据表转换为Excele表

先添加引用：Microsoft.Office.Interop.Excel;、System.Runtime.InteropServices;、System.Data;、System.Web.Configuration;

 

namespace WebApplication1
{
    public class DataExcel
    {
        private static string conn_str = WebConfigurationManager.AppSettings["connString"]; //数据库连接字符串
        #region 启动导出数据的方法
        public static System.Data.DataTable ExecQue()
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            using (OracleConnection conn=new OracleConnection(conn_str))
            {
                conn.Open();                                                               //SQL查询语句
                OracleDataAdapter adp = new OracleDataAdapter(" select * from db_lwj", conn_str);
                adp.Fill(dt);
            }
            return dt;
        }
        #endregion
    }

    public class DataChangeExcel
    {
        public static void DataToExcel(System.Data.DataTable dt, string SaveFile)
        {
            Microsoft.Office.Interop.Excel.Application excel;
            Microsoft.Office.Interop.Excel._Workbook workBook;
            Microsoft.Office.Interop.Excel._Worksheet workSheet;
            object misValue = System.Reflection.Missing.Value;
            excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            workBook = excel.Workbooks.Add(misValue);
            workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.ActiveSheet;

            int rowIndex = 1,colIndex=0;
            //遍历数据表，将字段名填充到Excel中第一行
            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
            }
            //遍历数据表，将数据填充到Excel中
            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim();

                    //设置表格内容居中对齐
                    workSheet.get_Range(excel.Cells[rowIndex, colIndex],excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                }
            }

            excel.Visible = false;
            workBook.SaveAs(SaveFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            dt = null;
            workBook.Close(true, misValue, misValue);
            excel.Quit();
            PublicMethod.Kill(excel);
            releaseObject(workSheet);
            releaseObject(workBook);
            releaseObject(excel);
        }

        private static void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception)
            {

                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)
        {
            IntPtr t = new IntPtr(excel.Hwnd);
            int k = 0;
            GetWindowThreadProcessId(t,out k);

            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
            p.Kill();
        }
    }
}

 

 

                                                                                                    //设置导出路径

调用：DataChangeExcel.DataToExcel(DataExcel.ExecQue(),@"F:\outputFormDataBase.xls");