1、添加引用NPOI.dll
2、cs文件头部添加
1
2
3
|
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using System.IO; |
3、代码如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
|
using System; using System.Collections.Generic; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Configuration; using WSC.Framework; using System.Data; using WSC.Common; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using System.IO; public partial class WorkManage_SMT_SMTMaintain : WSC.FramePage { SQLHelper sql = new SQLHelper(ConfigurationManager.AppSettings[ "LocalConnectionString" ].ToString()); protected void Page_Load( object sender, EventArgs e) { if (!IsPostBack) { } } protected void btnReport_Click( object sender, EventArgs e) { string strSql = string .Format( @" select smtpicsmdl.model,smtmdl.submodel,pcbapn,PrdType,cycle,cast((12*3600/cycle) as int) as 'rate',onlineMan,offlineMan,reserve3,ptype_desc,minsqg,maxsqg from smtmdl left join smtpicsmdl on smtpicsmdl.submodel=smtmdl.submodel where pcbapn = '{0}' order by smtpicsmdl.model asc,smtpicsmdl.submodel asc,PrdType asc" , this .txtMdmitem.Text.Trim()); DataTable dt = sql.Query(strSql); string strFileName = "SMT机种信息" + DateTime.Now.ToString( "yyyyMMddHHmmss" ); ExportExcel(dt, strFileName, "SMT机种信息" ); } /// <summary> /// DataTable导出Excel /// </summary> /// <param name="dt">datatable数据源</param> /// <param name="strFileName">文件名</param> /// <param name="strSheetName">工作簿名</param> public void ExportExcel(DataTable dt, string strFileName, string strSheetName) { HSSFWorkbook book = new HSSFWorkbook(); ISheet sheet = book.CreateSheet(strSheetName); IRow headerrow = sheet.CreateRow(0); ICellStyle style = book.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0); string strColumns = "主机种,子机种,5E料号,产线类型,CT(S),rate/12H,线上人力,线外人力,总人力,面别,刮刀下限,刮刀上限" ; string [] strArry = strColumns.Split( ',' ); for ( int i = 0; i < strArry.Length; i++) { dataRow.CreateCell(i).SetCellValue(strArry[i]); dataRow.GetCell(i).CellStyle = style; } for ( int i = 0; i < dt.Rows.Count; i++) { dataRow = (HSSFRow)sheet.CreateRow(i + 1); for ( int j = 0; j < dt.Columns.Count; j++) { string ValueType = "" ; string Value = "" ; if (dt.Rows[i][j].ToString() != null ) { ValueType = dt.Rows[i][j].GetType().ToString(); Value = dt.Rows[i][j].ToString(); } switch (ValueType) { case "System.String" : //字符串类型 dataRow.CreateCell(j).SetCellValue(Value); break ; case "System.DateTime" : //日期类型 System.DateTime dateV; System.DateTime.TryParse(Value, out dateV); dataRow.CreateCell(j).SetCellValue(dateV); break ; case "System.Boolean" : //布尔型 bool boolV = false ; bool .TryParse(Value, out boolV); dataRow.CreateCell(j).SetCellValue(boolV); break ; case "System.Int16" : //整型 case "System.Int32" : case "System.Int64" : case "System.Byte" : int intV = 0; int .TryParse(Value, out intV); dataRow.CreateCell(j).SetCellValue(intV); break ; case "System.Decimal" : //浮点型 case "System.Double" : double doubV = 0; double .TryParse(Value, out doubV); dataRow.CreateCell(j).SetCellValue(doubV); break ; case "System.DBNull" : //空值处理 dataRow.CreateCell(j).SetCellValue( "" ); break ; default : dataRow.CreateCell(j).SetCellValue( "" ); break ; } dataRow.GetCell(j).CellStyle = style; //设置宽度 sheet.SetColumnWidth(j, (Value.Length + 10) * 256); } } MemoryStream ms = new MemoryStream(); book.Write(ms); Response.AddHeader( "Content-Disposition" , string .Format( "attachment; filename={0}.xls" , HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8))); Response.BinaryWrite(ms.ToArray()); Response.End(); book = null ; ms.Close(); ms.Dispose(); } } |
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,同时也希望多多支持服务器之家!
原文链接:http://www.cnblogs.com/lengzhan/p/6042911.html