C#导出excel或csv文件

最新推荐文章于 2025-06-12 08:20:38 发布

原创最新推荐文章于 2025-06-12 08:20:38 发布 · 1.8k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#C# #数据导出 #excel或csv

C# 同时被 2 个专栏收录

14 篇文章

订阅专栏

MVC

4 篇文章

订阅专栏

本文介绍两种将用户数据导出到Excel的方法。第一种方法使用C#代码从数据库读取用户信息，并构建HTML表格内容，然后通过浏览器下载为Excel文件。第二种方法涉及搜索额外运行信息，将数据整理成CSV格式并提供下载。

方法一：

Model:

public class Execlinfo

{

public int UserID { get; set; }

public string UserName { get; set; }

public string Password { get; set; }

public string PasswordSalt { get; set; }

public string PasswordFormat { get; set; }

public string AccountStatus { get; set; }

public string TrueName { get; set; }

public string Phone { get; set; }

public string Mobile { get; set; }

public string Email { get; set; }

public string Address { get; set; }

public string SN { get; set; }

public string CompanyName { get; set; }

public string Gender { get; set; }

public string Zip { get; set; }

public string Position { get; set; }

public string Sequence { get; set; }

public string Description { get; set; }

public string Permission { get; set; }

public string NickName { get; set; }

public DateTime BirthDay { get; set; }

public string RegisterSite { get; set; }

public string ProvinceID { get; set; }

public string CityID { get; set; }

public string Subscription { get; set; }

public string Signature { get; set; }

public string CardNumber { get; set; }

public string CardPoints { get; set; }

public string Balance { get; set; }

public string CardType { get; set; }

public string TotalPoints { get; set; }

}

Dao:

public IList<Execlinfo> execlout()

{

string sql = @"select u.UserID,u.UserName,u.Password,u.PasswordSalt,u.PasswordFormat,u.AccountStatus,me.TrueName,

me.Phone,me.Mobile,me.Email,me.Address,me.SN,me.CompanyName,me.Gender,me.Zip,me.Position,

me.Sequence,me.Description,me.Permission,me.NickName,me.BirthDay,me.RegisterSite,me.ProvinceID,

me.CityID,me.Subscription,me.Signature,mem.CardNumber,mem.CardPoints,mem.Balance,mem.CardType,mem.TotalPoints

from dn_user as u inner join dn_membership me on u.UserId=me.UserID inner join

dn_membershipcard mem on me.UserID=mem.UserID";

DnDataReader dnreader = new DnDataReader(this);

IList<Execlinfo> list = new List<Execlinfo>();

try

{

IDataReader dr = dnreader.ExecuteReader(sql);

while (dr.Read())

{

Execlinfo info = getinfo(dr);

list.Add(info);

}

finally

{

dnreader.Close();

}

return list;

}

public Execlinfo getinfo(IDataReader dr)

{

Execlinfo info = new Execlinfo();

info.UserName = dr["UserName"].ToString();

info.Password = dr["Password"].ToString();

info.PasswordSalt = dr["PasswordSalt"].ToString();

info.PasswordFormat = dr["PasswordFormat"].ToString();

info.AccountStatus = dr["AccountStatus"].ToString();

info.Phone = dr["Phone"].ToString();

info.Mobile = dr["Mobile"].ToString();

info.Email = dr["Email"].ToString();

info.Address = dr["Address"].ToString();

info.SN = dr["SN"].ToString();

info.CompanyName = dr["CompanyName"].ToString();

info.Gender = dr["Gender"].ToString();

info.Zip = dr["Zip"].ToString();

info.Position = dr["Position"].ToString();

info.Sequence = dr["Sequence"].ToString();

info.Description = dr["Description"].ToString();

info.Permission = dr["Permission"].ToString();

info.NickName = dr["NickName"].ToString();

info.BirthDay = dr["BirthDay"] != Convert.DBNull ? Convert.ToDateTime(dr["BirthDay"]) : DateTime.MinValue;

info.RegisterSite = dr["RegisterSite"].ToString();

info.ProvinceID = dr["ProvinceID"].ToString();

info.CityID = dr["CityID"].ToString();

info.Signature = dr["Signature"].ToString();

info.CardNumber = dr["CardNumber"].ToString();

info.CardPoints = dr["CardPoints"].ToString();

info.Balance = dr["Balance"].ToString();

info.CardType = dr["CardType"].ToString();

info.TotalPoints = dr["TotalPoints"].ToString();

info.UserID = Convert.ToInt32(dr["UserID"]);

return info;

}

control层:

public void ExeclDown()

{

string tablecontent = Getoutexecl();

if (!string.IsNullOrEmpty(tablecontent))

{

if (!string.IsNullOrEmpty(Request.QueryString["role"]) && Request.QueryString["role"] == "2")

{

ExportExcel("移动会员数据.xls", Getoutexecl());

}

else

{

ExportExcel("网站会员数据.xls", Getoutexecl());

}

public string Getoutexecl()

{

IList<Execlinfo> lt = iuser.execlout();

if (!string.IsNullOrEmpty(Request.QueryString["role"]) && Request.QueryString["role"] == "2")

{

lt = lt.Where(c => c.Signature != "网站会员" &&UserUtils.GetUserInfo(c.UserID).ContainsRole(300)).ToList();

//list = list.Where(c => c.ContainsRole(100)).ToList();

}

else

{

lt = lt.Where(c => c.Signature == "网站会员" && UserUtils.GetUserInfo(c.UserID).ContainsRole(100)).ToList();

}

StringBuilder builder = new StringBuilder();

builder.Append("<table border='1'>");

builder.Append("<tr>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>UserName </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>Password </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>PasswordSalt </th>");

builder.Append("<th style='mso-number-format:'\\@';text-align:center;background:#F3F3F3;'>PasswordFormat </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>AccountStatus </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>TrueName </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>Phone </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>Mobile </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>Email </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>Address </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>SN </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>CompanyName </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>Gender </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>Zip </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>Position </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>Sequence </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>Description </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>Permission </th>");

builder.Append("<th style='mso-number-format:'\\@';text-align:center;background:#F3F3F3;'>NickName </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>BirthDay </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>RegisterSite </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>ProvinceID </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>CityID </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>Subscription </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>Signature </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>CardPoints </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>Balance </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>CardType </th>");

builder.Append("<th style='text-align:center;background:#F3F3F3;'>TotalPoints </th>");

builder.Append("</tr>");

foreach (Execlinfo t in lt)

{

builder.Append("<td style='text-align:center;'>" + t.UserName + "</td>");

builder.Append("<td style='text-align:center;'>" + t.Password + "</td>");

builder.Append("<td style='mso-number-format:\\@;text-align:center;'>" + t.PasswordSalt + "</td>");

builder.Append("<td style='text-align:center;'>" + t.PasswordFormat + "</td>");

builder.Append("<td style='text-align:center;'>" + t.AccountStatus + "</td>");

builder.Append("<td style='text-align:center;'>" + t.TrueName + "</td>");

builder.Append("<td style='text-align:center;'>" + t.Phone + "</td>");

builder.Append("<td style='text-align:center;'>" + t.Mobile + "</td>");

builder.Append("<td style='text-align:center;'>" + t.Email + "</td>");

builder.Append("<td style='text-align:center;'>" + t.Address + "</td>");

builder.Append("<td style='text-align:center;'>" + t.SN + "</td>");

builder.Append("<td style='text-align:center;'>" + t.CompanyName + "</td>");

builder.Append("<td style='text-align:center;'>" + t.Gender + "</td>");

builder.Append("<td style='text-align:center;'>" + t.Zip + "</td>");

builder.Append("<td style='text-align:center;'>" + t.Position + "</td>");

builder.Append("<td style='text-align:center;'>" + t.Sequence + "</td>");

builder.Append("<td style='text-align:center;'>" + t.Description + "</td>");

builder.Append("<td style='mso-number-format:\\@;text-align:center;'>" + t.Permission + "</td>");

builder.Append("<td style='text-align:center;'>" + t.NickName + "</td>");

builder.Append("<td style='text-align:center;'>" + t.BirthDay.ToString("yyyy-MM-dd") + "</td>");

builder.Append("<td style='text-align:center;'>" + t.RegisterSite + "</td>");

builder.Append("<td style='text-align:center;'>" + t.ProvinceID + "</td>");

builder.Append("<td style='text-align:center;'>" + t.CityID + "</td>");

builder.Append("<td style='text-align:center;'>" + t.Subscription + "</td>");

builder.Append("<td style='text-align:center;'>" + t.Signature + "</td>");

builder.Append("<td style='text-align:center;'>" + t.CardPoints + "</td>");

builder.Append("<td style='text-align:center;'>" + t.Balance + "</td>");

builder.Append("<td style='text-align:center;'>" + t.CardType + "</td>");

builder.Append("<td style='text-align:center;'>" + t.TotalPoints + "</td>");

builder.Append("</tr>");

}

builder.Append("</table>");

return builder.ToString();

}

public void ExportExcel(string FileName, string tableContent)

{

//byte[] byteArray = Encoding.UTF8.GetBytes(tableContent);

//byteArray = Encoding.Convert(Encoding.UTF8, Encoding.GetEncoding("gb2312"), byteArray);

byte[] byteArray = Encoding.GetEncoding("gb2312").GetBytes(tableContent);

System.Web.HttpContext.Current.Response.Clear();

System.Web.HttpContext.Current.Response.ClearContent();

System.Web.HttpContext.Current.Response.ClearHeaders();

System.Web.HttpContext.Current.Response.Charset = "utf-8";

HttpContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");

string userAgent = System.Web.HttpContext.Current.Request.ServerVariables["http_user_agent"].ToLower();

if (userAgent.Contains("firefox"))

{

System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName);

}

else

{

System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpContext.Current.Server.UrlEncode(FileName));

}

System.Web.HttpContext.Current.Response.AddHeader("Content-Length", byteArray.Length.ToString());

System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";

System.Web.HttpContext.Current.Response.BinaryWrite(byteArray);

System.Web.HttpContext.Current.Response.Flush();

System.Web.HttpContext.Current.Response.Close();

System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest();

}

方法二：

public void ExportToExcelOfficeALLByAdditional()

{

SearchAdditionalRun search;

string excelstr = "";

string name = Request.QueryString["name"];

string date = Request.QueryString["date"];

if (date == "ALL")

{

search = new SearchAdditionalRun

{

SearchType = SearchTypeEnum.Total,

Status = StatusEnum.Active,

};

}

else

{

search = new SearchAdditionalRun

{

ActualDate = date.ToInteger(),

SearchType = SearchTypeEnum.Total,

Status = StatusEnum.Active,

};

}

IList<AdditionalRunInfo> info = iadditionalrun.Search(search);

#region AdditionalRunInfo导出字段

string strNull = "";

excelstr = "AdditionalRunID" + "," +

"RequestDescription" + "," +

"ReasonID" + "," +

"Reason" + "," +

"DateRaised" + "," +

"ExpectedDate" + "," +

"ActualDate" + "," +

"ProjectID" + "," +

"Project #" + "," +

"ProjectName" + "," +

"InsertTime" + "," +

"LastChanged" + "," +

"Status" + "\n";//标题

#endregion

foreach (AdditionalRunInfo item in info)

{

#region AdditionalRunInfo导出字段

string DateRaised = item.DateRaised == DateTime.MinValue ? "" : item.DateRaised.ToString("yyyy-MM-dd");

string ExpectedDate = item.ExpectedDate == DateTime.MinValue ? "" : item.ExpectedDate.ToString("yyyy-MM-dd");

string InsertTime = item.InsertTime == DateTime.MinValue ? "" : item.InsertTime.ToString("yyyy-MM-dd");

string LastChanged = item.LastChanged == DateTime.MinValue ? "" : item.LastChanged.ToString("yyyy-MM-dd");

string ProjectNumber = ibkfm.GetBKFM(item.ProjectID).ProjectNumber;

string ProjectName = ibkfm.GetBKFM(item.ProjectID).ProjectName;

string Reason = isimplecategory.GetSimpleCategoryInfo(item.ReasonID).GetLangInfo(LanguageTypeEnum.Chinese).CategoryName;

excelstr += item.AdditionalRunID + "," +

StringFormat(item.RequestDescription) + "," +

item.ReasonID + "," +

Reason + "," +

DateRaised + "," +

ExpectedDate + "," +

date + "," +

item.ProjectID + "," +

ProjectNumber + "," +

ProjectName + "," +

InsertTime + "," +

LastChanged + "," +

item.Status + "\n";//标题

#endregion

}

string url = Server.MapPath("~\\fileupload\\") + name + ".csv";//要存储的路径

string path = "/fileupload/" + name + ".csv";//导出文件的相对路径，用于下载

StreamWriter sw = new StreamWriter(url, false, Encoding.GetEncoding("GB2312"));//把文件作为文件流保存在固定的路径

sw.Write(excelstr);// 文件流写出

sw.Flush();//判断是否有文件，如果有则删除

sw.Dispose();//释放流的资源

Response.Write("" + path + "");//输出文件相对路径

}