C# 在MVC 中把DataTable中的数据导出到Excel 

  1 // 类库
  2 // NPOI.dll
  3 // NPOI.OOXML.dll
  4 // NPOI.OpenXml4Net.dll
  5 // NPOI.OpenXmlFormats.dll
  6 
  7 using NPOI.XSSF.UserModel;
  8 using NPOI.SS.UserModel;
  9 
 10 
 11 
 12 public class ExcelHelper
 13     {
 14         public static Stream ExportData(System.Data.DataTable dt)
 15         {
 16             if (dt == null)
 17             {
 18                 throw new ArgumentNullException();
 19             }
 20             if (dt.Rows.Count > 5000)
 21                 dt=dt.Select().Take(5000).CopyToDataTable();
 22 
 23             XSSFWorkbook book = new XSSFWorkbook();
 24             
 25             ISheet sheet = book.CreateSheet("sheet1");
 26             int rowCount = dt.Rows.Count;
 27             int cellCount = dt.Columns.Count;
 28 
 29             var titleRow = sheet.CreateRow(0);
 30             #region header style
 31             titleRow.HeightInPoints = 18;
 32             ICellStyle headStyle = book.CreateCellStyle();
 33             headStyle.Alignment = HorizontalAlignment.Center;
 34             IFont font = book.CreateFont();
 35             font.FontHeightInPoints = 12;
 36             font.Boldweight = 1200;
 37             font.Color = NPOI.HSSF.Util.HSSFColor.Green.Index;
 38             headStyle.SetFont(font);
 39 
 40             headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
 41             headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
 42             headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
 43             headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
 44 
 45             #endregion
 46 
 47             for (int i = 0; i < dt.Columns.Count; i++)
 48             {
 49                 ICell cell = titleRow.CreateCell(i);
 50                 cell.SetCellValue(dt.Columns[i].ColumnName);
 51                 cell.CellStyle = headStyle;
 52             }
 53 
 54             for (int i = 0; i < dt.Rows.Count; i++)
 55             {
 56                 IRow row = sheet.CreateRow(i + 1);
 57                 for (int j = 0; j < cellCount; j++)
 58                 {
 59                     string formattedString;
 60                     if (dt.Columns[j].DataType.Name == "String")
 61                     {
 62                         Encoding ascii = Encoding.ASCII;
 63                         Encoding unicode = Encoding.Unicode;
 64                         // Convert the string into a byte array.
 65                         byte[] unicodeBytes = unicode.GetBytes(dt.Rows[i][j].ToString());
 66                         // Perform the conversion from one encoding to the other.
 67                         byte[] asciiBytes = Encoding.Convert(unicode, ascii, unicodeBytes);
 68                         // Convert the new byte[] into a char[] and then into a string.
 69                         char[] asciiChars = new char[ascii.GetCharCount(asciiBytes, 0, asciiBytes.Length)];
 70                         ascii.GetChars(asciiBytes, 0, asciiBytes.Length, asciiChars, 0);
 71                         string asciiString = new string(asciiChars);
 72 
 73                         formattedString = asciiString.Replace(Convert.ToChar(0x0).ToString(), "");                       
 74                     }
 75                     else
 76                     {
 77                         formattedString = dt.Rows[i][j].ToString();
 78                     }
 79 
 80                     row.CreateCell(j).SetCellValue(formattedString);
 81                 }
 82             }
 83 
 84 
 85             NpoiMemoryStream stream = new NpoiMemoryStream();
 86             stream.AllowClose = false;
 87             book.Write(stream);
 88  
 89             stream.Seek(0, SeekOrigin.Begin);
 90             stream.AllowClose = true;
 91             book.Close();
 92 
 93             return stream;
 94         }
 95 
 96         public static List<string> ImportData(Stream stream)
 97         {
 98             List<string> data = new List<string>();
 99             XSSFWorkbook workbook = new XSSFWorkbook(stream);
100             ISheet sheet = workbook.GetSheet("Sheet1");
101             for (int row = 0; row <= sheet.LastRowNum; row++)
102             {
103                 if (sheet.GetRow(row) != null) //null is when the row only contains empty cells 
104                 {
105                     data.Add(sheet.GetRow(row).GetCell(0).StringCellValue);
106                 }
107             }
108             return data;
109         }
110     }
111     public class NpoiMemoryStream : MemoryStream
112     {
113         public NpoiMemoryStream()
114         {
115             AllowClose = true;
116         }
117 
118         public bool AllowClose { get; set; }
119         public override void Close()
120         {
121             if (AllowClose)
122                 base.Close();
123         }
124     }

 1 // actionresult 调用
 2 //
 3 using System.Web.Mvc;
 4  public class QueryController : Controller{
 5  public ActionResult Excel(string region)
 6  {
 7         System.IO.Stream stream = null;
 8 
 9         var dt = new System.Data.DataTable();
10         stream = ExcelHelper.ExportData(dt);
11 
12         return new FileStreamResult(stream, "application/ms-excel") { FileDownloadName = "Result.xlsx" };
13 
14  }
  }

 

1 //前台调用代码
2   function Download() {
3             var Region = $('#Region').children("option:selected").val();
4             window.location.href = '@Url.Content("~/Query/Excel?region=")'+Region; 
5 }
1  <div class="form-inline">
2                     <input type="button" value="Download" class="myButton create-btn" style="width:160px;" onclick="Download()" />
3                 </div>

 

posted @ 2019-02-26 13:36  王小二不在家  阅读(452)  评论(0)    收藏  举报