MyException - 我的异常网
当前位置:我的异常网» ASP.NET » 对于导出excel

对于导出excel

www.MyException.Cn  网友分享于:2013-12-26  浏览:42次
关于导出excel
之前找了很多这样的问题,要么是使用COM组件,要么就是导出的格式还有编码不对,要么是没法导,要么是只能到处本页流或者GRIDVIEW之类的控件,没有通用一点的方法吗?
比如导出DataSet到Excel?

------解决方案--------------------
还是用DataSet到Excel吧.
http://www.cnblogs.com/asdcer/archive/2006/07/20/455365.aspx

//读取临时文件
GYYW.DA.Common.Base_SqlDataBase daBZDM = new GYYW.DA.Common.Base_SqlDataBase();
DataSet dsBZDM = daBZDM.GetDataSetBySql( "select QCDM,MC,GG from WG_BZDM where QCDM like '02% ' ");


//同时将虚拟目录下的Data作为临时文件目录。
string urlPath = HttpContext.Current.Request.ApplicationPath + "/Data/ ";
string physicPath = HttpContext.Current.Server.MapPath(urlPath);
//string fileName = Guid.NewGuid() + ".Xls ";
string fileName = "DownLoad.Xls ";
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + physicPath + fileName + ";Extended Properties=Excel 8.0; ";

OleDbConnection objConn = new OleDbConnection(connString);
OleDbCommand objCmd = new OleDbCommand();
objCmd.Connection = objConn;
objCmd.Connection.Open();


//建立表结构
objCmd.CommandText = @ "CREATE TABLE Sheet1(器材代码 varchar,名称 varchar, 规格 varchar) ";
objCmd.ExecuteNonQuery();

//建立插入动作的Command

objCmd.CommandText = "INSERT INTO Sheet1(器材代码, 名称,规格) VALUES (@QCDM, @MC, @GG) ";

objCmd.Parameters.Clear();

objCmd.Parameters.Add(new OleDbParameter( "@QCDM ", OleDbType.VarChar));
objCmd.Parameters.Add(new OleDbParameter( "@MC ", OleDbType.VarChar));
objCmd.Parameters.Add(new OleDbParameter( "@GG ",OleDbType.VarChar));

//遍历DataSet将数据插入新建的Excel文件中
foreach (DataRow row in dsBZDM.Tables[0].Rows)
{
for (int i=0; i <objCmd.Parameters.Count; i++)
{
objCmd.Parameters[i].Value = row[i];
}
objCmd.ExecuteNonQuery();
}
objCmd.Connection.Close();

//提供下载
//清除临时文件
HttpResponse response = HttpContext.Current.Response;
response.Clear();
//为输出作准备
response.WriteFile(urlPath + fileName);
string httpHeader= "attachment;filename=KCMX.Xls ";
response.AppendHeader( "Content-Disposition ", httpHeader);
response.Flush();
//输出完毕后清除临时文件
string strSaveDir = "../Data/ ";
string strFile = Server.MapPath(strSaveDir + fileName).ToString();
//string sss = urlPath + fileName;
System.IO.File.Delete(strFile);//删除临时文件
response.End();


------解决方案--------------------
LS的是一种方法,还有一种简单的方法,被称为终极解决方案

2005可以建立一个临时的GridView或者DataGrid,2003建立一个DataGrid

//dtData是要导出为Excel的DataTable,FileName是要导出的Excel文件名(不加.xls)
public static void DataTable2Excel(System.Data.DataTable dtData, String FileName)
{
System.Web.UI.WebControls.GridView dgExport = null;
//当前对话
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
//IO用于导出并返回excel文件
System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;

if (dtData != null)
{
//设置编码和附件格式
//System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)作用是方式中文文件名乱码
curContext.Response.AddHeader( "content-disposition ", "attachment;filename= " + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls ");
curContext.Response.ContentType = "application/vnd.ms-excel ";

文章评论

软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有