c# 导入导出excel方法封装

最新推荐文章于 2023-05-11 16:16:52 发布

weixin_30361641

最新推荐文章于 2023-05-11 16:16:52 发布

阅读量313

点赞数

文章标签： c#

原文链接：http://www.cnblogs.com/kekelele/p/9417816.html

版权

在很多项目中，都会使用到文件的上传下载等，为了方便，封装了一个帮助类，每次直接拿过来使用就可以了，下面是封装的类和使用方法。

using Common.AttributeHelper;

using NPOI.HSSF.UserModel;

using NPOI.SS.UserModel;

using NPOI.SS.Util;

using NPOI.XSSF.UserModel;

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.IO;

using System.Linq;

using System.Reflection;

using System.Text;

using System.Threading.Tasks;

using System.Web;

namespace Web.CommonHelper

{

/// <summary>

/// 导出excel帮助类

/// </summary>

/// <typeparam name="T"></typeparam>

public class ExcelHelper<T>

{

/// <summary>

/// 属性描述与类集合缓存

/// </summary>

private static Dictionary<Type, List<string>> _propertyNameDic;

/// <summary>

/// 属性与类集合缓存

/// </summary>

private static Dictionary<Type, PropertyInfo[]> _propertyDic;

/// <summary>

/// 属性与描述对应关系集合缓存

/// </summary>

public static Dictionary<Type, List<DisplayMappProperty>> _displayMappProperty;

private string fileName = null; //文件名

private HSSFWorkbook workbook = null;

private XSSFWorkbook xworkbook = null;

private FileStream fs = null;

private bool disposed;

public ExcelHelper()

{

}

/// <summary>

/// 有参构造函数导入EXCEL 时使用

/// </summary>

/// <param name="fileName"></param>

public ExcelHelper(string fileName)

{

this.fileName = fileName;

disposed = false;

}

/// <summary>

/// 将excel中的数据导入到实体集合中

/// 使用说明：1.传入的实体属性中displayName描述对应excel中的列名

/// </summary>

/// <param name="file">文件</param>

/// <param name="sheetName">sheet名称</param>

/// <param name="firstRow">起始第一行（excel列名开始行）</param>

/// <returns>返回的指定的实体集合</returns>

public List<T> ExcelToModel<T>(HttpPostedFileBase file, string sheetName = null, int firstRow = 0) where T : new()

{

var models = new List<T>();

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<T>(sheet, models, firstRow);

}

else

{

return null;

}

return models;

}

catch (Exception ex)

{

Console.WriteLine("Exception: " + ex.Message);

return null;

}

/// <summary>

/// 保存文件到本地，并且数据转化为实体

/// </summary>

/// <param name="file">文件</param>

/// <param name="savePath">文件保存路径</param>

/// <param name="sheetName">sheet名称</param>

/// <param name="firstRow">起始第一行（excel列名开始行）</param>

/// <returns></returns>

public List<T> ExcelToModelAndSave<T>(HttpPostedFileBase file, string savePath, string sheetName = null, int firstRow = 0) where T : new()

{

var models = new List<T>();

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<T>(sheet, models, firstRow);

}

else

{

return null;

}

file.SaveAs(savePath);

return models;

}

catch (Exception ex)

{

Console.WriteLine("Exception: " + ex.Message);

return null;

}

/// <summary>

/// 导出Excel

/// 使用说明：1.实体中属性的displayName为导出excel中对应的列名，如果没有则按照属性名称

/// 2.实体的属性最好都是字符串或者数字类型的，在展示过程中，不会进行数据转化

/// </summary>

/// <param name="models">需要导出的实体模型（实体的属性最好都是字符串或者数字类型的，在展示过程中，不会进行数据转化）</param>

/// <param name="Response">请求上下文</param>

/// <param name="fileName">导出的文件名（默认时间）</param>

/// <param name="remark">文件头备注</param>

/// <param name="addUp">文件底部备注（eg:统计数据的添加）</param>

public void Export(List<T> models, HttpResponseBase Response, string fileName, string remark, string addUp)

{

//首先获取excel中的列名

Type type = typeof(T);

List<string> 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);

}

/// <summary>

/// 导出文件到浏览器

/// </summary>

/// <param name="workbook"></param>

/// <param name="Response"></param>

/// <param name="fileName">文件名称</param>

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();

}

/// <summary>

/// 根据类型获取实体的描述集合

/// </summary>

/// <param name="type">类型</param>

private List<string> GetDisplayNames(Type type)

{

List<string> propertyNames = new List<string>();

if (_propertyNameDic != null && _propertyNameDic.ContainsKey(type))

{

propertyNames = _propertyNameDic[type];

}

else

{

var properties = type.GetProperties();

var propertyResult = new List<PropertyInfo>();

propertyNames = GetDisplayNames(properties, out propertyResult);

//添加到缓存

if (_propertyNameDic == null)

{

_propertyNameDic = new Dictionary<Type, List<string>>();

}

if (_propertyDic == null)

{

_propertyDic = new Dictionary<Type, PropertyInfo[]>();

}

_propertyNameDic.Add(type, propertyNames);

_propertyDic.Add(type, propertyResult.ToArray());

}

return propertyNames;

}

/// <summary>

/// 获取属性描述对应关系

/// </summary>

/// <param name="type">类型</param>

public List<DisplayMappProperty> GetMapping(Type type)

{

List<DisplayMappProperty> mapping = new List<DisplayMappProperty>();

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<DisplayMappProperty>>();

}

if (_propertyDic == null)

{

_propertyDic = new Dictionary<Type, PropertyInfo[]>();

}

_displayMappProperty.Add(type, mapping);

_propertyDic.Add(type, properties);

}

return mapping;

}

/// <summary>

/// 获取实体的描述集合

/// </summary>

/// <param name="propertyInfos">实体属性组</param>

private List<string> GetDisplayNames(PropertyInfo[] propertyInfos, out List<PropertyInfo> propertyInfoList)

{

List<string> propertyNames = new List<string>();

propertyInfoList = new List<PropertyInfo>();

if (propertyInfos != null)

{

for (var i = 0; i < propertyInfos.Length; i++)

{

//判断是否是不需要导出的字段

var expoertAttribute = propertyInfos[i].GetCustomAttribute<ExportAttribute>();

if (expoertAttribute == null || expoertAttribute.NeedExport)

{

var propertyName = propertyInfos[i].GetCustomAttribute<DisplayNameAttribute>();

if (propertyName != null && !string.IsNullOrEmpty(propertyName.DisplayName))

{

propertyNames.Add(propertyName.DisplayName);

}

else

{

propertyNames.Add(propertyInfos[i].Name);

}

propertyInfoList.Add(propertyInfos[i]);

}

return propertyNames;

}

/// <summary>

/// 获取实体中的属性和描述对应关系数据

/// </summary>

/// <param name="propertyInfos">属性集合</param>

private List<DisplayMappProperty> GetMapping(PropertyInfo[] propertyInfos)

{

List<DisplayMappProperty> mapping = new List<DisplayMappProperty>();

if (propertyInfos != null)

{

for (var i = 0; i < propertyInfos.Length; i++)

{

var propertyName = propertyInfos[i].GetCustomAttribute<DisplayNameAttribute>();

if (propertyName != null && !string.IsNullOrEmpty(propertyName.DisplayName))

{

mapping.Add(new DisplayMappProperty { Property = propertyInfos[i], DisplayName = propertyName.DisplayName });

}

return mapping;

}

/// <summary>

/// 获取excel中映射的实体数据

/// </summary>

/// <typeparam name="T"></typeparam>

/// <param name="sheet"></param>

/// <param name="models"></param>

/// <param name="firstRowNum"></param>

/// <returns></returns>

private List<T> GetModel<T>(ISheet sheet, List<T> models, int firstRowNum = 0) where T : new()

{

//首先获取excel中的列名

Type type = typeof(T);

List<DisplayMappProperty> 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;

}

/// <summary>

/// 将excel转化为DataTable

/// </summary>

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;

}

/// <summary>

/// 将excel中的数据导入到实体中

/// </summary>

/// <param name="sheetName">excel工作薄sheet的名称</param>

/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>

/// <returns>返回的DataTable</returns>

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;

}

/// <summary>

/// 属性描述对应关系

/// </summary>

public class DisplayMappProperty

{

/// <summary>

/// 属性

/// </summary>

public PropertyInfo Property { set; get; }

/// <summary>

/// 属性描述描述对应excel中的列名

/// </summary>

public string DisplayName { set; get; }

}

下面是调用方法实例

首先是导入

[HttpPost]

public ActionResult Import(HttpPostedFileBase importFile)

{

ExcelHelper<AgentImport> important = new ExcelHelper<AgentImport>();

var agentInfos = important.ExcelToModel<AgentImport>(importFile, null, 1);

//获取到实例类，具体判断具体分析

}

然后是导出其中exportModels是需要导出的数据集合

new ExcelHelper<ShowActivityShareViewModel>().Export(exportModels, Response, "活动分润", null, null);

转载于:https://www.cnblogs.com/kekelele/p/9417816.html

weixin_30361641

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
c# 导入导出excel方法封装

在很多项目中，都会使用到文件的上传下载等，为了方便，封装了一个帮助类，每次直接拿过来使用就可以了，下面是封装的类和使用方法。using Common.AttributeHelper;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;using NPOI.SS.Util;using NPOI.XSSF.UserModel...
复制链接

扫一扫