ジャナ・ビジネス・コンサルティング
1. What is ExcelTDF
2. Introduction
3.1. How to use ExcelTDF in your solution
As the following diagram shows, ExcelTDF is able to run at server-side, client-side and client-only. The interface and usage is the same wherever the ExcelTDF is running. For the both server-side and client-side, the templates of excel are managed on the server. The difference between server-side and client-side is that client-side provides auto saving functionality.
3.2. ExcelTDF Core classes
ExcelTDF does almost of work for you by some core classes. Excel2007TemplateParser and Excel2007DataParser are the two of these classes. But the convenient way for you is using implementation classes of IExcelHandler or IOpenXmlHandler interface named ExcelHandler, OpenXmlHandler. Excel2007TemplateParser, as the name of class, analyzes the template file and load the template range configuration file. After template is analyzed, template parser will know the mapping relationship of variables/cells and template range tree. Excel2007DataParser analyzes your data class structure and its meta data or SQL data. Metadata named ExcelRange and ExcelAttribute defines range information and Excel cell variable information. After data is analyzed, Excel2007DataParser will know the data/variable mapping relationship and get ExcelRange‘s tree structure that is used to used to determine how to process template.
3.3. How to get ExcelTDF work
In order to let ExcelTDF work, there are fourth steps to do, the first two steps is only needed to do one time.
. Set Configuration
. Creating template file by Excel 2007 above and creating template range configuration file. If you use Metadata to define all range information, this step can be omitted
. Creating IDataProvider class or Data Model class to get data
. Download or open your parsed document or report
3.4. Set Configuration
When you use ExcelTDF to process template, you should tell ExcelTDF how to get template files and template range configuration files. Your template files and template range configuration files maybe saved as files in specified directory or saved in database or gotten from service.
3.4.1. Manage templates by files
Your template excel file and template range configuration file can be managed on server by files. In ExcelTDF we call it repository. Typically your configuration file is named as Repository.config. It is loaded by ExcelTDF when application starting.
The following is the content of Repository.config. You can add more repositories in this file.
<?xml version="1.0" encoding="utf-8" ?> <objects xmlns="http://www.springframework.net"> <object id="mydemo" type="JanaBiz.OpenXml.Repository.FileSystemRepositoryManager, JanaBiz.OpenXml"> <property name="RepositoryName" value="myrepository"/> <property name="RepositoryUri" value="../../Templates"/> </object> </objects>
On the above, the root repository directory is ../../Templates/myrepository. This is relative path to the executable dll. Certainlly, it is can be any full path. It is also be able to database connection string or others for RepositoryUri.
Usually you can use following two ways to load Repository.config when application starting.
a). For console application or Windows form application
[assembly: RegisterRepository("your_namespace.Repository.config")]
b). For all type of application
OpenXmlContext.RegisterRepository("your_namespace.Repository.config");
Refresh your repository
If your template range configuration file is changed or you added, removed some lines in template.xml and resource.xml, you must refresh context to load new configuration information. ExcelTDF provides a method in class OpenXmlContext to reload repositories.
3.4.2. Manage templates by database
If you get template file and template range configuration file from database or other stream resource, you don’t have to set repository information . The resource handler’s protocol named “memory” will load your template file, template range configuration file into context. In order to do this work, you must create a class that implements the interface ITemplateService. By following code, you can set your database template service which implements ITemplateService. This tells ExcelTDF MyDatabaseTemplateService is used to get template file and template range configuration, similar to handler.GetTemplateService(“myconfig”) which use LocalTemplateService to load template files in the repository. There are no access limitation when using database to manage templates, you can do yourself.
ExcelHandler handler = new ExcelHandler(); //implements ITemplateService interface MyDatabaseTemplateService service = new MyTemplateService(); handler.SetTemplateServiceservice(service); //or MyDatabaseTemplateService service = handler.GetTemplateServiceservice<MyDatabaseTemplateService >();
3.4.3. Get templates from web service
If your templates managed on server and you use IExcelHandler on client side, you can tell ExcelTDF getting templates by web service. In this case, your web service must implement methods defined in ITemplateService but don’t need to implement the interface of ITemplateService. This feature gives you capability of managing your templates by web service very simple. Following is some code.
ExcelHandler handler = new ExcelHandler();
...
TemplateServiceSoapClient templateService = handler.GetTemplateService<TemplateServiceSoapClient>();
3.5. Template File
keywords:
. General variable
${VAR1}
. Formula variable
#{=SUM(A20*SUM(VAR1)+VAR2)}
. Custom service variable
%{=XSPAGEBEAK}
. Parameter variable
?{VAR=PARAM1}
. Template Range binding
[ ExcelRange("MyRange", "A1", "Z20", "RowRangeService")]
. Excel Variable binding
[Excel("VAR1"......)]
3.5.1. Variables in Template
When template processing, IExcel2007TemplateParser will search variables in the template and collect mapped information of variable and cell name. As the result, variables are mapped into data value after Excel2007DataParser parsed document. Template variable is a name of an arbitrary text string, whose formats are defined as ${..}, #{..}, %{..}, ?{..} four types.
3.5.1.1. General variable: ${VAR1}
VAR1 is a general variable. ExcelTDF will replace the the variable with the value in data model or SQL query data or some others depending on what data provider is used. For the data model, here is an example.
[ExcelRange("ROOT", "A1, "C3", "RowRangeService")]
public class MyTestMode
[Excel("VAR1")]
public string MyName {get; set; }
}
As the above, after processed, the variable of ${VAR1} will be replaced by value of MyName property in the data mode of MyTestMode. If ${VAR1} is in list range and MyTestMode is element of list, The ${VAR1} will be the value of each element MyTestMode in the list.
3.5.1.2. Formula variable: #{VAR2=SUM(VAR1, CJ12, 20)}
Formula variable is an Excel formula expression variable. After processed, the cell of formula variable will be a normal Excel formula function expression, such as =SUM(R12) etc. In the above expression, the left side of the equal “=” is variable name VAR2, and the right side is expression. Here VAR2′s value is the sum of VAR1. ExcelTDF will search VAR1 variable and compute the cell name of VAR1 automatically. In the expression of formula variable, you can use both normal cell name, such as CJ12, AR12:AR20, $AR$20 and variable name. ExcelTDF is able to identify expression variable type of cell name, variable, text and number. For example, assume that VAR1 is in the list range and the cells are AK20,AK21,AK22 after range is processed, the variable of #{VAR2=SUM(VAR1, CJ12, 20)} will be explained as the sum of =SUM(AK20,AK21,AK22, CJ12, 20).
#{VAR2=SUM(VAR1, CJ12, 20)} --> =SUM(AK20,AK21,AK22, CJ12, 20)
[ExcelRange("ROOT", "A1, "C3", "RowRangeService")]
public class MyTestMode
[Excel("VAR1")]
public string MyName {get; set; }
[Excel("VAR2")]
public double Sum {get; set; }
}
Formula variable expression is able to has sub expression. Generally no limitation on the formula expression, you can use as normal excel file. Here is complicated one. The variable’s name is MYNO, in the expression, no other variables referenced.
#{MYNO=IF(ISERROR(MATCH("◎", R6:BZ6, 0))=FALSE,
HLOOKUP("◎", R6:BZ9, 3, FALSE),
IF(ISERROR(MATCH("○", R6:BZ6, 0))=FALSE,
HLOOKUP("○", R6:BZ9, 3, FALSE),
IF(ISERROR(MATCH("▲", R6:BZ6, 0))=FALSE,
HLOOKUP("▲", R6:BZ9, 3, FALSE),
IF(ISERROR(MATCH("△", R6:BZ6, 0))=FALSE,
HLOOKUP("△", R6:BZ9, 3, FALSE),
""
)
)
)
)}
The following are also formula variables. In the expression, TRACKNO, FACTOR, QUANTITY, PRICE, BASEPRICE are referenced variables, ExcelTDF will replace them with correct cell names when building formula.
#{VAR2=CONCATENATE("ABCD",LEFT(TRACKNO,1))}
#{VAR2=SUM(FACTOR*SUM(QUANTITY*PRICE))+ BASEPRICE*15%}
If no other variable references VAR2, the name of VAR2 can be omitted. for example
#{=SUM(FACTOR*SUM(QUANTITY*PRICE))+ BASEPRICE*15%}
Certainly, you can use excel formula directly if the cell name is not changed dynamically and don’t need to reference a variable. ExcelTDF don’t process them, for example =SUM(12*C12), this is not a variable.
In the excel formula variable expression, you can use custom custom function service as the same as Excel function. About custom function service, please see next section of Custom service variable.
3.5.1.3. Custom service variable: %{VAR3=XFSRANGE(RACENO)}
Custom service variable is customized cell service or function service, which is scalable functionality provided by ExcelTDF. ExcelTDF provides several custom cell services and function services.
You can develop your custom cell service and function service too. Custom services are used to processing special cells or some computing functionality. There are two types of custom services, one is Cell Service which implements interface of IExcelCellService, and another is Function Service which implements interface of IExcelFunctionService. IExcelFunctionService is a sub interface of IExcelCellService. The two types of services are used for different purpose. In order to distinguish from Excel formula functions, cell service name and function service name in ExcelTDF start with XS and XF. In fact the name is the object id defined in the Spring.NET configuration file.
<object id="XFSRANGE" type="JanaBiz.OpenXml.Service.XFSiblingRangeFunctionService, JanaBiz.OpenXml" singleton="false"> </object>
[ExcelRange("ROOT", "A1, "C3", "RowRangeService")]
public class MyTestMode
[Excel("VAR1")]
public string MyName {get; set; }
[Excel("VAR2")]
public double Sum {get; set; }
[Excel("VAR3")]
public string No {get; set; }
}
If no other variable references VAR3, the name of VAR3 can be omitted. for example
%{=XFSRANGE(VAR1)}
3.5.1.3.1. Cell Service
Cell service does some special processing, for example, showing a barcode. Cell service has no result returned. Following are some cell services provided by ExcelTDF.
XSBARCODE: output a barcode
XSCHECKBOX: output a checkbox
XSPAGEBREAK: add page break of excel
XSVDLIST: showing cell as pulldown list
%{=XSBARCODE("BCODE", BARCODE_NO, "UPC-A")}
%{=XSCHECKBOX(1, PLAYER)}
%{=XSPAGEBREAK(20)}
%{AAA=XSVDLIST(RESULT)}
On the above, in the expression, BARCODE_NO, PLAYER, RESULT is referenced template variables.
3.5.1.3.2. Function Service
Function Service does some processing and returns the result as string. Depending on what you want to do, the function’s result maybe is variable’s cell name or variable’s value. The function is able to have sub expression in which another custom function might be called. The following is a example.
%{=XFOFFSET(XFMATCH("◎,○,▲", RACENO, 1), 0, 1)}
In the above, custom function XFMATCH is called, and the result of XFMATCH becomes a parameter of custom function XFOFFSET.
Because custom function returns the result, the custom function can be called in expression of Excel formula variable expression, such as the below.
#{=CONCATENATE(XFOFFSET(XFMATCH("◎,○,▲", RACENO, 1), 0, 1), "=", XFOFFSET(XFMATCH("○,▲,△", RACENO, 1), 0, 1), XFOFFSET(XFMATCH("△", RACENO, 1), 0, 1))}
In the above, in excel formula function expression CONCATENATE, custom function XFOFFSET and XFMATCH are called.
Note: Custom function service can not call excel function as sub expression because the custom function service and cell service are processed only one time while workbook are loading, not in the period of running.
3.5.1.4. Parameter variable: ?{VAR2}
Parameter variable is used to specify parameter when searching the data. This is run-time functionality like CrystalReport, in the version 1.0.0.0, no support for parameter variable.
3.5.1.5. Data type of variable
In ExcelTDF, you can specify data type forcibly. You just need to add a special char after the leading of letter. N is number type, C is currency type, D is date type. If no N, C, D specified, the variable considered as general string type. In the version of 1.0.0.0, type N, C will forcibly set cell type as NUMBER, D is now processed as string type. Don’t confuse the data type with data output format. Output format such as $#,###,### is set by excel on the template designing, data type just tell excel the output data is whether a number or not etc. The following is examples.
General variable ${VAR1}, $N{VAR1}, $C{VAR1}, $D{VAR1}
${VAR1}: output as type of string or standard
$N{VAR1}: output should be a number and ExcelTDF set the cell’s type to Number
$C{VAR1}: output should be currency and ExcelTDF set the cell’s type to Number
$D{VAR1}: output should be date and ExcelTDF does as string or standard in the version 1.0.0.0
The same rules are used for formula variables, custom service variables and parameter variables.
Formula variable: #{..}, #N{..}, #C{..}, #D{..}
Custom service variable: %{..}, %N{..}, %C{..}, %D{..}
Parameter variable: ?{..}, ?N{..}, ?C{..}, ?D{..}
3.5.1.6. The Range of variable
In a range, variable must be unique, but in different range, there is no this limitation. When a variable is referenced by formula or custom service, ExcelTDF try to search the variable and find cell names or values of variable. ExcelTDF search variable by some orders. ExcelTDF always search expression variable first from the range of variable itself. If not found, ExcelTDF then search expression variable from child ranges. Last, try to search expression variable from all. Understanding the searching order will help you to get correct result.
3.5.2. Template range
Template range is an excel range specified by row index or column index or excel cell name. Range Service Name must be specified to identify which template range to be processed by ExcelTDF. Template range is defined both by object in Spring.NET configuration file and by Metadata defined in data model. Defined items in template range are the properties of type of class TemplateRange.
When you design your template, the first thing is that you should determine how to output data with template. You can determine how many template ranges there are and how to process template ranges according to your data structure. If your data is a list, define a template range as a list service range will simplify your processing. Please see the examples to understand Template Range and Range Services.
Template file is a Excel workbook(*.xlsx). In this version only *.xlsx format is supported. As the known, one workbook might have several worksheets. ExcelTDF splits a worksheet as some ranges which include some cells to be processed or not. For the cells to be processed, the cell text might include one or more defined variables. A range might have some child ranges, and child range is able to has its own child ranges too. These range’s definition information must be specified by template range configuration file or defined by Metadata in data model. So the whole worksheet is considered as a root range, all other ranges are worksheet’s child or descendant ranges.
See the following diagram. The worksheet root range has three child ranges named R-A, R-E, R-D, and range R-A also has two child range R-B, R-C. From diagram we can see a tree of range.
The template range configuration file, simply, we can consider it as TemplateRange class. The all items’ name are TemplateRange’s properties. The service name used to process range is listed in service names, You can get all names by call OpenXmlContext’s GetRangeServiceIds() methods too.
The following is template whose layout is the same as description above.
The following is corresponding template range configuration file.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net"> <object id="SAMPLE_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="RootRange" /> <property name="LeftTop" value="A1" /> <property name="RightBottom" value="Z51" /> <property name="ServiceId" value="RowRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="R-A-ID" /> <ref object="R-E-ID" /> <ref object="R-D-ID" /> </list> </property> </object> <object id="R-A-ID" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="R-A" /> <property name="LeftTop" value="B3" /> <property name="RightBottom" value="S31" /> <property name="ServiceId" value="BlockRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="R-B-ID" /> <ref object="R-C-ID" /> </list> </property> </object> <object id="R-E-ID" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="R-E" /> <property name="LeftTop" value="B37" /> <property name="RightBottom" value="S49" /> <property name="ServiceId" value="BlockRangeService"/> </object> <object id="R-D-ID" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="R-D" /> <property name="LeftTop" value="U2" /> <property name="RightBottom" value="Y49" /> <property name="ServiceId" value="BlockRangeService"/> </object> <object id="R-B-ID" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="R-B" /> <property name="LeftTop" value="C9" /> <property name="RightBottom" value="Q16" /> <property name="ServiceId" value="BlockRangeService"/> </object> <object id="R-C-ID" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="R-C" /> <property name="LeftTop" value="B20" /> <property name="RightBottom" value="Q29" /> <property name="ServiceId" value="BlockRangeService"/> </object> </objects>
The following is model class used to process template above.
[ExcelRange("SHEET1")]
public class RT
{
[ExcelRange("R-A")]
public RA ra;
[ExcelRange("R-D")]
public RD rd;
[ExcelRange("R-E")]
public RE re;
}
public class RA{
[ExcelRange("R-B")]
public RB rb;
[ExcelRange("R-C")]
public RC rc
}
public class RB {}
public class RC {}
public class RD {}
public class RE {}
If you want to define range’s all information into Metadata to replace template range configuration file, the following is sample of data model.
[ExcelRange("SHEET1", "A1", "Z51", "RowRangeService")]
public class RT
{
[ExcelRange("R-A", "B3", "S31", "BlockRangeService")]
public RA ra;
[ExcelRange("R-D", "U2", "Y49", "BlockRangeService")]
public RD rd;
[ExcelRange("R-E", "B37", "Y49", "BlockRangeService")]
public RE re;
}
public class RA{
[ExcelRange("R-B", "C9", "Q16", BlockRangeService")]
public RB rb;
[ExcelRange("R-C", "B20", "Q29", "BlockRangeService")]
public RC rc
}
public class RB {}
public class RC {}
public class RD {}
public class RE {}
3.5.2.1. Template range example 1
Next let us see how to determine your template range by some examples.
First, see the following most simple example. We just want to replace the variables between row 8 and row 10, so the worksheet has only one root range from row 8 to row 10.
In order to tell ExcelTDF the range’s information , we write these information into a Spring.NET object xml file as following.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="RowRangeServiceTest_sheet1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT"/> <property name="Top" value="8"/> <property name="Bottom" value="10"/> <property name="ServiceId" value="RowRangeService"/> </object> </objects>
On the above, in configuration file, the type of object is always JanaBiz.OpenXml.TemplateRange. TemplateRange has some properties must be specified. These properties are RangeName, ServiceId and range defined by (Top|Left|LeftTop) and (Bottom|Right|RightBottom). If we want to specify range by row index without column limitation, the Top and Bottom property should be set. If we want to specify range by column index without limitation of row index, the Left and Right property should be set. If we want to specify rectangle range with the limitation of row and column, the property LeftTop and RightBottom should be set.
RangeName: the identifier of the range
Top/Bottom | Left/Right | LeftTop/RightBottom : the range specified by row or column or cell name
ServiceId: the service name used to process this range. see service names
The object id can be any string but it must be unique in all configuration files, For the worksheet root range, it is best to assign object id as template id + “sheet” + worksheet index . This will be convenient to ExcelTDF to find worksheet root range object. About some other property please see TemplateRange class API.
For the template above, following is the processed result. Because the service is RowRangeService, it just replaces the variables in specified template range with the data in data model.
3.5.2.2. Template range example 2
Following is another template file. In this template file we want to output data as a list. The list’s element is from row 8 to row 10, let us see how different result will be got.
The template file is shown as following.
The template configuration file is shown as following.
<?xml version="1.0" encoding="shift_jis"?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="RowListRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT"/> <property name="Top" value="1"/> <property name="Bottom" value="30"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="RLRSChildList1"/> </list> </property> </object> <object id="RLRSChildList1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="CHILDLIST1"/> <property name="LeftTop" value="L8"/> <property name="RightBottom" value="BN10"/> <property name="ServiceId" value="RowListRangeService"/> </object> </objects>
There are two TemplateRanges in worksheet. The worksheet root range, whose RangeName is ROOT and it has a child range. Root range is from row 8 to row 22. Child range’s id is RLRSChildList1 and RangeName is CHILDLIST1. Child range is from L8 to BE10. Child range is processed by service of RowListRangeService which output data as list with the same format as defined range L8:BE10, Assume your list in the data model has 5 elements, the data of 5 elements will be outputted from L8 to L20.
The following is processed result.
If we change the ROOT range’s row from row 7 to row 20, what will be changed? The variable of ${HouseNumber1} is in the range of ROOT, this variable will be processed. ROOT range’s ServiceId is RowRangeService, the variable is just replaced by value in data model corresponding to the ROOT range.
3.5.2.3. Template range example 3
Next, let us see a some complicated examples. In this example we want to output a list, and the element of list is also a list. We usually bump into this case. As the shown, template design is very simple too. The outer list’s element is from B5 to BJ11, the inner list’s element is from C8 to BE10. So the variable of $N{HouseNumber1}, #C{S1=SUM(HBⅡBody)}, #C{=SUM(S10)} etc. are processed by outer list. Inner list size might be different, the service of specified will process them correctly.
As the diagram of following, the worksheet has three ranges. Root range of worksheet is from row 1 to 18, which is processed by RowRangeService. The root range has a child range whose id is CRCRS_PARENTLIST1, named PARENTLIST1 and colored with gray. Child range is from B5 to BJ11, which is processed by service of ComplexRowCloneRangeService. The service named with prefix of ComplexXX is used to process list ranges who’s element has child range. So the part of B5:BJ11 will be cloned list size-1 times. The cloned rows are inserted in the below of the last row BJ11, here it is row 12. Because the element of list includes a child range, whose id is CRCRS_CHILDLIST1 , this range is also a cloned list, the cloned rows is from C8 to BE10, The row of parent range will be changed dynamically. ExcelTDF control ranges processing order, first parent range, then child ranges. If child range is a coned list, parent range’s row or column will be changed automatically.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="ComplexRowCloneRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT"/> <property name="Top" value="1"/> <property name="Bottom" value="18"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CRCRS_PARENTLIST1"/> </list> </property> </object> <object id="CRCRS_PARENTLIST1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARENTLIST1"/> <property name="LeftTop" value="B5"/> <property name="RightBottom" value="BJ11"/> <property name="ServiceId" value="ComplexRowCloneRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CRCRS_CHILDLIST1"/> </list> </property> </object> <object id="CRCRS_CHILDLIST1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="CHILDLIST1"/> <property name="LeftTop" value="C8"/> <property name="RightBottom" value="BE10"/> <property name="ServiceId" value="RowCloneRangeService"/> </object> </objects>
The result is shown in the following. You can see, the simple template creates complicated data output. There are many patterns you can assemble by provided services in ExcelTDF.
3.5.3. Without template range file
If your template doesn’t need to be changed frequently, maybe you don’t want to create template range configuration file. In this case the template range information can be written in code as metadata of program. If you write range information into code, and your template layout is changed, you must modify your code. The most benefit of using template range configuration file is you don’t have to change your code if template layout changed. For the detail, please see next chapter about data provider.
3.6. Data Provider and Data Model
ExcelTDF uses two types of interface to control the mapping information. IDataProviderManager controls mapping behavour of IRangeDataProvider and template range, while IRangeDataProvider controls mapping behavour of data field in data model and template variable. Data field maybe is field or property in data model, or columns of SQL query, or some others.
3.6.1 The types of data provider manager
Each implementation class of IDataProviderManager knows how to map data provider to template range. Actually manager maps RangeData in IRangeDataProvider to template range. RangeData can be data model, DataSet or other data objects. ExcelTDF provides three manager classes.
3.6.1.1. DataModelProviderManager
DataModelProviderManager, in which the default DataModelRangeDataProvider is used, maps data model to template range. ExcelRange and ExcelAttibute(Excel) Attribute classes are used to help ExcelTDF doing those works. The RangeData in DataModelRangeDataProvider is your data model for DataModelProviderManager. In the next you will see how to use them in data model.
3.6.1.2. QueryProviderManager
QueryProviderManager let you create document or report by specified SQL query, all template ranges use the same one query data if you use QueryProviderManager. If you are familiar with CrystalReport, creating a query and getting report, this is the similarity functionality.
3.6.1.3. AnyTypeProviderManager
AnyTypeProviderManager let you create document or report by any data type. You should create IRangeDataProvider for each template range. If no IRangeDataProvider found, the IRangeDataProvider of parent range will be used. You can specify a data model for a range, SQL query for another range. You can specify SQL query for each range and so on. It is the most flexible manager.
See the following diagram, it shows the association between template and data.
As you see, ExcelTDF provides various IRangeDataProvider implementation classes, from data mode to usually used database, even SAP. IRangeDataProvider has two methods ReadRangeData() for getting data from database or background service, and WriteRangeData() for saving edited data to database or sending to background service. ExcelTDF maps RangeData in IRangeDataProvider to template range. ExcelTDF provides three IDataProviderManager implementation classes, they are DataModelProviderManager, QueryProviderManager and AnyTypeProviderManager. The three data provider managers have the different control behavior and are used in different way. The DataModelProviderManager is the most often used data provider manager. If the RangeData in IRangeDataProvider is data model, not a SQL DataSet and not SAP’s DataReader, DataModelProviderManager is your suitable selection.
3.6.2 Data model of Template range example 1
In the following, let us see how to use DataModelProviderManager and what your data model should be.
For the Template range example-1 which we discussed on the above, data model is shown in the diagram. Please see the fields that have defined Excel metadata, for example [Excel("HBⅡBody")], it will map the value of HBⅡBody to the template variable defined as ${HBⅡBody}. Every template variable must have corresponding metadata with the same name in data model. If [Excel("X")] is defined in data model, but no ${X} or #{X=..} or %{X=..} found, “X” can be used in formula expression and custom service expression. For example, #{VAR2=SUM(VAR1*X/100)}, X is not a variable in template but a variable in data model. In the same way, the metadata [ExcelRange("ROOT")] tells ExcelTDF that the range information named ROOT in the template range configuration is used for this model, and variables defined in this model by metadata are mapped to template variables in the range named ROOT.
using System; using System.Xml.Serialization; using JanaBiz.OpenXml; namespace OpenXmlDemoData.Data { [ExcelRange("ROOT")] [Serializable] public class RowRangeServiceData1 { public string HouseType; [Excel("HouseNumber1")] public string HouseNum = "20"; [Excel("HBⅡBody")] public double HBⅡBody; [Excel("VAR2")] public string Var2; [Excel("LBⅡBody")] public string LBⅡBody; [Excel("KCBody")] public string KCBody; [Excel("SHⅡBody")] public string SHⅢBody; ..... } }
Here is the code to parse template and get your document. As you see, after you finished your template design and wrote well your template configuration, the coding is very simple.
using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Text; using JanaBiz.OpenXml.Handler; using JanaBiz.OpenXml.Config; using JanaBiz.OpenXml.Data; using OpenXmlDemoData; namespace TemplateConvertDemo { class Program { static void Main(string[] args) { //create your handler IOpenXmlHandler handler = new OpenXmlHandler(); //tell ExcelTDF which ITemplateService is used to //get template file and template range configuration handler.GetTemplateService<LocalTemplateService>(); //tell ExcelTDF which IDataProviderManager and //what data model is used to data handler.DataProviderManager = new DataModelProviderManager( new OpenXmlDemoData.Data.RowRangeServiceData1()); //set template id to identify which template is used handler.TemplateId = "RowRangeServiceTest"; //parse your template handler.ParseTemplate(); //parse your data handler.ParseWorkbook(null); //get parsed document byte[] doc = handler.GetParsedDocument(); //release some resource handler.Close(); } } }
In the code, creating DataModelProviderManager with parameter of RowRangeServiceData1 will enable manager use default data provider of DataModelRangeDataProvider. In fact, DataModelProviderManager have two constructors with parameter of either data model or IRangeDataProvider when you want use your customized IRangeDataProvider.
public DataModelProviderManager(Object dataModel) public DataModelProviderManager(IRangeDataProvider rootRangeDataProvider)
If you want to open parsed workbook into excel in your excel AddIn program directly, ExcelHandler is prepared for you.
The code is shown as the following. The step of coding is the same as the above example. Here we create and initialize handler first, and get template file and template range configuration by web service of TemplateServiceSoapClient. So your web service should perform the methods of interface ITemplateService, but your service don’t have to implement the interface of ITemplateService. This will be helpful for you to reference web service directly.
private ExcelHandler handler = null; private TemplateServiceSoapClient webService = null; public RibbonDemo() { InitializeComponent(); } private void RibbonDemo_Load(object sender, RibbonUIEventArgs e) { InitializeExcelHandler(); } /// <summary> /// Create and initialize <see cref="IExcelHandler"/> /// </summary> private void InitializeExcelHandler() { //create IExcelHandler instance handler = new ExcelHandler(); // set excel application handler.ExcelApp = Globals.ThisAddIn.Application; //create or get template service webService = handler.GetTemplateService<TemplateServiceSoapClient>(); //create data service DataService dataService = new DataService(); //get excel data event handler handler.OnGetDocumentData += dataService.GetDocumentData; //save excel data event handler handler.OnSaveDocumentData += dataService.SaveDocumentData; }
On the above, we create DataService object which performs OnGetDocumentData event handler for getting data model. Certainly you can assign DataModelProviderManager to handler directly too, such as
handler.DataProviderManager = new DataModelProviderManager( new OpenXmlDemoData.Data.RowRangeServiceData1());
Define a event handler is very flexible. First, coding logic to get data for all templates can be put in event handler method. Second, you can override IRangeDataProvider, and get data in your way. The event handler method is show in the below.
/// <summary> /// Assign a data provider manager or data range provider to a template for specified template id /// </summary> /// <param name="handler"></param> /// <param name="sheetIndex"></param> public void GetDocumentData(IOpenXmlHandler handler, int sheetIndex) { switch (handler.TemplateId) { case "RowRangeServiceTest": switch (sheetIndex) { case 0: // you can get from web service etc., for simple, here just new a data model handler.DataProviderManager = new DataModelProviderManager( new RowRangeServiceData1()); break; } break; } }
Let us see the code about opening parsed document, only several lines. Open() method try to find data provider manager and data provider by event handler of OnGetDocumentData. If event handler exists, it will be called.
/// <summary> /// Open parsed workbook of spreadsheet /// </summary> /// <param name="templateId"></param> private void OpenDocument(string templateId) { //close opened workbook before handler.Close(); //template id used handler.TemplateId = templateId; //other input parameter for get excel data handler.InputData = null; //parse template handler.ParseTemplate(); //parse workbook and open it in excel or other component handler.Open(); }
3.6.3 Data model of Template range example 2
In this example because the root template range has a child range, the data model is also created into two classes hierarchically. RowListRangeServiceData class has metadata [ExcelRange("ROOT")] and its field member ChildList1 has metadata [ExcelRange("CHILDLIST1")]. The names of ROOT and CHILDLIST1 matches the RangeName defined in template range configuration. In template range configuration, the ServiceId of CHILDLIST1 is RowListRangeService, so in the data model of RowListRangeServiceData, the member with metadata [ExcelRange("CHILDLIST1")], ChildList1 must be a list.
ExcelRange binding a data model is associated to template range, and if your template range ServiceId is list related service, data member should be a list type of IList. That is all.
using System; using System.Collections.Generic; using JanaBiz.OpenXml; using JanaBiz.Common.Serialize; namespace OpenXmlDemoData.Data { [Serializable] [ExcelRange("ROOT")] public class RowListRangeServiceData { public RowListRangeServiceData() { //Create data for demo for (int i = 0; i < 5; i++) { ChildList1.Add(new RowRangeServiceData1()); } } [ExcelRange("CHILDLIST1")] public IList<RowRangeServiceData1> ChildList1 = new List<RowRangeServiceData1>(); } }
In OnGetDocumentData event handler we added a piece of coding for getting data model. other works are the same as example-1
/// <summary> /// Assign a data provider manager or data range provider to a template for specified template id /// </summary> /// <param name="handler"></param> /// <param name="sheetIndex"></param> public void GetDocumentData(IOpenXmlHandler handler, int sheetIndex) { switch (handler.TemplateId) { case "RowRangeServiceTest": switch (sheetIndex) { case 0: // you can get from web service etc., for simple, here just new a data model handler.DataProviderManager = new DataModelProviderManager( new RowRangeServiceData1()); break; } break; case "RowListRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new RowListRangeServiceData()); break; } break; } }
3.6.4 Data model of Template range example 3
In this example, the template range has a list, and list’s element is also a list. Let us see the data model how to construct.
As you have seen, entry class ComplexRowCloneRangeServiceData has metadata ExcelRange(“ROOT”), its member MasterDoorList1 has metadata [ExcelRange("PARENTLIST1")], MasterDoorList1 is a list. MasterDoorList1′s element is RoomTypeList1 which has a member MasterDoorChildList, and MasterDoorChildList has metadata [ExcelRange("CHILDLIST1")], it is also a list. In the OnGetDocumentData event handler we added code to get data provider manager. For the others no need to change.
using System; using System.Collections.Generic; using JanaBiz.OpenXml; namespace OpenXmlDemoData.Data { [ExcelRange("ROOT")] public class ComplexRowCloneRangeServiceData { public ComplexRowCloneRangeServiceData() { CreateDataForDemo(); } [ExcelRange("PARENTLIST1")] public IList<RoomTypeList1> MasterDoorList1 = new List<RoomTypeList1>(); } }
using System; using System.Collections.Generic; using System.Linq; using JanaBiz.OpenXml; namespace OpenXmlDemoData.Data { /// <summary> /// 戸当りマスタ登録・変更画面の概要説明です。 /// </summary> public class RoomTypeList1 { public RoomTypeList1() { CreateDataForDemo(); } public RoomTypeList1(string houseNumber) { CreateDataForDemo2(houseNumber); } [Excel("部屋数1")] public string HouseNumber="12"; [ExcelRange("CHILDLIST1")] public IList<RowRangeServiceData1> MasterDoorChildList = new List<RowRangeServiceData1>(); } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using System.IO; using System.Reflection; using JanaBiz.OpenXml; using JanaBiz.OpenXml.Handler; using JanaBiz.OpenXml.Data; using JanaBiz.OpenXml.Config; using OpenXmlDemoData.DataProvider; using OpenXmlDemoData.Data; namespace OpenXmlDemoData { public class DataService { /// <summary> /// Assign a data provider manager or data range provider to a template for specified template id /// </summary> /// <param name="handler"></param> /// <param name="sheetIndex"></param> public void GetDocumentData(IOpenXmlHandler handler, int sheetIndex) { switch (handler.TemplateId) { case "RowRangeServiceTest": switch (sheetIndex) { case 0: // you can get from web service etc., for simple, here just new a data model handler.DataProviderManager = new DataModelProviderManager( new RowRangeServiceData1()); break; } break; case "RowListRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new RowListRangeServiceData()); break; } break; case "ComplexRowCloneRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new ComplexRowCloneRangeServiceData()); break; } break; } }
3.6.5. How to create your class of IRangeDataProvider
If you need to search data from database, web service or other services in background, you might want to create your own implementation class of IRangeDataProvider. If your searched result is designed as data model demonstrated in the example above, the most convenient way is to create a sub class of DataModelRangeDataProvider. DataModelRangeDataProvider performs interface methods of IRangeDataProvider, but ReadRangeData() and WriteRangeData() do nothing. You can override these two methods in your way. When you open or parse document, firstly, IDataProviderManager calls ReadRangeData() to get data, then IDataProviderManager parses gotten data. When you save edited document by calling ExcelHandler or OpenXmlHandler’s Save() method, firstly, the Save() method will call Flush() method to save edited data into mapped data model of RangeData then call WriteRangeData() to save RangeData to database or other services.
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.IO; using System.Reflection; using JanaBiz.Common.Serialize; using JanaBiz.OpenXml.Data; using OpenXmlDemoData.Data.Boat; namespace OpenXmlDemoData.DataProvider { /// <summary> /// /// This is demo for getting data from database or background service. /// Here we get data from xml resource file. /// </summary> public class ColumnDataProvider : DataModelRangeDataProvider { public ColumnDataProvider() : base() { } /// <summary> /// Here you can call database service, web service of other background service to /// read data, the last step is assign the result data to RangeData. /// </summary> public override void ReadRangeData() { //here, read from xml file Assembly assembly = Assembly.GetExecutingAssembly(); string assemblyNamespace = assembly.FullName.Split(new char[]{','})[0]; string xmlfile = assemblyNamespace + ".Resources.ColumnRangeTestData.xml"; Stream stream = assembly.GetManifestResourceStream(xmlfile); BoatRace boatRace = Serializer.ReadModel<BoatRace>(stream); //this must be done this.RangeData = boatRace; } /// <summary> /// You can write your saving data in your way. /// Here we just write the data into a xml file /// </summary> /// <returns>return true, if saved data successfully, otherwise false</returns> public override bool WriteRangeData() { Serializer.WriteToXml(this.RangeData, "c:\\ColumnListRangeTestData.xml"); return true; } } }
In the example on the above, for simply demo, ColumnDataProvider just get data from a serialized xml resource file and write edited data into xml resource file. Next code is show how to assign the data provider to IDataProviderManager.
/// <summary> /// Assign a data provider manager or data range provider to a template for specified template id /// </summary> /// <param name="handler"></param> /// <param name="sheetIndex"></param> public void GetDocumentData(IOpenXmlHandler handler, int sheetIndex) { switch (handler.TemplateId) { case "RowRangeServiceTest": switch (sheetIndex) { case 0: // you can get from web service etc., for simple, here just new a data model handler.DataProviderManager = new DataModelProviderManager( new RowRangeServiceData1()); break; } break; case "ColumnListRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new ColumnDataProvider()); break; } break; case "ColumnCloneRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new ColumnDataProvider()); break; case 1: handler.DataProviderManager = new DataModelProviderManager( new ColumnDataProvider()); break; } break; case "ComplexColumnCloneRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new ComplexColumnDataProvider()); break; } break; case "ComplexColumnListRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new ComplexColumnDataProvider()); break; } break; } }
3.6.6. Use Metadata without template range configuration
If you don’t want to create template configuration file, you can write necessary information into ExcelRange metadata as the following.
using System; using System.Collections.Generic; using System.Linq; using System.Text; using JanaBiz.OpenXml; using JanaBiz.Common.Serialize; namespace OpenXmlDemoData.Data.Boat { [Serializable] [ExcelRange("ROOT", "B5", "EQ10", "RowRangeService")] public class BoatRace { public BoatRace() { } [ExcelRange("HEADER", "CX4", "DF48", "BlockRangeService")] public BoatRaceHeader HeaderData = new BoatRaceHeader(); [ExcelRange("LIST", "BZ4", "CK48", "ColumnListRangeServiceRL")] public SerializableList<BoatRaceRecord> itemDataList = new SerializableList<BoatRaceRecord>(); [ExcelRange("RESULT", "H4", "Q48", "BlockRangeService")] public BoatRaceResult ResultData = new BoatRaceResult(); } }
Then set parameter of method ParseTemplate(params bool[] usingConfig) to false, handler.ParseTemplate(false);
using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Text; using JanaBiz.OpenXml.Handler; using JanaBiz.OpenXml.Config; using JanaBiz.OpenXml.Data; using OpenXmlDemoData; namespace TemplateConvertDemo { class Program { static void Main(string[] args) { //create your handler IOpenXmlHandler handler = new OpenXmlHandler(); //tell ExcelTDF which ITemplateService is used to //get template file and template range configuration handler.GetTemplateService<LocalTemplateService>(); //tell ExcelTDF which IDataProviderManager and //what data model is used to data handler.DataProviderManager = new DataModelProviderManager( new OpenXmlDemoData.Data.RowRangeServiceData1()); //set template id to identify which template is used handler.TemplateId = "RowRangeServiceTest"; //parse your template handler.ParseTemplate(false); //parse your data handler.ParseWorkbook(null); //get parsed document byte[] doc = handler.GetParsedDocument(); //release some resource handler.Close(); } } }
4. Service
ExcelTDF provides many range service to process template range as what you want to. Range service are the implementation class of IExcelRangeService and set in template range configuration file. Simultaneously ExcelTDF provides some custom cell services and function services. As explained on the chapter of Custom service variable, cell services and function services has the writing format as the same as Excel formula. Custom cell services are used to perform special processing like barcode, page break and so on, there are no result returned. Function service does some special processing and returns function result as string. Simply, range service is used to process template range, custom service is used to process template cells.
4.1. IExcelRangeService
Default range service is configured in Spring.NET objects file included in ExcelTDF package as resource file. Service name which is called ServiceId in template range is object id that is upper case and lower case sensitive.
<object id="RowRangeService" type="JanaBiz.OpenXml.Service.RowRangeService, JanaBiz.OpenXml" singleton="false"/>
OpenXmlContext class provides some static methods to get ids of different services. You can get all range service ids by
OpenXmlContext.GetRangeServiceIds();
ExcelTDF full versions provides following range services.
. RowRangeService
. RowListRangeService
. RowCloneRangeService
. ComplexRowListRangeService
. ComplexRowCloneRangeService
. BlockRangeService
. BlockRowListRangeService
. ComplexBlockRowListRangeService
. ColumnListRangeService
. ColumnListRangeServiceRL
. ColumnCloneRangeService
. ColumnCloneRangeServiceRL
. ComplexColumnListRangeService
. ComplexColumnListRangeServiceRL
. ComplexColumnCloneRangeService
. ComplexColumnCloneRangeServiceRL
. MultiplePageRangeService
. BlockRowCloneRangeService
. BlockColumnCloneRangeService
4.1.1. RowRangeService
RowRangeService is the simplest service whose range is specified by top row index and bottom row index. RowRangeService replaces the variables in template range with data value in RangeData of IRangeDataProvider. If you specify range by cell name of LeftTop and RightBottom, the variables outside the specified column will not be processed.
The template range specified with this service is able to have child ranges. Generally, the worksheet’s root range is usually specified by this service.
4.1.2. RowListRangeService
RowListRangeService is used to show a data list whose first element is specified by top row index and bottom row index. RowListRangeService requires you design well-formatted rows for all element in list. The template variable between top row and bottom row will be repeatedly processed until the last element of list. If you specify range by cell name of LeftTop and RightBottom, the variables outside the specified column will not be processed. The range specified service as RowListRangeService can not have child ranges (ExcelTDF not process any child ranges even child range is specified in template range configuration file). see ComplexRowListRangeService
In template, one element of list maybe have several excel rows as a logical unit. For example, if you specified template range from top row 8 to bottom row 10, your logical unit is (10-8+1)=3 rows. Assume your data list size is 5, all data will be outputted from row 8 to row 8+3*5-1=22. If you specify the StartIndex and PageSize in template range configuration, the elements from StartIndex to StartIndex+PageSize will be outputted. If StartIndex + PageSize is greater than the size of list, service outputs to the last element of list. The format of all output logical rows must be the same as the first element rows except that the template variable is only written in the first element rows. The best way is that you copy all cells in the first element several times until your list’s max size. Make sure cells exist in worksheet. At sometime the cell without text (blank cell ) might not be created in worksheet by Open XML even the cells is shown. See the following diagram of RowListRangeService example. In template range configuration first element is defined from row index is from 8 to 10. list has max size 5.
4.1.3. RowCloneRangeService
RowCloneRangeService is one of usually used service. RowCloneRangeService is used to show data list too. The different from RowListRangeService is RowCloneRangeService don’t have to set format for each elements except of first element rows. RowCloneRangeService clones cells in first element rows and add them in the below. This is very useful, you don’t worry about the list size and are able to design template simply. After cloned row are added into document, the row index of other range and the cell name of formula will be changed, ExcelTDF will recalculate row index about them again. The RowCloneRangeService can not have child ranges as the same as RowListRangeService.
The following is an example. At the bottom of template, there are a Excel formula of =SUM(E21:L25) and a pull down cell. After template is processed, 99 logical unit rows are cloned. The logical row size is 3, so the number of changed rows is 99*3=297. Let us see the formula in the result diagram, SUM(E21:L25) is changed to SUM(E318:L324) and the pull down cell also is moved from row 21 to row 318 too. If RowCloneRangeService is used, the row index of objects on the below of RowCloneRangeService range will be changed automatically, you don’t have to do any work to correct row index, ExcelTDF does all for you.
The result processed by RowCloneRangeService is shown in the following.
4.1.4. ComplexRowListRangeService
ComplexRowListRangeService is a list service like RowListRangeService. The difference between ComplexRowListRangeService and RowListRangeService is that the element of ComplexRowListRangeService’s list data is able to include child ranges and child range is able to be any range type. RowListRangeService’s element is not able to include child ranges. Because ComplexRowListRangeService is also a list service, you must design the format for all elements in list as the same way as RowListRangeService. See the following template file, template range configuration and data model. The first element of ComplexRowListRangeService is from C4 to BJ22 and named as PARENTLIST1, other elements are well-designed as the same as the first element. In the element there is a RowListRangeService which is from C8 to BE10 and named as CHILDLIST1. CHILDLIST1′s maximum data size is 5 in first element of PARENTLIST1.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="ComplexRowListRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT"/> <property name="LeftTop" value="A1"/> <property name="RightBottom" value="CK275"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CRLRS_PARENTLIST1"/> </list> </property> </object> <object id="CRLRS_PARENTLIST1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARENTLIST1"/> <property name="LeftTop" value="C4"/> <property name="RightBottom" value="BJ22"/> <property name="ServiceId" value="ComplexRowListRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CRLRS_CHILDLIST1"/> </list> </property> </object> <object id="CRLRS_CHILDLIST1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="CHILDLIST1"/> <property name="LeftTop" value="C8"/> <property name="RightBottom" value="BE10"/> <property name="ServiceId" value="RowListRangeService"/> </object> </objects>
using System; using JanaBiz.OpenXml; using JanaBiz.Common.Serialize; namespace OpenXmlDemoData.Data { [ExcelRange("ROOT")] public class ComplexRowListRangeServiceData { [ExcelRange("PARENTLIST1")] public SerializableList<RoomTypeList1> MasterDoorList1 = new SerializableList<RoomTypeList1>(); } }
using System; using System.Collections.Generic; using System.Linq; using JanaBiz.OpenXml; namespace OpenXmlDemoData.Data { /// <summary> /// /// </summary> public class RoomTypeList1 { /// <summary> /// variable /// </summary> [Excel("部屋数1")] public string HouseNumber="12"; /// <summary> /// Child list range /// </summary> [ExcelRange("CHILDLIST1")] public IList<RowRangeServiceData1> MasterDoorChildList = new List<RowRangeServiceData1>(); } }
4.1.5. ComplexRowCloneRangeService
ComplexRowCloneRangeService is a list clone service like RowCloneRangeService. The difference between ComplexRowCloneRangeService and RowCloneRangeService is that the element of ComplexRowCloneRangeService’s list data is able to include child ranges and child range is able to be any range type. RowCloneRangeService’s element is not able to include child ranges. See the following template file, template range configuration and data model. Using ComplexRowCloneRangeService, your template design becomes very simple. As the same as service of RowCloneRangeService, you don’t have to copy the format for each element rows in the list. Following example is explained on the above of Template range example 3.
Let’s see more detail. ComplexRowCloneRangeService processes the range named PARENTLIST1 from B5 to BJ11, which has a RowCloneRangeService range from C8 to BE10 with color of green named CHILDLIST1.
The cell AA11′s template variable expression is #C{S4=SUM(SHⅡ本体)}, it is in the range of PARENTLIST1. The variable name is S4 and referenced variable is SHⅡ本体 which is in the child range of CHILDLIST1. In the first element of PARENTLIST1, the child list has 5 elements, so the variable cells of SHⅡ本体 is AA8,AA11,AA14,AA17,AA20 and the result of formula is =SUM(AA8,AA11,AA14,AA17,AA20). Please see the other variables on the template S1, S1, S2..S10,S11, their behavior are the same as S4.
The cell C11 in the range of PARENTLIST1 is normal formula, you can see it is become =SUM(L23:BE23), because row index is changed. It is the sum of S1..S11.
See the cells of AZ15, BE15, they are the variables expression #C{=SUM(S10)} and #C{=SUM(NWS本体)}. These two variables are in the range of ROOT. See the result on the diagram and what formula they have been changed to.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="ComplexRowCloneRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT"/> <property name="Top" value="1"/> <property name="Bottom" value="18"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CRCRS_PARENTLIST1"/> </list> </property> </object> <object id="CRCRS_PARENTLIST1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARENTLIST1"/> <property name="LeftTop" value="B5"/> <property name="RightBottom" value="BJ11"/> <property name="ServiceId" value="ComplexRowCloneRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CRCRS_CHILDLIST1"/> </list> </property> </object> <object id="CRCRS_CHILDLIST1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="CHILDLIST1"/> <property name="LeftTop" value="C8"/> <property name="RightBottom" value="BE10"/> <property name="ServiceId" value="RowCloneRangeService"/> </object> </objects>
using System; using System.Collections.Generic; using JanaBiz.OpenXml; namespace OpenXmlDemoData.Data { [ExcelRange("ROOT")] public class ComplexRowCloneRangeServiceData { public ComplexRowCloneRangeServiceData() { CreateDataForDemo(); } [ExcelRange("PARENTLIST1")] public IList<RoomTypeList1> MasterDoorList1 = new List<RoomTypeList1>(); } }
using System; using System.Collections.Generic; using System.Linq; using JanaBiz.OpenXml; namespace OpenXmlDemoData.Data { /// <summary> /// /// </summary> public class RoomTypeList1 { /// <summary> /// variable /// </summary> [Excel("部屋数1")] public string HouseNumber="12"; /// <summary> /// Child list range /// </summary> [ExcelRange("CHILDLIST1")] public IList<RowRangeServiceData1> MasterDoorChildList = new List<RowRangeServiceData1>(); } }
4.1.6. BlockRangeService
BlockRangeService is as the same as RowRangeService except that the range is specified by cell name. It is a service to process a rectangle region. If you only want to process some special region by service, BlockRangeService is very useful. You can split your complicated template into some simple region to process one by one. The range of RowRangeService is able to be specified by cell name too. In this case RowRangeService has the same action as BlockRangeService. About more detail see RowRangeService.
4.1.7. BlockRowListRangeService
BlockRowListRangeService is the same as RowListRangeService except that the range is specified by cell name. It is also a service to process a rectangle region, but in the region, data is output as list by rows. If the range of RowListRangeService is specified by cell name, RowListRangeService has the same action as BlockRowListRangeService. About more detail, see RowListRangeService.
4.1.8. ComplexBlockRowListRangeService
CpmplexBlockRowListRangeService is a list service whose range is defined by cell name and is able to have child ranges in the list elements. The behavior is like ComplexRowListRangeService. About more detail, see ComplexRowListRangeService.
4.1.9-10. ColumnListRangeService and ColumnListRangeServiceRL
ColumnListRangeService is similar to the RowListRangeService but the range is specified by column index. ColumnListRangeService outputs data from left to right as list. The first element is outputted on the left side of the range. In the opposite, the ColumnListRangeServiceRL outputs data from right to the left. The first element is outputted on the right side of the range. As the same as service of RowListRangeService, you must make sure all element’s columns have the same format. See the diagram following, the gray region is processed by ColumnListRangeServiceRL.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net"> <object id="ColumnListRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT" /> <property name="LeftTop" value="B2" /> <property name="RightBottom" value="EQ50" /> <property name="ServiceId" value="RowRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ColumnListRangeServiceTest_1_HEADER" /> <ref object="ColumnListRangeServiceTest_1_LIST" /> <ref object="ColumnListRangeServiceTest_1_RESULT" /> </list> </property> </object> <object id="ColumnListRangeServiceTest_1_HEADER" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="HEADER" /> <property name="LeftTop" value="CX4" /> <property name="RightBottom" value="DF48" /> <property name="ServiceId" value="BlockRangeService" /> </object> <object id="ColumnListRangeServiceTest_1_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="LIST" /> <property name="LeftTop" value="BZ4" /> <property name="RightBottom" value="CK48" /> <property name="ServiceId" value="ColumnListRangeServiceRL" /> </object> <object id="ColumnListRangeServiceTest_1_RESULT" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="RESULT" /> <property name="LeftTop" value="H4" /> <property name="RightBottom" value="Q48" /> <property name="ServiceId" value="BlockRangeService" /> </object> </objects>
using System; using System.Collections.Generic; using System.Linq; using System.Text; using JanaBiz.OpenXml; using JanaBiz.Common.Serialize; namespace OpenXmlDemoData.Data.Boat { [Serializable] [ExcelRange("ROOT")] public class BoatRace { public BoatRace() { } [ExcelRange("HEADER")] public BoatRaceHeader HeaderData = new BoatRaceHeader(); [ExcelRange("LIST")] public SerializableList<BoatRaceRecord> itemDataList = new SerializableList<BoatRaceRecord>(); [ExcelRange("RESULT")] public BoatRaceResult ResultData = new BoatRaceResult(); } }
4.1.11-12. ColumnCloneRangeService and ColumnCloneRangeServiceRL
ColumnCloneRangeService is similar to the RowCloneRangeService but the range is specified by column index. ColumnCloneRangeService clones specified columns and add cloned columns one by one from left to right. In the opposite, the ColumnCloneRangeServiceRL clones specified columns and add cloned columns one by one from right to left like the Japanese newspaper. Using ColumnCloneRangeService you don’t have to copy all elements format as ColumnListRangeService. After processed, column index of some ranges, formulas, objects will be recalculated and moved automatically.
The result is shown in the following.
4.1.13-14. ComplexColumnListRangeService and ComplexColumnListRangeServiceRL
ComplexColumnListRangeService is a list service like ColumnListRangeService. The difference between ComplexColumnListRangeService and ColumnListRangeService is that the element of ComplexColumnListRangeService’s list data is able to include child ranges and child range is able to be any range type. ColumnListRangeService’s element is not able to include child ranges. Because ComplexColumnListRangeService is also a list service, you must design the format for all elements in list as the same way as ColumnListRangeService. If in the element of ComplexColumnListRangeService there is a child range using ColumnListRangeService, make sure each element’s format is designed well too. See the following template file, template range configuration and data model.
Here ROOT range has two child ranges named TITLE and DATA. TITLE is a BlockRangeService and has one child range SIMAI which is processed by service BlockRowListRangeService. Let us see DATA range in detail. DATA range is processed by ComplexColumnListRangeService which has four child ranges; those of them are processed by BlockRowListRangeService. To understand how it works, please check the each child range’s region. ComplexColumnListRangeService adds the data from left to right. In the contrary, ComplexColumnListRangeServiceRL processes the data from right to left.
The following is the part of template.
Here is the template range configuration file.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net"> <object id="ComplexColumnListRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT" /> <property name="LeftTop" value="A1" /> <property name="RightBottom" value="IR147" /> <property name="ServiceId" value="RowRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ComplexColumnListRangeServiceTest_1_ROOT_TITLE" /> <ref object="ComplexColumnListRangeServiceTest_1_ROOT_DATA" /> </list> </property> </object> <object id="ComplexColumnListRangeServiceTest_1_ROOT_TITLE" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="TITLE" /> <property name="LeftTop" value="B2" /> <property name="RightBottom" value="K147" /> <property name="ServiceId" value="BlockRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ComplexColumnListRangeServiceTest_1_TITLE_SIMAI" /> </list> </property> </object> <object id="ComplexColumnListRangeServiceTest_1_TITLE_SIMAI" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="SIMAI" /> <property name="LeftTop" value="B32" /> <property name="RightBottom" value="B46" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="ComplexColumnListRangeServiceTest_1_ROOT_DATA" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="DATA" /> <property name="LeftTop" value="L2" /> <property name="RightBottom" value="U147" /> <property name="ServiceId" value="ComplexColumnListRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ComplexColumnListRangeServiceTest_1_DATA_NAME_LIST" /> <ref object="ComplexColumnListRangeServiceTest_1_DATA_DA_LIST" /> <ref object="ComplexColumnListRangeServiceTest_1_DATA_BAJYU_LIST" /> <ref object="ComplexColumnListRangeServiceTest_1_DATA_DA2_LIST" /> </list> </property> </object> <object id="ComplexColumnListRangeServiceTest_1_DATA_NAME_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="NAME_LIST" /> <property name="LeftTop" value="L32" /> <property name="RightBottom" value="L33" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="ComplexColumnListRangeServiceTest_1_DATA_DA_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="DA_LIST" /> <property name="LeftTop" value="L58" /> <property name="RightBottom" value="S59" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="ComplexColumnListRangeServiceTest_1_DATA_BAJYU_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="BAJYU_LIST" /> <property name="LeftTop" value="L66" /> <property name="RightBottom" value="U81" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="ComplexColumnListRangeServiceTest_1_DATA_DA2_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="DA2_LIST" /> <property name="LeftTop" value="L120" /> <property name="RightBottom" value="T121" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> </objects>
The result processed by ComplexColumnListRangeService is shown in the following.
4.1.15-16. ComplexColumnCloneRangeService and ComplexColumnCloneRangeServiceRL
ComplexColumnCloneRangeService is a list clone service like ColumnCloneRangeService. The difference between ComplexColumnCloneRangeService and ColumnCloneRangeService is that the element of ComplexColumnCloneRangeService’s list data is able to include child ranges and child range is able to be any range type. ColumnCloneRangeService’s element is not able to include child ranges. See the following template file, template range configuration and data model. Using ComplexColumnCloneRangeService, your template design becomes very simple. As the same as service of ColumnCloneRangeService, you don’t have to copy the format for each element rows in the list. Here we use ComplexColumnCloneRangeServiceRL to process the range of DATA, the cloned columns are added into the left of range DATA from right to left. The range of DATA has four child ranges. The child range of BAJYU_LIST has three elements; the first element region is from B66 to K81. Please check the output result. The column index of formulas and other objects in the right of range DATA will be changed, ExcelTDF automatically searches these objects and changes the column index of them.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net"> <object id="ComplexColumnCloneRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT" /> <property name="LeftTop" value="A1" /> <property name="RightBottom" value="Q148" /> <property name="ServiceId" value="RowRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CCCRST_1_TITLE" /> <ref object ="CCCRST_1_DATA"/> </list> </property> </object> <object id="CCCRST_1_TITLE" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="TITLE" /> <property name="LeftTop" value="L2" /> <property name="RightBottom" value="U147" /> <property name="ServiceId" value="BlockRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CCCRST_1_SIMAI" /> </list> </property> </object> <object id="CCCRST_1_SIMAI" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="SIMAI" /> <property name="LeftTop" value="L32" /> <property name="RightBottom" value="U33" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="CCCRST_1_DATA" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="DATA" /> <property name="LeftTop" value="B2" /> <property name="RightBottom" value="K147" /> <property name="ServiceId" value="ComplexColumnCloneRangeServiceRL" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CCCRST_1_DA_LIST" /> <ref object="CCCRST_1_BAJYU_LIST" /> <ref object="CCCRST_1_DA2_LIST" /> <ref object="CCCRST_1_NAME_LIST" /> </list> </property> </object> <object id="CCCRST_1_DA_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="DA_LIST" /> <property name="LeftTop" value="C58" /> <property name="RightBottom" value="I59" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="CCCRST_1_BAJYU_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="BAJYU_LIST" /> <property name="LeftTop" value="B66" /> <property name="RightBottom" value="K81" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="CCCRST_1_DA2_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="DA2_LIST" /> <property name="LeftTop" value="B120" /> <property name="RightBottom" value="K121" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="CCCRST_1_NAME_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="NAME_LIST" /> <property name="LeftTop" value="B32" /> <property name="RightBottom" value="K33" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="CCCRST_SHEET2" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT" /> <property name="LeftTop" value="A1" /> <property name="RightBottom" value="BP41" /> <property name="ServiceId" value="RowRangeService" /> </object> <object id="CCCRST_SHEET3" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT" /> <property name="LeftTop" value="A1" /> <property name="RightBottom" value="BP41" /> <property name="ServiceId" value="RowRangeService" /> </object> </objects>
Following is the result document processed by ComplexCiolumnCloneRangeService.
4.1.17. MultiplePageRangeService
MultiplePageRangeService is created to process some complicated reports or excel documents which has multiple different page designs. Sometimes we want to create a report which has page header, page footer, report header and report footer. In ExcelTDF, you can considered them as some ranges. In fact, using RowRangeService or BlockRowRangeService, you can output header and footer information easily. If your document or report has only one page, you can do your design using services explained on the above. By assembling those services, ExcelTDF is able to output various documents and reports as what you want. If your report has more pages that all of them are the same design, you can use ComplexRowCloneRangeService or ComplexRowListRangeService to output your report page by page. In this case, a page is an element of list. MultiplePageRangeService provides ability for creating document and report which has different page design while a list data might be outputted across all pages. Usually we want to output some summary information into the first page or last page, the detail information page by page. Depending on the size of list data, the number of pages to be outputted will be variable. MultiplePageRangeService controls these behaviors according to the properties set in the template range configuration file. MultiplePageRangeService is able to process document and report which has two or three different page designs.
Let us see following template, which has two reports, owner sales report and parking sales report. In the template, owner sales report has three different pages and parking sales report has one page. We want to output owner report and parking report for one owner in one worksheet. One owner maybe has a lot of parking, we need to output parking name, parking sales amount as one line for each parking in the owner report and output parking sales amount detail data in the parking report.
Download template SalesReport
Parking sales report template page is omitted.
The following is template range configuration file, download SalesReport
In the worksheet, there are two child ranges, and they are owner report and parking report named OWNER_REPORT, PARKING_REPORT. Owner report range has three page child ranges, OWNER_PAGE1, OWNER_PAGE2, OWNER_PAGE3. For three page ranges, there is a list range of sales amount for each parking. Assume the owner has 56 parkings, the list size will be 56. In the template configuration, the PageSize of the range PARK_DATA_LIST in OWNER_PAGE1, OWNER_PAGE2, OWNER_PAGE3 is 15, so the owner report will be output 4 pages. The property of IsRepeat in internal page OWNER_PAGE2, which is set to true, means this page is able to output repeated. ExcelTDF controls output behavior based on those properties. PARK_DATA_LIST range is outputted across all pages, this behavior is controlled by property of IsCrosssPage. Besides owner report, each parking detail data is outputed as one page, thus in the worksheet we output owner report as 4 pages and parking report as 56 pages.
<?xml version="1.0" encoding="utf-8" ?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="SalesReport_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="SALES_REPORT"/> <property name="LeftTop" value="B1"/> <property name="RightBottom" value="BG209"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="OwnerReport"/> <ref object="ParkReport"/> </list> </property> </object> <object id="OwnerReport" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="OWNER_REPORT"/> <property name="LeftTop" value="B1"/> <property name="RightBottom" value="BG150"/> <property name="ServiceId" value="MultiplePageRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="OwnerPage1"/> <ref object="OwnerPage2"/> <ref object="OwnerPage3"/> </list> </property> </object> <object id="OwnerPage1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="OWNER_PAGE1"/> <property name="LeftTop" value="B1"/> <property name="RightBottom" value="BG50"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ParkData1"/> </list> </property> </object> <object id="ParkData1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARK_DATA_LIST"/> <property name="LeftTop" value="B24"/> <property name="RightBottom" value="BG24"/> <property name="ServiceId" value="RowListRangeService"/> <property name="PageSize" value="15"/> <property name="IsCrossPage" value="true"/> </object> <object id="OwnerPage2" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="OWNER_PAGE2"/> <property name="LeftTop" value="B51"/> <property name="RightBottom" value="AT100"/> <property name="ServiceId" value="ComplexRowCloneRangeService"/> <property name="IsRepeat" value="true"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ParkData2"/> </list> </property> </object> <object id="ParkData2" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARK_DATA_LIST"/> <property name="LeftTop" value="B74"/> <property name="RightBottom" value="AJ74"/> <property name="ServiceId" value="RowListRangeService"/> <property name="PageSize" value="15"/> <property name="IsCrossPage" value="true"/> </object> <object id="OwnerPage3" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="OWNER_PAGE3"/> <property name="LeftTop" value="B101"/> <property name="RightBottom" value="AT150"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ParkData3"/> </list> </property> </object> <object id="ParkData3" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARK_DATA_LIST"/> <property name="LeftTop" value="B124"/> <property name="RightBottom" value="AJ124"/> <property name="ServiceId" value="RowListRangeService"/> <property name="PageSize" value="15"/> <property name="IsCrossPage" value="true"/> </object> <object id="ParkReport" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARK_REPORT"/> <property name="LeftTop" value="B151"/> <property name="RightBottom" value="AT209"/> <property name="ServiceId" value="ComplexRowCloneRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="AdditionItems"/> </list> </property> </object> <object id="AdditionItems" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ADDITIONS"/> <property name="LeftTop" value="B192"/> <property name="RightBottom" value="AJ192"/> <property name="ServiceId" value="BlockRowListRangeService"/> </object> </objects>
Download the result of report SalesReport result
The following is a part of data model.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using JanaBiz.OpenXml;
namespace OpenXmlDemoData.Data
{
/// <summary>
/// sales report
/// </summary>
[ExcelRange("SALES_REPORT")]
public class SalesReportData
{
#region constructor
public SalesReportData()
{
}
#endregion
/// <summary>
/// all parking data of owner
/// </summary>
[ExcelRange("OWNER_REPORT")]
public OwnerSalesReport ownerReport = new OwnerSalesReport();
/// <summary>
/// sales data of parking
/// </summary>
[ExcelRange("PARK_REPORT")]
public IList<OwnerParkSalesData> ParkList
{
get { return ownerReport.ParkList; }
}
}
}
4.1.18. BlockRowCloneRangeService
BlockRowCloneRangeService is similar to the RowCloneRangeService. The difference is that BlockRowCloneRangeService clones only cells from specified top row to bottom row between specified columns, so the row index of cells that is between the specified columns and row index is greater than the bottom row will be moved after cloned rows are added.
On the other hand, the RowCloneRangeService clones cells from specified top row to bottom row without the limitation of column and moves all cell’s index below the range itself after the cloned rows are added.
The result is shown in the following, you can see the range B3 and K4 is cloned and added on the below. The cells that the column index is between column B and K and the row is greater than 4 are moved.
4.1.19. BlockColumnCloneRangeService
BlockColumnCloneRangeService is similar to the ColumnCloneRangeService. The difference is that BlockColumnCloneRangeService clones only cells from specified left column to right column between specified rows, so the column index of cells that is between the specified rows and greater than the right column will be moved after cloned columns are added.
On the other hand, the ColumnCloneRangeService clones columns from specified left column to right column without row limitation, the columns index that is greater than the right column will be changed after cloned columns is added.
The result is shown in the following, you can see the range B3 and C23 is cloned and added on the right. The cells that row is between row 3 and 23 and column is greater than C are moved.
4.2. IExcelCellService & IExcelFunctionService
In ExcelTDF, one of the most flexible functionality is that you can create your own custom service. Custom service lets you control your cell output in the template design, that is similar to the excel function. Custom service has two types. The implementation class of IExcelCellService is custom cell service while the implementation class of IExcelFunctionService is custom function service. Cell service has no return result, in the contrary, function service return a string as result. In the custom service, the parameter can be template variable, cell name, constant of text or number. The base class of ExcelCellServiceBase provides a lot of methods to get parameter’s cell name or values. On the above example of MultiplePageRangeService, we used XSCHECKBOX, XSPAGEBREAK, XSBARCODE cell services and XFPRANGE and XFSRANGE function services. If parameter of service is cell name, the index of cell’s row and column will be recalculated. If the parameter of service is template variable, then ExcelTDF will search template variable’s cell name.
ExcelTDF provides following cell services and function services now.
4.2.1. Cell Service
4.2.1.1. XSCHAR
Output amount digit by digit.
Format:
%{=XSCHAR(TEXT, 1|LEFT|left|0|RIGHT|right)}
%{=XSCHAR(TEXT, 1|LEFT|left|0|RIGHT|right, LEADING)}
%{=XSCHAR(TEXT, 1|LEFT|left|0|RIGHT|right, LEADING, TAILING)}
where TEXT is a template variable or cell name or constant of number. Second parameter of 1|LEFT|left indicates that output digit from left to right(left alignment) start with the cell of this service. 0|RIGHT|right indicates that output digit from right to left (right alignment) start with the cell of this service. The third and fourth parameter is appended char on the beginning or ending of char, that maybe dollar marker etc.
Example:
%{=XSCHAR(“123999″, 0, “$”)}, %{=XSCHAR(A4, 1, “$”, “-”)}
4.2.1.2. XSCHECKBOX
Output a checkbox. The checkbox is not a vb control, it is just using some special font.
Format:
%{=XSCHECKBOX(FLAG)}
%{=XSCHECKBOX(FLAG,TEXT)}
where FLAG is a flag to indicate checked or not, 1|true is checked, otherwise not checked. The second parameter of TEXT is a string after checkbox. Both FLAG and TEXT can be a template variable, cell name of constant string, number.
Example:
%{=XSCHECKBOX(PARKTYPE)}, %{=XSCHECKBOX(PARKTYPE, PARKNAME)}
In data model
[Excel("PARKTYPE")]
public int ParkType
[Excel("PARKNAME")]
public string ParkName
4.2.1.3. XSBARCODE
XSBARCODE outputs bar code on the specified image. In order to output bar code you must add a image to template where your bar code will be output. Bar code will be output as the same size as image.
Format:
%{=XSBARCODE(IMAGENAME, BARCODETEXT, TYPE)}
%{=XSBARCODE(IMAGENAME, BARCODETEXT, TYPE, SHOW_LABEL)}
IMAGENAME: the image name you have added into template
BARCODETEXT: bar code string, which can be template variable, cell name or constant text, number
TYPE: bar code type
SHOW_LABEL: 1 or true specify to output string of bar code text.
Following bar code types are supported:
UCC12
UPCA, UPC-A
UCC13, EAN13, EAN-13
Interleaved2of5
Industrial2of5
Standard2of5
LOGMARS
CODE39
CODE39Extended
Codabar
PostNet
ISBN,BOOKLAND
JAN13,JAN-13
UPC_SUPPLEMENTAL_2DIGIT
MSI_Mod10,MSI_2Mod10,MSI_Mod11,MSI_Mod11_Mod10,Modified_Plessey
UPC_SUPPLEMENTAL_5DIGIT,UPCE,UPC-E
EAN8,EAN-8
USD8,CODE11
CODE128,CODE128A
CODE128B,CODE128C
ITF14,CODE93
Example:
%{=XSBARCODE(“BCODE”, BARCODENO, “UPC-A”)}
BCODE is image name that bar code image will replace it.
4.2.1.4. XSPAGEBREAK
Add excel page break in the worksheet for document and report printing. XSPAGEBREAK is very useful for more page output.
Format:
%{=XSPAGEBREAK}
%{=XSPAGEBREAK(ROW|CELL|VAR)}
If no parameter is specified , add page break on the row of this service written. If row or cell is specified add page break on the specified row. If template variable is specified, add page break on the row on which template variable is.
Example: %{=XSPAGEBREAK}
On the example of service MultiplePageRangeService, XSPAGEBREAK is added in the template.
XSPAGEBREAK are added on the last row in last page of owner report and parking report. Parking report is a page processed a clone service. When parking page is cloned, the page break will be added on the last row of cloned page.
4.2.1.5. XSVDLIST
Set cell as pull down list. It is the same as the menu of Data/Data Input/List in the Excel.
Format:
%{VAR=XSVDLIST(“A,B,C”|AJ12:AJ20|VARIABLE)}
XSVDLIST is some different from services above. The left side variable name is the value of cell in pull down list while the parameter in expression of XSVDLIST is pull down items which is able to be a string with comma separated or cell range or variable which is in the list range. The value of VAR can be referenced by other expression and can be saved back into data model as other variables.
Example:
%{ITEM=XSVDLIST(“iPod,iPhone,iPad”)}
In data model:
[Excel("ITEM")]
public string Item {get; set;}
4.2.2. Function Service
Custom function service can be used in expression of custom cell service, other custom function service and Excel formula.
4.2.2.1. XFPRANGE
Search variable from specified parent range and return the cell name such as A12 or cell name string such as A12,A13,A14 if variable is in the list. Because that ExcelTDF search variable first from range of itself, then the child ranges, if you have the same variable name in different range, this function maybe useful, you can search variable from your specified range. Usually, in child range you can reference parent range’s variable directly by this function.
Format:
%{=XFPRANGE(VAR)}
%{=XFPRANGE(VAR, PARENT_LEVEL)}
VAR: template variable to be searched
PARENT_LEVEL: the number of parent hierarchy
second parameter is the specified number from which parent range to search variable. For example, if the second parent is 2, search variable VAR from the parent’s parent range. If no second parameter is specified, search from parent range
Example:
#C{=SUM(XFPRANGE(FURIKOMI, 1))}
On the above MultiplePageRangeService, in the first page of owner report we want to output total sum of FURIKOMI which is across in the first page, internal page and last page. So we specified search FURIKOMI from parent range, that is OWNER_REPORT which includes all pages. FURIKOMI is a variable in a list, so SUM(XFPRANGE(FURIKOMI, 1)) is the total amount.
4.2.2.2. XFSRANGE
Search variable from all brother ranges including itself and return the cell name such as A12 or cell name string such as A12,A13,A14 if variable is in the list.
Format:
%{=XFSRANGE(VAR)}
VAR is the variable to be searched.
Example:
#C{小計=SUM(FURIKOMI)}
#C{合計=SUM(XFSRANGE(小計))}
On the above MultiplePageRangeService, in owner report, each page has a variable named 小計 which is the sum amount of parking in that page and in the last page there is a variable 合計 which is the sum of all 小計 in each page. You can download sales report result on the above to see how it works.
4.2.2.3. XFCELL
This is a simple function to get variable’s cell name and return cell name or cell name string such as A12,A13,A14.
Format:
%{=XFCELL(VAR)}
Example:
%{=XFCELL(ITEM)}
4.2.2.4. XFMATCH
XFMATCH is used to search some values from specified searching range, if matched, it returns the cell names or values.
Format:
%{XFMATCH(SEARCH_VALUES,SEARCH_RANGE)
%{XFMATCH(SEARCH_VALUES,SEARCH_RANGE, PARENT_HIERARCHY)
SEARCH_VALUES: the values to be searched. which can be a variable, cell name such as AJ12 or AJ12:AK20, or constant string separated by comma such as “A,B,C”. This function searches value by some orders. Assume SEARCH_VALUES is specified as “A,B,C”, function will search A first, if found return the result, then B, then C.
SEARCH_RANGE: the search range which can be a variable, cell name, or any constant string.
PARENT_HIERARCHY: Specify from which template range to search, 1 is the parent, 2 is the parent’s parent template range.
Example:
#{=CONCATENATE(XFOFFSET(XFMATCH(“◎,○,▲”, 本紙, 1), 0, 1), “=”, XFOFFSET(XFMATCH(“○,▲,△”, 本紙, 1), 0, 1), XFOFFSET(XFMATCH(“△”, 本紙, 1), 0, 1))}
In the example of ColumnCloneRangeService, we used function as above to search the cell which has one of values ◎,○,▲. You can see the function is used in Excel formula expression.
4.2.2.5. XFOFFSET
Get cell name with the specified offset. This function is like excel formula function OFFSET.
Format:
%{=XFOFFSET(VAR,ROW,COL,PARENT_HIERARCHY)}
VAR: variable to be searched, which is a template variable.
ROW: row offset number
COL: column offset number
PARENT_HIERARCHY: Specify from which template range to search, 1 is the parent, 2 is the parent’s parent template range.
Example:
#{=CONCATENATE(XFOFFSET(XFMATCH(“◎,○,▲”, 本紙, 1), 0, 1), “=”, XFOFFSET(XFMATCH(“○,▲,△”, 本紙, 1), 0, 1), XFOFFSET(XFMATCH(“△”, 本紙, 1), 0, 1))}
4.3. How to create your service
ExcelTDF provides mechanism to create your own range service, cell service and function service. The three interfaces are used to let you create your service easily. These interfaces are IExcelRangeService, IExcelCellService and IExcelFunctionService.
4.3.1. How to create template range service, IExcelRangeService
See the example of MyRowRangeService which do the same works as RowRangeService.
4.3.1.1. Create IExcelRangeService implementation class
using System; using System.Collections.Generic; using System.Linq; using System.Text; using log4net; using JanaBiz.Common.Serialize; using JanaBiz.OpenXml.Data; namespace myservice { /// <summary> /// Process excel range defined by top row, and bottom row. /// Template variable are replaced by value in data model. /// This is simple range. worksheet root range is this type. /// </summary> public class MyRowRangeService : ExcelRangeServiceBase, IExcelRangeService { /// <summary> /// Logger /// </summary> static readonly ILog logger = LogManager.GetLogger(typeof(MyRowRangeService)); /// <summary> /// constructor /// </summary> public MyRowRangeService() : base() { } #region interface method /// <summary> /// Parse specified worksheet using data provider /// </summary> /// <param name="sheetIndex"></param> public virtual void Parse(int sheetIndex) { //if no data, not processing if (ExcelRange == null || ExcelRange.DataProvider == null) return; //get data provider IRangeDataProvider dataProvider = ExcelRange.DataProvider; //reset mapping info ExcelRange.DataMapping.Clear(); //get mapping info from data provider SerializableDictionary<string, ExcelAttribute> dmp = dataProvider.GetMappingData(dataProvider[0]); ExcelRange.DataMapping.Add(dmp); //set data values into mapped variables SetNewCellValues(ExcelRange, dmp, ExcelRange.Top, ExcelRange.Bottom, 0, 0); //no row or column index moved or changed ExcelRange.ChangedRowCount = 0; //process flag of this range ExcelRange.Parsed = true; } #endregion } }
4.3.1.2. Add your service object into configuration file
In your application configuration file, there is Spring.NET configuration section of object; add your object as following.
<!-- my range service--> <objects> <object id="MyRowRangeService" type="myservice.MyRowRangeService, Your_Assembly(dll)" singleton="false"/> </objects>
4.3.1.3. Set your service into your template range configuration
<?xml version="1.0" encoding="shift_jis"?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="MyRowRangeService_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT"/> <property name="Top" value="8"/> <property name="Bottom" value="10"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="MyRangeService"/> </list> </property> </object> <object id="MyRangeService" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="MYRANGE"/> <property name="LeftTop" value="L8"/> <property name="RightBottom" value="BE10"/> <property name="ServiceId" value="MyRowRangeService"/> </object> </objects>
That is all, now ExcelTDF will call your service of MyRowRangeService to process specified range in your template configuration file.
4.3.2. How to create your custom cell service, IExcelCellService
See the example of MyVDListCellService which performs the same work as XSVDLIST
4.3.2.1. Create implementation class of IExcelCellService
using System; using System.Collections.Generic; using System.Collections; using System.Linq; using System.Text; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using log4net; using JanaBiz.ExcelFormulaParser; namespace myservice { /// <summary> /// My validate list cell service /// </summary> public class MyVDListCellService : ExcelCellServiceBase, IExcelCellService { static readonly ILog logger = LogManager.GetLogger(typeof(MyVDListCellService)); public MyVDListCellService() : base() { } public void Parse(int sheetIndex) { //1: get parameters Parameters = GetExpressionParameters(ExprTreeNode, sheetIndex); //2: check exists function variable in template if (!ExcelRange.IsMyVariable(CellData.VarName)) { throw new ServiceException(this, "E_NO_VDLIST_VAR", CellData.VarName); } //3: check if no paramter if (Parameters.Count == 0) { throw new ServiceException(this, "E_NO_PARAM"); } //4: check if parameter is empty string listValues = Parameters[0].Param; if (string.IsNullOrEmpty(listValues)) { throw new ServiceException(this, "E_EMPTY_PARAM"); } //5: parameter type check switch (Parameters[0].ParamType) { //if it is variable case ParamType.VARIABLE: IList<string> cells = GetReferencesOfVariable(listValues, sheetIndex, ExcelRange, ListIndex); if (cells == null || cells.Count == 0) { throw new ServiceException(this, "E_VAR_NOT_FOUND", listValues); } string[] cellrefs = OpenXmlSpreadsheet.CreateMergeCellname(WorksheetPart, cells.ToArray()); if (cellrefs.Length > 1) { listValues = string.Format("{0}:{1}", cellrefs[0], cellrefs[1]); } break; //if it is cell reference case ParamType.CELL: //if it is text case ParamType.TEXT: break; } //do your work here //set data validate list formula1 SetCellValue(CellData.DataType, CellData.CellName, CellData.CellValue); OpenXmlSpreadsheet.SetDataValidateFormula1(WorksheetPart, CellData.CellName, listValues); } } }
4.3.2.2. Add your service object into configuration file
In your application configuration file, there is Spring.NET configuration section of object, add your object as following.
<!-- my cell service--> <objects> <object id="MYVDLIST" type="myservice.MyVDListCellService, Your_Assembly(dll)" singleton="false"/> </objects>
Now you can use MYVDLIST in your template. ExcelTDF will parse template and build your cell service MYVDLIST by calling your created class.
4.3.3. How to create your custom function service, IExcelFunctionService
Custom function service is the same as custom cell service except that function service must return a result. The result is set in property of FunctionResultValue. See the following example; it does the same work as the XFCELL function explained above.
4.3.3.1. Create your implementation class of IExcelFunctionService
using System; using System.Collections.Generic; using System.Collections; using System.Linq; using System.Text; using log4net; using JanaBiz.OpenXml; namespace myservice { /// <summary> /// Get variable's cell name /// %{=XFCELL(VAR)} /// </summary> public class MyCellFunctionService : ExcelCellServiceBase, IExcelFunctionService { static readonly ILog logger = LogManager.GetLogger(typeof(MyCellFunctionService)); public MyCellFunctionService() : base() { } /// <summary> /// do your work here /// </summary> /// <param name="sheetIndex"></param> public void Parse(int sheetIndex) { Parameters = GetExpressionParameters(ExprTreeNode, sheetIndex); //1: no parameter check if (Parameters.Count == 0) throw new ServiceException(this, "E_NO_PARAM"); //2: get cellname parameter string var1 = Parameters[0].Param; //3: empty parameter check if (string.IsNullOrEmpty(var1)) throw new ServiceException(this, "E_EMPTY_PARAM"); //4: parameter type switch (Parameters[0].ParamType) { //it is a variable case ParamType.VARIABLE: FunctionResultValue = GetVariableResultAsString( GetReferencesOfVariable(var1, sheetIndex, ExcelRange, ListIndex)); break; //it is a cell name case ParamType.CELL: FunctionResultValue = var1; break; //for others default: throw new ServiceException(this, "E_NOT_CELLNAME", var1); } } } }
4.3.3.2. Add your service object into configuration file
In your application configuration file, there is Spring.NET configuration section of object, add your object as following.
<!-- my function service--> <objects> <object id="MYCELL" type="myservice.MyCellFunctionService, Your_Assembly(dll)" singleton="false"/> </objects>
Now you can use MYCELL in your template. ExcelTDF will parse template and build your cell service MYCELL by calling your created class.
3.1. How to use ExcelTDF in your solution
As the following diagram shows, ExcelTDF is able to run at server-side, client-side and client-only. The interface and usage is the same wherever the ExcelTDF is running. For the both server-side and client-side, the templates of excel are managed on the server. The difference between server-side and client-side is that client-side provides auto saving functionality.
3.2. ExcelTDF Core classes
ExcelTDF does almost of work for you by some core classes. Excel2007TemplateParser and Excel2007DataParser are the two of these classes. But the convenient way for you is using implementation classes of IExcelHandler or IOpenXmlHandler interface named ExcelHandler, OpenXmlHandler. Excel2007TemplateParser, as the name of class, analyzes the template file and load the template range configuration file. After template is analyzed, template parser will know the mapping relationship of variables/cells and template range tree. Excel2007DataParser analyzes your data class structure and its meta data or SQL data. Metadata named ExcelRange and ExcelAttribute defines range information and Excel cell variable information. After data is analyzed, Excel2007DataParser will know the data/variable mapping relationship and get ExcelRange‘s tree structure that is used to used to determine how to process template.
3.3. How to get ExcelTDF work
In order to let ExcelTDF work, there are fourth steps to do, the first two steps is only needed to do one time.
. Set Configuration
. Creating template file by Excel 2007 above and creating template range configuration file. If you use Metadata to define all range information, this step can be omitted
. Creating IDataProvider class or Data Model class to get data
. Download or open your parsed document or report
3.4. Set Configuration
When you use ExcelTDF to process template, you should tell ExcelTDF how to get template files and template range configuration files. Your template files and template range configuration files maybe saved as files in specified directory or saved in database or gotten from service.
3.4.1. Manage templates by files
Your template excel file and template range configuration file can be managed on server by files. In ExcelTDF we call it repository. Typically your configuration file is named as Repository.config. It is loaded by ExcelTDF when application starting.
The following is the content of Repository.config. You can add more repositories in this file.
<?xml version="1.0" encoding="utf-8" ?> <objects xmlns="http://www.springframework.net"> <object id="mydemo" type="JanaBiz.OpenXml.Repository.FileSystemRepositoryManager, JanaBiz.OpenXml"> <property name="RepositoryName" value="myrepository"/> <property name="RepositoryUri" value="../../Templates"/> </object> </objects>
On the above, the root repository directory is ../../Templates/myrepository. This is relative path to the executable dll. Certainlly, it is can be any full path. It is also be able to database connection string or others for RepositoryUri.
Usually you can use following two ways to load Repository.config when application starting.
a). For console application or Windows form application
[assembly: RegisterRepository("your_namespace.Repository.config")]
b). For all type of application
OpenXmlContext.RegisterRepository("your_namespace.Repository.config");
Refresh your repository
If your template range configuration file is changed or you added, removed some lines in template.xml and resource.xml, you must refresh context to load new configuration information. ExcelTDF provides a method in class OpenXmlContext to reload repositories.
3.4.2. Manage templates by database
If you get template file and template range configuration file from database or other stream resource, you don’t have to set repository information . The resource handler’s protocol named “memory” will load your template file, template range configuration file into context. In order to do this work, you must create a class that implements the interface ITemplateService. By following code, you can set your database template service which implements ITemplateService. This tells ExcelTDF MyDatabaseTemplateService is used to get template file and template range configuration, similar to handler.GetTemplateService(“myconfig”) which use LocalTemplateService to load template files in the repository. There are no access limitation when using database to manage templates, you can do yourself.
ExcelHandler handler = new ExcelHandler(); //implements ITemplateService interface MyDatabaseTemplateService service = new MyTemplateService(); handler.SetTemplateServiceservice(service); //or MyDatabaseTemplateService service = handler.GetTemplateServiceservice<MyDatabaseTemplateService >();
3.4.3. Get templates from web service
If your templates managed on server and you use IExcelHandler on client side, you can tell ExcelTDF getting templates by web service. In this case, your web service must implement methods defined in ITemplateService but don’t need to implement the interface of ITemplateService. This feature gives you capability of managing your templates by web service very simple. Following is some code.
ExcelHandler handler = new ExcelHandler();
...
TemplateServiceSoapClient templateService = handler.GetTemplateService<TemplateServiceSoapClient>();
3.5. Template File
keywords:
. General variable
${VAR1}
. Formula variable
#{=SUM(A20*SUM(VAR1)+VAR2)}
. Custom service variable
%{=XSPAGEBEAK}
. Parameter variable
?{VAR=PARAM1}
. Template Range binding
[ ExcelRange("MyRange", "A1", "Z20", "RowRangeService")]
. Excel Variable binding
[Excel("VAR1"......)]
3.5.1. Variables in Template
When template processing, IExcel2007TemplateParser will search variables in the template and collect mapped information of variable and cell name. As the result, variables are mapped into data value after Excel2007DataParser parsed document. Template variable is a name of an arbitrary text string, whose formats are defined as ${..}, #{..}, %{..}, ?{..} four types.
3.5.1.1. General variable: ${VAR1}
VAR1 is a general variable. ExcelTDF will replace the the variable with the value in data model or SQL query data or some others depending on what data provider is used. For the data model, here is an example.
[ExcelRange("ROOT", "A1, "C3", "RowRangeService")]
public class MyTestMode
[Excel("VAR1")]
public string MyName {get; set; }
}
As the above, after processed, the variable of ${VAR1} will be replaced by value of MyName property in the data mode of MyTestMode. If ${VAR1} is in list range and MyTestMode is element of list, The ${VAR1} will be the value of each element MyTestMode in the list.
3.5.1.2. Formula variable: #{VAR2=SUM(VAR1, CJ12, 20)}
Formula variable is an Excel formula expression variable. After processed, the cell of formula variable will be a normal Excel formula function expression, such as =SUM(R12) etc. In the above expression, the left side of the equal “=” is variable name VAR2, and the right side is expression. Here VAR2′s value is the sum of VAR1. ExcelTDF will search VAR1 variable and compute the cell name of VAR1 automatically. In the expression of formula variable, you can use both normal cell name, such as CJ12, AR12:AR20, $AR$20 and variable name. ExcelTDF is able to identify expression variable type of cell name, variable, text and number. For example, assume that VAR1 is in the list range and the cells are AK20,AK21,AK22 after range is processed, the variable of #{VAR2=SUM(VAR1, CJ12, 20)} will be explained as the sum of =SUM(AK20,AK21,AK22, CJ12, 20).
#{VAR2=SUM(VAR1, CJ12, 20)} --> =SUM(AK20,AK21,AK22, CJ12, 20)
[ExcelRange("ROOT", "A1, "C3", "RowRangeService")]
public class MyTestMode
[Excel("VAR1")]
public string MyName {get; set; }
[Excel("VAR2")]
public double Sum {get; set; }
}
Formula variable expression is able to has sub expression. Generally no limitation on the formula expression, you can use as normal excel file. Here is complicated one. The variable’s name is MYNO, in the expression, no other variables referenced.
#{MYNO=IF(ISERROR(MATCH("◎", R6:BZ6, 0))=FALSE,
HLOOKUP("◎", R6:BZ9, 3, FALSE),
IF(ISERROR(MATCH("○", R6:BZ6, 0))=FALSE,
HLOOKUP("○", R6:BZ9, 3, FALSE),
IF(ISERROR(MATCH("▲", R6:BZ6, 0))=FALSE,
HLOOKUP("▲", R6:BZ9, 3, FALSE),
IF(ISERROR(MATCH("△", R6:BZ6, 0))=FALSE,
HLOOKUP("△", R6:BZ9, 3, FALSE),
""
)
)
)
)}
The following are also formula variables. In the expression, TRACKNO, FACTOR, QUANTITY, PRICE, BASEPRICE are referenced variables, ExcelTDF will replace them with correct cell names when building formula.
#{VAR2=CONCATENATE("ABCD",LEFT(TRACKNO,1))}
#{VAR2=SUM(FACTOR*SUM(QUANTITY*PRICE))+ BASEPRICE*15%}
If no other variable references VAR2, the name of VAR2 can be omitted. for example
#{=SUM(FACTOR*SUM(QUANTITY*PRICE))+ BASEPRICE*15%}
Certainly, you can use excel formula directly if the cell name is not changed dynamically and don’t need to reference a variable. ExcelTDF don’t process them, for example =SUM(12*C12), this is not a variable.
In the excel formula variable expression, you can use custom custom function service as the same as Excel function. About custom function service, please see next section of Custom service variable.
3.5.1.3. Custom service variable: %{VAR3=XFSRANGE(RACENO)}
Custom service variable is customized cell service or function service, which is scalable functionality provided by ExcelTDF. ExcelTDF provides several custom cell services and function services.
You can develop your custom cell service and function service too. Custom services are used to processing special cells or some computing functionality. There are two types of custom services, one is Cell Service which implements interface of IExcelCellService, and another is Function Service which implements interface of IExcelFunctionService. IExcelFunctionService is a sub interface of IExcelCellService. The two types of services are used for different purpose. In order to distinguish from Excel formula functions, cell service name and function service name in ExcelTDF start with XS and XF. In fact the name is the object id defined in the Spring.NET configuration file.
<object id="XFSRANGE" type="JanaBiz.OpenXml.Service.XFSiblingRangeFunctionService, JanaBiz.OpenXml" singleton="false"> </object>
[ExcelRange("ROOT", "A1, "C3", "RowRangeService")]
public class MyTestMode
[Excel("VAR1")]
public string MyName {get; set; }
[Excel("VAR2")]
public double Sum {get; set; }
[Excel("VAR3")]
public string No {get; set; }
}
If no other variable references VAR3, the name of VAR3 can be omitted. for example
%{=XFSRANGE(VAR1)}
3.5.1.3.1. Cell Service
Cell service does some special processing, for example, showing a barcode. Cell service has no result returned. Following are some cell services provided by ExcelTDF.
XSBARCODE: output a barcode
XSCHECKBOX: output a checkbox
XSPAGEBREAK: add page break of excel
XSVDLIST: showing cell as pulldown list
%{=XSBARCODE("BCODE", BARCODE_NO, "UPC-A")}
%{=XSCHECKBOX(1, PLAYER)}
%{=XSPAGEBREAK(20)}
%{AAA=XSVDLIST(RESULT)}
On the above, in the expression, BARCODE_NO, PLAYER, RESULT is referenced template variables.
3.5.1.3.2. Function Service
Function Service does some processing and returns the result as string. Depending on what you want to do, the function’s result maybe is variable’s cell name or variable’s value. The function is able to have sub expression in which another custom function might be called. The following is a example.
%{=XFOFFSET(XFMATCH("◎,○,▲", RACENO, 1), 0, 1)}
In the above, custom function XFMATCH is called, and the result of XFMATCH becomes a parameter of custom function XFOFFSET.
Because custom function returns the result, the custom function can be called in expression of Excel formula variable expression, such as the below.
#{=CONCATENATE(XFOFFSET(XFMATCH("◎,○,▲", RACENO, 1), 0, 1), "=", XFOFFSET(XFMATCH("○,▲,△", RACENO, 1), 0, 1), XFOFFSET(XFMATCH("△", RACENO, 1), 0, 1))}
In the above, in excel formula function expression CONCATENATE, custom function XFOFFSET and XFMATCH are called.
Note: Custom function service can not call excel function as sub expression because the custom function service and cell service are processed only one time while workbook are loading, not in the period of running.
3.5.1.4. Parameter variable: ?{VAR2}
Parameter variable is used to specify parameter when searching the data. This is run-time functionality like CrystalReport, in the version 1.0.0.0, no support for parameter variable.
3.5.1.5. Data type of variable
In ExcelTDF, you can specify data type forcibly. You just need to add a special char after the leading of letter. N is number type, C is currency type, D is date type. If no N, C, D specified, the variable considered as general string type. In the version of 1.0.0.0, type N, C will forcibly set cell type as NUMBER, D is now processed as string type. Don’t confuse the data type with data output format. Output format such as $#,###,### is set by excel on the template designing, data type just tell excel the output data is whether a number or not etc. The following is examples.
General variable ${VAR1}, $N{VAR1}, $C{VAR1}, $D{VAR1}
${VAR1}: output as type of string or standard
$N{VAR1}: output should be a number and ExcelTDF set the cell’s type to Number
$C{VAR1}: output should be currency and ExcelTDF set the cell’s type to Number
$D{VAR1}: output should be date and ExcelTDF does as string or standard in the version 1.0.0.0
The same rules are used for formula variables, custom service variables and parameter variables.
Formula variable: #{..}, #N{..}, #C{..}, #D{..}
Custom service variable: %{..}, %N{..}, %C{..}, %D{..}
Parameter variable: ?{..}, ?N{..}, ?C{..}, ?D{..}
3.5.1.6. The Range of variable
In a range, variable must be unique, but in different range, there is no this limitation. When a variable is referenced by formula or custom service, ExcelTDF try to search the variable and find cell names or values of variable. ExcelTDF search variable by some orders. ExcelTDF always search expression variable first from the range of variable itself. If not found, ExcelTDF then search expression variable from child ranges. Last, try to search expression variable from all. Understanding the searching order will help you to get correct result.
3.5.2. Template range
Template range is an excel range specified by row index or column index or excel cell name. Range Service Name must be specified to identify which template range to be processed by ExcelTDF. Template range is defined both by object in Spring.NET configuration file and by Metadata defined in data model. Defined items in template range are the properties of type of class TemplateRange.
When you design your template, the first thing is that you should determine how to output data with template. You can determine how many template ranges there are and how to process template ranges according to your data structure. If your data is a list, define a template range as a list service range will simplify your processing. Please see the examples to understand Template Range and Range Services.
Template file is a Excel workbook(*.xlsx). In this version only *.xlsx format is supported. As the known, one workbook might have several worksheets. ExcelTDF splits a worksheet as some ranges which include some cells to be processed or not. For the cells to be processed, the cell text might include one or more defined variables. A range might have some child ranges, and child range is able to has its own child ranges too. These range’s definition information must be specified by template range configuration file or defined by Metadata in data model. So the whole worksheet is considered as a root range, all other ranges are worksheet’s child or descendant ranges.
See the following diagram. The worksheet root range has three child ranges named R-A, R-E, R-D, and range R-A also has two child range R-B, R-C. From diagram we can see a tree of range.
The template range configuration file, simply, we can consider it as TemplateRange class. The all items’ name are TemplateRange’s properties. The service name used to process range is listed in service names, You can get all names by call OpenXmlContext’s GetRangeServiceIds() methods too.
The following is template whose layout is the same as description above.
The following is corresponding template range configuration file.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net"> <object id="SAMPLE_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="RootRange" /> <property name="LeftTop" value="A1" /> <property name="RightBottom" value="Z51" /> <property name="ServiceId" value="RowRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="R-A-ID" /> <ref object="R-E-ID" /> <ref object="R-D-ID" /> </list> </property> </object> <object id="R-A-ID" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="R-A" /> <property name="LeftTop" value="B3" /> <property name="RightBottom" value="S31" /> <property name="ServiceId" value="BlockRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="R-B-ID" /> <ref object="R-C-ID" /> </list> </property> </object> <object id="R-E-ID" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="R-E" /> <property name="LeftTop" value="B37" /> <property name="RightBottom" value="S49" /> <property name="ServiceId" value="BlockRangeService"/> </object> <object id="R-D-ID" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="R-D" /> <property name="LeftTop" value="U2" /> <property name="RightBottom" value="Y49" /> <property name="ServiceId" value="BlockRangeService"/> </object> <object id="R-B-ID" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="R-B" /> <property name="LeftTop" value="C9" /> <property name="RightBottom" value="Q16" /> <property name="ServiceId" value="BlockRangeService"/> </object> <object id="R-C-ID" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="R-C" /> <property name="LeftTop" value="B20" /> <property name="RightBottom" value="Q29" /> <property name="ServiceId" value="BlockRangeService"/> </object> </objects>
The following is model class used to process template above.
[ExcelRange("SHEET1")]
public class RT
{
[ExcelRange("R-A")]
public RA ra;
[ExcelRange("R-D")]
public RD rd;
[ExcelRange("R-E")]
public RE re;
}
public class RA{
[ExcelRange("R-B")]
public RB rb;
[ExcelRange("R-C")]
public RC rc
}
public class RB {}
public class RC {}
public class RD {}
public class RE {}
If you want to define range’s all information into Metadata to replace template range configuration file, the following is sample of data model.
[ExcelRange("SHEET1", "A1", "Z51", "RowRangeService")]
public class RT
{
[ExcelRange("R-A", "B3", "S31", "BlockRangeService")]
public RA ra;
[ExcelRange("R-D", "U2", "Y49", "BlockRangeService")]
public RD rd;
[ExcelRange("R-E", "B37", "Y49", "BlockRangeService")]
public RE re;
}
public class RA{
[ExcelRange("R-B", "C9", "Q16", BlockRangeService")]
public RB rb;
[ExcelRange("R-C", "B20", "Q29", "BlockRangeService")]
public RC rc
}
public class RB {}
public class RC {}
public class RD {}
public class RE {}
3.5.2.1. Template range example 1
Next let us see how to determine your template range by some examples.
First, see the following most simple example. We just want to replace the variables between row 8 and row 10, so the worksheet has only one root range from row 8 to row 10.
In order to tell ExcelTDF the range’s information , we write these information into a Spring.NET object xml file as following.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="RowRangeServiceTest_sheet1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT"/> <property name="Top" value="8"/> <property name="Bottom" value="10"/> <property name="ServiceId" value="RowRangeService"/> </object> </objects>
On the above, in configuration file, the type of object is always JanaBiz.OpenXml.TemplateRange. TemplateRange has some properties must be specified. These properties are RangeName, ServiceId and range defined by (Top|Left|LeftTop) and (Bottom|Right|RightBottom). If we want to specify range by row index without column limitation, the Top and Bottom property should be set. If we want to specify range by column index without limitation of row index, the Left and Right property should be set. If we want to specify rectangle range with the limitation of row and column, the property LeftTop and RightBottom should be set.
RangeName: the identifier of the range
Top/Bottom | Left/Right | LeftTop/RightBottom : the range specified by row or column or cell name
ServiceId: the service name used to process this range. see service names
The object id can be any string but it must be unique in all configuration files, For the worksheet root range, it is best to assign object id as template id + “sheet” + worksheet index . This will be convenient to ExcelTDF to find worksheet root range object. About some other property please see TemplateRange class API.
For the template above, following is the processed result. Because the service is RowRangeService, it just replaces the variables in specified template range with the data in data model.
3.5.2.2. Template range example 2
Following is another template file. In this template file we want to output data as a list. The list’s element is from row 8 to row 10, let us see how different result will be got.
The template file is shown as following.
The template configuration file is shown as following.
<?xml version="1.0" encoding="shift_jis"?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="RowListRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT"/> <property name="Top" value="1"/> <property name="Bottom" value="30"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="RLRSChildList1"/> </list> </property> </object> <object id="RLRSChildList1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="CHILDLIST1"/> <property name="LeftTop" value="L8"/> <property name="RightBottom" value="BN10"/> <property name="ServiceId" value="RowListRangeService"/> </object> </objects>
There are two TemplateRanges in worksheet. The worksheet root range, whose RangeName is ROOT and it has a child range. Root range is from row 8 to row 22. Child range’s id is RLRSChildList1 and RangeName is CHILDLIST1. Child range is from L8 to BE10. Child range is processed by service of RowListRangeService which output data as list with the same format as defined range L8:BE10, Assume your list in the data model has 5 elements, the data of 5 elements will be outputted from L8 to L20.
The following is processed result.
If we change the ROOT range’s row from row 7 to row 20, what will be changed? The variable of ${HouseNumber1} is in the range of ROOT, this variable will be processed. ROOT range’s ServiceId is RowRangeService, the variable is just replaced by value in data model corresponding to the ROOT range.
3.5.2.3. Template range example 3
Next, let us see a some complicated examples. In this example we want to output a list, and the element of list is also a list. We usually bump into this case. As the shown, template design is very simple too. The outer list’s element is from B5 to BJ11, the inner list’s element is from C8 to BE10. So the variable of $N{HouseNumber1}, #C{S1=SUM(HBⅡBody)}, #C{=SUM(S10)} etc. are processed by outer list. Inner list size might be different, the service of specified will process them correctly.
As the diagram of following, the worksheet has three ranges. Root range of worksheet is from row 1 to 18, which is processed by RowRangeService. The root range has a child range whose id is CRCRS_PARENTLIST1, named PARENTLIST1 and colored with gray. Child range is from B5 to BJ11, which is processed by service of ComplexRowCloneRangeService. The service named with prefix of ComplexXX is used to process list ranges who’s element has child range. So the part of B5:BJ11 will be cloned list size-1 times. The cloned rows are inserted in the below of the last row BJ11, here it is row 12. Because the element of list includes a child range, whose id is CRCRS_CHILDLIST1 , this range is also a cloned list, the cloned rows is from C8 to BE10, The row of parent range will be changed dynamically. ExcelTDF control ranges processing order, first parent range, then child ranges. If child range is a coned list, parent range’s row or column will be changed automatically.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="ComplexRowCloneRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT"/> <property name="Top" value="1"/> <property name="Bottom" value="18"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CRCRS_PARENTLIST1"/> </list> </property> </object> <object id="CRCRS_PARENTLIST1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARENTLIST1"/> <property name="LeftTop" value="B5"/> <property name="RightBottom" value="BJ11"/> <property name="ServiceId" value="ComplexRowCloneRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CRCRS_CHILDLIST1"/> </list> </property> </object> <object id="CRCRS_CHILDLIST1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="CHILDLIST1"/> <property name="LeftTop" value="C8"/> <property name="RightBottom" value="BE10"/> <property name="ServiceId" value="RowCloneRangeService"/> </object> </objects>
The result is shown in the following. You can see, the simple template creates complicated data output. There are many patterns you can assemble by provided services in ExcelTDF.
3.5.3. Without template range file
If your template doesn’t need to be changed frequently, maybe you don’t want to create template range configuration file. In this case the template range information can be written in code as metadata of program. If you write range information into code, and your template layout is changed, you must modify your code. The most benefit of using template range configuration file is you don’t have to change your code if template layout changed. For the detail, please see next chapter about data provider.
3.6. Data Provider and Data Model
ExcelTDF uses two types of interface to control the mapping information. IDataProviderManager controls mapping behavour of IRangeDataProvider and template range, while IRangeDataProvider controls mapping behavour of data field in data model and template variable. Data field maybe is field or property in data model, or columns of SQL query, or some others.
3.6.1 The types of data provider manager
Each implementation class of IDataProviderManager knows how to map data provider to template range. Actually manager maps RangeData in IRangeDataProvider to template range. RangeData can be data model, DataSet or other data objects. ExcelTDF provides three manager classes.
3.6.1.1. DataModelProviderManager
DataModelProviderManager, in which the default DataModelRangeDataProvider is used, maps data model to template range. ExcelRange and ExcelAttibute(Excel) Attribute classes are used to help ExcelTDF doing those works. The RangeData in DataModelRangeDataProvider is your data model for DataModelProviderManager. In the next you will see how to use them in data model.
3.6.1.2. QueryProviderManager
QueryProviderManager let you create document or report by specified SQL query, all template ranges use the same one query data if you use QueryProviderManager. If you are familiar with CrystalReport, creating a query and getting report, this is the similarity functionality.
3.6.1.3. AnyTypeProviderManager
AnyTypeProviderManager let you create document or report by any data type. You should create IRangeDataProvider for each template range. If no IRangeDataProvider found, the IRangeDataProvider of parent range will be used. You can specify a data model for a range, SQL query for another range. You can specify SQL query for each range and so on. It is the most flexible manager.
See the following diagram, it shows the association between template and data.
As you see, ExcelTDF provides various IRangeDataProvider implementation classes, from data mode to usually used database, even SAP. IRangeDataProvider has two methods ReadRangeData() for getting data from database or background service, and WriteRangeData() for saving edited data to database or sending to background service. ExcelTDF maps RangeData in IRangeDataProvider to template range. ExcelTDF provides three IDataProviderManager implementation classes, they are DataModelProviderManager, QueryProviderManager and AnyTypeProviderManager. The three data provider managers have the different control behavior and are used in different way. The DataModelProviderManager is the most often used data provider manager. If the RangeData in IRangeDataProvider is data model, not a SQL DataSet and not SAP’s DataReader, DataModelProviderManager is your suitable selection.
3.6.2 Data model of Template range example 1
In the following, let us see how to use DataModelProviderManager and what your data model should be.
For the Template range example-1 which we discussed on the above, data model is shown in the diagram. Please see the fields that have defined Excel metadata, for example [Excel("HBⅡBody")], it will map the value of HBⅡBody to the template variable defined as ${HBⅡBody}. Every template variable must have corresponding metadata with the same name in data model. If [Excel("X")] is defined in data model, but no ${X} or #{X=..} or %{X=..} found, “X” can be used in formula expression and custom service expression. For example, #{VAR2=SUM(VAR1*X/100)}, X is not a variable in template but a variable in data model. In the same way, the metadata [ExcelRange("ROOT")] tells ExcelTDF that the range information named ROOT in the template range configuration is used for this model, and variables defined in this model by metadata are mapped to template variables in the range named ROOT.
using System; using System.Xml.Serialization; using JanaBiz.OpenXml; namespace OpenXmlDemoData.Data { [ExcelRange("ROOT")] [Serializable] public class RowRangeServiceData1 { public string HouseType; [Excel("HouseNumber1")] public string HouseNum = "20"; [Excel("HBⅡBody")] public double HBⅡBody; [Excel("VAR2")] public string Var2; [Excel("LBⅡBody")] public string LBⅡBody; [Excel("KCBody")] public string KCBody; [Excel("SHⅡBody")] public string SHⅢBody; ..... } }
Here is the code to parse template and get your document. As you see, after you finished your template design and wrote well your template configuration, the coding is very simple.
using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Text; using JanaBiz.OpenXml.Handler; using JanaBiz.OpenXml.Config; using JanaBiz.OpenXml.Data; using OpenXmlDemoData; namespace TemplateConvertDemo { class Program { static void Main(string[] args) { //create your handler IOpenXmlHandler handler = new OpenXmlHandler(); //tell ExcelTDF which ITemplateService is used to //get template file and template range configuration handler.GetTemplateService<LocalTemplateService>(); //tell ExcelTDF which IDataProviderManager and //what data model is used to data handler.DataProviderManager = new DataModelProviderManager( new OpenXmlDemoData.Data.RowRangeServiceData1()); //set template id to identify which template is used handler.TemplateId = "RowRangeServiceTest"; //parse your template handler.ParseTemplate(); //parse your data handler.ParseWorkbook(null); //get parsed document byte[] doc = handler.GetParsedDocument(); //release some resource handler.Close(); } } }
In the code, creating DataModelProviderManager with parameter of RowRangeServiceData1 will enable manager use default data provider of DataModelRangeDataProvider. In fact, DataModelProviderManager have two constructors with parameter of either data model or IRangeDataProvider when you want use your customized IRangeDataProvider.
public DataModelProviderManager(Object dataModel) public DataModelProviderManager(IRangeDataProvider rootRangeDataProvider)
If you want to open parsed workbook into excel in your excel AddIn program directly, ExcelHandler is prepared for you.
The code is shown as the following. The step of coding is the same as the above example. Here we create and initialize handler first, and get template file and template range configuration by web service of TemplateServiceSoapClient. So your web service should perform the methods of interface ITemplateService, but your service don’t have to implement the interface of ITemplateService. This will be helpful for you to reference web service directly.
private ExcelHandler handler = null; private TemplateServiceSoapClient webService = null; public RibbonDemo() { InitializeComponent(); } private void RibbonDemo_Load(object sender, RibbonUIEventArgs e) { InitializeExcelHandler(); } /// <summary> /// Create and initialize <see cref="IExcelHandler"/> /// </summary> private void InitializeExcelHandler() { //create IExcelHandler instance handler = new ExcelHandler(); // set excel application handler.ExcelApp = Globals.ThisAddIn.Application; //create or get template service webService = handler.GetTemplateService<TemplateServiceSoapClient>(); //create data service DataService dataService = new DataService(); //get excel data event handler handler.OnGetDocumentData += dataService.GetDocumentData; //save excel data event handler handler.OnSaveDocumentData += dataService.SaveDocumentData; }
On the above, we create DataService object which performs OnGetDocumentData event handler for getting data model. Certainly you can assign DataModelProviderManager to handler directly too, such as
handler.DataProviderManager = new DataModelProviderManager( new OpenXmlDemoData.Data.RowRangeServiceData1());
Define a event handler is very flexible. First, coding logic to get data for all templates can be put in event handler method. Second, you can override IRangeDataProvider, and get data in your way. The event handler method is show in the below.
/// <summary> /// Assign a data provider manager or data range provider to a template for specified template id /// </summary> /// <param name="handler"></param> /// <param name="sheetIndex"></param> public void GetDocumentData(IOpenXmlHandler handler, int sheetIndex) { switch (handler.TemplateId) { case "RowRangeServiceTest": switch (sheetIndex) { case 0: // you can get from web service etc., for simple, here just new a data model handler.DataProviderManager = new DataModelProviderManager( new RowRangeServiceData1()); break; } break; } }
Let us see the code about opening parsed document, only several lines. Open() method try to find data provider manager and data provider by event handler of OnGetDocumentData. If event handler exists, it will be called.
/// <summary> /// Open parsed workbook of spreadsheet /// </summary> /// <param name="templateId"></param> private void OpenDocument(string templateId) { //close opened workbook before handler.Close(); //template id used handler.TemplateId = templateId; //other input parameter for get excel data handler.InputData = null; //parse template handler.ParseTemplate(); //parse workbook and open it in excel or other component handler.Open(); }
3.6.3 Data model of Template range example 2
In this example because the root template range has a child range, the data model is also created into two classes hierarchically. RowListRangeServiceData class has metadata [ExcelRange("ROOT")] and its field member ChildList1 has metadata [ExcelRange("CHILDLIST1")]. The names of ROOT and CHILDLIST1 matches the RangeName defined in template range configuration. In template range configuration, the ServiceId of CHILDLIST1 is RowListRangeService, so in the data model of RowListRangeServiceData, the member with metadata [ExcelRange("CHILDLIST1")], ChildList1 must be a list.
ExcelRange binding a data model is associated to template range, and if your template range ServiceId is list related service, data member should be a list type of IList. That is all.
using System; using System.Collections.Generic; using JanaBiz.OpenXml; using JanaBiz.Common.Serialize; namespace OpenXmlDemoData.Data { [Serializable] [ExcelRange("ROOT")] public class RowListRangeServiceData { public RowListRangeServiceData() { //Create data for demo for (int i = 0; i < 5; i++) { ChildList1.Add(new RowRangeServiceData1()); } } [ExcelRange("CHILDLIST1")] public IList<RowRangeServiceData1> ChildList1 = new List<RowRangeServiceData1>(); } }
In OnGetDocumentData event handler we added a piece of coding for getting data model. other works are the same as example-1
/// <summary> /// Assign a data provider manager or data range provider to a template for specified template id /// </summary> /// <param name="handler"></param> /// <param name="sheetIndex"></param> public void GetDocumentData(IOpenXmlHandler handler, int sheetIndex) { switch (handler.TemplateId) { case "RowRangeServiceTest": switch (sheetIndex) { case 0: // you can get from web service etc., for simple, here just new a data model handler.DataProviderManager = new DataModelProviderManager( new RowRangeServiceData1()); break; } break; case "RowListRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new RowListRangeServiceData()); break; } break; } }
3.6.4 Data model of Template range example 3
In this example, the template range has a list, and list’s element is also a list. Let us see the data model how to construct.
As you have seen, entry class ComplexRowCloneRangeServiceData has metadata ExcelRange(“ROOT”), its member MasterDoorList1 has metadata [ExcelRange("PARENTLIST1")], MasterDoorList1 is a list. MasterDoorList1′s element is RoomTypeList1 which has a member MasterDoorChildList, and MasterDoorChildList has metadata [ExcelRange("CHILDLIST1")], it is also a list. In the OnGetDocumentData event handler we added code to get data provider manager. For the others no need to change.
using System; using System.Collections.Generic; using JanaBiz.OpenXml; namespace OpenXmlDemoData.Data { [ExcelRange("ROOT")] public class ComplexRowCloneRangeServiceData { public ComplexRowCloneRangeServiceData() { CreateDataForDemo(); } [ExcelRange("PARENTLIST1")] public IList<RoomTypeList1> MasterDoorList1 = new List<RoomTypeList1>(); } }
using System; using System.Collections.Generic; using System.Linq; using JanaBiz.OpenXml; namespace OpenXmlDemoData.Data { /// <summary> /// 戸当りマスタ登録・変更画面の概要説明です。 /// </summary> public class RoomTypeList1 { public RoomTypeList1() { CreateDataForDemo(); } public RoomTypeList1(string houseNumber) { CreateDataForDemo2(houseNumber); } [Excel("部屋数1")] public string HouseNumber="12"; [ExcelRange("CHILDLIST1")] public IList<RowRangeServiceData1> MasterDoorChildList = new List<RowRangeServiceData1>(); } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using System.IO; using System.Reflection; using JanaBiz.OpenXml; using JanaBiz.OpenXml.Handler; using JanaBiz.OpenXml.Data; using JanaBiz.OpenXml.Config; using OpenXmlDemoData.DataProvider; using OpenXmlDemoData.Data; namespace OpenXmlDemoData { public class DataService { /// <summary> /// Assign a data provider manager or data range provider to a template for specified template id /// </summary> /// <param name="handler"></param> /// <param name="sheetIndex"></param> public void GetDocumentData(IOpenXmlHandler handler, int sheetIndex) { switch (handler.TemplateId) { case "RowRangeServiceTest": switch (sheetIndex) { case 0: // you can get from web service etc., for simple, here just new a data model handler.DataProviderManager = new DataModelProviderManager( new RowRangeServiceData1()); break; } break; case "RowListRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new RowListRangeServiceData()); break; } break; case "ComplexRowCloneRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new ComplexRowCloneRangeServiceData()); break; } break; } }
3.6.5. How to create your class of IRangeDataProvider
If you need to search data from database, web service or other services in background, you might want to create your own implementation class of IRangeDataProvider. If your searched result is designed as data model demonstrated in the example above, the most convenient way is to create a sub class of DataModelRangeDataProvider. DataModelRangeDataProvider performs interface methods of IRangeDataProvider, but ReadRangeData() and WriteRangeData() do nothing. You can override these two methods in your way. When you open or parse document, firstly, IDataProviderManager calls ReadRangeData() to get data, then IDataProviderManager parses gotten data. When you save edited document by calling ExcelHandler or OpenXmlHandler’s Save() method, firstly, the Save() method will call Flush() method to save edited data into mapped data model of RangeData then call WriteRangeData() to save RangeData to database or other services.
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.IO; using System.Reflection; using JanaBiz.Common.Serialize; using JanaBiz.OpenXml.Data; using OpenXmlDemoData.Data.Boat; namespace OpenXmlDemoData.DataProvider { /// <summary> /// /// This is demo for getting data from database or background service. /// Here we get data from xml resource file. /// </summary> public class ColumnDataProvider : DataModelRangeDataProvider { public ColumnDataProvider() : base() { } /// <summary> /// Here you can call database service, web service of other background service to /// read data, the last step is assign the result data to RangeData. /// </summary> public override void ReadRangeData() { //here, read from xml file Assembly assembly = Assembly.GetExecutingAssembly(); string assemblyNamespace = assembly.FullName.Split(new char[]{','})[0]; string xmlfile = assemblyNamespace + ".Resources.ColumnRangeTestData.xml"; Stream stream = assembly.GetManifestResourceStream(xmlfile); BoatRace boatRace = Serializer.ReadModel<BoatRace>(stream); //this must be done this.RangeData = boatRace; } /// <summary> /// You can write your saving data in your way. /// Here we just write the data into a xml file /// </summary> /// <returns>return true, if saved data successfully, otherwise false</returns> public override bool WriteRangeData() { Serializer.WriteToXml(this.RangeData, "c:\\ColumnListRangeTestData.xml"); return true; } } }
In the example on the above, for simply demo, ColumnDataProvider just get data from a serialized xml resource file and write edited data into xml resource file. Next code is show how to assign the data provider to IDataProviderManager.
/// <summary> /// Assign a data provider manager or data range provider to a template for specified template id /// </summary> /// <param name="handler"></param> /// <param name="sheetIndex"></param> public void GetDocumentData(IOpenXmlHandler handler, int sheetIndex) { switch (handler.TemplateId) { case "RowRangeServiceTest": switch (sheetIndex) { case 0: // you can get from web service etc., for simple, here just new a data model handler.DataProviderManager = new DataModelProviderManager( new RowRangeServiceData1()); break; } break; case "ColumnListRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new ColumnDataProvider()); break; } break; case "ColumnCloneRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new ColumnDataProvider()); break; case 1: handler.DataProviderManager = new DataModelProviderManager( new ColumnDataProvider()); break; } break; case "ComplexColumnCloneRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new ComplexColumnDataProvider()); break; } break; case "ComplexColumnListRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new ComplexColumnDataProvider()); break; } break; } }
3.6.6. Use Metadata without template range configuration
If you don’t want to create template configuration file, you can write necessary information into ExcelRange metadata as the following.
using System; using System.Collections.Generic; using System.Linq; using System.Text; using JanaBiz.OpenXml; using JanaBiz.Common.Serialize; namespace OpenXmlDemoData.Data.Boat { [Serializable] [ExcelRange("ROOT", "B5", "EQ10", "RowRangeService")] public class BoatRace { public BoatRace() { } [ExcelRange("HEADER", "CX4", "DF48", "BlockRangeService")] public BoatRaceHeader HeaderData = new BoatRaceHeader(); [ExcelRange("LIST", "BZ4", "CK48", "ColumnListRangeServiceRL")] public SerializableList<BoatRaceRecord> itemDataList = new SerializableList<BoatRaceRecord>(); [ExcelRange("RESULT", "H4", "Q48", "BlockRangeService")] public BoatRaceResult ResultData = new BoatRaceResult(); } }
Then set parameter of method ParseTemplate(params bool[] usingConfig) to false, handler.ParseTemplate(false);
using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Text; using JanaBiz.OpenXml.Handler; using JanaBiz.OpenXml.Config; using JanaBiz.OpenXml.Data; using OpenXmlDemoData; namespace TemplateConvertDemo { class Program { static void Main(string[] args) { //create your handler IOpenXmlHandler handler = new OpenXmlHandler(); //tell ExcelTDF which ITemplateService is used to //get template file and template range configuration handler.GetTemplateService<LocalTemplateService>(); //tell ExcelTDF which IDataProviderManager and //what data model is used to data handler.DataProviderManager = new DataModelProviderManager( new OpenXmlDemoData.Data.RowRangeServiceData1()); //set template id to identify which template is used handler.TemplateId = "RowRangeServiceTest"; //parse your template handler.ParseTemplate(false); //parse your data handler.ParseWorkbook(null); //get parsed document byte[] doc = handler.GetParsedDocument(); //release some resource handler.Close(); } } }
4. Service
ExcelTDF provides many range service to process template range as what you want to. Range service are the implementation class of IExcelRangeService and set in template range configuration file. Simultaneously ExcelTDF provides some custom cell services and function services. As explained on the chapter of Custom service variable, cell services and function services has the writing format as the same as Excel formula. Custom cell services are used to perform special processing like barcode, page break and so on, there are no result returned. Function service does some special processing and returns function result as string. Simply, range service is used to process template range, custom service is used to process template cells.
4.1. IExcelRangeService
Default range service is configured in Spring.NET objects file included in ExcelTDF package as resource file. Service name which is called ServiceId in template range is object id that is upper case and lower case sensitive.
<object id="RowRangeService" type="JanaBiz.OpenXml.Service.RowRangeService, JanaBiz.OpenXml" singleton="false"/>
OpenXmlContext class provides some static methods to get ids of different services. You can get all range service ids by
OpenXmlContext.GetRangeServiceIds();
ExcelTDF full versions provides following range services.
. RowRangeService
. RowListRangeService
. RowCloneRangeService
. ComplexRowListRangeService
. ComplexRowCloneRangeService
. BlockRangeService
. BlockRowListRangeService
. ComplexBlockRowListRangeService
. ColumnListRangeService
. ColumnListRangeServiceRL
. ColumnCloneRangeService
. ColumnCloneRangeServiceRL
. ComplexColumnListRangeService
. ComplexColumnListRangeServiceRL
. ComplexColumnCloneRangeService
. ComplexColumnCloneRangeServiceRL
. MultiplePageRangeService
. BlockRowCloneRangeService
. BlockColumnCloneRangeService
4.1.1. RowRangeService
RowRangeService is the simplest service whose range is specified by top row index and bottom row index. RowRangeService replaces the variables in template range with data value in RangeData of IRangeDataProvider. If you specify range by cell name of LeftTop and RightBottom, the variables outside the specified column will not be processed.
The template range specified with this service is able to have child ranges. Generally, the worksheet’s root range is usually specified by this service.
4.1.2. RowListRangeService
RowListRangeService is used to show a data list whose first element is specified by top row index and bottom row index. RowListRangeService requires you design well-formatted rows for all element in list. The template variable between top row and bottom row will be repeatedly processed until the last element of list. If you specify range by cell name of LeftTop and RightBottom, the variables outside the specified column will not be processed. The range specified service as RowListRangeService can not have child ranges (ExcelTDF not process any child ranges even child range is specified in template range configuration file). see ComplexRowListRangeService
In template, one element of list maybe have several excel rows as a logical unit. For example, if you specified template range from top row 8 to bottom row 10, your logical unit is (10-8+1)=3 rows. Assume your data list size is 5, all data will be outputted from row 8 to row 8+3*5-1=22. If you specify the StartIndex and PageSize in template range configuration, the elements from StartIndex to StartIndex+PageSize will be outputted. If StartIndex + PageSize is greater than the size of list, service outputs to the last element of list. The format of all output logical rows must be the same as the first element rows except that the template variable is only written in the first element rows. The best way is that you copy all cells in the first element several times until your list’s max size. Make sure cells exist in worksheet. At sometime the cell without text (blank cell ) might not be created in worksheet by Open XML even the cells is shown. See the following diagram of RowListRangeService example. In template range configuration first element is defined from row index is from 8 to 10. list has max size 5.
4.1.3. RowCloneRangeService
RowCloneRangeService is one of usually used service. RowCloneRangeService is used to show data list too. The different from RowListRangeService is RowCloneRangeService don’t have to set format for each elements except of first element rows. RowCloneRangeService clones cells in first element rows and add them in the below. This is very useful, you don’t worry about the list size and are able to design template simply. After cloned row are added into document, the row index of other range and the cell name of formula will be changed, ExcelTDF will recalculate row index about them again. The RowCloneRangeService can not have child ranges as the same as RowListRangeService.
The following is an example. At the bottom of template, there are a Excel formula of =SUM(E21:L25) and a pull down cell. After template is processed, 99 logical unit rows are cloned. The logical row size is 3, so the number of changed rows is 99*3=297. Let us see the formula in the result diagram, SUM(E21:L25) is changed to SUM(E318:L324) and the pull down cell also is moved from row 21 to row 318 too. If RowCloneRangeService is used, the row index of objects on the below of RowCloneRangeService range will be changed automatically, you don’t have to do any work to correct row index, ExcelTDF does all for you.
The result processed by RowCloneRangeService is shown in the following.
4.1.4. ComplexRowListRangeService
ComplexRowListRangeService is a list service like RowListRangeService. The difference between ComplexRowListRangeService and RowListRangeService is that the element of ComplexRowListRangeService’s list data is able to include child ranges and child range is able to be any range type. RowListRangeService’s element is not able to include child ranges. Because ComplexRowListRangeService is also a list service, you must design the format for all elements in list as the same way as RowListRangeService. See the following template file, template range configuration and data model. The first element of ComplexRowListRangeService is from C4 to BJ22 and named as PARENTLIST1, other elements are well-designed as the same as the first element. In the element there is a RowListRangeService which is from C8 to BE10 and named as CHILDLIST1. CHILDLIST1′s maximum data size is 5 in first element of PARENTLIST1.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="ComplexRowListRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT"/> <property name="LeftTop" value="A1"/> <property name="RightBottom" value="CK275"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CRLRS_PARENTLIST1"/> </list> </property> </object> <object id="CRLRS_PARENTLIST1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARENTLIST1"/> <property name="LeftTop" value="C4"/> <property name="RightBottom" value="BJ22"/> <property name="ServiceId" value="ComplexRowListRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CRLRS_CHILDLIST1"/> </list> </property> </object> <object id="CRLRS_CHILDLIST1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="CHILDLIST1"/> <property name="LeftTop" value="C8"/> <property name="RightBottom" value="BE10"/> <property name="ServiceId" value="RowListRangeService"/> </object> </objects>
using System; using JanaBiz.OpenXml; using JanaBiz.Common.Serialize; namespace OpenXmlDemoData.Data { [ExcelRange("ROOT")] public class ComplexRowListRangeServiceData { [ExcelRange("PARENTLIST1")] public SerializableList<RoomTypeList1> MasterDoorList1 = new SerializableList<RoomTypeList1>(); } }
using System; using System.Collections.Generic; using System.Linq; using JanaBiz.OpenXml; namespace OpenXmlDemoData.Data { /// <summary> /// /// </summary> public class RoomTypeList1 { /// <summary> /// variable /// </summary> [Excel("部屋数1")] public string HouseNumber="12"; /// <summary> /// Child list range /// </summary> [ExcelRange("CHILDLIST1")] public IList<RowRangeServiceData1> MasterDoorChildList = new List<RowRangeServiceData1>(); } }
4.1.5. ComplexRowCloneRangeService
ComplexRowCloneRangeService is a list clone service like RowCloneRangeService. The difference between ComplexRowCloneRangeService and RowCloneRangeService is that the element of ComplexRowCloneRangeService’s list data is able to include child ranges and child range is able to be any range type. RowCloneRangeService’s element is not able to include child ranges. See the following template file, template range configuration and data model. Using ComplexRowCloneRangeService, your template design becomes very simple. As the same as service of RowCloneRangeService, you don’t have to copy the format for each element rows in the list. Following example is explained on the above of Template range example 3.
Let’s see more detail. ComplexRowCloneRangeService processes the range named PARENTLIST1 from B5 to BJ11, which has a RowCloneRangeService range from C8 to BE10 with color of green named CHILDLIST1.
The cell AA11′s template variable expression is #C{S4=SUM(SHⅡ本体)}, it is in the range of PARENTLIST1. The variable name is S4 and referenced variable is SHⅡ本体 which is in the child range of CHILDLIST1. In the first element of PARENTLIST1, the child list has 5 elements, so the variable cells of SHⅡ本体 is AA8,AA11,AA14,AA17,AA20 and the result of formula is =SUM(AA8,AA11,AA14,AA17,AA20). Please see the other variables on the template S1, S1, S2..S10,S11, their behavior are the same as S4.
The cell C11 in the range of PARENTLIST1 is normal formula, you can see it is become =SUM(L23:BE23), because row index is changed. It is the sum of S1..S11.
See the cells of AZ15, BE15, they are the variables expression #C{=SUM(S10)} and #C{=SUM(NWS本体)}. These two variables are in the range of ROOT. See the result on the diagram and what formula they have been changed to.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="ComplexRowCloneRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT"/> <property name="Top" value="1"/> <property name="Bottom" value="18"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CRCRS_PARENTLIST1"/> </list> </property> </object> <object id="CRCRS_PARENTLIST1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARENTLIST1"/> <property name="LeftTop" value="B5"/> <property name="RightBottom" value="BJ11"/> <property name="ServiceId" value="ComplexRowCloneRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CRCRS_CHILDLIST1"/> </list> </property> </object> <object id="CRCRS_CHILDLIST1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="CHILDLIST1"/> <property name="LeftTop" value="C8"/> <property name="RightBottom" value="BE10"/> <property name="ServiceId" value="RowCloneRangeService"/> </object> </objects>
using System; using System.Collections.Generic; using JanaBiz.OpenXml; namespace OpenXmlDemoData.Data { [ExcelRange("ROOT")] public class ComplexRowCloneRangeServiceData { public ComplexRowCloneRangeServiceData() { CreateDataForDemo(); } [ExcelRange("PARENTLIST1")] public IList<RoomTypeList1> MasterDoorList1 = new List<RoomTypeList1>(); } }
using System; using System.Collections.Generic; using System.Linq; using JanaBiz.OpenXml; namespace OpenXmlDemoData.Data { /// <summary> /// /// </summary> public class RoomTypeList1 { /// <summary> /// variable /// </summary> [Excel("部屋数1")] public string HouseNumber="12"; /// <summary> /// Child list range /// </summary> [ExcelRange("CHILDLIST1")] public IList<RowRangeServiceData1> MasterDoorChildList = new List<RowRangeServiceData1>(); } }
4.1.6. BlockRangeService
BlockRangeService is as the same as RowRangeService except that the range is specified by cell name. It is a service to process a rectangle region. If you only want to process some special region by service, BlockRangeService is very useful. You can split your complicated template into some simple region to process one by one. The range of RowRangeService is able to be specified by cell name too. In this case RowRangeService has the same action as BlockRangeService. About more detail see RowRangeService.
4.1.7. BlockRowListRangeService
BlockRowListRangeService is the same as RowListRangeService except that the range is specified by cell name. It is also a service to process a rectangle region, but in the region, data is output as list by rows. If the range of RowListRangeService is specified by cell name, RowListRangeService has the same action as BlockRowListRangeService. About more detail, see RowListRangeService.
4.1.8. ComplexBlockRowListRangeService
CpmplexBlockRowListRangeService is a list service whose range is defined by cell name and is able to have child ranges in the list elements. The behavior is like ComplexRowListRangeService. About more detail, see ComplexRowListRangeService.
4.1.9-10. ColumnListRangeService and ColumnListRangeServiceRL
ColumnListRangeService is similar to the RowListRangeService but the range is specified by column index. ColumnListRangeService outputs data from left to right as list. The first element is outputted on the left side of the range. In the opposite, the ColumnListRangeServiceRL outputs data from right to the left. The first element is outputted on the right side of the range. As the same as service of RowListRangeService, you must make sure all element’s columns have the same format. See the diagram following, the gray region is processed by ColumnListRangeServiceRL.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net"> <object id="ColumnListRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT" /> <property name="LeftTop" value="B2" /> <property name="RightBottom" value="EQ50" /> <property name="ServiceId" value="RowRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ColumnListRangeServiceTest_1_HEADER" /> <ref object="ColumnListRangeServiceTest_1_LIST" /> <ref object="ColumnListRangeServiceTest_1_RESULT" /> </list> </property> </object> <object id="ColumnListRangeServiceTest_1_HEADER" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="HEADER" /> <property name="LeftTop" value="CX4" /> <property name="RightBottom" value="DF48" /> <property name="ServiceId" value="BlockRangeService" /> </object> <object id="ColumnListRangeServiceTest_1_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="LIST" /> <property name="LeftTop" value="BZ4" /> <property name="RightBottom" value="CK48" /> <property name="ServiceId" value="ColumnListRangeServiceRL" /> </object> <object id="ColumnListRangeServiceTest_1_RESULT" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="RESULT" /> <property name="LeftTop" value="H4" /> <property name="RightBottom" value="Q48" /> <property name="ServiceId" value="BlockRangeService" /> </object> </objects>
using System; using System.Collections.Generic; using System.Linq; using System.Text; using JanaBiz.OpenXml; using JanaBiz.Common.Serialize; namespace OpenXmlDemoData.Data.Boat { [Serializable] [ExcelRange("ROOT")] public class BoatRace { public BoatRace() { } [ExcelRange("HEADER")] public BoatRaceHeader HeaderData = new BoatRaceHeader(); [ExcelRange("LIST")] public SerializableList<BoatRaceRecord> itemDataList = new SerializableList<BoatRaceRecord>(); [ExcelRange("RESULT")] public BoatRaceResult ResultData = new BoatRaceResult(); } }
4.1.11-12. ColumnCloneRangeService and ColumnCloneRangeServiceRL
ColumnCloneRangeService is similar to the RowCloneRangeService but the range is specified by column index. ColumnCloneRangeService clones specified columns and add cloned columns one by one from left to right. In the opposite, the ColumnCloneRangeServiceRL clones specified columns and add cloned columns one by one from right to left like the Japanese newspaper. Using ColumnCloneRangeService you don’t have to copy all elements format as ColumnListRangeService. After processed, column index of some ranges, formulas, objects will be recalculated and moved automatically.
The result is shown in the following.
4.1.13-14. ComplexColumnListRangeService and ComplexColumnListRangeServiceRL
ComplexColumnListRangeService is a list service like ColumnListRangeService. The difference between ComplexColumnListRangeService and ColumnListRangeService is that the element of ComplexColumnListRangeService’s list data is able to include child ranges and child range is able to be any range type. ColumnListRangeService’s element is not able to include child ranges. Because ComplexColumnListRangeService is also a list service, you must design the format for all elements in list as the same way as ColumnListRangeService. If in the element of ComplexColumnListRangeService there is a child range using ColumnListRangeService, make sure each element’s format is designed well too. See the following template file, template range configuration and data model.
Here ROOT range has two child ranges named TITLE and DATA. TITLE is a BlockRangeService and has one child range SIMAI which is processed by service BlockRowListRangeService. Let us see DATA range in detail. DATA range is processed by ComplexColumnListRangeService which has four child ranges; those of them are processed by BlockRowListRangeService. To understand how it works, please check the each child range’s region. ComplexColumnListRangeService adds the data from left to right. In the contrary, ComplexColumnListRangeServiceRL processes the data from right to left.
The following is the part of template.
Here is the template range configuration file.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net"> <object id="ComplexColumnListRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT" /> <property name="LeftTop" value="A1" /> <property name="RightBottom" value="IR147" /> <property name="ServiceId" value="RowRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ComplexColumnListRangeServiceTest_1_ROOT_TITLE" /> <ref object="ComplexColumnListRangeServiceTest_1_ROOT_DATA" /> </list> </property> </object> <object id="ComplexColumnListRangeServiceTest_1_ROOT_TITLE" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="TITLE" /> <property name="LeftTop" value="B2" /> <property name="RightBottom" value="K147" /> <property name="ServiceId" value="BlockRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ComplexColumnListRangeServiceTest_1_TITLE_SIMAI" /> </list> </property> </object> <object id="ComplexColumnListRangeServiceTest_1_TITLE_SIMAI" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="SIMAI" /> <property name="LeftTop" value="B32" /> <property name="RightBottom" value="B46" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="ComplexColumnListRangeServiceTest_1_ROOT_DATA" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="DATA" /> <property name="LeftTop" value="L2" /> <property name="RightBottom" value="U147" /> <property name="ServiceId" value="ComplexColumnListRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ComplexColumnListRangeServiceTest_1_DATA_NAME_LIST" /> <ref object="ComplexColumnListRangeServiceTest_1_DATA_DA_LIST" /> <ref object="ComplexColumnListRangeServiceTest_1_DATA_BAJYU_LIST" /> <ref object="ComplexColumnListRangeServiceTest_1_DATA_DA2_LIST" /> </list> </property> </object> <object id="ComplexColumnListRangeServiceTest_1_DATA_NAME_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="NAME_LIST" /> <property name="LeftTop" value="L32" /> <property name="RightBottom" value="L33" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="ComplexColumnListRangeServiceTest_1_DATA_DA_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="DA_LIST" /> <property name="LeftTop" value="L58" /> <property name="RightBottom" value="S59" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="ComplexColumnListRangeServiceTest_1_DATA_BAJYU_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="BAJYU_LIST" /> <property name="LeftTop" value="L66" /> <property name="RightBottom" value="U81" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="ComplexColumnListRangeServiceTest_1_DATA_DA2_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="DA2_LIST" /> <property name="LeftTop" value="L120" /> <property name="RightBottom" value="T121" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> </objects>
The result processed by ComplexColumnListRangeService is shown in the following.
4.1.15-16. ComplexColumnCloneRangeService and ComplexColumnCloneRangeServiceRL
ComplexColumnCloneRangeService is a list clone service like ColumnCloneRangeService. The difference between ComplexColumnCloneRangeService and ColumnCloneRangeService is that the element of ComplexColumnCloneRangeService’s list data is able to include child ranges and child range is able to be any range type. ColumnCloneRangeService’s element is not able to include child ranges. See the following template file, template range configuration and data model. Using ComplexColumnCloneRangeService, your template design becomes very simple. As the same as service of ColumnCloneRangeService, you don’t have to copy the format for each element rows in the list. Here we use ComplexColumnCloneRangeServiceRL to process the range of DATA, the cloned columns are added into the left of range DATA from right to left. The range of DATA has four child ranges. The child range of BAJYU_LIST has three elements; the first element region is from B66 to K81. Please check the output result. The column index of formulas and other objects in the right of range DATA will be changed, ExcelTDF automatically searches these objects and changes the column index of them.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net"> <object id="ComplexColumnCloneRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT" /> <property name="LeftTop" value="A1" /> <property name="RightBottom" value="Q148" /> <property name="ServiceId" value="RowRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CCCRST_1_TITLE" /> <ref object ="CCCRST_1_DATA"/> </list> </property> </object> <object id="CCCRST_1_TITLE" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="TITLE" /> <property name="LeftTop" value="L2" /> <property name="RightBottom" value="U147" /> <property name="ServiceId" value="BlockRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CCCRST_1_SIMAI" /> </list> </property> </object> <object id="CCCRST_1_SIMAI" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="SIMAI" /> <property name="LeftTop" value="L32" /> <property name="RightBottom" value="U33" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="CCCRST_1_DATA" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="DATA" /> <property name="LeftTop" value="B2" /> <property name="RightBottom" value="K147" /> <property name="ServiceId" value="ComplexColumnCloneRangeServiceRL" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CCCRST_1_DA_LIST" /> <ref object="CCCRST_1_BAJYU_LIST" /> <ref object="CCCRST_1_DA2_LIST" /> <ref object="CCCRST_1_NAME_LIST" /> </list> </property> </object> <object id="CCCRST_1_DA_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="DA_LIST" /> <property name="LeftTop" value="C58" /> <property name="RightBottom" value="I59" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="CCCRST_1_BAJYU_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="BAJYU_LIST" /> <property name="LeftTop" value="B66" /> <property name="RightBottom" value="K81" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="CCCRST_1_DA2_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="DA2_LIST" /> <property name="LeftTop" value="B120" /> <property name="RightBottom" value="K121" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="CCCRST_1_NAME_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="NAME_LIST" /> <property name="LeftTop" value="B32" /> <property name="RightBottom" value="K33" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="CCCRST_SHEET2" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT" /> <property name="LeftTop" value="A1" /> <property name="RightBottom" value="BP41" /> <property name="ServiceId" value="RowRangeService" /> </object> <object id="CCCRST_SHEET3" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT" /> <property name="LeftTop" value="A1" /> <property name="RightBottom" value="BP41" /> <property name="ServiceId" value="RowRangeService" /> </object> </objects>
Following is the result document processed by ComplexCiolumnCloneRangeService.
4.1.17. MultiplePageRangeService
MultiplePageRangeService is created to process some complicated reports or excel documents which has multiple different page designs. Sometimes we want to create a report which has page header, page footer, report header and report footer. In ExcelTDF, you can considered them as some ranges. In fact, using RowRangeService or BlockRowRangeService, you can output header and footer information easily. If your document or report has only one page, you can do your design using services explained on the above. By assembling those services, ExcelTDF is able to output various documents and reports as what you want. If your report has more pages that all of them are the same design, you can use ComplexRowCloneRangeService or ComplexRowListRangeService to output your report page by page. In this case, a page is an element of list. MultiplePageRangeService provides ability for creating document and report which has different page design while a list data might be outputted across all pages. Usually we want to output some summary information into the first page or last page, the detail information page by page. Depending on the size of list data, the number of pages to be outputted will be variable. MultiplePageRangeService controls these behaviors according to the properties set in the template range configuration file. MultiplePageRangeService is able to process document and report which has two or three different page designs.
Let us see following template, which has two reports, owner sales report and parking sales report. In the template, owner sales report has three different pages and parking sales report has one page. We want to output owner report and parking report for one owner in one worksheet. One owner maybe has a lot of parking, we need to output parking name, parking sales amount as one line for each parking in the owner report and output parking sales amount detail data in the parking report.
Download template SalesReport
Parking sales report template page is omitted.
The following is template range configuration file, download SalesReport
In the worksheet, there are two child ranges, and they are owner report and parking report named OWNER_REPORT, PARKING_REPORT. Owner report range has three page child ranges, OWNER_PAGE1, OWNER_PAGE2, OWNER_PAGE3. For three page ranges, there is a list range of sales amount for each parking. Assume the owner has 56 parkings, the list size will be 56. In the template configuration, the PageSize of the range PARK_DATA_LIST in OWNER_PAGE1, OWNER_PAGE2, OWNER_PAGE3 is 15, so the owner report will be output 4 pages. The property of IsRepeat in internal page OWNER_PAGE2, which is set to true, means this page is able to output repeated. ExcelTDF controls output behavior based on those properties. PARK_DATA_LIST range is outputted across all pages, this behavior is controlled by property of IsCrosssPage. Besides owner report, each parking detail data is outputed as one page, thus in the worksheet we output owner report as 4 pages and parking report as 56 pages.
<?xml version="1.0" encoding="utf-8" ?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="SalesReport_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="SALES_REPORT"/> <property name="LeftTop" value="B1"/> <property name="RightBottom" value="BG209"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="OwnerReport"/> <ref object="ParkReport"/> </list> </property> </object> <object id="OwnerReport" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="OWNER_REPORT"/> <property name="LeftTop" value="B1"/> <property name="RightBottom" value="BG150"/> <property name="ServiceId" value="MultiplePageRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="OwnerPage1"/> <ref object="OwnerPage2"/> <ref object="OwnerPage3"/> </list> </property> </object> <object id="OwnerPage1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="OWNER_PAGE1"/> <property name="LeftTop" value="B1"/> <property name="RightBottom" value="BG50"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ParkData1"/> </list> </property> </object> <object id="ParkData1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARK_DATA_LIST"/> <property name="LeftTop" value="B24"/> <property name="RightBottom" value="BG24"/> <property name="ServiceId" value="RowListRangeService"/> <property name="PageSize" value="15"/> <property name="IsCrossPage" value="true"/> </object> <object id="OwnerPage2" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="OWNER_PAGE2"/> <property name="LeftTop" value="B51"/> <property name="RightBottom" value="AT100"/> <property name="ServiceId" value="ComplexRowCloneRangeService"/> <property name="IsRepeat" value="true"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ParkData2"/> </list> </property> </object> <object id="ParkData2" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARK_DATA_LIST"/> <property name="LeftTop" value="B74"/> <property name="RightBottom" value="AJ74"/> <property name="ServiceId" value="RowListRangeService"/> <property name="PageSize" value="15"/> <property name="IsCrossPage" value="true"/> </object> <object id="OwnerPage3" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="OWNER_PAGE3"/> <property name="LeftTop" value="B101"/> <property name="RightBottom" value="AT150"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ParkData3"/> </list> </property> </object> <object id="ParkData3" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARK_DATA_LIST"/> <property name="LeftTop" value="B124"/> <property name="RightBottom" value="AJ124"/> <property name="ServiceId" value="RowListRangeService"/> <property name="PageSize" value="15"/> <property name="IsCrossPage" value="true"/> </object> <object id="ParkReport" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARK_REPORT"/> <property name="LeftTop" value="B151"/> <property name="RightBottom" value="AT209"/> <property name="ServiceId" value="ComplexRowCloneRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="AdditionItems"/> </list> </property> </object> <object id="AdditionItems" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ADDITIONS"/> <property name="LeftTop" value="B192"/> <property name="RightBottom" value="AJ192"/> <property name="ServiceId" value="BlockRowListRangeService"/> </object> </objects>
Download the result of report SalesReport result
The following is a part of data model.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using JanaBiz.OpenXml;
namespace OpenXmlDemoData.Data
{
/// <summary>
/// sales report
/// </summary>
[ExcelRange("SALES_REPORT")]
public class SalesReportData
{
#region constructor
public SalesReportData()
{
}
#endregion
/// <summary>
/// all parking data of owner
/// </summary>
[ExcelRange("OWNER_REPORT")]
public OwnerSalesReport ownerReport = new OwnerSalesReport();
/// <summary>
/// sales data of parking
/// </summary>
[ExcelRange("PARK_REPORT")]
public IList<OwnerParkSalesData> ParkList
{
get { return ownerReport.ParkList; }
}
}
}
4.1.18. BlockRowCloneRangeService
BlockRowCloneRangeService is similar to the RowCloneRangeService. The difference is that BlockRowCloneRangeService clones only cells from specified top row to bottom row between specified columns, so the row index of cells that is between the specified columns and row index is greater than the bottom row will be moved after cloned rows are added.
On the other hand, the RowCloneRangeService clones cells from specified top row to bottom row without the limitation of column and moves all cell’s index below the range itself after the cloned rows are added.
The result is shown in the following, you can see the range B3 and K4 is cloned and added on the below. The cells that the column index is between column B and K and the row is greater than 4 are moved.
4.1.19. BlockColumnCloneRangeService
BlockColumnCloneRangeService is similar to the ColumnCloneRangeService. The difference is that BlockColumnCloneRangeService clones only cells from specified left column to right column between specified rows, so the column index of cells that is between the specified rows and greater than the right column will be moved after cloned columns are added.
On the other hand, the ColumnCloneRangeService clones columns from specified left column to right column without row limitation, the columns index that is greater than the right column will be changed after cloned columns is added.
The result is shown in the following, you can see the range B3 and C23 is cloned and added on the right. The cells that row is between row 3 and 23 and column is greater than C are moved.
4.2. IExcelCellService & IExcelFunctionService
In ExcelTDF, one of the most flexible functionality is that you can create your own custom service. Custom service lets you control your cell output in the template design, that is similar to the excel function. Custom service has two types. The implementation class of IExcelCellService is custom cell service while the implementation class of IExcelFunctionService is custom function service. Cell service has no return result, in the contrary, function service return a string as result. In the custom service, the parameter can be template variable, cell name, constant of text or number. The base class of ExcelCellServiceBase provides a lot of methods to get parameter’s cell name or values. On the above example of MultiplePageRangeService, we used XSCHECKBOX, XSPAGEBREAK, XSBARCODE cell services and XFPRANGE and XFSRANGE function services. If parameter of service is cell name, the index of cell’s row and column will be recalculated. If the parameter of service is template variable, then ExcelTDF will search template variable’s cell name.
ExcelTDF provides following cell services and function services now.
4.2.1. Cell Service
4.2.1.1. XSCHAR
Output amount digit by digit.
Format:
%{=XSCHAR(TEXT, 1|LEFT|left|0|RIGHT|right)}
%{=XSCHAR(TEXT, 1|LEFT|left|0|RIGHT|right, LEADING)}
%{=XSCHAR(TEXT, 1|LEFT|left|0|RIGHT|right, LEADING, TAILING)}
where TEXT is a template variable or cell name or constant of number. Second parameter of 1|LEFT|left indicates that output digit from left to right(left alignment) start with the cell of this service. 0|RIGHT|right indicates that output digit from right to left (right alignment) start with the cell of this service. The third and fourth parameter is appended char on the beginning or ending of char, that maybe dollar marker etc.
Example:
%{=XSCHAR(“123999″, 0, “$”)}, %{=XSCHAR(A4, 1, “$”, “-”)}
4.2.1.2. XSCHECKBOX
Output a checkbox. The checkbox is not a vb control, it is just using some special font.
Format:
%{=XSCHECKBOX(FLAG)}
%{=XSCHECKBOX(FLAG,TEXT)}
where FLAG is a flag to indicate checked or not, 1|true is checked, otherwise not checked. The second parameter of TEXT is a string after checkbox. Both FLAG and TEXT can be a template variable, cell name of constant string, number.
Example:
%{=XSCHECKBOX(PARKTYPE)}, %{=XSCHECKBOX(PARKTYPE, PARKNAME)}
In data model
[Excel("PARKTYPE")]
public int ParkType
[Excel("PARKNAME")]
public string ParkName
4.2.1.3. XSBARCODE
XSBARCODE outputs bar code on the specified image. In order to output bar code you must add a image to template where your bar code will be output. Bar code will be output as the same size as image.
Format:
%{=XSBARCODE(IMAGENAME, BARCODETEXT, TYPE)}
%{=XSBARCODE(IMAGENAME, BARCODETEXT, TYPE, SHOW_LABEL)}
IMAGENAME: the image name you have added into template
BARCODETEXT: bar code string, which can be template variable, cell name or constant text, number
TYPE: bar code type
SHOW_LABEL: 1 or true specify to output string of bar code text.
Following bar code types are supported:
UCC12
UPCA, UPC-A
UCC13, EAN13, EAN-13
Interleaved2of5
Industrial2of5
Standard2of5
LOGMARS
CODE39
CODE39Extended
Codabar
PostNet
ISBN,BOOKLAND
JAN13,JAN-13
UPC_SUPPLEMENTAL_2DIGIT
MSI_Mod10,MSI_2Mod10,MSI_Mod11,MSI_Mod11_Mod10,Modified_Plessey
UPC_SUPPLEMENTAL_5DIGIT,UPCE,UPC-E
EAN8,EAN-8
USD8,CODE11
CODE128,CODE128A
CODE128B,CODE128C
ITF14,CODE93
Example:
%{=XSBARCODE(“BCODE”, BARCODENO, “UPC-A”)}
BCODE is image name that bar code image will replace it.
4.2.1.4. XSPAGEBREAK
Add excel page break in the worksheet for document and report printing. XSPAGEBREAK is very useful for more page output.
Format:
%{=XSPAGEBREAK}
%{=XSPAGEBREAK(ROW|CELL|VAR)}
If no parameter is specified , add page break on the row of this service written. If row or cell is specified add page break on the specified row. If template variable is specified, add page break on the row on which template variable is.
Example: %{=XSPAGEBREAK}
On the example of service MultiplePageRangeService, XSPAGEBREAK is added in the template.
XSPAGEBREAK are added on the last row in last page of owner report and parking report. Parking report is a page processed a clone service. When parking page is cloned, the page break will be added on the last row of cloned page.
4.2.1.5. XSVDLIST
Set cell as pull down list. It is the same as the menu of Data/Data Input/List in the Excel.
Format:
%{VAR=XSVDLIST(“A,B,C”|AJ12:AJ20|VARIABLE)}
XSVDLIST is some different from services above. The left side variable name is the value of cell in pull down list while the parameter in expression of XSVDLIST is pull down items which is able to be a string with comma separated or cell range or variable which is in the list range. The value of VAR can be referenced by other expression and can be saved back into data model as other variables.
Example:
%{ITEM=XSVDLIST(“iPod,iPhone,iPad”)}
In data model:
[Excel("ITEM")]
public string Item {get; set;}
4.2.2. Function Service
Custom function service can be used in expression of custom cell service, other custom function service and Excel formula.
4.2.2.1. XFPRANGE
Search variable from specified parent range and return the cell name such as A12 or cell name string such as A12,A13,A14 if variable is in the list. Because that ExcelTDF search variable first from range of itself, then the child ranges, if you have the same variable name in different range, this function maybe useful, you can search variable from your specified range. Usually, in child range you can reference parent range’s variable directly by this function.
Format:
%{=XFPRANGE(VAR)}
%{=XFPRANGE(VAR, PARENT_LEVEL)}
VAR: template variable to be searched
PARENT_LEVEL: the number of parent hierarchy
second parameter is the specified number from which parent range to search variable. For example, if the second parent is 2, search variable VAR from the parent’s parent range. If no second parameter is specified, search from parent range
Example:
#C{=SUM(XFPRANGE(FURIKOMI, 1))}
On the above MultiplePageRangeService, in the first page of owner report we want to output total sum of FURIKOMI which is across in the first page, internal page and last page. So we specified search FURIKOMI from parent range, that is OWNER_REPORT which includes all pages. FURIKOMI is a variable in a list, so SUM(XFPRANGE(FURIKOMI, 1)) is the total amount.
4.2.2.2. XFSRANGE
Search variable from all brother ranges including itself and return the cell name such as A12 or cell name string such as A12,A13,A14 if variable is in the list.
Format:
%{=XFSRANGE(VAR)}
VAR is the variable to be searched.
Example:
#C{小計=SUM(FURIKOMI)}
#C{合計=SUM(XFSRANGE(小計))}
On the above MultiplePageRangeService, in owner report, each page has a variable named 小計 which is the sum amount of parking in that page and in the last page there is a variable 合計 which is the sum of all 小計 in each page. You can download sales report result on the above to see how it works.
4.2.2.3. XFCELL
This is a simple function to get variable’s cell name and return cell name or cell name string such as A12,A13,A14.
Format:
%{=XFCELL(VAR)}
Example:
%{=XFCELL(ITEM)}
4.2.2.4. XFMATCH
XFMATCH is used to search some values from specified searching range, if matched, it returns the cell names or values.
Format:
%{XFMATCH(SEARCH_VALUES,SEARCH_RANGE)
%{XFMATCH(SEARCH_VALUES,SEARCH_RANGE, PARENT_HIERARCHY)
SEARCH_VALUES: the values to be searched. which can be a variable, cell name such as AJ12 or AJ12:AK20, or constant string separated by comma such as “A,B,C”. This function searches value by some orders. Assume SEARCH_VALUES is specified as “A,B,C”, function will search A first, if found return the result, then B, then C.
SEARCH_RANGE: the search range which can be a variable, cell name, or any constant string.
PARENT_HIERARCHY: Specify from which template range to search, 1 is the parent, 2 is the parent’s parent template range.
Example:
#{=CONCATENATE(XFOFFSET(XFMATCH(“◎,○,▲”, 本紙, 1), 0, 1), “=”, XFOFFSET(XFMATCH(“○,▲,△”, 本紙, 1), 0, 1), XFOFFSET(XFMATCH(“△”, 本紙, 1), 0, 1))}
In the example of ColumnCloneRangeService, we used function as above to search the cell which has one of values ◎,○,▲. You can see the function is used in Excel formula expression.
4.2.2.5. XFOFFSET
Get cell name with the specified offset. This function is like excel formula function OFFSET.
Format:
%{=XFOFFSET(VAR,ROW,COL,PARENT_HIERARCHY)}
VAR: variable to be searched, which is a template variable.
ROW: row offset number
COL: column offset number
PARENT_HIERARCHY: Specify from which template range to search, 1 is the parent, 2 is the parent’s parent template range.
Example:
#{=CONCATENATE(XFOFFSET(XFMATCH(“◎,○,▲”, 本紙, 1), 0, 1), “=”, XFOFFSET(XFMATCH(“○,▲,△”, 本紙, 1), 0, 1), XFOFFSET(XFMATCH(“△”, 本紙, 1), 0, 1))}
4.3. How to create your service
ExcelTDF provides mechanism to create your own range service, cell service and function service. The three interfaces are used to let you create your service easily. These interfaces are IExcelRangeService, IExcelCellService and IExcelFunctionService.
4.3.1. How to create template range service, IExcelRangeService
See the example of MyRowRangeService which do the same works as RowRangeService.
4.3.1.1. Create IExcelRangeService implementation class
using System; using System.Collections.Generic; using System.Linq; using System.Text; using log4net; using JanaBiz.Common.Serialize; using JanaBiz.OpenXml.Data; namespace myservice { /// <summary> /// Process excel range defined by top row, and bottom row. /// Template variable are replaced by value in data model. /// This is simple range. worksheet root range is this type. /// </summary> public class MyRowRangeService : ExcelRangeServiceBase, IExcelRangeService { /// <summary> /// Logger /// </summary> static readonly ILog logger = LogManager.GetLogger(typeof(MyRowRangeService)); /// <summary> /// constructor /// </summary> public MyRowRangeService() : base() { } #region interface method /// <summary> /// Parse specified worksheet using data provider /// </summary> /// <param name="sheetIndex"></param> public virtual void Parse(int sheetIndex) { //if no data, not processing if (ExcelRange == null || ExcelRange.DataProvider == null) return; //get data provider IRangeDataProvider dataProvider = ExcelRange.DataProvider; //reset mapping info ExcelRange.DataMapping.Clear(); //get mapping info from data provider SerializableDictionary<string, ExcelAttribute> dmp = dataProvider.GetMappingData(dataProvider[0]); ExcelRange.DataMapping.Add(dmp); //set data values into mapped variables SetNewCellValues(ExcelRange, dmp, ExcelRange.Top, ExcelRange.Bottom, 0, 0); //no row or column index moved or changed ExcelRange.ChangedRowCount = 0; //process flag of this range ExcelRange.Parsed = true; } #endregion } }
4.3.1.2. Add your service object into configuration file
In your application configuration file, there is Spring.NET configuration section of object; add your object as following.
<!-- my range service--> <objects> <object id="MyRowRangeService" type="myservice.MyRowRangeService, Your_Assembly(dll)" singleton="false"/> </objects>
4.3.1.3. Set your service into your template range configuration
<?xml version="1.0" encoding="shift_jis"?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="MyRowRangeService_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT"/> <property name="Top" value="8"/> <property name="Bottom" value="10"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="MyRangeService"/> </list> </property> </object> <object id="MyRangeService" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="MYRANGE"/> <property name="LeftTop" value="L8"/> <property name="RightBottom" value="BE10"/> <property name="ServiceId" value="MyRowRangeService"/> </object> </objects>
That is all, now ExcelTDF will call your service of MyRowRangeService to process specified range in your template configuration file.
4.3.2. How to create your custom cell service, IExcelCellService
See the example of MyVDListCellService which performs the same work as XSVDLIST
4.3.2.1. Create implementation class of IExcelCellService
using System; using System.Collections.Generic; using System.Collections; using System.Linq; using System.Text; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using log4net; using JanaBiz.ExcelFormulaParser; namespace myservice { /// <summary> /// My validate list cell service /// </summary> public class MyVDListCellService : ExcelCellServiceBase, IExcelCellService { static readonly ILog logger = LogManager.GetLogger(typeof(MyVDListCellService)); public MyVDListCellService() : base() { } public void Parse(int sheetIndex) { //1: get parameters Parameters = GetExpressionParameters(ExprTreeNode, sheetIndex); //2: check exists function variable in template if (!ExcelRange.IsMyVariable(CellData.VarName)) { throw new ServiceException(this, "E_NO_VDLIST_VAR", CellData.VarName); } //3: check if no paramter if (Parameters.Count == 0) { throw new ServiceException(this, "E_NO_PARAM"); } //4: check if parameter is empty string listValues = Parameters[0].Param; if (string.IsNullOrEmpty(listValues)) { throw new ServiceException(this, "E_EMPTY_PARAM"); } //5: parameter type check switch (Parameters[0].ParamType) { //if it is variable case ParamType.VARIABLE: IList<string> cells = GetReferencesOfVariable(listValues, sheetIndex, ExcelRange, ListIndex); if (cells == null || cells.Count == 0) { throw new ServiceException(this, "E_VAR_NOT_FOUND", listValues); } string[] cellrefs = OpenXmlSpreadsheet.CreateMergeCellname(WorksheetPart, cells.ToArray()); if (cellrefs.Length > 1) { listValues = string.Format("{0}:{1}", cellrefs[0], cellrefs[1]); } break; //if it is cell reference case ParamType.CELL: //if it is text case ParamType.TEXT: break; } //do your work here //set data validate list formula1 SetCellValue(CellData.DataType, CellData.CellName, CellData.CellValue); OpenXmlSpreadsheet.SetDataValidateFormula1(WorksheetPart, CellData.CellName, listValues); } } }
4.3.2.2. Add your service object into configuration file
In your application configuration file, there is Spring.NET configuration section of object, add your object as following.
<!-- my cell service--> <objects> <object id="MYVDLIST" type="myservice.MyVDListCellService, Your_Assembly(dll)" singleton="false"/> </objects>
Now you can use MYVDLIST in your template. ExcelTDF will parse template and build your cell service MYVDLIST by calling your created class.
4.3.3. How to create your custom function service, IExcelFunctionService
Custom function service is the same as custom cell service except that function service must return a result. The result is set in property of FunctionResultValue. See the following example; it does the same work as the XFCELL function explained above.
4.3.3.1. Create your implementation class of IExcelFunctionService
using System; using System.Collections.Generic; using System.Collections; using System.Linq; using System.Text; using log4net; using JanaBiz.OpenXml; namespace myservice { /// <summary> /// Get variable's cell name /// %{=XFCELL(VAR)} /// </summary> public class MyCellFunctionService : ExcelCellServiceBase, IExcelFunctionService { static readonly ILog logger = LogManager.GetLogger(typeof(MyCellFunctionService)); public MyCellFunctionService() : base() { } /// <summary> /// do your work here /// </summary> /// <param name="sheetIndex"></param> public void Parse(int sheetIndex) { Parameters = GetExpressionParameters(ExprTreeNode, sheetIndex); //1: no parameter check if (Parameters.Count == 0) throw new ServiceException(this, "E_NO_PARAM"); //2: get cellname parameter string var1 = Parameters[0].Param; //3: empty parameter check if (string.IsNullOrEmpty(var1)) throw new ServiceException(this, "E_EMPTY_PARAM"); //4: parameter type switch (Parameters[0].ParamType) { //it is a variable case ParamType.VARIABLE: FunctionResultValue = GetVariableResultAsString( GetReferencesOfVariable(var1, sheetIndex, ExcelRange, ListIndex)); break; //it is a cell name case ParamType.CELL: FunctionResultValue = var1; break; //for others default: throw new ServiceException(this, "E_NOT_CELLNAME", var1); } } } }
4.3.3.2. Add your service object into configuration file
In your application configuration file, there is Spring.NET configuration section of object, add your object as following.
<!-- my function service--> <objects> <object id="MYCELL" type="myservice.MyCellFunctionService, Your_Assembly(dll)" singleton="false"/> </objects>
Now you can use MYCELL in your template. ExcelTDF will parse template and build your cell service MYCELL by calling your created class.
3.1. How to use ExcelTDF in your solution
As the following diagram shows, ExcelTDF is able to run at server-side, client-side and client-only. The interface and usage is the same wherever the ExcelTDF is running. For the both server-side and client-side, the templates of excel are managed on the server. The difference between server-side and client-side is that client-side provides auto saving functionality.
3.2. ExcelTDF Core classes
ExcelTDF does almost of work for you by some core classes. Excel2007TemplateParser and Excel2007DataParser are the two of these classes. But the convenient way for you is using implementation classes of IExcelHandler or IOpenXmlHandler interface named ExcelHandler, OpenXmlHandler. Excel2007TemplateParser, as the name of class, analyzes the template file and load the template range configuration file. After template is analyzed, template parser will know the mapping relationship of variables/cells and template range tree. Excel2007DataParser analyzes your data class structure and its meta data or SQL data. Metadata named ExcelRange and ExcelAttribute defines range information and Excel cell variable information. After data is analyzed, Excel2007DataParser will know the data/variable mapping relationship and get ExcelRange‘s tree structure that is used to used to determine how to process template.
3.3. How to get ExcelTDF work
In order to let ExcelTDF work, there are fourth steps to do, the first two steps is only needed to do one time.
. Set Configuration
. Creating template file by Excel 2007 above and creating template range configuration file. If you use Metadata to define all range information, this step can be omitted
. Creating IDataProvider class or Data Model class to get data
. Download or open your parsed document or report
3.4. Set Configuration
When you use ExcelTDF to process template, you should tell ExcelTDF how to get template files and template range configuration files. Your template files and template range configuration files maybe saved as files in specified directory or saved in database or gotten from service.
3.4.1. Manage templates by files
Your template excel file and template range configuration file can be managed on server by files. In ExcelTDF we call it repository. Typically your configuration file is named as Repository.config. It is loaded by ExcelTDF when application starting.
The following is the content of Repository.config. You can add more repositories in this file.
<?xml version="1.0" encoding="utf-8" ?> <objects xmlns="http://www.springframework.net"> <object id="mydemo" type="JanaBiz.OpenXml.Repository.FileSystemRepositoryManager, JanaBiz.OpenXml"> <property name="RepositoryName" value="myrepository"/> <property name="RepositoryUri" value="../../Templates"/> </object> </objects>
On the above, the root repository directory is ../../Templates/myrepository. This is relative path to the executable dll. Certainlly, it is can be any full path. It is also be able to database connection string or others for RepositoryUri.
Usually you can use following two ways to load Repository.config when application starting.
a). For console application or Windows form application
[assembly: RegisterRepository("your_namespace.Repository.config")]
b). For all type of application
OpenXmlContext.RegisterRepository("your_namespace.Repository.config");
Refresh your repository
If your template range configuration file is changed or you added, removed some lines in template.xml and resource.xml, you must refresh context to load new configuration information. ExcelTDF provides a method in class OpenXmlContext to reload repositories.
3.4.2. Manage templates by database
If you get template file and template range configuration file from database or other stream resource, you don’t have to set repository information . The resource handler’s protocol named “memory” will load your template file, template range configuration file into context. In order to do this work, you must create a class that implements the interface ITemplateService. By following code, you can set your database template service which implements ITemplateService. This tells ExcelTDF MyDatabaseTemplateService is used to get template file and template range configuration, similar to handler.GetTemplateService(“myconfig”) which use LocalTemplateService to load template files in the repository. There are no access limitation when using database to manage templates, you can do yourself.
ExcelHandler handler = new ExcelHandler(); //implements ITemplateService interface MyDatabaseTemplateService service = new MyTemplateService(); handler.SetTemplateServiceservice(service); //or MyDatabaseTemplateService service = handler.GetTemplateServiceservice<MyDatabaseTemplateService >();
3.4.3. Get templates from web service
If your templates managed on server and you use IExcelHandler on client side, you can tell ExcelTDF getting templates by web service. In this case, your web service must implement methods defined in ITemplateService but don’t need to implement the interface of ITemplateService. This feature gives you capability of managing your templates by web service very simple. Following is some code.
ExcelHandler handler = new ExcelHandler();
...
TemplateServiceSoapClient templateService = handler.GetTemplateService<TemplateServiceSoapClient>();
3.5. Template File
keywords:
. General variable
${VAR1}
. Formula variable
#{=SUM(A20*SUM(VAR1)+VAR2)}
. Custom service variable
%{=XSPAGEBEAK}
. Parameter variable
?{VAR=PARAM1}
. Template Range binding
[ ExcelRange("MyRange", "A1", "Z20", "RowRangeService")]
. Excel Variable binding
[Excel("VAR1"......)]
3.5.1. Variables in Template
When template processing, IExcel2007TemplateParser will search variables in the template and collect mapped information of variable and cell name. As the result, variables are mapped into data value after Excel2007DataParser parsed document. Template variable is a name of an arbitrary text string, whose formats are defined as ${..}, #{..}, %{..}, ?{..} four types.
3.5.1.1. General variable: ${VAR1}
VAR1 is a general variable. ExcelTDF will replace the the variable with the value in data model or SQL query data or some others depending on what data provider is used. For the data model, here is an example.
[ExcelRange("ROOT", "A1, "C3", "RowRangeService")]
public class MyTestMode
[Excel("VAR1")]
public string MyName {get; set; }
}
As the above, after processed, the variable of ${VAR1} will be replaced by value of MyName property in the data mode of MyTestMode. If ${VAR1} is in list range and MyTestMode is element of list, The ${VAR1} will be the value of each element MyTestMode in the list.
3.5.1.2. Formula variable: #{VAR2=SUM(VAR1, CJ12, 20)}
Formula variable is an Excel formula expression variable. After processed, the cell of formula variable will be a normal Excel formula function expression, such as =SUM(R12) etc. In the above expression, the left side of the equal “=” is variable name VAR2, and the right side is expression. Here VAR2′s value is the sum of VAR1. ExcelTDF will search VAR1 variable and compute the cell name of VAR1 automatically. In the expression of formula variable, you can use both normal cell name, such as CJ12, AR12:AR20, $AR$20 and variable name. ExcelTDF is able to identify expression variable type of cell name, variable, text and number. For example, assume that VAR1 is in the list range and the cells are AK20,AK21,AK22 after range is processed, the variable of #{VAR2=SUM(VAR1, CJ12, 20)} will be explained as the sum of =SUM(AK20,AK21,AK22, CJ12, 20).
#{VAR2=SUM(VAR1, CJ12, 20)} --> =SUM(AK20,AK21,AK22, CJ12, 20)
[ExcelRange("ROOT", "A1, "C3", "RowRangeService")]
public class MyTestMode
[Excel("VAR1")]
public string MyName {get; set; }
[Excel("VAR2")]
public double Sum {get; set; }
}
Formula variable expression is able to has sub expression. Generally no limitation on the formula expression, you can use as normal excel file. Here is complicated one. The variable’s name is MYNO, in the expression, no other variables referenced.
#{MYNO=IF(ISERROR(MATCH("◎", R6:BZ6, 0))=FALSE,
HLOOKUP("◎", R6:BZ9, 3, FALSE),
IF(ISERROR(MATCH("○", R6:BZ6, 0))=FALSE,
HLOOKUP("○", R6:BZ9, 3, FALSE),
IF(ISERROR(MATCH("▲", R6:BZ6, 0))=FALSE,
HLOOKUP("▲", R6:BZ9, 3, FALSE),
IF(ISERROR(MATCH("△", R6:BZ6, 0))=FALSE,
HLOOKUP("△", R6:BZ9, 3, FALSE),
""
)
)
)
)}
The following are also formula variables. In the expression, TRACKNO, FACTOR, QUANTITY, PRICE, BASEPRICE are referenced variables, ExcelTDF will replace them with correct cell names when building formula.
#{VAR2=CONCATENATE("ABCD",LEFT(TRACKNO,1))}
#{VAR2=SUM(FACTOR*SUM(QUANTITY*PRICE))+ BASEPRICE*15%}
If no other variable references VAR2, the name of VAR2 can be omitted. for example
#{=SUM(FACTOR*SUM(QUANTITY*PRICE))+ BASEPRICE*15%}
Certainly, you can use excel formula directly if the cell name is not changed dynamically and don’t need to reference a variable. ExcelTDF don’t process them, for example =SUM(12*C12), this is not a variable.
In the excel formula variable expression, you can use custom custom function service as the same as Excel function. About custom function service, please see next section of Custom service variable.
3.5.1.3. Custom service variable: %{VAR3=XFSRANGE(RACENO)}
Custom service variable is customized cell service or function service, which is scalable functionality provided by ExcelTDF. ExcelTDF provides several custom cell services and function services.
You can develop your custom cell service and function service too. Custom services are used to processing special cells or some computing functionality. There are two types of custom services, one is Cell Service which implements interface of IExcelCellService, and another is Function Service which implements interface of IExcelFunctionService. IExcelFunctionService is a sub interface of IExcelCellService. The two types of services are used for different purpose. In order to distinguish from Excel formula functions, cell service name and function service name in ExcelTDF start with XS and XF. In fact the name is the object id defined in the Spring.NET configuration file.
<object id="XFSRANGE" type="JanaBiz.OpenXml.Service.XFSiblingRangeFunctionService, JanaBiz.OpenXml" singleton="false"> </object>
[ExcelRange("ROOT", "A1, "C3", "RowRangeService")]
public class MyTestMode
[Excel("VAR1")]
public string MyName {get; set; }
[Excel("VAR2")]
public double Sum {get; set; }
[Excel("VAR3")]
public string No {get; set; }
}
If no other variable references VAR3, the name of VAR3 can be omitted. for example
%{=XFSRANGE(VAR1)}
3.5.1.3.1. Cell Service
Cell service does some special processing, for example, showing a barcode. Cell service has no result returned. Following are some cell services provided by ExcelTDF.
XSBARCODE: output a barcode
XSCHECKBOX: output a checkbox
XSPAGEBREAK: add page break of excel
XSVDLIST: showing cell as pulldown list
%{=XSBARCODE("BCODE", BARCODE_NO, "UPC-A")}
%{=XSCHECKBOX(1, PLAYER)}
%{=XSPAGEBREAK(20)}
%{AAA=XSVDLIST(RESULT)}
On the above, in the expression, BARCODE_NO, PLAYER, RESULT is referenced template variables.
3.5.1.3.2. Function Service
Function Service does some processing and returns the result as string. Depending on what you want to do, the function’s result maybe is variable’s cell name or variable’s value. The function is able to have sub expression in which another custom function might be called. The following is a example.
%{=XFOFFSET(XFMATCH("◎,○,▲", RACENO, 1), 0, 1)}
In the above, custom function XFMATCH is called, and the result of XFMATCH becomes a parameter of custom function XFOFFSET.
Because custom function returns the result, the custom function can be called in expression of Excel formula variable expression, such as the below.
#{=CONCATENATE(XFOFFSET(XFMATCH("◎,○,▲", RACENO, 1), 0, 1), "=", XFOFFSET(XFMATCH("○,▲,△", RACENO, 1), 0, 1), XFOFFSET(XFMATCH("△", RACENO, 1), 0, 1))}
In the above, in excel formula function expression CONCATENATE, custom function XFOFFSET and XFMATCH are called.
Note: Custom function service can not call excel function as sub expression because the custom function service and cell service are processed only one time while workbook are loading, not in the period of running.
3.5.1.4. Parameter variable: ?{VAR2}
Parameter variable is used to specify parameter when searching the data. This is run-time functionality like CrystalReport, in the version 1.0.0.0, no support for parameter variable.
3.5.1.5. Data type of variable
In ExcelTDF, you can specify data type forcibly. You just need to add a special char after the leading of letter. N is number type, C is currency type, D is date type. If no N, C, D specified, the variable considered as general string type. In the version of 1.0.0.0, type N, C will forcibly set cell type as NUMBER, D is now processed as string type. Don’t confuse the data type with data output format. Output format such as $#,###,### is set by excel on the template designing, data type just tell excel the output data is whether a number or not etc. The following is examples.
General variable ${VAR1}, $N{VAR1}, $C{VAR1}, $D{VAR1}
${VAR1}: output as type of string or standard
$N{VAR1}: output should be a number and ExcelTDF set the cell’s type to Number
$C{VAR1}: output should be currency and ExcelTDF set the cell’s type to Number
$D{VAR1}: output should be date and ExcelTDF does as string or standard in the version 1.0.0.0
The same rules are used for formula variables, custom service variables and parameter variables.
Formula variable: #{..}, #N{..}, #C{..}, #D{..}
Custom service variable: %{..}, %N{..}, %C{..}, %D{..}
Parameter variable: ?{..}, ?N{..}, ?C{..}, ?D{..}
3.5.1.6. The Range of variable
In a range, variable must be unique, but in different range, there is no this limitation. When a variable is referenced by formula or custom service, ExcelTDF try to search the variable and find cell names or values of variable. ExcelTDF search variable by some orders. ExcelTDF always search expression variable first from the range of variable itself. If not found, ExcelTDF then search expression variable from child ranges. Last, try to search expression variable from all. Understanding the searching order will help you to get correct result.
3.5.2. Template range
Template range is an excel range specified by row index or column index or excel cell name. Range Service Name must be specified to identify which template range to be processed by ExcelTDF. Template range is defined both by object in Spring.NET configuration file and by Metadata defined in data model. Defined items in template range are the properties of type of class TemplateRange.
When you design your template, the first thing is that you should determine how to output data with template. You can determine how many template ranges there are and how to process template ranges according to your data structure. If your data is a list, define a template range as a list service range will simplify your processing. Please see the examples to understand Template Range and Range Services.
Template file is a Excel workbook(*.xlsx). In this version only *.xlsx format is supported. As the known, one workbook might have several worksheets. ExcelTDF splits a worksheet as some ranges which include some cells to be processed or not. For the cells to be processed, the cell text might include one or more defined variables. A range might have some child ranges, and child range is able to has its own child ranges too. These range’s definition information must be specified by template range configuration file or defined by Metadata in data model. So the whole worksheet is considered as a root range, all other ranges are worksheet’s child or descendant ranges.
See the following diagram. The worksheet root range has three child ranges named R-A, R-E, R-D, and range R-A also has two child range R-B, R-C. From diagram we can see a tree of range.
The template range configuration file, simply, we can consider it as TemplateRange class. The all items’ name are TemplateRange’s properties. The service name used to process range is listed in service names, You can get all names by call OpenXmlContext’s GetRangeServiceIds() methods too.
The following is template whose layout is the same as description above.
The following is corresponding template range configuration file.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net"> <object id="SAMPLE_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="RootRange" /> <property name="LeftTop" value="A1" /> <property name="RightBottom" value="Z51" /> <property name="ServiceId" value="RowRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="R-A-ID" /> <ref object="R-E-ID" /> <ref object="R-D-ID" /> </list> </property> </object> <object id="R-A-ID" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="R-A" /> <property name="LeftTop" value="B3" /> <property name="RightBottom" value="S31" /> <property name="ServiceId" value="BlockRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="R-B-ID" /> <ref object="R-C-ID" /> </list> </property> </object> <object id="R-E-ID" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="R-E" /> <property name="LeftTop" value="B37" /> <property name="RightBottom" value="S49" /> <property name="ServiceId" value="BlockRangeService"/> </object> <object id="R-D-ID" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="R-D" /> <property name="LeftTop" value="U2" /> <property name="RightBottom" value="Y49" /> <property name="ServiceId" value="BlockRangeService"/> </object> <object id="R-B-ID" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="R-B" /> <property name="LeftTop" value="C9" /> <property name="RightBottom" value="Q16" /> <property name="ServiceId" value="BlockRangeService"/> </object> <object id="R-C-ID" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="R-C" /> <property name="LeftTop" value="B20" /> <property name="RightBottom" value="Q29" /> <property name="ServiceId" value="BlockRangeService"/> </object> </objects>
The following is model class used to process template above.
[ExcelRange("SHEET1")]
public class RT
{
[ExcelRange("R-A")]
public RA ra;
[ExcelRange("R-D")]
public RD rd;
[ExcelRange("R-E")]
public RE re;
}
public class RA{
[ExcelRange("R-B")]
public RB rb;
[ExcelRange("R-C")]
public RC rc
}
public class RB {}
public class RC {}
public class RD {}
public class RE {}
If you want to define range’s all information into Metadata to replace template range configuration file, the following is sample of data model.
[ExcelRange("SHEET1", "A1", "Z51", "RowRangeService")]
public class RT
{
[ExcelRange("R-A", "B3", "S31", "BlockRangeService")]
public RA ra;
[ExcelRange("R-D", "U2", "Y49", "BlockRangeService")]
public RD rd;
[ExcelRange("R-E", "B37", "Y49", "BlockRangeService")]
public RE re;
}
public class RA{
[ExcelRange("R-B", "C9", "Q16", BlockRangeService")]
public RB rb;
[ExcelRange("R-C", "B20", "Q29", "BlockRangeService")]
public RC rc
}
public class RB {}
public class RC {}
public class RD {}
public class RE {}
3.5.2.1. Template range example 1
Next let us see how to determine your template range by some examples.
First, see the following most simple example. We just want to replace the variables between row 8 and row 10, so the worksheet has only one root range from row 8 to row 10.
In order to tell ExcelTDF the range’s information , we write these information into a Spring.NET object xml file as following.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="RowRangeServiceTest_sheet1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT"/> <property name="Top" value="8"/> <property name="Bottom" value="10"/> <property name="ServiceId" value="RowRangeService"/> </object> </objects>
On the above, in configuration file, the type of object is always JanaBiz.OpenXml.TemplateRange. TemplateRange has some properties must be specified. These properties are RangeName, ServiceId and range defined by (Top|Left|LeftTop) and (Bottom|Right|RightBottom). If we want to specify range by row index without column limitation, the Top and Bottom property should be set. If we want to specify range by column index without limitation of row index, the Left and Right property should be set. If we want to specify rectangle range with the limitation of row and column, the property LeftTop and RightBottom should be set.
RangeName: the identifier of the range
Top/Bottom | Left/Right | LeftTop/RightBottom : the range specified by row or column or cell name
ServiceId: the service name used to process this range. see service names
The object id can be any string but it must be unique in all configuration files, For the worksheet root range, it is best to assign object id as template id + “sheet” + worksheet index . This will be convenient to ExcelTDF to find worksheet root range object. About some other property please see TemplateRange class API.
For the template above, following is the processed result. Because the service is RowRangeService, it just replaces the variables in specified template range with the data in data model.
3.5.2.2. Template range example 2
Following is another template file. In this template file we want to output data as a list. The list’s element is from row 8 to row 10, let us see how different result will be got.
The template file is shown as following.
The template configuration file is shown as following.
<?xml version="1.0" encoding="shift_jis"?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="RowListRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT"/> <property name="Top" value="1"/> <property name="Bottom" value="30"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="RLRSChildList1"/> </list> </property> </object> <object id="RLRSChildList1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="CHILDLIST1"/> <property name="LeftTop" value="L8"/> <property name="RightBottom" value="BN10"/> <property name="ServiceId" value="RowListRangeService"/> </object> </objects>
There are two TemplateRanges in worksheet. The worksheet root range, whose RangeName is ROOT and it has a child range. Root range is from row 8 to row 22. Child range’s id is RLRSChildList1 and RangeName is CHILDLIST1. Child range is from L8 to BE10. Child range is processed by service of RowListRangeService which output data as list with the same format as defined range L8:BE10, Assume your list in the data model has 5 elements, the data of 5 elements will be outputted from L8 to L20.
The following is processed result.
If we change the ROOT range’s row from row 7 to row 20, what will be changed? The variable of ${HouseNumber1} is in the range of ROOT, this variable will be processed. ROOT range’s ServiceId is RowRangeService, the variable is just replaced by value in data model corresponding to the ROOT range.
3.5.2.3. Template range example 3
Next, let us see a some complicated examples. In this example we want to output a list, and the element of list is also a list. We usually bump into this case. As the shown, template design is very simple too. The outer list’s element is from B5 to BJ11, the inner list’s element is from C8 to BE10. So the variable of $N{HouseNumber1}, #C{S1=SUM(HBⅡBody)}, #C{=SUM(S10)} etc. are processed by outer list. Inner list size might be different, the service of specified will process them correctly.
As the diagram of following, the worksheet has three ranges. Root range of worksheet is from row 1 to 18, which is processed by RowRangeService. The root range has a child range whose id is CRCRS_PARENTLIST1, named PARENTLIST1 and colored with gray. Child range is from B5 to BJ11, which is processed by service of ComplexRowCloneRangeService. The service named with prefix of ComplexXX is used to process list ranges who’s element has child range. So the part of B5:BJ11 will be cloned list size-1 times. The cloned rows are inserted in the below of the last row BJ11, here it is row 12. Because the element of list includes a child range, whose id is CRCRS_CHILDLIST1 , this range is also a cloned list, the cloned rows is from C8 to BE10, The row of parent range will be changed dynamically. ExcelTDF control ranges processing order, first parent range, then child ranges. If child range is a coned list, parent range’s row or column will be changed automatically.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="ComplexRowCloneRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT"/> <property name="Top" value="1"/> <property name="Bottom" value="18"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CRCRS_PARENTLIST1"/> </list> </property> </object> <object id="CRCRS_PARENTLIST1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARENTLIST1"/> <property name="LeftTop" value="B5"/> <property name="RightBottom" value="BJ11"/> <property name="ServiceId" value="ComplexRowCloneRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CRCRS_CHILDLIST1"/> </list> </property> </object> <object id="CRCRS_CHILDLIST1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="CHILDLIST1"/> <property name="LeftTop" value="C8"/> <property name="RightBottom" value="BE10"/> <property name="ServiceId" value="RowCloneRangeService"/> </object> </objects>
The result is shown in the following. You can see, the simple template creates complicated data output. There are many patterns you can assemble by provided services in ExcelTDF.
3.5.3. Without template range file
If your template doesn’t need to be changed frequently, maybe you don’t want to create template range configuration file. In this case the template range information can be written in code as metadata of program. If you write range information into code, and your template layout is changed, you must modify your code. The most benefit of using template range configuration file is you don’t have to change your code if template layout changed. For the detail, please see next chapter about data provider.
3.6. Data Provider and Data Model
ExcelTDF uses two types of interface to control the mapping information. IDataProviderManager controls mapping behavour of IRangeDataProvider and template range, while IRangeDataProvider controls mapping behavour of data field in data model and template variable. Data field maybe is field or property in data model, or columns of SQL query, or some others.
3.6.1 The types of data provider manager
Each implementation class of IDataProviderManager knows how to map data provider to template range. Actually manager maps RangeData in IRangeDataProvider to template range. RangeData can be data model, DataSet or other data objects. ExcelTDF provides three manager classes.
3.6.1.1. DataModelProviderManager
DataModelProviderManager, in which the default DataModelRangeDataProvider is used, maps data model to template range. ExcelRange and ExcelAttibute(Excel) Attribute classes are used to help ExcelTDF doing those works. The RangeData in DataModelRangeDataProvider is your data model for DataModelProviderManager. In the next you will see how to use them in data model.
3.6.1.2. QueryProviderManager
QueryProviderManager let you create document or report by specified SQL query, all template ranges use the same one query data if you use QueryProviderManager. If you are familiar with CrystalReport, creating a query and getting report, this is the similarity functionality.
3.6.1.3. AnyTypeProviderManager
AnyTypeProviderManager let you create document or report by any data type. You should create IRangeDataProvider for each template range. If no IRangeDataProvider found, the IRangeDataProvider of parent range will be used. You can specify a data model for a range, SQL query for another range. You can specify SQL query for each range and so on. It is the most flexible manager.
See the following diagram, it shows the association between template and data.
As you see, ExcelTDF provides various IRangeDataProvider implementation classes, from data mode to usually used database, even SAP. IRangeDataProvider has two methods ReadRangeData() for getting data from database or background service, and WriteRangeData() for saving edited data to database or sending to background service. ExcelTDF maps RangeData in IRangeDataProvider to template range. ExcelTDF provides three IDataProviderManager implementation classes, they are DataModelProviderManager, QueryProviderManager and AnyTypeProviderManager. The three data provider managers have the different control behavior and are used in different way. The DataModelProviderManager is the most often used data provider manager. If the RangeData in IRangeDataProvider is data model, not a SQL DataSet and not SAP’s DataReader, DataModelProviderManager is your suitable selection.
3.6.2 Data model of Template range example 1
In the following, let us see how to use DataModelProviderManager and what your data model should be.
For the Template range example-1 which we discussed on the above, data model is shown in the diagram. Please see the fields that have defined Excel metadata, for example [Excel("HBⅡBody")], it will map the value of HBⅡBody to the template variable defined as ${HBⅡBody}. Every template variable must have corresponding metadata with the same name in data model. If [Excel("X")] is defined in data model, but no ${X} or #{X=..} or %{X=..} found, “X” can be used in formula expression and custom service expression. For example, #{VAR2=SUM(VAR1*X/100)}, X is not a variable in template but a variable in data model. In the same way, the metadata [ExcelRange("ROOT")] tells ExcelTDF that the range information named ROOT in the template range configuration is used for this model, and variables defined in this model by metadata are mapped to template variables in the range named ROOT.
using System; using System.Xml.Serialization; using JanaBiz.OpenXml; namespace OpenXmlDemoData.Data { [ExcelRange("ROOT")] [Serializable] public class RowRangeServiceData1 { public string HouseType; [Excel("HouseNumber1")] public string HouseNum = "20"; [Excel("HBⅡBody")] public double HBⅡBody; [Excel("VAR2")] public string Var2; [Excel("LBⅡBody")] public string LBⅡBody; [Excel("KCBody")] public string KCBody; [Excel("SHⅡBody")] public string SHⅢBody; ..... } }
Here is the code to parse template and get your document. As you see, after you finished your template design and wrote well your template configuration, the coding is very simple.
using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Text; using JanaBiz.OpenXml.Handler; using JanaBiz.OpenXml.Config; using JanaBiz.OpenXml.Data; using OpenXmlDemoData; namespace TemplateConvertDemo { class Program { static void Main(string[] args) { //create your handler IOpenXmlHandler handler = new OpenXmlHandler(); //tell ExcelTDF which ITemplateService is used to //get template file and template range configuration handler.GetTemplateService<LocalTemplateService>(); //tell ExcelTDF which IDataProviderManager and //what data model is used to data handler.DataProviderManager = new DataModelProviderManager( new OpenXmlDemoData.Data.RowRangeServiceData1()); //set template id to identify which template is used handler.TemplateId = "RowRangeServiceTest"; //parse your template handler.ParseTemplate(); //parse your data handler.ParseWorkbook(null); //get parsed document byte[] doc = handler.GetParsedDocument(); //release some resource handler.Close(); } } }
In the code, creating DataModelProviderManager with parameter of RowRangeServiceData1 will enable manager use default data provider of DataModelRangeDataProvider. In fact, DataModelProviderManager have two constructors with parameter of either data model or IRangeDataProvider when you want use your customized IRangeDataProvider.
public DataModelProviderManager(Object dataModel) public DataModelProviderManager(IRangeDataProvider rootRangeDataProvider)
If you want to open parsed workbook into excel in your excel AddIn program directly, ExcelHandler is prepared for you.
The code is shown as the following. The step of coding is the same as the above example. Here we create and initialize handler first, and get template file and template range configuration by web service of TemplateServiceSoapClient. So your web service should perform the methods of interface ITemplateService, but your service don’t have to implement the interface of ITemplateService. This will be helpful for you to reference web service directly.
private ExcelHandler handler = null; private TemplateServiceSoapClient webService = null; public RibbonDemo() { InitializeComponent(); } private void RibbonDemo_Load(object sender, RibbonUIEventArgs e) { InitializeExcelHandler(); } /// <summary> /// Create and initialize <see cref="IExcelHandler"/> /// </summary> private void InitializeExcelHandler() { //create IExcelHandler instance handler = new ExcelHandler(); // set excel application handler.ExcelApp = Globals.ThisAddIn.Application; //create or get template service webService = handler.GetTemplateService<TemplateServiceSoapClient>(); //create data service DataService dataService = new DataService(); //get excel data event handler handler.OnGetDocumentData += dataService.GetDocumentData; //save excel data event handler handler.OnSaveDocumentData += dataService.SaveDocumentData; }
On the above, we create DataService object which performs OnGetDocumentData event handler for getting data model. Certainly you can assign DataModelProviderManager to handler directly too, such as
handler.DataProviderManager = new DataModelProviderManager( new OpenXmlDemoData.Data.RowRangeServiceData1());
Define a event handler is very flexible. First, coding logic to get data for all templates can be put in event handler method. Second, you can override IRangeDataProvider, and get data in your way. The event handler method is show in the below.
/// <summary> /// Assign a data provider manager or data range provider to a template for specified template id /// </summary> /// <param name="handler"></param> /// <param name="sheetIndex"></param> public void GetDocumentData(IOpenXmlHandler handler, int sheetIndex) { switch (handler.TemplateId) { case "RowRangeServiceTest": switch (sheetIndex) { case 0: // you can get from web service etc., for simple, here just new a data model handler.DataProviderManager = new DataModelProviderManager( new RowRangeServiceData1()); break; } break; } }
Let us see the code about opening parsed document, only several lines. Open() method try to find data provider manager and data provider by event handler of OnGetDocumentData. If event handler exists, it will be called.
/// <summary> /// Open parsed workbook of spreadsheet /// </summary> /// <param name="templateId"></param> private void OpenDocument(string templateId) { //close opened workbook before handler.Close(); //template id used handler.TemplateId = templateId; //other input parameter for get excel data handler.InputData = null; //parse template handler.ParseTemplate(); //parse workbook and open it in excel or other component handler.Open(); }
3.6.3 Data model of Template range example 2
In this example because the root template range has a child range, the data model is also created into two classes hierarchically. RowListRangeServiceData class has metadata [ExcelRange("ROOT")] and its field member ChildList1 has metadata [ExcelRange("CHILDLIST1")]. The names of ROOT and CHILDLIST1 matches the RangeName defined in template range configuration. In template range configuration, the ServiceId of CHILDLIST1 is RowListRangeService, so in the data model of RowListRangeServiceData, the member with metadata [ExcelRange("CHILDLIST1")], ChildList1 must be a list.
ExcelRange binding a data model is associated to template range, and if your template range ServiceId is list related service, data member should be a list type of IList. That is all.
using System; using System.Collections.Generic; using JanaBiz.OpenXml; using JanaBiz.Common.Serialize; namespace OpenXmlDemoData.Data { [Serializable] [ExcelRange("ROOT")] public class RowListRangeServiceData { public RowListRangeServiceData() { //Create data for demo for (int i = 0; i < 5; i++) { ChildList1.Add(new RowRangeServiceData1()); } } [ExcelRange("CHILDLIST1")] public IList<RowRangeServiceData1> ChildList1 = new List<RowRangeServiceData1>(); } }
In OnGetDocumentData event handler we added a piece of coding for getting data model. other works are the same as example-1
/// <summary> /// Assign a data provider manager or data range provider to a template for specified template id /// </summary> /// <param name="handler"></param> /// <param name="sheetIndex"></param> public void GetDocumentData(IOpenXmlHandler handler, int sheetIndex) { switch (handler.TemplateId) { case "RowRangeServiceTest": switch (sheetIndex) { case 0: // you can get from web service etc., for simple, here just new a data model handler.DataProviderManager = new DataModelProviderManager( new RowRangeServiceData1()); break; } break; case "RowListRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new RowListRangeServiceData()); break; } break; } }
3.6.4 Data model of Template range example 3
In this example, the template range has a list, and list’s element is also a list. Let us see the data model how to construct.
As you have seen, entry class ComplexRowCloneRangeServiceData has metadata ExcelRange(“ROOT”), its member MasterDoorList1 has metadata [ExcelRange("PARENTLIST1")], MasterDoorList1 is a list. MasterDoorList1′s element is RoomTypeList1 which has a member MasterDoorChildList, and MasterDoorChildList has metadata [ExcelRange("CHILDLIST1")], it is also a list. In the OnGetDocumentData event handler we added code to get data provider manager. For the others no need to change.
using System; using System.Collections.Generic; using JanaBiz.OpenXml; namespace OpenXmlDemoData.Data { [ExcelRange("ROOT")] public class ComplexRowCloneRangeServiceData { public ComplexRowCloneRangeServiceData() { CreateDataForDemo(); } [ExcelRange("PARENTLIST1")] public IList<RoomTypeList1> MasterDoorList1 = new List<RoomTypeList1>(); } }
using System; using System.Collections.Generic; using System.Linq; using JanaBiz.OpenXml; namespace OpenXmlDemoData.Data { /// <summary> /// 戸当りマスタ登録・変更画面の概要説明です。 /// </summary> public class RoomTypeList1 { public RoomTypeList1() { CreateDataForDemo(); } public RoomTypeList1(string houseNumber) { CreateDataForDemo2(houseNumber); } [Excel("部屋数1")] public string HouseNumber="12"; [ExcelRange("CHILDLIST1")] public IList<RowRangeServiceData1> MasterDoorChildList = new List<RowRangeServiceData1>(); } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using System.IO; using System.Reflection; using JanaBiz.OpenXml; using JanaBiz.OpenXml.Handler; using JanaBiz.OpenXml.Data; using JanaBiz.OpenXml.Config; using OpenXmlDemoData.DataProvider; using OpenXmlDemoData.Data; namespace OpenXmlDemoData { public class DataService { /// <summary> /// Assign a data provider manager or data range provider to a template for specified template id /// </summary> /// <param name="handler"></param> /// <param name="sheetIndex"></param> public void GetDocumentData(IOpenXmlHandler handler, int sheetIndex) { switch (handler.TemplateId) { case "RowRangeServiceTest": switch (sheetIndex) { case 0: // you can get from web service etc., for simple, here just new a data model handler.DataProviderManager = new DataModelProviderManager( new RowRangeServiceData1()); break; } break; case "RowListRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new RowListRangeServiceData()); break; } break; case "ComplexRowCloneRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new ComplexRowCloneRangeServiceData()); break; } break; } }
3.6.5. How to create your class of IRangeDataProvider
If you need to search data from database, web service or other services in background, you might want to create your own implementation class of IRangeDataProvider. If your searched result is designed as data model demonstrated in the example above, the most convenient way is to create a sub class of DataModelRangeDataProvider. DataModelRangeDataProvider performs interface methods of IRangeDataProvider, but ReadRangeData() and WriteRangeData() do nothing. You can override these two methods in your way. When you open or parse document, firstly, IDataProviderManager calls ReadRangeData() to get data, then IDataProviderManager parses gotten data. When you save edited document by calling ExcelHandler or OpenXmlHandler’s Save() method, firstly, the Save() method will call Flush() method to save edited data into mapped data model of RangeData then call WriteRangeData() to save RangeData to database or other services.
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.IO; using System.Reflection; using JanaBiz.Common.Serialize; using JanaBiz.OpenXml.Data; using OpenXmlDemoData.Data.Boat; namespace OpenXmlDemoData.DataProvider { /// <summary> /// /// This is demo for getting data from database or background service. /// Here we get data from xml resource file. /// </summary> public class ColumnDataProvider : DataModelRangeDataProvider { public ColumnDataProvider() : base() { } /// <summary> /// Here you can call database service, web service of other background service to /// read data, the last step is assign the result data to RangeData. /// </summary> public override void ReadRangeData() { //here, read from xml file Assembly assembly = Assembly.GetExecutingAssembly(); string assemblyNamespace = assembly.FullName.Split(new char[]{','})[0]; string xmlfile = assemblyNamespace + ".Resources.ColumnRangeTestData.xml"; Stream stream = assembly.GetManifestResourceStream(xmlfile); BoatRace boatRace = Serializer.ReadModel<BoatRace>(stream); //this must be done this.RangeData = boatRace; } /// <summary> /// You can write your saving data in your way. /// Here we just write the data into a xml file /// </summary> /// <returns>return true, if saved data successfully, otherwise false</returns> public override bool WriteRangeData() { Serializer.WriteToXml(this.RangeData, "c:\\ColumnListRangeTestData.xml"); return true; } } }
In the example on the above, for simply demo, ColumnDataProvider just get data from a serialized xml resource file and write edited data into xml resource file. Next code is show how to assign the data provider to IDataProviderManager.
/// <summary> /// Assign a data provider manager or data range provider to a template for specified template id /// </summary> /// <param name="handler"></param> /// <param name="sheetIndex"></param> public void GetDocumentData(IOpenXmlHandler handler, int sheetIndex) { switch (handler.TemplateId) { case "RowRangeServiceTest": switch (sheetIndex) { case 0: // you can get from web service etc., for simple, here just new a data model handler.DataProviderManager = new DataModelProviderManager( new RowRangeServiceData1()); break; } break; case "ColumnListRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new ColumnDataProvider()); break; } break; case "ColumnCloneRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new ColumnDataProvider()); break; case 1: handler.DataProviderManager = new DataModelProviderManager( new ColumnDataProvider()); break; } break; case "ComplexColumnCloneRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new ComplexColumnDataProvider()); break; } break; case "ComplexColumnListRangeServiceTest": switch (sheetIndex) { case 0: handler.DataProviderManager = new DataModelProviderManager( new ComplexColumnDataProvider()); break; } break; } }
3.6.6. Use Metadata without template range configuration
If you don’t want to create template configuration file, you can write necessary information into ExcelRange metadata as the following.
using System; using System.Collections.Generic; using System.Linq; using System.Text; using JanaBiz.OpenXml; using JanaBiz.Common.Serialize; namespace OpenXmlDemoData.Data.Boat { [Serializable] [ExcelRange("ROOT", "B5", "EQ10", "RowRangeService")] public class BoatRace { public BoatRace() { } [ExcelRange("HEADER", "CX4", "DF48", "BlockRangeService")] public BoatRaceHeader HeaderData = new BoatRaceHeader(); [ExcelRange("LIST", "BZ4", "CK48", "ColumnListRangeServiceRL")] public SerializableList<BoatRaceRecord> itemDataList = new SerializableList<BoatRaceRecord>(); [ExcelRange("RESULT", "H4", "Q48", "BlockRangeService")] public BoatRaceResult ResultData = new BoatRaceResult(); } }
Then set parameter of method ParseTemplate(params bool[] usingConfig) to false, handler.ParseTemplate(false);
using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Text; using JanaBiz.OpenXml.Handler; using JanaBiz.OpenXml.Config; using JanaBiz.OpenXml.Data; using OpenXmlDemoData; namespace TemplateConvertDemo { class Program { static void Main(string[] args) { //create your handler IOpenXmlHandler handler = new OpenXmlHandler(); //tell ExcelTDF which ITemplateService is used to //get template file and template range configuration handler.GetTemplateService<LocalTemplateService>(); //tell ExcelTDF which IDataProviderManager and //what data model is used to data handler.DataProviderManager = new DataModelProviderManager( new OpenXmlDemoData.Data.RowRangeServiceData1()); //set template id to identify which template is used handler.TemplateId = "RowRangeServiceTest"; //parse your template handler.ParseTemplate(false); //parse your data handler.ParseWorkbook(null); //get parsed document byte[] doc = handler.GetParsedDocument(); //release some resource handler.Close(); } } }
4. Service
ExcelTDF provides many range service to process template range as what you want to. Range service are the implementation class of IExcelRangeService and set in template range configuration file. Simultaneously ExcelTDF provides some custom cell services and function services. As explained on the chapter of Custom service variable, cell services and function services has the writing format as the same as Excel formula. Custom cell services are used to perform special processing like barcode, page break and so on, there are no result returned. Function service does some special processing and returns function result as string. Simply, range service is used to process template range, custom service is used to process template cells.
4.1. IExcelRangeService
Default range service is configured in Spring.NET objects file included in ExcelTDF package as resource file. Service name which is called ServiceId in template range is object id that is upper case and lower case sensitive.
<object id="RowRangeService" type="JanaBiz.OpenXml.Service.RowRangeService, JanaBiz.OpenXml" singleton="false"/>
OpenXmlContext class provides some static methods to get ids of different services. You can get all range service ids by
OpenXmlContext.GetRangeServiceIds();
ExcelTDF full versions provides following range services.
. RowRangeService
. RowListRangeService
. RowCloneRangeService
. ComplexRowListRangeService
. ComplexRowCloneRangeService
. BlockRangeService
. BlockRowListRangeService
. ComplexBlockRowListRangeService
. ColumnListRangeService
. ColumnListRangeServiceRL
. ColumnCloneRangeService
. ColumnCloneRangeServiceRL
. ComplexColumnListRangeService
. ComplexColumnListRangeServiceRL
. ComplexColumnCloneRangeService
. ComplexColumnCloneRangeServiceRL
. MultiplePageRangeService
. BlockRowCloneRangeService
. BlockColumnCloneRangeService
4.1.1. RowRangeService
RowRangeService is the simplest service whose range is specified by top row index and bottom row index. RowRangeService replaces the variables in template range with data value in RangeData of IRangeDataProvider. If you specify range by cell name of LeftTop and RightBottom, the variables outside the specified column will not be processed.
The template range specified with this service is able to have child ranges. Generally, the worksheet’s root range is usually specified by this service.
4.1.2. RowListRangeService
RowListRangeService is used to show a data list whose first element is specified by top row index and bottom row index. RowListRangeService requires you design well-formatted rows for all element in list. The template variable between top row and bottom row will be repeatedly processed until the last element of list. If you specify range by cell name of LeftTop and RightBottom, the variables outside the specified column will not be processed. The range specified service as RowListRangeService can not have child ranges (ExcelTDF not process any child ranges even child range is specified in template range configuration file). see ComplexRowListRangeService
In template, one element of list maybe have several excel rows as a logical unit. For example, if you specified template range from top row 8 to bottom row 10, your logical unit is (10-8+1)=3 rows. Assume your data list size is 5, all data will be outputted from row 8 to row 8+3*5-1=22. If you specify the StartIndex and PageSize in template range configuration, the elements from StartIndex to StartIndex+PageSize will be outputted. If StartIndex + PageSize is greater than the size of list, service outputs to the last element of list. The format of all output logical rows must be the same as the first element rows except that the template variable is only written in the first element rows. The best way is that you copy all cells in the first element several times until your list’s max size. Make sure cells exist in worksheet. At sometime the cell without text (blank cell ) might not be created in worksheet by Open XML even the cells is shown. See the following diagram of RowListRangeService example. In template range configuration first element is defined from row index is from 8 to 10. list has max size 5.
4.1.3. RowCloneRangeService
RowCloneRangeService is one of usually used service. RowCloneRangeService is used to show data list too. The different from RowListRangeService is RowCloneRangeService don’t have to set format for each elements except of first element rows. RowCloneRangeService clones cells in first element rows and add them in the below. This is very useful, you don’t worry about the list size and are able to design template simply. After cloned row are added into document, the row index of other range and the cell name of formula will be changed, ExcelTDF will recalculate row index about them again. The RowCloneRangeService can not have child ranges as the same as RowListRangeService.
The following is an example. At the bottom of template, there are a Excel formula of =SUM(E21:L25) and a pull down cell. After template is processed, 99 logical unit rows are cloned. The logical row size is 3, so the number of changed rows is 99*3=297. Let us see the formula in the result diagram, SUM(E21:L25) is changed to SUM(E318:L324) and the pull down cell also is moved from row 21 to row 318 too. If RowCloneRangeService is used, the row index of objects on the below of RowCloneRangeService range will be changed automatically, you don’t have to do any work to correct row index, ExcelTDF does all for you.
The result processed by RowCloneRangeService is shown in the following.
4.1.4. ComplexRowListRangeService
ComplexRowListRangeService is a list service like RowListRangeService. The difference between ComplexRowListRangeService and RowListRangeService is that the element of ComplexRowListRangeService’s list data is able to include child ranges and child range is able to be any range type. RowListRangeService’s element is not able to include child ranges. Because ComplexRowListRangeService is also a list service, you must design the format for all elements in list as the same way as RowListRangeService. See the following template file, template range configuration and data model. The first element of ComplexRowListRangeService is from C4 to BJ22 and named as PARENTLIST1, other elements are well-designed as the same as the first element. In the element there is a RowListRangeService which is from C8 to BE10 and named as CHILDLIST1. CHILDLIST1′s maximum data size is 5 in first element of PARENTLIST1.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="ComplexRowListRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT"/> <property name="LeftTop" value="A1"/> <property name="RightBottom" value="CK275"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CRLRS_PARENTLIST1"/> </list> </property> </object> <object id="CRLRS_PARENTLIST1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARENTLIST1"/> <property name="LeftTop" value="C4"/> <property name="RightBottom" value="BJ22"/> <property name="ServiceId" value="ComplexRowListRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CRLRS_CHILDLIST1"/> </list> </property> </object> <object id="CRLRS_CHILDLIST1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="CHILDLIST1"/> <property name="LeftTop" value="C8"/> <property name="RightBottom" value="BE10"/> <property name="ServiceId" value="RowListRangeService"/> </object> </objects>
using System; using JanaBiz.OpenXml; using JanaBiz.Common.Serialize; namespace OpenXmlDemoData.Data { [ExcelRange("ROOT")] public class ComplexRowListRangeServiceData { [ExcelRange("PARENTLIST1")] public SerializableList<RoomTypeList1> MasterDoorList1 = new SerializableList<RoomTypeList1>(); } }
using System; using System.Collections.Generic; using System.Linq; using JanaBiz.OpenXml; namespace OpenXmlDemoData.Data { /// <summary> /// /// </summary> public class RoomTypeList1 { /// <summary> /// variable /// </summary> [Excel("部屋数1")] public string HouseNumber="12"; /// <summary> /// Child list range /// </summary> [ExcelRange("CHILDLIST1")] public IList<RowRangeServiceData1> MasterDoorChildList = new List<RowRangeServiceData1>(); } }
4.1.5. ComplexRowCloneRangeService
ComplexRowCloneRangeService is a list clone service like RowCloneRangeService. The difference between ComplexRowCloneRangeService and RowCloneRangeService is that the element of ComplexRowCloneRangeService’s list data is able to include child ranges and child range is able to be any range type. RowCloneRangeService’s element is not able to include child ranges. See the following template file, template range configuration and data model. Using ComplexRowCloneRangeService, your template design becomes very simple. As the same as service of RowCloneRangeService, you don’t have to copy the format for each element rows in the list. Following example is explained on the above of Template range example 3.
Let’s see more detail. ComplexRowCloneRangeService processes the range named PARENTLIST1 from B5 to BJ11, which has a RowCloneRangeService range from C8 to BE10 with color of green named CHILDLIST1.
The cell AA11′s template variable expression is #C{S4=SUM(SHⅡ本体)}, it is in the range of PARENTLIST1. The variable name is S4 and referenced variable is SHⅡ本体 which is in the child range of CHILDLIST1. In the first element of PARENTLIST1, the child list has 5 elements, so the variable cells of SHⅡ本体 is AA8,AA11,AA14,AA17,AA20 and the result of formula is =SUM(AA8,AA11,AA14,AA17,AA20). Please see the other variables on the template S1, S1, S2..S10,S11, their behavior are the same as S4.
The cell C11 in the range of PARENTLIST1 is normal formula, you can see it is become =SUM(L23:BE23), because row index is changed. It is the sum of S1..S11.
See the cells of AZ15, BE15, they are the variables expression #C{=SUM(S10)} and #C{=SUM(NWS本体)}. These two variables are in the range of ROOT. See the result on the diagram and what formula they have been changed to.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="ComplexRowCloneRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT"/> <property name="Top" value="1"/> <property name="Bottom" value="18"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CRCRS_PARENTLIST1"/> </list> </property> </object> <object id="CRCRS_PARENTLIST1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARENTLIST1"/> <property name="LeftTop" value="B5"/> <property name="RightBottom" value="BJ11"/> <property name="ServiceId" value="ComplexRowCloneRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CRCRS_CHILDLIST1"/> </list> </property> </object> <object id="CRCRS_CHILDLIST1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="CHILDLIST1"/> <property name="LeftTop" value="C8"/> <property name="RightBottom" value="BE10"/> <property name="ServiceId" value="RowCloneRangeService"/> </object> </objects>
using System; using System.Collections.Generic; using JanaBiz.OpenXml; namespace OpenXmlDemoData.Data { [ExcelRange("ROOT")] public class ComplexRowCloneRangeServiceData { public ComplexRowCloneRangeServiceData() { CreateDataForDemo(); } [ExcelRange("PARENTLIST1")] public IList<RoomTypeList1> MasterDoorList1 = new List<RoomTypeList1>(); } }
using System; using System.Collections.Generic; using System.Linq; using JanaBiz.OpenXml; namespace OpenXmlDemoData.Data { /// <summary> /// /// </summary> public class RoomTypeList1 { /// <summary> /// variable /// </summary> [Excel("部屋数1")] public string HouseNumber="12"; /// <summary> /// Child list range /// </summary> [ExcelRange("CHILDLIST1")] public IList<RowRangeServiceData1> MasterDoorChildList = new List<RowRangeServiceData1>(); } }
4.1.6. BlockRangeService
BlockRangeService is as the same as RowRangeService except that the range is specified by cell name. It is a service to process a rectangle region. If you only want to process some special region by service, BlockRangeService is very useful. You can split your complicated template into some simple region to process one by one. The range of RowRangeService is able to be specified by cell name too. In this case RowRangeService has the same action as BlockRangeService. About more detail see RowRangeService.
4.1.7. BlockRowListRangeService
BlockRowListRangeService is the same as RowListRangeService except that the range is specified by cell name. It is also a service to process a rectangle region, but in the region, data is output as list by rows. If the range of RowListRangeService is specified by cell name, RowListRangeService has the same action as BlockRowListRangeService. About more detail, see RowListRangeService.
4.1.8. ComplexBlockRowListRangeService
CpmplexBlockRowListRangeService is a list service whose range is defined by cell name and is able to have child ranges in the list elements. The behavior is like ComplexRowListRangeService. About more detail, see ComplexRowListRangeService.
4.1.9-10. ColumnListRangeService and ColumnListRangeServiceRL
ColumnListRangeService is similar to the RowListRangeService but the range is specified by column index. ColumnListRangeService outputs data from left to right as list. The first element is outputted on the left side of the range. In the opposite, the ColumnListRangeServiceRL outputs data from right to the left. The first element is outputted on the right side of the range. As the same as service of RowListRangeService, you must make sure all element’s columns have the same format. See the diagram following, the gray region is processed by ColumnListRangeServiceRL.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net"> <object id="ColumnListRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT" /> <property name="LeftTop" value="B2" /> <property name="RightBottom" value="EQ50" /> <property name="ServiceId" value="RowRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ColumnListRangeServiceTest_1_HEADER" /> <ref object="ColumnListRangeServiceTest_1_LIST" /> <ref object="ColumnListRangeServiceTest_1_RESULT" /> </list> </property> </object> <object id="ColumnListRangeServiceTest_1_HEADER" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="HEADER" /> <property name="LeftTop" value="CX4" /> <property name="RightBottom" value="DF48" /> <property name="ServiceId" value="BlockRangeService" /> </object> <object id="ColumnListRangeServiceTest_1_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="LIST" /> <property name="LeftTop" value="BZ4" /> <property name="RightBottom" value="CK48" /> <property name="ServiceId" value="ColumnListRangeServiceRL" /> </object> <object id="ColumnListRangeServiceTest_1_RESULT" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="RESULT" /> <property name="LeftTop" value="H4" /> <property name="RightBottom" value="Q48" /> <property name="ServiceId" value="BlockRangeService" /> </object> </objects>
using System; using System.Collections.Generic; using System.Linq; using System.Text; using JanaBiz.OpenXml; using JanaBiz.Common.Serialize; namespace OpenXmlDemoData.Data.Boat { [Serializable] [ExcelRange("ROOT")] public class BoatRace { public BoatRace() { } [ExcelRange("HEADER")] public BoatRaceHeader HeaderData = new BoatRaceHeader(); [ExcelRange("LIST")] public SerializableList<BoatRaceRecord> itemDataList = new SerializableList<BoatRaceRecord>(); [ExcelRange("RESULT")] public BoatRaceResult ResultData = new BoatRaceResult(); } }
4.1.11-12. ColumnCloneRangeService and ColumnCloneRangeServiceRL
ColumnCloneRangeService is similar to the RowCloneRangeService but the range is specified by column index. ColumnCloneRangeService clones specified columns and add cloned columns one by one from left to right. In the opposite, the ColumnCloneRangeServiceRL clones specified columns and add cloned columns one by one from right to left like the Japanese newspaper. Using ColumnCloneRangeService you don’t have to copy all elements format as ColumnListRangeService. After processed, column index of some ranges, formulas, objects will be recalculated and moved automatically.
The result is shown in the following.
4.1.13-14. ComplexColumnListRangeService and ComplexColumnListRangeServiceRL
ComplexColumnListRangeService is a list service like ColumnListRangeService. The difference between ComplexColumnListRangeService and ColumnListRangeService is that the element of ComplexColumnListRangeService’s list data is able to include child ranges and child range is able to be any range type. ColumnListRangeService’s element is not able to include child ranges. Because ComplexColumnListRangeService is also a list service, you must design the format for all elements in list as the same way as ColumnListRangeService. If in the element of ComplexColumnListRangeService there is a child range using ColumnListRangeService, make sure each element’s format is designed well too. See the following template file, template range configuration and data model.
Here ROOT range has two child ranges named TITLE and DATA. TITLE is a BlockRangeService and has one child range SIMAI which is processed by service BlockRowListRangeService. Let us see DATA range in detail. DATA range is processed by ComplexColumnListRangeService which has four child ranges; those of them are processed by BlockRowListRangeService. To understand how it works, please check the each child range’s region. ComplexColumnListRangeService adds the data from left to right. In the contrary, ComplexColumnListRangeServiceRL processes the data from right to left.
The following is the part of template.
Here is the template range configuration file.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net"> <object id="ComplexColumnListRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT" /> <property name="LeftTop" value="A1" /> <property name="RightBottom" value="IR147" /> <property name="ServiceId" value="RowRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ComplexColumnListRangeServiceTest_1_ROOT_TITLE" /> <ref object="ComplexColumnListRangeServiceTest_1_ROOT_DATA" /> </list> </property> </object> <object id="ComplexColumnListRangeServiceTest_1_ROOT_TITLE" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="TITLE" /> <property name="LeftTop" value="B2" /> <property name="RightBottom" value="K147" /> <property name="ServiceId" value="BlockRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ComplexColumnListRangeServiceTest_1_TITLE_SIMAI" /> </list> </property> </object> <object id="ComplexColumnListRangeServiceTest_1_TITLE_SIMAI" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="SIMAI" /> <property name="LeftTop" value="B32" /> <property name="RightBottom" value="B46" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="ComplexColumnListRangeServiceTest_1_ROOT_DATA" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="DATA" /> <property name="LeftTop" value="L2" /> <property name="RightBottom" value="U147" /> <property name="ServiceId" value="ComplexColumnListRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ComplexColumnListRangeServiceTest_1_DATA_NAME_LIST" /> <ref object="ComplexColumnListRangeServiceTest_1_DATA_DA_LIST" /> <ref object="ComplexColumnListRangeServiceTest_1_DATA_BAJYU_LIST" /> <ref object="ComplexColumnListRangeServiceTest_1_DATA_DA2_LIST" /> </list> </property> </object> <object id="ComplexColumnListRangeServiceTest_1_DATA_NAME_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="NAME_LIST" /> <property name="LeftTop" value="L32" /> <property name="RightBottom" value="L33" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="ComplexColumnListRangeServiceTest_1_DATA_DA_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="DA_LIST" /> <property name="LeftTop" value="L58" /> <property name="RightBottom" value="S59" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="ComplexColumnListRangeServiceTest_1_DATA_BAJYU_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="BAJYU_LIST" /> <property name="LeftTop" value="L66" /> <property name="RightBottom" value="U81" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="ComplexColumnListRangeServiceTest_1_DATA_DA2_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="DA2_LIST" /> <property name="LeftTop" value="L120" /> <property name="RightBottom" value="T121" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> </objects>
The result processed by ComplexColumnListRangeService is shown in the following.
4.1.15-16. ComplexColumnCloneRangeService and ComplexColumnCloneRangeServiceRL
ComplexColumnCloneRangeService is a list clone service like ColumnCloneRangeService. The difference between ComplexColumnCloneRangeService and ColumnCloneRangeService is that the element of ComplexColumnCloneRangeService’s list data is able to include child ranges and child range is able to be any range type. ColumnCloneRangeService’s element is not able to include child ranges. See the following template file, template range configuration and data model. Using ComplexColumnCloneRangeService, your template design becomes very simple. As the same as service of ColumnCloneRangeService, you don’t have to copy the format for each element rows in the list. Here we use ComplexColumnCloneRangeServiceRL to process the range of DATA, the cloned columns are added into the left of range DATA from right to left. The range of DATA has four child ranges. The child range of BAJYU_LIST has three elements; the first element region is from B66 to K81. Please check the output result. The column index of formulas and other objects in the right of range DATA will be changed, ExcelTDF automatically searches these objects and changes the column index of them.
<?xml version="1.0" encoding="utf-8"?> <objects xmlns="http://www.springframework.net"> <object id="ComplexColumnCloneRangeServiceTest_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT" /> <property name="LeftTop" value="A1" /> <property name="RightBottom" value="Q148" /> <property name="ServiceId" value="RowRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CCCRST_1_TITLE" /> <ref object ="CCCRST_1_DATA"/> </list> </property> </object> <object id="CCCRST_1_TITLE" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="TITLE" /> <property name="LeftTop" value="L2" /> <property name="RightBottom" value="U147" /> <property name="ServiceId" value="BlockRangeService" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CCCRST_1_SIMAI" /> </list> </property> </object> <object id="CCCRST_1_SIMAI" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="SIMAI" /> <property name="LeftTop" value="L32" /> <property name="RightBottom" value="U33" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="CCCRST_1_DATA" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="DATA" /> <property name="LeftTop" value="B2" /> <property name="RightBottom" value="K147" /> <property name="ServiceId" value="ComplexColumnCloneRangeServiceRL" /> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="CCCRST_1_DA_LIST" /> <ref object="CCCRST_1_BAJYU_LIST" /> <ref object="CCCRST_1_DA2_LIST" /> <ref object="CCCRST_1_NAME_LIST" /> </list> </property> </object> <object id="CCCRST_1_DA_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="DA_LIST" /> <property name="LeftTop" value="C58" /> <property name="RightBottom" value="I59" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="CCCRST_1_BAJYU_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="BAJYU_LIST" /> <property name="LeftTop" value="B66" /> <property name="RightBottom" value="K81" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="CCCRST_1_DA2_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="DA2_LIST" /> <property name="LeftTop" value="B120" /> <property name="RightBottom" value="K121" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="CCCRST_1_NAME_LIST" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="NAME_LIST" /> <property name="LeftTop" value="B32" /> <property name="RightBottom" value="K33" /> <property name="ServiceId" value="BlockRowListRangeService" /> </object> <object id="CCCRST_SHEET2" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT" /> <property name="LeftTop" value="A1" /> <property name="RightBottom" value="BP41" /> <property name="ServiceId" value="RowRangeService" /> </object> <object id="CCCRST_SHEET3" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT" /> <property name="LeftTop" value="A1" /> <property name="RightBottom" value="BP41" /> <property name="ServiceId" value="RowRangeService" /> </object> </objects>
Following is the result document processed by ComplexCiolumnCloneRangeService.
4.1.17. MultiplePageRangeService
MultiplePageRangeService is created to process some complicated reports or excel documents which has multiple different page designs. Sometimes we want to create a report which has page header, page footer, report header and report footer. In ExcelTDF, you can considered them as some ranges. In fact, using RowRangeService or BlockRowRangeService, you can output header and footer information easily. If your document or report has only one page, you can do your design using services explained on the above. By assembling those services, ExcelTDF is able to output various documents and reports as what you want. If your report has more pages that all of them are the same design, you can use ComplexRowCloneRangeService or ComplexRowListRangeService to output your report page by page. In this case, a page is an element of list. MultiplePageRangeService provides ability for creating document and report which has different page design while a list data might be outputted across all pages. Usually we want to output some summary information into the first page or last page, the detail information page by page. Depending on the size of list data, the number of pages to be outputted will be variable. MultiplePageRangeService controls these behaviors according to the properties set in the template range configuration file. MultiplePageRangeService is able to process document and report which has two or three different page designs.
Let us see following template, which has two reports, owner sales report and parking sales report. In the template, owner sales report has three different pages and parking sales report has one page. We want to output owner report and parking report for one owner in one worksheet. One owner maybe has a lot of parking, we need to output parking name, parking sales amount as one line for each parking in the owner report and output parking sales amount detail data in the parking report.
Download template SalesReport
Parking sales report template page is omitted.
The following is template range configuration file, download SalesReport
In the worksheet, there are two child ranges, and they are owner report and parking report named OWNER_REPORT, PARKING_REPORT. Owner report range has three page child ranges, OWNER_PAGE1, OWNER_PAGE2, OWNER_PAGE3. For three page ranges, there is a list range of sales amount for each parking. Assume the owner has 56 parkings, the list size will be 56. In the template configuration, the PageSize of the range PARK_DATA_LIST in OWNER_PAGE1, OWNER_PAGE2, OWNER_PAGE3 is 15, so the owner report will be output 4 pages. The property of IsRepeat in internal page OWNER_PAGE2, which is set to true, means this page is able to output repeated. ExcelTDF controls output behavior based on those properties. PARK_DATA_LIST range is outputted across all pages, this behavior is controlled by property of IsCrosssPage. Besides owner report, each parking detail data is outputed as one page, thus in the worksheet we output owner report as 4 pages and parking report as 56 pages.
<?xml version="1.0" encoding="utf-8" ?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="SalesReport_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="SALES_REPORT"/> <property name="LeftTop" value="B1"/> <property name="RightBottom" value="BG209"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="OwnerReport"/> <ref object="ParkReport"/> </list> </property> </object> <object id="OwnerReport" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="OWNER_REPORT"/> <property name="LeftTop" value="B1"/> <property name="RightBottom" value="BG150"/> <property name="ServiceId" value="MultiplePageRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="OwnerPage1"/> <ref object="OwnerPage2"/> <ref object="OwnerPage3"/> </list> </property> </object> <object id="OwnerPage1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="OWNER_PAGE1"/> <property name="LeftTop" value="B1"/> <property name="RightBottom" value="BG50"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ParkData1"/> </list> </property> </object> <object id="ParkData1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARK_DATA_LIST"/> <property name="LeftTop" value="B24"/> <property name="RightBottom" value="BG24"/> <property name="ServiceId" value="RowListRangeService"/> <property name="PageSize" value="15"/> <property name="IsCrossPage" value="true"/> </object> <object id="OwnerPage2" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="OWNER_PAGE2"/> <property name="LeftTop" value="B51"/> <property name="RightBottom" value="AT100"/> <property name="ServiceId" value="ComplexRowCloneRangeService"/> <property name="IsRepeat" value="true"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ParkData2"/> </list> </property> </object> <object id="ParkData2" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARK_DATA_LIST"/> <property name="LeftTop" value="B74"/> <property name="RightBottom" value="AJ74"/> <property name="ServiceId" value="RowListRangeService"/> <property name="PageSize" value="15"/> <property name="IsCrossPage" value="true"/> </object> <object id="OwnerPage3" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="OWNER_PAGE3"/> <property name="LeftTop" value="B101"/> <property name="RightBottom" value="AT150"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="ParkData3"/> </list> </property> </object> <object id="ParkData3" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARK_DATA_LIST"/> <property name="LeftTop" value="B124"/> <property name="RightBottom" value="AJ124"/> <property name="ServiceId" value="RowListRangeService"/> <property name="PageSize" value="15"/> <property name="IsCrossPage" value="true"/> </object> <object id="ParkReport" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="PARK_REPORT"/> <property name="LeftTop" value="B151"/> <property name="RightBottom" value="AT209"/> <property name="ServiceId" value="ComplexRowCloneRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="AdditionItems"/> </list> </property> </object> <object id="AdditionItems" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ADDITIONS"/> <property name="LeftTop" value="B192"/> <property name="RightBottom" value="AJ192"/> <property name="ServiceId" value="BlockRowListRangeService"/> </object> </objects>
Download the result of report SalesReport result
The following is a part of data model.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using JanaBiz.OpenXml;
namespace OpenXmlDemoData.Data
{
/// <summary>
/// sales report
/// </summary>
[ExcelRange("SALES_REPORT")]
public class SalesReportData
{
#region constructor
public SalesReportData()
{
}
#endregion
/// <summary>
/// all parking data of owner
/// </summary>
[ExcelRange("OWNER_REPORT")]
public OwnerSalesReport ownerReport = new OwnerSalesReport();
/// <summary>
/// sales data of parking
/// </summary>
[ExcelRange("PARK_REPORT")]
public IList<OwnerParkSalesData> ParkList
{
get { return ownerReport.ParkList; }
}
}
}
4.1.18. BlockRowCloneRangeService
BlockRowCloneRangeService is similar to the RowCloneRangeService. The difference is that BlockRowCloneRangeService clones only cells from specified top row to bottom row between specified columns, so the row index of cells that is between the specified columns and row index is greater than the bottom row will be moved after cloned rows are added.
On the other hand, the RowCloneRangeService clones cells from specified top row to bottom row without the limitation of column and moves all cell’s index below the range itself after the cloned rows are added.
The result is shown in the following, you can see the range B3 and K4 is cloned and added on the below. The cells that the column index is between column B and K and the row is greater than 4 are moved.
4.1.19. BlockColumnCloneRangeService
BlockColumnCloneRangeService is similar to the ColumnCloneRangeService. The difference is that BlockColumnCloneRangeService clones only cells from specified left column to right column between specified rows, so the column index of cells that is between the specified rows and greater than the right column will be moved after cloned columns are added.
On the other hand, the ColumnCloneRangeService clones columns from specified left column to right column without row limitation, the columns index that is greater than the right column will be changed after cloned columns is added.
The result is shown in the following, you can see the range B3 and C23 is cloned and added on the right. The cells that row is between row 3 and 23 and column is greater than C are moved.
4.2. IExcelCellService & IExcelFunctionService
In ExcelTDF, one of the most flexible functionality is that you can create your own custom service. Custom service lets you control your cell output in the template design, that is similar to the excel function. Custom service has two types. The implementation class of IExcelCellService is custom cell service while the implementation class of IExcelFunctionService is custom function service. Cell service has no return result, in the contrary, function service return a string as result. In the custom service, the parameter can be template variable, cell name, constant of text or number. The base class of ExcelCellServiceBase provides a lot of methods to get parameter’s cell name or values. On the above example of MultiplePageRangeService, we used XSCHECKBOX, XSPAGEBREAK, XSBARCODE cell services and XFPRANGE and XFSRANGE function services. If parameter of service is cell name, the index of cell’s row and column will be recalculated. If the parameter of service is template variable, then ExcelTDF will search template variable’s cell name.
ExcelTDF provides following cell services and function services now.
4.2.1. Cell Service
4.2.1.1. XSCHAR
Output amount digit by digit.
Format:
%{=XSCHAR(TEXT, 1|LEFT|left|0|RIGHT|right)}
%{=XSCHAR(TEXT, 1|LEFT|left|0|RIGHT|right, LEADING)}
%{=XSCHAR(TEXT, 1|LEFT|left|0|RIGHT|right, LEADING, TAILING)}
where TEXT is a template variable or cell name or constant of number. Second parameter of 1|LEFT|left indicates that output digit from left to right(left alignment) start with the cell of this service. 0|RIGHT|right indicates that output digit from right to left (right alignment) start with the cell of this service. The third and fourth parameter is appended char on the beginning or ending of char, that maybe dollar marker etc.
Example:
%{=XSCHAR(“123999″, 0, “$”)}, %{=XSCHAR(A4, 1, “$”, “-”)}
4.2.1.2. XSCHECKBOX
Output a checkbox. The checkbox is not a vb control, it is just using some special font.
Format:
%{=XSCHECKBOX(FLAG)}
%{=XSCHECKBOX(FLAG,TEXT)}
where FLAG is a flag to indicate checked or not, 1|true is checked, otherwise not checked. The second parameter of TEXT is a string after checkbox. Both FLAG and TEXT can be a template variable, cell name of constant string, number.
Example:
%{=XSCHECKBOX(PARKTYPE)}, %{=XSCHECKBOX(PARKTYPE, PARKNAME)}
In data model
[Excel("PARKTYPE")]
public int ParkType
[Excel("PARKNAME")]
public string ParkName
4.2.1.3. XSBARCODE
XSBARCODE outputs bar code on the specified image. In order to output bar code you must add a image to template where your bar code will be output. Bar code will be output as the same size as image.
Format:
%{=XSBARCODE(IMAGENAME, BARCODETEXT, TYPE)}
%{=XSBARCODE(IMAGENAME, BARCODETEXT, TYPE, SHOW_LABEL)}
IMAGENAME: the image name you have added into template
BARCODETEXT: bar code string, which can be template variable, cell name or constant text, number
TYPE: bar code type
SHOW_LABEL: 1 or true specify to output string of bar code text.
Following bar code types are supported:
UCC12
UPCA, UPC-A
UCC13, EAN13, EAN-13
Interleaved2of5
Industrial2of5
Standard2of5
LOGMARS
CODE39
CODE39Extended
Codabar
PostNet
ISBN,BOOKLAND
JAN13,JAN-13
UPC_SUPPLEMENTAL_2DIGIT
MSI_Mod10,MSI_2Mod10,MSI_Mod11,MSI_Mod11_Mod10,Modified_Plessey
UPC_SUPPLEMENTAL_5DIGIT,UPCE,UPC-E
EAN8,EAN-8
USD8,CODE11
CODE128,CODE128A
CODE128B,CODE128C
ITF14,CODE93
Example:
%{=XSBARCODE(“BCODE”, BARCODENO, “UPC-A”)}
BCODE is image name that bar code image will replace it.
4.2.1.4. XSPAGEBREAK
Add excel page break in the worksheet for document and report printing. XSPAGEBREAK is very useful for more page output.
Format:
%{=XSPAGEBREAK}
%{=XSPAGEBREAK(ROW|CELL|VAR)}
If no parameter is specified , add page break on the row of this service written. If row or cell is specified add page break on the specified row. If template variable is specified, add page break on the row on which template variable is.
Example: %{=XSPAGEBREAK}
On the example of service MultiplePageRangeService, XSPAGEBREAK is added in the template.
XSPAGEBREAK are added on the last row in last page of owner report and parking report. Parking report is a page processed a clone service. When parking page is cloned, the page break will be added on the last row of cloned page.
4.2.1.5. XSVDLIST
Set cell as pull down list. It is the same as the menu of Data/Data Input/List in the Excel.
Format:
%{VAR=XSVDLIST(“A,B,C”|AJ12:AJ20|VARIABLE)}
XSVDLIST is some different from services above. The left side variable name is the value of cell in pull down list while the parameter in expression of XSVDLIST is pull down items which is able to be a string with comma separated or cell range or variable which is in the list range. The value of VAR can be referenced by other expression and can be saved back into data model as other variables.
Example:
%{ITEM=XSVDLIST(“iPod,iPhone,iPad”)}
In data model:
[Excel("ITEM")]
public string Item {get; set;}
4.2.2. Function Service
Custom function service can be used in expression of custom cell service, other custom function service and Excel formula.
4.2.2.1. XFPRANGE
Search variable from specified parent range and return the cell name such as A12 or cell name string such as A12,A13,A14 if variable is in the list. Because that ExcelTDF search variable first from range of itself, then the child ranges, if you have the same variable name in different range, this function maybe useful, you can search variable from your specified range. Usually, in child range you can reference parent range’s variable directly by this function.
Format:
%{=XFPRANGE(VAR)}
%{=XFPRANGE(VAR, PARENT_LEVEL)}
VAR: template variable to be searched
PARENT_LEVEL: the number of parent hierarchy
second parameter is the specified number from which parent range to search variable. For example, if the second parent is 2, search variable VAR from the parent’s parent range. If no second parameter is specified, search from parent range
Example:
#C{=SUM(XFPRANGE(FURIKOMI, 1))}
On the above MultiplePageRangeService, in the first page of owner report we want to output total sum of FURIKOMI which is across in the first page, internal page and last page. So we specified search FURIKOMI from parent range, that is OWNER_REPORT which includes all pages. FURIKOMI is a variable in a list, so SUM(XFPRANGE(FURIKOMI, 1)) is the total amount.
4.2.2.2. XFSRANGE
Search variable from all brother ranges including itself and return the cell name such as A12 or cell name string such as A12,A13,A14 if variable is in the list.
Format:
%{=XFSRANGE(VAR)}
VAR is the variable to be searched.
Example:
#C{小計=SUM(FURIKOMI)}
#C{合計=SUM(XFSRANGE(小計))}
On the above MultiplePageRangeService, in owner report, each page has a variable named 小計 which is the sum amount of parking in that page and in the last page there is a variable 合計 which is the sum of all 小計 in each page. You can download sales report result on the above to see how it works.
4.2.2.3. XFCELL
This is a simple function to get variable’s cell name and return cell name or cell name string such as A12,A13,A14.
Format:
%{=XFCELL(VAR)}
Example:
%{=XFCELL(ITEM)}
4.2.2.4. XFMATCH
XFMATCH is used to search some values from specified searching range, if matched, it returns the cell names or values.
Format:
%{XFMATCH(SEARCH_VALUES,SEARCH_RANGE)
%{XFMATCH(SEARCH_VALUES,SEARCH_RANGE, PARENT_HIERARCHY)
SEARCH_VALUES: the values to be searched. which can be a variable, cell name such as AJ12 or AJ12:AK20, or constant string separated by comma such as “A,B,C”. This function searches value by some orders. Assume SEARCH_VALUES is specified as “A,B,C”, function will search A first, if found return the result, then B, then C.
SEARCH_RANGE: the search range which can be a variable, cell name, or any constant string.
PARENT_HIERARCHY: Specify from which template range to search, 1 is the parent, 2 is the parent’s parent template range.
Example:
#{=CONCATENATE(XFOFFSET(XFMATCH(“◎,○,▲”, 本紙, 1), 0, 1), “=”, XFOFFSET(XFMATCH(“○,▲,△”, 本紙, 1), 0, 1), XFOFFSET(XFMATCH(“△”, 本紙, 1), 0, 1))}
In the example of ColumnCloneRangeService, we used function as above to search the cell which has one of values ◎,○,▲. You can see the function is used in Excel formula expression.
4.2.2.5. XFOFFSET
Get cell name with the specified offset. This function is like excel formula function OFFSET.
Format:
%{=XFOFFSET(VAR,ROW,COL,PARENT_HIERARCHY)}
VAR: variable to be searched, which is a template variable.
ROW: row offset number
COL: column offset number
PARENT_HIERARCHY: Specify from which template range to search, 1 is the parent, 2 is the parent’s parent template range.
Example:
#{=CONCATENATE(XFOFFSET(XFMATCH(“◎,○,▲”, 本紙, 1), 0, 1), “=”, XFOFFSET(XFMATCH(“○,▲,△”, 本紙, 1), 0, 1), XFOFFSET(XFMATCH(“△”, 本紙, 1), 0, 1))}
4.3. How to create your service
ExcelTDF provides mechanism to create your own range service, cell service and function service. The three interfaces are used to let you create your service easily. These interfaces are IExcelRangeService, IExcelCellService and IExcelFunctionService.
4.3.1. How to create template range service, IExcelRangeService
See the example of MyRowRangeService which do the same works as RowRangeService.
4.3.1.1. Create IExcelRangeService implementation class
using System; using System.Collections.Generic; using System.Linq; using System.Text; using log4net; using JanaBiz.Common.Serialize; using JanaBiz.OpenXml.Data; namespace myservice { /// <summary> /// Process excel range defined by top row, and bottom row. /// Template variable are replaced by value in data model. /// This is simple range. worksheet root range is this type. /// </summary> public class MyRowRangeService : ExcelRangeServiceBase, IExcelRangeService { /// <summary> /// Logger /// </summary> static readonly ILog logger = LogManager.GetLogger(typeof(MyRowRangeService)); /// <summary> /// constructor /// </summary> public MyRowRangeService() : base() { } #region interface method /// <summary> /// Parse specified worksheet using data provider /// </summary> /// <param name="sheetIndex"></param> public virtual void Parse(int sheetIndex) { //if no data, not processing if (ExcelRange == null || ExcelRange.DataProvider == null) return; //get data provider IRangeDataProvider dataProvider = ExcelRange.DataProvider; //reset mapping info ExcelRange.DataMapping.Clear(); //get mapping info from data provider SerializableDictionary<string, ExcelAttribute> dmp = dataProvider.GetMappingData(dataProvider[0]); ExcelRange.DataMapping.Add(dmp); //set data values into mapped variables SetNewCellValues(ExcelRange, dmp, ExcelRange.Top, ExcelRange.Bottom, 0, 0); //no row or column index moved or changed ExcelRange.ChangedRowCount = 0; //process flag of this range ExcelRange.Parsed = true; } #endregion } }
4.3.1.2. Add your service object into configuration file
In your application configuration file, there is Spring.NET configuration section of object; add your object as following.
<!-- my range service--> <objects> <object id="MyRowRangeService" type="myservice.MyRowRangeService, Your_Assembly(dll)" singleton="false"/> </objects>
4.3.1.3. Set your service into your template range configuration
<?xml version="1.0" encoding="shift_jis"?> <objects xmlns="http://www.springframework.net" > <!-- sheet 1 --> <object id="MyRowRangeService_SHEET1" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="ROOT"/> <property name="Top" value="8"/> <property name="Bottom" value="10"/> <property name="ServiceId" value="RowRangeService"/> <property name="ChildRanges"> <list element-type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml"> <ref object="MyRangeService"/> </list> </property> </object> <object id="MyRangeService" type="JanaBiz.OpenXml.TemplateRange, JanaBiz.OpenXml" singleton="false"> <property name="RangeName" value="MYRANGE"/> <property name="LeftTop" value="L8"/> <property name="RightBottom" value="BE10"/> <property name="ServiceId" value="MyRowRangeService"/> </object> </objects>
That is all, now ExcelTDF will call your service of MyRowRangeService to process specified range in your template configuration file.
4.3.2. How to create your custom cell service, IExcelCellService
See the example of MyVDListCellService which performs the same work as XSVDLIST
4.3.2.1. Create implementation class of IExcelCellService
using System; using System.Collections.Generic; using System.Collections; using System.Linq; using System.Text; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using log4net; using JanaBiz.ExcelFormulaParser; namespace myservice { /// <summary> /// My validate list cell service /// </summary> public class MyVDListCellService : ExcelCellServiceBase, IExcelCellService { static readonly ILog logger = LogManager.GetLogger(typeof(MyVDListCellService)); public MyVDListCellService() : base() { } public void Parse(int sheetIndex) { //1: get parameters Parameters = GetExpressionParameters(ExprTreeNode, sheetIndex); //2: check exists function variable in template if (!ExcelRange.IsMyVariable(CellData.VarName)) { throw new ServiceException(this, "E_NO_VDLIST_VAR", CellData.VarName); } //3: check if no paramter if (Parameters.Count == 0) { throw new ServiceException(this, "E_NO_PARAM"); } //4: check if parameter is empty string listValues = Parameters[0].Param; if (string.IsNullOrEmpty(listValues)) { throw new ServiceException(this, "E_EMPTY_PARAM"); } //5: parameter type check switch (Parameters[0].ParamType) { //if it is variable case ParamType.VARIABLE: IList<string> cells = GetReferencesOfVariable(listValues, sheetIndex, ExcelRange, ListIndex); if (cells == null || cells.Count == 0) { throw new ServiceException(this, "E_VAR_NOT_FOUND", listValues); } string[] cellrefs = OpenXmlSpreadsheet.CreateMergeCellname(WorksheetPart, cells.ToArray()); if (cellrefs.Length > 1) { listValues = string.Format("{0}:{1}", cellrefs[0], cellrefs[1]); } break; //if it is cell reference case ParamType.CELL: //if it is text case ParamType.TEXT: break; } //do your work here //set data validate list formula1 SetCellValue(CellData.DataType, CellData.CellName, CellData.CellValue); OpenXmlSpreadsheet.SetDataValidateFormula1(WorksheetPart, CellData.CellName, listValues); } } }
4.3.2.2. Add your service object into configuration file
In your application configuration file, there is Spring.NET configuration section of object, add your object as following.
<!-- my cell service--> <objects> <object id="MYVDLIST" type="myservice.MyVDListCellService, Your_Assembly(dll)" singleton="false"/> </objects>
Now you can use MYVDLIST in your template. ExcelTDF will parse template and build your cell service MYVDLIST by calling your created class.
4.3.3. How to create your custom function service, IExcelFunctionService
Custom function service is the same as custom cell service except that function service must return a result. The result is set in property of FunctionResultValue. See the following example; it does the same work as the XFCELL function explained above.
4.3.3.1. Create your implementation class of IExcelFunctionService
using System; using System.Collections.Generic; using System.Collections; using System.Linq; using System.Text; using log4net; using JanaBiz.OpenXml; namespace myservice { /// <summary> /// Get variable's cell name /// %{=XFCELL(VAR)} /// </summary> public class MyCellFunctionService : ExcelCellServiceBase, IExcelFunctionService { static readonly ILog logger = LogManager.GetLogger(typeof(MyCellFunctionService)); public MyCellFunctionService() : base() { } /// <summary> /// do your work here /// </summary> /// <param name="sheetIndex"></param> public void Parse(int sheetIndex) { Parameters = GetExpressionParameters(ExprTreeNode, sheetIndex); //1: no parameter check if (Parameters.Count == 0) throw new ServiceException(this, "E_NO_PARAM"); //2: get cellname parameter string var1 = Parameters[0].Param; //3: empty parameter check if (string.IsNullOrEmpty(var1)) throw new ServiceException(this, "E_EMPTY_PARAM"); //4: parameter type switch (Parameters[0].ParamType) { //it is a variable case ParamType.VARIABLE: FunctionResultValue = GetVariableResultAsString( GetReferencesOfVariable(var1, sheetIndex, ExcelRange, ListIndex)); break; //it is a cell name case ParamType.CELL: FunctionResultValue = var1; break; //for others default: throw new ServiceException(this, "E_NOT_CELLNAME", var1); } } } }
4.3.3.2. Add your service object into configuration file
In your application configuration file, there is Spring.NET configuration section of object, add your object as following.
<!-- my function service--> <objects> <object id="MYCELL" type="myservice.MyCellFunctionService, Your_Assembly(dll)" singleton="false"/> </objects>
Now you can use MYCELL in your template. ExcelTDF will parse template and build your cell service MYCELL by calling your created class.
