NPOI导入,导出

  1. 概述
  2. 表格导入
    1. 单元格数据的解析
    2. 类型转换
    3. 导出代码的主体部分
      1. 策略接口
      2. 其他部分代码
      3. 调用
  3. 表格导出
    1. 调用者直接接触的操作类
    2. 表格样式配置
    3. 样式解析
    4. 涉及到的枚举类型
    5. 表格建造器
    6. 表格样式解析器
    7. 调用
  4.  

1、 概述

NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。NPOI 可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写,包含了大部分EXCEL的特性(单元格样式、数据格式、公式等等)。(复制百度百科)

 

表格导入导出的操作经常会碰到,最近抽空专门封装了一下。

遵循的原则:

1. 一行数据对应一个数据实体,需提供它们二者间的映射关系。

2. 支持合并单元格的导入导出(导出数据只支持竖向合并,导入数据遇到横向合并的单元格会抛出异常),图片未支持

 

导出的复杂在于样式的控制,当解决这一问题后后续操作就很简单了,而样式解析相关的代码行数也真的是不少,因为对API的不熟悉,连写带测折腾了不少时间。

导入因为行为的多变性,要求程序员调用时将具体的读写逻辑以策略的形式传入。所以代码量相对来说要少得多。其实也是避开了一些难题,比如公式符号,数字的上标,下标的解析(尚不知道怎么解决)。

导出效果

 

2、 表格导入

2.1、 单元格数据的解析

1. 先依据单元格类的数据类型cell.CellType(枚举类型),读取其内部保存的数据,如果类型是公式的,则依据cell.CachedFormulaResultType再进行一次判别。

2. 从单元格读到数据后还再进一步完成一次类型的转换,将它转换为我们需要的类型。

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
/// <summary>
/// 扩展
/// </summary>
public static class IRowExtension
{
    /// <summary>
    /// 读取表格中某单元格的值,如果单元格是合并单元格,则竖向合并的正常返回,横向合并的抛出异常
    /// </summary>
    /// <typeparam name="A">要读取的数据类型</typeparam>
    /// <param name="row">Excel中的某行数据</param>
    /// <param name="index">行内的列下标</param>
    /// <returns></returns>
    public static A GetValue<A>(this IRow row, int index)
    {
        try
        {
            //获取单元格
            ICell cell = row.GetCell(index);
 
            //判别是否合并单元格
            if (cell.IsMergedCell)
            {
                ISheet sheet = cell.Sheet;
                for (int i = 0, length = sheet.NumMergedRegions; i < length; i++)
                {
                    var cellRange = sheet.GetMergedRegion(i);
                    if (cell.ColumnIndex >= cellRange.FirstColumn
                        && cell.ColumnIndex <= cellRange.LastColumn
                        && cell.RowIndex >= cellRange.FirstRow
                        && cell.RowIndex <= cellRange.LastRow)
                    {
                        if (cellRange.FirstColumn != cellRange.LastColumn)
                        {
                            throw new Exception("不能有横向合并的单元格");
                        }
 
                        cell = sheet.GetRow(cellRange.FirstRow).GetCell(cellRange.FirstColumn);
                    }
                }
            }
            return GetValue<A>(cell);
        }
        catch (Exception ex)
        {
            //读取单元格信息时出错
            throw new Exception("出错位置的列下标:" + index + ",报错信息:" + ex.Message);
        }
    }
 
    /// <summary>
    /// 读取表格中某单元格的值,如果单元格是合并单元格,则竖向合并的正常返回,横向合并的抛出异常
    /// </summary>
    /// <typeparam name="A">要读取的数据类型</typeparam>
    /// <param name="cell">Excel中的某单元格</param>
    /// <returns></returns>
    private static A GetValue<A>(ICell cell)
    {
        //依据单元格数据类型读取数据,并强转为目标类型
        switch (cell.CellType)
        {
            case CellType.Boolean:
                return cell.BooleanCellValue.ChangeType<A>();
            case CellType.Error:
                return ErrorEval.GetText(cell.ErrorCellValue).ChangeType<A>();
            case CellType.Numeric:
                if (DateUtil.IsCellDateFormatted(cell))
                {
                    return cell.DateCellValue.ChangeType<A>();
                }
                else
                {
                    return cell.NumericCellValue.ChangeType<A>();
                }
            case CellType.String:
                string strValue = cell.StringCellValue;
                if (string.IsNullOrEmpty(strValue))
                {
                    return default(A);
                }
                else
                {
                    return strValue.ChangeType<A>();
                }
            case CellType.Formula://公式
                switch (cell.CachedFormulaResultType)
                {
                    case CellType.Boolean:
                        return cell.BooleanCellValue.ChangeType<A>();
                    case CellType.Error:
                        return ErrorEval.GetText(cell.ErrorCellValue).ChangeType<A>();
                    case CellType.Numeric:
                        if (DateUtil.IsCellDateFormatted(cell))
                        {
                            return cell.DateCellValue.ChangeType<A>();
                        }
                        else
                        {
                            return cell.NumericCellValue.ChangeType<A>();
                        }
                    case CellType.String:
                        string str = cell.StringCellValue;
                        if (string.IsNullOrEmpty(str))
                        {
                            return default(A);
                        }
                        else
                        {
                            return str.ChangeType<A>();
                        }
                    case CellType.Unknown:
                    case CellType.Blank:
                    default:
                        return default(A);
                }
            case CellType.Unknown:
            case CellType.Blank:
            default:
                return default(A);
        }
    }
}

 

2.2、 类型转换

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
/// <summary>
/// 调用Convert.ChangeType
/// </summary>
/// <typeparam name="T">要返回的类型</typeparam>
/// <param name="token">待转换的值</param>
/// <returns>转换后的值</returns>
public static T ChangeType<T>(this object token)
{
    object result = ChangeType(token, typeof(T));
    if (result == null)
    {
        return default(T);
    }
    return (T)result;
}
 
/// <summary>
/// 把对象类型转换为指定类型
/// </summary>
/// <param name="value">要转换的值</param>
/// <param name="targetType">目标类型</param>
/// <returns> 转化后的指定类型的对象</returns>
public static object ChangeType(this object value, Type targetType)
{
    if (value == null)
    {
        return null;
    }
    if (targetType.IsNullableType())
    {
        targetType = targetType.GetUnderlyingType();
    }
    if (targetType.IsEnum)
    {
        return Enum.Parse(targetType, value.ToString());
    }
    if (targetType == typeof(Guid))
    {
        return Guid.Parse(value.ToString());
    }
    return Convert.ChangeType(value, targetType);
}

 

2.3、 导出代码的主体部分

2.3.1、 策略接口

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
/// <summary>
/// 提供每行数据的操作策略(不同的实现方式,达成不同的导入目标,存储目标不必一定是数据库)
/// 通过GetValue读取某单元格数据
/// </summary>
public interface ExcelImportHelper
{
     /// <summary>
     /// 判断本行数据是否数据库中已存在(有则会进行修改,没有则会新建)
     /// </summary>
     /// <param name="row">Excel中的某行数据</param>
     /// <returns>不存在则返回null,存在则返回该项数据</returns>
     T IsExist(IRow row);
 
    /// <summary>
    /// 新数据添加到数据库
    /// </summary>
    /// <param name="model">已完成映射的实体类实例</param>
    void Add(T model);
 
    /// <summary>
    /// 更新数据库中的已有数据
    /// </summary>
    /// <param name="model">已完成映射的实体类实例</param>
    void Update(T model);
 
    /// <summary>
    /// 提供从表格行数据映射到实体类的方法(解析失败的时候直接要抛出异常)
    /// </summary>
    /// <param name="row">Excel中的某行数据</param>
    /// <param name="model">新建的或刚从数据库中读取到的实体类实例</param>
    /// <returns>映射失败或数据为空时返回false,则不会录入数据库</returns>
    bool Map(IRow row, T model);
}

2.3.2、 其他部分代码

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
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
/// <summary>
/// 表格导入数据库
/// 也可以是导入DataTable等形式,只要提供不同的策略
/// </summary>
/// <typeparam name="T">Model的类型</typeparam>
public class ExcelImport<T> where T : new()
{
    private ExcelImportHelper helper;
 
    /// <summary>
    /// 构造方法
    /// </summary>
    /// <param name="helper">操作策略</param>
    public ExcelImport(ExcelImportHelper helper)
    {
        this.helper = helper;
    }
 
    /// <summary>
    /// 处理结果
    /// </summary>
    public struct Result
    {
        private int num;           //导入行数
        private int num_add;       //新增行数
        private int num_edit;      //修改行数
 
        /// <summary>
        /// 导入行数
        /// </summary>
        public int Num
        {
            get
            {
                return num;
            }
 
            set
            {
                num = value;
            }
        }
 
        /// <summary>
        /// 新增行数
        /// </summary>
        public int Num_add
        {
            get
            {
                return num_add;
            }
 
            set
            {
                num_add = value;
            }
        }
 
        /// <summary>
        /// 修改行数
        /// </summary>
        public int Num_edit
        {
            get
            {
                return num_edit;
            }
 
            set
            {
                num_edit = value;
            }
        }
 
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="num">导入行数</param>
        /// <param name="num_add">新增行数</param>
        /// <param name="num_edit">修改行数</param>
        internal Result(int num, int num_add, int num_edit)
        {
            this.num = num;
            this.num_add = num_add;
            this.num_edit = num_edit;
        }
    }
 
    #region 导入操作
 
    /// <summary>
    /// 表格导入到数据库
    /// </summary>
    /// <param name="name">上传控件的name</param>
    /// <param name="maxLength">最大上传的文件大小(MB)</param>
    /// <param name="headerRowsNum">表格头部所占的高度</param>
    /// <returns>表中的数据(只读取表中第一个Sheet)</returns>
    public Result Inport(string name, int maxLength, int headerRowsNum)
    {
        Stream stream = null;
        try
        {
            HttpContext context = HttpContext.Current;
            HttpRequest request = context.Request;
 
            //验证文件格式大小,返回文件流
            stream = GetInputStream(request, name, maxLength);
 
            //逐行读取文件内容并执行导入
            return Operation(stream, headerRowsNum);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (stream != null)
            {
                stream.Close();
                stream.Dispose();
            }
        }
    }
 
    private Result Operation(Stream stream,int headerRowsNum)
    {
        //操作结果
        Result result = new Result(0, 0, 0);
 
        //读取表格内容
        try
        {
            using (stream)
            {
                //自动识别.xlsx(XSSFWorkbook),.xls(HSSFWorkbook)
                IWorkbook workbook = WorkbookFactory.Create(stream);
 
                if (workbook.NumberOfSheets == 0)
                    throw new Exception("报错信息:文件无数据");
                //取第一张表
                ISheet sheet = workbook.GetSheetAt(0);
                if (sheet.PhysicalNumberOfRows == 0)
                    throw new Exception("报错信息:文件无数据");
 
                //数据行数
                int rowCount = sheet.LastRowNum;
 
                //读取表中所有数据
                for (int i = sheet.FirstRowNum + headerRowsNum; i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        result.Num++;
                        T model = helper.IsExist(row);
                        if (model == null)
                        {
                            result.Num_add++;
                            //新建
                            model = new T();
                            bool ok = helper.Map(row, model);
                            if (ok)
                            {
                                helper.Add(model);
                            }
                        }
                        else
                        {
                            result.Num_edit++;
                            //修改
                            bool ok = helper.Map(row, model);
                            if (ok)
                            {
                                helper.Update(model);
                            }
                        }
                    }
                }
            }
            return result;
        }
        catch (Exception ex)
        {
            //在异常信息中添加出错位置
            throw new Exception("当前读取到第"+ result.Num + "行数据,已添加"+ result .Num_add+ "条,已更新"
                + result.Num_edit + "条," + ex.Message);
        }
    }
    #endregion
}

2.3.3、 调用

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
//策略
private class helper : ExcelImport<B_NEWS>.ExcelImportHelper
{
    public void Add(B_NEWS model)
    {
        bll.Add(model);
    }
 
    public B_NEWS IsExist(IRow row)
    {
        return null;
    }
 
    public bool Map(IRow row, B_NEWS model)
    {
        model.id = row.GetValue<int>(0);
        model.c_info_title = row.GetValue<string>(1);
        model.c_info_content = row.GetValue<string>(2);
        model.d_list_date = row.GetValue<DateTime>(3);
        model.d_add_date = row.GetValue<DateTime>(4);
        model.n_is_active = (row.GetValue<bool>(5) ? 1 : 0);
        model.int1 = row.GetValue<int>(6);
        return true;
    }
 
    public void Update(B_NEWS model)
    {
        bll.Update(model);
    }
}
 
protected void daoru(object sender, EventArgs e)
{
    new ExcelImport<B_NEWS>(new helper()).Inport("excel", 10, 2);
}

3、 表格导出

3.1、 调用者直接接触的操作类

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
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
public class ExcelExport<T>
{
    /// <summary>
    /// 每列数据的解析方法
    /// </summary>
    private List<Func<T, object>> func = new List<Func<T, object>>();
 
    private IExcelBuilder builder = new ExcelBuilder();
 
    private string headJson;
 
    /// <summary>
    /// 更换表格建造器
    /// </summary>
    /// <param name="builder">自定义的建造器</param>
    /// <returns></returns>
    public ExcelExport<T> ChangeBuilder(IExcelBuilder builder)
    {
        this.builder = builder;
        return this;
    }
     
    /// <summary>
    /// 定义列
    /// </summary>
    /// <param name="convert">委托</param>
    /// <returns></returns>
    public ExcelExport<T> Column(Func<T, object> convert)
    {
        func.Add(convert);
        return this;
    }
 
    /// <summary>
    /// 设置Excel属性
    /// </summary>
    /// <param name="ext"></param>
    /// <returns></returns>
    public ExcelExport<T> SetExcelProperty(ExcelProperty ext)
    {
        builder.SetWorkbook(ext);
        return this;
    }
 
    /// <summary>
    /// 设置表头
    /// </summary>
    /// <param name="headJson">json形式的表头配置,可以配置多行表头</param>
    /// <returns></returns>
    public ExcelExport<T> SetExcelHeadJson(string headJson)
    {
        this.headJson = headJson;
        return this;
    }
 
    /// <summary>
    /// 设置表头
    /// </summary>
    /// <param name="path">绝对路径,内里保存json形式的表头配置</param>
    /// <returns></returns>
    public ExcelExport<T> SetExcelHeadFromFile(string path)
    {
        //读取配置信息
        this.headJson = File.ReadAllText(path);
        return this;
    }
 
    /// <summary>
    /// 设置表头
    /// </summary>
    /// <param name="headStr">“序号|订单号|订单状态|客户收货情况反馈|商品评价”的形式,仅支持单行表头</param>
    /// <returns></returns>
    public ExcelExport<T> SetExcelHeadStr(string headStr)
    {
        string[] head = headStr.Split('|');
        string headJson = "{\"root\":{\"head\":[";
        for (int i = 0, length = head.Length; i < length; i++)
        {
            if (i > 0)
            {
                headJson += ",";
            }
            headJson += "{";
            headJson += "\"text\":\"" + head [i]+ "\",";
            headJson += "\"cell_region\":\"0,0," + i + "," + i + "\"";
            headJson += "}";
        }
        headJson += "]";
        headJson += "}";
        headJson += "}";
 
        this.headJson = headJson;
        return this;
    }
 
    /// <summary>
    /// 导出WEB
    /// </summary>
    /// <param name="list">数据源(分组后的)</param>
    /// <param name="fileName">将要下载显示的名字</param>
    public void ExportToWeb(List<T> list, string fileName)
    {
        byte[] buffers = ForExport(list, fileName);
        ExportToWebExcel(buffers, fileName);
    }
 
    /// <summary>
    /// 导出到本地存储
    /// </summary>
    /// <param name="list">数据源(分组后的)</param>
    /// <param name="fileName">文件名称,请自己包含路径,例如C:\\test.xls</param>
    public void ExportToLocal(List<T> list, string fileName)
    {
        byte[] buffers = ForExport(list, fileName);
        ExportToLocalExcel(buffers, fileName);
    }
 
    #region private
    private byte[] ForExport(List<T> list, string fileName)
    {
        Check(list);
         
        //构建表格
        IWorkbook workbook = builder.BuildExcel<T>(list, headJson, func.ToArray());
        return SaveToByteArray(workbook);
    }
 
    private void Check(List<T> list)
    {
        //表头不算必填项
        //数据却不能为空
        if (list.Count == 0)
        {
            throw new Exception("数据源不能为空");
        }
    }
 
    /// <summary>
    /// 将WorkBook对象转换成内存流
    /// </summary>
    /// <param name="wookbook"></param>
    /// <returns></returns>
    private byte[] SaveToByteArray(IWorkbook wookbook)
    {
        using (MemoryStream ms = new MemoryStream())
        {
            wookbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            return ms.GetBuffer();
        }
    }
 
    /// <summary>
    ///  本地存储到excel
    /// </summary>
    /// <param name="buffers">文件二进制流</param>
    /// <param name="fileName">文件目录例如C:\\test.xls</param>
    private void ExportToLocalExcel(byte[] buffers, string fileName)
    {
        using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
        {
            fs.Write(buffers, 0, buffers.Length);
            fs.Flush();
        }
    }
 
    /// <summary>
    /// web导出excel
    /// </summary>
    /// <param name="buffers">文件二进制流</param>
    /// <param name="fileName">文件名称</param>
    private void ExportToWebExcel(byte[] buffers, string fileName)
    {
        HttpResponse Response = HttpContext.Current.Response;
 
        Response.BufferOutput = false;
        Response.ContentEncoding = Encoding.UTF8;
        Response.Charset = "utf-8";
 
        Response.ContentType = "application/octet-stream";
        Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
        Response.AddHeader("Content-Length", buffers.Length.ToString());
 
        Response.Clear();
        Response.BinaryWrite(buffers);
        Response.End();
    }
    #endregion
}

  

3.2、 表格样式配置

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
/**为方便样式书写,属性统一用小写*/
 
/// <summary>
/// 根节点, 定义Json表头的格式
/// </summary>
public class JsonSetting
{
    /// <summary>
    /// Root类
    /// </summary>
    public HeadInfo root { get; set; }
}
 
#region HeadInfo
 
/// <summary>
/// 报表表格头部信息
/// </summary>
public class HeadInfo
{
    /// <summary>
    /// 表头的样式
    /// </summary>
    public IList<Attribute4Head> head { get; set; }
    /// <summary>
    /// 每列单元格的样式
    /// </summary>
    public IList<Attribute4Cell> cell { get; set; }
 
    /// <summary>
    /// 表名,默认sheet
    /// </summary>
    public string sheet_name { get; set; }
 
    /// <summary>
    /// 默认单元格宽度(所有列),不设置则自适应宽度
    /// </summary>
    public int? default_width { get; set; }
 
    /// <summary>
    /// 默认行高度,不设置则使用表格的默认单元格高度
    /// </summary>
    public int? default_height { get; set; }
 
    /// <summary>
    /// 表格边框颜色 例如#000000
    /// </summary>
    public string border_color { get; set; }
 
    /// <summary>
    /// 边框风格,none,thin,dashed,dotted,double 默认 thin
    /// </summary>
    public string border_style { get; set; }
 
    /// <summary>
    /// 分组所在的列(索引从0开始:0,1)
    /// </summary>
    public string group_column { get; set; }
}
#endregion
 
/// <summary>
/// 关于表头单元格设置属性
/// </summary>
public class Attribute4Head : CellAttribute
{
    /// <summary>
    /// 单元格合并位置,(fromRow,toRow,fromColumn,toColumn)
    /// </summary>
    public string cell_region { get; set; }
    /// <summary>
    /// 显示的文字
    /// </summary>
    public string text { get; set; }
}
 
/// <summary>
/// 关于内容单元格设置属性
/// </summary>
public class Attribute4Cell : CellAttribute
{
    /// <summary>
    /// 数据格式(仅支持Excel的内嵌格式)
    /// </summary>
    public string data_format { get; set; }
 
    /// <summary>
    /// 宽度(内容单元格宽度覆盖默认宽度)
    /// </summary>
    public int? width { get; set; }
}
 
#region 公共属性
/// <summary>
/// 公共属性
/// </summary>
public abstract class CellAttribute
{
    /// <summary>
    /// 高度(限定行高,一行中只要一个单元格给定高度就可以了,多个单元格都有配置高度时,取最后一次赋值)
    /// </summary>
    public int? height { get; set; }
    /// <summary>
    /// 水平对齐方式 center,left,right
    /// </summary>
    public string align { get; set; }
    /// <summary>
    /// 垂直对齐方式center,top,bottom
    /// </summary>
    public string vertical_align { get; set; }
    /// <summary>
    /// 背景颜色.例如#000000
    /// </summary>
    public string background_color { get; set; }
    /// <summary>
    /// 前景颜色.例如#000000(单元格的背景色有bgcolor和forecolor两个背景色,一般使用forecolor)
    /// </summary>
    public string foreground_color { get; set; }
    /// <summary>
    /// 背景填充方式
    /// </summary>
    public string fill_pattern { get; set; }
    /// <summary>
    /// 字体大小
    /// </summary>
    public short? font_size { get; set; }
    /// <summary>
    /// 字体颜色
    /// </summary>
    public string font_color { get; set; }
    /// <summary>
    /// 字体名称,默认微软雅黑
    /// </summary>
    public string font_name { get; set; }
    /// <summary>
    ///是否是斜体
    /// </summary>
    public bool? italic { get; set; }
    /// <summary>
    /// 是否有中间线
    /// </summary>
    public bool? strike_out { get; set; }
    /// <summary>
    /// 是否设置下划线
    /// </summary>
    public bool? underline { get; set; }
    /// <summary>
    /// 表头文字是否加粗
    /// </summary>
    public bool? font_weight { get; set; }
}

3.3、 样式解析

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
/// <summary>
/// 由字符样式读取相应的枚举值
/// </summary>
/// <typeparam name="T">枚举类型</typeparam>
/// <param name="str">字符串</param>
/// <param name="defaultValue">默认值</param>
/// <returns></returns>
private T ToEnumValue<T>(string str,T defaultValue)
{
    if (string.IsNullOrEmpty(str))
    {
        return defaultValue;
    }
 
    //不区分大小写
    str = str.Trim().ToLower();
    if (str == "0")
    {
        //输入的是数值
        return (T)Enum.Parse(typeof(T),"0");
    }
    else
    {
        var fields = typeof(T).GetFields(BindingFlags.Static | BindingFlags.Public);
        int val = str.ToInt();
        if (val != 0 && val < fields.Length)
        {
            //输入的是数值
            return (T)Enum.Parse(typeof(T), str);
        }
        else
        {
            //输入的是枚举名
            foreach (var fi in fields)
            {
                var value = fi.GetValue(null);
                if(value.ToString().ToLower() == str)
                {
                    return (T)value;
                }
            }
            return defaultValue;
        }
    }
}
 
/// <summary>
/// 根据十六进制颜色获得颜色索引
/// </summary>
/// <param name="workbook"></param>
/// <param name="color"></param>
/// <returns></returns>
private short GetColorIndex(string color, HSSFWorkbook workbook)
{
    Color colour = ColorTranslator.FromHtml(color);
 
    if (this.XlPalette == null)
    {
        this.XlPalette = workbook.GetCustomPalette();
    }
 
    short s = 0;
    HSSFColor XlColour = XlPalette.FindColor(colour.R, colour.G, colour.B);
    if (XlColour == null)
    {
        XlColour = XlPalette.FindSimilarColor(colour.R, colour.G, colour.B);
        s = XlColour.Indexed;
    }
    else
    {
        s = XlColour.Indexed;
    }
    return s;
}

3.4、  涉及到的枚举类型

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
涉及到的枚举类型
 
 public enum FillPattern : short
{
    NoFill = 0,
    SolidForeground = 1,
    FineDots = 2,
    AltBars = 3,
    SparseDots = 4,
    ThickHorizontalBands = 5,
    ThickVerticalBands = 6,
    ThickBackwardDiagonals = 7,
    ThickForwardDiagonals = 8,
    BigSpots = 9,
    Bricks = 10,
    ThinHorizontalBands = 11,
    ThinVerticalBands = 12,
    ThinBackwardDiagonals = 13,
    ThinForwardDiagonals = 14,
    Squares = 15,
    Diamonds = 16,
    LessDots = 17,
    LeastDots = 18
}
 
public enum HorizontalAlignment
{
    General = 0,
    Left = 1,
    Center = 2,
    Right = 3,
    Fill = 4,
    Justify = 5,
    CenterSelection = 6,
    Distributed = 7
}
 
public enum VerticalAlignment
{
    None = -1,
    Top = 0,
    Center = 1,
    Bottom = 2,
    Justify = 3,
    Distributed = 4
}
 
public enum BorderStyle : short
{
    None = 0,
    Thin = 1,
    Medium = 2,
    Dashed = 3,
    Dotted = 4,
    Thick = 5,
    Double = 6,
    Hair = 7,
    MediumDashed = 8,
    DashDot = 9,
    MediumDashDot = 10,
    DashDotDot = 11,
    MediumDashDotDot = 12,
    SlantedDashDot = 13
}

  

3.5、 表格建造器

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
/// <summary>
/// 表格建造器
/// </summary>
public interface IExcelBuilder
{
    /// <summary>
    /// 更换样式解析器
    /// </summary>
    IExcelHelper Helper { set; }
 
    /// <summary>
    /// 构建表格
    /// </summary>
    /// <typeparam name="T">泛型实体</typeparam>
    /// <param name="list">要转换的列表</param>
    /// <param name="headJson">json形式的表头配置</param>
    /// <param name="fieldFuncs">字段委托</param>
    /// <returns></returns>
    HSSFWorkbook BuildExcel<T>(List<T> list, string headJson, Func<T, object>[] fieldFuncs);
 
    /// <summary>
    /// 设置excel文件基本属性
    /// </summary>
    /// <param name="ep"></param>
    void SetWorkbook(ExcelProperty ep);
}

  

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
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
/// <summary>
/// 构建表格
/// </summary>
public class ExcelBuilder : IExcelBuilder
{
    #region 字段及属性
    private const int RowPerSheet = 65530;                             //每张sheet最多数据行数
    private const int MaxColumnWidth = 50;                             //自适应列宽时的上限
    private string SheetName                                           //表名
    {
        get
        {
            return Helper.SheetName;
        }
    }
    private int HeadRow                                                //表头占据的行数
    {
        get
        {
            return Helper.HeadRow;
        }
    }
 
    /// <summary>
    /// 样式解析器
    /// </summary>
    public IExcelHelper Helper
    {
        set
        {
            this.helper = value;
        }
        private get
        {
            return helper;
        }
    }
 
    private HSSFWorkbook Workbook = new HSSFWorkbook();                //要导出的excel
    private IExcelHelper helper = new ExcelHelper();                   //用于解析属性
    private List<GroupClass> GroupColumn = new List<GroupClass>();     //分组的列的配置信息
    private List<CellRangeAddress> CellRange = new List<CellRangeAddress>(); //要合并的单元格
    private ISheet sheet = null;                                       //要导出的excel对象中的一个表
    #endregion
 
    #region IExcelBuilder
 
    /// <summary>
    /// 构建表格
    /// </summary>
    /// <typeparam name="T">泛型实体</typeparam>
    /// <param name="list">要转换的列表</param>
    /// <param name="headJson">json形式的表头配置</param>
    /// <param name="fieldFuncs">字段委托</param>
    /// <returns></returns>
    public HSSFWorkbook BuildExcel<T>(List<T> list, string headJson, Func<T, object>[] fieldFuncs)
    {
        //获取第一行数据
        List<object> dataRow = GetRowData(list[0], fieldFuncs);
 
        //解析第一行的数据,初步定下每列的默认样式
        Helper.GetStyle(headJson,dataRow, fieldFuncs, Workbook, GroupColumn);
 
        //写入数据
        Process(list, fieldFuncs);
 
        return Workbook;
    }
 
    /// <summary>
    /// 设置excel文件基本属性
    /// </summary>
    /// <param name="ep"></param>
    public void SetWorkbook(ExcelProperty ep)
    {
        DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
        dsi.Company = ep.Company;
        dsi.Manager = ep.Manager;
        dsi.Category = ep.Catagory;
        Workbook.DocumentSummaryInformation = dsi;
        SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
        si.Author = ep.Author;                    //填加xls文件作者信息
        si.ApplicationName = ep.ApplicationName;  //填加xls文件创建程序信息
        si.LastAuthor = ep.LastAuthor;            //填加xls文件最后保存者信息
        si.Comments = ep.Comments;                //填加xls文件作者信息
        si.Title = ep.Title;                      //填加xls文件标题信息
        si.Subject = ep.Subject;                  //填加文件主题信息
        si.Keywords = ep.KeyWord;
        si.CreateDateTime = DateTime.Now;
        si.Comments = ep.Comments;
        Workbook.SummaryInformation = si;
    }
    #endregion
 
    #region 完成从数据到excel的转化
    /// <summary>
    /// 整合数据
    /// </summary>
    /// <typeparam name="T">泛型实体</typeparam>
    /// <param name="list">要转换的列表</param>
    /// <param name="fieldFuncs">字段委托</param>
    private void Process<T>(List<T> list, Func<T, object>[] fieldFuncs)
    {
        //内容行
        int rowNum = -1;
        foreach (T item in list)
        {
            rowNum++;
 
            if ((rowNum % RowPerSheet) == 0)
            {
                //新建sheet
                NewSheet(rowNum);
            }
 
            //创建新行,获取数据
            int rowIndex = rowNum % RowPerSheet + HeadRow;
            IRow row = sheet.CreateRow(rowIndex);
            if (Helper.CellHeight.HasValue)
            {
                //行高
                row.HeightInPoints = Helper.CellHeight.Value;
            }
            List<object> dataRow = GetRowData(item, fieldFuncs);
 
            //写入
            int maxIndex = dataRow.Count;
             
            for (int colIndex = 0; colIndex < maxIndex; colIndex++)
            {
                //bool,时间,字符串,数字
                var val = dataRow[colIndex];
                GroupClass groupClass = CheckGroup(colIndex);
                if (groupClass == null)
                {
                    ICell cell = row.CreateCell(colIndex);
                    WriteCell(row, cell, colIndex, val, fieldFuncs);
                }
                else
                {
                    //分组
                    MergedRegionInColumn(groupClass, val, fieldFuncs, row, rowIndex, colIndex);
                }
            }
        }
 
        //单元格合并
        foreach (var cellRange in CellRange)
        {
            sheet.AddMergedRegion(cellRange);
            //设置边框
            ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(cellRange, Helper.WholeBorderStyle, Helper.WholeBorderColor);
        }
    }
 
    private void NewSheet(int rowNum)
    {
        int sheetIndex = (rowNum / RowPerSheet) + 1;
        sheet = Workbook.CreateSheet(SheetName + sheetIndex);
        //表头
        Helper.SetHeadAndDefaultStyle(Workbook, sheet);
        //新表新列
        for (int i = 0, length = GroupColumn.Count; i < length; i++)
        {
            GroupColumn[i].Value = null;
            GroupColumn[i].Count = 0;
            GroupColumn[i].cellRange = null;
        }
    }
 
    private void MergedRegionInColumn<T>(GroupClass groupClass,object val, Func<T, object>[] fieldFuncs, IRow row, int rowIndex, int colIndex)
    {
        object LastRowValue = groupClass.Value;
        //比对上一行的数据
        if (LastRowValue != null && LastRowValue.Equals(val))
        {
            //相等则合并单元格,行数累计
 
            //同一个单元格参与多次单元格合并,最终表格样式会出问题,需要先将旧的合并记录清掉
            CellRangeAddress lastCellRange = groupClass.cellRange;
            if (lastCellRange != null)
            {
                CellRange.Remove(lastCellRange);
            }
 
            //新的合并配置
            groupClass.Count++;
            CellRangeAddress cellRange = new CellRangeAddress(rowIndex - groupClass.Count, rowIndex, colIndex, colIndex);
            CellRange.Add(cellRange);
            groupClass.cellRange = cellRange;
        }
        else
        {
            //不等则更新后续分组的信息
            for (int i = groupClass.Index, length = GroupColumn.Count; i < length; i++)
            {
                GroupColumn[i].Value = null;
                GroupColumn[i].Count = 0;
                groupClass.cellRange = null;
            }
            groupClass.Value = val;
            //创建新单元格,普通写入操作
            ICell cell = row.CreateCell(colIndex);
            WriteCell(row, cell, colIndex, val, fieldFuncs);
        }
    }
    private void WriteCell<T>(IRow row, ICell cell,int colIndex,object val,Func<T, object>[] fieldFuncs)
    {
        ColumnInfo cellInfo = Helper.GetColumnInfo(colIndex);
        if (cellInfo == null)
        {
            cellInfo = Helper.AddColumn<T>(val, fieldFuncs, Workbook, sheet);
        }
 
        SetValue(cell, val);
        cell.CellStyle = cellInfo.Style;
 
        //调整列宽度
        if (cellInfo.Width == ColumnInfo.Auto)
        {
            int len = Encoding.GetEncoding(936).GetByteCount(val + "") + 8;
            if (len > MaxColumnWidth)
            {
                len = MaxColumnWidth;
            }
            len = len * 256;
            if (sheet.GetColumnWidth(colIndex) < len)
            {
                sheet.SetColumnWidth(colIndex, len);
            }
        }
    }
 
    #region 取值与填值
 
    /// <summary>
    /// 设置单元格的值
    /// </summary>
    /// <param name="cell"></param>
    /// <param name="val"></param>
    private void SetValue(ICell cell, object val)
    {
        if (val is bool)
        {
            var data = val.ChangeType<bool>();
            cell.SetCellValue(data);
        }
        else if (val is DateTime)
        {
            var data = val.ChangeType<DateTime>();
            cell.SetCellValue(data);
        }
        else if (val is double ||
         val is float ||
         val is long ||
         val is int ||
         val is short ||
         val is byte ||
         val is decimal)
        {
            var data = val.ChangeType<double>();
            cell.SetCellValue(data);
        }
        else
        {
            var data = val.ToString();
            cell.SetCellValue(data);
        }
    }
 
    /// <summary>
    /// 解析数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="item"></param>
    /// <param name="fieldFuncs"></param>
    /// <returns></returns>
    private List<object> GetRowData<T>(T item, Func<T, object>[] fieldFuncs)
    {
        List<object> dataRow = new List<object>();
        if (fieldFuncs.Length > 0)
        {
            for (int i = 0; i < fieldFuncs.Length; i++)
            {
                dataRow.Add(fieldFuncs[i](item));
            }
        }
        else
        {
            if (item is DataRow)
            {
                var row = item as DataRow;
                foreach (DataColumn column in row.Table.Columns)
                {
                    dataRow.Add(row[column]);
                }
            }
            else if (item is IEnumerable<T>)
            {
                foreach (var val in item as IEnumerable<T>)
                {
                    dataRow.Add(val);
                }
            }
            else
            {
                throw new Exception("请添加从数据实例映射到表格行的方法");
            }
        }
        return dataRow;
    }
 
    #endregion
 
    /// <summary>
    /// 判断当前操作的列是不是要分组
    /// </summary>
    /// <param name="colIndex">列下表</param>
    /// <returns></returns>
    private GroupClass CheckGroup(int colIndex)
    {
        foreach (var item in GroupColumn)
        {
            if(item.Column == colIndex)
            {
                return item;
            }
        }
        return null;
    }
    #endregion     
}

 

3.6、 表格样式解析器

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
/// <summary>
   /// 表格样式解析器
   /// </summary>
   public interface IExcelHelper
   {
       /// <summary>
       /// 内容行的定高
       /// </summary>
       int? CellHeight { get; }
 
       /// <summary>
       /// 表头占据的行数
       /// </summary>
       int HeadRow { get; }
 
       /// <summary>
       /// 表名
       /// </summary>
       string SheetName { get; }
 
       /// <summary>
       /// 整个表格border样式
       /// </summary>
       short WholeBorderColor { get; }
 
       /// <summary>
       /// 整个表格border颜色
       /// </summary>
       BorderStyle WholeBorderStyle { get; }
 
       /// <summary>
       /// 比第一行数据多出的列
       /// </summary>
       /// <typeparam name="T"></typeparam>
       /// <param name="val"></param>
       /// <param name="fieldFuncs"></param>
       /// <param name="workbook"></param>
       /// <param name="sheet"></param>
       ColumnInfo AddColumn<T>(object val, Func<T, object>[] fieldFuncs, HSSFWorkbook workbook, ISheet sheet);
 
       /// <summary>
       /// 读取列配置
       /// </summary>
       /// <param name="index">列下标</param>
       /// <returns></returns>
       ColumnInfo GetColumnInfo(int index);
 
       /// <summary>
       /// 计算每一列的默认单元格样式
       /// </summary>
       void GetStyle<T>(string headJson, List<object> dataRow, Func<T, object>[] fieldFuncs, HSSFWorkbook workbook, List<GroupClass> groupList);
 
       /// <summary>
       /// 为新sheet设置表头和默认样式
       /// </summary>
       /// <param name="workbook"></param>
       /// <param name="sheet"></param>
       void SetHeadAndDefaultStyle(HSSFWorkbook workbook, ISheet sheet);
   }

 

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
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
/// <summary>
/// 解析表格样式
/// </summary>
public class ExcelHelper : IExcelHelper
{
    #region 字段及属性
    private IList<Attribute4Head> CellStyle4Head = null;               //头部的配置信息
    private IList<Attribute4Cell> CellStyle4Cell = null;               //列配置信息
    private IList<ColumnInfo> columnInfo = new List<ColumnInfo>();     //记录解析后得到的每列的宽度及样式
    private string sheetName;                                          //表名
    private int headRow = 1;                                           //表头占据的行数
    private int  CellDefaultWidth;                                     //单元格宽度(默认值为ColumnInfo.Auto)
    private int? RowDefaultHeight;                                     //默认单元格高度(内容多了会自动撑高)
    private int? cellHeight;                                           //内容行的定高
    private BorderStyle wholeBorderStyle;                              //整个表格border样式,默认solid(thin)
    private short wholeBorderColor;                                    //整个表格border颜色
    private HSSFPalette XlPalette = null;                              //用于自定义颜色的对象(GetSetting中创建)
 
    /// <summary>
    /// 表名
    /// </summary>
    public string SheetName
    {
        get
        {
            return sheetName;
        }
    }
 
    /// <summary>
    /// 表头占据的行数
    /// </summary>
    public int HeadRow
    {
        get
        {
            return headRow;
        }
    }
 
    /// <summary>
    /// 内容行的定高
    /// </summary>
    public int? CellHeight
    {
        get
        {
            return cellHeight;
        }
    }
 
    /// <summary>
    /// 整个表格border样式
    /// </summary>
    public BorderStyle WholeBorderStyle
    {
        get
        {
            return wholeBorderStyle;
        }
    }
 
    /// <summary>
    /// 整个表格border颜色
    /// </summary>
    public short WholeBorderColor
    {
        get
        {
            return wholeBorderColor;
        }
    }
    #endregion
 
    #region 列配置操作
 
    /// <summary>
    /// 比第一行数据多出的列
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="val"></param>
    /// <param name="fieldFuncs"></param>
    /// <param name="workbook"></param>
    /// <param name="sheet"></param>
    public ColumnInfo AddColumn<T>(object val, Func<T, object>[] fieldFuncs, HSSFWorkbook workbook, ISheet sheet)
    {
        ColumnInfo info = GetDefaultCellStyle(val, fieldFuncs, workbook);
        columnInfo.Add(info);
        int index = columnInfo.Count - 1;
        UpdateColumn(index, workbook);
        //设置列宽
        if (info.Width != ColumnInfo.Auto)
        {
            sheet.SetColumnWidth(index, info.Width * 256);
        }
        return info;
    }
 
    /// <summary>
    /// 依据配置更新列配置
    /// </summary>
    /// <param name="index">列下标</param>
    /// <param name="workbook"></param>
    private void UpdateColumn(int index,HSSFWorkbook workbook)
    {
        ColumnInfo columnInfo = GetColumnInfo(index);
        if (columnInfo != null)
        {
            //列宽先赋值一个默认宽度
            columnInfo.Width = this.CellDefaultWidth;
            ICellStyle cellStyle = columnInfo.Style;
            //设置单元格边框
            cellStyle.BorderRight
                = cellStyle.BorderLeft
                = cellStyle.BorderBottom
                = cellStyle.BorderTop
                = WholeBorderStyle;
            cellStyle.BottomBorderColor
                   = cellStyle.RightBorderColor
                   = cellStyle.LeftBorderColor
                   = cellStyle.TopBorderColor
                   = WholeBorderColor;
            //更新样式
            Attribute4Cell style = null;
            if (CellStyle4Cell != null && CellStyle4Cell.Count > index)
            {
                style = CellStyle4Cell[index];
            }
            if (style != null)
            {
                //数据格式
                if (!string.IsNullOrEmpty(style.data_format))
                {
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat(style.data_format);
                }
                //列宽
                if (style.width.HasValue)
                {
                    columnInfo.Width = style.width.Value;
                }
                //高度
                if (style.height.HasValue)
                {
                    this.cellHeight = style.height.Value;
                }
 
                //其他属性
                AdaperCellStyle(cellStyle, style, workbook);
            }
        }
    }
 
    /// <summary>
    /// 读取列配置
    /// </summary>
    /// <param name="index">列下标</param>
    /// <returns></returns>
    public ColumnInfo GetColumnInfo(int index)
    {
        if (columnInfo.Count > index)
        {
            return columnInfo[index];
        }
        return null;
    }
    #endregion
 
    #region 解析样式
 
    /// <summary>
    /// 计算每一列的默认单元格样式
    /// </summary>
    public void GetStyle<T>(string headJson, List<object> dataRow, Func<T, object>[] fieldFuncs, HSSFWorkbook workbook, List<GroupClass> groupList)
    {
        // 读取第一行数据,为每列解析默认样式,依序加入cellStyle4Cell
        GetDefaultCellStyle(dataRow, fieldFuncs, workbook);
 
        //解析headJson
        GetSetting(headJson, workbook, groupList);
    }
 
    /// <summary>
    /// 依据一行示例数据为每列解析默认样式,依序加入cellStyle4Cell
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="dataRow"></param>
    /// <param name="fieldFuncs"></param>
    /// <param name="workbook"></param>
    private void GetDefaultCellStyle<T>(List<object> dataRow, Func<T, object>[] fieldFuncs, IWorkbook workbook)
    {
        for (int colIndex = 0; colIndex < dataRow.Count; colIndex++)
        {
            var val = dataRow[colIndex];
            ColumnInfo info = GetDefaultCellStyle(val, fieldFuncs, workbook);
            columnInfo.Add(info);
        }
    }
    /// <summary>
    /// //依据每个单元格对应的数据的数据类型计算出每列的默认样式,列的宽度设为自动
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="val"></param>
    /// <param name="fieldFuncs"></param>
    /// <param name="workbook"></param>
    /// <returns></returns>
    private ColumnInfo GetDefaultCellStyle<T>(object val, Func<T, object>[] fieldFuncs, IWorkbook workbook)
    {
        ICellStyle cellStyle;
        if (val is bool)
        {
            cellStyle = GetCellStyle(workbook, "默认");
        }
        else if (val is DateTime)
        {
            var data = val.ChangeType<DateTime>();
            if (data.Hour == 0 && data.Minute == 0 && data.Second == 0)
            {
                cellStyle = GetCellStyle(workbook, "时间");
            }
            else
            {
                cellStyle = GetCellStyle(workbook, "时间2");
            }
        }
        else if (val is double ||
         val is float ||
         val is long ||
         val is int ||
         val is short ||
         val is byte ||
         val is decimal)
        {
            var data = val.ChangeType<double>();
            if ((val + "").IndexOf(".") > 0)
            {
                cellStyle = GetCellStyle(workbook, "数字");
            }
            else
            {
                cellStyle = GetCellStyle(workbook, "数字2");
            }
        }
        else
        {
            cellStyle = GetCellStyle(workbook, "默认");
        }
 
        return new ColumnInfo() { Style = cellStyle, Width = ColumnInfo.Auto };
    }
 
    /// <summary>
    /// 解析json配置
    /// </summary>
    /// <param name="headJson">json字符串</param>
    /// <param name="workbook"></param>
    /// <param name="groupList"></param>
    private void GetSetting(string headJson, HSSFWorkbook workbook, List<GroupClass> groupList)
    {
        JsonSetting T = headJson.ToObject<JsonSetting>();
        //DebugUtils.Log(new MT.Business.Json().Convert2Json(T));
        //==========
 
        //分组的列信息
        GetGroupCellSetting(T.root.group_column, groupList);
 
        //表名
        this.sheetName = T.root.sheet_name;
        if (string.IsNullOrEmpty(this.sheetName))
        {
            this.sheetName = "sheet";
        }
 
        //默认单元格高度
        this.RowDefaultHeight = T.root.default_height;
        //边框样式
        this.wholeBorderStyle = ToEnumValue<BorderStyle>(T.root.border_style, BorderStyle.Thin);
        //边框颜色
        this.wholeBorderColor = GetColorIndex(T.root.border_color, workbook);
 
        //表头样式
        CellStyle4Head = T.root.head;
        //单元格样式
        CellStyle4Cell = T.root.cell;
        //默认单元格宽度
        this.CellDefaultWidth = (T.root.default_width.HasValue ? T.root.default_width.Value : ColumnInfo.Auto);
 
        //修正每列的配置信息
        for (int i = 0, length = this.columnInfo.Count; i < length; i++)
        {
            UpdateColumn(i, workbook);
        }
    }
 
    /// <summary>
    /// 初始化分组统计
    /// </summary>
    private void GetGroupCellSetting(string groupColumn, List<GroupClass> list)
    {
        int[] group = (string.IsNullOrEmpty(groupColumn) ? null : ToIntArray(groupColumn.Split(',')));
        if (group != null)
        {
            //依列下标排序
            group = group.OrderBy(x => x).ToArray();
            for (int i = 0, length = group.Length; i < length; i++)
            {
                list.Add(new GroupClass { Column = group[i], Count = 0, Index = i });
            }
        }
    }
 
    #endregion
 
    #region 为新sheet设置表头和默认样式等
 
    /// <summary>
    /// 设置表头和默认样式
    /// </summary>
    /// <param name="workbook"></param>
    /// <param name="sheet"></param>
    public void SetHeadAndDefaultStyle(HSSFWorkbook workbook, ISheet sheet)
    {
        sheet.DisplayGridlines = true;
        if(this.CellDefaultWidth != ColumnInfo.Auto)
        {
            //默认单元格宽度
            sheet.DefaultColumnWidth = this.CellDefaultWidth;
        }
        if (this.RowDefaultHeight.HasValue)
        {
            //设置表格默认行高
            sheet.DefaultRowHeight = (short)(this.RowDefaultHeight.Value * 20);
        }
 
        //给定的列宽
        for (int i = 0, length = this.columnInfo.Count; i < length; i++)
        {
            var info = this.columnInfo[i];
            if (info.Width != ColumnInfo.Auto)
            {
                sheet.SetColumnWidth(i, info.Width * 256);
            }
        }
        //写入表头
        SetHead(workbook,sheet);
    }
 
    /// <summary>
    /// 设置表头
    /// </summary>
    /// <param name="workbook"></param>
    /// <param name="sheet"></param>
    private void SetHead(HSSFWorkbook workbook, ISheet sheet)
    {
        List<IRow> rowList = new List<IRow>();
        //创建行
        for (int i = 0; i < HeadRow; i++)
        {
            IRow temp = sheet.CreateRow(i);
            rowList.Add(temp);
        }
 
        for (int i = 0; i < CellStyle4Head.Count; i++)
        {
            Attribute4Head attribute4Head = CellStyle4Head[i];
            int[] c = ToIntArray(attribute4Head.cell_region.Split(','));
             
            //计算title要插入的位置的索引
            int txtIndex = -1;
            int txtRow = -1;
            if ((c[0] <= c[1] && c[2] <= c[3]))
            {
                #region 写入表头
                //表头所占的列数
                int _headRow = c[1] + 1;
                if (headRow<= _headRow)
                {
                    //创建行
                    for (int j = headRow; j < _headRow; j++)
                    {
                        IRow temp = sheet.CreateRow(j);
                        rowList.Add(temp);
                    }
                    headRow = _headRow;
                }
 
                //@ 合并单元格
                if (c[0] < c[1] || c[2] < c[3])
                {
                    //例如1,1,2,2 第二行中的第3列,例如1,1,2,7 第二行中的(第3列到8列),合并列
                    CellRangeAddress cellRange = new CellRangeAddress(c[0], c[1], c[2], c[3]);
                    sheet.AddMergedRegion(cellRange);
                    //设置边框
                    ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(cellRange, WholeBorderStyle, WholeBorderColor);
                }
 
                //@ 填充内容
                txtIndex = c[2];
                txtRow = c[0];
                ICell cell1 = rowList[txtRow].CreateCell(txtIndex);
 
                cell1.SetCellValue(attribute4Head.text);
                cell1.CellStyle = GetHeadCellStyle(workbook, attribute4Head);
 
                //高度
                if (attribute4Head.height.HasValue)
                {
                    rowList[txtRow].HeightInPoints = attribute4Head.height.Value;
                }
                #endregion
            }
        }
         
    }
 
    private ICellStyle GetHeadCellStyle(HSSFWorkbook workbook,Attribute4Head cellStyle)
    {
        ICellStyle headStyle = workbook.CreateCellStyle();
        //设置单元格边框
        headStyle.BorderRight
            = headStyle.BorderLeft
            = headStyle.BorderBottom
            = headStyle.BorderTop
            = WholeBorderStyle;
        headStyle.BottomBorderColor
               = headStyle.RightBorderColor
               = headStyle.LeftBorderColor
               = headStyle.TopBorderColor
               = WholeBorderColor;
        //默认样式
        //水平对齐
        headStyle.Alignment = HorizontalAlignment.Center;
 
        //垂直对齐
        headStyle.VerticalAlignment = VerticalAlignment.Center;
 
        IFont font = workbook.CreateFont();
        //字体大小
        font.FontHeightInPoints = 12;
        //字体颜色
        font.Color = 8; //默认黑色
        //字体名称
        font.FontName = "微软雅黑";
        //加粗
        font.Boldweight = (short)FontBoldWeight.Bold;
        headStyle.SetFont(font);
 
        AdaperCellStyle(headStyle, cellStyle, workbook);
        return headStyle;
    }
 
    #endregion
 
    #region 字符串To 样式
 
    /// <summary>
    /// 由字符样式读取响应的枚举值
    /// </summary>
    /// <typeparam name="T">枚举类型</typeparam>
    /// <param name="str">字符串</param>
    /// <param name="defaultValue">默认值</param>
    /// <returns></returns>
    private T ToEnumValue<T>(string str,T defaultValue)
    {
        if (string.IsNullOrEmpty(str))
        {
            return defaultValue;
        }
 
        //不区分大小写
        str = str.Trim().ToLower();
        if (str == "0")
        {
            //输入的是数值
            return (T)Enum.Parse(typeof(T),"0");
        }
        else
        {
            var fields = typeof(T).GetFields(BindingFlags.Static | BindingFlags.Public);
            int val = str.ToInt();
            if (val != 0 && val < fields.Length)
            {
                //输入的是数值
                return (T)Enum.Parse(typeof(T), str);
            }
            else
            {
                //输入的是枚举名
                foreach (var fi in fields)
                {
                    var value = fi.GetValue(null);
                    if(value.ToString().ToLower() == str)
                    {
                        return (T)value;
                    }
                }
                return defaultValue;
            }
        }
    }
 
    /// <summary>
    /// 根据十六进制颜色获得颜色索引
    /// </summary>
    /// <param name="workbook"></param>
    /// <param name="color"></param>
    /// <returns></returns>
    private short GetColorIndex(string color, HSSFWorkbook workbook)
    {
        Color colour = ColorTranslator.FromHtml(color);
 
        if (this.XlPalette == null)
        {
            this.XlPalette = workbook.GetCustomPalette();
        }
 
        short s = 0;
        HSSFColor XlColour = XlPalette.FindColor(colour.R, colour.G, colour.B);
        if (XlColour == null)
        {
            XlColour = XlPalette.FindSimilarColor(colour.R, colour.G, colour.B);
            s = XlColour.Indexed;
        }
        else
        {
            s = XlColour.Indexed;
        }
        return s;
    }
    #endregion
 
    #region utils
 
    /// <summary>
    /// 依据用户json配置调整样式(公共部分)
    /// </summary>
    /// <param name="defaultCellStyle">默认样式</param>
    /// <param name="cellStyle">json传入的配置信息</param>
    /// <param name="workbook"></param>
    private void AdaperCellStyle(ICellStyle defaultCellStyle, CellAttribute cellStyle, HSSFWorkbook workbook)
    {
        //水平对齐
        if (!string.IsNullOrEmpty(cellStyle.align))
        {
            defaultCellStyle.Alignment = ToEnumValue<HorizontalAlignment>(cellStyle.align, HorizontalAlignment.Center);
        }
 
        //垂直对齐
        if (!string.IsNullOrEmpty(cellStyle.vertical_align))
        {
            defaultCellStyle.VerticalAlignment = ToEnumValue<VerticalAlignment>(cellStyle.vertical_align, VerticalAlignment.Center);
        }
 
        //背景颜色
        if (!string.IsNullOrEmpty(cellStyle.background_color) || !string.IsNullOrEmpty(cellStyle.foreground_color))
        {
            //重复设置背景色,有时候颜色会叠加,这里并不是简单的更换新值……
            //defaultCellStyle.FillForegroundColor = HSSFColor.White.Index;
            //defaultCellStyle.FillBackgroundColor = HSSFColor.Maroon.Index;
            defaultCellStyle.FillPattern = ToEnumValue<FillPattern>(cellStyle.fill_pattern, FillPattern.SolidForeground);
            if (!string.IsNullOrEmpty(cellStyle.background_color))
            {
                //背景色
                defaultCellStyle.FillBackgroundColor = GetColorIndex(cellStyle.background_color, workbook);
 
            }
            if (!string.IsNullOrEmpty(cellStyle.foreground_color))
            {
                //前景色
                defaultCellStyle.FillForegroundColor = GetColorIndex(cellStyle.foreground_color, workbook);
            }
        }
 
        IFont font = defaultCellStyle.GetFont(workbook);
        //字体大小
        if (cellStyle.font_size.HasValue)
        {
            font.FontHeightInPoints = cellStyle.font_size.Value; //设置字体大小
        }
        //字体颜色
        if (!string.IsNullOrEmpty(cellStyle.font_color))
        {
            font.Color = GetColorIndex(cellStyle.font_color, workbook);
        }
        //字体名称
        if (!string.IsNullOrEmpty(cellStyle.font_name))
        {
            font.FontName = cellStyle.font_name;
        }
        //是否是斜体
        if (cellStyle.italic.HasValue)
        {
            font.IsItalic = cellStyle.italic.Value ? true : false;
        }
        //是否有中间线
        if (cellStyle.strike_out.HasValue)
        {
            font.IsStrikeout = cellStyle.strike_out.Value ? true : false;
        }
        //是否设置下划线
        if (cellStyle.underline.HasValue)
        {
            font.Underline = cellStyle.underline.Value ? FontUnderlineType.Single : FontUnderlineType.None;
        }
        //是否加粗
        if (cellStyle.font_weight.HasValue)
        {
            font.Boldweight = cellStyle.font_weight.Value ? (short)FontBoldWeight.Bold : (short)FontBoldWeight.None;
        }
    }
 
    /// <summary>
    /// 获取默认样式
    /// </summary>
    /// <param name="wb">IWorkbook</param>
    /// <param name="str">标识</param>
    /// <returns></returns>
    private ICellStyle GetCellStyle(IWorkbook wb, string str)
    {
        ICellStyle cellStyle = wb.CreateCellStyle();
        IFont font = wb.CreateFont();
 
        //字体
        font.FontName = "微软雅黑";
 
        //居中对齐       
        cellStyle.Alignment = HorizontalAlignment.Center;
        //垂直对齐
        cellStyle.VerticalAlignment = VerticalAlignment.Center;
 
        //自动换行
        cellStyle.WrapText = true;
 
        //缩进
        cellStyle.Indention = 0;
        IDataFormat datastyle = wb.CreateDataFormat();
        switch (str)
        {
            /*
              HSSFDataFormat.GetBuiltinFormat("0.00") 与 datastyle.GetFormat("yyyy/mm/dd"):
              前者调用的是Excel的内嵌格式
            */
            case "时间":
                cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
                cellStyle.SetFont(font);
                break;
            case "时间2":
                cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd HH:mm");
                cellStyle.SetFont(font);
                break;
            case "数字":
                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
                cellStyle.SetFont(font);
                break;
            case "数字2":
                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
                cellStyle.SetFont(font);
                break;
            case "钱":
                cellStyle.DataFormat = datastyle.GetFormat("¥#,##0");
                cellStyle.SetFont(font);
                break;
            case "url":
                font.Underline = FontUnderlineType.Single;
                font.Color = HSSFColor.OliveGreen.Blue.Index;
                font.IsItalic = true;
                cellStyle.SetFont(font);
                cellStyle.Alignment = HorizontalAlignment.Center;
                break;
            case "百分比":
                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
                cellStyle.SetFont(font);
                break;
            case "中文大写":
                cellStyle.DataFormat = datastyle.GetFormat("[DbNum2][$-804]0");
                cellStyle.Alignment = HorizontalAlignment.Center;
                cellStyle.SetFont(font);
                break;
            case "科学计数法":
                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
                cellStyle.SetFont(font);
                break;
            case "默认":
                cellStyle.SetFont(font);
                cellStyle.Alignment = HorizontalAlignment.Center;
                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");//全部做字符串处理
                break;
        }
        return cellStyle;
    }
 
    /// <summary>
    /// 字符串数组to整型数组
    /// </summary>
    /// <param name="region">字符串数组</param>
    /// <returns></returns>
    private int[] ToIntArray(string[] region)
    {
        ArrayList aList = new ArrayList();
        foreach (string i in region)
        {
            aList.Add(Convert.ToInt32(i));
        }
        return (int[])aList.ToArray(typeof(int));
    }
    #endregion
}

3.7、 调用

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
   public class ExamEntity
   {
       public int Id { get; set; }
       /// <summary>
       /// 学生姓名
       /// </summary>
       public string Name { get; set; }
       public bool Sex { get; set; }
       /// <summary>
       /// 考试时间,每次考试时间
       /// </summary>
       public DateTime ExamTime { get; set; }
       /*各科成绩,语数英 物化生*/
       public int Chinese { get; set; }
       public int English { get; set; }
       public int Math { get; set; }
       public int Physics { get; set; }
       public int Chemistry { get; set; }
       public int Biology { get; set; }
       /// <summary>
       /// 总分
       /// </summary>
       public int SumCount { get; set; }
 
   }
   public List<ExamEntity> exam = new List<ExamEntity> {
         new ExamEntity{Id=1,Name="张三",ExamTime=Convert.ToDateTime("2013-03-01 08:30:00"),Chinese=122,English=111,Math=90,Physics=50,Chemistry=80,Biology=40,SumCount=493},
         new ExamEntity{Id=1,Name="张三",ExamTime=Convert.ToDateTime("2013-04-01 08:30:00"),Chinese=109,English=100,Math=100,Physics=90,Chemistry=70,Biology=60,SumCount=529},
         new ExamEntity{Id=1,Name="张三",ExamTime=Convert.ToDateTime("2013-05-01 08:30:00"),Chinese=109,English=100,Math=100,Physics=90,Chemistry=70,Biology=60,SumCount=529},
          new ExamEntity{Id=2,Name="李四",ExamTime=Convert.ToDateTime("2013-03-01 08:30:00"),Chinese=122,English=111,Math=90,Physics=50,Chemistry=80,Biology=40,SumCount=493},
         new ExamEntity{Id=2,Name="李四",ExamTime=Convert.ToDateTime("2013-04-01 08:30:00"),Chinese=109,English=100,Math=100,Physics=90,Chemistry=70,Biology=60,SumCount=529},
         new ExamEntity{Id=2,Name="李四",ExamTime=Convert.ToDateTime("2013-05-01 08:30:00"),Chinese=109,English=100,Math=100,Physics=90,Chemistry=70,Biology=60,SumCount=529},
         new ExamEntity{Id=3,Name="王五",ExamTime=Convert.ToDateTime("2013-03-01 08:30:00"),Chinese=122,English=111,Math=90,Physics=50,Chemistry=80,Biology=40,SumCount=493},
         new ExamEntity{Id=3,Name="王五",ExamTime=Convert.ToDateTime("2013-04-01 08:30:00"),Chinese=109,English=100,Math=100,Physics=90,Chemistry=70,Biology=60,SumCount=529},
         new ExamEntity{Id=4,Name="陈流",ExamTime=Convert.ToDateTime("2013-05-01 08:30:00"),Chinese=109,English=100,Math=100,Physics=90,Chemistry=70,Biology=60,SumCount=529}
       };
   protected void daochu(object sender, EventArgs e)
   {
       string json = @"{ 'root':{'sheet_name':'学生成绩','default_width':12,'group_column': '0,1,2,3','head':[ {
                                                                      'text':'编号',
                                                                      'height':54,
                                                                     'cell_region':'0,2,0,0'
                                                                  },
                                                                 {
                                                                     'text':'学生姓名',
                                                                     'cell_region':'0,2,1,1'
                                                                },
                                                                   {
                                                                     'text':'性别',
                                                                     'cell_region':'0,2,2,2'
                                                                }, {
                                                                     'text':'考试时间',
                                                                     'cell_region':'0,2,3,3'
                                                                }, {
                                                                     'text':'主科目',
'height':18,
                                                                     'cell_region':'0,0,4,6'
                                                                }, {
                                                                     'text':'副科目',
                                                                     'cell_region':'0,1,7,9'
                                                                },
                                                                    {
                                                                     'text':'必修课',
                                                                   'height':18,
                                                                     'cell_region':'1,1,4,5'
                                                                }, {
                                                                     'text':'选修课',
                                                                     'cell_region':'1,1,6,6'
                                                                },
                                                               {
                                                                     'text':'语文',
                                                                     'height':18, 
                                                                     'cell_region':'2,2,4,4'
                                                                }, {
                                                                     'text':'数学',
                                                                     'cell_region':'2,2,5,5'
                                                                }, {
                                                                     'text':'英语',
                                                                     'cell_region':'2,2,6,6'
                                                                }, {
                                                                     'text':'物理',
                                                                     'cell_region':'2,2,7,7'
                                                                }, {
                                                                     'text':'化学',
                                                                     'cell_region':'2,2,8,8'
                                                                }, {
                                                                     'text':'生物',
                                                                     'cell_region':'2,2,9,9'
                                                                }, {
                                                                     'text':'总分',
                                                                     'cell_region':'0,2,10,10'
                                                                }
                                                                   ],
                                                              'cell':[{},{},{},{'width':30}]
                                                               }
                                                              }";
       //处理数据源,对要分组的几列进行排序(order by Name,ExamTime)
       List<ExamEntity> exams = exam.OrderByDescending(x => x.Name).ThenByDescending(x => x.ExamTime).ToList<ExamEntity>();
 
       new ExcelExport<ExamEntity>()
                   //.SetExcelHeadStr(headStr)
                   .SetExcelHeadJson(json)
                   .Column(a => a.Id)
                   .Column(a => a.Name)
                   .Column(a => a.Sex ? "男" : "女")
                   .Column(a => a.ExamTime)
                   .Column(a => a.Chinese)
                   .Column(a => a.Math)
                   .Column(a => a.English)
                   .Column(a => a.Physics)
                   .Column(a => a.Chemistry)
                   .Column(a => a.Biology)
                   .Column(a => a.SumCount)
                   .ExportToWeb(exams, "daochu.xls");
   }

 

posted @   随心~  阅读(953)  评论(0编辑  收藏  举报
编辑推荐:
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 用 C# 插值字符串处理器写一个 sscanf
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
· .NET Core内存结构体系(Windows环境)底层原理浅谈
阅读排行:
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· DeepSeek 解答了困扰我五年的技术问题。时代确实变了!
· 本地部署DeepSeek后,没有好看的交互界面怎么行!
· 趁着过年的时候手搓了一个低代码框架
· 推荐一个DeepSeek 大模型的免费 API 项目!兼容OpenAI接口!

点击右上角即可分享
微信分享提示