C#导入excel的问题

Question

WebForm中，要把sqlServer的信息导入到excel中，应该怎样实现？

Answer 1

protected void btnExport_Click(object sender, EventArgs e)
    {
        Response.Clear();
        Response.Buffer = true;
        //Response.Charset = "GB2312";
        Response.AppendHeader("Content-Disposition", "attachment;filename=Report.xls");
        //Response.ContentEncoding = System.Text.Encoding.UTF8;
        Response.ContentType = "application/ms-excel";
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
        this.divResult.RenderControl(oHtmlTextWriter);
        Response.Output.Write(oStringWriter.ToString());
        Response.Flush();
        Response.End();
    }    public override void VerifyRenderingInServerForm(Control control)
    { }

Answer 2

public override void VerifyRenderingInServerForm(Control control)
    { }
这一段代码是执行什么功能的？

Answer 3

我这种方法是通过ADO.net读取出DataSet 后输出的SqlConnection conn=DB.createCon();; 
SqlDataAdapter da=new SqlDataAdapter("select * from product",conn); 
DataSet ds=new DataSet(); 
da.Fill(ds,"table1"); 
DataTable dt=ds.Tables["table1"]; 
StringWriter sw=new StringWriter(); 
//EXCEL显示的列头名
sw.WriteLine("自动编号\t姓名\tSid");  
//读取数据
foreach(DataRow dr in dt.Rows) 
{ 
    sw.WriteLine(dr["pID"]+"\t"+dr["pName"]+"\t"+dr["pSexID"]); 
} 
 
sw.Close(); 
Response.AddHeader("Content-Disposition", "attachment; filename=test.xls"); 
Response.ContentType = "application/ms-excel"; 
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312"); 
//输出
Response.Write(sw); 
Response.End();

Answer 4

public void Createxcal(DataSet ds)//服务器端启动
    {
        
        
            Excel.Application excel = new Excel.Application();
            int rowIndex = 1;
            int colIndex = 0;
            excel.Application.Workbooks.Add(true);
            excel.Visible = true;
            DataTable table = ds.Tables[0];
            foreach (DataColumn col in table.Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
            }
            foreach (DataRow row in table.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in table.Columns)
                {
                    colIndex++;
                    excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                }
            }
        
        
    }
    public  void CreateBoweser(DataSet ds)//客户端启动
    {
        HttpContext curContext = HttpContext.Current;
        StringWriter strwriter = null;
        HtmlTextWriter htmlwriter = null;
        DataGrid dg = new DataGrid();
        
        if (ds != null)
        {
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
            curContext.Response.Charset = "";
            strwriter = new StringWriter();
            htmlwriter = new HtmlTextWriter(strwriter);            dg.DataSource = ds;
            dg.AllowPaging = false;
            dg.DataBind();
            dg.RenderControl(htmlwriter);
            curContext.Response.Write(strwriter.ToString());
            curContext.Response.End();
           
            
        }
    }

Answer 5

public void Createxcal(DataSet ds)//winform
    {
        
        
            Excel.Application excel = new Excel.Application();
            int rowIndex = 1;
            int colIndex = 0;
            excel.Application.Workbooks.Add(true);
            excel.Visible = true;
            DataTable table = ds.Tables[0];
            foreach (DataColumn col in table.Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
            }
            foreach (DataRow row in table.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in table.Columns)
                {
                    colIndex++;
                    excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                }
            }
        
        
    }
    public  void CreateBoweser(DataSet ds)//web
    {
        HttpContext curContext = HttpContext.Current;
        StringWriter strwriter = null;
        HtmlTextWriter htmlwriter = null;
        DataGrid dg = new DataGrid();
        
        if (ds != null)
        {
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
            curContext.Response.Charset = "";
            strwriter = new StringWriter();
            htmlwriter = new HtmlTextWriter(strwriter);            dg.DataSource = ds;
            dg.AllowPaging = false;
            dg.DataBind();
            dg.RenderControl(htmlwriter);
            curContext.Response.Write(strwriter.ToString());
            curContext.Response.End();
           
            
        }
    }
需要添加 类库

Answer 6

添加com+
using Microsoft.Office.Interop.Excel;

Answer 7

wlw175733747() 的web方法可以
可是我要怎么控制导出到的excel的格式呢？让导出的东西按照我要的格式显示

Answer 8

SQL导出到EXCEL http://www.onlinedown.net/soft/44040.htm

Answer 9

方法一，用ADO.NET连接EXCEL文件写入，缺点是速度很慢，并且要在服务器生成一个临时文件。
优点是格式比较好，容易控制。
方法二，用response输出datatable，直接输出到客户端，比较快。但有时格式会乱。

Answer 10

可以直接输出字符串，列之间用\t分隔，换行用\r\n
如
姓名\t性别\r\n
A\t\男\r\n
显示为:
姓名|性别
A|男