private static GoogleSheetAppendRequest CreateAppendRequest(string sheetName, int columnKey, string valueKey,
int columnStart, object[] values)
{
var row = new GoogleSheetRow();
var countColumns = Math.Max(columnKey, columnStart + values.Length);
for (int i = 0; i < countColumns; i++)
{
row.Add(null);
}
for (int i = 0; i < values.Length; i++)
{
row[i + columnStart] = GoogleSheetCell.Create(values[i]);
}
row[columnKey] = GoogleSheetCell.Create(valueKey);
var request = new GoogleSheetAppendRequest(sheetName)
{
Rows = { row },
};
return(request);
}
public void SaveAs()
{
var gsh = new GoogleSheetsHelper(@"..\ini\MyTutorialGsheet-d6d0997cf1ec.json",
"1U72wGk-LojflkxPAWH-rDv2FGc8iIvPiNRGTy7CTB3I/");
var row1 = new GoogleSheetRow();
var row2 = new GoogleSheetRow();
var cell1 = new GoogleSheetCell()
{
CellValue = "Header 1", IsBold = true, BackgroundColor = Color.DarkGoldenrod
};
var cell2 = new GoogleSheetCell()
{
CellValue = "Header 2", BackgroundColor = Color.Cyan
};
var cell3 = new GoogleSheetCell()
{
CellValue = "Value 1"
};
var cell4 = new GoogleSheetCell()
{
CellValue = "Value 2"
};
row1.Cells.AddRange(new List <GoogleSheetCell>()
{
cell1, cell2
});
row2.Cells.AddRange(new List <GoogleSheetCell>()
{
cell3, cell4
});
var rows = new List <GoogleSheetRow>()
{
row1, row2
};
gsh.AddCells(new GoogleSheetParameters()
{
SheetName = "Sheet1", RangeColumnStart = 1, RangeRowStart = 1
}, rows);
}
private static IEnumerable <GoogleSheetUpdateRequest> CreateUpdateRequests(string sheetName, int columnStart,
int rowStart, object[] values)
{
for (int i = 0; i < values.Length; i++)
{
var value = values[i];
if (value == null)
{
continue;
}
var row = new GoogleSheetRow();
row.Add(GoogleSheetCell.Create(value));
var request = new GoogleSheetUpdateRequest(sheetName)
{
ColumnStart = columnStart + i,
RowStart = rowStart,
Rows = { row },
};
yield return(request);
}
}
public void Return_EmptyString_For_Out_Of_Range_Index()
{
var row = new GoogleSheetRow(new List <object>());
row.Item(0).ShouldBe(string.Empty);
}
public IActionResult Get(string siteName)
{
try
{
WebClient webClient = new WebClient();
var siteUrl = string.Format("https://{0}.eraprojects.sg/units", siteName);
//string page = webClient.DownloadString(siteUrl);
string path = Path.Combine(hostingEnv.WebRootPath, "data/Units.html");
var text = System.IO.File.ReadAllText(path);
HtmlDocument doc = new HtmlDocument();
doc.LoadHtml(text);
var towersList = doc.DocumentNode.SelectNodes("//button[@data-filter]")
.Select(b => b.InnerText).ToList();
var towers = new List <TowerModel>();
foreach (var t in towersList.Select((value, i) => new { i, value }))
{
var tower = new TowerModel();
var unitText = string.Format("//div[contains(@class,'{0}')]", "unit-" + t.i);
var floors = doc.DocumentNode.SelectSingleNode(unitText)
.Descendants("tr");
var floorsList = new List <FloorModel>();
foreach (var f in floors)
{
var floor = new FloorModel()
{
UnitNumbers = new List <string>()
};
var floorId = f.Element("th").InnerText;
var tHeads = f.Elements("th").ToList();
if (tHeads != null && tHeads.Count > 1)
{
foreach (var th in tHeads)
{
floor.UnitNumbers.Add(th.InnerText);
}
}
floor.FloorId = floorId;
var units = new List <UnitModel>();
foreach (var u in f.Elements("td"))
{
var imageLink = u.Element("a").GetAttributeValue("href", "");
var innerHtmlTextList = u.InnerText
.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries)
.Select(i => i.Trim())
.Where(d => !string.IsNullOrEmpty(d))
.ToList();
var unit = new UnitModel()
{
ImageUrl = imageLink,
Sqft = innerHtmlTextList.ElementAtOrDefault(0) != null ? innerHtmlTextList[0] : "",
BedRooms = innerHtmlTextList.ElementAtOrDefault(1) != null ? innerHtmlTextList[1] : "",
Availability = innerHtmlTextList.ElementAtOrDefault(2) != null ? innerHtmlTextList[2] : "",
SoldDate = innerHtmlTextList.ElementAtOrDefault(3) != null ? innerHtmlTextList[3] : "",
};
units.Add(unit);
}
floor.units = units;
floorsList.Add(floor);
}
tower.TowerName = t.value;
tower.Floors = floorsList;
tower.TowerId = t.i;
towers.Add(tower);
}
var unitDestributions = doc.DocumentNode.SelectSingleNode("//h2[text()='Units Distribution']")
.ParentNode
.Descendants("tr")
.Skip(1);
var unitsList = new List <UnitsDistributionModel>();
var type = "";
UnitsDistributionModel distributionModel = new UnitsDistributionModel();
foreach (var d in unitDestributions)
{
var thCell = d.Element("th");
//bool isNewType = thCell.InnerText.Replace(Environment.NewLine, "").Trim().Equals(type);
if (thCell != null)
{
if (!string.IsNullOrEmpty(distributionModel.Type))
{
unitsList.Add(distributionModel);
}
type = thCell.InnerText.Replace(Environment.NewLine, "").Trim();
distributionModel = new UnitsDistributionModel
{
Type = type,
DistributionData = new List <DistributionDataModel>()
};
}
var tdCells = d.Elements("td").ToList();
var unit = new DistributionDataModel()
{
ImageUrl = tdCells[0].Element("a").GetAttributeValue("href", ""),
Name = tdCells[0].InnerText.Replace(Environment.NewLine, "").Trim(),
Area = tdCells[1].InnerText.Replace(Environment.NewLine, "").Trim(),
AvailableUnits = tdCells[2].InnerText.Replace(Environment.NewLine, "").Trim(),
PriceFrom = Regex.Replace(tdCells[3].InnerText, @"\r\n?|\n", "").Trim()
};
distributionModel.DistributionData.Add(unit);
}
var transactions = doc.DocumentNode.SelectSingleNode("//h2[text()='Transactions']")
.ParentNode
.Descendants("tr");
var trasactionsList = new List <TransactionsModel>();
foreach (var d in transactions)
{
var thCell = d.Element("th");
var tdCell = d.Element("td");
var trans = new TransactionsModel()
{
Date = thCell.InnerText.Replace(Environment.NewLine, "").Trim(),
Record = tdCell.InnerText.Replace(Environment.NewLine, "").Trim()
};
trasactionsList.Add(trans);
}
var project = new SiteModel()
{
Towers = towers,
UnitsDistributions = unitsList,
Transactions = trasactionsList,
TowerNames = towersList
};
/*
*
* Google sheet Implementation
*
*/
/*
* Sheets Name creation
*/
var tabsName = new List <String>(towersList);
tabsName.Add("Unit-Distributions");
tabsName.Add("Transactions");
AddTabs(tabsName);
/*
* Add towers data "Unit-Distributions",
*/
foreach (var tower in towers)
{
var towersHeaders = new GoogleSheetRow();
var towersRowsList = new List <GoogleSheetRow>();
var towersCellList = new List <List <GoogleSheetCell> >();
foreach (var fls in tower.Floors)
{
if (fls.units.Count() > 0)
{
towersCellList.Add(setTowersCellData(fls.units, fls.FloorId));
}
}
foreach (var cell in towersCellList)
{
towersRowsList.Add(new GoogleSheetRow()
{
Cells = cell
});
}
towersHeaders.Cells.AddRange(setTowersHeaders(tower.Floors));
var towersRows = new List <GoogleSheetRow>();
towersRows.Add(towersHeaders);
towersRows.AddRange(towersRowsList);
AddCells(new GoogleSheetParameters()
{
SheetName = tower.TowerName, RangeColumnStart = 1, RangeRowStart = 1
}, towersRows);
}
;
/*
* Add unit -Distributions data
*/
var unitDestributionsHeaders = new GoogleSheetRow();
var unitDestributionsRowsList = new List <GoogleSheetRow>();
var unitDestributionsCellList = new List <List <GoogleSheetCell> >();
foreach (var ut in unitsList)
{
unitDestributionsCellList.Add(setDistributionsCellData(ut.DistributionData, ut.Type));
}
foreach (var cell in unitDestributionsCellList)
{
unitDestributionsRowsList.Add(new GoogleSheetRow()
{
Cells = cell
});
}
unitDestributionsHeaders.Cells.AddRange(setDistributionsHeaders());
var unitDestributionsRows = new List <GoogleSheetRow>();
unitDestributionsRows.Add(unitDestributionsHeaders);
unitDestributionsRows.AddRange(unitDestributionsRowsList);
AddCells(new GoogleSheetParameters()
{
SheetName = "Unit-Distributions", RangeColumnStart = 1, RangeRowStart = 1
}, unitDestributionsRows);
/*
* Add transactions data
*
*/
var transactionsHeaders = new GoogleSheetRow();
var transactionsRowsList = new List <GoogleSheetRow>();
var trasactioncCellList = new List <List <GoogleSheetCell> >();
var trasactionscell = new List <GoogleSheetCell>();
foreach (var transaction in trasactionsList)
{
trasactioncCellList.Add(setTransactionsCellData(transaction));
}
foreach (var cell in trasactioncCellList)
{
transactionsRowsList.Add(new GoogleSheetRow()
{
Cells = cell
});
}
var transactionsRows = new List <GoogleSheetRow>();
transactionsRows.AddRange(transactionsRowsList);
AddCells(new GoogleSheetParameters()
{
SheetName = "Transactions", RangeColumnStart = 1, RangeRowStart = 1
}, transactionsRows);
return(Ok(project));
}
catch (Exception)
{
throw;
}
}
