.Net Excel操作之NPOI（二）常用操作封装

一、Excel数据导出常用操作

1.指定表头和描述

2.指定数据库中读出的数据集合

二、ExcelExport封装

/// <summary>
/// Excel常用的表格导出逻辑封装
/// 单表写入
/// </summary>
public class ExcelExport
{
    /// <summary>
    /// 导出的Excel文件名称+路径
    /// </summary>
    public string FullName { get; set; }
    /// <summary>
    /// 导出的字段名称和描述
    /// </summary>
    public Dictionary<string, string> Fields { get; set; }

    private HSSFWorkbook _workbook = null;
    private ISheet _sheet = null;
    /// <summary>
    /// 创建实例，验证导出文件名
    /// </summary>
    /// <param name="FullName"></param>
    /// <param name="Fields"></param>
    public ExcelExport(string FullName, Dictionary<string, string> Fields)
    {
        this.FullName = FullName;
        this.Fields = Fields;
        Check();
        _workbook = new HSSFWorkbook();
        _sheet = _workbook.CreateSheet("Sheet1");
    }
    /// <summary>
    /// 验证Excel文件名
    /// </summary>
    private void Check()
    {
        try
        {
            FileInfo info = new FileInfo(this.FullName);
            string[] extentions = new string[] {
                ".xls",
                ".xlsx"
            };
            if (extentions.Any(q => q == info.Extension) == false)
                throw new Exception("excel文件的扩展名不正确，应该为xls或xlsx");
            if (info.Exists == false)
                info.Create().Close();
        }
        catch (Exception ex)
        {
            throw new Exception("创建Excel文件失败", ex);
        }
    }

    /// <summary>
    /// 执行导出操作
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="list"></param>
    public void Export<T>(List<T> list)
    {
        //写入表格头
        WriteHead();
        //写入数据
        ICellStyle cellStyle = _workbook.CreateCellStyle();
        cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
        cellStyle.BorderBottom = BorderStyle.Thin;
        cellStyle.BorderLeft = BorderStyle.Thin;
        cellStyle.BorderRight = BorderStyle.Thin;
        cellStyle.BorderTop = BorderStyle.Thin;
        cellStyle.VerticalAlignment = VerticalAlignment.Center;
        cellStyle.Alignment = HorizontalAlignment.Center;

        IFont cellFont = _workbook.CreateFont();
        cellFont.Boldweight = (short)FontBoldWeight.Normal;
        cellStyle.SetFont(cellFont);

        //建立行内容，从1开始
        int rowInex = 1;

        foreach (var rowItem in list)
        {
            //创建行
            IRow row = _sheet.CreateRow(rowInex);
            row.HeightInPoints = 25;

            int cellIndex = 0;
            foreach (var cellItem in this.Fields)
            {
                //创建单元格
                ICell cell = row.CreateCell(cellIndex);
                //反射获取属性的值
                PropertyInfo info = rowItem.GetType().GetProperty(cellItem.Key);
                if (info == null)
                {
                    cell.SetCellValue($"'{cellItem.Key}'属性不存在");
                }
                else
                {
                    object value = info.GetValue(rowItem);
                    if (value != null)
                        cell.SetCellValue(value.ToString());
                }
                cell.CellStyle = cellStyle;
                cellIndex++;
            }
            //进入下一次循环
            rowInex++;
        }

        //自适应列宽度
        for (int i = 0; i < this.Fields.Count; i++)
        {
            _sheet.AutoSizeColumn(i);
        }

        //导出到文件
        WriteFile();
    }
    /// <summary>
    /// 写入表头
    /// </summary>
    private void WriteHead()
    {
        //设置表头样式
        ICellStyle headStyle = _workbook.CreateCellStyle();
        headStyle.BorderBottom = BorderStyle.Thin;
        headStyle.BorderLeft = BorderStyle.Thin;
        headStyle.BorderRight = BorderStyle.Thin;
        headStyle.BorderRight = BorderStyle.Thin;
        headStyle.Alignment = HorizontalAlignment.Center;
        headStyle.FillForegroundColor = HSSFColor.Blue.Index;
        headStyle.VerticalAlignment = VerticalAlignment.Center;

        IFont headFont = _workbook.CreateFont();
        headFont.Boldweight = (short)FontBoldWeight.Bold;
        headStyle.SetFont(headFont);

        IRow row = _sheet.CreateRow(0);
        row.HeightInPoints = 30;

        int index = 0;
        foreach (var item in this.Fields)
        {
            ICell cell = row.CreateCell(index);
            cell.SetCellValue(item.Value);
            cell.CellStyle = headStyle;
            index++;
        }
    }
    /// <summary>
    /// 创建文件到磁盘
    /// </summary>
    private void WriteFile()
    {
        using (FileStream fs = new FileStream(this.FullName, FileMode.OpenOrCreate))
        {
            _workbook.Write(fs);
            fs.Flush();
            fs.Close();
        }
    }
}

三、使用示例

1.匿名对象集合导出

Dictionary<string, string> fields = new Dictionary<string, string>();
fields.Add("ID", "主键");
fields.Add("Name", "姓名");
fields.Add("Age", "年龄");
fields.Add("Birthday", "生日");
ExcelExport _export = new ExcelExport(LocalPathHelper.GetCurrentData() + "/export1.xls", fields);


List<object> list = new List<object>() {
    new {ID=1,Name="张三丰",Age=20,Birthday=DateTime.Now },
    new {ID=2,Name="王芳",Age=30,Birthday=DateTime.Now }
};
_export.Export(list);

2.List集合导出

TestOne _Context = new DBA.TestOne();
List<Member_Info> list = _Context.Member_Info.ToList();
Dictionary<string, string> fields = new Dictionary<string, string>();
fields.Add("MemberID", "主键");
fields.Add("code", "账号");
fields.Add("RealName", "姓名");
fields.Add("IsActive", "是否激活");
fields.Add("commission", "奖金余额");

//使用
ExcelExport _export = new ExcelExport(LocalPathHelper.GetCurrentData() + "\\export2.xls", fields);
//_export.Export(list);
_export.Export<Member_Info>(list);