xoSpreadsheet，只需通过元数据即可快速创建excel报告 [英] xoSpreadsheet, fast create excel report by metadata simply
问题描述
xoSpreadsheet提供了一种非常简单的方法来创建复杂的excel报表. xoSpreadsheet能够处理行范围，列范围，块范围和复杂范围，其中包括一些子范围，例如列表，列表克隆范围，列表中列表范围等.您可以通过元数据定义这些范围.让我们展示一些例子.关于更多xoSpreadsheet，请参阅http://products.e-dbxml.net
下载元数据示例 FastExcelReport
1.创建一个控制台应用程序项目:假设它是S:\ project \ FastExcelReport.
2.创建app.config并将xoSpreadsheet配置添加到您的app.config中，如下所示
2.1.将处理程序定义添加到app.config/configSections
xoSpreadsheet provides very simple way to create complicated excel report. xoSpreadsheet 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. About more xoSpreadsheet please see http://products.e-dbxml.net
Download Metadata examples FastExcelReport
1. Create a console application project: assume it is S:\project\FastExcelReport.
2. Create app.config and add xoSpreadsheet configuration into your app.config as following
2.1. Add handler definition into app.config/configSections
<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net" />
<sectiongroup name="spring">
<section name="resourceHandlers" type="Spring.Context.Support.ResourceHandlersSectionHandler, Spring.Core" />
<section name="context" type="Spring.Context.Support.ContextHandler, Spring.Core" />
<section name="objects" type="Spring.Context.Support.DefaultSectionHandler, Spring.Core" />
</sectiongroup>
2.2.将模板配置添加到app.config/configuration中，如下所示
2.2. Add template configuration into app.config/configuration as following
<spring>
<resourcehandlers>
<handler protocol="memory" type="JanaBiz.OpenXml.Template.MemoryResource, JanaBiz.OpenXml" />
<handler protocol="repository" type="JanaBiz.OpenXml.Template.RepositoryResource, JanaBiz.OpenXml" />
</resourcehandlers>
<context>
<resource uri="config://spring/objects" />
<resource uri="repository://repository=mydemo;path=../../Templates" />
</context>
<objects xmlns="http://www.springframework.net">
</objects>
</spring>
2.3.如果需要，将log4net配置添加到app.config/configuration
2.3. Add log4net configuration into app.config/configuration if necessary
<log4net>
<appender name="ConsoleAppender" type="log4net.Appender.ConsoleAppender,log4net">
<layout type="log4net.Layout.PatternLayout">
<conversionpattern value="%date [%thread] %-5level %logger - %message%newline" />
</layout>
</appender>
<appender name="DebugAppender" type="log4net.Appender.DebugAppender,log4net">
<layout type="log4net.Layout.PatternLayout">
<conversionpattern value="%date [%thread] %-5level %logger [%property{NDC}] - %message%newline" />
</layout>
</appender>
<appender name="RollingFileAppender" type="log4net.Appender.RollingFileAppender,log4net">
<param name="File" value="openxml.log" />
<param name="MaximumFileSize" value="5MB" />
<param name="CountDirection" value="1" />
<param name="MaxSizeRollBackups" value="5" />
<param name="Encoding" value="Unicode" />
<layout type="log4net.Layout.PatternLayout,log4net">
<param name="ConversionPattern" value="%d [%5t] %-5p [%-20c] - %m%n" />
</layout>
</appender>
<root>
<level value="INFO" />
<appender-ref ref="RollingFileAppender" />
<appender-ref ref="ConsoleAppender" />
</root>
</log4net>
3.设计您的模板(此处名为MetadataTest.xslx)，并将其保存到S:\ project \ FastExcelReport \ Templates \ Template \ MetadataTest.xlsx
上方配置的存储库的指定路径中.
参见模板:http://products.e-dbxml.net/openxml-spreadsheet/wp-content/uploads/metadatatest.png
4.创建存储库必需的文件:template.xml(使用元数据时不需要resource.xml)
S:\ project \ FastExcelReport \ Templates \ template.xml文件，如下所示
3. Design your template, here named MetadataTest.xslx, and save it into the specified path of repository configured above S:\project\FastExcelReport\Templates\Template\MetadataTest.xlsx
see template: http://products.e-dbxml.net/openxml-spreadsheet/wp-content/uploads/metadatatest.png
4. Create repository necessary file: template.xml (resource.xml is not necessary when using metadata)
S:\project\FastExcelReport\Templates\template.xml file as following
<objects xmlns="http://www.springframework.net">
<object id="TemplateConfig" type="JanaBiz.OpenXml.Template.TemplateFileConfig, JanaBiz.OpenXml" singleton="true">
<property name="TemplateFileList">
<dictionary key-type="string" value-type="string">
<entry key="MetadataTest" value="${repository}/Template/MetaDataTest.xlsx" />
</dictionary>
</property>
</object>
</objects>
5.根据模板范围创建数据模型.
5.1.添加参考装配
. JanaBiz.OpenXml.dll
. DocumentFormat.OpenXml.dll
. log4net.dll
. Spring.Core.dll
5.2.创建数据模型类，如下所示
5. Create your data model according to the template ranges.
5.1. Add reference assemblies
. JanaBiz.OpenXml.dll
. DocumentFormat.OpenXml.dll
. log4net.dll
. Spring.Core.dll
5.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; }
}
}
}
</crange></crange>
6.将以下模板处理代码添加到您的程序中.结束了
6. 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 JanaBiz.OpenXml.Template;
using JanaBiz.OpenXml.Handler;
using JanaBiz.OpenXml.Data;
namespace FastExcelReport
{
class Program
{
static void Main(string[] args)
{
//create your handler
IOpenXmlHandler handler = new OpenXmlHandler();
//tell xoSpreadsheet 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 xoSpreadsheet 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();
}
}
}
</localtemplateservice>
请参阅结果文档:http://products.e-dbxml.net/openxml-spreadsheet/wp-content/uploads/metadatatest-result.png
see the result document: http://products.e-dbxml.net/openxml-spreadsheet/wp-content/uploads/metadatatest-result.png
推荐答案
{存储库}/Template/MetaDataTest.xlsx"/> </dictionary> </property> </object> </objects>
{repository}/Template/MetaDataTest.xlsx" /> </dictionary> </property> </object> </objects>
5.根据模板范围创建数据模型.
5.1.添加参考装配
. JanaBiz.OpenXml.dll
. DocumentFormat.OpenXml.dll
. log4net.dll
. Spring.Core.dll
5.2.创建数据模型类，如下所示
5. Create your data model according to the template ranges.
5.1. Add reference assemblies
. JanaBiz.OpenXml.dll
. DocumentFormat.OpenXml.dll
. log4net.dll
. Spring.Core.dll
5.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; }
}
}
}
</crange></crange>
6.将以下模板处理代码添加到您的程序中.结束了
6. 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 JanaBiz.OpenXml.Template;
using JanaBiz.OpenXml.Handler;
using JanaBiz.OpenXml.Data;
namespace FastExcelReport
{
class Program
{
static void Main(string[] args)
{
//create your handler
IOpenXmlHandler handler = new OpenXmlHandler();
//tell xoSpreadsheet 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 xoSpreadsheet 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();
}
}
}
</localtemplateservice>
see the result document: http://products.e-dbxml.net/openxml-spreadsheet/wp-content/uploads/metadatatest-result.png
see the result document: http://products.e-dbxml.net/openxml-spreadsheet/wp-content/uploads/metadatatest-result.png
这篇关于xoSpreadsheet，只需通过元数据即可快速创建excel报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
