您现在的位置是:首页>博客详情

NPOI 1.2教程 - 5 高级功能

FreshMan 2019年04月13日 23:53 NPOI,Excel,Office 334

简介在Excel中,可以通过调整右下角的滚动条来调整Sheet的显示比例,设置密码,生成下拉式菜单等复杂功能。

2.6.1 用NPOI操作EXCEL--调整表单显示比例

在Excel中,可以通过调整右下角的滚动条来调整Sheet的显示比例。如图:

在NPOI中,也能通过代码实现这样的功能,并且代码非常简单:

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
sheet1.CreateRow(
0).CreateCell(0).SetCellValue("This is a test.");
//50% zoom
sheet1.SetZoom(1,2); 

我们发现,SetZoom有两个参数。其中第一个参数表示缩放比例的分子,第二个参数表示缩放比例的分母,所以SetZoom(1,2)就表示缩小到1/2,也就是50%。代码执行后生成的Excel样式如下:


如果将SetZoom的参数改成(2,1),代码执行后生成的Excel样式如下,表示扩大两倍:


2.6.2 用NPOI操作EXCEL--设置密码

有时,我们可能需要某些单元格只读,如在做模板时,模板中的数据是不能随意让别人改的。在Excel中,可以通过“审阅->保护工作表”来完成,如下图:
     
 那么,在NPOI中有没有办法通过编码的方式达到这一效果呢?答案是肯定的。

复制代码

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

HSSFRow row1 
= sheet1.CreateRow(0);
HSSFCell cel1 
= row1.CreateCell(0);
HSSFCell cel2 
= row1.CreateCell(1);

HSSFCellStyle unlocked 
= hssfworkbook.CreateCellStyle();
unlocked.IsLocked 
= false;

HSSFCellStyle locked 
= hssfworkbook.CreateCellStyle();
locked.IsLocked 
= true;

cel1.SetCellValue(
"没被锁定");
cel1.CellStyle 
= unlocked;

cel2.SetCellValue(
"被锁定");
cel2.CellStyle 
= locked;

sheet1.ProtectSheet(
"password");

复制代码

正如代码中所看到的,我们通过设置CellStype的ISLocked为True,表示此单元格将被锁定。相当于在Excel中执行了如下操作:

然后通过ProtectSheet设置密码。

执行结果如下:

没被锁定的列可以任意修改。
 

被锁定的列不能修改。
 


输入密码可以解除锁定。

2.6.3 组合行、列

可能我们在过去生成Excel文件的时候根本不会用这个功能,也没办法用,因为cvs法和html法没办法控制这些东西。这里简单的介绍一下什么叫做组合:

组合分为行组合和列组合,所谓行组合,就是让n行组合成一个集合,能够进行展开和合拢操作。就是用于控制行组合折叠的图标,图中上部就是用于控制列组合的,是不是有点像TreeView中的折叠节点?很多时候由于数据太多,为了让用户对于大量数据一目了然,我们可以使用行列组合来解决显示大纲,这和Visual Studio 里面的region的概念是类似的。

细心的朋友可能已经注意到了,我们其实可以对一行做多次组合操作,这就是分级显示的概念,图中就把行2-3分为2个组合,第2行到第4行为一个组合,第2行到第5行一个组合,所以是分两级。

在NPOI中,要实现分组其实并不难,你只需要调用HSSFSheet.GroupRow和HSSFSheet.GroupColumn这两个方法就可以了。

首先我们来看HSSFSheet.GroupRow,GroupRow有2个参数,分别是fromRow和toRow,表示起始行号和结束行号,这些行号都是从0开始算起的。

HSSFWorkbookhssfworkbook =newHSSFWorkbook();

HSSFSheet s =hssfworkbook.CreateSheet("Sheet1");

s.GroupRow(1,3);

上面的代码把第2行到第4行做了组合。

要组合列,其实代码很相似,如下所示:

s.GroupColumn(1,3)

上面的代码把B至D列做了组合。

正如上图中Excel的“分级显示”面板所示,有“组合”,也一定有“取消组合”,NPOI中你可以用HSSFSheet.UngroupRow和HSSFSheet.UngroupColumn,参数和GroupXXX是一样的,如果要取消第2到第4行的组合,就可以用下面的代码:

s.UngroupColumn(1,3)

相关范例请见NPOI 1.2正式版中的GroupRowAndColumnInXls项目。


2.6.4 用NPOI操作EXCEL--锁定列

在Excel中,有时可能会出现列数太多或是行数太多的情况,这时可以通过锁定列来冻结部分列,不随滚动条滑动,方便查看。在Excel中设置冻结列的方法如下:


同样,利用NPOI,通过代码也能实现上面的效果:

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFRow row1 
= sheet1.CreateRow(0);
row1.CreateCell(
0).SetCellValue("冻结列");
sheet1.CreateFreezePane(
1010);


代码执行结果如下:


下面对CreateFreezePane的参数作一下说明:
第一个参数表示要冻结的列数;
第二个参数表示要冻结的行数,这里只冻结列所以为0;
第三个参数表示右边区域可见的首列序号,从1开始计算;
第四个参数表示下边区域可见的首行序号,也是从1开始计算,这里是冻结列,所以为0;

举例说明也许更好理解,将各参数设置为如下:

sheet1.CreateFreezePane(2,0,5,0);

 

得到的效果如下图:

注意图中C、D和E列默认是看不到的,滚动才看得到,这就是第三个参数5起了作用,是不是很好理解了呢:)

接下来,看一下冻结行的效果。将上面的代码稍作修改:

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFRow row1 
= sheet1.CreateRow(0);
row1.CreateCell(
0).SetCellValue("冻结行");
sheet1.CreateFreezePane(
0101);

执行后生成的Excel文件效果见下图:


那么,如果要行和列同时冻结该怎么做呢?聪明的你一定能想得到,呵呵~~


2.6.6 用NPOI操作EXCEL--设置初始视图的行、列

有些时候,我们可能希望生成的Excel文件在被打开的时候自动将焦点定位在某个单元格或是选中某个区域中。在NPOI中可以通过SetAsActiveCell和SetActiveCellRange等几个方法实现。

首先我们看一下设置初始视图中选中某个单元格的方法:

复制代码

//use HSSFCell.SetAsActiveCell() to select B6 as the active column
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet A");
CreateCellArray(sheet1);
sheet1.GetRow(
5).GetCell(1).SetAsActiveCell();
//set TopRow and LeftCol to make B6 the first cell in the visible area
sheet1.TopRow = 5;
sheet1.LeftCol 
= 1;

复制代码

其中CreateCellArray(sheet1)方法用来写示范数据,其代码为(下同):

复制代码

static void CreateCellArray(HSSFSheet sheet)
{
    
for (int i = 0; i < 300; i++)
    {
        HSSFRow row
=sheet.CreateRow(i);
        
for (int j = 0; j < 150; j++)
        {
            HSSFCell cell 
= row.CreateCell(j);
            cell.SetCellValue(i
*j);
        }
    }
}

复制代码

生成的Excel打开时效果如下,注意B6为默认选中状态,TopRow和LeftCol设置B6为当前可见区域的第一个单元格:

如果不设置TopRow和LeftCol属性,默认的可见域的第一个单元格为A1,如下是另一种设置活动单元格的方法,但没有设置此Sheet的TopRow和LeftCol:

HSSFSheet sheet2 = hssfworkbook.CreateSheet("Sheet B");
sheet2.Sheet.SetActiveCell(
15);

对应生成的Excel显示为:

除了设置某个单元格为选中状态外,还NPOI可以设置某个区域为选中状态:

 

//use Sheet.SetActiveCellRange to select a cell range
HSSFSheet sheet3 = hssfworkbook.CreateSheet("Sheet C");
CreateCellArray(sheet3);
sheet3.Sheet.SetActiveCellRange(
2515);

以上代码设置了Sheet C的选中区域为B3:F6:

还有更强大的,设置多个选中区域:

复制代码

//use Sheet.SetActiveCellRange to select multiple cell ranges
HSSFSheet sheet4 = hssfworkbook.CreateSheet("Sheet D");
CreateCellArray(sheet4);
List
<CellRangeAddress8Bit> cellranges = new List<CellRangeAddress8Bit>();
cellranges.Add(
new CellRangeAddress8Bit(1,3,2,5));
cellranges.Add(
new CellRangeAddress8Bit(6,7,8,9));
sheet4.Sheet.SetActiveCellRange(cellranges,
1,6,9);

复制代码

如果一个Excel文件中有多个Sheet,还可以通过如下语句设置打开时的初始Sheet:

hssfworkbook.ActiveSheetIndex = 2;


2.6.7 用NPOI操作EXCEL--数据有效性

在有些情况下(比如Excel引入),我们可能不允许用户在Excel随意输入一些无效数据,这时就要在模板中加一些数据有效性的验证。在Excel中,设置数据有效性的方步骤如下:
(1)先选定一个区域;
(2)在菜单“数据数据有效性”中设置数据有效性验证(如图)。


同样,利用NPOI,用代码也可以实现:

复制代码

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

sheet1.CreateRow(
0).CreateCell(0).SetCellValue("日期列");
CellRangeAddressList regions1 
= new CellRangeAddressList(16553500);
DVConstraint constraint1 
= DVConstraint.CreateDateConstraint(DVConstraint.OperatorType.BETWEEN, "1900-01-01""2999-12-31""yyyy-MM-dd");
HSSFDataValidation dataValidate1 
= new HSSFDataValidation(regions1, constraint1);
dataValidate1.CreateErrorBox(
"error""You must input a date.");
sheet1.AddValidationData(dataValidate1);

复制代码

上面是一个在第一列要求输入1900-1-1至2999-12-31之间日期的有效性验证的例子,生成的Excel效果如下,当输入非法时将给出警告:


下面对刚才用到的几个方法加以说明:
      CellRangeAddressList类表示一个区域,构造函数中的四个参数分别表示起始行序号,终止行序号,起始列序号,终止列序号。所以第一列所在区域就表示为:

//所有序号都从零算起,第一行标题行除外,所以第一个参数是1,65535是一个Sheet的最大行数
new CellRangeAddressList(16553500);

      另外,CreateDateConstraint的第一个参数除了设置成DVConstraint.OperatorType.BETWEEN外,还可以设置成如下一些值,大家可以自己一个个去试看看效果:

      最后,dataValidate1.CreateErrorBox(title,text),用来创建出错时的提示信息。第一个参数表示提示框的标题,第二个参数表示提示框的内容。

理解了上面这些,创建一个整数类型的有效性验证也不难实现:

复制代码

sheet1.CreateRow(0).CreateCell(1).SetCellValue("数值列");
CellRangeAddressList regions2 
= new CellRangeAddressList(16553511);
DVConstraint constraint2 
= DVConstraint.CreateNumericConstraint(DVConstraint.ValidationType.INTEGER,DVConstraint.OperatorType.BETWEEN, "0""100");
HSSFDataValidation dataValidate2 
= new HSSFDataValidation(regions2, constraint2);
dataValidate2.CreateErrorBox(
"error""You must input a numeric between 0 and 100.");
sheet1.AddValidationData(dataValidate2);

复制代码

 

生成的Excel效果为:


下一节我们将学习利用数据有效性创建下拉列表的例子。


2.6.8 用NPOI操作EXCEL--生成下拉列表

上一节我们讲了简单的数据有效性验证,这一节我们学习一下数据有效性的另一个应用--下拉列表。在Excel中,并没有类似Web中的下拉控件,其下拉效果是通过数据有效性来实现的。设置步骤为:
(1)选定一个要生成下拉列表的区域;
(2)设置数据有效性为序列,并在来源中填充可选下拉的值,用“,”隔开(如图)。

对应的效果为:


同样,利用NPOI代码也可以实现上面的效果:

复制代码

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

CellRangeAddressList regions 
= new CellRangeAddressList(06553500);
DVConstraint constraint 
= DVConstraint.CreateExplicitListConstraint(new string[] { "itemA""itemB""itemC" });
HSSFDataValidation dataValidate 
= new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);

复制代码

下面对代码作一下简要说明:
先设置一个需要提供下拉的区域,关于CellRangeAddressList构造函数参数的说明请参见上一节

CellRangeAddressList regions = new CellRangeAddressList(06553500);

然后将下拉项作为一个数组传给CreateExplicitListConstraint作为参数创建一个约束,根据要控制的区域和约束创建数据有效性就可以了。

      但是这样会有一个问题:Excel中允许输入的序列来源长度最大为255个字符,也就是说当下拉项的总字符串长度超过255是将会出错。那么如果下拉项很多的情况下应该怎么处理呢?答案是通过引用的方式。步骤如下:
先创建一个Sheet专门用于存储下拉项的值,并将各下拉项的值写入其中:

HSSFSheet sheet2 = hssfworkbook.CreateSheet("ShtDictionary");
sheet2.CreateRow(
0).CreateCell(0).SetCellValue("itemA");
sheet2.CreateRow(
1).CreateCell(0).SetCellValue("itemB");
sheet2.CreateRow(
2).CreateCell(0).SetCellValue("itemC");

然后定义一个名称,指向刚才创建的下拉项的区域:

HSSFName range = hssfworkbook.CreateName();
range.Reference 
= "ShtDictionary!$A1:$A3";
range.NameName 
= "dicRange";

最后,设置数据约束时指向这个名称而不是字符数组:

复制代码

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
CellRangeAddressList regions 
= new CellRangeAddressList(06553500);

DVConstraint constraint 
= DVConstraint.CreateFormulaListConstraint("dicRange");
HSSFDataValidation dataValidate 
= new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);

复制代码

执行这段代码,生成的Excel效果如下:

在名称管理器中会发现有一个名为"dicRange"的名称,指向"ShtDictionary!$A1:$A3"的下拉项区域:

在数据有效性中会发现来源变成了"=dicRange",指向上面定义的名称。而不是以前的"itemA,itemB,itemC":