NPOI 生成Excel 的——常用

本文主要是介绍NPOI 生成Excel 的——常用，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

把自己常用的整理了出来，(*^__^*) 嘻嘻……

有关文件输出文件名乱码的问题

有设置Excel中的一些样式问题

有直接保存Excel的

更多NPOI的实例说明：http://www.cnblogs.com/tonyqus/archive/2009/04/12/1434209.html   这个可是别个大虾滴。

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;using System.IO;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;public partial class _Default : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e){DownLoadExcel();}/// <summary>/// 下载Excel/// </summary>public void DownLoadExcel(){/** ①：输出文档*/string fileName = DateTime.Now.ToString("yyyyMMdd") + "测试.xls";string UserAgent = Request.ServerVariables["http_user_agent"].ToLower();// Firfox和IE下输出中文名显示正常if (UserAgent.IndexOf("firefox") == -1){fileName = HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);}Response.ContentType = "application/vnd.ms-excel;charset=UTF-8";Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));Response.Clear();//写入内容到ExcelHSSFWorkbook hssfworkbook = writeToExcel();//将Excel内容写入到流中MemoryStream file = new MemoryStream();hssfworkbook.Write(file);//输出Response.BinaryWrite(file.GetBuffer());Response.End();/** ②：将文档保存到指定路径*/string destFileName = @"D:\test.xls";HSSFWorkbook hssfworkbook2 = writeToExcel();MemoryStream msfile = new MemoryStream();hssfworkbook.Write(msfile);System.IO.File.WriteAllBytes(destFileName, msfile.ToArray());}/// <summary>/// 写入内容到Excel中/// </summary>/// <returns></returns>private HSSFWorkbook writeToExcel(){//string template = "模板路径.xls";//FileStream file = new FileStream(template, FileMode.Open, FileAccess.Read);//HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);// 创建对象//HSSFSheet excelSheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);// 获得sheetHSSFWorkbook hssfworkbook = new HSSFWorkbook();HSSFSheet excelSheet = (HSSFSheet)hssfworkbook.CreateSheet("sheet1");Row row0 = excelSheet.CreateRow(0);Cell cell0 = CreateCell(0, row0);cell0.SetCellValue("NUM");cell0.CellStyle = GetCellStyle(hssfworkbook, CellBorderType.THIN, CellBorderType.THIN, CellBorderType.THIN, CellBorderType.THIN, HSSFColor.LIGHT_YELLOW.index, "#,##0");for (int i = 100, j = 1; i < 10000; i++, j++){Row row = CreateRow(j, excelSheet);Cell cell = CreateCell(0, row);cell.SetCellValue(i);cell.CellStyle = GetCellStyle(hssfworkbook, CellBorderType.THIN, CellBorderType.THIN, CellBorderType.THIN, CellBorderType.THIN, HSSFColor.LIGHT_GREEN.index, "#,##0");}return hssfworkbook;}/// <summary>/// 设置样式/// </summary>/// <param name="hssfworkbook"></param>/// <param name="borderLeft">左边框</param>/// <param name="borderBottom">下边框</param>/// <param name="borderRight">右边框</param>/// <param name="borderTop">上边框</param>/// <param name="fillforgeroundColor">背景填充色</param>/// <param name="dataFormat">数据格式</param>/// <returns></returns>private CellStyle GetCellStyle(HSSFWorkbook hssfworkbook, CellBorderType borderLeft, CellBorderType borderBottom, CellBorderType borderRight, CellBorderType borderTop, short fillforgeroundColor, string dataFormat){CellStyle styleInfo = hssfworkbook.CreateCellStyle();styleInfo.BorderLeft = borderLeft;styleInfo.BorderBottom = borderBottom;styleInfo.BorderRight = borderRight;styleInfo.BorderTop = borderTop;styleInfo.Alignment = HorizontalAlignment.CENTER;styleInfo.VerticalAlignment = VerticalAlignment.CENTER;styleInfo.FillForegroundColor = fillforgeroundColor;//设置填充色styleInfo.FillPattern = FillPatternType.SOLID_FOREGROUND;//设置填充色的时候必须设置这个styleInfo.DataFormat = HSSFDataFormat.GetBuiltinFormat(dataFormat);// 当前日期格式的需要以下这样设置//HSSFDataFormat format = (HSSFDataFormat)hssfworkbook.CreateDataFormat();//styleInfo.DataFormat = format.GetFormat("yyyy年m月d日");return styleInfo;}/// <summary>/// 创建行对象/// </summary>/// <param name="rowID"></param>/// <param name="excelSheet"></param>/// <returns></returns>private Row CreateRow(int rowID, HSSFSheet excelSheet){Row row = excelSheet.GetRow(rowID);if (row == null){row = excelSheet.CreateRow(rowID);}return row;}/// <summary>/// 创建列对象/// </summary>/// <param name="rowID"></param>/// <param name="excelSheet"></param>/// <returns></returns>private Cell CreateCell(int cellID, Row row){Cell cell = row.GetCell(cellID);if (cell == null){cell = row.CreateCell(cellID);}return cell;}}

最近被Excel的給整疯了。

整理一下，希望可以给人帮助！

这篇关于NPOI 生成Excel 的——常用的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---