首页 > 代码库 > MVC导出Excel到客户端
MVC导出Excel到客户端
MVC导出数据到Excel详解
今天为大家分享一个利用NPIO导出数据到Excel 客户端的例子！（刚毕业的小白，第一次写博，如有错误 还望各位大咖指正）
1、NPOI官方网站；（http://npoi.codeplex.com/） 需要引用的dll文件如下：
2、用到的ExcelHelper类:
1 using Common.Logging; 2 using NPOI.HPSF; 3 using NPOI.HSSF.UserModel; 4 using NPOI.HSSF.Util; 5 using NPOI.SS.UserModel; 6 using NPOI.SS.Util; 7 using NPOI.XSSF.UserModel; 8 using System; 9 using System.Collections.Generic; 10 using System.Data; 11 using System.IO; 12 using System.Linq; 13 using System.Text; 14 using System.Text.RegularExpressions; 15 using System.Web; 16 17 namespace HHM.Web 18 { 19 20 /// <summary> 21 /// 导出数据到excel 22 /// </summary> 23 public class ExcelHelper 24 { 25 26 /// <summary> 27 /// 将json转换为DataTable 28 /// </summary> 29 /// <param name="strJson">得到的json</param> 30 /// <returns></returns> 31 public static DataTable JsonToDataTable(string strJson) 32 { 33 //转换json格式 34 strJson = strJson.Replace(",\"", "*\"").Replace("\":", "\"#").ToString(); 35 //取出表名 36 var rg = new Regex(@"(?<={)[^:]+(?=:\[)", RegexOptions.IgnoreCase); 37 string strName = rg.Match(strJson).Value; 38 DataTable tb = null; 39 //去除表名 40 strJson = strJson.Substring(strJson.IndexOf("[") + 1); 41 strJson = strJson.Substring(0, strJson.IndexOf("]")); 42 43 //获取数据 44 rg = new Regex(@"(?<={)[^}]+(?=})"); 45 MatchCollection mc = rg.Matches(strJson); 46 for (int i = 0; i < mc.Count; i++) 47 { 48 string strRow = mc[i].Value; 49 string[] strRows = strRow.Split(‘*‘); 50 51 //创建表 52 if (tb == null) 53 { 54 tb = new DataTable(); 55 tb.TableName = strName; 56 foreach (string str in strRows) 57 { 58 var dc = new DataColumn(); 59 string[] strCell = str.Split(‘#‘); 60 61 if (strCell[0].Substring(0, 1) == "\"") 62 { 63 int a = strCell[0].Length; 64 dc.ColumnName = strCell[0].Substring(1, a - 2); 65 } 66 else 67 { 68 dc.ColumnName = strCell[0]; 69 } 70 tb.Columns.Add(dc); 71 } 72 tb.AcceptChanges(); 73 } 74 75 //增加内容 76 DataRow dr = tb.NewRow(); 77 for (int r = 0; r < strRows.Length; r++) 78 { 79 try 80 { 81 string a = strRows[r].Split(‘#‘)[1].Trim(); 82 if (a.Equals("null")) 83 { 84 dr[r] = ""; 85 } 86 else 87 { 88 dr[r] = strRows[r].Split(‘#‘)[1].Trim().Replace("，", ",").Replace("：", ":").Replace("\"", ""); 89 } 90 } 91 catch (Exception e) 92 { 93 94 throw e; 95 } 96 } 97 tb.Rows.Add(dr); 98 tb.AcceptChanges(); 99 } 100 101 try 102 { 103 if (tb != null) 104 { 105 return tb; 106 } 107 else 108 { 109 throw new Exception("解析错误"); 110 } 111 } 112 catch (Exception e) 113 { 114 115 throw e; 116 } 117 } 118 119 120 /// <summary> 121 /// DataTable导出到Excel文件 122 /// </summary> 123 /// <param name="dtSource">源DataTable</param> 124 /// <param name="strHeaderText">表头文本</param> 125 /// <param name="strFileName">保存位置</param> 126 /// <param name="sheetName">工作薄名称</param> 127 public static void Export(DataTable dtSource, string strHeaderText, string strFileName, string sheetName) 128 { 129 using (MemoryStream ms = Export(dtSource, strHeaderText, sheetName)) 130 { 131 using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) 132 { 133 byte[] data =http://www.mamicode.com/ ms.ToArray(); 134 fs.Write(data, 0, data.Length); 135 fs.Flush(); 136 } 137 } 138 } 139 140 /// <summary> 141 /// DataTable导出到Excel的MemoryStream 142 /// </summary> 143 /// <param name="dtSource">源DataTable</param> 144 /// <param name="strHeaderText">表头文本</param> 145 /// <param name="sheetName">工作薄名称</param> 146 public static MemoryStream Export(DataTable dtSource, string strHeaderText, string sheetName) 147 { 148 HSSFWorkbook workbook = new HSSFWorkbook(); 149 ///设置工作薄名称 150 ISheet sheet = workbook.CreateSheet(sheetName); 151 sheet.TabColorIndex = HSSFColor.Red.Index; 152 #region 右击文件 属性信息 153 { 154 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); 155 dsi.Company = "XXXX有限公司"; 156 dsi.Category = "业务导出";///类别 157 workbook.DocumentSummaryInformation = dsi; 158 SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); 159 si.Author = "XXXX有限公司信息技术部"; //填加xls文件作者信息 160 si.ApplicationName = "代理商服务平台"; //填加xls文件创建程序信息 161 si.LastAuthor = "XXXX有限公司信息技术部"; //填加xls文件最后保存者信息 162 si.Comments = "如有疑问请询问在线客服"; //填加xls文件作者信息 163 si.Title = strHeaderText; //填加xls文件标题信息 164 si.Subject = "XXXX在线系统业务导出";//填加文件主题信息 165 si.CreateDateTime = DateTime.Now; 166 workbook.SummaryInformation = si; 167 } 168 #endregion 169 ///时间格式化格式 170 HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle; 171 HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat; 172 dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd HH:mm:ss"); 173 //取得列宽 174 int[] arrColWidth = new int[dtSource.Columns.Count]; 175 foreach (DataColumn item in dtSource.Columns) 176 { 177 arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; 178 } 179 for (int i = 0; i < dtSource.Rows.Count; i++) 180 { 181 for (int j = 0; j < dtSource.Columns.Count; j++) 182 { 183 int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; 184 if (intTemp > arrColWidth[j]) 185 { 186 arrColWidth[j] = intTemp; 187 } 188 } 189 } 190 int rowIndex = 0; 191 foreach (DataRow row in dtSource.Rows) 192 { 193 #region 新建表，填充表头，填充列头，样式 194 if (rowIndex == 65535 || rowIndex == 0) 195 { 196 if (rowIndex != 0) 197 { 198 sheet = workbook.CreateSheet() as HSSFSheet; 199 } 200 #region 表头及样式 201 { 202 203 //HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow; 204 //headerRow.HeightInPoints = 25; 205 //headerRow.CreateCell(0).SetCellValue(strHeaderText); 206 //HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; 207 //headStyle.Alignment = HorizontalAlignment.CENTER; 208 //HSSFFont font = workbook.CreateFont() as HSSFFont; 209 //font.FontHeightInPoints = 20; 210 //font.Boldweight = 200; 211 //headStyle.SetFont(font); 212 //headerRow.GetCell(0).CellStyle = headStyle; 213 //sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); 214 } 215 #endregion 216 217 #region 列头及样式 218 { 219 HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow; 220 HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; 221 headStyle.Alignment = HorizontalAlignment.Center; 222 HSSFFont font = workbook.CreateFont() as HSSFFont; 223 font.FontHeightInPoints = 10; 224 font.Boldweight = 700; 225 headStyle.IsLocked = true; 226 headStyle.SetFont(font); 227 //设置每列的文字修改 228 string[] header = strHeaderText.Split(‘,‘); 229 int i = 0; 230 foreach (DataColumn column in dtSource.Columns) 231 { 232 if (i < header.Length) 233 { 234 //headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); 235 headerRow.CreateCell(column.Ordinal).SetCellValue(header[i]); 236 headerRow.GetCell(column.Ordinal).CellStyle = headStyle; 237 //设置列宽 238 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); 239 } 240 i++; 241 } 242 //让列头不动 243 //sheet.CreateFreezePane(0, 2, 0, dtSource.Columns.Count - 1); 244 sheet.CreateFreezePane(0, 1, 0, dtSource.Columns.Count - 1); 245 } 246 #endregion 247 //修改把内容提到第二行 248 // rowIndex = 2; 249 rowIndex = 1; 250 } 251 #endregion 252 253 #region 填充内容 254 HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow; 255 foreach (DataColumn column in dtSource.Columns) 256 { 257 HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell; 258 string drValue =http://www.mamicode.com/ row[column].ToString(); 259 switch (column.DataType.ToString()) 260 { 261 case "System.String"://字符串类型 262 newCell.SetCellValue(drValue); 263 break; 264 case "System.DateTime"://日期类型 265 DateTime dateV; 266 DateTime.TryParse(drValue, out dateV); 267 newCell.SetCellValue(dateV); 268 269 newCell.CellStyle = dateStyle;//格式化显示 270 break; 271 case "System.Boolean"://布尔型 272 bool boolV = false; 273 bool.TryParse(drValue, out boolV); 274 newCell.SetCellValue(boolV); 275 break; 276 case "System.Int16"://整型 277 case "System.Int32": 278 case "System.Int64": 279 case "System.Byte": 280 int intV = 0; 281 int.TryParse(drValue, out intV); 282 newCell.SetCellValue(intV); 283 break; 284 case "System.Decimal"://浮点型 285 case "System.Double": 286 double doubV = 0; 287 double.TryParse(drValue, out doubV); 288 newCell.SetCellValue(doubV); 289 break; 290 case "System.DBNull"://空值处理 291 newCell.SetCellValue(""); 292 break; 293 default: 294 newCell.SetCellValue(""); 295 break; 296 } 297 } 298 #endregion 299 rowIndex++; 300 } 301 using (MemoryStream ms = new MemoryStream()) 302 { 303 workbook.Write(ms); 304 ms.Flush(); 305 ms.Position = 0; 306 return ms; 307 } 308 } 309 310 311 /// <summary> 312 /// 用于Web导出 313 /// </summary> 314 /// <param name="dtSource">源DataTable</param> 315 /// <param name="strHeaderText">表头文本</param> 316 /// <param name="strFileName">文件名</param> 317 /// <param name="sheetName">工作薄名称</param> 318 public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName, string sheetName) 319 { 320 HttpContext curContext = HttpContext.Current; 321 // 设置编码和附件格式 322 curContext.Response.ContentType = "application/vnd.ms-excel"; 323 curContext.Response.ContentEncoding = Encoding.UTF8; 324 curContext.Response.Charset = ""; 325 curContext.Response.AppendHeader("Content-Disposition", 326 "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8) + ".xls"); 327 curContext.Response.BinaryWrite(Export(dtSource, strHeaderText, sheetName).GetBuffer()); 328 //curContext.Response.End(); 329 // 因为（由于代码已经过优化或本机框架位于调用堆栈之上,无法计算表达式的值）异常 330 HttpContext.Current.ApplicationInstance.CompleteRequest(); 331 } 332 333 334 /// <summary>读取excel 335 /// 默认第一行为标头 336 /// </summary> 337 /// <param name="strFileName">excel文档路径</param> 338 /// <returns></returns> 339 public static DataTable Import(string strFileName) 340 { 341 DataTable dt = new DataTable(); 342 HSSFWorkbook hssfworkbook; 343 using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) 344 { 345 hssfworkbook = new HSSFWorkbook(file); 346 } 347 HSSFSheet sheet = hssfworkbook.GetSheetAt(0) as HSSFSheet; 348 System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); 349 HSSFRow headerRow = sheet.GetRow(0) as HSSFRow; 350 int cellCount = headerRow.LastCellNum; 351 for (int j = 0; j < cellCount; j++) 352 { 353 HSSFCell cell = headerRow.GetCell(j) as HSSFCell; 354 dt.Columns.Add(cell.ToString()); 355 } 356 for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) 357 { 358 HSSFRow row = sheet.GetRow(i) as HSSFRow; 359 DataRow dataRow = dt.NewRow(); 360 for (int j = row.FirstCellNum; j < cellCount; j++) 361 { 362 if (row.GetCell(j) != null) 363 dataRow[j] = row.GetCell(j).ToString(); 364 } 365 dt.Rows.Add(dataRow); 366 } 367 return dt; 368 } 369 370 /// <summary> 371 /// Excel表格转DataTable 372 /// </summary> 373 /// <param name="FilePath">Excel文件物理路径</param> 374 /// <returns>DataTable</returns> 375 public DataTable ExcelToDataTable(string FilePath) 376 { 377 DataTable dt = new DataTable("table1"); 378 using (FileStream fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read)) 379 { 380 IWorkbook workbook = WorkbookFactory.Create(fs);//使用接口，自动识别excel2003/2007格式 381 ISheet sheet = workbook.GetSheetAt(0);//得到里面第一个sheet 382 //表头 383 IRow headerRow = sheet.GetRow(0);//获得第一行 384 int cellCount = headerRow.LastCellNum - 1;//获得最后一个单元格的列号 385 for (int i = 0; i <= cellCount; i++) 386 { 387 //DataColumn column = new DataColumn(headerRow.Cells[i].ToString()); 388 DataColumn column; 389 if (headerRow.GetCell(i) == null) 390 { 391 column = new DataColumn(""); 392 } 393 else 394 { 395 string ColumnName = headerRow.GetCell(i).StringCellValue.ToString().Trim(); 396 //判断重名 397 if (dt.Columns.Contains(ColumnName)) 398 { 399 int RepeatNum = 1; 400 //加重复数后缀，循环判断 401 while (dt.Columns.Contains(ColumnName + RepeatNum.ToString())) 402 { 403 RepeatNum++; 404 } 405 column = new DataColumn(ColumnName + RepeatNum.ToString()); 406 } 407 else 408 { 409 column = new DataColumn(ColumnName); 410 } 411 } 412 dt.Columns.Add(column); 413 } 414 //内容 415 for (int i = 1; i <= sheet.LastRowNum; i++) 416 { 417 DataRow dr = dt.NewRow(); 418 IRow Row = sheet.GetRow(i); 419 if (Row == null) 420 { 421 continue; 422 } 423 for (int j = 0; j <= cellCount; j++) 424 { 425 if (Row.GetCell(j) == null) 426 { 427 dr[j] = ""; 428 } 429 else 430 { 431 dr[j] = Row.GetCell(j).ToString().Trim(); 432 } 433 } 434 dt.Rows.Add(dr); 435 } 436 } 437 return dt; 438 } 439 } 440 441 }
3、导出选中行的Js代码：
1 //EasyUI datagrid 动态导出Excel 2 function exportExcel() { 3 var rows = $("#grid").datagrid("getSelections"); 4 5 var bodyData = http://www.mamicode.com/JSON.stringify(rows); //转成json字符串 6 7 window.open(‘/SmsCount/ExportExcel?id=‘ + bodyData, ‘_black‘); 8 }
4、在控制器中调用ExcelHelper类方法：
1 /// <summary> 2 /// 导出Excel 3 /// </summary> 4 /// <returns></returns> 5 public ActionResult ExportExcel(string id) 6 { 7 try 8 { 9 DataTable dt = ExcelHelper.JsonToDataTable(id); 10 ExcelHelper.ExportByWeb(dt,"编号,日期,下行数量,上行数量","短信统计","sheet1"); 11 return Content("<script >alert(‘导出成功！‘);</script >", "text/html"); 12 } 13 catch (Exception ex) 14 { 15 HHM.Common.LogHelper.Error(ex); 16 return Content("<script >alert(‘导出失败！‘);</script >", "text/html"); 17 } 18 }
5、导出效果：
原文：http://www.cnblogs.com/congcongliu/p/6806134.html
MVC导出Excel到客户端
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。