1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.IO;
  6 using System.Data;
  7 using System.Reflection;
  8 using System.Web;
  9 
 10     public static class ExcelUtil
 11     {
 12         private static void dataTableToCsv(DataTable table, string file)
 13         {
 14             string title = "";
 15             FileStream fs = new FileStream(file, FileMode.OpenOrCreate);
 16             StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default);
 17 
 18             for (int i = 0; i < table.Columns.Count; i++)
 19             {
 20                 title += table.Columns[i].ColumnName + "\t"; //栏位：自动跳到下一单元格
 21             }
 22 
 23             title = title.Substring(0, title.Length - 1) + "\n";
 24             sw.Write(title);
 25 
 26             foreach (DataRow row in table.Rows)
 27             {
 28                 string line = "";
 29 
 30                 for (int i = 0; i < table.Columns.Count; i++)
 31                 {
 32                     line += row[i].ToString().Trim() + "\t"; //内容：自动跳到下一单元格
 33                 }
 34                 line = line.Substring(0, line.Length - 1) + "\n";
 35                 sw.Write(line);
 36             }
 37             sw.Close();
 38             fs.Close();
 39         }
 40 
 41         /// <summary>
 42         /// 将一组对象导出成EXCEL
 43         /// </summary>
 44         /// <typeparam name="T">要导出对象的类型</typeparam>
 45         /// <param name="objList">一组对象</param>
 46         /// <param name="FileName">导出后的文件名</param>
 47         /// <param name="columnInfo">列名信息</param>
 48         public static void ExExcel<T>(List<T> objList, string FileName, Dictionary<string, string> columnInfo)
 49         {
 50             ExExcel(objList, FileName, columnInfo, null);
 51         }
 52 
 53         /// <summary>
 54         /// 将一组对象导出成EXCEL
 55         /// </summary>
 56         /// <typeparam name="T">要导出对象的类型</typeparam>
 57         /// <param name="objList">一组对象</param>
 58         /// <param name="FileName">导出后的文件名</param>
 59         /// <param name="columnInfo">列名信息</param>
 60         /// <param name="other">追加其他内容</param>
 61         public static void ExExcel<T>(List<T> objList, string FileName, Dictionary<string, string> columnInfo, string other)
 62         {
 63             if (columnInfo.Count == 0) { return; }
 64             if (objList.Count == 0) { return; }
 65             //生成EXCEL的HTML
 66             string excelStr = "";
 67 
 68             Type myType = objList[0].GetType();
 69             //根据反射从传递进来的属性名信息得到要显示的属性
 70             List<PropertyInfo> myPro = new List<PropertyInfo>();
 71             foreach (string cName in columnInfo.Keys)
 72             {
 73                 PropertyInfo p = myType.GetProperty(cName);
 74                 if (p != null)
 75                 {
 76                     myPro.Add(p);
 77                     excelStr += columnInfo[cName] + "\t";
 78                 }
 79             }
 80             //如果没有找到可用的属性则结束
 81             if (myPro.Count == 0) { return; }
 82             excelStr += "\n";
 83 
 84             foreach (T obj in objList)
 85             {
 86                 foreach (PropertyInfo p in myPro)
 87                 {
 88                     excelStr += p.GetValue(obj, null) + "\t";
 89                 }
 90                 excelStr += "\n";
 91             }
 92             if (!string.IsNullOrEmpty(other))
 93             {
 94                 excelStr += other;
 95             }
 96             //输出EXCEL
 97             HttpResponse rs = System.Web.HttpContext.Current.Response;
 98             rs.Clear();
 99             rs.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
100             rs.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(FileName, Encoding.UTF8));
101             rs.ContentType = "application/ms-excel";
102             rs.Write(excelStr);
103             rs.End();
104         }
105 
106         #region 保存数据列表到Excel（泛型）+void SaveToExcel<T>(IEnumerable<T> data, string FileName, string OpenPassword = "")
107         /// <summary>
108         /// 保存数据列表到Excel（泛型）
109         /// </summary>
110         /// <typeparam name="T">集合数据类型</typeparam>
111         /// <param name="data">数据列表</param>
112         /// <param name="FileName">Excel文件</param>
113         /// <param name="OpenPassword">Excel打开密码</param>
114         public static void SaveToExcel<T>(IEnumerable<T> data, string FileName, string OpenPassword = "")
115         {
116             FileInfo file = new FileInfo(FileName);
117             try
118             {
119                 using (ExcelPackage ep = new ExcelPackage(file, OpenPassword))
120                 {
121                     ExcelWorksheet ws = ep.Workbook.Worksheets.Add(typeof(T).Name);
122                     ws.Cells["A1"].LoadFromCollection(data, true, TableStyles.Medium10);
123 
124                     ep.Save(OpenPassword);
125                 }
126             }
127             catch (InvalidOperationException ex)
128             {
129                 //Console.WriteLine(ex.Message);
130                 throw ex;
131             }
132         }
133         #endregion
134 
135         #region 从Excel中加载数据（泛型）+IEnumerable<T> LoadFromExcel<T>(string FileName) where T : new()
136         /// <summary>
137         /// 从Excel中加载数据（泛型）
138         /// </summary>
139         /// <typeparam name="T">每行数据的类型</typeparam>
140         /// <param name="FileName">Excel文件名</param>
141         /// <returns>泛型列表</returns>
142         private static IEnumerable<T> LoadFromExcel<T>(string FileName) where T : new()
143         {
144             FileInfo existingFile = new FileInfo(FileName);
145             List<T> resultList = new List<T>();
146             Dictionary<string, int> dictHeader = new Dictionary<string, int>();
147 
148             using (ExcelPackage package = new ExcelPackage(existingFile))
149             {
150                 ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
151 
152                 int colStart = worksheet.Dimension.Start.Column;  //工作区开始列
153                 int colEnd = worksheet.Dimension.End.Column;       //工作区结束列
154                 int rowStart = worksheet.Dimension.Start.Row;       //工作区开始行号
155                 int rowEnd = worksheet.Dimension.End.Row;       //工作区结束行号
156 
157                 //将每列标题添加到字典中
158                 for (int i = colStart; i <= colEnd; i++)
159                 {
160                     dictHeader[worksheet.Cells[rowStart, i].Value.ToString()] = i;
161                 }
162 
163                 List<PropertyInfo> propertyInfoList = new List<PropertyInfo>(typeof(T).GetProperties());
164 
165                 for (int row = rowStart + 1; row < rowEnd; row++)
166                 {
167                     T result = new T();
168 
169                     //为对象T的各属性赋值
170                     foreach (PropertyInfo p in propertyInfoList)
171                     {
172                         try
173                         {
174                             ExcelRange cell = worksheet.Cells[row, dictHeader[p.Name]]; //与属性名对应的单元格
175 
176                             if (cell.Value == null)
177                                 continue;
178                             switch (p.PropertyType.Name.ToLower())
179                             {
180                                 case "string":
181                                     p.SetValue(result, cell.GetValue<String>(), null);
182                                     break;
183                                 case "int16":
184                                     p.SetValue(result, cell.GetValue<Int16>(), null);
185                                     break;
186                                 case "int32":
187                                     p.SetValue(result, cell.GetValue<Int32>(), null);
188                                     break;
189                                 case "int64":
190                                     p.SetValue(result, cell.GetValue<Int64>(), null);
191                                     break;
192                                 case "decimal":
193                                     p.SetValue(result, cell.GetValue<Decimal>(), null);
194                                     break;
195                                 case "double":
196                                     p.SetValue(result, cell.GetValue<Double>(), null);
197                                     break;
198                                 case "datetime":
199                                     p.SetValue(result, cell.GetValue<DateTime>(), null);
200                                     break;
201                                 case "boolean":
202                                     p.SetValue(result, cell.GetValue<Boolean>(), null);
203                                     break;
204                                 case "byte":
205                                     p.SetValue(result, cell.GetValue<Byte>(), null);
206                                     break;
207                                 case "char":
208                                     p.SetValue(result, cell.GetValue<Char>(), null);
209                                     break;
210                                 case "single":
211                                     p.SetValue(result, cell.GetValue<Single>(), null);
212                                     break;
213                                 default:
214                                     break;
215                             }
216                         }
217                         catch (KeyNotFoundException ex)
218                         {
219                             throw ex;
220                         }
221                     }
222                     resultList.Add(result);
223                 }
224             }
225             return resultList;
226         }
227         #endregion
228 
229         /// <summary>
230         /// 创造参数对
231         /// </summary>
232         /// <typeparam name="T"></typeparam>
233         /// <param name="comnName"></param>
234         /// <param name="func"></param>
235         /// <returns></returns>
236         public static KeyValuePair<string, Func<T, string>> CreateKVP<T>(string comnName, Func<T, string> func)
237         {
238             return new KeyValuePair<string, Func<T, string>>(comnName, func);
239         }
240 
241         /// <summary>
242         /// 向表中添加列
243         /// </summary>
244         /// <param name="sheet"></param>
245         /// <param name="columnName"></param>
246         public static void AddSheetHeadRange(this ExcelWorksheet sheet, params string[] columnNames)
247         {
248             for (int i = 0; i < columnNames.Length; i++)
249                 sheet.Cells[1, i + 1].Value = columnNames[i];
250         }
251 
252         /// <summary>
253         /// 向表中添加行数据
254         /// </summary>
255         /// <typeparam name="T"></typeparam>
256         /// <param name="sheet"></param>
257         /// <param name="listSources"></param>
258         /// <param name="values"></param>
259         public static void AddSheetRow<T>(this ExcelWorksheet sheet, IList<T> listSources, params KeyValuePair<string, Func<T, string>>[] values)
260         {
261             if (values != null && values.Length > 0)
262             {
263                 sheet.AddSheetHeadRange(values.Select(item => item.Key).ToArray());
264                 if (listSources != null && listSources.Count > 0)
265                 {
266                     IList<Func<T, string>> listVs = values.Select(item => item.Value).ToList();
267                     for (int i = 0; i < listSources.Count; i++)
268                     {
269                         for (int j = 0; j < listVs.Count; j++)
270                         {
271                             sheet.Cells[(i + 2), (j + 1)].Value = listVs[j](listSources[i]);
272                         }
273                     }
274                 }
275             }
276         }
277     }