c# 导入导出excel方法封装

 



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


 
posted @ 2018-08-04 10:44  keke..lele  阅读(2177)  评论(2编辑  收藏  举报