IT:AD:T4:HowTo:Import Data From an Excel File

(UP)

Summary

Excel's are the files of preference of BAs on a project, so it makes sense to let them populate them with Resources, MessageCodes, etc. – and use T4 to import them.

Use Nuget to add LinqToExcel to your project.

Use a spreadsheet with each sheet named (without spaces), containing cells as follows:

Group	Key	Value
GrpA	KeyA	Foo!
GrpB	KeyB	Bar!
GrpA	KeyC	Bum!

Create a new T4 file containing something similar to the one below.

we're including the referenced Nuget assemblies, and the assemblies they in turn rely on.
- don't use the output bin as the source (it would always fail the first time after a clean, therefore fail on a build server).
it's template@host specific
it's using a file manager to collect blocks of output (one per target .cs file) it's using the file manager to commit those blocks at the end. </callout>

<callout icon=“true” type=“tip”

>

I

was surprised at how fragile T4 was regarding assemblies. It crashed visual studio hard if the Log4Net reference was not included.


<#@ template debug="true" hostspecific="true" language="C#v3.5" #>
<#@ include file="EF.Utility.CS.ttinclude" #>
<#@ assembly name="$(ProjectDir)..\packages\log4net.2.0.3\lib\net40-full\Log4Net.dll" #>
<#@ assembly name="$(ProjectDir)..\packages\LinqToExcel.1.10.1\lib\Remotion.dll" #>
<#@ assembly name="$(ProjectDir)..\packages\LinqToExcel.1.10.1\lib\Remotion.Interfaces.Dll" #>
<#@ assembly name="$(ProjectDir)..\packages\LinqToExcel.1.10.1\lib\Remotion.Data.Linq.Dll" #>
<#@ assembly name="$(ProjectDir)..\packages\LinqToExcel.1.10.1\lib\LinqToExcel.Dll" #>
<#@ assembly name="System.Core" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="LinqToExcel" #>
<#@ import namespace="LinqToExcel.Query" #>
<#@ assembly name="EnvDTE" #>
<#@ output extension=".txt" #>
<#
//==================================================
		//Step: determin the namespace to use for this file.
		var hostServiceProvider = (IServiceProvider)Host;
		var dte = 
			(EnvDTE.DTE)hostServiceProvider.GetService(typeof(EnvDTE.DTE));
		var activeSolutionProjects = (Array)dte.ActiveSolutionProjects;
		var dteProject = (EnvDTE.Project)activeSolutionProjects.GetValue(0);
		var defaultNamespace =  dteProject.Properties.Item("DefaultNamespace").Value;
		var templateDir = Path.GetDirectoryName(Host.TemplateFile);
		var fullPath = dteProject.Properties.Item("FullPath").Value.ToString();
		fullPath = 
			fullPath.EndsWith("\\") 
				? fullPath.Substring(0, fullPath.Length-1) 
				: fullPath;
		var subNamespace = 
			templateDir.Replace(fullPath, string.Empty).Replace("\\", ".");
		var fileNamespace = string.Concat(defaultNamespace, subNamespace);
//==================================================
#>
README
======
This file is automatically generated by ResourceGenerator.tt

* Input: `.\Source\Resources.xlsx` folder
* Output: a set of *.cs files (one per Sheet in the Source Excel file) 
<#
//==================================================
		// T4 templates can spit out one or more pages.
		// In this case, we want one page per Spreadsheet tab.
		// For that, without requiring dependencies on 3rd party ttincludes, 
		// we can use a fileManager:

		EntityFrameworkTemplateFileManager entityFrameworkTemplateFileManager = EntityFrameworkTemplateFileManager.Create(this);
//==================================================
		// Find the Source Excel File:

		// Find the Excel file's path, relative to current host template:
		string fileName = Host.ResolvePath(".\\Source\\Resources.xlsx");


		// Open Excel file as Queryable object:
		var excelQueryFactory = new ExcelQueryFactory(fileName);

		// Get list of Sheets. which will be basis of classes to create:
		string[] sheetNames = excelQueryFactory.GetWorksheetNames().ToArray();


		// Iterate through sheet names, creating a new code file each time:
		foreach(string sheetName in sheetNames)
		{
			//Get the queryable sheet:
			var sheet = excelQueryFactory.Worksheet(sheetName);
			
			// From the sheet, get all the Resources defined on it:
            // var resourceDefinitions = sheet.Where(x => x["Group"].Cast<string>() == "GrpA").ToArray();
			var resourceDefinitions = sheet.ToArray();

			// Create a file called 'Resources_Whatever.cs', beside this file:
			entityFrameworkTemplateFileManager.StartNewFile( string.Format("Resources_{0}.cs",sheetName));
//==================================================
 #>
using System;
using System.Data.Entity;
using XAct.Library.Settings;
using XAct.Resources;
using System.Data.Entity.Migrations;
using App.Core.Infrastructure.Services;

namespace <#=fileNamespace#>
{
	public class Seeder_<#=sheetName#> 
	{
        public void Seed(DbContext dbContext)
        {
			var resources = dbContext.Set<XAct.Resources.Resource>();
<#           
             foreach (var r in resourceDefinitions)   
             {
#>
				AddOrUpdateResource(resources, "<#=r["Group"]#>", "<#=r["Key"]#>", "<#=r["Value"]#>");
<# 
             }
#>
             dbContext.SaveChanges();
		}
	    private void AddOrUpdateResource(DbSet<Resource> resources, string group, string key, string value)
	    {
            if (XAct.Library.Settings.Db.SeedingType >= SeedingType.ResetImmutableReferenceData)
            {
                //Help Rescource Generated on 06/05/14 Help text matrix v1.1 04/05/14
                resources.AddOrUpdate(
					m => new { m.Filter, m.Key, m.CultureCode }, 
					new Resource(GenGuid(), group, key, value, "")
				);
			}
	    }

		private static Guid GenGuid()
        {
            var result = XAct.DependencyResolver.Current.GetInstance<IDistributedIdService>().NewGuid();

            return result;
        }

	}//~class
}//~ns
<#
//==================================================
			entityFrameworkTemplateFileManager.EndBlock();
            }//~loop through sheetNames
//==================================================
		// Important: Process the blocks, and convert them to output files:
		entityFrameworkTemplateFileManager.Process(true);
//==================================================
#>

The above output produces individual files, one per worksheet filename.

Each one approximately as follows. Neat.


using System;
using System.Data.Entity;
using XAct.Library.Settings;
using XAct.Resources;
using System.Data.Entity.Migrations;
using App.Core.Infrastructure.Services;

namespace App.Core.Infrastructure.Data.Seeders.Imported
{
	public class Seeder_ExHelp 
	{
        public void Seed(DbContext dbContext)
        {
			var resources = dbContext.Set<XAct.Resources.Resource>();
				AddOrUpdateResource(resources, "GrpA", "KeyA", "Foo");
				AddOrUpdateResource(resources, "GrpB", "KeyB", "Bar");
				AddOrUpdateResource(resources, "GrpA", "KeyC", "Drink");
				AddOrUpdateResource(resources, "GrpA", "KeyD", "Stuff");
             dbContext.SaveChanges();
		}
	    private void AddOrUpdateResource(DbSet<Resource> resources, string group, string key, string value)
	    {
            if (XAct.Library.Settings.Db.SeedingType >= SeedingType.ResetImmutableReferenceData)
            {
                //Help Rescource Generated on 06/05/14 Help text matrix v1.1 04/05/14
                resources.AddOrUpdate(
					m => new { m.Filter, m.Key, m.CultureCode }, 
					new Resource(GenGuid(), group, key, value, "")
				);
			}
	    }

		private static Guid GenGuid()
        {
            var result = XAct.DependencyResolver.Current.GetInstance<IDistributedIdService>().NewGuid();

            return result;
        }

	}//~class
}//~ns

IT:AD:T4:HowTo:Import Data From an Excel File

Summary

Process

Input

T4 Transformer

Output

Resources

Notes