NPOI 生成Excel 的——常用

来源：互联网发布：windows 活动目录编辑：程序博客网时间：2024/05/01 19:05
把自己常用的整理了出来，(*^__^*) 嘻嘻……
有关文件输出文件名乱码的问题
有设置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();        //写入内容到Excel        HSSFWorkbook 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);// 获得sheet        HSSFWorkbook 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的給整疯了。
整理一下，希望可以给人帮助！