C#将数据库数据导出为Excel

1、创建DataChangeExcel方法

public class DataChangeExcel
    {
       public static void DataSetToExcel(DataTable dataTable, 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;
               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().Insert(0,"'");
                       //设置表格内容居中对齐
                       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);
               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
               {
               }
           }
       }
    }

2、进行方法调用

public class DataBaseHelper
        {
            public static DataTable ExecuterQuery(string connectionString, string commandSql)
            {
                DataTable dataTable = new DataTable();
                try
                {
                    using (SqlConnection conn = new SqlConnection(connectionString))
                    {
                        conn.Open();
                        using (SqlDataAdapter adapter = new SqlDataAdapter(commandSql, conn))
                        {
                            adapter.Fill(dataTable);
                        }
                        conn.Close();
                    }
                }
                catch
                {
                    return null;
                }
                return dataTable;
            }
        }
         
        public class DataBaseDao
        {
            public static DataTable GetDataBaseTable()
            {
                string sql = "select POC_SN,POC_ADDRESS,POC_LAT,POC_LNG,METER_NO,DLMSPASSWORD,SIM_NO,CUSTOMER_NO,CUSTOMER_NAME,[1P/3P],GARDU from Excel1";
                return DataBaseHelper.ExecuterQuery("server=172.16.100.59;DataBase=Load;uid=sa;pwd=123456",sql);
            }
        }
         
 //生成Excel,path为文件存放路径
 DataChangeExcel.DataSetToExcel(DataBaseDao.GetDataBaseTable(), path);