## **实现用户自定义Excel模板** **需求背景：** 在Excel的实际使用过程中，动态生成报表、导出报表等都是最终用户常常使用的功能，使用常规的方式针对模板中具体的每个单元格编程，动态的插入数据库中数据到Excel报表模板的每一个单元格，就可以实现动态报表功能。但是很多时候用户报表的样式不是一成不变的，如某公司2015年的产品报表希望在2014年的产品报表基础上，进行一些报表文本颜色、数据显示位置等方面的调整，2014年报表模板中“合计”数据原来位于单元格：A50，调整后，2015年报表模板中的“合计”数据单元格位于：A80。由于生成报表的程序代码是针对报表模板中的每一个具体的单元格编程的，模板中数据项所在单元格的位置发生了改变，就需要同步修改填充数据的程序代码，原来给A50单元格填充数据的代码需要修改为操作A80单元格。若是每次Excel模板的调整都需要开发人员重新修改代码来实现的话，那就是一项永远不会完成的工作了。 ### **编辑Excel模板：定义名称** ---- >[info]定义名称：Excel中有一个特别好的工具就是“定义名称”，顾名思义，就是为一个单元格或者区域定义一个名称，这样的话，我们在之后的程序控制时可以很方便的用所定义的名称进行代码编写。 下面以给一个区域定义名称为例介绍一下Excel定义名称的操作步骤： 1. 在本地磁盘上打开一个Excel文件模板，选中需要填充数据的单元格区域，如B4:F13，如下图所示； :-:  2. 在选中的单元格区域上，“右击→定义名称”（或者点击Office工具栏上的“公式→定义名称“），在名称输入框中输入该区域的名称，如命名为“report”，点击“确定”按钮即可。如下图所示。 :-:  :-:  3. 若是需要删除、修改定义好的单元格区域，可点击Office工具栏上的“公式→名称管理器“来对名称进行管理，如下图所示。 :-:  ### **PageOffice操作Excel模板中定义了名称的单元格和区域** ---- 在PageOffice开发平台下，为了更好的在Excel模板中动态的、灵活的填充数据，专门开发了相应的程序接口，提供了完美解决上述问题的方法——openCellByDefinedName()和openTableByDefinedName()。在代码实现过程中，通过灵活的运用这两个方法，即可完美的实现在不修改代码的情况下，满足最终用户自定义修改Excel模板的需求。 **一、openCellByDefinedName和openTableByDefinedName的定义说明** 这两个方法用来实现操作用户自定义Excel模板中的。其中openCellByDefinedName()方法用来打开Excel中具有指定名称的单元格，并返回 Cell 对象。openTableByDefinedName()方法用来打开具有指定名称的单元格区域（一般为连续的多个单元格，在PageOffice的概念里称这块区域为一个Table），并返回 Table 对象。 Java方法： ~~~ openCellByDefinedName(String definedName); openTableByDefinedName(String definedName, int rowCount, int colCount, boolean autoIncrease); ~~~ ASP.NET方法： ~~~ public Cell OpenCellByDefinedName( string DefinedName ) public Table OpenTableByDefinedName( string DefinedName, int RowCount, int ColCount, bool AutoIncrease ) ~~~ **二、编写代码给Excel模板中的表格填充数据** Java部分代码如下： ~~~ Workbook workBook = new Workbook(); Sheet sheet = workBook.openSheet("Sheet1"); //定义Table对象，参数“report”就是Excel模板中定义的单元格区域的名称 Table table = sheet.openTableByDefinedName("report", 10, 5, false); //给区域中的单元格赋值 table.getDataFields().get(0).setValue("轮胎"); table.getDataFields().get(1).setValue("100"); table.getDataFields().get(2).setValue("120"); table.getDataFields().get(3).setValue("500"); table.getDataFields().get(4).setValue("120%"); table.nextRow(); //如果循环填充数据的话，执行下一行 table.close(); //定义单元格对象，参数“year”就是Excel模板中定义的单元格的名称 Cell cellYear = sheet.openCellByDefinedName("year"); Calendar c=new GregorianCalendar(); int year=c.get(Calendar.YEAR);//获取年份 cellYear.setValue(year + "年"); Cell cellName = sheet.openCellByDefinedName("name"); cellName.setValue("张三"); poCtrl1.setWriter(workBook); …… ~~~ ASP.NET部分代码如下： ~~~ PageOffice.ExcelWriter.Workbook wk = new PageOffice.ExcelWriter.Workbook(); PageOffice.ExcelWriter.Sheet sheet = wk.OpenSheet("Sheet1"); //定义Table对象，参数“report”就是Excel模板中定义的单元格区域的名称 PageOffice.ExcelWriter.Table table = sheet.OpenTableByDefinedName("report", 10, 5, false); //给区域中的单元格赋值 table.DataFields[0].Value = "轮胎"; table.DataFields[1].Value = "100"; table.DataFields[2].Value = "120"; table.DataFields[3].Value = "500"; table.DataFields[4].Value = "120%"; table.NextRow();//如果循环填充数据的话，执行下一行 table.Close(); //定义单元格对象，参数“year”就是Excel模板中定义的单元格的名称 PageOffice.ExcelWriter.Cell cellYear = sheet.OpenCellByDefinedName("year"); cellYear.Value="2015年"; PageOffice.ExcelWriter.Cell cellName = sheet.OpenCellByDefinedName("name"); cellName.Value = "张三"; PageOfficeCtrl1.SetWriter(wk);// 注意不要忘记此代码，如果缺少此句代码，不会赋值成功。 …… ~~~ 在填充数据的代码不做任何修改的情况下，打开两个不同的Excel模板并填充数据的效果： 1. 打开模板一并填充数据，实现的效果如图所示 :-:  2. 打开模板二并填充数据，实现的效果如图10所示： :-:  可以看出在不需修改代码的情况下，用户可以根据实际需求自行定义Excel模板。只要Excel模板中有代码中所涉及到的定义名称（如“report”、“year”、“name”），那么无论定义了该名称的单元格区域的位置、大小怎么变化，程序都会自动在变化后的位置填充数据。而如果程序代码中涉及到的定义名称在Excel模板中不存在的话，那么对该对象的所有操作将被自动忽略，打开的Excel文档中也不会再显示相应的信息。 ### **优势总结** ----- openCellByDefinedName()和openTableByDefinedName()方法相比于openCell()和openTable()方法的具体优势在于： 1. 可以避免同一个Excel工作薄中有多个表格时，上面的表格数据行增长后与下面的表格数据出现互相覆盖的问题。 假如在一个Excel模板的同一个工作薄中，使用openTable(rangeAddress)方法打开两个不同的Table，分别为Table1（数据填充范围：第4-7行，行数：4行）、Table2（数据填充范围：第13-16行）。而要动态填充到Table1里的实际数据行数为12行，超过了指定的单元格区域行数，且Table1设置了默认自动扩展单元格行数到实际大小，并且给新的单元格数据行应用 RangeAddress 指定的单元格区域的格式，那么数据填充完后，Table1的实际填充范围应该是第4-15行；然而由于Table2的起始填充位置是不变的，还是从第13行开始填充，这样一来，Table2填充的数据就会覆盖Table1第13-15行的数据，即发生数据重叠覆盖的现象。 :-:  :-:  而如果使用openTableByDefinedName()来分别打开这两个Table，且设置表格也会按实际数据行数自动扩展，当Table1自动扩展后数据行数为12行，填充范围为第4-15行时，Table2的起始位置将不再是固定不变的，Table2的起始位置由原来的第13行变成了第21行，填充范围为第21-24行。这样一来，前后两个Table的数据就不会发生重叠覆盖的现象了，填充数据后的效果如图3所示。 :-:  2. 当Excel模板数据内容不变，样式（数据的位置、大小等）改变时， openCell()和openTable()必须要修改其方法中的参数，即操作的单元格和Table对象，否则显示或提交的数据时就可能发生错乱；而openCellByDefinedName()和openTableByDefinedName()方法只要提前在模板中定义好要填充数据的单元格区域的名称，那么无论数据的位置、大小怎么变化，都不需修改代码就能将数据准确填充到相应的位置，并准确获取到提交的数据。 3. 还可以实现用户可根据实际情况和实际需求自行便捷、简单地定义Excel模板，而无需每次更改模板时都要联系程序的开发者更改代码。PageOffice开发平台以其更好的灵活性和适应性，充分的满足了最终用户对系统功能的要求，极大的减少了程序开发者对系统后期的维护量。 >[info]详细请参考PageOffice开发包中Samples4示例： >二、33、给Excel模板中定义了名称的一块区域赋值（专业版、企业版）