工具类：
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using System.Web;
namespace Exprot.Utils
{
public class ExcelExportHelper
{
public static string ExcelContentType
{
get
{
return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
}
}
/// <summary>
/// List转DataTable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data"></param>
/// <returns></returns>
public static DataTable ListToDataTable<T>(List<T> data)
{
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
DataTable dataTable = new DataTable();
for (int i = 0; i < properties.Count; i++)
{
PropertyDescriptor property = properties[i];
dataTable.Columns.Add(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType);
}
object[] values = new object[properties.Count];
foreach (T item in data)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = properties[i].GetValue(item);
}
dataTable.Rows.Add(values);
}
return dataTable;
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dataTable">数据源</param>
/// <param name="heading">工作簿Worksheet</param>
/// <param name="showSrNo">是否显示行编号</param>
/// <param name="columnsToTale">要导出的列</param>
/// <returns></returns>
public static byte[] ExportExcel (DataTable dataTable, Dictionary<string, string> keys, string heading = "", bool showSrNo = false)
{
foreach (var dic in keys)
{
Console.Write("key: {0}value: {1}\n", dic.Key, dic.Value);
}
byte[] reslut = null;
using (ExcelPackage package = new ExcelPackage())
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(heading);
int startRowFrom = string.IsNullOrEmpty(heading) ? 1 : 3;
if (showSrNo)
{
DataColumn dataColumn = dataTable.Columns.Add("#", typeof(int));
dataColumn.SetOrdinal(0);
int index = 1;
foreach (DataRow item in dataTable.Rows)
{
item[0] = index;
index++;
}
}
workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true);
int columnIndex = 1;
foreach (DataColumn item in dataTable.Columns)
{
ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row, columnIndex, workSheet.Dimension.End.Row, columnIndex];
int maxLength = columnCells.Max(cell => cell.Value == null ? 1 : cell.Value.ToString().Count());
if (maxLength < 150)
{
workSheet.Column(columnIndex).AutoFit();
}
columnIndex++;
}
foreach (var a in workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
{
foreach (var dic in keys)
{
try
{
if (a.Value.ToString().Equals(dic.Key))
{
a.Value = dic.Value;
}
}
catch (Exception ex)
{
continue;
}
}
}
using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
{
r.Style.Font.Color.SetColor(System.Drawing.Color.White);
r.Style.Font.Bold = true;
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad"));
}
using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count])
{
r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
}
for (int i = 0; i >= dataTable.Columns.Count - 1; i++)
{
if (i == 0 && showSrNo)
{
continue;
}
workSheet.DeleteRow(i + 1);
}
if (!String.IsNullOrEmpty(heading))
{
workSheet.Cells["A1"].Value = heading;
workSheet.Cells["A1"].Style.Font.Size = 20;
workSheet.InsertColumn(1, 1);
workSheet.InsertRow(1, 1);
workSheet.Column(1).Width = 5;
}
reslut = package.GetAsByteArray();
// BytesToFile(reslut, "E:\\测试文件.xlsx");
}
return reslut;
}
/// <summary>
/// 导出Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data"></param>
/// <param name="heading"></param>
/// <param name="isShowSlNo"></param>
/// <param name="ColumnsToTake"></param>
/// <returns></returns>
public static byte[] ExportExcel<T>(List<T> data, Dictionary<string, string> keys, string heading = "", bool isShowSlNo = false, params string[] ColumnsToTake)
{
return ExportExcel(ListToDataTable<T>(data), keys, heading, isShowSlNo);
}
public static string EntityListToExcel(Dictionary<string, string> cellHeader, IList enList, string sheetName)
{
try
{
string fileName = sheetName + "-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; // 文件名称
string urlPath = "UpFiles/ExcelFiles/" + fileName; // 文件下载的URL地址，供给前台下载
string filePath = HttpContext.Current.Server.MapPath("\\" + urlPath); // 文件路径
// 5.返回下载路径
return urlPath;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 保存文件 生成Url
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static string SaveExcelFile(HttpPostedFile file)
{
try
{
var fileName = file.FileName.Insert(file.FileName.LastIndexOf('.'), "-" + DateTime.Now.ToString("yyyyMMddHHmmssfff"));
var filePath = Path.Combine(HttpContext.Current.Server.MapPath("~/UpFiles/ExcelFiles"), fileName);
string directoryName = Path.GetDirectoryName(filePath);
if (!Directory.Exists(directoryName))
{
Directory.CreateDirectory(directoryName);
}
file.SaveAs(filePath);
return filePath;
}
catch
{
return string.Empty;
}
}
public static void BytesToFile(byte[] buff, string savepath)
{
if (System.IO.File.Exists(savepath))
{
System.IO.File.Delete(savepath);
}
FileStream fs = new FileStream(savepath, FileMode.CreateNew);
BinaryWriter bw = new BinaryWriter(fs);
bw.Write(buff, 0, buff.Length);
bw.Close();
fs.Close();
}
}
}
调用示例：
//进行Excel转换操作，并返回转换的文件下载链接
//subsibyexprotdtos List<T>数据源
//cellheader 获取类的属性备注
var cellheader = GetEnumName<ExprotSubsidydto>();
var bytes = ExcelExportHelper.ExportExcel(List<ExprotSubsidydto>, cellheader, "补助发放清单", true);
string fileName = "补助信息" + DateTime.Now.ToString("yyyy-MM-dd_HH:mm:ss") + ".xlsx"; // 文件名称
fileName = GB2312ToUTF8(fileName);
关联方法：
/// <summary>
/// 获取类属性备注
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static Dictionary<string, string> GetEnumName<T>() where T : new()
{
Type t = typeof(T);
Dictionary<string, string> kvs = new Dictionary<string, string>();
foreach (MemberInfo mInfo in t.GetMembers())
{
foreach (Attribute attr in Attribute.GetCustomAttributes(mInfo))
{
if (attr.GetType() == typeof(System.ComponentModel.DescriptionAttribute))
{
kvs.Add(mInfo.Name, ((DescriptionAttribute)attr).Description);
}
}
}
return kvs;
}
private string GB2312ToUTF8(string str)
{
Encoding uft8 = Encoding.GetEncoding(65001);
Encoding gb2312 = Encoding.GetEncoding("gb2312");
byte[] temp = uft8.GetBytes(str);
byte[] temp1 = Encoding.Convert(uft8, gb2312, temp);
string result = gb2312.GetString(temp1);
return result;
}
类：
public class ExprotSubsidydto
{
/// <summary>
/// 姓名
/// </summary>
[Description("姓名")]
public string userFullName { get; set; }
/// <summary>
/// 餐补
/// </summary>
[Description("餐补")]
public Double? subsidy_Food { get; set; }
/// <summary>
/// 交通补
/// </summary>
[Description("交通补")]
public Double? subsidy_Communication { get; set; }
/// <summary>
/// 补助总额
/// </summary>
[Description("补助总额")]
public Double? subsidyCash { get; set; }
}
网友评论