导出规则的EXCEL文件并下载,该如何解决

2012-04-25

导出规则的EXCEL文件并下载我用EXCEL组件导出了标准的EXCEL文件请问怎么在导出完这个文件的同事弹出下载

导出规则的EXCEL文件并下载
我用EXCEL组件导出了标准的EXCEL文件请问怎么在导出完这个文件的同事弹出下载框让用户下载到本地？

C# code

//// <summary>        /// 导出 Excel 文件        /// </summary>        /// <param name="ds">要导出的DataSet</param>        /// <param name="strExcelFileName">要导出的文件名</param>        public static void ExportExcel(DataSet ds, string strExcelFileName)        {            object objOpt = Missing.Value;            Application excel = new Application();            excel.Visible = true;            _Workbook wkb = excel.Workbooks.Add(objOpt);            _Worksheet wks = (_Worksheet)wkb.ActiveSheet;            wks.Visible = XlSheetVisibility.xlSheetVisible;            int rowIndex = 1;            int colIndex = 0;            System.Data.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();                }            }            //excel.Sheets[0] = "sss";            wkb.SaveAs(strExcelFileName, objOpt, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);            wkb.Close(false, objOpt, objOpt);            excel.Quit();        }        #endregion

一般的导出后都带有格式在用户修改完后无法进行导入操作所以我希望导出的文件是规则的EXCEL文件不带格式

[解决办法]

C# code

public void OutPutExcel()        {            //定义文档类型、字符编码             Response.Clear();　　         Response.Buffer= true;　　         Response.Charset="GB2312";            //下面这行很重要， attachment 参数表示作为附件下载，您可以改成 online在线打开　　        //filename=FileFlow.xls 指定输出文件的名称，注意其扩展名和指定文件类型相符，可以为：.doc 　　 .xls 　　 .txt 　　.htm　　         Response.AppendHeader("Content-Disposition","attachment;filename=FileFlow.xls");　　         Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");　　        //Response.ContentType指定文件类型 可以为application/ms-excel、application/ms-word、application/ms-txt、application/ms-html 或其他浏览器可直接支持文档　　         Response.ContentType = "application/ms-excel";　　        this.EnableViewState = false;            //　定义一个输入流　　         System.IO.StringWriter oStringWriter = new System.IO.StringWriter();　　         System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);            this.RenderControl(oHtmlTextWriter);　　        //this 表示输出本页，你也可以绑定datagrid,或其他支持obj.RenderControl()属性的控件　　         Response.Write(oStringWriter.ToString());　　         Response.End();         }
[解决办法]
C# codeStringBuilder sb = new StringBuilder();        foreach (DataRow dr in ds.Tables[0].Rows)        {            Content = dr["Site"].ToString() + "," + dr["Title"].ToString() + "," + dr["Company"].ToString() + "," + dr["Location"].ToString() + "," + dr["PublishTime"].ToString() + "," + dr["Trade"].ToString();            sb.AppendLine(Content);        }        Response.Clear();        Response.Buffer = false;        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");        Response.ContentType = "application/octet-stream";        Response.AppendHeader("content-disposition", "attachment;filename=" + FileName);        Response.Write(sb.ToString());        Response.Flush();        Response.End(); 
 
[解决办法]
标准的 高效的 导出excel方法： 用存储过程导出 具体如下：
C# codeusing System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using Maticsoft.DBUtility;using System.Data.SqlClient;public partial class admin_BugSystem : System.Web.UI.Page{    public static string connectionString1 = PubConstant.ConnectionString1;    protected void Page_Load(object sender, EventArgs e)    {        if (!IsPostBack)        {        }    }    //导出Excel    protected void Button3_Click(object sender, EventArgs e)    {        #region 方法一        string Types = "";        if (rall.Checked == true)        {            Types = "1=1";        }        else        {            Types = "1=1";        }        DateTime Time11 = Convert.ToDateTime(Time1.Text.Trim());        DateTime Time22 = Convert.ToDateTime(Time2.Text.Trim());        if (DateTime.Compare(Time22, Time11) > 0)        {            Types = Types + "and BUGStartDateTime BETWEEN '" + Time11 + "' and '" + Time22 + "'";            DataSet ds = new DataSet();            //查询语句,如果查询语句中使用了order by ,请加上top 100 percent            string SqlStr = "select UserName,GameRole,ServiceName,BUGQuestion,BUGDescription,BUGAnswer from bugsystem where " + Types;            SqlParameter[] paramers =                {                    new SqlParameter("@sqlses",SqlDbType.NVarChar,1000)                };            paramers[0].Value = SqlStr;            ds = DbHelperSQL.RunProcedure("UP_Outloadbugtoexcel", paramers, "Dss");            if (ds.Tables[0].Rows.Count == 0)            {                Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('你选择的时间段没记录！请重新选择时间！');</script>");            }            else if (ds.Tables[0].Rows.Count <= 65530 && ds.Tables[0].Rows.Count >= 0)            {                //文件存放目录 盘的目录                string Path = ConfigurationManager.AppSettings["BUGOPLOAD"].ToString();                //生成当天的excel文件名                string ExcenlNamae = DateTime.Now.ToString("yyyyMMddhhmmss");                //文件名称                string Fname = ExcenlNamae;                string SheetName = "BUG";                string FileName = Path + ExcenlNamae;                try                {                    OutputExcel(SqlStr, Path, Fname, SheetName);                }                catch (Exception ex)                {                    throw ex;                }                Response.Redirect("../Bugexcel/" + ExcenlNamae + ".xls");                Response.End();            }            else            {                Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('要导出来BUG的条数大于Excel的最大容量，请选适中的时间段再试试！');</script>");            }            ds.Dispose();            GC.Collect();        }        else        {            Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('结束时间必须大于开始时间');</script>");        }        #endregion    }    /// <summary>    /// 执行存储过程    /// </summary>    /// <param name="storedProcName">存储过程名</param>    /// <param name="parameters">存储过程参数</param>    /// <param name="tableName">DataSet结果中的表名</param>    /// <returns>DataSet</returns>    public void RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)    {        string connectionString = connectionString1;        using (SqlConnection connection = new SqlConnection(connectionString))        {            DataSet dataSet = new DataSet();            connection.Open();            SqlDataAdapter sqlDA = new SqlDataAdapter();            sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);            sqlDA.Fill(dataSet, tableName);            connection.Close();        }        GC.Collect();    }    /// <summary>    /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)    /// </summary>    /// <param name="connection">数据库连接</param>    /// <param name="storedProcName">存储过程名</param>    /// <param name="parameters">存储过程参数</param>    /// <returns>SqlCommand</returns>    private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)    {        SqlCommand command = new SqlCommand(storedProcName, connection);        command.CommandType = CommandType.StoredProcedure;        foreach (SqlParameter parameter in parameters)        {            if (parameter != null)            {                // 检查未分配值的输出参数,将其分配以DBNull.Value.                if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&                    (parameter.Value == null))                {                    parameter.Value = DBNull.Value;                }                command.Parameters.Add(parameter);            }        }        return command;    }    //导出excel方法存储过程方法    public void OutputExcel(string SqlStr, string Path, string Fname, string SheetName)    {        SqlParameter[] paramers =            {                 new SqlParameter("@SqlStr",SqlDbType.VarChar,8000),                 new SqlParameter("@Path",SqlDbType.NVarChar,1000),                 new SqlParameter("@Fname",SqlDbType.NVarChar,250),                 new SqlParameter("@SheetName",SqlDbType.VarChar,250)            };        paramers[0].Value = SqlStr;        paramers[1].Value = Path;        paramers[2].Value = Fname;        paramers[3].Value = SheetName;        RunProcedure("p_exporttb", paramers, "BUG");    }} 
 
[解决办法]
你导出来了,其实就是文件下载的问题
C# codeprivate void Download(string FileName)     {         System.IO.FileStream r = new System.IO.FileStream(FileName, System.IO.FileMode.Open);         //设置基本信息          System.Web.HttpContext.Current.Response.Buffer = false;         System.Web.HttpContext.Current.Response.AddHeader("Connection", "Keep-Alive");         System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream";         System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + System.IO.Path.GetFileName(FileName));         System.Web.HttpContext.Current.Response.AddHeader("Content-Length", r.Length.ToString());         while (true)         {             //开辟缓冲区空间              byte[] buffer = new byte[1024];             //读取文件的数据              int leng = r.Read(buffer, 0, 1024);             if (leng == 0)//到文件尾，结束                  break;             if (leng == 1024)//读出的文件数据长度等于缓冲区长度，直接将缓冲区数据写入                  System.Web.HttpContext.Current.Response.BinaryWrite(buffer);             else             {                 //读出文件数据比缓冲区小，重新定义缓冲区大小，只用于读取文件的最后一个数据块                  byte[] b = new byte[leng];                 for (int i = 0; i < leng; i++)                     b[i] = buffer[i];                 System.Web.HttpContext.Current.Response.BinaryWrite(b);             }         }         r.Close();//关闭下载文件          System.IO.File.Delete(FileName);         System.Web.HttpContext.Current.Response.End();//结束文件下载      }
[解决办法]
C# code /// <summary>    /// 导出Excel    /// </summary>    /// <param name="dgExport">要导出的GridView</param>    /// <param name="dtData">导出的数据源</param>    public static void Export(GridView dgExport, DataTable dtData)    {        System.Web.HttpContext curContext = System.Web.HttpContext.Current;        // IO用于导出并返回excel文件         System.IO.StringWriter strWriter = null;        System.Web.UI.HtmlTextWriter htmlWriter = null;        if (dtData != null)        {            // 设置编码和附件格式             curContext.Response.Clear();            curContext.Response.ClearContent();            curContext.Response.Buffer = true;            curContext.Response.ContentType = "application/vnd.ms-excel";            curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");//utf-7可以解决一定问题            curContext.Response.Charset = "GB2312";            curContext.Response.AppendHeader("content-disposition", "filename=\"" + System.Web.HttpUtility.UrlEncode(dtData.TableName, System.Text.Encoding.UTF8) + ".xls\"");            // 导出excel文件             strWriter = new System.IO.StringWriter();            htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);            HtmlForm frm = new HtmlForm();            frm.Attributes["runat"] = "server";            frm.Controls.Add(dgExport);            dgExport.DataSource = dtData.DefaultView;            dgExport.DataBind();            // 返回客户端             dgExport.RenderControl(htmlWriter);            curContext.Response.Write(strWriter.ToString());            curContext.Response.End();        }    }
[解决办法]
很想帮下楼主，可惜我们用的是不同的开发语言
如果楼主要java实现导出Excel，并提供客户端的下载可以找本人索要
联系方式QQ:154166621,请加的时候注明来自csdn,需要代码
[解决办法]
生成excel分服务器和客户端编程，不知道你想哪种
[解决办法]
http://www.cnblogs.com/wangdetian168/archive/2008/06/20/1226973.html 
 
把文件下载下来就行了
  Missing.Value;
是什么呢
[解决办法]
C# codeprotected void ButSave_Click(object sender, EventArgs e)     {         sendTableName = "Phone, Illness, See_Illness,Leechdom,Notes";         sendStrSQL = this.Label8.Text;         dataBase();         DataView dv = new DataView(ds.Tables[0]);         OutputExcel(dv,"我的报表");     }     public void OutputExcel(DataView dv, string str)     {         //         // TODO: 在此处添加构造函数逻辑         //         //dv为要输出到Excel的数据，str为标题名称         GC.Collect();         //Application excel;// = new Application();         int rowIndex = 2;         int colIndex = 0;         int SUM = 0;         int number = 0;         _Workbook xBk;         _Worksheet xSt;         Excel.ApplicationClass excel = new Excel.ApplicationClass();         xBk = excel.Workbooks.Add(true);         xSt = (_Worksheet)xBk.ActiveSheet;         //         //取得标题         //         foreach (DataColumn col in dv.Table.Columns)         {             colIndex++;             excel.Cells[2, colIndex] = col.ColumnName;             xSt.get_Range(excel.Cells[2, colIndex], excel.Cells[2, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐         }         //         //取得表格中的数据         //         foreach (DataRowView row in dv)         {             rowIndex++;             colIndex = 0;             foreach (DataColumn col in dv.Table.Columns)             {                 colIndex++;                 //if (col.DataType == System.Type.GetType("System.DateTime"))                 //{                 //    excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");                 //    xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐                 //}                 //else                     if (col.DataType == System.Type.GetType("System.String"))                     {                                               excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();                                                   xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐                     }                     else                     {                         excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();                         if (col.ColumnName.ToString() == "用药数量")                         {                             number = colIndex;                         }                     }             }         }         //         //加载一个合计行         //         int rowSum = rowIndex + 1;         int colSum = 1;         if (number != 0)         {             excel.Cells[rowSum, number] = this.lblnumber.Text;         }         excel.Cells[rowSum, 1] = "合计";         xSt.get_Range(excel.Cells[rowSum, 1], excel.Cells[rowSum, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter;               //if (row[col.ColumnName].ToString() = "用药数量")         //{         //}         //         //设置选中的部分的颜色         //         xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();         xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Interior.ColorIndex = 19;//设置为浅黄色，共计有56种         //         //取得整个报表的标题         //         excel.Cells[1, 1] = str;         //         //设置整个报表的标题格式         //         //xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Bold = true;         xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Font.Size = 16;         //         //设置报表表格为最适应宽度         //         xSt.get_Range(excel.Cells[2, 1], excel.Cells[rowSum, colIndex]).Select();         xSt.get_Range(excel.Cells[2, 1], excel.Cells[rowSum, colIndex]).Columns.AutoFit();         //         //设置整个报表的标题为跨列居中         //         xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, colIndex]).Select();         xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;         //         //绘制边框         //         xSt.get_Range(excel.Cells[2, 1], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;         xSt.get_Range(excel.Cells[2, 1], excel.Cells[rowSum, 1]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗         xSt.get_Range(excel.Cells[2, 1], excel.Cells[2, colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置上边线加粗         xSt.get_Range(excel.Cells[2, colIndex], excel.Cells[rowSum, colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗         xSt.get_Range(excel.Cells[rowSum, 1], excel.Cells[rowSum, colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗         //         //显示效果         //         excel.Visible = true;         xBk.SaveCopyAs(Server.MapPath(".") + "\\" + "2008.xls");         ds = null;         xBk.Close(false, null, null);         excel.Quit();         System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);         System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);         System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);         xBk = null;         excel = null;         xSt = null;         GC.Collect();         string path = Server.MapPath("2008.xls");         System.IO.FileInfo file = new System.IO.FileInfo(path);         Response.Clear();         Response.Charset = "GB2312";         Response.ContentEncoding = System.Text.Encoding.UTF8;         // 添加头信息，为"文件下载/另存为"对话框指定默认文件名         Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));         // 添加头信息，指定文件大小，让浏览器能够显示下载进度         Response.AddHeader("Content-Length", file.Length.ToString());         // 指定返回的是一个不能被客户端读取的流，必须被下载         Response.ContentType = "application/ms-excel";               // 把文件流发送到客户端         Response.WriteFile(file.FullName);         // 停止页面的执行                 Response.End();           } 
 
[解决办法]
http://www.kehansoft.com/soaexcel/login.asp
看看这个效果怎么样，先在客户端生成excel文件，然后客户另存就可以了

热点排行

asp.net

导出规则的EXCEL文件并下载,该如何解决