This project is read-only.

SharpLightReporting involves two steps for report creation. First step is template creation and second step involves data model creation. 

 

Sample project could be downloaded from http://sharplightreporting.codeplex.com/downloads/get/848610 . Also, please change the referenced assembly in the sample project to the latest one. Easiest way is through Nuget.

TemplateCreation

To create a template, open any existing excel worksheet or create a new one. Add all the static data and formating into the spreadsheet and leave gap for dynamic data. Please keep in mind that the report engine parser flows downwards and then towards right this means it parses a row only once but each column is parsed for each and every row, parser flows to.  In simple words report engine starts from the first cell of the row and moves towards right than it goes to next row and then again it moves towards right.

The tags that SharpLightReporting define are as follows:

1)Report Tag(defines the bounds of the report).

2)Variable Tag(defines the name of the property whose value will be placed at the tags position).

3)Method Tag(defines the name of the parameter less method that will be called when the SharpLightReporting engine encounters this tag).

4)Vertical Repeater Tag(repeats defined portion of the spread sheet vertically down below).

5)Horizontal Repeater Tag(repeats defined portion of the spread sheet horizontally towards right).

6)CellFormat Tag (tells the reporting engine to set the basic data formatting of the cell when dynamic data is inserted or the cells position is changed).

7)Picture Tag(this tag informs the reporting engine when a dynamic picture needs to be added to the report and also the image source and type etc.).

8)Chart Tag (this tag helps to add charts dynamically to the report).

9)PageBreak Tag (Adds custom page break so that template designer can control what portion of the report needs to be shifted to next page).

10)Custom Tag (When SharpLightReporting engine encounters this tag it calls an event with position of this tag and base SLDocument as parameters, users can do whatever they want with the report document using underneath SpreadsheetLight spreadsheet automation).

11)DeleteRow Tag (deletes a row after report processing).

12)DeleteCol Tag (deletes a col after report processing).

Report Data Model Creation

Report data model creation is very simple. Just create any .Net class and implement IReportModel interface. This is a blank interface so involves no extra coding. Define all the variables(properties, not fields), methods that you have put inside the template. Make them fetch and transform data the way you want it and that is it.

Running The Reporting Engine

Create a new instance of SharpLightReporting.ReportEngine class and call the method ProcessReport with the parameters :template file (file name as string or file as stream) and output file name or stream and the ReportDataModel. It's done. !!!

******************************************************************************

Template Tag Reference

Rules : Tags should be defined exactly as mentioned in the example: <variable is not same as < variable i.e . space after and before angular brackets are not allowed.

Try using all tags in lower case except the name of the property or method in variable and method tags. Name of property and method should be in the same case as defined in the report data model class.  

Tags may appear as xml but they are not xml and their values are not enclosed within double quotes.

Attributes within a tag are separated by comma. Forgetting a comma is a common mistake which you should avoid.

Rows and columns are refered with positive  integer number values and start with index 1. Tags that work with rows and columns have top and bottom attributes to hold row numbers and left and right attributes to hold column numbers.

Repeater tags when defined within the bounds of another such tags should not have their bounds pointing outside the bounds of parent tags.

Templates must be saved in .xlsx format. This format is supported by excel 2007 and above and also by various open source and free office solutions.     

##### in the examples below, means that you should put your own values in it’s place.

1)      Report Tag : Report tag should be placed in the very first cell of the template spreadsheet. Report tag defines the bounds of the template and this is the minimum area that SharpLightReport engine parses. If this tag is not mentioned in the very first row and the first column of the spread sheet template that the sheet will not be parsed.

 

Syntax:  <report left=#####, right=#####, top=#####, bottom=#####/>  e.g. <report left=1, right=20, top=2, bottom=42/>

 

2)      Variable Tag : Variable tag holds the name of the property whose value is pushed into the section of the report template that defines this tag. Please note that it defines the name of the property and not the field. This tag could be used as a value placeholder within another tag also. Please see vertical repeater tag example below.  

Syntax : <variable name=#####/>  e.g. <variable name=InvoiceNo/>

3)      Method Tag : This tag defines a name of a parameter less method that returns void. The method this tag defines is called whenever the report engine encounters this tag. Method name should be exactly as defined in code.

 

Syntax: <method name=##### /> e.g. <method name=MoveToNextRecord />

 

4)       Cell format tag : Cell format tag defines the format of the cell that should be enforced when ever the reporting engine encounters this tag. This tag could be put inside a report cell along side any other tag or static value. This provides certain control over the format of the cell value when the value of such cell is copied or moved. This tag ensures that the formatting is kept intact.

 

Syntax : <cellformat =#####/> e.g. <cellformat =decimal />

 

Other  values that this tag can contain are : number, datetime, currency, decimal, bool, text. Default is text when cell format could not be correctly evaluated or not defined.

 

5)      Delete row tag : SharpLightReport engine deletes the row defining this tag after completely processing the report  template.

        Syntax: <deleterow/>

6)       Delete column tag: Same as delete row tag but deletes the column instead.

 

Syntax: <deletecol/>

 

7)      Vertical repeater tag: This tags repeats the defined bounds vertically. Attributes that are part of this tag are top, bottom(which define top index and bottom index of the rows which contain the section that has to be repeated),left and right(define left index and right index of the columns that contain the section that needs to be repeated. These attributes are mandatory along with the frequency attribute. Mode attribute defines how the section will be repeated i.e by inserting new rows below or by shifting the values of the cells below or by overwriting the values of the cells below.  Please note that any kind of repeater when defined within another repeater should not point outside the bounds of parent repeater.

 

Syntax: <vertrepeat top=####, bottom=#####, left=#####, right=#####, frequency=#####, mode=#####  />

e.g.

<vertrepeat top=12, bottom=15, left=1, right=10, frequency=5, mode=shift  />                   or

<vertrepeat top=12, bottom=15, left=1, right=10, frequency=<variable name=Count/>, mode=shift  />

Mode attribute can hold shift, insert, overwrite as values. Default for vertical repeater  is insert and for horizontal repeater is shift.

 

8)      Horizontal repeater tag: This tag is similar to vertical repeater tag but repeats the defined bounds horizontally . Default mode of this tag is shift unless specified otherwise.

Syntax : <horizrepeat top=#####, bottom=#####, left=#####, right =#####, frequency = #####, mode=##### />

e.g.

<horizrepeat top=21, bottom=24, left=3, right =3, frequency = 2, mode=overwrite />

9)      Custom page break tag : This tag puts a custom page break  on top and left edge of the cell that defines it.

 

        Syntax: <pagebreak/>

 

10)     Picture tag : This tag is used to put a dynamic image on the report. Image data could come from a class property, file reference or a web reference. One can also define the size of the image.  Picture size could be defined in cms, inches or emu. Position of the image is relative to the position of the tag.  This tag defines following attributes:

 

name : This is optional attribute. One can provide a name to a picture for programmatic access to it.

ref: This holds the complete file reference or web reference or property name from where the picture data has to be fetched.

reftype: This tells the reporting engine that the value that ref attribute holds is a property or a local file or a web address. Values that this attribute holds are file, prop, web.

format: This attribute informs the reporting engine about the picture format. It can have following values: jpeg, wmf, tif, png, pcx, ico, gif, emf.

widthcm or widthemu or widthinch: This attributes holds the width of the image.

heightcm  or heightemu or heightinch: This attribute holds the height of the image.

addtocolpos : Contains a decimal number which will be added to the current column position of the picture. E.g. 2.5 will add two and half columns to the current picture position.

addtorowpos: Contains a decimal number which will be added to the current row position of the picture. E.g. 2.5 will add two and half rows to the current picture position.

 

Syntax:

<pic name=#####, ref=#####, reftype=#####, format=#####, widthcm=#####, heightcm=#####, addtocolpos=#####, addtorowpos=##### />

e.g.

<pic name=itempic, ref=clothes.jpg, reftype=file, format=jpeg, widthcm=14.29, heightcm=10.58, addtocolpos=1.5, addtorowpos=2.5 />

 

1)      Chart tag : This tag helps to create a dynamic chart within the report. Position of the chart is relative to the situation of the tag in the template. Bounds described within the tag determine the data zone for which the chart will be created.  Attributes used within this tag are as follows :

name: This attribute gives a meaningful name to the chart. It could be used in conjunction with a variable tag. The name attribute is very handy when one needs to change chart type programmatically by prescribing to SetChartTypeAndStyle” event.

charttype: This attribute describes the type of chart that needs to be created. Underlying SpreadsheetLight library supports many different types of charts and their customization.But in the present version of SharpLightReporting we can add only these charttype through template tag, although one can subscribe to SetChartTypeAndStyle event and change the chart type programmatically to any chart type that SpreadsheetLight supports. Values that this attribute can take are: pie, bar, line, area, bubble, doughnut, surface and radar. If you do not put this attribute into the chart tag than by default clustered bar chart will be created.

 

style: It is a positive integer value starting from 1 to 48. This means any of the chart type described above can be rendered in 48 different ways.

 

width: Chart width in columns

height: Chart height in rows

 

top, bottom : chart’s data zone’s top and bottom row.

left, right: chart’s data zone’s left and right column.

 

addtorowpos: Adds mentioned floating point value to the charts top-left starting row position. This value is in rows and not in cms/emu/inches. (supported in version 1.0.2 and above)

addtocolpos: Adds mentioned floating point value to the charts top-left starting column position. This value is in columns and not in cms/emu/inches. (supported in version 1.0.2 and above)

 

   

Syntax:

 

<chart name=#####, width= #####, height=#####, top=#####, bottom=#####, left=#####, right=#####, type=#####,  style=#####, addtorowpos=#####, addtocolpos=##### />

 

e.g.

 

<chart name=test, width= 8, height=15, top=20, bottom=24, left=1, right=7, style=48, addtorowpos=1.5, addtocolpos=2.5 />

Or

<chart name=test, width= 8, height=15, top=20, bottom=24, left=1, right=7,style=48/>

Or

<chart name=test, width= 8, height=15, top=20, bottom=24, left=1, right=7, type=pie,  style=48, addtorowpos=1.5, addtocolpos=2.5 />

Starting from version 1.0.70 you can directly mention SpreadsheetLight's enum value as type parameter of chart tag to get access to so many more different types of charts
eg:
<chart name=test, width= 8, height=15, top=20, bottom=24, left=1, right=7, type=SLBarChartType.StackedHorizontalCone, &nbsp;style=48, addtorowpos=1.5, addtocolpos=2.5 >

Please check the enums available as below :

 

SLAreaChartType
Area 0 Area.
  StackedArea 1 Stacked Area.
  StackedAreaMax 2 100% Stacked Area.
  Area3D 3 3D Area.
  StackedArea3D 4 Stacked Area in 3D.
  StackedAreaMax3D
SLBarChartType

  ClusteredBar 0
  StackedBar 1 Stacked Bar.
  StackedBarMax 2 100% Stacked Bar.
  ClusteredBar3D 3 Clustered Bar in 3D.
  StackedBar3D 4 Stacked Bar in 3D.
  StackedBarMax3D 5 100% Stacked Bar in 3D.
  ClusteredHorizontalCylinder 6 Clustered Horizontal Cylinder.
  StackedHorizontalCylinder 7 Stacked Horizontal Cylinder.
  StackedHorizontalCylinderMax 8 100% Stacked Horizontal Cylinder.
  ClusteredHorizontalCone 9 Clustered Horizontal Cone.
  StackedHorizontalCone 10 Stacked Horizontal Cone.
  StackedHorizontalConeMax 11 100% Stacked Horizontal Cone.
  ClusteredHorizontalPyramid 12 Clustered Horizontal Pyramid.
  StackedHorizontalPyramid 13 Stacked Horizontal Pyramid.
  StackedHorizontalPyramidMax 14 100% Stacked Horizontal Pyramid. 
SLBubbleChartType

Bubble 0 Bubble.
  Bubble3D 1 Bubble with a 3D effect. 

 

SLPieChartType

Pie 0 Pie.
  Pie3D 1 Pie in 3D.
  PieOfPie 2 Pie of Pie.
  ExplodedPie 3 Exploded Pie.
  ExplodedPie3D 4 Exploded Pie in 3D.
  BarOfPie 5 Bar of Pie 
 
SLLineChartType

Line 0 Line.
  StackedLine 1 Stacked Line.
  StackedLineMax 2 100% Stacked Line.
  LineWithMarkers 3 Line with Markers.
  StackedLineWithMarkers 4 Stacked Line with Markers.
  StackedLineWithMarkersMax 5 100% Stacked Line with Markers.
  Line3D 6 3D Line. 

 

SLDoughnutChartType

Doughnut 0 Doughnut.
  ExplodedDoughnut 1 Exploded Doughnut. 

 

SLSurfaceChartType

Surface3D 0 3D Surface.
  WireframeSurface3D 1 Wiredframe 3D Surface.
  Contour 2 Contour.
  WireframeContour 3 Wireframe Contour.

 

SLRadarChartType

Radar 0 Radar.
  RadarWithMarkers 1 Radar with Markers.
  FilledRadar 2 Filled Radar. 

 

 
 
 
 

 
 
 

 

 

***Since people have already started to download this software I have put a tag reference above. Which should get you started. More on custom tags is coming up.

***Please download the sample project from:

http://sharplightreporting.codeplex.com/downloads/get/848610

*******************************************************************************************

DONATE USD 5            Donate

DONATE USD 10          Donate

DONATE USD 25          Donate

DONATE USD 50          Donate

DONATE USD 100        Donate

Last edited Oct 9, 2015 at 5:38 AM by himanshu_kodwani, version 13