导出excel表问题

Question

protected void Button1_Click(object sender, EventArgs e)
    {
        string sqlstr = "select * from fault";
        ds = d.GetData(sqlstr, "excel");//获取数据放到excel表上，已验证ds.Tables["excel"]已有数据        int ItenCountPerSheet = 500;
        int SheetCount = Convert.ToInt32(Math.Ceiling((double)ds.Tables["excel"].Rows.Count / ItenCountPerSheet));
        Response.ClearContent();
        Response.BufferOutput = true;
        Response.Charset = "utf-8";
        Response.ContentType = "application/ms-excel";
        Response.AddHeader("Content-Transfer-Encoding", "binary");
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode("Excel表格测试") + ".xml");
        //Response.AppendHeader("Content-Disposition", "attachment;filename="+Server.UrlEncode("孟宪会Excel表格测试")+".xls");
        // 采用下面的格式，将兼容 Excel 2003,Excel 2007, Excel 2010。        String FileName = "Excel表格测试";
        if (!String.IsNullOrEmpty(Request.UserAgent))
        {
            // firefox 里面文件名无需编码。
            if (!(Request.UserAgent.IndexOf("Firefox") > -1 && Request.UserAgent.IndexOf("Gecko") > -1))
            {
                FileName = Server.UrlEncode(FileName);
            }
        }
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xml");
        Response.Write("<?xml version='1.0'?><?mso-application progid='Excel.Sheet'?>");
        Response.Write(@"<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
      xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'
      xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'>");
        Response.Write(@"<DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>");
        Response.Write(@"<Author>孟宪会</Author><LastAuthor>孟子E章</LastAuthor>
          <Created>2010-09-08T14:07:11Z</Created><Company>mxh</Company><Version>1990</Version>");
        Response.Write("</DocumentProperties>");
        Response.Write(@"<Styles><Style ss:ID='Default' ss:Name='Normal'><Alignment ss:Vertical='Center'/>
      <Borders/><Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/><Interior/><NumberFormat/><Protection/></Style>");
        //定义标题样式    
        Response.Write(@"<Style ss:ID='Header'><Borders><Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
       <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
       <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
       <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders>
       <Font ss:FontName='宋体' x:CharSet='134' ss:Size='18' ss:Color='#FF0000' ss:Bold='1'/></Style>");        //定义边框
        Response.Write(@"<Style ss:ID='border'><NumberFormat ss:Format='@'/><Borders>
      <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
      <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
      <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
      <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders></Style>");        Response.Write("</Styles>");        //SheetCount代表生成的 Sheet 数目。
        for (int i = 0; i < SheetCount; i++)
        {
            //计算该 Sheet 中的数据起始行和结束行。
            int start = ItenCountPerSheet * i;
            int end = ItenCountPerSheet * (i + 1);
            if (end > ds.Tables["excel"].Rows.Count) end = ds.Tables["excel"].Rows.Count;            Response.Write("<Worksheet ss:Name='Sheet" + (i + 1) + "'>");
            Response.Write("<Table x:FullColumns='1' x:FullRows='1'>");            //输出标题
            Response.Write("\r\n<Row ss:AutoFitHeight='1'>");
            for (int j = 0; j < ds.Tables["excel"].Columns.Count; j++)
            {
                Response.Write("<Cell ss:StyleID='Header'><Data ss:Type='String'>" + ds.Tables["excel"].Columns[j].ColumnName + "</Data></Cell>");
            }
            Response.Write("\r\n</Row>");
            for (int j = start; j < end; j++)
            {
                Response.Write("<Row>");
                for (int c = 0; c < 6; c++)
                {
                    //对于数字，采用Number数字类型
                    if (c > 1)
                    {
                        Response.Write("<Cell ss:StyleID='border'><Data ss:Type='Number'>" + ds.Tables["excel"].Rows[j][c].ToString() + "</Data></Cell>");
                    }
                    else
                    {
                        Response.Write("<Cell ss:StyleID='border'><Data ss:Type='String'>" + ds.Tables["excel"].Rows[j][c].ToString() + "</Data></Cell>");
                    }
                }
                Response.Write("</Row>");
            }
            Response.Write("</Table>");
            Response.Write("</Worksheet>");
            Response.Flush();
        }
        Response.Write("</Workbook>");
        Response.End();
以上代码是参考孟子E章的代码，它的代码是用datatable的，我改了用dataset
地址：http://dotnet.aspx.cc/article/e9e795ab-8fb5-47e8-a586-2c943a8e5408/read.aspx运行没有出错，能导出一个xml文件，改为xls后打开，弹出“加载期间出现问题”的框框的内容：
加载期间在下述区域中出现了问题（P）：
表格由于错误，此文件无法打开。错误列于：http://dotnet.aspx.cc/article/e9e795ab-8fb5-47e8-a586-2c943a8e5408/read.aspx
求帮忙看看是怎么回事？？谢谢了！！！

Answer 1

由于错误，此文件无法打开。错误列于：http://dotnet.aspx.cc/article/e9e795ab-8fb5-47e8-a586-2c943a8e5408/read.aspx这句错了，由于错误，此文件无法打开。错误列于：C:\Documents and Settings\Administrator\Local Settings\Temporary Internet Files

Answer 2

你为什么要先导成xml呢 直接导入到excel不就可以了吗？

Answer 3

你的这个EXCEL文件已经悲剧了修复一下看看导出代码应该没问题。

Answer 4

从新导出还是这个样子么？换个路径。public class GridViewToExcel
    {
        HttpResponse Response = null;
        public GridViewToExcel(HttpResponse response)
        {
            Response = response;
        }        /// <summary>
        /// 将GridView里的数据导入到Excel文件中，并另存文档
        /// </summary>
        /// <param name="FileName">文件名称</param>
        /// <param name="GridViewTeacher">要导出数据的GridView</param>
        public void ExportToExcel(string FileName, GridView gridview)
        {
            Response.Charset = "GB2312";
            Response.ContentEncoding = System.Text.Encoding.UTF7;
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
            Response.ContentType = "application/ms-excel";//导出excel文件
            //this.EnableViewState = false;
            StringWriter tw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(tw);
            gridview.RenderControl(hw);
            Response.Write(tw.ToString());
            Response.End();
        }
    }

Answer 5

直接用孟子E章的代码能正常导出的，为什么我用自己的数据源的datatable会不行呢？？6楼朋友的你的代码怎么用？？

Answer 6

谁帮帮忙啊？我改用孟子E章的gridview的导出，用我的数据，要是只查找sql 2个字段，导出后能显示，3个字段就不行了！！为什么？？？

Answer 7

其实我倒是会导出EXL不过是用NPOI

Answer 8

有详细方法介绍下吗？用NPOI方便？