public Spreadsheet CreateSpreadSheet(GoogleCredentials googleCredentials, System.Security.Claims.ClaimsPrincipal user, CreateRequest createRequest)
{
UserCredential credential;
var userMail = user.Claims.FirstOrDefault(claim => claim.Value.Contains("@"))?.Value ?? "user";
using (var stream =
new FileStream("credentials.json", FileMode.Open, FileAccess.Read))
{
var credPath = "token.json";
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
_scopes,
userMail,
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = "creatingcompetitionlists",
});
var myNewSheet = new Spreadsheet
{
Properties = new SpreadsheetProperties {
Title = createRequest.TableName
},
Sheets = new List <Sheet> {
new Sheet {
Properties = new SheetProperties {
Title = "БАЗА"
}
}
}
};
foreach (var directionId in createRequest.DirectionIds.Select(int.Parse))
{
using var db = new competition_listContext();
myNewSheet.Sheets.Add(new Sheet {
Properties = new SheetProperties {
Title = db.Directions.FirstOrDefault(x => x.Id == directionId)?.ShortTitle
}
});
}
myNewSheet.Sheets.Add(new Sheet {
Properties = new SheetProperties {
Title = "ЧИСЛО МЕСТ"
}
});
return(service.Spreadsheets.Create(myNewSheet).Execute());
}
public List <Faculty> GetFaculties()
{
using var db = new competition_listContext();
return(db.Faculties.ToList());
}
public List <Direction> GetDirections()
{
using var db = new competition_listContext();
return(db.Directions.ToList());
}
public void FillSpreadsheet(Spreadsheet spreadsheet,
System.Security.Claims.ClaimsPrincipal user,
List <string> directions, int countWave, int possibleDirections)
{
var columnAfterDirection = GetNumberByLetter(_startDirectionColumn) + directions.Count;
_originalColumn = GetLetterByNumber(columnAfterDirection + 1);
_predictionColumn = GetLetterByNumber(GetNumberByLetter(_originalColumn) + 1);
_enrolledOnColumn = GetLetterByNumber(GetNumberByLetter(_predictionColumn) + 1);
_commentColumn = GetLetterByNumber(GetNumberByLetter(_enrolledOnColumn) + 1);
_phoneColumn = GetLetterByNumber(GetNumberByLetter(_commentColumn) + 1);
var spreadsheetId = spreadsheet.SpreadsheetId;
var startValues = new List <object>
{
"№", "ФИО", "СНИЛС", "Преимущ. право", "Сумма баллов", "Вид документа об образовании",
"Согласие на зачисление"
};
for (int i = 1; i <= directions.Count; i++)
{
startValues.Add("Н_" + i);
}
startValues.Add("Оригинал");
startValues.Add("Прогноз");
startValues.Add("Зачислен НА");
startValues.Add("Комментарий");
startValues.Add("Телефон");
var baseValues = new List <object>
{
"Прогноз (зачислен (Ц, ОП, 1 волна), ДА, НЕТ)", "Комментарий", "ЕГЭ", "Номер", "ФИО"
};
for (var i = 1; i <= possibleDirections; i++)
{
baseValues.Add("Н_" + i);
}
baseValues.Add("Оригинал");
baseValues.Add("Русский язык");
baseValues.Add("Математика");
baseValues.Add("География");
baseValues.Add("Биология");
baseValues.Add("История");
baseValues.Add("Об-во");
baseValues.Add("Химия");
baseValues.Add("Физика");
baseValues.Add("Литература");
baseValues.Add("Английский язык");
baseValues.Add("Французский язык");
baseValues.Add("Немецкий язык");
baseValues.Add("");
baseValues.Add("ИКТ");
baseValues.Add("Телефон");
baseValues.Add("E-mail");
baseValues.Add("Адрес по прописке");
baseValues.Add("Общежитие");
baseValues.Add("Зачислен НА");
var headValues = new RowData {
Values = new List <CellData>()
};
var baseHeadValues = new RowData {
Values = new List <CellData>()
};
var columns = new List <SpreadsheetColumn>();
for (var i = 0; i < startValues.Count; i++)
{
headValues.Values.Add(new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = startValues[i].ToString()
}
});
columns.Add(new SpreadsheetColumn {
HeadName = startValues[i].ToString(), Column = GetLetterByNumber(i + 1)
});
}
foreach (var baseValue in baseValues)
{
baseHeadValues.Values.Add(new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = baseValue.ToString()
}
});
}
foreach (var sheet in spreadsheet.Sheets)
{
var valueRange = new ValueRange();
var simpleBorder = new Border {
Style = "SOLID", Color = new Color {
Blue = 0, Green = 0, Red = 0
}
};
var rows = new List <RowData>();
Request updateRequest;
Request repeatRequest;
Request appendRequest;
BatchUpdateSpreadsheetRequest bussr;
SpreadsheetsResource.BatchUpdateRequest bur;
var userEnteredFormat = new CellFormat
{
Borders = new Borders
{
Bottom = simpleBorder,
Top = simpleBorder,
Left = simpleBorder,
Right = simpleBorder
}
};
switch (sheet.Properties.Title)
{
case "БАЗА":
rows = new List <RowData>();
rows.Add(baseHeadValues);
SetBaseFormula(rows);
updateRequest = new Request
{
UpdateCells = new UpdateCellsRequest
{
Range = new GridRange
{
SheetId = sheet.Properties.SheetId,
StartColumnIndex = 0,
StartRowIndex = 0,
EndColumnIndex = baseHeadValues.Values.Count,
EndRowIndex = 900
},
Rows = rows,
Fields = "UserEnteredValue"
}
};
appendRequest = new Request()
{
AppendDimension = new AppendDimensionRequest()
{
SheetId = sheet.Properties.SheetId,
Dimension = "COLUMNS",
Length = 50
}
};
repeatRequest = new Request
{
RepeatCell = new RepeatCellRequest
{
Range = new GridRange
{
SheetId = sheet.Properties.SheetId,
StartColumnIndex = 0,
StartRowIndex = 0,
EndColumnIndex = baseHeadValues.Values.Count,
EndRowIndex = 900
},
Cell = new CellData
{
UserEnteredFormat = userEnteredFormat
},
Fields = "UserEnteredFormat(Borders)"
}
};
bussr = new BatchUpdateSpreadsheetRequest();
bussr.Requests = new List <Request>();
bussr.Requests.Add(appendRequest);
bussr.Requests.Add(updateRequest);
bussr.Requests.Add(repeatRequest);
bur = Service(user).Spreadsheets.BatchUpdate(bussr, spreadsheet.SpreadsheetId);
bur.Execute();
break;
case "ЧИСЛО МЕСТ":
rows = new List <RowData>();
var placesColumns = new Dictionary <string, string>();
placesColumns.Add("A", "КЦП");
placesColumns.Add("B", DateTime.Today.Year.ToString());
placesColumns.Add("C", "Целевики");
placesColumns.Add("D", "Особые права");
placesColumns.Add("E", "Число мест на 1 волну");
placesColumns.Add("F", "Проходной балл");
if (countWave == 2)
{
placesColumns.Add("G", "Число мест на 2 волну");
placesColumns.Add("H", "Проходной балл");
}
var placesHeadRow = new RowData {
Values = new List <CellData>()
};
placesColumns.Values.ToList().ForEach(x =>
placesHeadRow.Values.Add(new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = x
}
}));
rows.Add(placesHeadRow);
foreach (var direction in directions)
{
using var db = new competition_listContext();
var directionDb = db.Directions.FirstOrDefault(x => x.Id.Equals(int.Parse(direction)));
var placesRow = new RowData {
Values = new List <CellData>()
};
placesRow.Values.Add(new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = directionDb.CountForEnrollee.Value.ToString()
}
});
placesRow.Values.Add(new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = directionDb.ShortTitle
}
});
placesRow.Values.Add(new CellData {
UserEnteredValue = new ExtendedValue {
NumberValue = 0
}
});
placesRow.Values.Add(new CellData {
UserEnteredValue = new ExtendedValue {
NumberValue = 0
}
});
if (countWave == 1)
{
placesRow.Values.Add(new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = directionDb.CountForEnrollee.Value.ToString()
}
});
placesRow.Values.Add(new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = ""
}
});
}
else
{
placesRow.Values.Add(new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = (directionDb.CountForEnrollee.Value * 0.8).ToString()
}
});
placesRow.Values.Add(new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = ""
}
});
placesRow.Values.Add(new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = (directionDb.CountForEnrollee.Value * 0.2).ToString()
}
});
placesRow.Values.Add(new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = ""
}
});
}
rows.Add(placesRow);
}
updateRequest = new Request
{
UpdateCells = new UpdateCellsRequest
{
Range = new GridRange
{
SheetId = sheet.Properties.SheetId,
StartColumnIndex = 0,
StartRowIndex = 0,
EndColumnIndex = placesColumns.Count,
EndRowIndex = directions.Count + 1
},
Rows = rows,
Fields = "UserEnteredValue"
}
};
repeatRequest = new Request
{
RepeatCell = new RepeatCellRequest
{
Range = new GridRange
{
SheetId = sheet.Properties.SheetId,
StartColumnIndex = 0,
StartRowIndex = 0,
EndColumnIndex = placesColumns.Count,
EndRowIndex = directions.Count + 1
},
Cell = new CellData
{
UserEnteredFormat = userEnteredFormat
},
Fields = "UserEnteredFormat(Borders)"
}
};
bussr = new BatchUpdateSpreadsheetRequest();
bussr.Requests = new List <Request>();
bussr.Requests.Add(updateRequest);
bussr.Requests.Add(repeatRequest);
bur = Service(user).Spreadsheets.BatchUpdate(bussr, spreadsheet.SpreadsheetId);
bur.Execute();
break;
default:
rows = new List <RowData>();
rows.Add(headValues);
SetFormulas(columns, sheet.Properties.Title, rows);
updateRequest = new Request
{
UpdateCells = new UpdateCellsRequest
{
Range = new GridRange
{
SheetId = sheet.Properties.SheetId,
StartColumnIndex = 0,
StartRowIndex = 15,
EndColumnIndex = columns.Count,
EndRowIndex = 900
},
Rows = rows,
Fields = "UserEnteredValue"
}
};
repeatRequest = new Request
{
RepeatCell = new RepeatCellRequest
{
Range = new GridRange
{
SheetId = sheet.Properties.SheetId,
StartColumnIndex = 0,
StartRowIndex = 15,
EndColumnIndex = columns.Count,
EndRowIndex = 900
},
Cell = new CellData
{
UserEnteredFormat = userEnteredFormat
},
Fields = "UserEnteredFormat(Borders)"
}
};
bussr = new BatchUpdateSpreadsheetRequest();
bussr.Requests = new List <Request>();
bussr.Requests.Add(updateRequest);
bussr.Requests.Add(repeatRequest);
bur = Service(user).Spreadsheets.BatchUpdate(bussr, spreadsheet.SpreadsheetId);
bur.Execute();
break;
}
}
}
public CreateFormData()
{
using var db = new competition_listContext();
_faculties = db.Faculties.ToList();
_directions = db.Directions.ToList();
}
