.Net Excel操作之NPOI（二）常用操作封装-白红宇

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

阅读量：5941 次

发布时间：2019-06-19

本文共 5400 字，大约阅读时间需要 18 分钟。

一、Excel数据导出常用操作

1.指定表头和描述

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

二、ExcelExport封装

/// /// Excel常用的表格导出逻辑封装/// 单表写入/// 
public class ExcelExport{    ///     /// 导出的Excel文件名称+路径    /// 
    public string FullName { get; set; }    ///     /// 导出的字段名称和描述    /// 
    public Dictionary
     
       Fields { get; set; }    private HSSFWorkbook _workbook = null;    private ISheet _sheet = null;    /// 
          /// 创建实例，验证导出文件名    /// 
    /// 
          /// 
          public ExcelExport(string FullName, Dictionary
      
        Fields)    {        this.FullName = FullName;        this.Fields = Fields;        Check();        _workbook = new HSSFWorkbook();        _sheet = _workbook.CreateSheet("Sheet1");    }    /// 
           /// 验证Excel文件名    /// 
    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);        }    }    /// 
           /// 执行导出操作    /// 
    /// 
           /// 
           public void Export
       
        (List
        
          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();    }    /// 
             /// 写入表头    /// 
    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++;        }    }    /// 
             /// 创建文件到磁盘    /// 
    private void WriteFile()    {        using (FileStream fs = new FileStream(this.FullName, FileMode.OpenOrCreate))        {            _workbook.Write(fs);            fs.Flush();            fs.Close();        }    }}

三、使用示例

1.匿名对象集合导出

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

2.List集合导出

TestOne _Context = new DBA.TestOne();List
     
       list = _Context.Member_Info.ToList();Dictionary
      
        fields = new Dictionary
       
        ();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
        
         (list);