Skip to content

spreadsheets with opendocument format

Xlsx files are basically zip files.

You’ll need to add the following using statements:
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

(additionally you’ll need to add references to WindowsBase (for the compression), and DocumentFormat.OpenXML which can be downloaded from the open xml sdk: http://www.microsoft.com/en-gb/download/details.aspx?id=30425)

Basic spreadsheet parsing for the openDocument standard can use with DOM or SAX.


using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(physicalPath, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
foreach (WorksheetPart wsp in workbookPart.WorksheetParts)
{
SheetData sheetData = wsp.Worksheet.Elements().First();

foreach (Row row in sheetData.Elements())
{
checkInitialSpans(row);
Score s = new Score();
// workbook needs to be passed through as spreadsheet strings are not stored in the cell, but a separate lookup table.
s.ScoreName = row.getScoreName(workbookPart);
s.ScoreValue = row.getScoreValue();
product.Scores.Add(s);
}
}
}

The biggest thing to notice is that strings are stored in a different location, and you you’ll end up wondering why you always get back “0”.


public static double getScoreValue(this Row r)
{
double cellValue = -1;

Cell c = (Cell)r.ElementAt(1);
cellValue = double.Parse(c.CellValue.Text);
return cellValue;
}

public static string getScoreName(this Row r, WorkbookPart workbookPart)
{
string cellText = string.Empty;
Cell c = (Cell)r.ElementAt(0);
if (c != null && c.DataType != null && c.DataType == CellValues.SharedString)
{
int id = -1;
Int32.TryParse(c.InnerText, out id);
var item = GetSharedStringItemById(workbookPart, id);
if (item.Text != null)
{
cellText = item.Text.Text;
}
else if (item.InnerText != null)
{
cellText = item.InnerText;
}
else if (item.InnerXml != null)
{
cellText = item.InnerXml;
}
}
return cellText;
}

public static SharedStringItem GetSharedStringItemById(WorkbookPart workbookPart, int id)
{
return workbookPart.SharedStringTablePart.SharedStringTable.Elements().ElementAt(id);
}

Accessing via SAX, would be:

using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(product.SystemFileName, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
var sheetCount = workbookPart.Workbook.Sheets.Count();
foreach (WorksheetPart wsp in workbookPart.WorksheetParts)
{
var columnCount = wsp.Worksheet.Descendants().Count();
var rowCount = wsp.Worksheet.Descendants().Count();
var cellCount = wsp.Worksheet.Descendants().Count();

OpenXmlReader reader = OpenXmlReader.Create(wsp);
string text;
while (reader.Read())
{
text = reader.GetText();
System.Diagnostics.Debug.WriteLine(text + "");
}
}
}

For more information about these methods, see the example at MSDN: https://msdn.microsoft.com/en-us/library/office/gg575571.aspx

Alternatively there are several thirdparty libraries (like: https://github.com/ExcelDataReader/ExcelDataReader) or there is the ever present OLEDB or SSIS.

Parsing via OLEDB would be something like:
private static DataTable ParseOldDb(string physicalPath)
{
string conStr = "Provider=Microsoft.ACE.OLEDB.12.0;data source='" + physicalPath + "';Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\" ";
string cmd = "select * from [sheet1$]";
var dt = new DataTable();

using (OleDbConnection cn = new OleDbConnection(conStr))
{
cn.Open();
DataTable schema = cn.GetSchema();

using (OleDbDataAdapter ad = new OleDbDataAdapter(cmd, cn))
{
ad.Fill(dt);
return dt;
}
}
}

JsonCamelCaseFormatter (hows that for an ironically cased title)

In the test web api projects I’ve been adjusting the formatting sligtly in the Startup file (configuration method):

var config = GlobalConfiguration.Configuration;
var xmlFormatter = config.Formatters.XmlFormatter;
config.Formatters.Remove(xmlFormatter);

var jsonFormatter = config.Formatters.JsonFormatter;
var JsonmediaTypeformatter = System.Net.Http.Formatting.MediaTypeFormatter.GetDefaultValueForType(typeof(JsonMediaTypeFormatter));

JsonSerializerSettings jsonSettings = new JsonSerializerSettings();
jsonSettings.ContractResolver = new CamelCasePropertyNamesContractResolver();

jsonFormatter.SerializerSettings = jsonSettings;

Its even appearing in umbraco now!
I was pleased to see the inclusion of the [JsonCamelCaseFormatter] attribute in umbraco (add to the class of a webAPI0.

umbraco 7.2 Custom Sections (part of developer section)

a bit of a custom section in angular (it doesn’t do much), but it does show how to create a custom section in v7 with angularjs.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net.Http.Formatting;
using System.Text;
using System.Web;
//using umbraco.businesslogic;
using umbraco.BusinessLogic.Actions;
using umbraco.interfaces;
using Umbraco.Core.Services;
using Umbraco.Web.Models.Trees;
using Umbraco.Web.Mvc;
using Umbraco.Web.Trees;
using Umbraco.Core;
using Umbraco.Core.Models;
using Umbraco.Web.WebApi.Filters;
using umbraco;
using umbraco.BusinessLogic.Actions;

// note that this relates to:
App_Plugins
+-+
–+-umbAltFileEditor (got from the plugincontroller attribute area name)
—-+-BackOffice
——-+ umbAltController (got from the tree attribute string alias)


[Tree("developer", "umbAltController", "umb alt controller")]
[PluginController("umbAltFileEditor")]
public class umbAltController : Umbraco.Web.Trees.TreeController
{

protected override TreeNodeCollection GetTreeNodes(string id, FormDataCollection queryStrings)
{
// check if we're rendering the root node's children
if (id == Constants.System.Root.ToInvariantString())
{
// empty tree
var tree = new TreeNodeCollection();

var path = HttpContext.Current.Server.MapPath("~/AngularJSViews");
var folder = new DirectoryInfo(path);

foreach (FileInfo fi in folder.GetFiles())
{
var fileitem = this.CreateTreeNode(fi.FullName, "-1", queryStrings, fi.Name, "icon-truck", false);
tree.Add(fileitem);
}

return tree;
}
throw new NotSupportedException();
}

protected override MenuItemCollection GetMenuForNode(string id, FormDataCollection queryStrings)
{
// create my menu item "Import"
var menu = new MenuItemCollection();
// duplicate this section for more than one icon
menu.DefaultMenuAlias = ActionNew.Instance.Alias;
return menu;
}

}

package.manifest (/app_plugins/umbaltfileeditor/package.manifest):
{
javascript: [
'~/App_Plugins/umbAltFileEditor/BackOffice/umbAltController/umbAltFileEditorController.js'
]
}

angular controller (/app_plugins/umbaltfileeditor/BackOffice/umbAltController/umbAltFileEditorController.js):

'use strict';
(function () {
//create the controller
function umbAltFileEditorListController($scope, $routeParams, $http) {
//set a property on the scope equal to the current route id
$scope.id = $routeParams.id;
$scope.message = "fred1";

};
//register the controller
angular.module("umbraco").controller('umbAltFileEditorListController', umbAltFileEditorListController);

})();

Thanks in part to the work of:

https://our.umbraco.org/Documentation/Extending-Umbraco/Property-Editors/creating-tutorial1-v7

http://siempresolutions.co.uk/blog/Extending_Umbraco_Custom_Tree_and_MVC_API_Part_1

http://www.nibble.be/

…. and I want to have a read of this one too:

http://shazwazza.com/post/uploading-files-and-json-data-in-the-same-request-with-angular-js/

IoC Autofac OWIN MVC experiments

Ninject is coming out as the a slow container nowdays. I also noted that in a quick test it wasn’t calling the dispose method – though this could have been a quick test implementation.

Startup.cs (standard OWIN created MVC file)
Not sure whether this is the best place to put this, but it does give me access to the IAppBuilder interface

using Microsoft.Owin;
using MVCAutoFacTest.App_Start;
using Owin;

[assembly: OwinStartupAttribute(typeof(MVCAutoFacTest.Startup))]
namespace MVCAutoFacTest
{
    public partial class Startup
    {
        public void Configuration(IAppBuilder app)
        {
            IoCConfig.RegisterDespendencies(app);
            ConfigureAuth(app);
        }
    }
}

IoCConfig.cs

using Autofac;
using Autofac.Integration.Mvc;
using Autofac.Integration.Owin;
using MVCAutoFacTest.Controllers;
using MVCAutoFacTest.Services;
using Owin;
using Repositories;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace MVCAutoFacTest.App_Start
{
    public class IoCConfig
    {

        //http://stackoverflow.com/questions/15226536/register-generic-type-with-autofac//


        public static void RegisterDespendencies(IAppBuilder app)
        {
            var builder = new ContainerBuilder();
            var mvcAssembly = typeof(MvcApplication).Assembly;
            var repoAssembly = typeof(RepositoryBase).Assembly; // need any type of the project to get the assembly id. 

            builder
                .RegisterType(typeof(CustomDbContext))
                .AsSelf()
                .InstancePerLifetimeScope();

            builder
               .RegisterGeneric(typeof(Repository))
               .As(typeof(IRepository));

            builder.RegisterGeneric(typeof(UnitOfWork))
                .As(typeof(IUnitOfWork));


            builder.RegisterAssemblyTypes(mvcAssembly)
                .Where(t => t.Name.EndsWith("Service"))
                .AsImplementedInterfaces();

            //builder
            //    .RegisterType(typeof(TestService))
            //    .As()
            //    .WithParameter<IRepository>();

            //builder
            //    .RegisterType()
            //    .As();


            builder.RegisterControllers(mvcAssembly);
    
            var container = builder.Build();
            
            DependencyResolver.SetResolver(new AutofacDependencyResolver(container));
                        
            app.UseAutofacMiddleware(container);
            app.UseAutofacMvc();
        }
    }
}


IRepository.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;

namespace Repositories
{
    public interface IRepository : IDisposable
    {
        string GetRepoName();
    }
}

Repository.cs

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Repositories
{
    //http://techbrij.com/generic-repository-unit-of-work-entity-framework-unit-testing-asp-net-mvc
    public class Repository : IRepository 
    {
        public string GetRepoName(CustomDbContext dbContext)
        {
            return typeof(T).Name;
        }

        public void Dispose()
        {
        }

        public string GetRepoName()
        {
            throw new NotImplementedException();
        }
    }
}


IUnitOfWork.cs (interface should be in separate file)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Repositories
{
    public interface IUnitOfWork: IDisposable  where E : class
    {
        void SaveChanges();
        string GetRepoName();
    }
    public class UnitOfWork : IUnitOfWork where E: class // E should be baseEntity
    {
        private readonly IRepository repository;

        public UnitOfWork(CustomDbContext dbContext, IRepository repository)
        {
            this.repository = repository;
        }


        public void SaveChanges()
        {
            throw new NotImplementedException();
        }


        public string GetRepoName()
        {
            return repository.GetRepoName();
        }

        public void Dispose()
        {
            
        }
    }
}


RepositoryBase.cs
Currently only used to be able to get the assembly name for registrations.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Repositories
{
    public class RepositoryBase
    {
    }
}


HomeService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Repositories;
using ProjectModels.DbModels;
namespace MVCAutoFacTest.Services
{
    public interface IHomeService
    {
        string GetStuff();
    }
    
    public class HomeService : IHomeService
    {
        IRepository _repository;
        IUnitOfWork _unitOfWork;

        public HomeService(IRepository repository, IUnitOfWork unitOfWork)
        {
            this._repository = repository;
            this._unitOfWork = unitOfWork;
        }

        public string GetStuff()
        {
            return "stuff from home service";
        }
    }
}


HomeController.cs

using MVCAutoFacTest.Models;
using MVCAutoFacTest.Services;
using Repositories;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace MVCAutoFacTest.Controllers
{
    public class HomeController : Controller
    {
        IHomeService _homeService;
        public HomeController(IHomeService homeService)
        {
            this._homeService = homeService;
        }
        public ActionResult Index()
        {
            var message = string.Format("{0}", _homeService.GetStuff());
            ViewBag.Message = message;

            return View();
        }
    }
}

So far, I’ve liked what i’ve seen of autofac. Need to play with cross cutting concerns, and filters. Also need to thing about how best to implement the Repository and UnitOfWork patterns. Finally, I’m tempted to put the configuration in the web.config file in a custom section. It wouldn’t need to do everything, but injectible classes should not need to require the project to be recompiled.

sql reindexing…

EXECUTE sp_MSforeachtable ‘DBCC DBREINDEX  ([?])';

Google Tag Manager

Used to inject “code” into a website. Useful to put things like google analytics or double click tracking code into site, but may be used for injecting custom tracking code.

Note the Firing rules on the right hand side is set to all page. This is essential. You also need to put in the Tracking Id which can be found in Google analytics.

tagmanager1

Add a click listener. This means that you can hook into click events throughout the site:

tagmanager2

Add the actual event that you want to track:

tagmanager1tagmanager3

Tools and stuff

SQL

Red-gate sql comparison bundle.

http://www.sql-format.com/

Json

http://www.jsoneditoronline.org/

Rest

https://www.getpostman.com/

https://chrome.google.com/webstore/detail/postman-rest-client/fdmmgilgnpjigdojojpjoooidkmcomcm

JS

JSFiddle

JSLint

Boilerplate

bootstrap

http://www.initializr.com/

Follow

Get every new post delivered to your Inbox.