使用NPOI导出Excel文件

【NPOI简介】

NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。
 
使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。NPOI是构建在POI 3.x版本之上的，它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。

NPOI官方教程地址：http://tonyqus.sinaapp.com

googlecode：http://code.google.com/p/npoi/

codeplex：http://npoi.codeplex.com/

 

Excel 助手类：

  1 using System;  2 using System.Data;  3 using System.IO;  4 using System.Text;  5 using System.Web;  6 using NPOI.HPSF;  7 using NPOI.HSSF.UserModel;  8 using NPOI.SS.UserModel;  9 using System.Collections.Generic; 10 using System.Collections; 11 namespace Weilog.Core.Document.Excel 12 { 13     /// <summary> 14     /// Excel 助手类。 15     /// </summary> 16     public class ExcelHelper 17     { 18  19         private void InitializeWorkbook(HSSFWorkbook hssfworkbook, string headerText) 20         { 21             hssfworkbook = new HSSFWorkbook(); 22  23             //创建一个文档摘要信息实体。 24             DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); 25             dsi.Company = "Weilog Team"; //公司名称 26             hssfworkbook.DocumentSummaryInformation = dsi; 27  28             //创建一个摘要信息实体。 29             SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); 30             si.Subject = "Weilog 系统生成"; 31             si.Author = "Weilog 系统"; 32             si.Title = headerText; 33             si.Subject = headerText; 34             si.CreateDateTime = DateTime.Now; 35             hssfworkbook.SummaryInformation = si; 36  37         } 38  39         private static MemoryStream WriteToStream(HSSFWorkbook hssfworkbook) 40         { 41             //Write the stream data of workbook to the root directory 42             MemoryStream file = new MemoryStream(); 43             hssfworkbook.Write(file); 44             return file; 45         } 46         //Export(DataTable table, string headerText, string sheetName, string[] columnName, string[] columnTitle) 47         /// <summary> 48         /// 向客户端输出文件。 49         /// </summary> 50         /// <param name="table">数据表。</param> 51         /// <param name="headerText">头部文本。</param> 52         /// <param name="sheetName"></param> 53         /// <param name="columnName">数据列名称。</param> 54         /// <param name="columnTitle">表标题。</param> 55         /// <param name="fileName">文件名称。</param> 56         public static void Write(DataTable table, string headerText, string sheetName, string[] columnName, string[] columnTitle, string fileName) 57         { 58             HttpContext context = HttpContext.Current; 59             context.Response.ContentType = "application/vnd.ms-excel"; 60             context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}",HttpUtility.UrlEncode(fileName, Encoding.UTF8))); 61             context.Response.Clear(); 62             HSSFWorkbook hssfworkbook = GenerateData(table, headerText, sheetName, columnName, columnTitle); 63             context.Response.BinaryWrite(WriteToStream(hssfworkbook).GetBuffer()); 64             context.Response.End(); 65         } 66         /// <summary> 67         ///  68         /// </summary> 69         /// <param name="table"></param> 70         /// <param name="headerText"></param> 71         /// <param name="sheetName"></param> 72         /// <param name="columnName"></param> 73         /// <param name="columnTitle"></param> 74         /// <returns></returns> 75         public static HSSFWorkbook GenerateData(DataTable table, string headerText, string sheetName, string[] columnName, string[] columnTitle) 76         { 77             HSSFWorkbook hssfworkbook = new HSSFWorkbook(); 78             ISheet sheet = hssfworkbook.CreateSheet(sheetName); 79  80             #region 设置文件属性信息 81  82             //创建一个文档摘要信息实体。 83             DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); 84             dsi.Company = "Weilog Team"; //公司名称 85             hssfworkbook.DocumentSummaryInformation = dsi; 86  87             //创建一个摘要信息实体。 88             SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); 89             si.Subject = "本文档由 Weilog 系统生成"; 90             si.Author = " Weilog 系统"; 91             si.Title = headerText; 92             si.Subject = headerText; 93             si.CreateDateTime = DateTime.Now; 94             hssfworkbook.SummaryInformation = si; 95  96             #endregion 97  98             ICellStyle dateStyle = hssfworkbook.CreateCellStyle(); 99             IDataFormat format = hssfworkbook.CreateDataFormat();100             dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");101 102             #region 取得列宽103 104             int[] colWidth = new int[columnName.Length];105             for (int i = 0; i < columnName.Length; i++)106             {107                 colWidth[i] = Encoding.GetEncoding(936).GetBytes(columnTitle[i]).Length;108             }109             for (int i = 0; i < table.Rows.Count; i++)110             {111                 for (int j = 0; j < columnName.Length; j++)112                 {113                     int intTemp = Encoding.GetEncoding(936).GetBytes(table.Rows[i][columnName[j]].ToString()).Length;114                     if (intTemp > colWidth[j])115                     {116                         colWidth[j] = intTemp;117                     }118                 }119             }120 121             #endregion122 123             int rowIndex = 0;124             foreach (DataRow row in table.Rows)125             {126                 #region 新建表，填充表头，填充列头，样式127                 if (rowIndex == 65535 || rowIndex == 0)128                 {129                     if (rowIndex != 0)130                     {131                         sheet = hssfworkbook.CreateSheet(sheetName + ((int)rowIndex / 65535).ToString());132                     }133 134                     #region 表头及样式135                     //if (!string.IsNullOrEmpty(headerText))136                     {137                         IRow headerRow = sheet.CreateRow(0);138                         headerRow.HeightInPoints = 25;139                         headerRow.CreateCell(0).SetCellValue(headerText);140 141                         ICellStyle headStyle = hssfworkbook.CreateCellStyle();142                         headStyle.Alignment = HorizontalAlignment.CENTER;143                         IFont font = hssfworkbook.CreateFont();144                         font.FontHeightInPoints = 20;145                         font.Boldweight = 700;146                         headStyle.SetFont(font);147 148                         headerRow.GetCell(0).CellStyle = headStyle;149                         //sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); 150                         sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, table.Columns.Count - 1));151                     }152                     #endregion153 154                     #region 列头及样式155                     {156                         //HSSFRow headerRow = sheet.CreateRow(1); 157                         IRow headerRow;158                         //if (!string.IsNullOrEmpty(headerText))159                         //{160                         //    headerRow = sheet.CreateRow(0);161                         //}162                         //else163                         //{164                         headerRow = sheet.CreateRow(1);165                         //}166                         ICellStyle headStyle = hssfworkbook.CreateCellStyle();167                         headStyle.Alignment = HorizontalAlignment.CENTER;168                         IFont font = hssfworkbook.CreateFont();169                         font.FontHeightInPoints = 10;170                         font.Boldweight = 700;171                         headStyle.SetFont(font);172 173                         for (int i = 0; i < columnName.Length; i++)174                         {175                             headerRow.CreateCell(i).SetCellValue(columnTitle[i]);176                             headerRow.GetCell(i).CellStyle = headStyle;177                             //设置列宽 178                             if ((colWidth[i] + 1) * 256 > 30000)179                             {180                                 sheet.SetColumnWidth(i, 10000);181                             }182                             else183                             {184                                 sheet.SetColumnWidth(i, (colWidth[i] + 1) * 256);185                             }186                         }187                         /* 188                         foreach (DataColumn column in dtSource.Columns) 189                         { 190                             headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); 191                             headerRow.GetCell(column.Ordinal).CellStyle = headStyle; 192    193                             //设置列宽    194                             sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); 195                         } 196                          * */197                     }198                     #endregion199                     //if (!string.IsNullOrEmpty(headerText))200                     //{201                     //    rowIndex = 1;202                     //}203                     //else204                     //{205                     rowIndex = 2;206                     //}207 208                 }209                 #endregion210 211                 #region 填充数据212 213                 IRow dataRow = sheet.CreateRow(rowIndex);214                 for (int i = 0; i < columnName.Length; i++)215                 {216                     ICell newCell = dataRow.CreateCell(i);217 218                     string drValue = row[columnName[i]].ToString();219 220                     switch (table.Columns[columnName[i]].DataType.ToString())221                     {222                         case "System.String"://字符串类型   223                             if (drValue.ToUpper() == "TRUE")224                                 newCell.SetCellValue("是");225                             else if (drValue.ToUpper() == "FALSE")226                                 newCell.SetCellValue("否");227                             newCell.SetCellValue(drValue);228                             break;229                         case "System.DateTime"://日期类型    230                             DateTime dateV;231                             DateTime.TryParse(drValue, out dateV);232                             newCell.SetCellValue(dateV);233 234                             newCell.CellStyle = dateStyle;//格式化显示    235                             break;236                         case "System.Boolean"://布尔型    237                             bool boolV = false;238                             bool.TryParse(drValue, out boolV);239                             if (boolV)240                                 newCell.SetCellValue("是");241                             else242                                 newCell.SetCellValue("否");243                             break;244                         case "System.Int16"://整型    245                         case "System.Int32":246                         case "System.Int64":247                         case "System.Byte":248                             int intV = 0;249                             int.TryParse(drValue, out intV);250                             newCell.SetCellValue(intV);251                             break;252                         case "System.Decimal"://浮点型    253                         case "System.Double":254                             double doubV = 0;255                             double.TryParse(drValue, out doubV);256                             newCell.SetCellValue(doubV);257                             break;258                         case "System.DBNull"://空值处理    259                             newCell.SetCellValue("");260                             break;261                         default:262                             newCell.SetCellValue("");263                             break;264                     }265 266                 }267 268                 #endregion269 270                 rowIndex++;271             }272 273             return hssfworkbook;274         }275     }276 }

Excel 助手类

使用的过程中需要将实体对象集合转换成DataTable

使用方法：

 1         #region 将指定的集合转换成数据表... 2  3         /// <summary> 4         /// 将指定的集合转换成DataTable。 5         /// </summary> 6         /// <param name="list">将指定的集合。</param> 7         /// <returns>返回转换后的DataTable。</returns> 8         public static DataTable ListToDataTable(IList list) 9         {10             DataTable table = new DataTable();11             if (list.Count > 0)12             {13                 PropertyInfo[] propertys = list[0].GetType().GetProperties();14                 foreach (PropertyInfo pi in propertys)15                 {16                     Type pt = pi.PropertyType;17                     if ((pt.IsGenericType) && (pt.GetGenericTypeDefinition() == typeof(Nullable<>)))18                     {19                         pt = pt.GetGenericArguments()[0];20                     }21                     table.Columns.Add(new DataColumn(pi.Name, pt));22                 }23 24                 for (int i = 0; i < list.Count; i++)25                 {26                     ArrayList tempList = new ArrayList();27                     foreach (PropertyInfo pi in propertys)28                     {29                         object obj = pi.GetValue(list[i], null);30                         tempList.Add(obj);31                     }32                     object[] array = tempList.ToArray();33                     table.LoadDataRow(array, true);34                 }35             }36             return table;37         }38 39         #endregion40 41         #region 导出数据...42 43         private void ExportData(List<ProductInfo> productList)44         {45             var exportDataList =46                 (from productInfo in ProductList47                   new48                  {49                      Code = productInfo.Code,50                      Name = productInfo.Name,51                      DeptName = productInfo.DeptName,52                      ProjectName = productInfo.ProjectName,53                      CategoryName = productInfo.CategoryName,54                      Intro = productInfo.Intro,55                      Level = productInfo.Level,56                      Objective = productInfo.Objective57                  }).ToList();58 59             DataTable table = ListToDataTable(exportDataList);60             string[] strFields = { "Code", "Name", "DeptName", "ProjectName", "CategoryName", "Intro", "Level", "Objective" };61             string[] strFieldsName = { "编码", "名称", "所属部门", "所属项目", "分类", "简介", "等级", "目标" };62             ExcelHelper.Write(table, "产品表", "产品表", strFields, strFieldsName, "产品表.xls");63         }64 65         #endregion

使用方法