创建EXCEl文件

 #region 创建EXCEl文件
    /// <summary>
    ///
    /// </summary>
    /// <param name="fileName">传一个excel文件名</param>
    /// <param name="worksheetName">传一个worksheet名字</param>
    /// <param name="colNames">传一个包含全部列名的数组</param>
    /// <param name="dt">传包含所有要写入excel文件的数据的dataTable</param>
    public void CreatExcelFile(string fileName, string worksheetName, string[] colNames, DataTable dt)
    {
        Microsoft.Office.Interop.Excel.Application excel = null;
        Microsoft.Office.Interop.Excel.Workbook workbook = null;
        Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
        try
        {
            //创建excel对象
            excel = new Microsoft.Office.Interop.Excel.Application();
            System.Reflection.Missing missing = System.Reflection.Missing.Value;
            //设置表的序列
            int workbookIndex = 1;
            //创建一个workbook并添加到excel中
            workbook = excel.Workbooks.Add(missing);
            //创建一个worksheet
            worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
            if (string.Empty != worksheetName)
                worksheet.Name = worksheetName;
            else
                worksheet.Name = "sheet1";
            if (dt.Rows.Count <= 0)
            {
                throw new Exception("没有数据");
            }
            //获取列名并添加到表中
            if (dt.Columns.Count >= colNames.GetLength(0))
            {
                int RowNameIndex = 1;
                int colNameIndex = 0;
                for (; colNameIndex < Math.Max(dt.Columns.Count, colNames.GetLength(0)); colNameIndex++)
                {
                    DataColumn dc = dt.Columns[colNameIndex];
                    if (colNameIndex < dt.Columns.Count)
                    {
                        excel.Cells[RowNameIndex, colNameIndex + 1] = dc.ColumnName;
                    }
                    if (colNameIndex < colNames.GetLength(0))
                    {
                        excel.Cells[RowNameIndex + 1, colNameIndex + 1] = colNames[colNameIndex];
                    }

                }
            }
            else
            {
                throw new Exception("数据库中数据的内容少于要展示的内容");
            }
            int RowIndex = 2;
            int colIndex = 0;
            //把数据写入到表里
            foreach (DataRow row in dt.Rows)
            {
                RowIndex++;
                colIndex = 0;
                foreach (DataColumn dc in dt.Columns)
                {
                    if (colIndex <= excel.Cells.Columns.Count)
                    {
                        colIndex++;
                        ((Microsoft.Office.Interop.Excel.Range)excel.Cells[RowIndex, colIndex]).NumberFormatLocal = "@";
                        excel.Cells[RowIndex, colIndex] = row[dc.ColumnName].ToString();
                    }

                }

            }
            //不可见，即后台处理
            excel.Visible = false;
            excel.DisplayAlerts = false;
            //返回刚才创建的Excel表格
            workbook.Saved = true;
            //workbook.SaveCopyAs(fileName + ".xls");
            //workbook.SaveAs(
            string tempFileName = Server.MapPath("~/upload/temp/" + System.DateTime.Now.Millisecond.ToString() + ".xls");
            // string tempFileName = System.DateTime.Now.Millisecond.ToString() + ".xls";
            workbook.SaveAs(tempFileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
            //设置文件下载
            if (worksheet != null)
            {

                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                worksheet = null;
            }
            if (workbook != null)
            {
                workbook.Close(false, null, null);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;
            }

            if (excel != null)
            {
                excel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                excel = null;
            }

            GC.Collect();
            Response.ClearContent();
            Response.ClearHeaders();
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName) + ".xls;charset=GB2312");
            Response.Buffer = true;
            Response.ContentType = "application/ms-excel";
            //将报表文件存入本地
            Response.WriteFile(tempFileName);
            Response.Flush();
            Response.Close();
        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            if (worksheet != null)
            {

                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                worksheet = null;
            }
            if (workbook != null)
            {
                workbook.Close(false, null, null);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;
            }

            if (excel != null)
            {
                excel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                excel = null;
            }

            GC.Collect();
        }

    }

    #endregion