急,急,急,怎么设置excel格式????

Question

public virtual void ExportToExcel(DataTable dt) 
    { 
        System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid(); 
        dg.DataSource = dt.DefaultView; 
        dg.DataBind();         Response.ContentType = "application/vnd.ms-excel"; 
        //Response.HeaderEncoding = System.Text.Encoding.GetEncoding("UTF7"); 
        //Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); 
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF7; 
    
        //'从Content-Type header中除去charset设置 
        Response.Charset = "";         //'关闭ViewState 
        this.EnableViewState=false;         System.IO.StringWriter tw=new System.IO.StringWriter(); 
        System.Web.UI.HtmlTextWriter hw=new System.Web.UI.HtmlTextWriter(tw);         dg.RenderControl(hw);         Response.Write(tw.ToString()); 
        Response.End(); 
    }
这是从datatable导入到excel中,但导入到excel中的标题仍然是数据库里的中文,是不是要设置一下导入excel格式啊,小弟是新手,望高手指教啊??

Answer 1

Response.AppendHeader("Content-Disposition", "attachment;filename=" + "你要的标题");

Answer 2

谢谢楼上的,我的意思是获取的数据库里的字段对应的标题都是中文,比如获取的一列全是姓名,它对应的是name,我想是姓名,还有获取的数据过长的话,会以科学记数法显示,是不是要设置excel的格式啊,望指教???

Answer 3

导出的excel再导入，会出现错误：外部表不是预期的格式！怎么解决？

Answer 4

1.实现导出Excel,可以自定义Excel样式        protected void Export_XLS_Click(object sender, EventArgs e)
        {
            ExportXLS ExportExcel = new ExportXLS();
            ExportExcel._StartCount = 0;
            ExportExcel._GridView = GridView1;
            ExportExcel._HeaderText = "CustomerList";
            ExportExcel._FileName = "CustomerNoList";
            ArrayList aList = new ArrayList();
            aList.Add(HtmlTableCellx.AddItem("No.", "center", "", "20%", "", ""));
            aList.Add(HtmlTableCellx.AddItem("用户帐号", "center", "", "40%", "", ""));
            aList.Add(HtmlTableCellx.AddItem("用户密码", "center", "", "40%", "", ""));
            Response.Write(ExportExcel.ExportToExcel(this, aList));
            Response.End();
        }       2.导出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.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using System.Text;
using System.IO;namespace GilClientMgr.Service
{
    public class ExportXLS : System.Web.UI.Page
    {//导出EXCEL类
        public ExportXLS()
        {        }
        private GridView _gridview = null;//GridView数据源
        private DataTable _dt = null;//DataTable数据源
        private int _startcount = 0;
        private String _headertext = "";
        private String _filename = "FileFlow";
        /// <summary>
        /// GridView数据源
        /// </summary>
        public GridView _GridView
        {
            get
            {
                return _gridview;
            }
            set
            {
                _gridview = value;
            }
        }
        public DataTable _DT
        {
            get
            {
                return _dt;
            }
            set
            {
                _dt = value;
            }
        }
        /// <summary>
        /// 数据循环开始点 默认 0
        /// </summary>
        public int _StartCount
        {
            get
            {
                return _startcount;
            }
            set
            {
                _startcount = value;
            }
        }
        /// <summary>
        /// 标题
        /// </summary>
        public String _HeaderText
        {
            get
            {
                return _headertext;
            }
            set
            {
                _headertext = value;
            }
        }
        /// <summary>
        /// 导出的Excel文件名
        /// </summary>
        public String _FileName
        {
            get
            {
                return _filename;
            }
            set
            {
                _filename = value;
            }
        }
        /// <summary>
        /// 导出到Excel
        /// </summary>
        public StringWriter ExportToExcel(System.Web.UI.Page aPage, ArrayList HeaderList)
        {
            DateTime dd = System.DateTime.Now;
            string Export_Date = "导出时间：" + dd.ToLongDateString() + dd.ToLongTimeString();
            ArrayList aListStyle = new ArrayList(); //样式列
            aPage.Response.Buffer = true;
            aPage.Response.Charset = "utf-8";
            //aPage.Response.AppendHeader("Content-Disposition", "attachment;filename=FileFlow.xls");
            aPage.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(_filename + ".xls")));
            aPage.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            aPage.Response.ContentType = "application/ms-excel";
            try
            {
                StringWriter oStringWriter = new StringWriter();
                #region 样式
                oStringWriter.WriteLine("<html xmlns='http://www.w3.org/1999/xhtml'  xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'><HEAD>");
                oStringWriter.WriteLine("<META HTTP-EQUIV='content-type' CONTENT='text/html; charset=GB2312'> ");
                oStringWriter.WriteLine("<meta name=ProgId content=Excel.Sheet>");
                oStringWriter.WriteLine("<meta name=Generator content='Microsoft Excel 10'>");
                oStringWriter.WriteLine("<!--[if gte mso 9]><xml>");
                oStringWriter.WriteLine("<o:DocumentProperties>");
                oStringWriter.WriteLine("<o:LastAuthor>chenrui</o:LastAuthor>");
                oStringWriter.WriteLine("<o:Created>1996-12-17T01:32:42Z</o:Created>");
                oStringWriter.WriteLine("<o:LastSaved>2006-06-28T15:21:22Z</o:LastSaved>");
                oStringWriter.WriteLine("<o:Version>9.2812</o:Version>");
                oStringWriter.WriteLine("</o:DocumentProperties>");
                oStringWriter.WriteLine("<o:OfficeDocumentSettings>");
                oStringWriter.WriteLine("<o:DownloadComponents/>");
                oStringWriter.WriteLine("<o:LocationOfComponents HRef='file:G:\\msowc.cab'/>");
                oStringWriter.WriteLine("</o:OfficeDocumentSettings>");
                oStringWriter.WriteLine("</xml><![endif]-->");<代码未完>

Answer 5

<接上面>                oStringWriter.WriteLine("<!--[if gte mso 9]><xml>");
                oStringWriter.WriteLine("<x:ExcelWorkbook>");
                oStringWriter.WriteLine("<x:ExcelWorksheets>");
                oStringWriter.WriteLine("<x:ExcelWorksheet>");
                oStringWriter.WriteLine("<x:Name>Sheet1</x:Name>");
                oStringWriter.WriteLine("<x:WorksheetOptions>");
                oStringWriter.WriteLine("<x:DefaultRowHeight>285</x:DefaultRowHeight>");
                oStringWriter.WriteLine("<x:Selected/>");
                oStringWriter.WriteLine("<x:Panes>");
                oStringWriter.WriteLine("<x:Pane>");
                oStringWriter.WriteLine("<x:Number>3</x:Number>");
                oStringWriter.WriteLine("<x:ActiveRow>8</x:ActiveRow>");
                oStringWriter.WriteLine("</x:Pane>");
                oStringWriter.WriteLine("</x:Panes>");
                oStringWriter.WriteLine("<x:ProtectContents>False</x:ProtectContents>");
                oStringWriter.WriteLine("<x:ProtectObjects>False</x:ProtectObjects>");
                oStringWriter.WriteLine("<x:ProtectScenarios>False</x:ProtectScenarios>");
                oStringWriter.WriteLine("</x:WorksheetOptions>");
                oStringWriter.WriteLine("</x:ExcelWorksheet>");
                oStringWriter.WriteLine("<x:ExcelWorksheet>");
                oStringWriter.WriteLine("<x:Name>Sheet2</x:Name>");
                oStringWriter.WriteLine("<x:WorksheetOptions>");
                oStringWriter.WriteLine("<x:DefaultRowHeight>285</x:DefaultRowHeight>");
                oStringWriter.WriteLine("<x:ProtectContents>False</x:ProtectContents>");
                oStringWriter.WriteLine("<x:ProtectObjects>False</x:ProtectObjects>");
                oStringWriter.WriteLine("<x:ProtectScenarios>False</x:ProtectScenarios>");
                oStringWriter.WriteLine("</x:WorksheetOptions>");
                oStringWriter.WriteLine("</x:ExcelWorksheet>");
                oStringWriter.WriteLine("<x:ExcelWorksheet>");
                oStringWriter.WriteLine("<x:Name>Sheet3</x:Name>");
                oStringWriter.WriteLine("<x:WorksheetOptions>");
                oStringWriter.WriteLine("<x:DefaultRowHeight>285</x:DefaultRowHeight>");
                oStringWriter.WriteLine("<x:ProtectContents>False</x:ProtectContents>");
                oStringWriter.WriteLine("<x:ProtectObjects>False</x:ProtectObjects>");
                oStringWriter.WriteLine("<x:ProtectScenarios>False</x:ProtectScenarios>");
                oStringWriter.WriteLine("</x:WorksheetOptions>");
                oStringWriter.WriteLine("</x:ExcelWorksheet>");
                oStringWriter.WriteLine("</x:ExcelWorksheets>");
                oStringWriter.WriteLine("<x:WindowHeight>4530</x:WindowHeight>");
                oStringWriter.WriteLine("<x:WindowWidth>8505</x:WindowWidth>");
                oStringWriter.WriteLine("<x:WindowTopX>480</x:WindowTopX>");
                oStringWriter.WriteLine("<x:WindowTopY>120</x:WindowTopY>");
                oStringWriter.WriteLine("<x:AcceptLabelsInFormulas/>");
                oStringWriter.WriteLine("<x:ProtectStructure>False</x:ProtectStructure>");
                oStringWriter.WriteLine("<x:ProtectWindows>False</x:ProtectWindows>");
                oStringWriter.WriteLine("</x:ExcelWorkbook>");
                oStringWriter.WriteLine("</xml><![endif]-->");
                oStringWriter.WriteLine("<style>");
                oStringWriter.WriteLine(".xlsText{mso-style-parent:style0;mso-number-format:'\\@';border:.5pt solid windowtext;text-align:center;font-size:20.0pt;font-weight:400;}");
                oStringWriter.WriteLine(".xls26{mso-style-parent:style0; font-size:9.0pt;font-family:新宋体, monospace;mso-font-charset:134;text-align:center;}");
                oStringWriter.WriteLine(".xls%{mso-style-parent:style0;font-size:9.0pt;font-family:新宋体, monospace;mso-font-charset:134;mso-number-format:'0\\.0%';text-align:center;}");
                oStringWriter.WriteLine("</style>");
                oStringWriter.WriteLine("</HEAD>");
                oStringWriter.Write("<table cellSpacing='0' cellPadding='0' width ='90%' border='1'");
                oStringWriter.Write(" bordercolor='black'");
                oStringWriter.WriteLine(">");
                #endregion
             

Answer 6

   //标题
                oStringWriter.WriteLine("<tr><td class=xlsText align=center height=40 colspan='" + Convert.ToString(HeaderList.Count) + "'>");                oStringWriter.WriteLine(_headertext);
                oStringWriter.WriteLine("</td></tr>");                //导出日期
                oStringWriter.WriteLine("<tr><td class=xls26 align=left height=30 colspan='" + Convert.ToString(HeaderList.Count) + "'>");                oStringWriter.WriteLine(Export_Date);
                oStringWriter.WriteLine("</td></tr>");                //动态生成表头
                oStringWriter.WriteLine("<tr valign='middle' style='FONT-WEIGHT: bold'>");
                for (int i = 0; i < HeaderList.Count; i++)
                {
                    HtmlTableCellx t = (HtmlTableCellx)HeaderList[i];
                    oStringWriter.WriteLine("<td ");
                    if (t.align != "")
                    {
                        oStringWriter.WriteLine(" align='" + t.align + "'");
                    }
                    if (t.height != "")
                    {
                        oStringWriter.WriteLine(" height='" + t.height + "'");
                    }
                    if (t.valign != "")
                    {
                        oStringWriter.WriteLine(" valign='" + t.valign + "'");
                    }
                    if (t.width != "")
                    {
                        oStringWriter.WriteLine(" width='" + t.width + "'");
                    }
                    oStringWriter.WriteLine(" >");
                    oStringWriter.WriteLine("" + t.Text + "");
                    oStringWriter.WriteLine("</td>");                    aListStyle.Add(t.Style);//将列的样式添加到新的列表                }
                oStringWriter.WriteLine("</tr>");
                if (_gridview != null)
                {
                    for (int i = 0; i < _gridview.Rows.Count; i++)
                    {
                        oStringWriter.Write("<tr valign='middle' align='left'>");
                        for (int j = _startcount; j < HeaderList.Count + _startcount; j++)
                        {
                            oStringWriter.WriteLine("<td align='center' ");
                            if (aListStyle[j - _startcount].ToString() != "")
                            {
                                oStringWriter.WriteLine(" class='" + aListStyle[j - _startcount].ToString() + "'");
                            }
                            oStringWriter.WriteLine(">");
                            oStringWriter.WriteLine(_gridview.Rows[i].Cells[j].Text);
                            oStringWriter.WriteLine("</td>");                        } oStringWriter.WriteLine("</tr>");
                    }
                }
                else if (_dt != null)
                {
                    for (int i = 0; i < _dt.Rows.Count; i++)
                    {
                        oStringWriter.Write("<tr valign='middle' align='left'>");
                        for (int j = 0; j < HeaderList.Count; j++)
                        {
                            oStringWriter.WriteLine("<td align='center' ");
                            if (aListStyle[j].ToString() != "")
                            {
                                oStringWriter.WriteLine(" class='" + aListStyle[j].ToString() + "'");
                            }
                            oStringWriter.WriteLine(">");
                            oStringWriter.WriteLine(_dt.Rows[i][j].ToString());
                            oStringWriter.WriteLine("</td>");                        } oStringWriter.WriteLine("</tr>");
                    }
                }
                oStringWriter.WriteLine("</TABLE></HTML>");
                oStringWriter.Close();
                return oStringWriter;
            }
            catch (Exception)
            {                throw;
            }
        }
    }
    public struct HtmlTableCellx
    {
        public String Text;
        public String align;
        public String height;
        public String width;
        public String valign;
        public String Style;
        public static HtmlTableCellx AddItem(String aText, String aAlgin, String aHegight, String aWidth, String aValign, String aStyle)
        {
            HtmlTableCellx t = new HtmlTableCellx();
            t.Text = aText;
            t.align = aAlgin;
            t.height = aHegight;
            t.width = aWidth;
            t.valign = aValign;
            if (aStyle == "")
            {
                aStyle = "xls26";
            }
            t.Style = aStyle;
            return t;
        }    }
    
}

Answer 7

 <asp:DataGrid ID="DG" runat="server"><Columns><asp:BoundColumn DataField="姓名" HeaderText="name"></asp:BoundColumn></Columns></asp:DataGrid> 
然后在：
protected void DG_ItemDataBound(object sender, DataGridItemEventArgs e)
    {
if (e.Row.RowType == DataControlRowType.DataRow)
        {
 e.Row.Cells[2].Attributes.Add("style", "vnd.ms-excel.numberformat:@"); //将身份证号当字符串处理
        }
    }
如果属性不对，就找对应的DataGrid 对应属性。我用的gridview

Answer 8

谢谢楼上各位,我的意思是获取的数据库里的字段对应的标题都是中文,比如获取的一列全是姓名,它对应的是name,我想是姓名,还有获取的数据过长的话,会以科学记数法显示,是不是要设置excel的格式啊,有没有简单点的方法 啊??

Answer 9

导入到excel中的数据由于长度过长,用科学记数法显示了,怎么解决啊??