{"id":6785,"date":"2018-11-06T09:24:36","date_gmt":"2018-11-06T01:24:36","guid":{"rendered":"http:\/\/www.yeyaorong.cn\/?p=6785"},"modified":"2018-11-06T15:20:07","modified_gmt":"2018-11-06T07:20:07","slug":"vs2017%e7%94%a8nuget%e5%ae%89%e8%a3%85npoi%e5%ae%9e%e7%8e%b0datatable%e5%af%bc%e5%87%ba%e5%88%b0excel%e5%b9%b6%e4%b8%8b%e8%bd%bd","status":"publish","type":"post","link":"http:\/\/www.yeyaorong.cn\/?p=6785","title":{"rendered":"VS2017\u7528nuget\u5b89\u88c5NPOI\u5b9e\u73b0datatable\u5bfc\u51fa\u5230excel\u5e76\u4e0b\u8f7d"},"content":{"rendered":"

1.\u7528nuget\u547d\u4ee4\u884c\u5b89\u88c5NPOI\u5f15\u7528\uff1a<\/p>\n

Install-Package NPOI -Version 2.4.0<\/span>\uff1b<\/p>\n

2.\u65b9\u6cd5\uff1a<\/p>\n

public void ToExcelFile(string filename, DataTable dt, KeyValuePair<string, string>[] column_names)<\/span>
\n{<\/span>
\ntry<\/span>
\n{<\/span>
\n\/\/ \u6839\u636e\u6a21\u677f\u6587\u4ef6\u521b\u5efa\u526f\u672c<\/span>
\n\/\/string filePath = Server.MapPath(this.TemporaryFolder + Path.DirectorySeparatorChar + Guid.NewGuid().ToString() + “.xls”);<\/span>
\nMemoryStream ms = new MemoryStream();<\/span>
\nvar book = new HSSFWorkbook();<\/span>
\nvar sheet = book.CreateSheet(Path.GetFileNameWithoutExtension(filename));<\/span>
\n
\nvar row_h = sheet.CreateRow(0);<\/span>
\nvar style_h = book.CreateCellStyle();<\/span>
\nfor (int i = 0; i < column_names.Length; i++)<\/span>
\n{<\/span>
\nvar cell = row_h.CreateCell(i, NPOI.SS.UserModel.CellType.String);<\/span>
\ncell.SetCellValue(column_names[i].Value);<\/span>
\nstyle_h.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;<\/span>
\nstyle_h.FillPattern = NPOI.SS.UserModel.FillPattern.FineDots;<\/span>
\nstyle_h.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;<\/span>
\ncell.CellStyle = style_h;<\/span>
\n}<\/span>
\nfor (int i = 0; i < dt.Rows.Count; i++)<\/span>
\n{<\/span>
\nvar row = sheet.CreateRow(i + 1);<\/span>
\nfor (int j = 0; j < column_names.Length; j++)<\/span>
\n{<\/span>
\nNPOI.SS.UserModel.ICell cell = null;<\/span><\/p>\n

if (dt.Columns[column_names[j].Key].DataType == typeof(decimal) || dt.Columns[column_names[j].Key].DataType == typeof(Int64) || dt.Columns[column_names[j].Key].DataType == typeof(Int32) || dt.Columns[column_names[j].Key].DataType == typeof(Int16) || dt.Columns[column_names[j].Key].DataType == typeof(double) || dt.Columns[column_names[j].Key].DataType == typeof(float))<\/span>
\n{<\/span>
\ncell = row.CreateCell(j, NPOI.SS.UserModel.CellType.Numeric);<\/span>
\nif (!(dt.Rows[i][column_names[j].Key] == DBNull.Value || dt.Rows[i][column_names[j].Key] == null))<\/span>
\n{<\/span>
\ncell.SetCellValue((double)decimal.Parse(dt.Rows[i][column_names[j].Key].ToString()));<\/span>
\n}<\/span>
\n}<\/span>
\nelse<\/span>
\n{<\/span>
\ncell = row.CreateCell(j, NPOI.SS.UserModel.CellType.String);<\/span>
\nif (!(dt.Rows[i][column_names[j].Key] == DBNull.Value || dt.Rows[i][column_names[j].Key] == null))<\/span>
\n{<\/span>
\ncell.SetCellValue(dt.Rows[i][column_names[j].Key].ToString());<\/span>
\n}<\/span>
\n}<\/span>
\n}<\/span>
\n}<\/span>
\nbook.Write(ms);<\/span>
\nms.Flush();<\/span><\/p>\n

\/\/ \u8f93\u51fa\u526f\u672c\u7684\u4e8c\u8fdb\u5236\u5b57\u8282\u6d41<\/span>
\nthis.Response.Clear();<\/span>
\nthis.Response.ClearContent();<\/span>
\nthis.Response.ClearHeaders();<\/span><\/p>\n

this.Response.ContentType = “application\/ms-excel”;<\/span>
\nthis.Response.AppendHeader(“Content-Disposition”, “attachment;filename=” + this.Server.UrlPathEncode(filename));<\/span>
\nthis.Response.BinaryWrite(ms.ToArray());<\/span>
\n\/\/ \u5220\u9664\u526f\u672c<\/span>
\n\/\/this.Response.End();<\/span>
\nbook.Close();<\/span>
\n}<\/span>
\ncatch (Exception)<\/span>
\n{<\/span>
\nthrow;<\/span>
\n}<\/span>
\n}<\/span><\/p>\n

3.\u8c03\u7528\uff1a<\/p>\n

KeyValuePair<string, string>[] column_names = new KeyValuePair<string, string>[2];<\/span>
\n\/\/\u8bbe\u7f6e\u6570\u636e\u8868\u5217\u540d\u548c\u5bfc\u51fa\u7684excel\u5217\u540d\u5bf9\u5e94\u5173\u7cfb<\/span>
\ncolumn_names[0] = new KeyValuePair<string, string>(“id”,”\u5e8f\u53f7”);<\/span>
\ncolumn_names[1] = new KeyValuePair<string, string>(“faultnumber”, “\u7f3a\u9677\u5355\u53f7”);<\/span><\/p>\n

ToExcelFile( “123.xls”, dt4, column_names);<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

1.\u7528nuget\u547d\u4ee4\u884c\u5b89\u88c5NPOI\u5f15\u7528\uff1a Install-Package NPOI -Version 2.4. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"class_list":["post-6785","post","type-post","status-publish","format-standard","hentry","category-coding"],"_links":{"self":[{"href":"http:\/\/www.yeyaorong.cn\/index.php?rest_route=\/wp\/v2\/posts\/6785","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.yeyaorong.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.yeyaorong.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.yeyaorong.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.yeyaorong.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6785"}],"version-history":[{"count":6,"href":"http:\/\/www.yeyaorong.cn\/index.php?rest_route=\/wp\/v2\/posts\/6785\/revisions"}],"predecessor-version":[{"id":6791,"href":"http:\/\/www.yeyaorong.cn\/index.php?rest_route=\/wp\/v2\/posts\/6785\/revisions\/6791"}],"wp:attachment":[{"href":"http:\/\/www.yeyaorong.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6785"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.yeyaorong.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6785"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.yeyaorong.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6785"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}