EPPlus实战篇——Excel写入

.net core 项目

可以向excel写入任何类型(T)的数据，只要T中的field的[Display(Name = "1233", Description = "#,##0.00")]：name==excel column header's name   ，dicription==excel cell's formate

 

引用的nuget包：

1.EPPlus.Core

2. System.ComponentModel.Annotations

 

 

操作类：

?
1
2
3
4
5
6
7
8
9
10
public class ExcelWriteReadAccordingDisplayService<T> : IExcelWriteService<T> where T : class
{
    ILogBase _logger;
    Dictionary<int, PropertyInfo> _columnIndexDicForProperInfo;
    Dictionary<int, DisplayAttribute> _columnIndexDicForDisplayAttr;
    public ExcelWriteReadAccordingDisplayService(ILogBase logBase)
    {
        _logger = logBase;
    }
}

class ExcelWriteReadAccordingDisplayService 中的方法：

 

　　main method：

复制代码
        public bool WriteData(List<T> data, string excelPath, string sheetName)
        {

            try
            {
                if (!WriteRequestCheck(excelPath))
                {
                    _logger.Warn($"WriteData Request not valid.excelPath :{excelPath},sheetName:{sheetName}");
                    return false;
                }
                if (string.IsNullOrWhiteSpace(sheetName))
                {
                    sheetName = DateTime.Now.ToString("yyyyMM");
                }

                //set sheet style 
                Func<ExcelWorksheet, Color, bool> SetHeadStyle = (targetSheet, backgroundColor) =>
                {
                    using (ExcelRange rng = targetSheet.Cells[1, 1, 1, targetSheet.Dimension.Columns])
                    {
                        rng.Style.Font.Bold = true;
                        rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        rng.Style.Fill.BackgroundColor.SetColor(backgroundColor);
                    }
                    targetSheet.Row(1).Height = targetSheet.Row(1).Height * 1.4f;
                    return true;
                };

                Func<ExcelWorksheet, bool> SetAllCellsStyle = (targetSheet) =>
                {
                    using (ExcelRange rng = targetSheet.Cells)
                    {
                        rng.AutoFitColumns();
                    }
                    return true;
                };

                using (ExcelPackage package = new ExcelPackage())
                {
                    ExcelWorksheet sheet = AddSheet(package, sheetName);

                    //write data to excel 
                    GetColumnIndexDic(sheet);
                    WriteContent(data, sheet);

                    //set style for excel
                    SetHeadStyle(sheet, Color.FromArgb(255, 242, 204));
                    SetAllCellsStyle(sheet);

                    //save
                    package.SaveAs(new FileInfo(excelPath));
                }

                return true;
            }catch(Exception ex)
            {
                _logger.Error($"ExcelWrite data exception :{ex.ToString()},excel:{excelPath},data:{JsonConvert.SerializeObject(data)}");
                throw ex;
            }
        }
复制代码

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
public byte[] WriteData(List<T> data, string sheetName)
{
    try
    {
        if (string.IsNullOrWhiteSpace(sheetName))
        {
            sheetName = DateTime.Now.ToString("yyyyMM");
        }
        if (data == null || data.Count() == 0)
        {
            _logger.Warn($"WriteData Request not valid. request.data:{JsonConvert.SerializeObject(data)}");
            return null;
        }
        return SaveData(data, sheetName);
    }
    catch (Exception ex)
    {
        _logger.Error($"ExcelWrite data exception :{ex.ToString()},sheetName:{sheetName},data:{JsonConvert.SerializeObject(data)}");
        throw ex;
    }
}
 
private bool WriteRequestCheck(string excelPath)
{
    Func<string, bool> pathValidCheck = (path) =>
    {
        if (string.IsNullOrWhiteSpace(path) || !Path.IsPathRooted(path)) return false;
        return true;
    };
 
    if (!pathValidCheck(excelPath))
    {
        _logger.Warn($"excelPath not valid,path :{excelPath}");
        return false;
    }
    return true;
}
 
private ExcelWorksheet AddSheet(ExcelPackage package, string sheetName)
{
    if (package.Workbook.Worksheets[sheetName] != null)
    {
        package.Workbook.Worksheets.Delete(sheetName);
    }
    var sheet = package.Workbook.Worksheets.Add(sheetName);
    return sheet;
}
 
 
 
 
 
 
private byte[] SaveData(List<T> data, string sheetName)
{
    byte[] excelContent = new byte[] { };
 
    using (ExcelPackage package = new ExcelPackage())
    {
        ExcelWorksheet sheet = AddSheet(package, sheetName);
 
        //write data to excel
        GetColumnIndexDic(sheet);
        WriteContent(data, sheet);
 
        //set style for excel
        SetHeadStyle(sheet, Color.FromArgb(255, 242, 204));
        SetAllCellsStyle(sheet);
 
        //save
        using (System.IO.MemoryStream outStream = new System.IO.MemoryStream())
        {
            package.SaveAs(outStream);
            excelContent = outStream.ToArray();
        }
    }
    return excelContent;
}

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
// type T => init excel's header && get some setting info to write excel cell
 private void GetColumnIndexDic(ExcelWorksheet sheet,bool writeHeader=true)
 {
     try
     {
         var typeOfObject = typeof(T);
         var pds = typeOfObject.GetProperties();
         if (pds == null)
         {
             _logger.Warn($"no PropertyInfos can get from class Type:{typeOfObject.FullName} ");
             return;
         }
 
         //Dictionary<excel column index,T's PropertyInfo>
         _columnIndexDicForProperInfo = new Dictionary<int, PropertyInfo>();
         //Dictionary<excel column index,T's PropertyInfo's DisplayAttribute(its name=excle header name,its discription =excel cell style formate)>
         _columnIndexDicForDisplayAttr = new Dictionary<int, DisplayAttribute>();
         int column = 1;
         int row = 1;
         foreach (var p in pds)
         {
             var attr = p.GetCustomAttribute(typeof(DisplayAttribute)) as DisplayAttribute;
             if (attr != null)
             {
                 _columnIndexDicForDisplayAttr.Add(column, attr);
             }
             else
             {
                 _logger.Warn($"no DisplayAttribute can get from PropertyInfo:(class:{typeOfObject.FullName},property:{p.Name})");
             }
 
             if (writeHeader)
             {
                 sheet.Cells[row, column].Value = attr == null ? p.Name : attr.Name;
             }
             _columnIndexDicForProperInfo.Add(column, p);
             column++;
         }
 
         if (_columnIndexDicForProperInfo.Count == 0)
         {
             _logger.Warn($"no _columnIndexDicForProperInfo can get from type:{typeOfObject.FullName}");
         }
         if (_columnIndexDicForDisplayAttr.Count == 0)
         {
             _logger.Warn($"no _columnIndexDicForDisplayAttr can get from type:{typeOfObject.FullName}");
         }
     }
     catch (Exception ex)
     {
         _logger.Error($"ExcelWrite-GetColumnIndexDic exception :{ex.ToString()}");
         throw ex;
     }
 }

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
//fill sheet content according list data
 private void WriteContent(List<T> data,ExcelWorksheet sheet,int startRow=2)
 {
     try
     {
         PropertyInfo propertyTemp = null;
         DisplayAttribute displayAttrTemp = null;
         int column = 1;
         int row = startRow;
         Dictionary<string, Dictionary<string, DisplayAttribute>> enumDic = new Dictionary<string, Dictionary<string, DisplayAttribute>>();
         foreach (var eachData in data)
         {
             column = 1;
             foreach (var eachColumn in _columnIndexDicForProperInfo)
             {
                 if (!_columnIndexDicForProperInfo.ContainsKey(column))
                 {
                     _logger.Warn($"no PropertyInfos can get from _columnIndexDic. current column:{column},_columnIndexDic:{JsonConvert.SerializeObject(_columnIndexDicForProperInfo)} ");
                     continue;
                 }
                 propertyTemp = _columnIndexDicForProperInfo[column];
                 var cellValue = propertyTemp.GetValue(eachData);
                  
                 if (cellValue != null)
                 {
                     FormatCellValue(ref enumDic, ref cellValue, propertyTemp.PropertyType);
                 }
                 sheet.Cells[row, column].Value = cellValue == null ? "" : cellValue;
                 if (_columnIndexDicForDisplayAttr.ContainsKey(column))
                 {
                     displayAttrTemp = _columnIndexDicForDisplayAttr[column];
                     var styleFormate = displayAttrTemp.Description;
                     if (!string.IsNullOrWhiteSpace(styleFormate))
                     {
                         sheet.Cells[row, column].Style.Numberformat.Format = styleFormate;
                     }
                 }
                 column++;
             }
             row++;
         }
     }
     catch (Exception ex)
     {
         _logger.Error($"ExcelWrite-WriteContent exception :{ex.ToString()},data:{JsonConvert.SerializeObject(data)}");
         throw ex;
     }
 }
 
 
 
// formate cell value according type T‘s property’s DisplayAttribute
 private void FormatCellValue(ref Dictionary<string, Dictionary<string, DisplayAttribute>> enumDic, ref object cellValue, Type propertyTypeOfCell)
 {
     if (cellValue == null) return;
     if (propertyTypeOfCell.IsEnum)
     {
         Dictionary<string, DisplayAttribute> enumDicTemp;
         if (enumDic.ContainsKey(propertyTypeOfCell.FullName))
         {
             enumDicTemp = enumDic[propertyTypeOfCell.FullName];
         }
         else
         {
             enumDicTemp = GetEnumNameDicForDisplayAttr(propertyTypeOfCell);
             enumDic.Add(propertyTypeOfCell.FullName, enumDicTemp);
         }
 
         if (enumDicTemp != null)
         {
             if (enumDicTemp.ContainsKey(cellValue.ToString()))
             {
                 cellValue = enumDicTemp[cellValue.ToString()].Name;
                 return;
             }
             else
             {
                 _logger.Warn($"no enum value can get from enum dictionary:{JsonConvert.SerializeObject(enumDicTemp.Keys)} , enum Type:{propertyTypeOfCell.FullName},cell value:{cellValue}");
             }
         }
         else
         {
             _logger.Warn($"no enum dictionary can get from enum Type:{propertyTypeOfCell.FullName} ");
         }
         return;
     }
     /*if (propertyTypeOfCell == typeof(int))
     {
         cellValue = Convert.ToInt32(cellValue);
         return;
     }
     if (propertyTypeOfCell == typeof(long))
     {
         cellValue = Convert.ToInt64(cellValue);
         return;
     }
     if (propertyTypeOfCell == typeof(DateTime))
     {
         cellValue = Convert.ToDateTime(cellValue);
         return;
     }
     if (propertyTypeOfCell == typeof(string))
     {
         cellValue = cellValue.ToString();
         return;
     }*/
     return;
 
 }
 
 
 
 
 // get enum property Dic<enum value,DisplayAttribute> =>show in excel cell
 private Dictionary<string, DisplayAttribute> GetEnumNameDicForDisplayAttr(Type enumClassType)
 {
     try
     {
         var result = new Dictionary<string, DisplayAttribute>();
         if (enumClassType.IsEnum)
         {
             var enumValues = enumClassType.GetEnumValues();
             foreach (var value in enumValues)
             {
                 MemberInfo memberInfo =
                     enumClassType.GetMember(value.ToString()).First();
                 var descriptionAttribute =
                     memberInfo.GetCustomAttribute<DisplayAttribute>();
                 if (descriptionAttribute != null)
                 {
                     var enumString = Enum.GetName(enumClassType, value);
                     result.Add(value.ToString(), descriptionAttribute);
                 }
             }
             if (result == null || result.Count() == 0)
             {
                 _logger.Warn($"no EnumDic can get from enum Type:{enumClassType.FullName} ");
             }
         }
         return result;
     }
     catch (Exception ex)
     {
         _logger.Error($"ExcelWrite-GetEnumNameDicForDisplayAttr exception :{ex.ToString()},Type:{enumClassType.FullName}");
         throw ex;
     }
 }

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
private bool WriteRequestCheck(string excelPath)
{
    Func<string, bool> pathValidCheck = (path) =>
    {
        if (string.IsNullOrWhiteSpace(path) || !Path.IsPathRooted(path)) return false;
        return true;
    };
 
    if (!pathValidCheck(excelPath))
    {
        _logger.Warn($"excelPath not valid,path :{excelPath}");
        return false;
    }
    return true;
}
 
private ExcelWorksheet AddSheet(ExcelPackage package, string sheetName)
{
    if (package.Workbook.Worksheets[sheetName] != null)
    {
        package.Workbook.Worksheets.Delete(sheetName);
    }
    var sheet = package.Workbook.Worksheets.Add(sheetName);
    return sheet;
}

　　

 

enum 定义：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
public enum AdvertiseType:Int32
{
    /// <summary>
    /// Search
    /// </summary>
    [Display(Name = "Search")]//important
    Search = 1,
  
    /// <summary>
    /// Display
    /// </summary>
    [Display(Name = "Display")]
    Display = 2,
}

type T 的定义

?
1
2
3
4
5
6
7
8
9
10
11
12
13
public class FinancialBillEntity
 {
     
     [Display(Name = "类型")]
     public AdvertiseType AdvertiseType{ get; set; }
 
     [Display(Name = "总金额", Description = "#,##0.00")]//name==excel header name；discription=cell style formate
     public decimal TotalAdivitisingCost{ get; set; }
 
    
     [Display(Name = "赠送", Description = "#,##0.00")]
     public decimal PromotionAmountUSD { get; set; }
}

　　

应用：

?
1
2
3
4
5
6
7
8
9
//register interface
services.RegisterServiceR<IExcelWriteService<FinancialBillEntity>, ExcelWriteReadAccordingDisplayService<FinancialBillEntity>>(lifeStyle);
  
//get interface instance
var excelWriteService= services.GetInstance<IExcelWriteService<FinancialBillEntity>>();
  
  
//execute interface method
bool result=_excelWriteService.WriteData(financeBills,cmdOptions.OutputFinanceBillExcelPath,cmdOptions.OutputFinanceBillSheetName);

　　

 

posted @   PanPan003  阅读(2433)  评论(0)    收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示