你的位置:首页 > ASP.net教程

[ASP.net教程]NPOI控件的使用导出excel文件和word文件


 1 public HttpResponseMessage GetReportRateOutput(DateTime? begin_time = null, DateTime? end_time = null, string type = "大浮标") 2     { 3       var dataList = _adapter.DataReportRate(type, begin_time, end_time).ToList(); 4  5       NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); 6       NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("“" + type + "”到报率统计"); 7  8       // 第一列 9       NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);10       row.CreateCell(0).SetCellValue("站名");11       row.CreateCell(1).SetCellValue("应到报数");12       row.CreateCell(2).SetCellValue("叶绿素到报率");13       row.CreateCell(3).SetCellValue("气压到报率");14       row.CreateCell(4).SetCellValue("风速到报率");15       row.CreateCell(5).SetCellValue("气温到报率");16       row.CreateCell(6).SetCellValue("水温到报率");17       row.CreateCell(7).SetCellValue("波高到报率");18       row.CreateCell(8).SetCellValue("盐度到报率");19 20       NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();21       style.Alignment = HorizontalAlignment.Center;22       style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index;23       style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index;24       row.GetCell(0).CellStyle = style;25       row.GetCell(1).CellStyle = style;26       row.GetCell(2).CellStyle = style;27       row.GetCell(3).CellStyle = style;28       row.GetCell(4).CellStyle = style;29       row.GetCell(5).CellStyle = style;30       row.GetCell(6).CellStyle = style;31       row.GetCell(7).CellStyle = style;32       row.GetCell(8).CellStyle = style;33       sheet.SetColumnWidth(0, 15 * 256);34       sheet.SetColumnWidth(1, 15 * 256);35       sheet.SetColumnWidth(2, 15 * 256);36       sheet.SetColumnWidth(3, 15 * 256);37       sheet.SetColumnWidth(4, 15 * 256);38       sheet.SetColumnWidth(5, 15 * 256);39       sheet.SetColumnWidth(6, 15 * 256);40       sheet.SetColumnWidth(7, 15 * 256);41       sheet.SetColumnWidth(8, 15 * 256);42 43 44       int index = 1;45       foreach (DataTransferStatus dataInfo in dataList)46       {47         // 第二列48         NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(index);49 50         row2.CreateCell(0).SetCellValue(dataInfo.name);51         row2.CreateCell(1).SetCellValue(dataInfo.report_number);52         row2.CreateCell(2).SetCellValue(dataInfo.phyll_report_rate + "%");53         row2.CreateCell(3).SetCellValue(dataInfo.pressure_report_rate + "%");54         row2.CreateCell(4).SetCellValue(dataInfo.speed_report_rate + "%");55         row2.CreateCell(5).SetCellValue(dataInfo.temp_report_rate + "%");56         row2.CreateCell(6).SetCellValue(dataInfo.water_report_rate + "%");57         row2.CreateCell(7).SetCellValue(dataInfo.wave_report_rate + "%");58         row2.CreateCell(8).SetCellValue(dataInfo.salt_report_rate + "%");59 60         index++;61       }62       System.IO.MemoryStream stream = new System.IO.MemoryStream();63       book.Write(stream);64       stream.Seek(0, SeekOrigin.Begin);65       book = null;66       HttpResponseMessage mResult = new HttpResponseMessage(System.Net.HttpStatusCode.OK);67       mResult.Content = new StreamContent(stream);68       mResult.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");69       mResult.Content.Headers.ContentDisposition.FileName = type + "_到报率统计" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";70       mResult.Content.Headers.ContentType = new MediaTypeHeaderValue("application/ms-excel");71 72       return mResult;73     }

1.首先导入NPOI的dll,这个在网上有很多自行下载。先去官网:http://npoi.codeplex.com/下载需要引入dll(可以选择.net2.0或者.net4.0的dll),然后在网站中添加引用。

2.引用命名空间 using NPOI.SS.UserModel

3.(此处以导出excel为例)我们要明白一个完整的excel文件是由哪几部分组成的!

(1)一张工作薄BOOK,一张工作表sheet,然后包括ROW行,Column列,Cell单元格

4.

分别创建出每一部分
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();//NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("“" + type + "”到报率统计");NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);

设置excel文件的样式
NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle();style.Alignment = HorizontalAlignment.Center;style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index;
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index;