求一个datatable数据，导入excel的例子。
在百度找的好多不合适。
数据已经从数据库读出来了。
主要要求是：导入到excel，实现特定的行，列导入。
[最优解释]
GridView gv = new GridView();
gv.DataSource = datatable;
gv.DataBind();
using (StreamWriter sw=new StreamWriter("C:\\test.xls"))
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{
gv.RenderControl(htw);
htw.BeginRender();
}
}
[其他解释]
private DataTable ExcelToDataTable(string FilePath)
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";Extended Properties='Excel 8.0; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [ItemLocation$]", strConn);
DataSet myDataSet = new DataSet();
try
{
myCommand.Fill(myDataSet);
// 取出数据后删除文件
// 删除已存在的文件
if (File.Exists(FilePath))
{
File.Delete(FilePath);
}
}
catch
{
}
return myDataSet.Tables[0];
}
System.Web.UI.WebControls.GridView gvExport = null;
System.Web.HttpContext curContext = System.Web.HttpContext.Current; System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;
if (dtData != null)
{
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
curContext.Response.Charset = "utf-8";
curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=SASalesTotal.xls");
strWriter = new System.IO.StringWriter();
htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
gvExport = new System.Web.UI.WebControls.GridView();
gvExport.DataSource = dtData.DefaultView;
gvExport.AllowPaging = false;
gvExport.DataBind();
gvExport.RenderControl(htmlWriter);
curContext.Response.Write("<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />" + strWriter.ToString());
curContext.Response.End();
}
public void outExcel_click(Object sender, EventArgs e)
{
string name = "/excel/名称.csv";//换成你的路径
FileStream fs = new FileStream(Server.MapPath(name), FileMode.Create, FileAccess.Write);
StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.GetEncoding("gb2312"));
sw.WriteLine("列1,列2,列3");
DataTable t = new DataTable();//你的datatable
int c = 1;
for (int i = 0; i < t.Rows.Count; i++)
{
sw.WriteLine(t.Rows[i]["列1"] + "" + "," + t.Rows[i]["列2"] + "," + t.Rows[i]["列3"]);
}
sw.Close();
Response.Redirect(name);
}
