DotNet 使用 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276

using System; using System.Data; using System.IO; using System.Text; using System.Web; using NPOI.HPSF; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using System.Collections.Generic; using System.Collections; namespace Weilog.Core.Document.Excel {     /// <summary>     /// Excel 助手类。     /// </summary>     public class ExcelHelper     {           private void InitializeWorkbook(HSSFWorkbook hssfworkbook, string headerText)         {             hssfworkbook = new HSSFWorkbook();               //创建一个文档摘要信息实体。             DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();             dsi.Company = "Weilog Team"; //公司名称             hssfworkbook.DocumentSummaryInformation = dsi;               //创建一个摘要信息实体。             SummaryInformation si = PropertySetFactory.CreateSummaryInformation();             si.Subject = "Weilog 系统生成";             si.Author = "Weilog 系统";             si.Title = headerText;             si.Subject = headerText;             si.CreateDateTime = DateTime.Now;             hssfworkbook.SummaryInformation = si;           }           private static MemoryStream WriteToStream(HSSFWorkbook hssfworkbook)         {             //Write the stream data of workbook to the root directory             MemoryStream file = new MemoryStream();             hssfworkbook.Write(file);             return file;         }         //Export(DataTable table, string headerText, string sheetName, string[] columnName, string[] columnTitle)         /// <summary>         /// 向客户端输出文件。         /// </summary>         /// <param name="table">数据表。</param>         /// <param name="headerText">头部文本。</param>         /// <param name="sheetName"></param>         /// <param name="columnName">数据列名称。</param>         /// <param name="columnTitle">表标题。</param>         /// <param name="fileName">文件名称。</param>         public static void Write(DataTable table, string headerText, string sheetName, string[] columnName, string[] columnTitle, string fileName)         {             HttpContext context = HttpContext.Current;             context.Response.ContentType = "application/vnd.ms-excel";             context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}",HttpUtility.UrlEncode(fileName, Encoding.UTF8)));             context.Response.Clear();             HSSFWorkbook hssfworkbook = GenerateData(table, headerText, sheetName, columnName, columnTitle);             context.Response.BinaryWrite(WriteToStream(hssfworkbook).GetBuffer());             context.Response.End();         }         /// <summary>         ///         /// </summary>         /// <param name="table"></param>         /// <param name="headerText"></param>         /// <param name="sheetName"></param>         /// <param name="columnName"></param>         /// <param name="columnTitle"></param>         /// <returns></returns>         public static HSSFWorkbook GenerateData(DataTable table, string headerText, string sheetName, string[] columnName, string[] columnTitle)         {             HSSFWorkbook hssfworkbook = new HSSFWorkbook();             ISheet sheet = hssfworkbook.CreateSheet(sheetName);               #region 设置文件属性信息               //创建一个文档摘要信息实体。             DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();             dsi.Company = "Weilog Team"; //公司名称             hssfworkbook.DocumentSummaryInformation = dsi;               //创建一个摘要信息实体。             SummaryInformation si = PropertySetFactory.CreateSummaryInformation();             si.Subject = "本文档由 Weilog 系统生成";             si.Author = " Weilog 系统";             si.Title = headerText;             si.Subject = headerText;             si.CreateDateTime = DateTime.Now;             hssfworkbook.SummaryInformation = si;               #endregion               ICellStyle dateStyle = hssfworkbook.CreateCellStyle();             IDataFormat format = hssfworkbook.CreateDataFormat();             dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");               #region 取得列宽               int[] colWidth = new int[columnName.Length];             for (int i = 0; i < columnName.Length; i++)             {                 colWidth[i] = Encoding.GetEncoding(936).GetBytes(columnTitle[i]).Length;             }             for (int i = 0; i < table.Rows.Count; i++)             {                 for (int j = 0; j < columnName.Length; j++)                 {                     int intTemp = Encoding.GetEncoding(936).GetBytes(table.Rows[i][columnName[j]].ToString()).Length;                     if (intTemp > colWidth[j])                     {                         colWidth[j] = intTemp;                     }                 }             }               #endregion               int rowIndex = 0;             foreach (DataRow row in table.Rows)             {                 #region 新建表，填充表头，填充列头，样式                 if (rowIndex == 65535 || rowIndex == 0)                 {                     if (rowIndex != 0)                     {                         sheet = hssfworkbook.CreateSheet(sheetName + ((int)rowIndex / 65535).ToString());                     }                       #region 表头及样式                     //if (!string.IsNullOrEmpty(headerText))                     {                         IRow headerRow = sheet.CreateRow(0);                         headerRow.HeightInPoints = 25;                         headerRow.CreateCell(0).SetCellValue(headerText);                           ICellStyle headStyle = hssfworkbook.CreateCellStyle();                         headStyle.Alignment = HorizontalAlignment.CENTER;                         IFont font = hssfworkbook.CreateFont();                         font.FontHeightInPoints = 20;                         font.Boldweight = 700;                         headStyle.SetFont(font);                           headerRow.GetCell(0).CellStyle = headStyle;                         //sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));                         sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, table.Columns.Count - 1));                     }                     #endregion                       #region 列头及样式                     {                         //HSSFRow headerRow = sheet.CreateRow(1);                         IRow headerRow;                         //if (!string.IsNullOrEmpty(headerText))                         //{                         //    headerRow = sheet.CreateRow(0);                         //}                         //else                         //{                         headerRow = sheet.CreateRow(1);                         //}                         ICellStyle headStyle = hssfworkbook.CreateCellStyle();                         headStyle.Alignment = HorizontalAlignment.CENTER;                         IFont font = hssfworkbook.CreateFont();                         font.FontHeightInPoints = 10;                         font.Boldweight = 700;                         headStyle.SetFont(font);                           for (int i = 0; i < columnName.Length; i++)                         {                             headerRow.CreateCell(i).SetCellValue(columnTitle[i]);                             headerRow.GetCell(i).CellStyle = headStyle;                             //设置列宽                             if ((colWidth[i] + 1) * 256 > 30000)                             {                                 sheet.SetColumnWidth(i, 10000);                             }                             else                             {                                 sheet.SetColumnWidth(i, (colWidth[i] + 1) * 256);                             }                         }                         /*                         foreach (DataColumn column in dtSource.Columns)                         {                             headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);                             headerRow.GetCell(column.Ordinal).CellStyle = headStyle;                                  //设置列宽                                sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);                         }                          * */                     }                     #endregion                     //if (!string.IsNullOrEmpty(headerText))                     //{                     //    rowIndex = 1;                     //}                     //else                     //{                     rowIndex = 2;                     //}                   }                 #endregion                   #region 填充数据                   IRow dataRow = sheet.CreateRow(rowIndex);                 for (int i = 0; i < columnName.Length; i++)                 {                     ICell newCell = dataRow.CreateCell(i);                       string drValue = row[columnName[i]].ToString();                       switch (table.Columns[columnName[i]].DataType.ToString())                     {                         case "System.String"://字符串类型                               if (drValue.ToUpper() == "TRUE")                                 newCell.SetCellValue("是");                             else if (drValue.ToUpper() == "FALSE")                                 newCell.SetCellValue("否");                             newCell.SetCellValue(drValue);                             break;                         case "System.DateTime"://日期类型                                DateTime dateV;                             DateTime.TryParse(drValue, out dateV);                             newCell.SetCellValue(dateV);                               newCell.CellStyle = dateStyle;//格式化显示                                break;                         case "System.Boolean"://布尔型                                bool boolV = false;                             bool.TryParse(drValue, out boolV);                             if (boolV)                                 newCell.SetCellValue("是");                             else                                 newCell.SetCellValue("否");                             break;                         case "System.Int16"://整型                            case "System.Int32":                         case "System.Int64":                         case "System.Byte":                             int intV = 0;                             int.TryParse(drValue, out intV);                             newCell.SetCellValue(intV);                             break;                         case "System.Decimal"://浮点型                            case "System.Double":                             double doubV = 0;                             double.TryParse(drValue, out doubV);                             newCell.SetCellValue(doubV);                             break;                         case "System.DBNull"://空值处理                                newCell.SetCellValue("");                             break;                         default:                             newCell.SetCellValue("");                             break;                     }                   }                   #endregion                   rowIndex++;             }               return hssfworkbook;         }     } }

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

使用方法：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65

#region 将指定的集合转换成数据表...           /// <summary>         /// 将指定的集合转换成DataTable。         /// </summary>         /// <param name="list">将指定的集合。</param>         /// <returns>返回转换后的DataTable。</returns>         public static DataTable ListToDataTable(IList list)         {             DataTable table = new DataTable();             if (list.Count > 0)             {                 PropertyInfo[] propertys = list[0].GetType().GetProperties();                 foreach (PropertyInfo pi in propertys)                 {                     Type pt = pi.PropertyType;                     if ((pt.IsGenericType) && (pt.GetGenericTypeDefinition() == typeof(Nullable<>)))                     {                         pt = pt.GetGenericArguments()[0];                     }                     table.Columns.Add(new DataColumn(pi.Name, pt));                 }                   for (int i = 0; i < list.Count; i++)                 {                     ArrayList tempList = new ArrayList();                     foreach (PropertyInfo pi in propertys)                     {                         object obj = pi.GetValue(list[i], null);                         tempList.Add(obj);                     }                     object[] array = tempList.ToArray();                     table.LoadDataRow(array, true);                 }             }             return table;         }           #endregion           #region 导出数据...           private void ExportData(List<ProductInfo> productList)         {             var exportDataList =                 (from productInfo in ProductList                   new                  {                      Code = productInfo.Code,                      Name = productInfo.Name,                      DeptName = productInfo.DeptName,                      ProjectName = productInfo.ProjectName,                      CategoryName = productInfo.CategoryName,                      Intro = productInfo.Intro,                      Level = productInfo.Level,                      Objective = productInfo.Objective                  }).ToList();               DataTable table = ListToDataTable(exportDataList);             string[] strFields = { "Code", "Name", "DeptName", "ProjectName", "CategoryName", "Intro", "Level", "Objective" };             string[] strFieldsName = { "编码", "名称", "所属部门", "所属项目", "分类", "简介", "等级", "目标" };             ExcelHelper.Write(table, "产品表", "产品表", strFields, strFieldsName, "产品表.xls");         }           #endregion