default.aspx.cs

把datagrid,datagridview,gdridview,datatable里的数据导出excel

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Reflection;
using Excel;
using System.Data.SqlClient;




public partial class _Default : System.Web.UI.Page
{
    Excel.ApplicationClass myApp;
    baseclass boperate = new baseclass();
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
      
        try
        {
           

            myApp = new Excel.ApplicationClass();
            myApp.Visible = false;
            object oMissiong = System.Reflection.Missing.Value;
            Excel.Workbook myBook = myApp.Workbooks.Add(true);
            Excel.Worksheet mySheet = myBook.Worksheets[1] as Worksheet;
            //设置禁止弹出保存和覆盖的询问提示框
            myApp.DisplayAlerts = false;
            myApp.AlertBeforeOverwriting = false;
            //保存工作簿

            string strFilename = "D:\\test.xls";
         
            int r;
            //获得特定计划序号的天数

            string str = "select count(distinct jh_jx_date) from  jx_plan_detail where jh_page='"+this.TextBox1.Text.Trim()+"'";
            SqlDataReader read = boperate.getread(str);
            read.Read();
            
          r = int.Parse(read[0].ToString());
            
            //int r = int.Parse(this.TextBox1.Text); ;
            object oo = false;
            mySheet.get_Range("A2", "A4").Merge(oo);
            mySheet.get_Range("A2", "A4").Value2 = "序号";
            mySheet.get_Range("B2", "B4").Merge(oo);
            mySheet.get_Range("B2", "B4").Value2 = "施工地点";
            mySheet.get_Range("C2", "C4").Merge(oo);
            mySheet.get_Range("C2", "C4").Value2 = "检修内容";
            //施工进度动态控制单元
            String str1 = "select distinct jh_jx_date from jx_plan_detail where jh_page='" + this.TextBox1.Text.Trim() + "'";
            SqlDataReader rd = boperate.getread(str1);
           
            char a = 'D';
            char a1 = 'A';
            char b1;
            b1 = (char)(a1 + 1);
            if (r <= 7)
            {
                for (int n = 1; n < r + 1; n++)
                {

                    String s1;
                    String s2;
                    s1 = (char)(a + 2) + "3";
                    s2 = a + "3";
                    mySheet.get_Range(s2, s1).Merge(oo);//合并单元格
                    if (rd.Read())
                    {
                        string s = rd["jh_jx_date"].ToString().Substring(0,10);//写入对应单元格日期
                        mySheet.get_Range(s2, s1).Value2 =s;
                    }
                
                   
                    a = (char)(a + 3);
                }
              String str2 = "select *from  jx_plan_detail where jh_page='" + this.TextBox1.Text.Trim() + "'"+" order by jh_jx_date";
                SqlDataReader rd2 = boperate.getread(str2);
                for (int i = 4; i < 3 * r + 4; i++)
                //region 写单元格
                {
                    mySheet.Cells[4, i] = "0点";
                    i = i + 2;
                  
                    rd2.Read();
                   
                      string s = rd2["jh_jx_date"].ToString();
                        SqlDataReader rd21 = boperate.getread("select *from jx_plan_detail where jh_jx_date='" + s + "'" + "and jh_page='" + this.TextBox1.Text.Trim() + "'");

                        int j = 4;
                       while(rd21.Read())
                        {
                            mySheet.Cells[++j, i-2] = rd21["jx_zerotime"].ToString();
                          
                        }
        
                }




                String str3 = "select *from  jx_plan_detail where jh_page='" + this.TextBox1.Text.Trim() + "'" + " order by jh_jx_date";
                SqlDataReader rd3 = boperate.getread(str3);
          
                for (int i = 5; i < 3 * r + 5; i++)
                {
                    mySheet.Cells[4, i] = "8点";
                    i = i + 2;
                    rd3.Read();


                    string s = rd3["jh_jx_date"].ToString();
                    SqlDataReader rd21 = boperate.getread("select *from jx_plan_detail where jh_jx_date='" + s + "'" + "and jh_page='" + this.TextBox1.Text.Trim() + "'");

                    int j = 4;
                    while (rd21.Read())
                    {
                        mySheet.Cells[++j, i - 2] = rd21["jx_eighttime"].ToString();

                    }
        

                }
                String str4 = "select *from  jx_plan_detail where jh_page='" + this.TextBox1.Text.Trim() + "'" + " order by jh_jx_date";
                SqlDataReader rd4 = boperate.getread(str4);
                for (int i = 6; i < 3 * r + 6; i++)
                {
                    mySheet.Cells[4, i] = "4点";
                    i = i + 2;
                    rd4.Read();

                    string s = rd4["jh_jx_date"].ToString();
                    SqlDataReader rd21 = boperate.getread("select *from jx_plan_detail where jh_jx_date='" + s + "'" + "and jh_page='" + this.TextBox1.Text.Trim() + "'");

                    int j = 4;
                    while (rd21.Read())
                    {
                        mySheet.Cells[++j, i - 2] = rd21["jx_fourtime"].ToString();

                    }
                }
                string b2 = (char)(a - 1) + "2";
                mySheet.get_Range("D2", b2).Merge(oo);
                mySheet.get_Range("D2", b2).Value2 = "施工进度";
                b2 = (char)(a) + "2";
                string b3 = (char)(a) + "4";
                mySheet.get_Range(b2, b3).Merge(oo);
                mySheet.get_Range(b2, b3).Value2 = "施工单位";
                b2 = (char)(a + 1) + "2";
                b3 = (char)(a + 1) + "4";
                mySheet.get_Range(b2, b3).Merge(oo);
                mySheet.get_Range(b2, b3).Value2 = "施工负责人";
                b2 = (char)(a + 2) + "2";
                b3 = (char)(a + 2) + "4";
                mySheet.get_Range(b2, b3).Merge(oo);
                mySheet.get_Range(b2, b3).Value2 = "参加人数";
                b2 = (char)(a + 3) + "2";
                b3 = (char)(a + 3) + "4";
                mySheet.get_Range(b2, b3).Merge(oo);
                mySheet.get_Range(b2, b3).Value2 = "备注";

            }
            if (r == 8)
            {
                for (int n = 1; n < 8; n++)
                {

                    String s1;
                    String s2;
                    s1 = (char)(a + 2) + "3";
                    s2 = a + "3";
                    mySheet.get_Range(s2, s1).Merge(oo);//合并单元格
                    if (rd.Read())
                    {
                        string s = rd["jh_jx_date"].ToString().Substring(0, 10);//写入对应单元格日期
                        mySheet.get_Range(s2, s1).Value2 = s;
                    }
                
                  
                    a = (char)(a + 3);
                }
                String str2 = "select *from  jx_plan_detail where jh_page='" + this.TextBox1.Text.Trim() + "'" + " order by jh_jx_date";
                SqlDataReader rd2 = boperate.getread(str2);
                for (int i = 4; i < 24; i++)
                //region 写单元格
                {

                    mySheet.Cells[4, i] = "0点";
                    i = i + 2;
                    rd2.Read();

                    string s = rd2["jh_jx_date"].ToString();
                    SqlDataReader rd21 = boperate.getread("select *from jx_plan_detail where jh_jx_date='" + s + "'" + "and jh_page='" + this.TextBox1.Text.Trim() + "'");

                    int j = 4;
                    while (rd21.Read())
                    {
                        mySheet.Cells[++j, i - 2] = rd21["jx_zerotime"].ToString();

                    }

                }
                String str3 = "select *from  jx_plan_detail where jh_page='" + this.TextBox1.Text.Trim() + "'" + " order by jh_jx_date";
                SqlDataReader rd3 = boperate.getread(str3);
          
                for (int i = 5; i < 25; i++)
                {
                    mySheet.Cells[4, i] = "8点";
                    i = i + 2;