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>
