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();
}
}
}
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();
}
}
}