C# NPOI导出excel 稍微有点通用

最新推荐文章于 2024-09-09 23:28:21 发布

weixin_34418883

最新推荐文章于 2024-09-09 23:28:21 发布

阅读量98

点赞数

文章标签： python 测试人工智能

原文链接：https://my.oschina.net/raddleoj/blog/909213

版权

2019独角兽企业重金招聘Python工程师标准>>>

通过继承节省代码


    using System;
    using System.Collections.Generic;
    using System.Diagnostics;
    using System.IO;
    using NPOI.HSSF.UserModel;
    using NPOI.SS.UserModel;
    using NPOI.XSSF.UserModel;
    using System.Linq;
    using System.Reflection;


    public interface IExportExcel<T>
    {
        string WriteToExcel(string filePath, string sheetName, List<T> sourceData);
    }


    /// <summary>
    /// 基础
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class ExportExcel<T> : IExportExcel<T>
    {
        public List<FieldMap<string, string>> FieldList { get; set; }
        public List<string> ExportFieldList { get; set; }

        public ExportExcel()
        {
            FieldList = new List<FieldMap<string, string>>();
            ExportFieldList = new List<string>();
        }

        public virtual void SetData(List<T> sourceData, ISheet sheet, ICellStyle style = null)
        {
            var rowCount = sourceData.Count - 1;
            IRow row;
            ICell cell = null;
            int cellPos = 0;
            // 标题
            row = sheet.CreateRow(0);//创建第i行//这行代码需改
            foreach (var item in FieldList)
            {
                if (this.ExportFieldList.Any((s) => s.Equals(item.Title)))//在显示列表里面的才会被输出
                {
                    cell = row.CreateCell(cellPos);
                    SetCellValue(cell, item.Title);
                    cellPos++;
                }
            }
            var itemPropList = this.GetProperties();
            for (int i = 0; i < rowCount; i++)
            {
                int ri = i + 1;
                row = sheet.CreateRow(ri);//创建第i行
                var currData = sourceData[i];
                cellPos = 0;
                foreach (var item in FieldList)
                {
                    if (this.ExportFieldList.Any((s) => s.Equals(item.Title)))
                    {
                        cell = row.CreateCell(cellPos);
                        if (string.IsNullOrWhiteSpace(item.Field))
                        {

                            SetCellValue(cell, string.Empty);
                        }
                        else
                        {
                            var currProp = itemPropList.Single((p) => p.Name.Equals(item.Field));
                            SetCellValue(cell, currProp.GetValue(currData));
                        }
                        cellPos++;
                    }
                }
                //如果要根据内容自动调整列宽，需要先setCellValue再调用
                //sheet.AutoSizeColumn();
            }
        }

        public Action<int, ICellStyle, IRow, ICell> DataSetAction { get; set; }
        public ICellStyle DateStyle { get; set; }

        public string WriteToExcel(string filePath, string sheetName, List<T> sourceData)
        {
            //创建工作薄  
            IWorkbook wb;
            string extension = System.IO.Path.GetExtension(filePath);
            //根据指定的文件格式创建对应的类
            if (extension.Equals(".xls"))
            {
                wb = new HSSFWorkbook();
            }
            else
            {
                wb = new XSSFWorkbook();
            }

            ICellStyle dateStyle = wb.CreateCellStyle();//样式
            dateStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//文字水平对齐方式
            dateStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式
                                                                                     //设置数据显示格式
            IDataFormat dataFormatCustom = wb.CreateDataFormat();
            dateStyle.DataFormat = dataFormatCustom.GetFormat("yyyy-MM-dd HH:mm:ss");
            DateStyle = dateStyle;
            //创建一个表单
            ISheet sheet = wb.CreateSheet(sheetName);
            设置列宽
            //int[] columnWidth = { 10, 10, 20, 10 };
            //for (int i = 0; i < columnWidth.Length; i++)
            //{
            //    //设置列宽度，256*字符数，因为单位是1/256个字符
            //    sheet.SetColumnWidth(i, 256 * columnWidth[i]);
            //}

            //日期可以直接传字符串，NPOI会自动识别
            //如果是DateTime类型，则要设置CellStyle.DataFormat，否则会显示为数字


            this.SetData(sourceData, sheet);

            合并单元格，如果要合并的单元格中都有数据，只会保留左上角的
            CellRangeAddress(0, 2, 0, 0)，合并0-2行，0-0列的单元格
            //CellRangeAddress region = new CellRangeAddress(0, 2, 0, 0);
            //sheet.AddMergedRegion(region);

            using (FileStream fs = File.OpenWrite(filePath))
            {
                wb.Write(fs);
            }
            return filePath;
        }

        /// <summary>
        /// 获取当前类型的 字段结构
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public virtual PropertyInfo[] GetProperties()
        {
            return typeof(T).GetProperties();
        }

        /// <summary>
        /// 设置值
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="obj"></param>
        public void SetCellValue(ICell cell, object obj)
        {
            if (obj is int)
            {
                cell.SetCellValue((int)obj);
            }
            else if (obj is double)
            {
                cell.SetCellValue((double)obj);
            }
            else if (obj is decimal)
            {
                cell.SetCellValue(Convert.ToDouble(obj));
            }
            else if (obj is IRichTextString)
            {
                cell.SetCellValue((IRichTextString)obj);
            }
            else if (obj is string)
            {
                cell.SetCellValue(obj.ToString());
            }
            else if (obj is DateTime)
            {
                cell.CellStyle = this.DateStyle;
                cell.SetCellValue((DateTime)obj);
            }
            else if (obj is bool)
            {
                cell.SetCellValue((bool)obj);
            }
            else
            {
                cell.SetCellValue(obj.ToString());
            }
        }
    }



    /// <summary>
    /// 导出 第一个方案，要建立标题和字段对照
    /// </summary>
    public class ReportExportExcel : ExportExcel<ReportItem>
    {
        public ReportExportExcel()
        {
            this.ExportFieldList = new List<string>() {
                "名称", "部门", "号", "状态", "人", "方式" };
            this.FieldList = new List<FieldMap<string, string>>()
            {
                new FieldMap<string, string>() { Title="名称", Field="Company" },
                new FieldMap<string, string>() { Title="部门", Field="DeptName" },
                new FieldMap<string, string>() { Title="号", Field="ID" },
                new FieldMap<string, string>() { Title="状态", Field="Status" },
                new FieldMap<string, string>() { Title="人", Field="" },
                new FieldMap<string, string>() { Title="方式", Field="" }
            };
        }
    }

    /// <summary>
    /// 导出 另一个方案， 只是显示字段不同，标题字段对照继承自基类 尽量最大化节省代码
    /// </summary>
    public class ReversedReportExportExcel : ReportExportExcel
    {
        public ReversedReportExportExcel()
        {
            this.ExportFieldList = new List<string>() {
                "名称", "部门" };
        }
    }


    /// <summary>
    /// 标题和字段对照
    /// </summary>
    /// <typeparam name="TF"></typeparam>
    /// <typeparam name="TT"></typeparam>
    public class FieldMap<TF, TT>
    {
        public TF Field { get; set; }
        public TT Title { get; set; }
    }
}

/// <summary>
        /// 核心方法 调用代码并 导出excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="name"></param>
        /// <param name="condition"></param>
        /// <param name="dataLoadFunc"></param>
        /// <param name="getter">某些特殊的值获取</param>
        /// <param name="exporter"></param>
        /// <returns></returns>
        private FileStreamResult PowerExportExcel<T>(string name, PCQueryCondition condition, Func<PCQueryCondition, ResultData<List<T>>> dataLoadFunc, Func<List<T>, string> getter, IExportExcel<T> exporter)
        {
            
            var result = dataLoadFunc(condition);
            // 
            var unShellResult = result.Data;
            // 构成名字
            var companyName = companyNameGetter(unShellResult);
            var startDate = condition.QueryDateStart.Value.ToString("yyyyMMdd");
            var endDate = condition.QueryDateEnd.Value.ToString("yyyyMMdd");
            var fileName = $"{companyName}_{name}_{startDate}-{endDate}";
            // 
            var mainPath = Server.MapPath("/DownLoadTemp/");
            var filePath = $"{fileName}.xlsx";
            string absoluFilePath = mainPath + filePath;
            exporter.WriteToExcel(absoluFilePath, "导出数据", unShellResult);
            // 这里随机触发一次删除临时目录文件/  大约1/3的概率
            if (new Random(DateTime.Now.Millisecond).Next(0, 9) > 6)
            {
                // 移除24小时前创建的文件
                RemoveOverLastNHourTempExcelFile();
            }
            //
            return File(new FileStream(absoluFilePath, FileMode.Open), "application/octet-stream", filePath);
        }


/// 查询输入实体样例
public class PCQueryCondition
    {
        /// <summary>
        /// 查询起始时间
        /// </summary>
        public DateTime? QueryDateStart { get; set; }

        /// <summary>
        /// 查询结束时间
        /// </summary>
        public DateTime? QueryDateEnd { get; set; }

        /// <summary>
        /// 企业id
        /// </summary>
        public string CompanyID { get; set; }

        /// <summary>
        /// 类型
        /// </summary>
        public int? OrderType { get; set; } = 0;
    }

辅助功能

/// <summary>
        /// 移除N小时前创建的临时文件
        /// </summary>
        /// <param name="hour"></param>
        private void RemoveOverLastNHourTempExcelFile(int hour = 24)
        {
            var mainPath = Server.MapPath("/DownLoadTemp/");
            var files = Directory.GetFiles(mainPath);
            if (files != null && files.Any())
            {
                foreach (var item in files)
                {
                    var currFilePath = item;
                    var createTime = System.IO.File.GetCreationTime(currFilePath);
                    if(createTime < DateTime.Now.AddHours(-hour))
                    {
                        try
                        {
                            System.IO.File.Delete(currFilePath);
                        }
                        catch (Exception ex)
                        {
                            // 无需报错，不影响进程；
                            // 可预见的错误就是删除时权限不足；
                        }
                    }
                }
            }
        }

最后来个使用样例

/// <summary>
        /// 查询国内机票报表数据
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        [System.Web.Mvc.HttpGet]
        [System.Web.Mvc.ActionName("ExportReport")]
        public ActionResult QueryReportDetail(PCStatisDayQueryCondition condition)
        {
            return PowerExportExcel<ReportItem>("A", condition, helper.QueryReportDetail, (l) => l[0].CompanyName, new ReportExportExcel());
        }
// 第一个是名字
// 第二个参数是查询条件实体
// 第三个是查询数据方法，一个输入，一个输出
// 第四个是 某些特殊处理的方法
// 第五个是具体的导出excel方法

转载于:https://my.oschina.net/raddleoj/blog/909213