c#生成Excel

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using MyExcel = Microsoft.Office.Interop;
using System.Reflection;
using System.Data.SqlClient;

namespace Weblinq
{
    public partial class _Excel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            object missing = System.Reflection.Missing.Value;
            MyExcel.Excel._Application myexcel;
            myexcel = new MyExcel.Excel.ApplicationClass();
            MyExcel.Excel._Workbook wk;
            MyExcel.Excel._Worksheet st;
            wk = myexcel.Workbooks.Add(true);
            DataTable dt = Loaddata();
            for (int i = 0; i < 5; i++)
            {
                st = (MyExcel.Excel._Worksheet)wk.ActiveSheet;
                st.Name = "chsnm" + i.ToString();
                if (i == 4)
                {
                    int colIndex = 1;
                    foreach (DataColumn col in dt.Columns)
                    {
                        colIndex++;
                        myexcel.Cells[4, colIndex] = col.ColumnName;
                        st.get_Range(myexcel.Cells[4, colIndex], myexcel.Cells[4, colIndex]).HorizontalAlignment = MyExcel.Excel.XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐  
                    }
                    int rowIndex = 4;
                    foreach (DataRow row in dt.Rows)
                    {
                        rowIndex++;
                        colIndex = 1;
                        foreach (DataColumn col in dt.Columns)
                        {
                            colIndex++;
                            if (col.DataType == System.Type.GetType("System.DateTime"))
                            {
                                myexcel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
                                st.get_Range(myexcel.Cells[rowIndex, colIndex], myexcel.Cells[rowIndex, colIndex]).HorizontalAlignment = MyExcel.Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐  
                            }
                            else
                                if (col.DataType == System.Type.GetType("System.String"))
                                {
                                    myexcel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                                    st.get_Range(myexcel.Cells[rowIndex, colIndex], myexcel.Cells[rowIndex, colIndex]).HorizontalAlignment = MyExcel.Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐  
                                }
                                else
                                {
                                    myexcel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                                }
                        }
                    }
                    int rowSum = rowIndex + 1;
                    int colSum = 2;
                    myexcel.Cells[rowSum, 2] = "合计";
                    st.get_Range(myexcel.Cells[rowSum, 2], myexcel.Cells[rowSum, 2]).HorizontalAlignment = MyExcel.Excel.XlHAlign.xlHAlignCenter;
                    //  
                    //设置选中的部分的颜色  
                    //  
                    st.get_Range(myexcel.Cells[rowSum, colSum], myexcel.Cells[rowSum, colIndex]).Select();
                    st.get_Range(myexcel.Cells[rowSum, colSum], myexcel.Cells[rowSum, colIndex]).Interior.ColorIndex = 19;//设置为浅黄色，共计有56种  
                    //  
                    //取得整个报表的标题  
                    //  
                    myexcel.Cells[2, 2] = "Orders";
                    //  
                    //设置整个报表的标题格式  
                    //  
                    st.get_Range(myexcel.Cells[2, 2], myexcel.Cells[2, 2]).Font.Bold = true;
                    st.get_Range(myexcel.Cells[2, 2], myexcel.Cells[2, 2]).Font.Size = 22;
                    //  
                    //设置报表表格为最适应宽度  
                    //  
                    st.get_Range(myexcel.Cells[4, 2], myexcel.Cells[rowSum, colIndex]).Select();
                    st.get_Range(myexcel.Cells[4, 2], myexcel.Cells[rowSum, colIndex]).Columns.AutoFit();
                    //  
                    //设置整个报表的标题为跨列居中  
                    //  
                    st.get_Range(myexcel.Cells[2, 2], myexcel.Cells[2, colIndex]).Select();
                    st.get_Range(myexcel.Cells[2, 2], myexcel.Cells[2, colIndex]).HorizontalAlignment = MyExcel.Excel.XlHAlign.xlHAlignCenterAcrossSelection;
                    //  
                    //绘制边框  
                    //  
                    st.get_Range(myexcel.Cells[4, 2], myexcel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
                    st.get_Range(myexcel.Cells[4, 2], myexcel.Cells[rowSum, 2]).Borders[MyExcel.Excel.XlBordersIndex.xlEdgeLeft].Weight = MyExcel.Excel.XlBorderWeight.xlThick;//设置左边线加粗  
                    st.get_Range(myexcel.Cells[4, 2], myexcel.Cells[4, colIndex]).Borders[MyExcel.Excel.XlBordersIndex.xlEdgeTop].Weight = MyExcel.Excel.XlBorderWeight.xlThick;//设置上边线加粗  
                    st.get_Range(myexcel.Cells[4, colIndex], myexcel.Cells[rowSum, colIndex]).Borders[MyExcel.Excel.XlBordersIndex.xlEdgeRight].Weight = MyExcel.Excel.XlBorderWeight.xlThick;//设置右边线加粗  
                    st.get_Range(myexcel.Cells[rowSum, 2], myexcel.Cells[rowSum, colIndex]).Borders[MyExcel.Excel.XlBordersIndex.xlEdgeBottom].Weight = MyExcel.Excel.XlBorderWeight.xlThick;//设置下边线加粗  
                    //  
                    //显示效果  
                    //  
                    myexcel.Visible = true;
                }
                if (i < 3)
                {
                    myexcel.Cells[2, 2] = "chh2h" + i.ToString();

                    myexcel.Sheets.Add(missing, missing, 1, MyExcel.Excel.XlSheetType.xlWorksheet);
                }
            }
            wk.SaveAs(Server.MapPath("cms.xls"), missing, missing, missing, missing, missing, MyExcel.Excel.XlSaveAsAccessMode.xlShared, missing, missing, missing, missing, missing);
          
            myexcel.Quit();
            // Response.ClearContent();
            // Response.ClearHeaders();
            // Response.AppendHeader("Content-Disposition", "attachment;filename=" + Server.MapPath("cms.xls"));
            // Response.Buffer = true;
            // Response.ContentType = "application/ms-excel";
            // //将报表文件存入本地 
            // Response.WriteFile(Server.MapPath("cms.xls"));
            Response.Flush();
            // Response.Close();

        }
        private DataTable Loaddata()
        {

            SqlConnection conn = new SqlConnection("Data Source=XUELIANG-PC;Initial Catalog=Northwind;User ID=sa");
            string sqlstr = "select * from orders";
            SqlDataAdapter sa = new SqlDataAdapter(sqlstr, conn);
            DataSet ds = new DataSet();
            sa.Fill(ds, "admin");
            return ds.Tables[0];

        }
    }
}