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

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
      
        TopTitle { get; set; } 23         public Dictionary
       
         DicTitle { get; set; } 24         public DataTable Data { get; set; } 25     } 26  27     public static class ExcelHelper 28     { 29         public static DataTable ToDataTable
        
         (this IList
         
           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
          
            cols = new List
           
            (); 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
            
             (Dictionary
             
               dicTitle, List
              
                data, string sheetName, string company, string subject) 86 { 87 return CreateExcel
               
                (dicTitle, data, sheetName, company, subject, string.Empty); 88 } 89 public static MemoryStream CreateExcel
                
                 (Dictionary
                 
                   dicTitle, List
                  
                    data, string sheetName, string company, string subject, string description) 90 { 91 return CreateExcel
                   
                    (dicTitle, data, sheetName, company, subject, description, null); 92 } 93 public static MemoryStream CreateExcel
                    
                     (Dictionary
                     
                       dicTitle, List
                      
                        data, string sheetName, string company, string subject, string description, Dictionary
                       
                         topTitle) 94 { 95 PropertyInfo[] properties = new PropertyInfo[dicTitle.Count]; 96 int idx = 0; 97 foreach (KeyValuePair
                        
                          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
                         
                           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 else167 {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 else175 {176 foreach (KeyValuePair
                          
                            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 #endregion192 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 #endregion204 205 #region 写入每个Sheet206 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 #endregion252 253 MemoryStream stream = new MemoryStream();254 workbook.Write(stream);255 return stream;256 }257 }258 }
                          
                         
                        
                       
                      
                     
                    
                   
                  
                 
                
               
              
             
            
           
          
         
        
       
      

调用方式

1 List
      
        sheets = new List
       
         {  2                 new MultiSheet{ 3                     SheetName = "车款关联信息", 4                     Data = new List
        
         ().ToDataTable
         
          (), 5                     Description = null, 6                     TopTitle = null, 7                     DicTitle = new Dictionary
          
           { 8                         {
    "CarStyleName","本系统车款"}, 9                         {
    "RME_CarModelName","RME车款"}10                     }11                 },12                 new MultiSheet{13                     SheetName = "本系统车系车型车款对应表",14                     Data = dsaCarStyle.ToList().ToDataTable
           
            (),15 Description = null,16 TopTitle = null,17 DicTitle = new Dictionary
            
             {18 { "CarSeries","车系"},19 { "CarType","车型"},20 { "CarStyle","车款"}21 }22 }, 23 new MultiSheet{24 SheetName = "RME车型车款对应表",25 Data = rmeCarModel.ToList().ToDataTable
             
              (),26 Description = null,27 TopTitle = null,28 DicTitle = new Dictionary
              
               {29 { "key","RME车型"},30 { "value","RME车款"}31 }32 } 33 };34 35 ExcelHelper.CreateExcel(sheets, "XXX", "车款关联信息").GetBuffer()