222

schangxiang@126.com

2024-12-14 de11618a6c41d627acd733a6b5d818d87d291c1e

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
148
149
150
151
152
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
 
namespace Admin.NET.Core.Helper.ExcelHelper
{
    /// <summary>
    /// 基于EXCEL基类实现 .xls 格式导出
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class Excel2003Result<T> : ExcelBaseResult<T> where T : new()
 
    {
        public Excel2003Result(IList<T> entity, string fileName, bool showTitle, string title)
           : base(entity, fileName, showTitle, title)
        {
            ContentType = "application/ms-excel";
            ExtName = ".xls";
        }
 
        public override MemoryStream GetExcelStream()
        {
            MemoryStream ms = new MemoryStream();
            //获取实体属性
            PropertyInfo[] propertys = typeof(T).GetProperties();
            if (propertys.Count() == 0)
            {
                return ms;
            }
            //创建Excel对象
            IWorkbook book = new HSSFWorkbook();
            //添加一个sheet
            ISheet sheet1 = book.CreateSheet("Sheet1");
 
            var index = ShowTitle ? 1 : 0;
 
 
            //样式设置
            IFont cellfont = book.CreateFont();
            cellfont.FontHeightInPoints = 11;
            cellfont.FontName = "宋体";
            ICellStyle cellStyle = book.CreateCellStyle();
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.Alignment = HorizontalAlignment.Center;
            cellStyle.SetFont(cellfont);
 
            IRow rowColumnHead = sheet1.CreateRow(index);
            IDataFormat format = book.CreateDataFormat();
            ushort firstColumn = ushort.MaxValue, lastColumn = ushort.MinValue;  //第一列下标和最后一列下标
            //添加列头
            for (int j = 0; j < propertys.Count(); j++)
            {
                ExcelDataOptionAttribute dataOption = propertys[j].GetCustomAttribute<ExcelDataOptionAttribute>();
                if (dataOption == null)
                {
                    continue;
                }
                IFont font = book.CreateFont();
                font.FontHeightInPoints = 11;
                font.FontName = "宋体";
                ICellStyle style = book.CreateCellStyle();
                style.VerticalAlignment = VerticalAlignment.Center;
                style.Alignment = HorizontalAlignment.Center;
                style.SetFont(font);
                if (!string.IsNullOrWhiteSpace(dataOption.Formater))
                {
                    style.DataFormat = format.GetFormat(dataOption.Formater);
                }
 
                sheet1.SetDefaultColumnStyle(dataOption.ColumnIndex, style);
 
                ICell cell = rowColumnHead.CreateCell(dataOption.ColumnIndex);
                cell.SetCellValue(dataOption.DisplayName);
                firstColumn = firstColumn < dataOption.ColumnIndex ? firstColumn : dataOption.ColumnIndex;
                lastColumn = lastColumn > dataOption.ColumnIndex ? lastColumn : dataOption.ColumnIndex;
 
            }
 
            index = ShowTitle ? 2 : 1;
 
            //将各行数据显示出来
            for (int i = 0; i < Entity.Count; i++)
            {
                IRow row = sheet1.CreateRow(i + index);
 
                //循环各属性，添加列
                for (int j = 0; j < propertys.Count(); j++)
                {
                    ExcelDataOptionAttribute dataOption = propertys[j].GetCustomAttribute<ExcelDataOptionAttribute>();
                    if (dataOption == null)
                    {
                        continue;
                    }
 
                    ICell cell = row.CreateCell(dataOption.ColumnIndex);
 
                    //样式设置
                    //cell.CellStyle = cellStyle;
                    if (dataOption.ColumnWidth != 0)
                    {
                        sheet1.SetColumnWidth(dataOption.ColumnIndex, dataOption.ColumnWidth * 256);
                    }
 
                    //根据数据类型判断显示格式
                    if (propertys[j].PropertyType == typeof(int))
                    {
                        cell.SetCellValue((int)propertys[j].GetValue(Entity[i]));
                    }
                    else if (propertys[j].PropertyType == typeof(decimal) || propertys[j].PropertyType == typeof(double) || propertys[j].PropertyType == typeof(float))
                    {
                        cell.SetCellValue(Convert.ToDouble(propertys[j].GetValue(Entity[i])));
                    }
                    else
                    {
                        cell.SetCellValue(propertys[j].GetValue(Entity[i]) == null ? "" : propertys[j].GetValue(Entity[i]).ToString());
                    }
                }
            }
            //将标题合并
            if (ShowTitle)
            {
                IRow rowHead = sheet1.CreateRow(0);
                ICell cellHead = rowHead.CreateCell(firstColumn);
                cellHead.SetCellValue(Title);
 
                //样式设置
                IFont font = book.CreateFont();
                font.FontHeightInPoints = 14;
                font.IsBold = true;
 
                ICellStyle style = book.CreateCellStyle();
                style.VerticalAlignment = VerticalAlignment.Center;
                style.Alignment = HorizontalAlignment.Center;
                style.SetFont(font);
                cellHead.CellStyle = style;
 
                rowHead.HeightInPoints = 20.25f;
 
                sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, firstColumn, lastColumn));
            }
 
 
            book.Write(ms);
            ms.Seek(0, System.IO.SeekOrigin.Begin);
            return ms;
        }
    }
}