Tag Archives: Excel

ExcelTDF, create excel report by metadata quickly

Posted on by

ExcelTDF provides very simple way to create complicated excel report. ExcelTDF is able to process row range, column range, block range and complicated range which including some child ranges such as list, list clone range, list-in-list range etc. You can define these ranges by Metadata. Let us show some examples.

Download Metadata examples FastExcelReport

1. Create a console application project: assume it is S:\project\FastExcelReport.

2. Set ExcelTDF configuration as following.

2.1. Add ExcelTDF repository configuration file Repository.config into your project. The content of Repository.config is shown as following

<?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="mydemo"/>
    <property name="RepositoryUri" value="../Templates"/>
  </object>
</objects>

By the above configuration, your template and template range configuration file are placed in the directory of ../Templates/mydemo (RepositoryName) which is related the executable directory bin. The template file name *.xlsx and template range configuration file *.xml must have the same name, and this name is also their Template ID. ExcelTDF will search all templates and template range configuration files in root repository and subdirectories. When you use metadata, no template range configuration file is needed

3. Design your template, here named MetadataTest.xslx, and save it into the specified path of repository configured above S:\project\FastExcelReport\Templates\mydemo\MetadataTest.xlsx

ExcelTDF .NET metadata | Excel report

4. Create your data model according to the template ranges.

4.1. Add reference assemblies
     . JanaBiz.OpenXml.dll
     . DocumentFormat.OpenXml.dll
     . log4net.dll
     . Spring.Core.dll

4.2. Create data model class as following

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using JanaBiz.OpenXml;

//define class for each range 
namespace FastExcelReport
{
    /// <summary>
    /// Assume A-Range is root range
    /// </summary>
    [ExcelRange("A-Range", "A1", "AF40", "BlockRangeService")]
    public class MetadataTestDataModel
    {
        public MetadataTestDataModel()
        {
            for (int i = 0; i < 4; i++)
            {
                cRangeList.Add(new CRange()
                {
                    Item1 = (i + 1) * 30
                });
            }
        }


        [Excel("AItem1")]
        public string Item1 = "Item1 in range A";

        [Excel("AItem3")]
        public string Item3 = "Item3 in range A";

        [ExcelRange("B-Range", "G11", "Q32", "BlockRangeService")]
        public BRange bRange = new BRange();


        /// <summary>
        /// The range is defined by first element area
        /// </summary>
        [ExcelRange("C-Range", "T4", "AE5", "RowListRangeService", PageSize = 4)]
        public IList<CRange> cRangeList = new List<CRange>();            

        [ExcelRange("D-Range", "B35", "AF40", "BlockRangeService")]
        public DRange dRange = new DRange();
    }

    /// <summary>
    /// The data model of B-Range
    /// </summary>
    public class BRange
    {
        [Excel("BItem1")]
        public string Item1 = "Item1 in range B";

        [Excel("BItem2")]
        public string Item2 = "Item2 in range B";

    }

    /// <summary>
    /// The data model of C-Range
    /// </summary>
    public class CRange
    {
        [Excel("CItem1")]
        public int Item1 = 300;

        [Excel("CItem2")]
        public double Item2;  //save the result of sum

        [Excel("CItem3")]
        public double Item3 = 12.5;

        [Excel("CItem4")]
        public string Item4 = "Item4 in range C";

        [Excel("CItem6")]
        public long Item6 = 1290865;
    }


    /// <summary>
    /// The data model of D-Range
    /// </summary>
    public class DRange
    {
        private string item1 = "Item1 in range D";


        [Excel("DItem1")]
        public string Item1
        {
            get { return item1; }
            set { item1 = value; }
        }
    }    
}


5. Add following template processing code into your program. That is over

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Reflection;

using JanaBiz.OpenXml.Template;
using JanaBiz.OpenXml.Handler;
using JanaBiz.OpenXml.Data;
using JanaBiz.OpenXml.Context;
using JanaBiz.OpenXml.Repository;


//load configuration of repository
[assembly: RegisterRepository("FastExcelReport.Repository.config")]

namespace FastExcelReport
{
    class Program
    {
        static void Main(string[] args)
        {

//            OpenXmlContext.RegisterRepository("FastExcelReport.Repository.config");

            //create your handler 
            IOpenXmlHandler handler = new OpenXmlHandler();

            //tell ExcelTDF which ITemplateService is used to 
            //get template file and template range configuration
            handler.GetTemplateService<LocalTemplateService>();

            //set template id to identify which template is used
            handler.TemplateId = "MetadataTest";

            //tell ExcelTDF which IDataProviderManager and 
            //what data provider is used            
            handler.DataProviderManager = new DataModelProviderManager(
                new MetadataTestDataModel());

            //parse your template 
            handler.ParseTemplate(false);

            //parse your data 
            handler.ParseWorkbook(null);

            //PLEASE REPLACE with your file stream to get result document
            FileStream fs = new FileStream(@"c:\temp\metadatatest-result.xlsx", FileMode.Create);           

            //write parsed document into response stream
            handler.WriteParsedDocument(fs);
            fs.Close();

            //release some resource
            handler.Close();
        }
    }
}

The line of 

[assembly: RegisterRepository("FastExcelReport.Repository.config")]

is used to load Repository.config file.

The result of pecessed template is show as following.

ExcelTDF .NET metadata | fast Excel report