C#导入excel的问题

[rainyubin]

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

[terryshi]

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)
{ }

[rainyubin]

能解释一下吗？ 我没太看明白

[超级帅虫虫No1]

ddd

[rainyubin]

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

[liubin911]

我这种方法是通过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();

[rainyubin]

楼上的 输出的路径在哪里啊？怎么设置输出的路径？

[wlw175733747]

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();


}
}

[wlw175733747]

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();


}
}
需要添加 类库

[trainli]

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

[rainyubin]

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

[李迎春82]

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

[飞不动]

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

[rainyubin]

thank you! everyone!

[zhgroup]

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