/// <summary>
/// Inserts all the contents of a supplied cbCompanyObject list in the active cell, formating the permalink to be www.crunchbase.com/#space#/#permalink#
/// </summary>
/// <param name="cbcompanylist">A list of cbCompanyObject</param>
/// <param name="space">string used to specify part of crunchbase namespace</param>
/// <param name="activecell">An excel range where the list should be inserted</param>
private static void InsertCompaniesAtCell(List <cbCompanyObject> cbcompanylist, string space, Microsoft.Office.Interop.Excel.Range activecell)
{
int countcompany = cbcompanylist.Count + 1;
string[,] companies = new string[countcompany, 1];
string[,] permalink = new string[countcompany, 1]; // actual hyperlink to crunchbase entry
string[,] shortperma = new string[countcompany, 1]; // just short part of permalink
// put headers in the 0 row
companies[0, 0] = "Company Name";
shortperma[0, 0] = "Short Permalink";
permalink[0, 0] = "Company Permalink";
int row = 1;
foreach (cbCompanyObject c in cbcompanylist)
{
companies[row, 0] = c.name;
shortperma[row, 0] = c.permalink;
if (c.permalink.Length < 200)
{
permalink[row++, 0] = "=HYPERLINK(\"" + "http://www.crunchbase.com/" + space + "/" + c.permalink + "\")";
}
else
{
permalink[row++, 0] = "ERROR: TOO LONG FOR EXCEL";
}
}
var curr = activecell.Worksheet;
var startCell = activecell;
var endCell = curr.Cells[startCell.Row + row - 1, startCell.Column];
var writeRange = curr.Range[startCell, endCell];
writeRange.Value2 = companies;
//format the permalink column as a hyperlink (for slow way)
startCell = curr.Cells[startCell.Row, startCell.Column + 1]; // set just to the 2nd column in the range
endCell = curr.Cells[startCell.Row, startCell.Column];
var writePermalinkHeader = Globals.ThisAddIn.Application.ActiveCell.get_Offset(0, 1);
writePermalinkHeader.Value2 = permalink[0, 0];
//
startCell = curr.Cells[startCell.Row + 1, startCell.Column]; // set just to the 2nd column in the range
endCell = curr.Cells[startCell.Row + row - 1, startCell.Column];
var hyperlinkcol = curr.Range[startCell, endCell];
//the fast way that doesn't work
//hyperlinkcol.FormulaLocal = permalink;
// THE SLOW WAY
System.DateTime slowstart = System.DateTime.Now;
for (int i = 1; i < countcompany; i++)
{
hyperlinkcol.Item(i).Formula = permalink[i, 0];
}
Console.Error.WriteLine("time to format hyperlinks the slow way {0}", System.DateTime.Now - slowstart);
//hack hack add the shortperma column
startCell = curr.Cells[activecell.Row, activecell.Column + 2]; //2 over
ThisAddIn.OutputArrayToExcel(startCell, shortperma);
// add stuff here-->endCell=curr.
}
private void CompanyDetailsButton_Click_1(object sender, RibbonControlEventArgs e)
{
Range permalinkrange = Globals.ThisAddIn.Application.Selection;
CrunchBaseConnect cbc = new CrunchBaseConnect();
foreach (Range c in permalinkrange.Cells)
{
if (c != null)
{
CrunchBase company = cbc.GetCrunchCompany(c.Value2);
if (company != null)
{
object[,] detailrow = new object[1, company.aggregateheaders.Count()];
for (int i = 0; i < company.aggregaterow.Count(); i++)
{
detailrow[0, i] = company.aggregaterow[i];
}
Range startCell = c.Worksheet.Cells[c.Row, c.Column + 1];
Range details = ThisAddIn.OutputArrayToExcel(startCell, detailrow);
}
}
}
}
private void AggregateButton_Click(object sender, EventArgs e)
{
var reportsheet = Globals.ThisAddIn.Application.ActiveSheet;
if (NewSheetCheck.Checked)
{
reportsheet = Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets.Add();
reportsheet.Activate();
}
var startCell = Globals.ThisAddIn.Application.ActiveCell;
// normalize into entity lists companies and finance orgs; ignore others for now
SearchResultsList financecompanies = new SearchResultsList();
SearchResultsList nonfincompanies = new SearchResultsList();
SearchResultsList entitiesnotfound = new SearchResultsList(); // add companies that we can't find in crunchbase to this list
foreach (cbSearchResults entry in EntityList)
{
switch (entry.Namespace)
{
case "financial-organization": {
financecompanies.Add(entry);
break;
}
case "company":
{
nonfincompanies.Add(entry);
break;
}
default:
break;
}
}
// output the finance org table
startCell.Value2 = "Crunchbase Search on " + DateTime.Now.ToLongTimeString() + " Scope: " + ScopeDesc.Text;
// move startcell one cell lower
startCell = reportsheet.Cells[startCell.Row + 1, startCell.Column];
if (financecompanies.Count > 0)
{
// output a finance orgs table, first the headers
CrunchFinancial cfh = new CrunchFinancial();
object[,] excelout = new object[financecompanies.Count + 1, cfh.headersaggregate.Count()];
// put the header in row 0
for (int i = 0; i < cfh.headersaggregate.Count(); i++)
{
excelout[0, i] = cfh.headersaggregate[i];
}
int currrow = 1;
CrunchBaseConnect cbc = new CrunchBaseConnect();
foreach (cbSearchResults f in financecompanies)
{
CrunchFinancial fc = cbc.GetCrunchFinanceOrg(f.permalink);
if (fc != null)
{
for (int i = 0; i < fc.headersaggregate.Count(); i++)
{
excelout[currrow, i] = fc.aggregateitems[i];
}
currrow++;
}
else
{ // add to not found list
entitiesnotfound.Add(f);
}
}
//now output to excel
Range financerange = ThisAddIn.OutputArrayToExcel(startCell, excelout);
// now move startCell below the financerange
startCell = reportsheet.Cells[startCell.Row + financerange.Rows.Count + 1, startCell.Column];
}
// now do companies entity
if (nonfincompanies.Count > 0)
{
CrunchBaseConnect cbc = new CrunchBaseConnect();
CrunchBase cb = new CrunchBase();
int currow = 1; // 0 has the header
object[,] excelout = new object[nonfincompanies.Count + 1, cb.aggregateheaders.Count()];
// output headers to row 0
for (int i = 0; i < cb.aggregateheaders.Count(); i++)
{
excelout[0, i] = cb.aggregateheaders[i];
}
foreach (cbSearchResults c in nonfincompanies)
{
CrunchBase cp = cbc.GetCrunchCompany(c.permalink);
if (cp != null)
{
for (int i = 0; i < cp.aggregateheaders.Count(); i++)
{
excelout[currow, i] = cp.aggregaterow[i];
}
currow++;
}
else
{
entitiesnotfound.Add(c);
}
}
Range companyrange = ThisAddIn.OutputArrayToExcel(startCell, excelout);
this.Close();
}
} //aggregate button click method