Excel导出操作类

PS: 暂无调用示例

 

 

后端导出excel

  1 using NPOI.HSSF.UserModel;
  2 using NPOI.SS.UserModel;
  3 using System;
  4 using System.Collections.Generic;
  5 using System.IO;
  6 using System.Linq;
  7 using System.Net;
  8 using System.Net.Http;
  9 using System.Net.Http.Headers;
 10 using System.Reflection;
 11 using System.Text;
 12 using System.Threading.Tasks;
 13 using System.Web;
 14 
 15 namespace SH3H.NC.Common.ExcelHelper
 16 {
 17     public class ExcelOutportHelper<T>
 18     {
 19         /// <summary>
 20         /// 导出Excel
 21         /// </summary>
 22         /// <param name="list">数据list</param>
 23         /// <param name="fileName">保存名称</param>
 24         /// <param name="propertyNames">字段名</param>
 25         /// <param name="propertyNameCn">首行title</param>
 26         public static void ListToExcel(IList<T> list, string fileName, string[] propertyNames, string[] propertyNameCn)
 27         {
 28             HttpContext.Current.Response.ContentType = "application/vnd.ms-excel;charset=UTF-8";
 29             HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", fileName));
 30             HttpContext.Current.Response.Clear();
 31             HttpContext.Current.Response.BinaryWrite(ListToExcel<T>(list, propertyNames, propertyNameCn).GetBuffer());
 32             HttpContext.Current.Response.End();
 33         }
 34 
 35         /// <summary>
 36         /// 导出Excel
 37         /// </summary>
 38         /// <param name="list">数据list</param>
 39         /// <param name="fileName">保存名称</param>
 40         /// <param name="propertyNames">字段名</param>
 41         /// <param name="propertyNameCn">首行title</param>
 42         public static HttpResponseMessage ListToExcelForHttpResponse(IList<T> list, string fileName, string[] propertyNames, string[] propertyNameCn)
 43         {
 44             HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
 45             var stream = ListToExcel(list, propertyNames, propertyNameCn);
 46 
 47             result.Content = new StreamContent(stream);
 48             result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel");
 49             result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
 50             {
 51                 FileName = fileName
 52             };
 53             return result;
 54         }
 55 
 56 
 57 
 58         /// <summary>
 59         /// 导出excel:仅导出设置了属性名的字段
 60         /// </summary>
 61         /// <param name="list"></param>
 62         /// <param name="fileName"></param>
 63         /// <param name="propertyNames"></param>
 64         /// <param name="propertyNameCn"></param>
 65         public static void ListToExcel2(IList<T> list, string fileName, string[] propertyNames, string[] propertyNameCn, string remark = "")
 66         {
 67             HttpContext.Current.Response.ContentType = "application/vnd.ms-excel;charset=UTF-8";
 68             HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", fileName));
 69             HttpContext.Current.Response.Clear();
 70             HttpContext.Current.Response.BinaryWrite(ListToExcel2(list, propertyNames, propertyNameCn, remark).GetBuffer());
 71             HttpContext.Current.Response.End();
 72         }
 73 
 74 
 75         /// <summary>
 76         /// 导出excel:仅导出设置了属性名的字段
 77         /// </summary>
 78         /// <param name="list"></param>
 79         /// <param name="fileName"></param>
 80         /// <param name="propertyNames"></param>
 81         /// <param name="propertyNameCn"></param>
 82         public static void ListToExcel3(IList<T> list, string fileName, string titleremark, string[] Fields, string[] propertyNames, string[] propertyNameCn)
 83         {
 84             HttpContext.Current.Response.ContentType = "application/vnd.ms-excel;charset=UTF-8";
 85             HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", fileName));
 86             HttpContext.Current.Response.Clear();
 87             HttpContext.Current.Response.BinaryWrite(ListToExcel3(list, titleremark, Fields, propertyNames, propertyNameCn).GetBuffer());
 88             HttpContext.Current.Response.End();
 89         }
 90 
 91 
 92         public static MemoryStream ListToExcel<T>(IList<T> list, string[] propertyNames, string[] propertyNameCn)
 93         {
 94             using (MemoryStream ms = new MemoryStream())
 95             {
 96                 IWorkbook workbook = new HSSFWorkbook();
 97                 ISheet sheet = workbook.CreateSheet();
 98                 IRow headerRow = sheet.CreateRow(0);
 99                 if (list.Count() > 0)
100                 {
101                     PropertyInfo[] propertys = list[0].GetType().GetProperties();
102                     for (int i = 0; i < propertys.Count(); i++)
103                     {
104                         if (propertyNames.Length == 0)
105                         {
106                             headerRow.CreateCell(i).SetCellValue(propertys[i].Name);
107                         }
108                         else
109                         {
110                             if (propertyNames.Contains(propertys[i].Name))
111                                 headerRow.CreateCell(i).SetCellValue(propertyNameCn[i]);
112                         }
113                     }
114                     int rowIndex = 1;
115                     //遍历集合生成excel的行集数据
116                     for (int i = 0; i < list.Count; i++)
117                     {
118                         IRow dataRow = sheet.CreateRow(rowIndex);
119                         for (int j = 0; j < propertys.Count(); j++)
120                         {
121                             if (propertyNames.Length == 0)
122                             {
123                                 object obj = propertys[j].GetValue(list[i], null);
124                                 dataRow.CreateCell(j).SetCellValue(obj != null ? obj.ToString() : string.Empty);
125                             }
126                             else
127                             {
128                                 if (propertyNames.Contains(propertys[j].Name))
129                                 {
130                                     object obj = propertys[j].GetValue(list[i], null);
131                                     dataRow.CreateCell(j).SetCellValue(obj != null ? obj.ToString() : string.Empty);
132                                 }
133                             }
134                         }
135                         rowIndex++;
136                     }
137                 }
138                 workbook.Write(ms);
139                 ms.Flush();
140                 ms.Position = 0;
141                 return ms;
142             }
143         }
144 
145         private static MemoryStream ListToExcel2(IList<T> list, string[] propertyNames, string[] propertyNameCn, string remark = "")
146         {
147             using (MemoryStream ms = new MemoryStream())
148             {
149                 IWorkbook workbook = new HSSFWorkbook();
150                 ISheet sheet = workbook.CreateSheet();
151 
152 
153                 var hrow = sheet.CreateRow(0);
154                 var titleRow = hrow;
155 
156                 if (!string.IsNullOrEmpty(remark))
157                 {
158                     hrow.CreateCell(0).SetCellValue(remark);
159                     titleRow = sheet.CreateRow(1);
160                 }
161 
162                 var beginIndex = string.IsNullOrEmpty(remark) ? 0 : 1;
163                 IRow headerRow = sheet.CreateRow(beginIndex);
164 
165                 if (list.Count() > 0)
166                 {
167                     PropertyInfo[] propertys = list[0].GetType().GetProperties();
168                     for (int i = 0; i < propertyNames.Length; i++)
169                     {
170                         if (propertys.Select(p => p.Name).Contains(propertyNames[i]))
171                         {
172                             headerRow.CreateCell(i).SetCellValue(propertyNameCn[i]);
173                         }
174                     }
175                     int rowIndex = 1;
176                     //遍历集合生成excel的行集数据
177                     for (int i = 0; i < list.Count; i++)
178                     {
179                         IRow dataRow = sheet.CreateRow(rowIndex + beginIndex);
180                         for (int j = 0; j < propertyNames.Length; j++)
181                         {
182                             var pro = propertys.Where(p => p.Name == propertyNames[j]).FirstOrDefault();
183                             if (pro != null)
184                             {
185                                 object obj = pro.GetValue(list[i], null);
186                                 dataRow.CreateCell(j).SetCellValue(obj != null ? obj.ToString() : string.Empty);
187                             }
188                         }
189                         rowIndex++;
190                     }
191                 }
192                 workbook.Write(ms);
193                 ms.Flush();
194                 ms.Position = 0;
195                 return ms;
196             }
197         }
198 
199         private static MemoryStream ListToExcel3(IList<T> list, string titleremark, string[] Fields, string[] propertyNames, string[] propertyNameCn)
200         {
201             using (MemoryStream ms = new MemoryStream())
202             {
203                 IWorkbook workbook = new HSSFWorkbook();
204                 var sheetFirst = workbook.CreateSheet();
205                 var hrow = sheetFirst.CreateRow(0);
206                 var titleRow = hrow;
207                 if (!string.IsNullOrEmpty(titleremark))
208                 {
209                     hrow.CreateCell(0).SetCellValue(titleremark);
210                     titleRow = sheetFirst.CreateRow(1);
211                 }
212                 for (int i = 0; i < Fields.Length; i++)
213                 {
214                     titleRow.CreateCell(i).SetCellValue(Fields[i]);
215                 }
216 
217 
218                 if (list != null && list.Count() > 0)
219                 {
220                     ISheet sheet = workbook.CreateSheet();
221                     IRow headerRow = sheet.CreateRow(0);
222                     PropertyInfo[] propertys = list[0].GetType().GetProperties();
223                     for (int i = 0; i < propertyNames.Length; i++)
224                     {
225                         if (propertys.Select(p => p.Name).Contains(propertyNames[i]))
226                         {
227                             headerRow.CreateCell(i).SetCellValue(propertyNameCn[i]);
228                         }
229                     }
230                     int rowIndex = 1;
231                     //遍历集合生成excel的行集数据
232                     for (int i = 0; i < list.Count; i++)
233                     {
234                         IRow dataRow = sheet.CreateRow(rowIndex);
235                         for (int j = 0; j < propertyNames.Length; j++)
236                         {
237                             var pro = propertys.Where(p => p.Name == propertyNames[j]).FirstOrDefault();
238                             if (pro != null)
239                             {
240                                 object obj = pro.GetValue(list[i], null);
241                                 dataRow.CreateCell(j).SetCellValue(obj != null ? obj.ToString() : string.Empty);
242                             }
243                         }
244                         rowIndex++;
245                     }
246                 }
247                 workbook.Write(ms);
248                 ms.Flush();
249                 ms.Position = 0;
250                 return ms;
251             }
252         }
253 
254     }
255 }

 

posted @ 2023-01-30 14:33  StarsOverTheSea  阅读(34)  评论(0)    收藏  举报