第12周的NuGet软件包-从C＃和托管Razor模板的G＃访问带有GData的Google Spreadsheets从控制台应用程序生成HTML

Sometimes I write apps for charities on the side. Recently I've been doing some charity coding on the side for The Red Pump Project. They are a non-profit focused on raising awareness about the impact of HIV/AIDS on women and girls. I encourage you to check them out, donate some money, or join their mailing list.

有时我会一边为慈善机构编写应用程序。 最近我一直在为The Red Pump Project做一些慈善编码。 他们是一个非营利性组织，致力于提高人们对艾滋病毒/艾滋病对妇女和女童影响的认识。 我鼓励您检查他们，捐赠一些钱或加入他们的邮件列表。

Side Note: Folks often ask me how they can get more experience and wonder if Open Source is a good way. It is! But, charities often need code too! You may be able to have the best of both worlds. Donate your time and your code...and work with them to open source the result. Everyone wins. You get knowledge, the charity get results, the world gets code.

旁注：人们经常问我如何获得更多经验，并想知道开放源代码是否是一种好方法。 它是！ 但是，慈善机构也经常需要代码！ 您也许可以兼得两全。 捐出您的时间和代码...并与他们一起开源结果。 每个人都赢。 您获得知识，慈善事业获得成果，世界获得代码。

Anyway, this charity has a Google Spreadsheet that holds the results of a survey of users they take. You can create a Form from a Google Spreadsheet; it's a very common thing.

无论如何，这个慈善机构都有一个Google Spreadsheet，其中包含对他们所接受的用户进行调查的结果。 您可以从Google Spreadsheet创建表单； 这是很普通的事情。

In the past, they've manually gone into the spreadsheet and copied the data out then painstakingly - manually - wrapped the data with HTML tags and posted donors names (who have opted in) to their site.

过去，他们手动进入电子表格，然后将数据复制出来，然后再费力地-手动-用HTML标签包装数据，并将捐赠者的名字(选择加入)张贴到他们的网站上。

It got the point where this tedium was the #1 most hated job at The Red Pump Project. They wanted to recognize donors but they aren't large enough yet to have a whole donation platform CRM, instead opting to use Google Apps and free tools.

到了这个枯燥乏味的地步，是《红泵计划》中最令人讨厌的工作排名第一。 他们想认可捐赠者，但他们还不够庞大，没有一个完整的捐赠平台CRM，而是选择使用Google Apps和免费工具。

I figured I could fix this and quickly. Over a one hour Skype last night with Luvvie, one of The Red Pump Founders, we 'paired' (in that I wrote code and she validated the results as I typed) and made a little app that would loop through a Google Spreadsheet and make some HTML that was then uploaded to a webserver and used as a resource within their larger blogging platform.

我想我可以很快解决这个问题。 昨晚与Red Pump Founders之一的Luvvie进行了一个多小时的Skype通话，我们“配对”了(因为我编写了代码，并且她在输入时验证了结果)，并制作了一个可以在Google Spreadsheet中循环播放并制作一些HTML，然后将其上传到Web服务器，并在其较大的Blog平台中用作资源。

Yes there are lots of simpler and better ways to do this but keep in mind that this is the result of a single hour, paired directly with the "on site customer" and they are thrilled. It also gives me something to build on. It could later to moved into the cloud, automated, moved to the server side, etc. One has to prioritize and this solution will save them dozens of hours of tedious work this fund raising season.

是的，有许多更简单，更好的方法可以做到这一点，但请记住，这是一个小时的结果，直接与“现场客户”配对，他们感到很兴奋。 它还给了我一些基础。 它可以稍后移至云中，进行自动化，移至服务器端等。必须确定优先级，此解决方案将在本筹款季节为他们节省数十小时的繁琐工作。

Here's our hour.

这是我们的时间。

第1步-通过GDATA和C＃访问Google Spreadsheet (Step 1 - Access Google Spreadsheet via GDATA and C#)

I was not familiar with the Google GData API but I knew there was one.  I made a console app and downloaded the Google Data API installer. You can also get them via NuGet:

我不熟悉Google GData API，但我知道有一个。 我制作了一个控制台应用程序，并下载了Google Data API安装程序。 您也可以通过NuGet获取它们：

I added references to Google.GData.Client, .Extensions, and .Spreadsheets. Per their documentation, you have to walk and object model, traversing first to find the Spreadsheet with in your Google Drive, then the Worksheet within a single Spreadsheet, and then the Rows and Columns as Cells within the Worksheet. Sounds like moving around a DOM. Get a reference, save it, dig down, continue.

我添加了对Google.GData.Client，.Extensions和.Spreadsheets的引用。 根据他们的文档，您必须走动和对象建模，先遍历才能在Google云端硬盘中找到电子表格，然后在单个电子表格中查找工作表，然后在工作表中查找作为单元格的行和列。 听起来就像在DOM上移动。 获取参考，将其保存，深入挖掘，继续。

So, that's Drive -> Spreadsheet -> Worksheet -> Cells (Rows, Cols)

因此，这就是驱动器->电子表格->工作表->单元格(行，列)

The supporters of the Red Pump Project call themselves "Red Pump Rockers" so I have a class to hold them. I want their site, url and twitter. I have a "strippedSite" property which will be the name of their site with only valid alphanumerics so I can make an alphabet navigator later and put some simple navigation in a sorted list.

Red Pump Project的支持者自称为“ Red Pump Rockers”，所以我开设了一个班级来支持他们。 我想要他们的网站，URL和Twitter。 我有一个“ strippedSite”属性，该属性将是仅包含有效字母数字的站点名称，因此以后我可以制作一个字母导航器，并将一些简单的导航放入排序列表中。

public class Rocker
{
    public string site { get; set; }
    public string strippedSite { get; set; }
    public string url { get; set; }
    public string twitter { get; set; }
}

Again, this is not my finest code but it works well given constraints.

再说一次，这不是我最好的代码，但是在约束条件下效果很好。

var rockers = new List<Rocker>();

SpreadsheetsService myService = new SpreadsheetsService("auniquename");
myService.setUserCredentials([e-mail], "password");

// GET THE SPREADSHEET from all the docs
SpreadsheetQuery query = new SpreadsheetQuery();
SpreadsheetFeed feed = myService.Query(query);

var campaign = (from x in feed.Entries where x.Title.Text.Contains("thetitleofthesheetineed") select x).First();

// GET THE first WORKSHEET from that sheet
AtomLink link = campaign.Links.FindService(GDataSpreadsheetsNameTable.WorksheetRel, null);
WorksheetQuery query2 = new WorksheetQuery(link.HRef.ToString());
WorksheetFeed feed2 = myService.Query(query2);

var campaignSheet = feed2.Entries.First();

// GET THE CELLS

AtomLink cellFeedLink = campaignSheet.Links.FindService(GDataSpreadsheetsNameTable.CellRel, null);
CellQuery query3 = new CellQuery(cellFeedLink.HRef.ToString());
CellFeed feed3 = myService.Query(query3);

uint lastRow = 1;
Rocker rocker = new Rocker();

foreach (CellEntry curCell in feed3.Entries) {

    if (curCell.Cell.Row > lastRow && lastRow != 1) { //When we've moved to a new row, save our Rocker
            rockers.Add(rocker);
            rocker = new Rocker();
    }

    //Console.WriteLine("Row {0} Column {1}: {2}", curCell.Cell.Row, curCell.Cell.Column, curCell.Cell.Value);

    switch (curCell.Cell.Column) {
        case 4: //site
            rocker.site = curCell.Cell.Value;
            Regex rgx = new Regex("[^a-zA-Z0-9]"); //Save a alphanumeric only version
            rocker.strippedSite = rgx.Replace(rocker.site, "");
            break;
        case 5: //url
            rocker.url = curCell.Cell.Value;
            break;
        case 6: //twitter
            rocker.twitter = curCell.Cell.Value;
            break;
    }
    lastRow = curCell.Cell.Row;
}

var sortedRockers = rockers.OrderBy(x => x.strippedSite).ToList();

At this point I have thousands of folks who "Rock The Red Pump" in a list called sortedRockers, sorted by site A-Z. I'm ready to do something with them.

至此，我已经有成千上万的人在名为sortedRockers的列表中按“ AZ”排序了“红色摇滚”。 我准备对他们做点事。

第2步-生成HTML(首先是错误的，然后是Razor模板，然后是正确的) (Step 2 - Generate HTML (first wrong, then later right with Razor Templates))

They wanted a list of website names linked to their sites with an optional twitter name like:

他们想要一个链接到其站点的网站名称列表，并带有一个可选的twitter名称，例如：

Scott's Blog - @shanselman

斯科特的博客-@shanselman

I started (poorly) with a StringBuilder. *Gasp*

我(很差)从StringBuilder开始。 *喘气*

This is a learning moment, because it was hard and it was silly and I wasted 20 minutes of Luvvie's time. Still, it gets better, keep reading.

这是一个学习的时刻，因为这很艰难而且很愚蠢，我把Luvvie的时间浪费了20分钟。 尽管如此，它会变得更好，请继续阅读。

Here's what I wrote, quickly, and first. Don't judge, I'm being honest here.

这是我第一时间写的。 不要判断，我在这里很诚实。

foreach (Rocker r in sortedRockers){
    string strippedName = r.strippedSite;

    if (char.ToUpperInvariant(lastCharacter) != char.ToUpperInvariant(strippedName[0])) {
        sb.AppendFormat("<h2><a name=\"{0}\">{0}</a></h2>", char.ToUpperInvariant(strippedName[0]));
    }

    sb.AppendFormat("<a href=\"{1}\" target=\"_blank\">{0}</a>", r.site, r.url);
    
    if (!string.IsNullOrWhiteSpace(r.twitter)){
        r.twitter = r.twitter.Replace("@", "");
        sb.AppendFormat(" &mdash; <a href=\"http://twitter.com/{0}\">@{0}</a>", r.twitter);
    }
    sb.AppendFormat("<br>");

    lastCharacter = strippedName[0];
}
sb.AppendFormat("</body></html>");

This works fine. It's also nuts and hard to read. Impossible to debug and generally confusing. Luvvie was patient but I clearly lost her here.

这很好。 它也是坚果并且难以阅读。 不可能调试并且通常会造成混乱。 Luvvie很耐心，但我显然在这里失去了她。

I realized that I should probably have used Razor Templating from within my Console App for this. I asked on StackOverflow as well.

我意识到我应该在我的控制台应用程序中使用Razor模板。 我也问过StackOverflow 。

UPDATE: There's a great answer by Demis from ServiceStack on StackOverflow showing how to use ServiceStack and Razor to generate HTML from Razor templates.

更新： StackOverflow上ServiceStack的Demis提供了一个很好的答案，展示了如何使用ServiceStack和Razor从Razor模板生成HTML 。

I ended up using RazorEngine, largely because of the promise of their first two lines of code on their original home page.  There is also RazorMachine, Nancy, and a post by Andrew Nurse (author of much of Razor itself) as other options.

我最终使用了RazorEngine ，这主要是因为在原始主页上承诺了前两行代码。 其他选项还有RazorMachine ， Nancy和Andrew Nurse (Razor本身的许多作者)的帖子。

But, these two lines right at their top of their site were too enticing to ignore.

但是，这两行位于其站点顶部，非常诱人，无法忽略。

string template = "Hello @Name.Name! Welcome to Razor!";
string result = Razor.Parse(template, new { Name = "World" });

(Open Source Developers Take Heed: Where's the easy quickstart code sample on your home page?)

(开源开发人员请注意：您的首页上的简单快速入门代码示例在哪里？)

This allowed me to change all that StringBuilder goo above into a nice clear Razor template in a string. I also added the alphabet navigation and the letter headers easily.

这使我可以将上面的所有StringBuilder粘性更改为字符串中清晰的Razor模板。 我还轻松添加了字母导航和字母标题。

<html><head><link rel="stylesheet"" href="style.css" type="text/css" media="screen"/></head><body>

    //Navigation - A B C D, etc.
    @foreach(char x in ""ABCDEFGHIJKLMNOPQRSTUVWXYZ"".ToList()) {
        <a href=""#@x"">@x</a>
    }
    
    @functions {
        //need @functions because I need this variable in a wider scope
        char lastCharacter = '0';    
    }

    @foreach(var r in Model) {
        var theUpperChar = char.ToUpperInvariant(r.strippedSite[0]);

        //Make a capital letter "heading" when letters change
        if (lastCharacter != theUpperChar) {
            <h2><a name="@theUpperChar">@theUpperChar</a></h2>
        }

        <a href="@r.url" target="_blank">@r.site</a>

        if (!string.IsNullOrWhiteSpace(r.twitter)) {   
            var twitter = r.twitter.Replace("@", String.Empty);
            <text>&mdash;</text> <a href="http://twitter.com/@twitter">@twitter</a>
        }
        <br/>
        lastCharacter = theUpperChar;
    }
</body></html>

And the "do it" code ended up being:

而“执行”代码最终是：

string result = Razor.Parse(template, sortedRockers);
File.WriteAllText("2013list.html", result);

StringBuilders are fine, to a point. When it gets hairy, consider a templating engine of some kind.

在某种程度上，StringBuilders很好。 当它变得毛茸茸时，请考虑使用某种模板引擎。

第3步-使用C＃通过FTP上传文件 (Step 3 - Upload a File with FTP with C#)

Now what? They want the little app to upload the result. Mads Kristensen to the rescue 7 years ago!

怎么办？ 他们希望这个小应用上传结果。 Mads Kristensen 7年前开始营救！

private static void Upload(string ftpServer, string userName, string password, string filename)
{
   using (System.Net.WebClient client = new System.Net.WebClient())
   {
      client.Credentials = new System.Net.NetworkCredential(userName, password);
      client.UploadFile(ftpServer + "/" + new FileInfo(filename).Name, "STOR", filename);
   }
}

Then it's just

那只是

Upload("ftp://192.168.1.1", "UserName", "Password", @"2013list.html");

结论 (Conclusion)

You can see that this is largely a spike, but it's a spike that solves a problem, today. Later we can build on it, move it to a server process, build a front end on it, and come up with more ways for them to keep  using tools like Google Spreadsheet while better integrating with their existing websites.

您可以看到，这在很大程度上是一个峰值，但这是今天解决问题的峰值。 稍后，我们可以在此基础上构建，将其移至服务器进程，在其上构建前端，并为他们提供更多方式来继续使用Google Spreadsheet等工具，同时更好地与现有网站集成。

Consider donating your coding time to your favorite local charity today!

考虑今天将您的编码时间捐赠给您最喜欢的当地慈善机构！

翻译自: https://www.hanselman.com/blog/nuget-packages-of-the-week-12-accessing-google-spreadsheets-with-gdata-from-c-and-hosting-razor-templates-to-generate-html-from-a-console-app