C# 导出Excell

来源：互联网发布：苹果老虎机软件下载编辑：程序博客网时间：2024/06/09 16:02

///////////////////////////////////////////////////////////////

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;

namespace GTek.Safe.BSite.BusinessModules.CommonClass
{
public static class ExcelHelper
{
public static string GetHeaderHtml(string sheetName = null)
{
sheetName = sheetName ?? "sheeet1";
return @"<html xmlns:v='urn:schemas-microsoft-com:vml' xmlns:o='urn:schemas-microsoft-com:office:office'
xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'><head>
<meta http-equiv='Content-Type' content='text/html; charset=UTF-8'>

<style type='text/css'>
td {
border-color: black;
border-width: medium;
padding: 1px 1px 1px 1px;
font: 11pt 宋体;
vertical-align: middle;
white-space: nowrap;
}
.font6 {
color: black;
font-size: 11.0pt;
font-weight: 700;
font-style: normal;
text-decoration: none;
font-family: 宋体;
text-align: center;
}
</style>
</head>
<body>
<table cellpadding='0' cellspacing='0' style='border-collapse: collapse;'>";
}
public static byte[] GetExportHtml<T>(this IEnumerable<T> list, string sheetName = null)
{
StringBuilder sb = new StringBuilder();
#region 表头html
sb.Append(GetHeaderHtml(sheetName));
#endregion
//属性列表
var properties = typeof(T).GetProperties();

#region 列名html
sb.Append("<tr style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;'>");
foreach (var item in properties)
{
sb.AppendFormat("<td>{0}</td>", item.Name);
}
sb.Append("</tr>");
#endregion

#region 内容html
// rowspan='1' colspan='1'
if (list != null)
{
foreach (var item in list)
{
sb.Append("<tr>");
foreach (var p in properties)
{
var obj = p.GetValue(item);

sb.Append(String.Format(@"<td style="" mso-number-format:'\@'; "">{0}</td>", obj));

}
sb.Append("</tr>");
}
}
sb.Append("</table></body></html>");
#endregion
return Encoding.UTF8.GetBytes(sb.ToString());
}

/// <summary>
/// 导出
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <param name="columns">列名列表</param>
/// <param name="fieds">列名对应T属性列表</param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static byte[] GetExportHtml<T>(this IEnumerable<T> list, string[] columns, string[] fieds, string sheetName = null)
{
StringBuilder sb = new StringBuilder();
#region 表头html
sb.Append(GetHeaderHtml(sheetName));
#endregion
//属性列表
var properties = typeof(T).GetProperties();

#region 列名html
sb.Append("<tr style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;'>");
foreach (var item in columns)
{
sb.AppendFormat("<td>{0}</td>", item);
}
sb.Append("</tr>");
#endregion

#region 内容html
// rowspan='1' colspan='1'
if (list != null)
{
foreach (var item in list)
{
sb.Append("<tr>");
foreach (var fied in fieds)
{
sb.Append(String.Format(@"<td style=""mso-number-format:'\@'; "">{0}</td>", item.GetPropertyValue(fied)));
}
sb.Append("</tr>");
}
}
sb.Append("</table></body></html>");
#endregion
return Encoding.UTF8.GetBytes(sb.ToString());
}

public static DataSet ToDataSet(string fileName)
{
string connectionString = string.Format("provider=Microsoft.Ace.OleDb.12.0; data source={0};Extended Properties=Excel 12.0;", fileName);

DataSet data = new DataSet();

foreach (var sheetName in GetExcelSheetNames(connectionString))
{
using (OleDbConnection con = new OleDbConnection(connectionString))
{
var dataTable = new DataTable();
string query = string.Format("SELECT * FROM [{0}]", sheetName);
con.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
adapter.Fill(dataTable);
data.Tables.Add(dataTable);
}
}
return data;
}

private static string[] GetExcelSheetNames(string connectionString)
{
OleDbConnection con = null;
DataTable dt = null;
con = new OleDbConnection(connectionString);
con.Open();
dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

if (dt == null)
{
return null;
}

String[] excelSheetNames = new String[dt.Rows.Count];
int i = 0;

foreach (DataRow row in dt.Rows)
{
excelSheetNames[i] = row["TABLE_NAME"].ToString();
i++;
}

return excelSheetNames;
}
}
}

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////

#region 导出统计列表

public ActionResult ExportStaticalList(string request)
{
Request<ComChekObjModel> bdrequest = new Request<ComChekObjModel>();
if (!string.IsNullOrEmpty(request))
{
bdrequest = JsonHelper.DeserializeJsonToObject<Request<ComChekObjModel>>(request);
}
bdrequest.PageSize = int.MaxValue;
bdrequest.PageIndex = 0;
var lists = service.CommunityStaticalList(bdrequest);
var sort = new List<ResponseCommunityStaticalModel>();
if (lists != null && lists.data.Count() > 0)
{
sort = lists.data.ToList();
}
var ds = lists.data.Select(p => new
{
序号 = sort.IndexOf(p) + 1,
社区名称 = p.OrganizationName,
居委会 = p.name

}).GetExportHtml();
string fileName = "表格名称.xls";
return File(ds, "application/ms-excel", HttpContext.Request.Browser.Browser == "IE" ? Url.Encode(fileName) : fileName);
}
#endregion

阅读全文

0 0