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();
        //写入内容到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的給整疯了。

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