NPOI的调用

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using NPOI;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HPSF;
using NPOI.SS.Util;


namespace NPOITest
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();  //初始化一个hssfworkbook对象
            ISheet sheet = hssfworkbook.CreateSheet("Sheet1");
            FileStream file = new FileStream(@"D:\test.xls", FileMode.Create);    //定义路径

            
            IRow row1 = sheet.CreateRow(1);
            row1.CreateCell(0).SetCellValue(2);
            sheet.CreateRow(0).CreateCell(0).SetCellValue("这是第一个单元格");

            //增加一行，循环输出1到19列
            IRow row = sheet.CreateRow(2);
            for (int i = 0; i < 20; i++)
            {
                ICell cell = row.CreateCell(i);  //在第二行中创建单元格
                cell.SetCellValue(i);//循环往第二行的单元格中添加数据
            }

            //  批注
            IDrawing patr = sheet.CreateDrawingPatriarch();
            IComment comment1 = patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 4, 4));
            comment1.String = new HSSFRichTextString("这是批注");
            comment1.Author = "姚";
            ICell cell1 = sheet.CreateRow(1).CreateCell(1);
            cell1.CellComment = comment1;
            comment1.Visible = false;     //当焦点不在单元格隐藏批注

            //创建页眉和页脚
            ISheet s1 = hssfworkbook.CreateSheet("页眉和页脚");
            s1.CreateRow(0).CreateCell(1).SetCellValue("页脚页眉的测试");
            //页眉复制
            s1.Header.Center = "这是页眉";
            //页脚赋值
            s1.Footer.Left = "这是页脚左边";
            s1.Footer.Right = "这是页脚右边";

            //自定义格式化日期demo
            ISheet s2 = hssfworkbook.CreateSheet("设置单元格格式");
            ICell cells2 = s2.CreateRow(0).CreateCell(0);
            cells2.SetCellValue(new DateTime(2016, 5, 12));
            //set date format
            ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
            IDataFormat format = hssfworkbook.CreateDataFormat();     //创建一个IDataFormat实例
            cellStyle.DataFormat = format.GetFormat("yyyy年m月d日");   //使用format.GetFormat来获取相应的格式
            cells2.CellStyle = cellStyle;

            // 保留两位小数
            ICell cells22 = s2.CreateRow(1).CreateCell(0);
            //给cells22单位格赋值
            cells22.SetCellValue(1.2);
            ICellStyle cellStyles2 = hssfworkbook.CreateCellStyle();   
            //这里与上面有所不同，用的是HSSFDataFormat.GetBuiltinFormat()方法，之所以用这个，是因为0.00是Excel内嵌的格式
            cellStyles2.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
            cells22.CellStyle = cellStyles2;

            //货币类型
            ICell cell3 = s2.CreateRow(2).CreateCell(0);
            cell3.SetCellValue(20000);
            ICellStyle cellStyle3 = hssfworkbook.CreateCellStyle();
            IDataFormat format2 = hssfworkbook.CreateDataFormat();
            cellStyle3.DataFormat = format2.GetFormat("¥#,##0");
            cell3.CellStyle = cellStyle3;

            //百分比类型
            ICell cell4 = s2.CreateRow(3).CreateCell(0);
            cell4.SetCellValue(0.998);
            ICellStyle cellStyle4=hssfworkbook.CreateCellStyle();
            cellStyle4.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
            cell4.CellStyle = cellStyle4;

            //数字小写转大写
            ICell cell5 = s2.CreateRow(4).CreateCell(0);
            cell5.SetCellValue(123);
            ICellStyle cellStyle5 = hssfworkbook.CreateCellStyle();
            IDataFormat Format5 = hssfworkbook.CreateDataFormat();
            cellStyle5.DataFormat = Format5.GetFormat("[DbNum2][$-804]0");
            cell5.CellStyle = cellStyle5;
            //总结：HSSFDataFormat.GetFormat和HSSFDataFormat.GetBuiltinFormat的区别：
            //当使用Excel内嵌的（或者说预定义）的格式时，直接用HSSFDataFormat.GetBuiltinFormat静态方法即可。
            //当使用自己定义的格式时，必须先调用HSSFWorkbook.CreateDataFormat()，
            //因为这时在底层会先找有没有匹配的内嵌FormatRecord，如果没有就会新建一个FormatRecord，
            //所以必须先调用这个方法，然后你就可以用获得的HSSFDataFormat实例的GetFormat方法了，
            //当然相对而言这种方式比较麻烦，所以内嵌格式还是用HSSFDataFormat.GetBuiltinFormat静态方法更加直接一些。
            //不过自定义的格式也不是天马行空随便定义。


            //单元格合并
            ISheet sheet4 = hssfworkbook.CreateSheet("单元格合并");
            IRow rows4 = sheet4.CreateRow(2);
            ICell cells4 = rows4.CreateCell(2);

            ICellStyle styles5 = hssfworkbook.CreateCellStyle();
            styles5.Alignment = HorizontalAlignment.Center;
            IFont font = hssfworkbook.CreateFont();
            font.FontHeight = 20 * 20;
            styles5.SetFont(font);
            cells4.CellStyle = styles5;
            sheet4.AddMergedRegion(new CellRangeAddress(2,7,2,7));
            cells4.SetCellValue("合并单元格");



            Response.Write("<script>alert('提示：创建成功！')</script>");

            hssfworkbook.Write(file);                                //输出EXCEL   
            file.Close();
        }
    }
}