C#数据导出到Excel

教授开发了一个nb的导出excel类，很是方便，分享下，留着以后用

View Code 
  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.IO;
  6 using System.Web;
  7 using NPOI.HSSF.UserModel;
  8 using NPOI.SS.UserModel;
  9 using NPOI.HPSF;
 10 using NPOI.SS.Util;
 11 using NPOI.HSSF.UserModel.Contrib;
 12 using System.Data;
 13 using System.Reflection;
 14 using System.ComponentModel;
 15 
 16 namespace XXXXManage.Common
 17 {
 18     public class MultiSheet
 19     {
 20         public string SheetName { get; set; }
 21         public string Description { get; set; }
 22         public Dictionary<string, int> TopTitle { get; set; }
 23         public Dictionary<string, string> DicTitle { get; set; }
 24         public DataTable Data { get; set; }
 25     }
 26 
 27     public static class ExcelHelper
 28     {
 29         public static DataTable ToDataTable<T>(this IList<T> data)
 30         {
 31             PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
 32             DataTable table = new DataTable();
 33             for (int i = 0; i < props.Count; i++)
 34             {
 35                 PropertyDescriptor prop = props[i];
 36                 table.Columns.Add(prop.Name);
 37             }
 38             object[] values = new object[props.Count];
 39             foreach (T item in data)
 40             {
 41                 for (int i = 0; i < values.Length; i++)
 42                 {
 43                     values[i] = props[i].GetValue(item);
 44                 }
 45                 table.Rows.Add(values);
 46             }
 47             return table;
 48         }
 49 
 50         public static DataTable GetData(Stream stream)
 51         {
 52             return GetData(stream, null);
 53         }
 54         public static DataTable GetData(Stream stream, string sheetName)
 55         {
 56             HSSFWorkbook workbook = new HSSFWorkbook(stream);
 57             Sheet sheet = string.IsNullOrEmpty(sheetName) ? workbook.GetSheetAt(0) : workbook.GetSheet(sheetName);
 58             List<string> cols = new List<string>();
 59             int colIdx = 0;
 60             while (sheet.GetRow(0).GetCell(colIdx) != null)
 61             {
 62                 cols.Add(sheet.GetRow(0).GetCell(colIdx++).StringCellValue.Trim());
 63             }
 64 
 65             DataTable dt = new DataTable();
 66             foreach (string colName in cols)
 67             {
 68                 dt.Columns.Add(colName, typeof(string));
 69             }
 70             int end = sheet.LastRowNum;
 71             int col = dt.Columns.Count;
 72             for (int i = 1; i <= end; i++)
 73             {
 74                 DataRow dr = dt.NewRow();
 75                 for (int j = 0; j < col; j++)
 76                 {
 77                     Cell cell = sheet.GetRow(i).GetCell(j);
 78                     dr[j] = cell == null ? string.Empty : cell.StringCellValue.Trim();
 79                 }
 80                 dt.Rows.Add(dr);
 81             }
 82 
 83             return dt;
 84         }
 85         public static MemoryStream CreateExcel<T>(Dictionary<string, string> dicTitle, List<T> data, string sheetName, string company, string subject)
 86         {
 87             return CreateExcel<T>(dicTitle, data, sheetName, company, subject, string.Empty);
 88         }
 89         public static MemoryStream CreateExcel<T>(Dictionary<string, string> dicTitle, List<T> data, string sheetName, string company, string subject, string description)
 90         {
 91             return CreateExcel<T>(dicTitle, data, sheetName, company, subject, description, null);
 92         }
 93         public static MemoryStream CreateExcel<T>(Dictionary<string, string> dicTitle, List<T> data, string sheetName, string company, string subject, string description, Dictionary<string, int> topTitle)
 94         {
 95             PropertyInfo[] properties = new PropertyInfo[dicTitle.Count];
 96             int idx = 0;
 97             foreach (KeyValuePair<string, string> kv in dicTitle)
 98             {
 99                 PropertyInfo property = typeof(T).GetProperty(kv.Key);
100                 if (property == null)
101                 {
102                     throw new Exception(string.Format("'{0}' not contains propertiy '{1}'", typeof(T).Name, kv.Key));
103                 }
104                 properties[idx++] = property;
105             }
106 
107             HSSFWorkbook workbook = new HSSFWorkbook();
108             DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
109             dsi.Company = company;
110             workbook.DocumentSummaryInformation = dsi;
111             SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
112             si.Subject = subject;
113             workbook.SummaryInformation = si;
114 
115             Sheet sheet = workbook.CreateSheet(sheetName);
116             int r = 0;
117             if (topTitle != null)
118             {
119                 Row topRow = sheet.CreateRow(r);
120                 int topIdx = 0;
121                 foreach (var kv in topTitle)
122                 {
123                     topRow.CreateCell(topIdx, CellType.STRING).SetCellValue(kv.Key);
124                     sheet.AddMergedRegion(new CellRangeAddress(r, r, topIdx, topIdx + kv.Value - 1));
125                     topIdx = topIdx + kv.Value;
126                 }
127 
128                 r++;
129             }
130             if (!string.IsNullOrEmpty(description))
131             {
132                 Row descRow = sheet.CreateRow(r);
133                 descRow.CreateCell(0, CellType.STRING).SetCellValue(description);
134                 sheet.AddMergedRegion(new CellRangeAddress(r, r, 0, dicTitle.Count - 1));
135                 r++;
136             }
137             Row row = sheet.CreateRow(r);
138             idx = 0;
139             foreach (var kv in dicTitle)
140             {
141                 row.CreateCell(idx++, CellType.STRING).SetCellValue(kv.Value);
142             }
143 
144             for (int i = 0; i < data.Count; i++)
145             {
146                 row = sheet.CreateRow(i + 1 + r);
147                 for (int j = 0; j < properties.Length; j++)
148                 {
149                     row.CreateCell(j, CellType.STRING).SetCellValue(properties[j].GetValue(data[i], null).ToString());
150                 }
151             }
152 
153             MemoryStream stream = new MemoryStream();
154             workbook.Write(stream);
155             return stream;
156         }
157         public static MemoryStream CreateExcel(List<MultiSheet> sheets, string company, string subject)
158         {
159             #region 有效性验证
160 
161             StringBuilder error = new StringBuilder();
162             if (sheets == null || sheets.Count == 0)
163             {
164                 error.Append(string.Format("不包含任何表单数据!"));
165             }
166             else
167             {
168                 foreach (MultiSheet ms in sheets)
169                 {
170                     if (ms.DicTitle == null || ms.DicTitle.Count == 0)
171                     {
172                         error.Append(string.Format("“{0}”中不包含任何列；\r\n", ms.SheetName));
173                     }
174                     else
175                     {
176                         foreach (KeyValuePair<string, string> kv in ms.DicTitle)
177                         {
178                             if (!ms.Data.Columns.Contains(kv.Key))
179                             {
180                                 error.Append(string.Format("“{0}”中不包含“{1}”列；\r\n", ms.SheetName, kv.Key));
181                             }
182                         }
183                     }
184                 }
185             }
186             if (error.Length > 0)
187             {
188                 throw new Exception(error.ToString());
189             }
190 
191             #endregion
192 
193             #region Excel文件信息
194 
195             HSSFWorkbook workbook = new HSSFWorkbook();
196             DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
197             dsi.Company = company;
198             workbook.DocumentSummaryInformation = dsi;
199             SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
200             si.Subject = subject;
201             workbook.SummaryInformation = si;
202 
203             #endregion
204 
205             #region 写入每个Sheet
206 
207             foreach (MultiSheet ms in sheets)
208             {
209                 Sheet sheet = workbook.CreateSheet(ms.SheetName);
210                 int rowIdx = 0;
211                 int colIdx = 0;
212                 if (ms.TopTitle != null)
213                 {
214                     Row topRow = sheet.CreateRow(rowIdx);
215                     int topIdx = 0;
216                     foreach (var kv in ms.TopTitle)
217                     {
218                         topRow.CreateCell(topIdx, CellType.STRING).SetCellValue(kv.Key);
219                         sheet.AddMergedRegion(new CellRangeAddress(rowIdx, rowIdx, topIdx, topIdx + kv.Value - 1));
220                         topIdx = topIdx + kv.Value;
221                     }
222 
223                     rowIdx++;
224                 }
225                 if (!string.IsNullOrEmpty(ms.Description))
226                 {
227                     Row descRow = sheet.CreateRow(rowIdx);
228                     descRow.CreateCell(0, CellType.STRING).SetCellValue(ms.Description);
229                     sheet.AddMergedRegion(new CellRangeAddress(rowIdx, rowIdx, 0, ms.DicTitle.Count - 1));
230                     rowIdx++;
231                 }
232                 Row row = sheet.CreateRow(rowIdx);
233 
234                 foreach (var kv in ms.DicTitle)
235                 {
236                     row.CreateCell(colIdx++, CellType.STRING).SetCellValue(kv.Value);
237                 }
238 
239                 for (int i = 0; i < ms.Data.Rows.Count; i++)
240                 {
241                     row = sheet.CreateRow(i + 1 + rowIdx);
242                     colIdx = 0;
243                     foreach (string colName in ms.DicTitle.Keys)
244                     {
245                         string value = ms.Data.Rows[i][colName] == null ? string.Empty : ms.Data.Rows[i][colName].ToString();
246                         row.CreateCell(colIdx++, CellType.STRING).SetCellValue(value);
247                     }
248                 }
249             }
250 
251             #endregion
252 
253             MemoryStream stream = new MemoryStream();
254             workbook.Write(stream);
255             return stream;
256         }
257     }
258 }


调用方式

View Code 
 1 List<MultiSheet> sheets = new List<MultiSheet> { 
 2                 new MultiSheet{
 3                     SheetName = "车款关联信息",
 4                     Data = new List<CarStyleRelationViewModels>().ToDataTable<CarStyleRelationViewModels>(),
 5                     Description = null,
 6                     TopTitle = null,
 7                     DicTitle = new Dictionary<string,string>{
 8                         {"CarStyleName","本系统车款"},
 9                         {"RME_CarModelName","RME车款"}
10                     }
11                 },
12                 new MultiSheet{
13                     SheetName = "本系统车系车型车款对应表",
14                     Data = dsaCarStyle.ToList().ToDataTable<DSACarStyleViewModel>(),
15                     Description = null,
16                     TopTitle = null,
17                     DicTitle = new Dictionary<string,string>{
18                         {"CarSeries","车系"},
19                         {"CarType","车型"},
20                         {"CarStyle","车款"}
21                     }
22                 }, 
23                 new MultiSheet{
24                     SheetName = "RME车型车款对应表",
25                     Data = rmeCarModel.ToList().ToDataTable<DictionaryViewModel>(),
26                     Description = null,
27                     TopTitle = null,
28                     DicTitle = new Dictionary<string,string>{
29                         {"key","RME车型"},
30                         {"value","RME车款"}
31                     }
32                 }            
33             };
34 
35 ExcelHelper.CreateExcel(sheets, "XXX", "车款关联信息").GetBuffer()
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/futao/p/2607816.html