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; } }