描述:按照不同的大类将用户所查询的数据导出到EXCEL中,每个Sheet对应一个大类,循环遍历所有大类然后将导好的EXCEL下载到客户端并删除临时文件。问题:导出一个大类下载并删除临时文件没有问题,但是循环遍历所有大类每个大类对应一个sheel并保存在一个EXCEL中老是调试不成功,请大家帮帮忙看如何解决。
解决方案 »
- 如何实现横向排满后换行!
- URLRewriter组件能否“default.html?id=123”以这种形式传参数呢?
- 我以前喜欢看别人的代码然后自己写下,接下来我该怎么做啊
- asp.net 生成二维表格?
- 初学:.cs、.aspx、.aspx.cs这三个文件如何互相调用
- 求一SQL语句。
- windows server2003 下开启FSO问题!
- 对oracle的,有参数的插入语句,想用dataset实现,怎样做呢?
- 求助:DATA GRIDE 怎么删除列咯 过路的高手过来看看 这个急
- asp.net服务器端创建的radiobuttonlist放入updatepanel后怎么加trigger
- 用asp编写歌曲点歌系统
- gridview +图片问题.
string templetFilePath= Server.MapPath("../").ToString() + @"Template\";
object missing = Missing.Value;
Excel.Application app;
Excel.Workbook workBook;
Excel.Worksheet workSheet;
app = new Excel.ApplicationClass();
if (app == null)
{
return;
}
app.Visible=false;
workBook = app.Workbooks.Add(templetFilePath +"SuperTemplet.xls");
for(int pacount=0;pacount<paname.GetLength(0);pacount++)
{
workSheet= (Excel.Worksheet)workBook.Sheets[pacount +1];
workSheet.Name = wsname;…. //循环遍历
}/
workBook.SaveAs(templetFilePath + filename);
GC.Collect();//强制代码回收
DownLoadFile(templetFilePath,fileName); //下载并删除临时文件
————————————————————————————————————
不知道这个方法对不对总共有N个大类,可只能遍历出3个大类而且只有最后一个sheel有数据这是为什么呢》
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ fileName +";"+"Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open(); System.Data.OleDb.OleDbDataAdapter myCommand = null;
string strExcel = "select * from [sheet1$]";
myCommand = new System.Data.OleDb.OleDbDataAdapter(strExcel, strConn);
DataTable dt = new DataTable();
myCommand.Fill(dt);
return dt;
你的这个方法我可能用不上,谢谢你的意见!
{
string sql = "select convert(varchar(10),detailtime,111) as 申請時間,detaildept as 申請單位,shenqingren as 申請人,fromdaytime as 日期從,todaytime as 日期止,carno as 車號,driver as 駕駛員 from gc_carinout where todaytime>='" + TextBox1.Text + "'and todaytime<=' " + TextBox2.Text + "' and [check] = 'C'";
SqlConnection conn = sda.getsqlconnection();
SqlDataAdapter sda1 = new SqlDataAdapter(sql,conn);
DataSet ds = new DataSet();
sda1.Fill(ds,"Report");
DataTable dt = new DataTable();
dt = ds.Tables["Report"];
DSToExcel(dt);
}
private void DSToExcel(DataTable dt) {
string strFile = "";
string path = "";
strFile = strFile + "DateQueryReport_";
strFile = strFile + DateTime.Now.ToString("yyyyMMddHHmm");
strFile = strFile + ".xls";
string fileName = strFile;
path = Server.MapPath(strFile);
System.IO.FileStream fs = new FileStream(path, System.IO.FileMode.Create, System.IO.FileAccess.Write);
StreamWriter sw = new StreamWriter(fs, new System.Text.UnicodeEncoding());
sw.Write("申請時間");
sw.Write("\t");
sw.Write("申請單位");
sw.Write("\t");
sw.Write("申請人");
sw.Write("\t");
sw.Write("日期從");
sw.Write("\t");
sw.Write("日期止");
sw.Write("\t");
sw.Write("車號");
sw.Write("\t");
sw.Write("駕駛員");
sw.Write("\t");
sw.WriteLine(""); for (int i = 0; i < dt.Rows.Count;i++ )
{
sw.Write(dt.Rows[i]["申請時間"].ToString());
sw.Write("\t");
sw.Write(dt.Rows[i]["申請單位"].ToString());
sw.Write("\t");
sw.Write(dt.Rows[i]["申請人"].ToString());
sw.Write("\t");
sw.Write(dt.Rows[i]["日期從"].ToString());
sw.Write("\t");
sw.Write(dt.Rows[i]["日期止"].ToString());
sw.Write("\t");
sw.Write(dt.Rows[i]["車號"].ToString());
sw.Write("\t");
sw.Write(dt.Rows[i]["駕駛員"].ToString());
sw.Write("\t");
sw.WriteLine("");
}
sw.Flush();
sw.Close();
try
{
if(path!="")
{
DownFile(path,fileName);
}
}
catch(Exception e)
{
//Response.Write(e);
throw(e);
//Response.Write("<script language='javascript'>alert('error')</script>");
Response.Write("failed");
}
} private void DownFile(string filePath, string fileName)
{
FileInfo fileInfo = new FileInfo(filePath);
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
Response.AddHeader("Content-Length", fileInfo.Length.ToString());
Response.AddHeader("Content-Transfer-Encoding", "binary");
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("Big5");
Response.WriteFile(fileInfo.FullName);
Response.Flush();
fileInfo.Delete();
Response.End();
}
測試通過了
你的这个方法用到我这个上面不知道行不行因为要导出的数据结构不一样————————————————————————————————————————如何按大类来增加sheel,之前的sheel是怎么保存的请大家来出出点子
强烈建议使用 xml + xslt 导出。
--------------------------------------------------------------------------------
析软网
http://www.parsesoft.net
http://bbs.parsesoft.net
致力于 DocBook 的应用,专注于开源软件的解析、手册/指南、以及应用文章、书籍的发布。
string templetePath = System.Configuration.ConfigurationSettings.AppSettings["EX_Excel_Templete_Path"].ToString();
string templete_All_Name = "asc_data_matnr.xls";
try
{
string fileName = @""+templetePath+templete_All_Name;
Excel.Application myExcel = new Excel.ApplicationClass();
Excel.Workbook myBook = myExcel.Workbooks.Add(fileName);
myExcel.Visible = false;
myExcel.DisplayAlerts = false;
//关键这句,你要设置是第几页
Excel.Worksheet IV = (Worksheet)myBook.Sheets[1]; IV.Cells[1,2] = RBox_NO; if(type == "1")
{
//
IV.Cells[2,1] = "BILL NO.";
Excel.Worksheet IV = (Worksheet)myBook.Sheets[1];=============================================================
这个页数是从数据库中有几个产品大类就有几页
具体怎么做呢?
{
if(Request.QueryString["bExcel"] == "1")
{
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = ""; //关闭 ViewState
EnableViewState = false;
System.IO.StringWriter tw = new System.IO.StringWriter();//将信息写入字符串
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);//在WEB窗体页上写出一系列连续的HTML特定字符和文本。
//此类提供ASP.NET服务器控件在将HTML内容呈现给客户端时所使用的格式化功能
//获取control的HTML
DataGrid1.RenderControl(hw);//将DATAGRID中的内容输出到HtmlTextWriter对象中
// 把HTML写回浏览器
Response.Write(tw.ToString());
Response.End(); }
private void LinkButton1_Click(object sender, System.EventArgs e)
{
Response.Redirect("../stock/print_this_list.aspx?bExcel=1"); //本页地址
}
{
if(ds.Tables.Count == 0|| strExcelFileName=="") return;
doExport(ds,strExcelFileName);
} //执行导出
public void doExport(DataSet ds , string strExcelFileName)
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();//建立Excel对象
int rowIndex = 1;
int colIndex=0;
excel.Application.Workbooks.Add(true);//Excel表为添加状态
System.Data.DataTable table = ds.Tables[0];//建立DataTable
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();
}
}
excel.Visible = false;
//excel.Visible = true;
excel.DisplayAlerts = false;
excel.ActiveWorkbook.SaveAs(strExcelFileName,Excel.XlFileFormat.xlExcel7,Type.Missing,Type.Missing, Type.Missing, Type.Missing,Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excel.Quit();
excel=null;
GC.Collect();//垃圾回收
}
object missing = Missing.Value;Excel.Application app;Excel.Workbook workBook;Excel.Worksheet workSheet;
app = new Excel.ApplicationClass();
if (app == null)
{
return;
}
app.Visible=false;
workBook = app.Workbooks.Add(templetFilePath +"SuperTemplet.xls");
for(int pacount=0;pacount<paname.GetLength(0);pacount++)
{
workSheet= (Excel.Worksheet)workBook.Sheets[pacount +1];
workSheet.Name = wsname;//-------此处应为-------------
workSheet.cell[rowcount,colcount]=yourvalue.tostring()
__________________________________________________________________
…. //循环遍历
}/
workBook.SaveAs(templetFilePath + filename);GC.Collect();//强制代码回收DownLoadFile(templetFilePath,fileName); //下载并删除临时文件
慢慢研究一下!
利用ASP.NET输出指定内容的WORD、EXCEL、TXT、HTM等类型的文档很容易的。主要分为三步来完成。
一、定义文档类型、字符编码
Response.Clear();
Response.Buffer= true;
Response.Charset="utf-8";
//下面这行很重要, attachment 参数表示作为附件下载,您可以改成 online在线打开
//filename=FileFlow.xls 指定输出文件的名称,注意其扩展名和指定文件类型相符,可以为:.doc .xls .txt .htm
Response.AppendHeader("Content-Disposition","attachment;filename=FileFlow.xls");
Response.ContentEncoding=System.Text.Encoding.GetEncoding("utf-8");
//Response.ContentType指定文件类型 可以为application/ms-excel application/ms-word application/ms-txt application/ms-html 或其他浏览器可直接支持文档
Response.ContentType = "application/ms-excel";
this.EnableViewState = false;