Overview
The Magicodes.IE library now suports dynamic export functionality when using Excel templates with JObject, Dictionary, and ExpandoObject. This tutorial will guide you through implementing this feature.
This capability was initially inspired by contributions from arik, and we appreciate their input.
Before diving into this tutorial, let's review the syntax used in template-based exports:
{{Company}} // Cell rendering
{{Table>>BookInfos|RowNo}} // Table start syntax
{{Remark|>>Table}} // Table end syntax
{{Image::ImageUrl?Width=50&Height=120&Alt=404}} // Image rendering
{{Image::ImageUrl?w=50&h=120&Alt=404}} // Image rendering
{{Image::ImageUrl?Alt=404}} // Image rendering
{{Formula::AVERAGE?params=G4:G6}} // Formula rendering
{{Formula::SUM?params=G4:G6&G4}} // Formula rendering
If you're unfamiliar with the template export features of Magicodes.IE, please refer to the following guide:
Excel Template Export: Ordering Form Example
Now, let’s begin with this tutorial:
- Installing Magicodes.IE.Excel Package
Install-Package Magicodes.IE.Excel
- Preparing the Excel Template File
Refer to the image below:
This file can be located in test project under the name 【DynamicExportTpl.xlsx】.
- Performing Dynamic Export Using JObject
The implementation is straightforward, as shown in the code snippet below:
string json = @"{
'Company': 'Snow Goose',
'Address': 'Changsha, Hunan',
'Contact': 'Snow Goose',
'Tel': '136xxx',
'BookInfos': [
{'No':'a1','RowNo':1,'Name':'Docker+Kubernetes Application Development and Cloud Deployment','EditorInChief':'Li Wenqiang','PublishingHouse':'China Machine Press','Price':65,'PurchaseQuantity':10000,'Cover':'https://img9.doubanio.com/view/ark_article_cover/retina/public/135025435.jpg?v=1585121965','Remark':'Notes'},
{'No':'a1','RowNo':1,'Name':'Docker+Kubernetes Application Development and Cloud Deployment','EditorInChief':'Li Wenqiang','PublishingHouse':'China Machine Press','Price':65,'PurchaseQuantity':10000,'Cover':'https://img9.doubanio.com/view/ark_article_cover/retina/public/135025435.jpg?v=1585121965','Remark':'Notes'}
]
}";
var jobj = JObject.Parse(json);
// Template path
var tplPath = Path.Combine(Directory.GetCurrentDirectory(), "TestFiles", "ExportTemplates",
"DynamicExportTpl.xlsx");
// Create Excel export instance
IExportFileByTemplate exporter = new ExcelExporter();
// Output file path
var filePath = Path.Combine(Directory.GetCurrentDirectory(), nameof(DynamicExportByTemplate_Test) + ".xlsx");
if (File.Exists(filePath)) File.Delete(filePath);
// Export using template
await exporter.ExportByTemplate(filePath, jobj, tplPath);
The above code can be found in the unit test DynamicExportWithJObjectByTemplate_Test.
Note that since JObject is used here, it requires the Newtonsoft.Json package. However, Magicodes.IE.Excel does not have a direct dependency on Newtonsoft.Json.
Current, dynamic export via Excel templates is supported using JObject objects. Future versions may support additional dynamic approaches.
After execution, the result should resemble the image shown below:
- Performing Dynamic Export Using Dictionary<string, object>
The code is similar to the previous example, but uses Dictionary instead:
var data = new Dictionary<string, object>()
{
{ "Company","Snow Goose" },
{ "Address", "Changsha, Hunan" },
{ "Contact", "Snow Goose" },
{ "Tel", "136xxx" },
{ "BookInfos",new List<Dictionary<string,object>>()
{
new Dictionary<string, object>()
{
{"No","A1" },
{"RowNo",1 },
{"Name","Docker+Kubernetes Application Development and Cloud Deployment" },
{"EditorInChief","Li Wenqiang" },
{"PublishingHouse","China Machine Press" },
{"Price",65 },
{"PurchaseQuantity",50000 },
{"Cover","https://img9.doubanio.com/view/ark_article_cover/retina/public/135025435.jpg?v=1585121965" },
{"Remark","Buy Now" }
},
new Dictionary<string, object>()
{
{"No","A2" },
{"RowNo",2 },
{"Name","Docker+Kubernetes Application Development and Cloud Deployment" },
{"EditorInChief","Li Wenqiang" },
{"PublishingHouse","China Machine Press" },
{"Price",65 },
{"PurchaseQuantity",50000 },
{"Cover","https://img9.doubanio.com/view/ark_article_cover/retina/public/135025435.jpg?v=1585121965" },
{"Remark","K8s is Amazing" }
}
}
}
};
// Template path
var tplPath = Path.Combine(Directory.GetCurrentDirectory(), "TestFiles", "ExportTemplates",
"DynamicExportTpl.xlsx");
// Create Excel export instance
IExportFileByTemplate exporter = new ExcelExporter();
// Output file path
var filePath = Path.Combine(Directory.GetCurrentDirectory(), nameof(DynamicExportWithDictionaryByTemplate_Test) + ".xlsx");
if (File.Exists(filePath)) File.Delete(filePath);
// Export using template
await exporter.ExportByTemplate(filePath, data, tplPath);
See DynamicExportWithDictionaryByTemplate_Test for the complete implementation.
- Performing Dynamic Export Using ExpandoObject
Again, the code follows a similar pattern:
dynamic data = new ExpandoObject();
data.Company = "Snow Goose";
data.Address = "Changsha, Hunan";
data.Contact = "Snow Goose";
data.Tel = "136xxx";
data.BookInfos = new List<ExpandoObject>() { };
dynamic book1 = new ExpandoObject();
book1.No = "A1";
book1.RowNo = 1;
book1.Name = "Docker+Kubernetes Application Development and Cloud Deployment";
book1.EditorInChief = "Li Wenqiang";
book1.PublishingHouse = "China Machine Press";
book1.Price = 65;
book1.PurchaseQuantity = 50000;
book1.Cover = "https://img9.doubanio.com/view/ark_article_cover/retina/public/135025435.jpg?v=1585121965";
book1.Remark = "Buy Buy Buy";
data.BookInfos.Add(book1);
dynamic book2 = new ExpandoObject();
book2.No = "A2";
book2.RowNo = 2;
book2.Name = "Docker+Kubernetes Application Development and Cloud Deployment";
book2.EditorInChief = "Li Wenqiang";
book2.PublishingHouse = "China Machine Press";
book2.Price = 65;
book2.PurchaseQuantity = 50000;
book2.Cover = "https://img9.doubanio.com/view/ark_article_cover/retina/public/135025435.jpg?v=1585121965";
book2.Remark = "Buy Buy Buy";
data.BookInfos.Add(book2);
// Template path
var tplPath = Path.Combine(Directory.GetCurrentDirectory(), "TestFiles", "ExportTemplates",
"DynamicExportTpl.xlsx");
// Create Excel export instance
IExportFileByTemplate exporter = new ExcelExporter();
// Output file path
var filePath = Path.Combine(Directory.GetCurrentDirectory(), nameof(DynamicExportWithExpandoObjectByTemplate_Test) + ".xlsx");
if (File.Exists(filePath)) File.Delete(filePath);
// Export using template
await exporter.ExportByTemplate(filePath, data, tplPath);
Please refer to DynamicExportWithExpandoObjectByTemplate_Test for detailed implementation.
Final Notes
This concludes our tutorial. If you encounter any issues, feel free to submit them as GitHub issues.
Magicodes.IE: A versatile import/export library supporting Dto imports/exports, template exports, various export styles, and dynamic exports, compatible with Excel, Csv, Word, Pdf, and Html.
- GitHub: https://github.com/dotnetcore/Magicodes.IE
- Gitee (manually synced, no active maintenance): https://gitee.com/magicodes/Magicodes.IE
Please note that libraries are continuously updated, and there may be minor differences between current functionality and the content of this tutorial. Always refer to the actual code, version logs, and unit tests for the most accurate information.