自己在看到网上一些table数据导出excel，自己为自己记录一下以免以后需要

需要自己去查到数据库的表格

直接调用方法 （需要电脑上安装有excel表格程序，否则导出失败：个人猜测）

 protected void Button1_Click(object sender, EventArgs e)
        {
            ajaxService ajax = new ajaxService();
            DataTable dt = ajax.SearchEmployees1();
            Guid guid = Guid.NewGuid();
            string stamp = guid.ToString("N");
            string path = "~/Fileupimage/axb.xls";
            ExportToExcel(dt, Server.MapPath(path));
        }


#region 导出Excel表
        /// 

        /// 将DataTable或DataSet导出为Excel
        /// 修改  2016/7/6
        /// 
        /// 需要导出的DataTable,DataSet或GridView
        /// Excel的绝对路径
        /// 
   
   
    
    是否成功
   
   
        public bool ExportToExcel(System.Data.DataTable dt, string AbosultedFilePath)
        {
            if (dt == null)
                return false;
            // 创建Excel应用程序对象
            Microsoft.Office.Interop.Excel.Application excApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workBook = excApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet workSeet = workBook.Worksheets[1]; //取得sheet1
            Microsoft.Office.Interop.Excel.Range range = null;
            int tableCount = dt.Rows.Count;
            int rowRead = 0;
            float percent = 0;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                workSeet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                //设置标题的样式
                range = (Microsoft.Office.Interop.Excel.Range)workSeet.Cells[1, i + 1];
                range.Font.Bold = true; //粗体
                range.Font.Size = "12";//字体大小
                range.Font.Name = "宋体";
                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中
                range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null); //背景色
                range.EntireColumn.AutoFit(); //自动设置列宽
                range.EntireRow.AutoFit(); //自动设置行高

            }
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    //写入内容
                    workSeet.Cells[r + 2, c + 1] = "'" + dt.Rows[r][c].ToString();
                    //设置样式
                    range = (Microsoft.Office.Interop.Excel.Range)workSeet.Cells[r + 2, c + 1];
                    range.Font.Size = 9; //字体大小
                    range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框
                    range.EntireColumn.AutoFit(); //自动调整列宽
                }
                rowRead++;
                percent = ((float)(100 * rowRead)) / tableCount;
                //System.Windows.Forms.Application.DoEvents();
            }
            range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            if (dt.Columns.Count > 1)
            {
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            }
            try
            {
                workBook.Saved = true;
                workBook.SaveCopyAs(AbosultedFilePath);
                string fileName = "axb.xls";//客户端保存的文件名
                //此处实现下载功能
                FileInfo fileInfo = new FileInfo(AbosultedFilePath);
                Response.Clear();
                Response.ClearContent();
                Response.ClearHeaders();
                Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
                Response.AddHeader("Content-Length", fileInfo.Length.ToString());
                Response.AddHeader("Content-Transfer-Encoding", "binary");
                Response.ContentType = "application/octet-stream";
                Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
                Response.WriteFile(fileInfo.FullName);
                Response.Flush();
                Response.End();
            }
            catch { }
            workBook.Close();
            if (excApp != null)
            {
                excApp.Workbooks.Close();
                excApp.Quit();
                int generation = System.GC.GetGeneration(excApp);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excApp);
                excApp = null;
                System.GC.Collect(generation);
            }
            GC.Collect(); //强行销毁
            #region 强行杀死最近打开的Excel进程
            System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
            System.DateTime startTime = new DateTime();
            int m, killID = 0;
            for (m = 0; m < excelProc.Length; m++)
            {
                if (startTime < excelProc[m].StartTime)
                {
                    startTime = excelProc[m].StartTime;
                    killID = m;
                }
            }
            if (excelProc[killID].HasExited == false)
            {
                excelProc[killID].Kill();
            }
            #endregion
            return true;
        }
        #endregion