C# EPPlus 访问 Excel表格

EPPlus是什么？

一个访问Excel表格的库，调用相当简单

怎么访问？

1. 表格可以简单理解成一个二维数组
2. 我希望访问表格像二维数组一样简单
3. 我希望消耗不算太大

封装一个类

下载DLL以及这个文件：《下载传送门->》

注意需要导入EPPlus插件

using OfficeOpenXml;
using System;
using System.IO;

namespace ExcelHelper
{
    public interface ITable
    {
        public int RowCount { get; }
        public int ColumnCount { get; }
    }
    public interface ITable<T> : ITable
    {
        T this[int row, int column] { get; set; }
    }
    public class ExcelStream : IDisposable, ITable<string>
    {

        public string SourcePath { get; set; } = string.Empty;

        public int Sheet { get; set; } = 1;
        private ConfigTable<string> data = null;

        public ConfigTable<string> Data
        {
            get
            {
                if (data == null) throw new NullReferenceException("Please read the data before accessing it");
                return data;
            }
        }


        public int RowCount => data.RowCount;


        public int ColumnCount => data.ColumnCount;

        public string this[int row, int column]
        {
            get => Data[row, column];
            set => Data[row, column] = value;

        }
//#if NETCOREAPP
//        public ExcelStream(string location, int sheet = 0, bool isCommerical = false)
//        {

//#else

 public ExcelStream(string location, int sheet = 1,bool isCommerical= false)
    {
            //#endif

            int x = 0;
            if(false)
            {
               int x = 10;
            }
           // int x;

            //OfficeOpenXml.ExcelPackage.LicenseContext = isCommerical ? OfficeOpenXml.LicenseContext.Commercial : OfficeOpenXml.LicenseContext.NonCommercial;

            SourcePath = location;
            Sheet = sheet;
        }

        private void Create()
        {
            using (var package = new ExcelPackage())
            {
                package.Workbook.Worksheets.Add("Sheet1");
                package.SaveAs(new FileInfo(SourcePath));
            }
        }

        /// 

        /// Read执行的操作是与Excel表进行IO交换，读取到Data
        /// 
        public void Read()
        {
            if (!File.Exists(SourcePath)) throw new Exception("Please call write to create an empty file before reading a non-existent file");
            using (var package = new ExcelPackage(new FileInfo(SourcePath)))
            {

                // 获取工作表
                ExcelWorksheet worksheet = package.Workbook.Worksheets[Sheet]; // 默认读取第一个工作表.

                int rowCount;
                int columnCount;
                //初始化数据表
                if (worksheet.Dimension == null)
                {
                    rowCount = 0;
                    columnCount = 0;
                }
                else
                {
                    rowCount = worksheet.Dimension.Rows;
                    columnCount = worksheet.Dimension.Columns;
                }


                data = new ConfigTable<string>(rowCount, columnCount);

                //初始化单元格值
                for (int i = 0; i < rowCount; i++)
                {
                    for (int j = 0; j < columnCount; j++)
                    {
                        data[i, j] = worksheet.Cells[i + 1, j + 1].Text;
                    }
                }
            }
        }

        /// 

        /// Write执行的操作是不存在则创建，存在则覆盖写入
        /// 
        public void Write()
        {
            if (!File.Exists(SourcePath))
                Create();

            using (var package = new ExcelPackage(new FileInfo(SourcePath)))
            {

                ExcelWorksheet worksheet = package.Workbook.Worksheets[Sheet];

                for (int i = 0; i < data.RowCount; i++)
                {
                    for (int j = 0; j < data.ColumnCount; j++)
                    {
                        worksheet.Cells[i + 1, j + 1].Value = data[i, j];
                    }
                }

                package.Save();
            }
        }

        /// 

        /// 添加一个页
        /// 
        /// 
        public void AddSheet(string name)
        {
            using (var package = new ExcelPackage())
            {
                package.Workbook.Worksheets.Add(name);
                package.Save(SourcePath);
            }
        }

        public void Dispose()
        {
            data = null;
        }

    }
    public class ConfigTable<T> : ITable<T>
    {
        /// 

        /// 行数
        /// 
        public int RowCount { get; private set; } = 0;

        /// 

        /// 列数
        /// 
        public int ColumnCount { get; private set; } = 0;

#pragma warning disable CS8625 // 无法将 null 字面量转换为非 null 的引用类型。
        private T[,] data = null;
#pragma warning restore CS8625 // 无法将 null 字面量转换为非 null 的引用类型。

        public ConfigTable(int rowCount, int columnCount)
        {
            SetSizeAndCopy(rowCount, columnCount);
        }

        public ConfigTable(ConfigTable<T> table)
        {
            SetSizeAndCopy(table.RowCount, table.ColumnCount, false);
#pragma warning disable CS8604 // 引用类型参数可能为 null。
            table.data.CopyTo(data, 0);
#pragma warning restore CS8604 // 引用类型参数可能为 null。
        }


        /// 

        /// 设置大小并拷贝
        /// 
        /// 
        /// 
        /// 
        /// 
        public void SetSizeAndCopy(int newRowCount, int newColumnCount, bool copy = true)
        {
            if (newRowCount < 0 || newColumnCount < 0) throw new InvalidOperationException($"Invalid New Size:[{newRowCount},{newColumnCount}]");
            int minRowCount = Math.Min(RowCount, newRowCount);
            int minColumnCount = Math.Min(ColumnCount, newColumnCount);

            RowCount = newRowCount;
            ColumnCount = newColumnCount;

            var temp = data;
            data = new T[RowCount, ColumnCount];

            if (temp != null && copy)
            {
                for (int i = 0; i < minRowCount; i++)
                {
                    for (int j = 0; j < minColumnCount; j++)
                    {
                        data[i, j] = temp[i, j];
                    }
                }
            }
            temp = null;
        }


        private void IndexOutOfRangeCheck(int row, int column)
        {
            if (row >= RowCount || row < 0 || column >= ColumnCount || column < 0) throw new IndexOutOfRangeException($"Row:{row}({RowCount - 1})  Column:{column}({ColumnCount - 1})");
        }
        public T this[int row, int column]
        {

            get
            {
                IndexOutOfRangeCheck(row, column);
                return data[row, column];
            }
            set
            {
                IndexOutOfRangeCheck(row, column);
                data[row, column] = value;
            }
        }
    }
}
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