使用NPOI導出Excel文件
【NPOI簡介】app
NPOI 是 POI 項目的 .NET 版本。POI是一個開源的Java讀寫Excel、WORD等微軟OLE2組件文檔的項目。
使用 NPOI 你就能夠在沒有安裝 Office 或者相應環境的機器上對 WORD/EXCEL 文檔進行讀寫。NPOI是構建在POI 3.x版本之上的，它能夠在沒有安裝Office的狀況下對Word/Excel文檔進行讀寫操做。ide
NPOI官方教程地址：http://tonyqus.sinaapp.comgoogle
googlecode：http://code.google.com/p/npoi/編碼
codeplex：http://npoi.codeplex.com/spa
Excel 助手類：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 #endregion 122 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 #endregion 153 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 //else 163 //{ 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 else 183 { 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 #endregion 199 //if (!string.IsNullOrEmpty(headerText)) 200 //{ 201 // rowIndex = 1; 202 //} 203 //else 204 //{ 205 rowIndex = 2; 206 //} 207 208 } 209 #endregion 210 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 else 242 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 #endregion 269 270 rowIndex++; 271 } 272 273 return hssfworkbook; 274 } 275 } 276 }
使用的過程當中須要將實體對象集合轉換成DataTablecode
使用方法：orm
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 #endregion 40 41 #region 導出數據... 42 43 private void ExportData(List<ProductInfo> productList) 44 { 45 var exportDataList = 46 (from productInfo in ProductList 47 new 48 { 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.Objective 57 }).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