本人VS2005开发的系统的某个网页上有一个GridView控件显示查询出的数据,使用以下一个函数导出一个Excel表,本地机安装的是office 2007,当打开该导出的Excel文件时,系统提示说:您尝试打开的文件“FileName.xls”的格式与文件扩展名指定的格式不一致。打开文件前请验证文件没有损坏且来源可靠。是否立刻打开该文件?当点击是(Y)后,文件可以打开,数据也对,另存为Excel文档后,打开新保存的文件正常。请各位帮忙看看,如何修改方能直接保存成正确的Excel文件,以下是导出的函数和参数。调用参数:string FileType="application/ms-excel"
string FileName="FileName.xls"自定义函数:private void Export(string FileType, string FileName)
{
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.Default ;
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
Response.ContentType = FileType;
this.EnableViewState = false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
GridView1.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
}
string FileName="FileName.xls"自定义函数:private void Export(string FileType, string FileName)
{
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.Default ;
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
Response.ContentType = FileType;
this.EnableViewState = false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
GridView1.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
}
解决方案 »
- 访问sql server2005时报错查询的表太多 最多允许256张
- 求用vs2005或者2008制作asp.net程序的安装包(包括IIS虚拟目录的创建和数据库附加)
- 新手散分:如何判断在客户端上传文件的路径是否存在?
- ASP.NET2.0 如何去掉 TreeView的节点的连接功能
- "1,11,111/2,22,222/3,33,333"
- 一个datagrid的问题!!!
- DataGrid分页问题
- DataGrid分页问题,论坛搜索不了,只好提问,很简单的
- 请给点帮助吧,我真得没办法了!!!谁碰到过关于machine.config的问题
- 如何用语句表达:输入框的值不能为空?
- gridview 隐藏某列 隐藏不了
- datagrid数据列绑定问题
string.Format(string.Format(@"{{0}}{0}.xls", tmpName), HttpContext.Current.Server.MapPath("../../../Temp/")), 56,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value);
{
this.GridView1.AllowPaging = false; // 将有分页的GridView中的数据全部导出到Excel
Export(GridView1, "application/ms-excel", "项目报表.xls");
//Export(GridView5, } public override void VerifyRenderingInServerForm(Control control)
{
//这里什么也不用写
}
public void Export(GridView gv, string FileType, string FileName)
{ string style = @"<style>.text{mso-number-format:@}</script>";//导入到excel时,保存表里数字列中前面存在的 0 . PrepareGridViewForExport(gv);//将模版列显示出来 Response.Clear(); Response.Charset = "GB2312"; Response.ContentEncoding = System.Text.Encoding.UTF7; Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8).ToString()); Response.ContentType = FileType; this.EnableViewState = false; //this.GridView5.AllowPaging = false;
gv.AllowPaging = false; System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true); System.IO.StringWriter sw = new System.IO.StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw); //this.GridView5.RenderControl(htw);
gv.RenderControl(htw);
Response.Write(style); Response.Write(sw.ToString().Replace("border='0'", "border='1'")); //Response.Write(dt.ToString()); Response.End(); } public void PrepareGridViewForExport(Control gv)//模式化特殊元素 flashcong
{ LinkButton lb = new LinkButton();
Label l = new Label();
string name = String.Empty;
for (int i = 0; i < gv.Controls.Count; i++)
{
if (gv.Controls[i].GetType() == typeof(LinkButton))
{
l.Text = (gv.Controls[i] as LinkButton).Text; gv.Controls.Remove(gv.Controls[i]);
gv.Controls.AddAt(i, l);
}
else if (gv.Controls[i].GetType() == typeof(HtmlAnchor))
{
l.Text = (gv.Controls[i] as HtmlAnchor).InnerText;
gv.Controls.Remove(gv.Controls[i]);
gv.Controls.AddAt(i, l);
}
else if (gv.Controls[i].GetType() == typeof(DropDownList))
{
l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;
gv.Controls.Remove(gv.Controls[i]);
gv.Controls.AddAt(i, l);
}
else if (gv.Controls[i].GetType() == typeof(CheckBox))
{
l.Text = (gv.Controls[i] as CheckBox).Checked ? "True" : "False";
gv.Controls.Remove(gv.Controls[i]);
gv.Controls.AddAt(i, l);
}
else if (gv.Controls[i].GetType() == typeof(ImageButton))
{
l.Text = "图片";
gv.Controls.Remove(gv.Controls[i]);
gv.Controls.AddAt(i, l);
}
if (gv.Controls[i].HasControls())
{
PrepareGridViewForExport(gv.Controls[i]);
} } }
试试看,我昨天还用的
你去中国移动的话费查询中的页面去看看 他那个下载 Excel 文档其实用记事本也可以打开
这是教程 http://blog.csdn.net/tonyqus/archive/2009/11/29/4898453.aspx你百度 试试从其它地方下载
protected void lbExport_Click(object sender, EventArgs e)
{
string blankfilepath = Server.MapPath("~") + "\\SetTeacherSalary.xls";
string destpath = "download\\" + Convert.ToString(Session["UserID"]) + "\\" + DateTime.Now.ToString("yyyyMM");
string destfilepath = Server.MapPath("~") + "\\" + destpath;
DirectoryInfo dir = new DirectoryInfo(destfilepath);
if (!dir.Exists)
dir.Create();
destfilepath = destfilepath + "\\SetTeacherSalary.xls";
destpath = destpath + "\\SetTeacherSalary.xls";
File.Copy(blankfilepath, destfilepath, true);
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + destfilepath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=0\";"); conn.Open();
try
{
DataSet ds = plan.getList("");
foreach (DataRow dr in ds.Tables[0].Rows)
{ string StudentName = Convert.ToString(dr["StudentName"]);
string SubjectName = Convert.ToString(dr["SubjectName"]);
string GradeDesc = Convert.ToString(dr["GradeDesc"]);
string sql = "insert into [sheet1$] (姓名,学科,年级)" +
" values('{0}','{1}','{2}')";
sql = string.Format(sql, StudentName, SubjectName,GradeDesc);
OleDbCommand cmd = new OleDbCommand(sql, conn);
cmd.ExecuteNonQuery();
} }
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
conn.Dispose();
}
}
我是先定义了一个模板是2003的!设置好了以后再把数据传到新建的Excel中那样可以
晕 怎么实现不了??他生成Excel的数据源都是你定的 gridwiew怎么你是怎么根据查询条件显示的? 不就是查询条件得到一个dataset嘛, 你再重新给它不就行了,没有非要全部 select * from 啊??你根本没有理解到那个控件生成Excel的方法
{
GridView1.AllowPaging = false; //清除分页
BindInfo();
}
Response.Clear();
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
Response.AddHeader("content-disposition", "attachment;filename=TCRM.xls");
//Response.Charset = "UTF-8";//设置字符集
//curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;//设置编码集
Response.Charset = "utf-8";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
//写到Excel的数据不用分页
//BindData();
GridView1.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());//向客户端写数据
Response.End(); GridView1.AllowSorting = true;
//恢复分页
BindInfo(); //再次绑定
[Quote=引用 16 楼 wyq29 的回复:]
protected void expNPOIExcel_Click(object sender, EventArgs e)
{
string strCurrentDir = Server.MapPath("Web.Config");
strCurrentDir = strCurrentDir.Substring(0, strCurrentDir.Length - 11);
DirectoryInfo excelDir = new DirectoryInfo(strCurrentDir + "\\ExcelReports");
HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileStream(excelDir.FullName+"\\SrayExcelReport.xlt", FileMode.Open));
HSSFSheet sheet = hssfworkbook.GetSheet ("Sheet1");//.CreateSheet(Request.QueryString[1].ToString());
HSSFRow [] rows=new HSSFRow [GridView1 .Rows.Count];
string strTEXT;
for (int i=0; i < GridView1.Rows.Count; i++)
{
rows[i] = sheet.GetRow (i+1);
// rows[i].Height = 20;
for (int j = 0; j < GridView1.Columns.Count; j++)
{
strTEXT = GridView1.Rows[i].Cells[j].Text;
if (strTEXT != " ")
rows[i].CreateCell(j).SetCellValue(GridView1.Rows[i].Cells[j].Text);
}
}
//以下生成ExcelReport
if (excelDir.Exists == false)
excelDir.Create();
FileStream file = new FileStream(excelDir.FullName +"\\"+ViewState[ "strClass"]+".xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
//以下下载ExcelReport
FileInfo excel = new FileInfo(excelDir.FullName + "\\" + ViewState["strClass"] + ".xls");
Response.Clear();
Response.ClearHeaders();
Response.Buffer = false;
Response.AppendHeader("Content-Disposition", "attachment;filename=" + (System.Web.HttpUtility.UrlEncode(Path.GetFileName(excelDir.FullName + "\\" + ViewState["strClass"] + ".xls"), System.Text.Encoding.UTF8)).Replace("+", " "));
Response.AppendHeader("Content-Length", excel.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.WriteFile(excelDir.FullName + "\\" + ViewState["strClass"].ToString () + ".xls"); if (excel.Exists)
excel.Delete();
Response.Flush();
Response.End();
}