private static IEnumerable <UpdateCellsRequest> GetUpdateStatsRequest(NewSheetInfo newSheetInfo, SheetProperties latestSheetProperties)
{
return(new[] { 2, 4, 6 }
.Select(rowIndex => new UpdateCellsRequest
{
Start = new GridCoordinate {
SheetId = newSheetInfo.Id, ColumnIndex = 14, RowIndex = rowIndex - 1
},
Fields = "userEnteredValue",
Rows = new List <RowData>
{
new RowData
{
Values = new List <CellData>
{
new CellData
{
UserEnteredValue =
new ExtendedValue {
FormulaValue = $"=N{rowIndex}-'{latestSheetProperties.Title}'!N{rowIndex}"
}
}
}
}
}
})
.ToList());
}
private static List <UpdateCellsRequest> GetUpdateRequests(NewSheetInfo newSheetInfo, SheetProperties latestSheetProperties, IEnumerable <Flat> flats)
{
var updateCellsRequests = new List <UpdateCellsRequest> {
GetUpdateFlatsRequest(newSheetInfo, flats)
};
updateCellsRequests.AddRange(GetUpdateStatsRequest(newSheetInfo, latestSheetProperties));
return(updateCellsRequests);
}
private static DuplicateSheetRequest GetDuplicateRequest(NewSheetInfo newSheetInfo, SheetProperties latestSheetProperties)
{
return(new DuplicateSheetRequest
{
SourceSheetId = latestSheetProperties.SheetId,
InsertSheetIndex = 0,
NewSheetId = newSheetInfo.Id,
NewSheetName = newSheetInfo.Name
});
}
private static UpdateCellsRequest GetUpdateFlatsRequest(NewSheetInfo newSheetInfo, IEnumerable <Flat> flats)
{
var rows = flats.Select(f => new RowData
{
Values = new List <CellData>
{
new CellData {
UserEnteredValue = new ExtendedValue {
NumberValue = f.Number
}
},
new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = $"{f.CurrentState}"
}
},
new CellData {
UserEnteredValue = new ExtendedValue {
NumberValue = f.Section
}
},
new CellData {
UserEnteredValue = new ExtendedValue {
NumberValue = f.Floor
}
},
new CellData {
UserEnteredValue = new ExtendedValue {
NumberValue = f.RoomsCount
}
},
new CellData {
UserEnteredValue = new ExtendedValue {
NumberValue = f.LivingArea
}
},
new CellData {
UserEnteredValue = new ExtendedValue {
NumberValue = f.TotalArea
}
},
new CellData {
UserEnteredValue = new ExtendedValue {
NumberValue = f.Price
}
},
new CellData {
UserEnteredValue = new ExtendedValue {
NumberValue = f.AreaRatio
}
},
new CellData {
UserEnteredValue = new ExtendedValue {
NumberValue = f.PricePerMetre
}
},
new CellData {
UserEnteredValue = new ExtendedValue {
NumberValue = f.KitchenArea
}
},
new CellData {
UserEnteredValue = new ExtendedValue {
NumberValue = f.Id
}
},
new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = f.Url
}
}
}
}).ToList();
return(new UpdateCellsRequest
{
Start = new GridCoordinate {
SheetId = newSheetInfo.Id, ColumnIndex = 0, RowIndex = 1
}, //cell A2
Fields = "userEnteredValue",
Rows = rows
});
}
