|
网上共享的ASP.NET中将数据生成excel报表的代码，往往都是不能根据需求自己定制表头，而是直接从数据源读取的数据表中的表字段名。这里我共享一种生成excel方法，并且根据实际需要随意更改标题。
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 System.Data.SqlClient;
using System.IO;
using System.Text;
/// <summary>
/// ToExcel 的摘要说明
/// </summary>
public class ToExcel
{
public ToExcel()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
//listname 和 cols 个数 要相等 ，顺序一一对应
public static void tableToExcel(DataTable tb, string[] listname, string[] cols)
{
if ((tb == null) || (tb.Rows.Count == 0))
{
return;
}
FileStream file;
StreamWriter filewrite;
Random r = new Random();
string t = r.NextDouble().ToString().Remove(0, 2);
string filename = GetRandom() + ".xls";
string path = System.Web.HttpContext.Current.Server.MapPath(".") + @"\tempdata\" + filename;
int i, j;
file = new FileStream(path, System.IO.FileMode.Create, System.IO.FileAccess.Write);
filewrite = new StreamWriter(file, System.Text.Encoding.Unicode);
StringBuilder strline = new StringBuilder();
for (i = 1; i <= listname.Length; i++)
{
strline.Append(listname[i - 1]).Append(Convert.ToChar(9));
}
filewrite.WriteLine(strline.ToString());
//表内容
for (i = 1; i <= tb.Rows.Count; i++)
{
strline.Remove(0,strline.Length);//清空全部内容
//strline = "";
for (j = 1; j <= cols.Length; j++)
{
if (j == 1)
{
strline.Append(tb.Rows[i-1][cols[j-1]]).Append(Convert.ToChar(9));
}
else
{
strline.Append(tb.Rows[i - 1][cols[j - 1]]).Append(Convert.ToChar(9));
}
}
filewrite.WriteLine(strline);
}
filewrite.Close();
file.Close();
if (File.Exists(path))
{
HttpContext.Current.Response.Clear();
bool success = ResponseFile(HttpContext.Current.Request, HttpContext.Current.Response, filename, path, 1024000);
if (!success)
HttpContext.Current.Response.Write("下载文件出错！");
HttpContext.Current.Response.End();
}
else
{
HttpContext.Current.Response.Write("文件不存在！");
}
string l_strHtml = "<script language='JavaScript'>";
l_strHtml += " window.open('../tempdata/excel.xls','newwindow','height=800,width=1024,scrollbars=yes,resizable=yes,location=yes, status=yes,menubar=yes,toolbar=yes,titlebar=yes')";
l_strHtml += "</script>";
HttpContext.Current.Response.Write(l_strHtml);
}
public static bool ResponseFile(HttpRequest _Request, HttpResponse _Response, string _fileName, string _fullPath, long _speed)
{
try
{
FileStream myFile = new FileStream(_fullPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
BinaryReader br = new BinaryReader(myFile);
try
{
_Response.AddHeader("Accept-Ranges", "bytes");
_Response.Buffer = false;
long fileLength = myFile.Length;
long startBytes = 0;
int pack = 10240; //10K bytes
//int sleep = 200; //每秒5次 即5*10K bytes每秒
int sleep = (int)System.Math.Floor(1000 * pack*1.0 / _speed) + 1;
if (_Request.Headers["Range"] != null)
{
_Response.StatusCode = 206;
string[] range = _Request.Headers["Range"].Split(new char[] { '=', '-' });
startBytes = Convert.ToInt64(range[1]);
}
_Response.AddHeader("Content-Length", (fileLength - startBytes).ToString());
if (startBytes != 0)
{
_Response.AddHeader("Content-Range", string.Format(" bytes {0}-{1}/{2}", startBytes, fileLength - 1, fileLength));
}
string filename = System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(_fileName)).Replace("+", "%20");
_Response.AddHeader("Connection", "Keep-Alive");
_Response.ContentType = "application/octet-stream";
_Response.AddHeader("Content-Disposition", "attachment;filename=" + filename); //HttpUtility.UrlEncode(_fileName));//HttpContext.Current.Server.UrlEncode(_fileName));//HttpUtility.UrlEncode(_fileName,System.Text.Encoding.Default));
br.BaseStream.Seek(startBytes, SeekOrigin.Begin);
int maxCount = (int)System.Math.Floor((fileLength - startBytes) *1.0/ pack) + 1;
for (int i = 0; i < maxCount; i++)
{
if (_Response.IsClientConnected)
{
_Response.BinaryWrite(br.ReadBytes(pack));
System.Threading.Thread.Sleep(sleep);
}
else
{
i = maxCount;
}
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
return false;
}
finally
{
br.Close();
myFile.Close();
}
}
catch
{
return false;
}
return true;
}
#region 用当前时间生成随机文件名
public static string GetRandom()//根据当前连接的用户的访问时间来生成excle文件，精确到秒，这样在tempdata文件夹里不会出现重复的excel文件，保证每次生成excel文件成功
{
string random = "";
DateTime date = DateTime.Now;
random = date.ToString().Replace("-", "").Replace(":", "").Replace(" ", "").Replace(" ", "");
return random;
}
#endregion
}
OK，类库编写完毕，我们在每个aspx页面直接调用这个类库就行，需要输入数据集，标题和数据库表中的字段名称两个数组。代码如下:
//PubSql为sql查询语句
if (PubSql != "")
{
SqlDataAdapter sda=new SqlDataAdapter(PubSql,MyConnection);
DataSet ds = new DataSet();
sda.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
string[] listname = { "标题一", "标题二", "标题三};
string[] cols = { "columns1", "columns2", "columns3"};
ToExcel.tableToExcel(ds.Tables[0], listname, cols);
}
}
第一次在博客园写blog好多格式还没熟悉，copy过来的代码没有了vs里面的格式风格了，多包含。
|
客服电话
APP下载
官方微信
请发表评论