首页 > 代码库 > 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 }
ExcelHelper类

  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到客户端