NPOI导入导出

NPOI导入导出

		/// <summary>
        /// 导出excle到指定文件处
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="list">数据集合</param>
        /// <param name="dic">字典：key为表头列值,value为对应数据上的属性名称；eg:"名称":"Name"</param>
        /// <param name="path">文件夹相对路径</param>
        /// <param name="fileName">保存的文件名</param>
        /// <returns></returns>
        public static async Task<string> SaveExcelToFile<T>(IEnumerable<T> list, Dictionary<string, string> dic, string path, string fileName = null) //where T : new()
        {
            var retpath = "";
            await Task.Run(() =>
            {
                var Ticks = DateTime.Now.Ticks;
                fileName = fileName ?? Ticks.ToString();
                var mainDir = Path.Combine(AppContext.BaseDirectory +  path);
                var mainPath = Path.Combine(mainDir, $"{fileName}.xls");
                retpath = Path.Combine(path, $"{fileName}.xls");
                //创建表格对象
                HSSFWorkbook hssfworkbook = new HSSFWorkbook();
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "NPOI Team";
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Subject = "NPOI SDK Example";
                hssfworkbook.DocumentSummaryInformation = dsi;
                hssfworkbook.SummaryInformation = si;

                //创建表格单元格
                ISheet sheet = hssfworkbook.CreateSheet("Sheet1");
                sheet.CreateRow(0);

                //添加表头
                var h = 0;
                foreach (var i in dic)
                {
                    var value = i.Key;
                    sheet.GetRow(0).CreateCell(h).SetCellValue(value);
                    h++;
                    //sheet.GetRow(0).GetCell(a).CellStyle = style;
                }

                //添加行数据
                if (list != null && list.Count() > 0)
                {
                    var row = 1;
                    Type Ts = list.ElementAt(0).GetType();
                    foreach (var item in list)
                    {
                        sheet.CreateRow(row);

                        var col = 0;
                        foreach (var i in dic)
                        {
                            var value = i.Value;
                            var obj = Ts.GetProperty(i.Value);
                            var val = "";
                            if (obj != null)
                            {
                                val = obj.GetValue(item, null).ToString();
                            }
                            //赋值操作
                            sheet.GetRow(row).CreateCell(col).SetCellValue(val);
                            col++;
                        }

                        row++;
                    }
                }

                //判断文件夹存不存在，不存在新建
                if (!Directory.Exists(mainDir))
                {
                    Directory.CreateDirectory(mainDir);
                }

                FileStream file = new FileStream(mainPath, FileMode.Create);

                hssfworkbook.Write(file);
                file.Close();
                hssfworkbook.Clear();
            });
            return retpath;
        }
 		/// <summary>
        /// npoi导出excel内存流
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="list">数据集合</param>
        /// <param name="dic">字典：key为表头列值,value为对应数据上的属性名称；eg:"名称":"Name"</param>
        /// <returns></returns>
        public static async Task<MemoryStream> ExportExcel<T>(IEnumerable<T> list, Dictionary<string, string> dic, string fileName = null) //where T : new()
        {
            return await Task.Run(() =>
            {
                var Ticks = DateTime.Now.Ticks;
                fileName = fileName ?? Ticks.ToString();
                //创建表格对象
                HSSFWorkbook hssfworkbook = new HSSFWorkbook();
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "NPOI Team";
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Subject = "NPOI SDK Example";
                hssfworkbook.DocumentSummaryInformation = dsi;
                hssfworkbook.SummaryInformation = si;

                //创建表格单元格
                ISheet sheet = hssfworkbook.CreateSheet("Sheet1");
                sheet.CreateRow(0);

                //添加表头
                var h = 0;
                foreach (var i in dic)
                {
                    var value = i.Key;
                    sheet.GetRow(0).CreateCell(h).SetCellValue(value);
                    h++;
                    //sheet.GetRow(0).GetCell(a).CellStyle = style;
                }

                //添加行数据
                if (list != null && list.Count() > 0)
                {
                    var row = 1;
                    Type Ts = list.ElementAt(0).GetType();
                    foreach (var item in list)
                    {
                        sheet.CreateRow(row);

                        var col = 0;
                        foreach (var i in dic)
                        {
                            var value = i.Value;
                            var obj = Ts.GetProperty(i.Value);
                            var val = "";
                            if (obj != null)
                            {
                                val = obj.GetValue(item, null).ToString();
                            }
                            //赋值操作
                            sheet.GetRow(row).CreateCell(col).SetCellValue(val);
                            col++;
                        }

                        row++;
                    }
                }
                MemoryStream stream = new MemoryStream();
                hssfworkbook.Write(stream);
                stream.Seek(0, SeekOrigin.Begin);
                return stream;
            });
            //return await File(stream, "application/vnd.ms-excel", $"{fileName}.xls");
        }

使用

		/// <summary>
        /// 导出
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public async Task<IActionResult> ExportReport(ListParameters model)
        {
			//列表数据
            var data = await _keyWordBaseRepository.ThroughTrainReportAsync(SellerId, model);
			//对应中文列名和属性
            Dictionary<string, string> dic = new Dictionary<string, string>();
            dic.Add("日期", "Date");
            dic.Add("点击量", "Click");
            dic.Add("点击率", "Expend");
            var ms = await FileController.ExportExcel<ThroughTrainActivityDto>(data, dic);
            return File(ms, "application/vnd.ms-excel", $"数据报告.xls");
        }