public SheetsLogEntry()
{
rowData.Values = new List <CellData>();
// Colors
m_backColor = new Google.Apis.Sheets.v4.Data.Color();
m_backColor.Red = 0.5764705882352941f;
m_backColor.Green = 0.7686274509803922f;
m_backColor.Blue = 0.4901960784313725f;
m_backColorTitle = new Google.Apis.Sheets.v4.Data.Color();
m_backColorTitle.Red = 0.4156862745098039f;
m_backColorTitle.Green = 0.6588235294117647f;
m_backColorTitle.Blue = 0.3098039215686275f;
m_backColorHighlight = new Google.Apis.Sheets.v4.Data.Color();
m_backColorHighlight.Red = 0.7137254901960784f;
m_backColorHighlight.Green = 0.8431372549019608f;
m_backColorHighlight.Blue = 0.6588235294117647f;
// CellFormat
m_formatTitle = new CellFormat();
m_formatTitle.TextFormat = new TextFormat();
m_formatTitle.TextFormat.Bold = true;
m_formatTitle.TextFormat.Underline = true;
m_formatTitle.BackgroundColor = m_backColorTitle;
m_formatMonth = new CellFormat();
m_formatMonth.NumberFormat = new NumberFormat();
m_formatMonth.NumberFormat.Pattern = "MMM dd";
m_formatMonth.NumberFormat.Type = "DATE";
m_formatMonth.BackgroundColor = m_backColor;
m_formatDay = new CellFormat();
m_formatDay.NumberFormat = new NumberFormat();
m_formatDay.NumberFormat.Pattern = "DDD";
m_formatDay.NumberFormat.Type = "DATE";
m_formatDay.BackgroundColor = m_backColor;
m_formatText = new CellFormat();
m_formatText.TextFormat = new TextFormat();
m_formatText.TextFormat.Bold = false;
m_formatText.TextFormat.Underline = false;
m_formatText.BackgroundColor = m_backColor;
}
public void HighlightOriginals(string spreadsheetId, ClaimsPrincipal user)
{
var spreadsheet = Service(user).Spreadsheets.Get(spreadsheetId).Execute();
var startRow = 17;
var placesSheet = spreadsheet.Sheets.FirstOrDefault(x =>
x.Properties.Title.Equals("число мест", StringComparison.OrdinalIgnoreCase));
var simpleBorder = new Border {
Style = "SOLID", Color = new Color {
Blue = 0, Green = 0, Red = 0
}
};
var borders = new Borders
{
Top = simpleBorder, Bottom = simpleBorder, Left = simpleBorder, Right = simpleBorder
};
var standartFormat = new CellFormat
{
BackgroundColor = new Color {
Blue = 255, Green = 255, Red = 255
},
TextFormat = new TextFormat {
Bold = false, Italic = false, Strikethrough = false, Underline = false, ForegroundColor = new Color {
Blue = 0, Green = 0, Red = 0
}
},
Borders = borders
};
var googleGreen = new Color
{
Blue = (float)systemColor.Lime.B / 255, Red = (float)systemColor.Lime.R / 255, Green = (float)systemColor.Lime.G / 255
};
var googleYellow = new Color
{
Blue = (float)systemColor.Yellow.B / 255, Green = (float)systemColor.Yellow.G / 255, Red = (float)systemColor.Yellow.R / 255
};
var googleGray = new Color
{
Blue = (float)systemColor.DimGray.B / 255, Green = (float)systemColor.DimGray.G / 255, Red = (float)systemColor.DimGray.R / 255
};
var greenFormat = new CellFormat
{
BackgroundColor = googleGreen,
Borders = borders
};
var yellowFormat = new CellFormat
{
BackgroundColor = googleYellow,
Borders = new Borders
{
Bottom = new Border
{
Color = new Color
{
Blue = systemColor.Red.B, Green = systemColor.Red.G, Red = systemColor.Red.R
},
Style = "SOLID_THICK"
},
Top = simpleBorder,
Left = simpleBorder,
Right = simpleBorder
}
};
var grayFormat = new CellFormat
{
BackgroundColor = googleGray,
TextFormat = new TextFormat {
Strikethrough = true
},
Borders = borders
};
var grayTextFormat = new CellFormat
{
TextFormat = new TextFormat
{
ForegroundColor = new Color
{
Blue = (float)systemColor.Gray.B / 255, Green = (float)systemColor.Gray.G / 255,
Red = (float)systemColor.Gray.R / 255
}
},
Borders = borders
};
var batchGet = Service(user).Spreadsheets.Values.BatchGet(spreadsheetId);
batchGet.DateTimeRenderOption = SpreadsheetsResource.ValuesResource.BatchGetRequest
.DateTimeRenderOptionEnum.FORMATTEDSTRING;
batchGet.ValueRenderOption =
SpreadsheetsResource.ValuesResource.BatchGetRequest.ValueRenderOptionEnum.FORMULA;
batchGet.MajorDimension = SpreadsheetsResource.ValuesResource.BatchGetRequest.MajorDimensionEnum.ROWS;
batchGet.Ranges = new Repeatable <string>(new [] { "АТПП!A17:N40" });
var test = batchGet.Execute().ValueRanges[0].Values;
foreach (var sheet in spreadsheet.Sheets)
{
var countRow = 800;
var countPlaces = 0;
var columnCount = sheet.Properties.GridProperties.ColumnCount.Value;
var placesRange = $"{placesSheet.Properties.Title}!A1:B";
// var placesValues = batchGet.Execute().ValueRanges[0].Values;
// for (int i = 1; i < placesValues.Count; i++)
// {
// for (int j = 1; j < placesValues[i].Count; j++)
// {
// if (placesValues[i][j].ToString() == sheet.Properties.Title)
// countPlaces = int.Parse(placesValues[i][0].ToString());
// }
// }
if (sheet.Properties.Title.Equals("База", StringComparison.OrdinalIgnoreCase) ||
sheet.Properties.Title.Equals("Число мест", StringComparison.OrdinalIgnoreCase))
{
continue;
}
var repeatRequest = new Request
{
RepeatCell = new RepeatCellRequest
{
Range = new GridRange
{
SheetId = sheet.Properties.SheetId,
StartColumnIndex = 0,
StartRowIndex = 16,
EndColumnIndex = columnCount,
EndRowIndex = 900
},
Cell = new CellData
{
UserEnteredFormat = standartFormat
},
Fields = "UserEnteredFormat"
}
};
var rows = new List <RowData>();
// for (var i = 0; i < countRow; i++)
// {
// rows.Add(new RowData {Values = new List<CellData>()});
// rows[^1].Values.Add(new CellData {UserEnteredValue = new ExtendedValue {StringValue = ""}});
// }
// var updateRequest = new Request
// {
// UpdateCells = new UpdateCellsRequest
// {
// Range = new GridRange
// {
// SheetId = sheet.Properties.SheetId,
// StartColumnIndex = 0,
// StartRowIndex = 16,
// EndColumnIndex = columnCount,
// EndRowIndex = 900
// },
// Rows = rows,
// Fields = "UserEnteredValue"
// }
// };
var bussr = new BatchUpdateSpreadsheetRequest
{
Requests = new List <Request> {
repeatRequest
}
};
var bur = Service(user).Spreadsheets.BatchUpdate(bussr, spreadsheet.SpreadsheetId);
// bur.Execute();
var range = $"{sheet.Properties.Title}!A{startRow - 1}:{GetLetterByNumber(columnCount)}";
var valueRange = Service(user).Spreadsheets.Values.Get(spreadsheet.SpreadsheetId, range).Execute();
var data = new List <RowData>();
for (int i = 0; i < valueRange.Values.Count; i++)
{
data.Add(new RowData {
Values = new List <CellData>()
});
for (int j = 0; j < valueRange.Values[i].Count; j++)
{
data[^ 1].Values.Add((CellData)valueRange.Values[i][j]);