Weekend Challenge #1: CSV to a Web Page

If you know programming and want to see samples of my work, you can read this blog. If you are not a programmer and want to evaluate my skills, there is nothing out there that I can show you. I haven’t worked on a public web site for over 3 years. My current projects are internal Windows Forms applications. You could load Improv Suggester onto your Windows Phone, if you have one.

So last weekend I challenged myself to write a public sample. I wanted to challenge myself to work in technologies that I haven’t worked in for awhile if ever.

Problem

Suppose you have a system that uses Excel spreadsheets to store data. Now, suppose that you want to transition to a web based system but you want to be able to fill out an existing spreadsheet and “Import” the data into the new system.

The Tools

Web Matrix 2

Editor – Half way between Visual Studio and notepad. The editor felt bulky without the features I would expect in exchange for the bulk. There were a couple of times when the editor was a dozen or so keystrokes behind me. Publishing to the web is easy.

Link to Web Matrix 2

Razor Rendering Engine

Language & Rendering Engine – It is interesting how HTML, JavaScript and C# are mixed together. It looks cleaner than ASP.NET, PHP or T4 markup, though I wish the JavaScript looked more different than C#, but that may be an editor issue.

Link to Razor

Hanson Table

Grid Control – This was the first JQuery control that I found that behaved the way I needed it to. There were other grids that allowed you to edit the cells but forced you to click on the cell you wanted to edit.

Link to Hanson Table

Lumenworks (CsvReader)

Parse CSV – This is somewhat of a cheat. I have used this library on a couple of other projects. It has this cool feature that allows you to reference columns by name, assuming that these names are on the first line. Since I wanted to allow the user to put global data at the top of the sheet, I looked for the headers and that was the first line that I gave to CsvReader.

Link to Lumenworks on Code Project

The Code

According to the rules that I have imposed on myself, I give you the code as it existed as of the end of August 19. You can see the sample in action on my main site.

@using Microsoft.Web.Helpers;
@using System.Web;
@using System.IO;
@using LumenWorks.Framework.IO.Csv;
@using System.Text;
@{
    Layout = "~/_SiteLayout.cshtml";
Page.Title = "Upload Grid Demo";

var make = "Make";
var date = string.Empty;
var submittedData = "";
var fileName = "";
var content = "";
var dataArray = "";
if (IsPost) 
{
var _txtMake = Request.Form["_txtMake"];

if(_txtMake != null)
{
submittedData = "Make: " + Request.Form["_txtMake"] + Environment.NewLine +
"Date: " + Request.Form["_txtDate"] + Environment.NewLine +
"Grid Data: " + Environment.NewLine +
Request.Form["_hidGridJson"];
}
else
{        
var fileSavePath = "";
var uploadedFile = Request.Files[0];
fileName = Path.GetFileName(uploadedFile.FileName);

StreamReader sr1 = new StreamReader(uploadedFile.InputStream);
string st = sr1.ReadToEnd();

Dictionary<string,string> topParams;
string lineItems;

getParamsAndGridDetail(st, out topParams, out lineItems);

Stream st2 = GenerateStreamFromString(lineItems);
TextReader tr = new StreamReader(st2);

dataArray = getGridDataForTextReader(tr);

if (!topParams.TryGetValue("Make:", out make))
{
make = string.Empty;
}
if (!topParams.TryGetValue("Date:", out date))
{
date = string.Empty;
}
}
}
}
@functions{
    public void getParamsAndGridDetail(string allLines, out Dictionary<string,string>
topParams, out string lineItems)
{
topParams = new Dictionary<string,string>();
StringBuilder sb = new StringBuilder();
string[] linesArray=allLines.Replace("\r", string.Empty).Split(new char[]{'\n'});
int stepNumber = 1;
foreach(string line in linesArray)
{
if (stepNumber == 1)
{
if (isHeaderLine(line))
{
sb.AppendLine(line);
stepNumber = 2;
}
else
{
lookForKeys(line, ref topParams);
}
}
else if (stepNumber == 2)
{
if(line.Replace(",", string.Empty).Trim().Length == 0)
{
stepNumber = 3;
}
else
{
sb.AppendLine(line);
}
}
}
lineItems = sb.ToString();
}
public void lookForKeys(string line, ref Dictionary<string,string> topParams)
{
string[] sumItems = line.Split(new char[] {','});
//foreach(string cell in sumItems)
for (int i = 0; i < sumItems.Length - 1; ++i)
{
if(sumItems[i].EndsWith(":"))
{
topParams.Add(sumItems[i], sumItems[i + 1]);
}
}
}
public bool isHeaderLine(string line)
{
bool foundModel = false;
bool foundYear = false;
bool foundColor = false;
string[] sumItems = line.Split(new char[] {','});
foreach(string cell in sumItems)
{
string normalizedCell = cell.Trim().ToLower();
if(normalizedCell == "model")
{
foundModel = true;
}
if(normalizedCell == "year")
{
foundYear = true;
}
if(normalizedCell == "color")
{
foundColor = true;
}
}
return foundModel && foundYear && foundColor;
}
public Stream GenerateStreamFromString(string s)
{
MemoryStream stream = new MemoryStream();
StreamWriter writer = new StreamWriter(stream);
writer.Write(s);
writer.Flush();
stream.Position = 0;
return stream;
}
public string getGridDataForTextReader(TextReader tr)
{
var sb = new StringBuilder();
sb.AppendLine("[");
using (var csv = new CsvReader(tr, true))
{
bool isFirstLine = true;
int fieldCount = csv.FieldCount;
while (csv.ReadNextRecord())
{
if(!isFirstLine)
{
sb.AppendLine(",");
}
isFirstLine = false;
sb.Append("        [");
sb.AppendFormat("\"{0}\",{1},\"{2}\"", csv["Model"], csv["Year"], csv["Color"]);
sb.Append("]");
}
}
sb.AppendLine("");
sb.Append("    ];");
return sb.ToString();
}
}
@section Scripts
{
<script src="@Href("~/lib/jquery.js")"></script>
<script src="@Href("~/jquery.handsontable.js")"></script>
<script src="@Href("~/lib/bootstrap-typeahead.js")"></script>
<script src="@Href("~/lib/jquery.autoresize.js")"></script>
<script src="@Href("~/lib/jQuery-contextMenu/jquery.contextMenu.js")"></script>
<script src="@Href("~/lib/jQuery-contextMenu/jquery.ui.position.js")"></script>
<link rel="stylesheet" media="screen" 
href="/lib/jQuery-contextMenu/jquery.contextMenu.css">
<link rel="stylesheet" media="screen" href="/jquery.handsontable.css">
}
    @FileUpload.GetHtml(
initialNumberOfFiles:1,
allowMoreFilesToBeAdded:false,
includeFormTag:true,
uploadText:"Parse CSV File")
@if (IsPost) {
<br/>
<span>File uploaded!</span>
<h3>@fileName</h3>
}
@if (string.IsNullOrEmpty(submittedData)){
<form action="" method="post" name="_formWriteBack" onclick="doOnSubmit();"  >
<input type="hidden" name="_hidGridJson" id="_hidGridJson" />
Make: <input name="_txtMake" id="_txtMake" value="@make" /><br />
Date: <input name="_txtDate" id="_txtDate" value="@date" /><br />
<div id="example1grid" class="dataTable"></div>
<script>
var $container = $("#example1grid");
var $parent = $container.parent();
$container.handsontable({
rows: 2,
cols: 3,
minSpareCols: 0, //always keep at least 0 spare row at the right
minSpareRows: 1, //always keep at least 1 spare row at the bottom
contextMenu: true,
colHeaders: ["Model", "Year", "Color"],
autoComplete: [
{
match: function (row, col, data) {
if (col == 2) {
return true;
}
return false;
},
highlighter: function (item) {
var query = this.query.replace(/[\-\[\]{}()*+?.,\\\^$|#\s]/g, '\\$&');
var label = item.replace(new RegExp('(' + query + ')', 'ig'), function ($1, match) {
return '<strong>' + match + '</strong>';
});
return '<span style="margin-right: 10px; background-color: ' +
item + '">&nbsp;&nbsp;&nbsp;</span>' + label;
},
source: function () {
return ["Yellow", "Red", "Orange", "Green", "Blue", "Gray", "Black", "White"]
},
strict: false //allows other values that defined in array above
}
]

});

var data = @Html.Raw(dataArray)
$container.handsontable("loadData", data);

function doOnSubmit() {
var newdata;
newdata = $container.handsontable("getData", "");
_hidGridJson.value = newdata;
}
</script>
<input type="submit" value="Submit Form" />
</form>
}
@if (!string.IsNullOrEmpty(submittedData)){
<p>This is the data you just submitted to the web server. </p>
<pre>
@submittedData
</pre>
}