public class ExportAttribute : Attribute
{
public ExportAttribute(bool needExport = true)
{
_NeedExport = needExport;
}
private bool _NeedExport { set; get; }
///
/// 是否需要导出的字段
///
public bool NeedExport
{
get { return _NeedExport; }
set { _NeedExport = value; }
}
}
///
/// 导出excel帮助类
///
///
public class ExcelHelper
{
///
/// 属性描述与类集合缓存
///
private static Dictionary<Type, List> _propertyNameDic;
///
/// 属性与类集合缓存
///
private static Dictionary<Type, PropertyInfo[]> _propertyDic;
///
/// 属性与描述对应关系集合缓存
///
public static Dictionary<Type, List> _displayMappProperty;
private string fileName = null; //文件名
private HSSFWorkbook workbook = null;
private XSSFWorkbook xworkbook = null;
private FileStream fs = null;
private bool disposed;
public ExcelHelper()
{
}
///
/// 有参构造函数 导入EXCEL 时使用
///
///
public ExcelHelper(string fileName)
{
this.fileName = fileName;
disposed = false;
}
///
/// 将excel中的数据导入到实体集合中
/// 使用说明：1.传入的实体属性中displayName描述对应excel中的列名
///
///文件
///sheet名称
///起始第一行（excel列名开始行）
///返回的指定的实体集合
public List ExcelToModel(HttpPostedFileBase file, string sheetName = null, int firstRow = 0) where T : new()
{
var models = new List();
DataTable data = new DataTable();
try
{
using (Stream fs = file.InputStream)
{
fileName = file.FileName;
var fileNameArray = fileName.Split('.');
if (fileNameArray.Length == 2)
{
ISheet sheet = null;
if (fileNameArray[1].ToLower().Trim() == "xls")
{
workbook = new HSSFWorkbook(fs);
if (sheetName != null)
{
sheet = xworkbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = xworkbook.GetSheetAt(0);
}
}
else
{
sheet = xworkbook.GetSheetAt(0);
}
}
else
{
xworkbook = new XSSFWorkbook(fs);
if (sheetName != null)
{
sheet = xworkbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = xworkbook.GetSheetAt(0);
}
}
else
{
sheet = xworkbook.GetSheetAt(0);
}
}
models = GetModel(sheet, models, firstRow);
}
else
{
return null;
}
}
return models;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return null;
}
}
///
/// 保存文件到本地，并且数据转化为实体
///
///文件
///文件保存路径
///sheet名称
///起始第一行（excel列名开始行）
///
public List ExcelToModelAndSave(HttpPostedFileBase file, string savePath, string sheetName = null, int firstRow = 0) where T : new()
{
var models = new List();
DataTable data = new DataTable();
if (!Directory.Exists(savePath))
{
Directory.CreateDirectory(savePath);
}
try
{
using (Stream fs = file.InputStream)
{
fileName = file.FileName;
var fileNameArray = fileName.Split('.');
if (fileNameArray.Length == 2)
{
ISheet sheet = null;
if (fileNameArray[1].ToLower().Trim() == "xls")
{
savePath = $"{savePath}\{DateTime.Now.ToString("dd-HH-mm-ss")}.xls";
workbook = new HSSFWorkbook(fs);
if (sheetName != null)
{
sheet = xworkbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = xworkbook.GetSheetAt(0);
}
}
else
{
sheet = xworkbook.GetSheetAt(0);
}
}
else
{
savePath = $"{savePath}\{DateTime.Now.ToString("dd-HH-mm-ss")}.xlsx";
xworkbook = new XSSFWorkbook(fs);
if (sheetName != null)
{
sheet = xworkbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = xworkbook.GetSheetAt(0);
}
}
else
{
sheet = xworkbook.GetSheetAt(0);
}
}
models = GetModel(sheet, models, firstRow);
}
else
{
return null;
}
}
file.SaveAs(savePath);
return models;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return null;
}
}
///
/// 导出Excel
/// 使用说明：1.实体中属性的displayName为导出excel中对应的列名，如果没有则按照属性名称
/// 2.实体的属性最好都是字符串或者数字类型的，在展示过程中，不会进行数据转化
///
///需要导出的实体模型（实体的属性最好都是字符串或者数字类型的，在展示过程中，不会进行数据转化）
///请求上下文
///导出的文件名（默认时间）
///文件头备注
///文件底部备注（eg:统计数据的添加）
public void Export(List models, HttpResponseBase Response, string fileName, string remark, string addUp)
{
//首先获取excel中的列名
Type type = typeof(T);
List propertyNames = GetDisplayNames(type);
//Create a new workbook
var workbook = new XSSFWorkbook();
//create a new sheet
var sheet = workbook.CreateSheet("User Accounts");
// Add header labels
var rowIndex = 0;
var rowLength = 0;
//存储文件头备注
if (!string.IsNullOrEmpty(remark))
{
var rowRemark = sheet.CreateRow(rowIndex);
rowRemark.CreateCell(rowIndex).SetCellValue(remark);
//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, propertyNames.Count - 1));
rowIndex++;
}
var row = sheet.CreateRow(rowIndex);
//存储列名
foreach (var propertyName in propertyNames)
{
row.CreateCell(rowLength).SetCellValue(propertyName);
rowLength++;
}
//存储值
var propertieValues = _propertyDic[type];
foreach (var model in models)
{
rowIndex++;
row = sheet.CreateRow(rowIndex);
for (var m = 0; m < rowLength; m++)
{
var value = propertieValues[m].GetValue(model, null);
row.CreateCell(m).SetCellValue(value?.ToString());
}
}
//存储文件尾备注（EG:统计数据）
if (!string.IsNullOrEmpty(addUp))
{
row = sheet.CreateRow(rowIndex + 1);
row.CreateCell(0).SetCellValue(addUp);
//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(rowIndex + 1, rowIndex + 2, 0, propertyNames.Count - 1));
}
fileName = $"{fileName}{DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss")}";
ExportExcel(workbook, Response, fileName);
}
///
/// 导出文件到浏览器
///
///
///
///文件名称
private void ExportExcel(XSSFWorkbook workbook, HttpResponseBase Response, string fileName)
{
using (var exportData = new MemoryStream())
{
workbook.Write(exportData);
Response.Buffer = true;
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
//response.ContentType = "application/ms-excel";
Response.ContentType = "application/vnd.openxmlformats - officedocument.spreadsheetml.sheet";
Response.AppendHeader("Content-Type", "text/html; charset=GB2312");
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", fileName));
Response.Charset = "GB2312";
Response.ContentEncoding = Encoding.GetEncoding("GB2312");
Response.BinaryWrite(exportData.GetBuffer());
Response.Flush();
}
}
///
/// 根据类型获取实体的描述集合
///
///类型
private List GetDisplayNames(Type type)
{
List propertyNames = new List();
if (_propertyNameDic != null && _propertyNameDic.ContainsKey(type))
{
propertyNames = _propertyNameDic[type];
}
else
{
var properties = type.GetProperties();
var propertyResult = new List();
propertyNames = GetDisplayNames(properties, out propertyResult);
//添加到缓存
if (_propertyNameDic == null)
{
_propertyNameDic = new Dictionary<Type, List>();
}
if (_propertyDic == null)
{
_propertyDic = new Dictionary<Type, PropertyInfo[]>();
}
_propertyNameDic.Add(type, propertyNames);
_propertyDic.Add(type, propertyResult.ToArray());
}
return propertyNames;
}
///
/// 获取属性描述对应关系
///
///类型
public List GetMapping(Type type)
{
List mapping = new List();
if (_displayMappProperty != null && _displayMappProperty.ContainsKey(type))
{
mapping = _displayMappProperty[type];
}
else
{
var properties = type.GetProperties();
mapping = GetMapping(properties);
//添加到缓存
if (_displayMappProperty == null)
{
_displayMappProperty = new Dictionary<Type, List>();
}
if (_propertyDic == null)
{
_propertyDic = new Dictionary<Type, PropertyInfo[]>();
}
_displayMappProperty.Add(type, mapping);
_propertyDic.Add(type, properties);
}
return mapping;
}
///
/// 获取实体的描述集合
///
///实体属性组
private List GetDisplayNames(PropertyInfo[] propertyInfos, out List propertyInfoList)
{
List propertyNames = new List();
propertyInfoList = new List();
if (propertyInfos != null)
{
for (var i = 0; i < propertyInfos.Length; i++)
{
//判断是否是不需要导出的字段
var expoertAttribute = propertyInfos[i].GetCustomAttribute();
if (expoertAttribute == null || expoertAttribute.NeedExport)
{
var propertyName = propertyInfos[i].GetCustomAttribute();
if (propertyName != null && !string.IsNullOrEmpty(propertyName.DisplayName))
{
propertyNames.Add(propertyName.DisplayName);
}
else
{
propertyNames.Add(propertyInfos[i].Name);
}
propertyInfoList.Add(propertyInfos[i]);
}
}
}
return propertyNames;
}
///
/// 获取实体中的属性和描述对应关系数据
///
///属性集合
private List GetMapping(PropertyInfo[] propertyInfos)
{
List mapping = new List();
if (propertyInfos != null)
{
for (var i = 0; i < propertyInfos.Length; i++)
{
var propertyName = propertyInfos[i].GetCustomAttribute();
if (propertyName != null && !string.IsNullOrEmpty(propertyName.DisplayName))
{
mapping.Add(new DisplayMappProperty { Property = propertyInfos[i], DisplayName = propertyName.DisplayName });
}
}
}
return mapping;
}
///
/// 获取excel中映射的实体数据
///
///
///
///
///
///
private List GetModel(ISheet sheet, List models, int firstRowNum = 0) where T : new()
{
//首先获取excel中的列名
Type type = typeof(T);
List mappings = GetMapping(type);
int startRow = 0;
if (sheet != null)
{
IRow firstRow = sheet.GetRow(firstRowNum);
//一行最后一个cell的编号 即总的列数
int cellCount = firstRow.LastCellNum;
var cellValues = new string[cellCount];
//获取excel中的列名
for (int i = firstRow.FirstCellNum; i < cellCount; i++)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
cellValues[i] = cellValue;
}
}
}
//数据开始行
startRow = firstRowNum + 1;
//最后一行的标号
int rowCount = sheet.LastRowNum;
//读取数据
for (int i = startRow; i <= rowCount; i++)
{
var singT = new T();
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
for (int j = row.FirstCellNum; j < cellCount; j++)
{
//获取Excel中列名
var cellValue = "";
if (j < cellValues.Length)
{
cellValue = cellValues[j];
}
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
//给实体赋值
{
//根据列名找到对应关系的属性值
var property = mappings.FirstOrDefault(n => n.DisplayName == cellValue)?.Property;
if (property != null)
{
property.SetValue(singT, row.GetCell(j)?.ToString());
}
}
}
models.Add(singT);
}
}
return models;
}
}
///
/// 将excel转化为DataTable
///
public class ExcelHelper : IDisposable
{
private string fileName = null; //文件名
private IWorkbook workbook = null;
private FileStream fs = null;
private bool disposed;
public ExcelHelper(string fileName)
{
this.fileName = fileName;
disposed = false;
}
///
/// 将excel中的数据导入到实体中
///
///excel工作薄sheet的名称
///第一行是否是DataTable的列名
/// 返回的DataTable
public DataTable ExcelToDataTable(string sheetName = null, bool isFirstRowColumn = true)
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
dataRow[j] = row.GetCell(j).ToString();
}
data.Rows.Add(dataRow);
}
}
return data;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return null;
}
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
protected virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
if (disposing)
{
if (fs != null)
fs.Close();
}
fs = null;
disposed = true;
}
}
}
///
/// 属性描述对应关系
///
public class DisplayMappProperty
{
///
/// 属性
///
public PropertyInfo Property { set; get; }
///
/// 属性描述 描述对应excel中的列名
///
public string DisplayName { set; get; }
}
调用方法实例,首先是导入,其中ImportModel是excel对应的实体类型：
public ActionResult Import(HttpPostedFileBase importFile)
{
ExcelHelper important = new ExcelHelper();
var agentInfos = important.ExcelToModel(importFile, null, 1);
}
导出实例，其中exportModels是需要导出的数据集合：
new ExcelHelper.Export(exportModels, Response, "活动分润", null,null);