代码如下
Scores score = new Scores(); //创建Scores对象
DataSet ds = score.QueryScore(); //调用QueryScore方法查询成绩并将查询结果放到DataSet数据集中
DataTable DT = ds.Tables[0];
//生成将要存放结果的Excel文件的名称
string NewFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//转换为物理路径
NewFileName = Server.MapPath("Temp/" + NewFileName);
//根据模板正式生成该Excel文件
File.Copy(Server.MapPath("../Module01.xls"), NewFileName, true);
//建立指向该Excel文件的数据库连接
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + NewFileName + ";Extended Properties='Excel 8.0;'";
OleDbConnection Conn = new OleDbConnection(strConn);
//打开连接,为操作该文件做准备
Conn.Open();
OleDbCommand Cmd = new OleDbCommand("", Conn); foreach (DataRow DR in DT.Rows)
{
string XSqlString = "insert into [Sheet1$]";
XSqlString += "([用户姓名],[试卷],[成绩],[考试时间]) values(";
XSqlString += "'" + DR["UserName"] + "',";
XSqlString += "'" + DR["PaperName"] + "',";
XSqlString += "'" + DR["Score"] + "',";
XSqlString += "'" + DR["ExamTime"] + "')";
Cmd.CommandText = XSqlString;
Cmd.ExecuteNonQuery();
} //操作结束,关闭连接
Conn.Close();
//打开要下载的文件,并把该文件存放在FileStream中
System.IO.FileStream Reader = System.IO.File.OpenRead(NewFileName);
//文件传送的剩余字节数:初始值为文件的总大小
long Length = Reader.Length; Response.Buffer = false;
Response.AddHeader("Connection", "Keep-Alive");
Response.ContentType = "application/octet-stream";
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("学生成绩.xls"));
Response.AddHeader("Content-Length", Length.ToString()); byte[] Buffer = new Byte[10000]; //存放欲发送数据的缓冲区
int ByteToRead; //每次实际读取的字节数 while (Length > 0)
{
//剩余字节数不为零,继续传送
if (Response.IsClientConnected)
{
//客户端浏览器还打开着,继续传送
ByteToRead = Reader.Read(Buffer, 0, 10000); //往缓冲区读入数据
Response.OutputStream.Write(Buffer, 0, ByteToRead); //把缓冲区的数据写入客户端浏览器
Response.Flush(); //立即写入客户端
Length -= ByteToRead; //剩余字节数减少
}
else
{
//客户端浏览器已经断开,阻止继续循环
Length = -1;
}
} //关闭该文件
Reader.Close();
//删除该Excel文件
File.Delete(NewFileName);问题就是能够正常的生成excel文件,下载下来后,打开的时候会提示“您尝试打开的文件”学生成绩.xls“的格式与扩展名指定的格式不一样,打开文件前请验证文件没有损坏且来源可信”,然后我就把后缀名改成xlsx试了一下,仍然是这个错误。拜托大家指点下
Scores score = new Scores(); //创建Scores对象
DataSet ds = score.QueryScore(); //调用QueryScore方法查询成绩并将查询结果放到DataSet数据集中
DataTable DT = ds.Tables[0];
//生成将要存放结果的Excel文件的名称
string NewFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//转换为物理路径
NewFileName = Server.MapPath("Temp/" + NewFileName);
//根据模板正式生成该Excel文件
File.Copy(Server.MapPath("../Module01.xls"), NewFileName, true);
//建立指向该Excel文件的数据库连接
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + NewFileName + ";Extended Properties='Excel 8.0;'";
OleDbConnection Conn = new OleDbConnection(strConn);
//打开连接,为操作该文件做准备
Conn.Open();
OleDbCommand Cmd = new OleDbCommand("", Conn); foreach (DataRow DR in DT.Rows)
{
string XSqlString = "insert into [Sheet1$]";
XSqlString += "([用户姓名],[试卷],[成绩],[考试时间]) values(";
XSqlString += "'" + DR["UserName"] + "',";
XSqlString += "'" + DR["PaperName"] + "',";
XSqlString += "'" + DR["Score"] + "',";
XSqlString += "'" + DR["ExamTime"] + "')";
Cmd.CommandText = XSqlString;
Cmd.ExecuteNonQuery();
} //操作结束,关闭连接
Conn.Close();
//打开要下载的文件,并把该文件存放在FileStream中
System.IO.FileStream Reader = System.IO.File.OpenRead(NewFileName);
//文件传送的剩余字节数:初始值为文件的总大小
long Length = Reader.Length; Response.Buffer = false;
Response.AddHeader("Connection", "Keep-Alive");
Response.ContentType = "application/octet-stream";
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("学生成绩.xls"));
Response.AddHeader("Content-Length", Length.ToString()); byte[] Buffer = new Byte[10000]; //存放欲发送数据的缓冲区
int ByteToRead; //每次实际读取的字节数 while (Length > 0)
{
//剩余字节数不为零,继续传送
if (Response.IsClientConnected)
{
//客户端浏览器还打开着,继续传送
ByteToRead = Reader.Read(Buffer, 0, 10000); //往缓冲区读入数据
Response.OutputStream.Write(Buffer, 0, ByteToRead); //把缓冲区的数据写入客户端浏览器
Response.Flush(); //立即写入客户端
Length -= ByteToRead; //剩余字节数减少
}
else
{
//客户端浏览器已经断开,阻止继续循环
Length = -1;
}
} //关闭该文件
Reader.Close();
//删除该Excel文件
File.Delete(NewFileName);问题就是能够正常的生成excel文件,下载下来后,打开的时候会提示“您尝试打开的文件”学生成绩.xls“的格式与扩展名指定的格式不一样,打开文件前请验证文件没有损坏且来源可信”,然后我就把后缀名改成xlsx试了一下,仍然是这个错误。拜托大家指点下
解决方案 »
- 难道就没人知道这个问题吗?
- 未将对象引用设置到对象的实例
- POPUP问题
- 如何能重写MessgaeBox 设置我想要的风格外观和功能?在线送分!
- C#2005用代码访问Access数据库老是出错,代码应该没错.请各位大哥看看是什么原因?
- 多线程处理业务请求,怎么把处理结果写入到文件里?总是报有进程打开文件
- 关于HttpWebRequest中使用socks代理的问题
- 请教各位,如何用c#实现一个监控报警的功能
- 请教,我把我的.NET代码,放到了空间供应商提供的空间,但是,他们问我,文件夹的权限怎么设置?谁能帮忙??
- API 的双击按纽 常量叫什么?
- C# 中句点的疑问,请高手给我讲讲概念
- 关于FTP批量下载的问题续
方法:开始 -> 运行 -> 输入regedit -> 确定2、找到注册表子项
HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security3、在右侧空白处点击鼠标右键,选择“新建 -> DWORD值(D)”,输入“ExtensionHardening”点击确定。4、用鼠标右键点击ExtensionHardening,然后单击“修改(M)”,在数值数据中填写“0”即可确定。5、关闭注册表编辑器,再次打开xls文件看看是不是提示是不是不见了?小知识:ExtensionHardening设置的值的数据设置: * 0: 不检查文件扩展名和文件类型并绕过该函数的警告消息。
* 1: 检查文件扩展名和文件类型。如果它们不匹配会显示警告消息。
* 2: 检查文件扩展名和文件类型。如果它们不匹配不要打开该文件。试一下行不行
丢失文件:E:\..\CSS\CSS.css还是没显示出来内容啊
项目中建立一个excel文件夹,里面放excel模版,然后通过连接数据库获得需要数据
接着填入excel显示出来我以前都这样写的
ASPX 代码
<%@ Page Language="C#" EnableViewState="true" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"> protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.DataRow dr;
dt.Columns.Add(new System.Data.DataColumn("学生班级", typeof(System.String)));
dt.Columns.Add(new System.Data.DataColumn("学生姓名", typeof(System.String)));
dt.Columns.Add(new System.Data.DataColumn("语文", typeof(System.Decimal)));
dt.Columns.Add(new System.Data.DataColumn("数学", typeof(System.Decimal)));
dt.Columns.Add(new System.Data.DataColumn("英语", typeof(System.Decimal)));
dt.Columns.Add(new System.Data.DataColumn("计算机", typeof(System.Decimal)));
System.Random rd = new System.Random();
for (int i = 0; i < 88; i++)
{
dr = dt.NewRow();
dr[0] = "班级" + i.ToString();
dr[1] = "【孟子E章】" + i.ToString();
dr[2] = System.Math.Round(rd.NextDouble() * 100, 0);
dr[3] = System.Math.Round(rd.NextDouble() * 100, 0);
dr[4] = System.Math.Round(rd.NextDouble() * 100, 0);
dr[5] = System.Math.Round(rd.NextDouble() * 100, 0);
dt.Rows.Add(dr);
}
GridView1.DataSource = dt;
GridView1.DataBind();
}
} protected void Button1_Click(object sender, EventArgs e)
{
//假如每10条数据放在一个 Sheet 里面,先计算需要多少个 Sheet
int ItenCountPerSheet = 10;
int SheetCount = Convert.ToInt32(Math.Ceiling((double)GridView1.Rows.Count / ItenCountPerSheet)); String ExportFileName = "孟宪会Excel表格测试";
if (Request.Browser.Browser.IndexOf("MSIE") > -1)
{
ExportFileName = Server.UrlEncode(ExportFileName);
}
Response.ClearContent();
Response.BufferOutput = true;
Response.Charset = "utf-8";
Response.ContentType = "text/xml";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AppendHeader("Content-Disposition", "attachment;filename=" + ExportFileName + ".xls");
// 采用下面的格式,将兼容 Excel 2003,Excel 2007, Excel 2010。
// Response.AppendHeader("Content-Disposition", "attachment;filename="+Server.UrlEncode("孟宪会Excel表格测试")+".xml");
Response.Write("<?xml version='1.0'?><?mso-application progid='Excel.Sheet'?>");
Response.Write(@"\r\n<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'
xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'>");
Response.Write(@"\r\n<DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>");
Response.Write(@"\r\n<Author>孟宪会</Author><LastAuthor>孟子E章</LastAuthor>
<Created>2010-09-08T14:07:11Z</Created><Company>mxh</Company><Version>1990</Version>");
Response.Write("\r\n</DocumentProperties>");
Response.Write(@"\r\n<Styles><Style ss:ID='Default' ss:Name='Normal'><Alignment ss:Vertical='Center'/>
<Borders/><Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/><Interior/><NumberFormat/><Protection/></Style>");
//定义标题样式
Response.Write(@"<Style ss:ID='Header'><Borders><Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders>
<Font ss:FontName='宋体' x:CharSet='134' ss:Size='18' ss:Color='#FF0000' ss:Bold='1'/></Style>");
//定义边框
Response.Write(@"<Style ss:ID='border'><NumberFormat ss:Format='@'/><Borders>
<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders></Style>");
Response.Write("</Styles>");
for (int i = 0; i < SheetCount; i++)
{
//计算该 Sheet 中的数据起始行和结束行。
int start = ItenCountPerSheet * i;
int end = ItenCountPerSheet * (i + 1);
if (end > GridView1.Rows.Count) end = GridView1.Rows.Count;
Response.Write("\r\n<Worksheet ss:Name='Sheet" + (i+1) + "'>");
Response.Write("\r\n<Table x:FullColumns='1' x:FullRows='1'>");
//输出标题 Response.Write("\r\n<Row ss:AutoFitHeight='1'>");
for (int j = 0; j < GridView1.HeaderRow.Cells.Count; j++)
{
Response.Write("<Cell ss:StyleID='Header'><Data ss:Type='String'>" + GridView1.HeaderRow.Cells[j].Text + "</Data></Cell>");
}
Response.Write("\r\n</Row>"); for (int j = start; j < end; j++)
{
Response.Write("\r\n<Row>");
for (int c = 0; c < GridView1.HeaderRow.Cells.Count; c++)
{
//对于数字,采用Number数字类型
if (c > 1)
{
Response.Write("<Cell ss:StyleID='border'><Data ss:Type='Number'>" + GridView1.Rows[j].Cells[c].Text + "</Data></Cell>");
}
else
{
Response.Write("<Cell ss:StyleID='border'><Data ss:Type='String'>" + GridView1.Rows[j].Cells[c].Text + "</Data></Cell>");
}
}
Response.Write("\r\n</Row>");
}
Response.Write("\r\n</Table>");
Response.Write("\r\n</Worksheet>");
Response.Flush();
}
Response.Write("\r\n</Workbook>");
Response.End();
}</script><html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="导出测试" />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</form>
</body>
</html>
C# 代码
<%@ Page Language="C#" %><script runat="server"> protected void Page_Load(object sender, EventArgs e)
{
// 下面采用的是DataTable,也可以采用DataSet,其中每个DataTable可以保存成一个 Sheet
// 迅雷下载时可以在下载完毕后会自动把文件名更新成 xls 或者 xml 的。
System.Data.DataTable dt = new System.Data.DataTable();
if (!Page.IsPostBack)
{
System.Data.DataRow dr;
dt.Columns.Add(new System.Data.DataColumn("学生班级", typeof(System.String)));
dt.Columns.Add(new System.Data.DataColumn("学生姓名", typeof(System.String)));
dt.Columns.Add(new System.Data.DataColumn("语文", typeof(System.Decimal)));
dt.Columns.Add(new System.Data.DataColumn("数学", typeof(System.Decimal)));
dt.Columns.Add(new System.Data.DataColumn("英语", typeof(System.Decimal)));
dt.Columns.Add(new System.Data.DataColumn("计算机", typeof(System.Decimal)));
System.Random rd = new System.Random();
for (int i = 0; i < 88; i++)
{
dr = dt.NewRow();
dr[0] = "班级" + i.ToString();
dr[1] = "【孟子E章】" + i.ToString();
dr[2] = System.Math.Round(rd.NextDouble() * 100, 0);
dr[3] = System.Math.Round(rd.NextDouble() * 100, 0);
dr[4] = System.Math.Round(rd.NextDouble() * 100, 0);
dr[5] = System.Math.Round(rd.NextDouble() * 100, 0);
dt.Rows.Add(dr);
}
} //假如每10条数据放在一个 Sheet 里面,先计算需要多少个 Sheet
int ItenCountPerSheet = 10;
int SheetCount = Convert.ToInt32(Math.Ceiling((double)dt.Rows.Count / ItenCountPerSheet));
Response.ClearContent();
Response.BufferOutput = true;
Response.Charset = "utf-8";
Response.ContentType = "application/ms-excel";
Response.AddHeader("Content-Transfer-Encoding", "binary");
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Response.AppendHeader("Content-Disposition", "attachment;filename="+Server.UrlEncode("孟宪会Excel表格测试")+".xls");
// 采用下面的格式,将兼容 Excel 2003,Excel 2007, Excel 2010。 String FileName = "孟宪会Excel表格测试";
if (!String.IsNullOrEmpty(Request.UserAgent))
{
// firefox 里面文件名无需编码。
if (!(Request.UserAgent.IndexOf("Firefox") > -1 && Request.UserAgent.IndexOf("Gecko") > -1))
{
FileName = Server.UrlEncode(FileName);
}
}
Response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls");
Response.Write("<?xml version='1.0'?><?mso-application progid='Excel.Sheet'?>");
Response.Write(@"<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'
xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'>");
Response.Write(@"<DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>");
Response.Write(@"<Author>孟宪会</Author><LastAuthor>孟子E章</LastAuthor>
<Created>2010-09-08T14:07:11Z</Created><Company>mxh</Company><Version>1990</Version>");
Response.Write("</DocumentProperties>");
Response.Write(@"<Styles><Style ss:ID='Default' ss:Name='Normal'><Alignment ss:Vertical='Center'/>
<Borders/><Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/><Interior/><NumberFormat/><Protection/></Style>");
//定义标题样式
Response.Write(@"<Style ss:ID='Header'><Borders><Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders>
<Font ss:FontName='宋体' x:CharSet='134' ss:Size='18' ss:Color='#FF0000' ss:Bold='1'/></Style>"); //定义边框
Response.Write(@"<Style ss:ID='border'><NumberFormat ss:Format='@'/><Borders>
<Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
<Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders></Style>"); Response.Write("</Styles>"); //SheetCount代表生成的 Sheet 数目。
for (int i = 0; i < SheetCount; i++)
{
//计算该 Sheet 中的数据起始行和结束行。
int start = ItenCountPerSheet * i;
int end = ItenCountPerSheet * (i + 1);
if (end > dt.Rows.Count) end = dt.Rows.Count; Response.Write("<Worksheet ss:Name='Sheet" + (i + 1) + "'>");
Response.Write("<Table x:FullColumns='1' x:FullRows='1'>"); //输出标题
Response.Write("\r\n<Row ss:AutoFitHeight='1'>");
for (int j = 0; j < dt.Columns.Count; j++)
{
Response.Write("<Cell ss:StyleID='Header'><Data ss:Type='String'>" + dt.Columns[j].ColumnName + "</Data></Cell>");
}
Response.Write("\r\n</Row>");
for (int j = start; j < end; j++)
{
Response.Write("<Row>");
for (int c = 0; c < 6; c++)
{
//对于数字,采用Number数字类型
if (c > 1)
{
Response.Write("<Cell ss:StyleID='border'><Data ss:Type='Number'>" + dt.Rows[j][c].ToString() + "</Data></Cell>");
}
else
{
Response.Write("<Cell ss:StyleID='border'><Data ss:Type='String'>" + dt.Rows[j][c].ToString() + "</Data></Cell>");
}
}
Response.Write("</Row>");
}
Response.Write("</Table>");
Response.Write("</Worksheet>");
Response.Flush();
}
Response.Write("</Workbook>");
Response.End();
}
</script>