.net导出Excel
2019独角兽企业重金招聘Python工程师标准>>>
综合参考了网上的方法，生成Excel文件提供下载，然后删除生成的Excel文件。
1、引用Microsoft.Office.Interop.Excel;( 属性里的嵌入互操作类型改为Fasle)
2、Default10.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default10.aspx.cs" Inherits="Default10" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="ExportToExcel" runat="server" Text="导出Excel"
onclick="ExportToExcel_Click" />
</div>
</form>
</body>
</html>
3、Default10.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Excel = Microsoft.Office.Interop.Excel; //添加引用
public partial class Default10 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void ExportToExcel_Click(object sender, EventArgs e)
{
Excel.Application excel1 = new Excel.Application();
excel1.DisplayAlerts = false;
Excel.Workbook workbook1 = excel1.Workbooks.Add(Type.Missing);
excel1.Visible = false;
Excel.Worksheet worksheet1 = (Excel.Worksheet)workbook1.Worksheets["sheet1"];
//表头
worksheet1.Cells[1, 1] = "姓名"; //Excel里从第1行，第1列计算
worksheet1.Cells[1, 2] = "性别";
worksheet1.Cells[1, 3] = "联系电话";
System.Data.DataTable dt = GetTestData(100);
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
worksheet1.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
}
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
string filePath = Server.MapPath("~/" + fileName);
workbook1.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excel1.Workbooks.Close();
excel1.Quit();
int generation = GC.GetGeneration(excel1);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel1);
excel1 = null;
GC.Collect(generation);
//打开要下载的文件，并把该文件存放在FileStream中
System.IO.FileStream Reader = System.IO.File.OpenRead(filePath);
//文件传送的剩余字节数：初始值为文件的总大小
long Length = Reader.Length;
HttpContext.Current.Response.Buffer = false;
HttpContext.Current.Response.AddHeader("Connection", "Keep-Alive");
HttpContext.Current.Response.ContentType = "application/octet-stream";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
HttpContext.Current.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); //往缓冲区读入数据
HttpContext.Current.Response.OutputStream.Write(Buffer, 0, ByteToRead); //把缓冲区的数据写入客户端浏览器
HttpContext.Current.Response.Flush(); //立即写入客户端
Length -= ByteToRead; //剩余字节数减少
}
else
{
//客户端浏览器已经断开，阻止继续循环
Length = -1;
}
}
//关闭该文件
Reader.Close();
if (System.IO.File.Exists(filePath))
System.IO.File.Delete(filePath);
}
System.Data.DataTable GetTestData(int num) //测试数据
{
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.DataRow dr;
dt.Columns.Add(new System.Data.DataColumn("ContactName", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("ContactSex", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("ContactPhone", typeof(String)));
for (int i = 0; i < num; i++)
{
Random rnd = new Random(Environment.TickCount * i);
dr = dt.NewRow();
dr[0] = "姓名" + rnd.Next(1, num);
dr[1] = rnd.Next(1, num) < num / 2 ? "男" : "女";
dr[2] = rnd.Next(1000000, 99999999);
dt.Rows.Add(dr);
}
return dt;
}
}
另一种利用Excel模板生成Excel方法如下：
private void ExportToExcel(DataTable dt, string fileName)
{
//转换为物理路径
string newFileName = HttpContext.Current.Server.MapPath("~/" + fileName);
//根据模板正式生成该Excel文件
File.Copy(HttpContext.Current.Server.MapPath("~/ContactTemplate.xls"), newFileName, true);
//建立指向该Excel文件的数据库连接
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + newFileName + ";Extended Properties='Excel 8.0;HDR=yes;IMEX=2'";
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["ContactName"] + "',";
XSqlString += "'" + (DR["ContactSex"].ToString() == "1" ? "男" : "女") + "',";
XSqlString += "'" + DR["ContactPhone"] + "')";
Cmd.CommandText = XSqlString;
Cmd.ExecuteNonQuery();
}
//操作结束，关闭连接
Conn.Close();
//打开要下载的文件，并把该文件存放在FileStream中
System.IO.FileStream Reader = System.IO.File.OpenRead(newFileName);
//文件传送的剩余字节数：初始值为文件的总大小
long Length = Reader.Length;
HttpContext.Current.Response.Buffer = false;
HttpContext.Current.Response.AddHeader("Connection", "Keep-Alive");
HttpContext.Current.Response.ContentType = "application/octet-stream";
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
HttpContext.Current.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); //往缓冲区读入数据
HttpContext.Current.Response.OutputStream.Write(Buffer, 0, ByteToRead); //把缓冲区的数据写入客户端浏览器
HttpContext.Current.Response.Flush(); //立即写入客户端
Length -= ByteToRead; //剩余字节数减少
}
else
{
//客户端浏览器已经断开，阻止继续循环
Length = -1;
}
}
//关闭该文件
Reader.Close();
//删除该Excel文件
if (File.Exists(newFileName))
File.Delete(newFileName);
}
---------------------------------------------------------------------------
2010-8-26 备注：
在项目中使用第2种方法时，出现"操作必须使用一个可更新的查询"的错误提示 ，原因是项目采用TFS管理，使Excel文件属性是只读的，解决方法是去掉只读属性：
File.Copy(HttpContext.Current.Server.MapPath("~/Views/ActivityAdmin/ContactTemplate.xls"), newFileName, true);
在上面代码的下面加上：
FileInfo f = new FileInfo(newFileName);
if (f.Attributes.ToString().IndexOf("ReadOnly") != -1)
{
f.Attributes = FileAttributes.Normal;
}
---------------------------------------------------------------------------
17:11 2010-12-23 备注：
用企业库读取Excel：
web.config配置：
<!--test.xls放在App_Data目录下-->
<!--HDR=yes;IMEX=1表示：第一行不作为数据返回，且以文本方式读取-->
<add name="testXls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;data source=|DataDirectory|test.xls;Extended Properties='Excel 8.0;HDR=yes;IMEX=1'" providerName="System.Data.OleDb" />
*.aspx.cs代码：
using Microsoft.Practices.EnterpriseLibrary.Data;
Database db = DatabaseFactory.CreateDatabase("testXls");
//[B0201$A2:C33]表示读取表B0201$的区域范围A2:C33
DataTable dt = db.ExecuteDataSet(CommandType.Text, "select * from [B0201$A2:C33]").Tables[0];
另一种不错方法：
使用HTML,CSS快速导出数据到Excel
http://www.cnblogs.com/ruinet/archive/2009/10/17/1585320.html
稍微改了下
public static void CreateExcel(string strTable, string fileName)
{
string HEADER = "<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">" +
"<meta http-equiv=Content-Type content=\"text/html; charset=\"gb2312\">" +
"<head>" +
"<!--[if gte mso 9]><xml>" +
"<x:ExcelWorkbook>" +
"<x:ExcelWorksheets>" +
"<x:ExcelWorksheet>" +
"<x:Name>Sheet1</x:Name>" +
"<x:WorksheetOptions>" +
"<x:Print>" +
"<x:ValidPrinterInfo />" +
"</x:Print>" +
"</x:WorksheetOptions>" +
"</x:ExcelWorksheet>" +
"</x:ExcelWorksheets>" +
"</x:ExcelWorkbook>" +
"</xml>" +
"<![endif]-->";
System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
System.Web.HttpContext.Current.Response.ContentType = "ms-excel/application";
StringBuilder sbHtml = new StringBuilder();
sbHtml.AppendFormat(@"{0}</head>
<body>{1}</body>
</html>", HEADER, strTable);
System.Web.HttpContext.Current.Response.Write(sbHtml.ToString());
System.Web.HttpContext.Current.Response.Flush();
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.End();
}
转载于:https://my.oschina.net/bv10000/blog/190999
总结
以上是生活随笔为你收集整理的.net导出Excel的全部内容，希望文章能够帮你解决所遇到的问题。
- 上一篇： 将用户输入内容中的尖括号、引号等进行转义
- 下一篇： 通过案例了解puppet创建虚拟资源解决