/// <summary>
/// Добавление значений в ячейки
/// </summary>
/// <param name="args"></param>
/// <returns></returns>
List <CellData> AddCellsInRow(params object[] args)
{
ExtendedValue value;
List <CellData> values = new List <CellData>();
foreach (object element in args)
{
value = new ExtendedValue();
if (element.GetType().Equals(typeof(double)))
{
value.NumberValue = (double)element;
}
else
{
value.StringValue = element.ToString();
}
values.Add(new CellData
{
UserEnteredValue = value
});
}
return(values);
}
/// <summary>
/// Return RowData with 5 cells. [empty] [MM dd] [DDD] [hh:mm:ss] [description]
/// </summary>
/// <param name="values"></param>
/// <returns></returns>
public RowData addEntry(string timeStr, string description)
{
ExtendedValue valEmpty = new ExtendedValue();
ExtendedValue valMonthDay = new ExtendedValue();
ExtendedValue valDayName = new ExtendedValue();
ExtendedValue valTime = new ExtendedValue();
ExtendedValue valDescription = new ExtendedValue();
valEmpty.StringValue = "";
//valMonthDay.FormulaValue = "=TEXT("+ DateTime.Now.Month + ",\"MMM\")&\" \"&TEXT("+ DateTime.Now.Day + ",\"dd\")";
//valDayName.FormulaValue = "=TEXT(\"" + DateTime.Now.Month + "\"/\"" + DateTime.Now.Day + "\"/\"" + DateTime.Now.Year + "\", \"DDD\")";
//valDayName.FormulaValue = "=TEXT("+DateTime.Now.DayOfWeek+", \"DDD\"";
valMonthDay.FormulaValue = "=DATE(" + DateTime.Now.Year + "," + DateTime.Now.Month + "," + DateTime.Now.Day + ")";
valDayName.FormulaValue = "=DATE(" + DateTime.Now.Year + "," + DateTime.Now.Month + "," + DateTime.Now.Day + ")";
valTime.StringValue = timeStr;
valDescription.StringValue = description;
CellData cellEmpty = new CellData();
CellData cellMonthDay = new CellData();
CellData cellDayName = new CellData();
CellData cellTime = new CellData();
CellData cellDescription = new CellData();
CellFormat formatText = m_formatText;
CellFormat formatMonth = m_formatMonth;
CellFormat formatDay = m_formatDay;
if (DateTime.Now.DayOfWeek == DayOfWeek.Sunday ||
DateTime.Now.DayOfWeek == DayOfWeek.Saturday)
{
formatText.BackgroundColor = m_backColorHighlight;
formatMonth.BackgroundColor = m_backColorHighlight;
formatDay.BackgroundColor = m_backColorHighlight;
}
cellEmpty.UserEnteredValue = valEmpty;
cellEmpty.UserEnteredFormat = formatText;
cellMonthDay.UserEnteredValue = valMonthDay;
cellMonthDay.UserEnteredFormat = formatMonth;
cellDayName.UserEnteredValue = valDayName;
cellDayName.UserEnteredFormat = formatDay;
cellTime.UserEnteredValue = valTime;
cellTime.UserEnteredFormat = formatText;
cellDescription.UserEnteredValue = valDescription;
cellDescription.UserEnteredFormat = formatText;
RowData rowData = new RowData();
rowData.Values = new List <CellData>();
rowData.Values.Add(cellEmpty);
rowData.Values.Add(cellMonthDay);
rowData.Values.Add(cellDayName);
rowData.Values.Add(cellTime);
rowData.Values.Add(cellDescription);
return(rowData);
}
/// <summary>
/// Ajoute les paramètres d'insertion.
/// </summary>
/// <param name="bean">Bean à insérér.</param>
/// <param name="beanDefinition">Définition du bean.</param>
/// <param name="parameters">Paramètres de la commande SQL.</param>
/// <param name="columnSelector">Selecteur de colonnes à mettre à jour ou à ignorer.</param>
protected void AddInsertParameters(T bean, BeanDefinition beanDefinition, SqlServerParameterCollection parameters, ColumnSelector columnSelector)
{
if (beanDefinition == null)
{
throw new ArgumentNullException("beanDefinition");
}
foreach (BeanPropertyDescriptor property in beanDefinition.Properties)
{
if (property.IsPrimaryKey || property.MemberName == null || (columnSelector != null && !columnSelector.ColumnList.Contains(property.MemberName)))
{
continue;
}
object value = property.GetValue(bean);
if (value != null)
{
ExtendedValue extValue = value as ExtendedValue;
if (extValue != null)
{
value = extValue.Value;
}
}
IStoreRule rule = this.GetStoreRule(property.PropertyName);
ValueRule valueRule = null;
if (rule != null)
{
valueRule = rule.GetInsertValue(value);
}
if (valueRule != null)
{
switch (valueRule.Action)
{
case ActionRule.DoNothing:
continue;
case ActionRule.Update:
value = valueRule.Value;
break;
default:
throw new NotSupportedException();
}
}
// Ajout du paramètre en entrée de la commande.
SqlServerParameter parameter = AddParameter(parameters, property, value);
if (property.PrimitiveType == typeof(byte[]))
{
parameter.DbType = DbType.Binary;
}
}
}
public override bool Equals(object obj)
{
if (obj is T)
{
return(ExtendedValue.Equality().Equal((T)obj));
}
else
{
return(false);
}
}
/// <summary>
/// Renvoie la valeur de la propriété.
/// </summary>
/// <param name="item">Item.</param>
/// <param name="property">Descrition de l'item.</param>
/// <returns>Valeur.</returns>
private static object GetPropertyValue(T item, BeanPropertyDescriptor property)
{
object value = property.GetValue(item);
ExtendedValue v = value as ExtendedValue;
if (v != null)
{
value = v.Value;
}
return(value);
}
/// <summary>
/// Crée la chaîne liée à la clause Where.
/// </summary>
/// <param name="bean">Bean à mettre à jour.</param>
/// <param name="sbUpdateWhere">Clause Where.</param>
/// <param name="property">Propriété courante.</param>
/// <param name="rule">Règle à appliquer.</param>
protected void BuildUpdateWhere(T bean, StringBuilder sbUpdateWhere, BeanPropertyDescriptor property, IStoreRule rule)
{
if (sbUpdateWhere == null)
{
throw new ArgumentNullException("sbUpdateWhere");
}
if (property == null)
{
throw new ArgumentNullException("property");
}
if (rule == null)
{
return;
}
object value = property.GetValue(bean);
if (value != null)
{
ExtendedValue extValue = value as ExtendedValue;
if (extValue != null)
{
value = extValue.Value;
}
}
ValueRule valueRule = rule.GetWhereClause(value);
if (valueRule == null)
{
return;
}
switch (valueRule.Action)
{
case ActionRule.Check:
sbUpdateWhere.Append(" and ").Append(property.MemberName).Append(" = ");
sbUpdateWhere.Append(VariablePrefix).Append("RU_").Append(
property.MemberName);
break;
case ActionRule.DoNothing:
break;
default:
throw new NotSupportedException();
}
}
public void UpdateCellValue(string column, int row, int sheet, string value, string sheetId)
{
var reqs = new BatchUpdateSpreadsheetRequest();
reqs.Requests = new List <Request>();
string[] colNames = new[] { value };
// Create starting coordinate where data would be written to
GridCoordinate gc = new GridCoordinate();
gc.ColumnIndex = GetNumberFromChar(column[0]);
gc.RowIndex = row - 1;
gc.SheetId = sheet; // Your specific sheet ID here
Request rq = new Request();
rq.UpdateCells = new UpdateCellsRequest();
rq.UpdateCells.Start = gc;
rq.UpdateCells.Fields = "*"; // needed by API, throws error if null
// Assigning data to cells
RowData rd = new RowData();
List <CellData> lcd = new List <CellData>();
foreach (String s in colNames)
{
ExtendedValue ev = new ExtendedValue();
ev.StringValue = s;
CellData cd = new CellData();
cd.UserEnteredValue = ev;
lcd.Add(cd);
}
rd.Values = lcd;
// Put cell data into a row
List <RowData> lrd = new List <RowData>();
lrd.Add(rd);
rq.UpdateCells.Rows = lrd;
// It's a batch request so you can create more than one request and send them all in one batch. Just use reqs.Requests.Add() to add additional requests for the same spreadsheet
reqs.Requests.Add(rq);
// Execute request
BatchUpdateSpreadsheetResponse response = service.Spreadsheets.BatchUpdate(reqs, sheetId).Execute(); // Replace Spreadsheet.SpreadsheetId with your recently created spreadsheet ID
}
public static string ToDataString(this ExtendedValue value)
{
// try all the properties :)
if (!(value.BoolValue is null))
{
return(value.BoolValue.Value.ToString());
}
if (!(value.NumberValue is null))
{
return(value.NumberValue.Value.ToString(CultureInfo.CurrentCulture));
}
if (!(value.StringValue is null))
{
return(value.StringValue);
}
return(null);
}
public GCell AddValue(object value)
{
if (mCellData == null) mCellData = new ExtendedValue();
var type = value?.GetType();
Action<object, ExtendedValue, GFields> act;
if (type != null && TypeGExtendedValueDict.TryGetValue(type, out act))
{
act.Invoke(value, mCellData, Fields);
}
else
{
mCellData.StringValue = "";
Fields.Add("userEnteredValue.stringValue");
}
return this;
}
public static void UpdateCells(SheetsService service, String spreadsheetId, int gid, int row, int col, String[] value)
{
List <Request> requests = new List <Request>();
List <RowData> rowDatas = new List <RowData>();
for (int i = 0; i < value.Length; i++)
{
List <CellData> valuess = new List <CellData>();
ExtendedValue extendedValue = new ExtendedValue();
extendedValue.StringValue = value[i];
CellData cd = new CellData();
cd.UserEnteredValue = extendedValue;
valuess.Add(cd);
RowData rowData = new RowData();
rowData.Values = valuess;
rowDatas.Add(rowData);
}
GridCoordinate gridCoordinate = new GridCoordinate();
gridCoordinate.SheetId = gid;
gridCoordinate.RowIndex = row;
gridCoordinate.ColumnIndex = col;
UpdateCellsRequest updateCellsRequest = new UpdateCellsRequest();
updateCellsRequest.Rows = rowDatas;
updateCellsRequest.Start = gridCoordinate;
updateCellsRequest.Fields = "userEnteredValue";
Request request = new Request();
request.UpdateCells = updateCellsRequest;
requests.Add(request);
BatchUpdateSpreadsheetRequest batchUpdateRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateRequest.Requests = requests;
service.Spreadsheets.BatchUpdate(batchUpdateRequest, spreadsheetId).Execute();
}
/// <summary>
/// Constructeur à partir d'une liste de critères.
/// </summary>
/// <param name="criteria">Critère.</param>
/// <param name="expressionIndex">Index des expressions à appliquer.</param>
private void AddAllCriteria(object criteria, IDictionary <string, Expression> expressionIndex)
{
BeanDefinition definition = BeanDescriptor.GetDefinition(criteria);
foreach (BeanPropertyDescriptor property in definition.Properties)
{
if (property.MemberName == null)
{
continue;
}
object value = property.GetValue(criteria);
if (value == null)
{
continue;
}
ExtendedValue extValue = value as ExtendedValue;
if (extValue != null)
{
if (extValue.Value == null)
{
continue;
}
value = extValue.Value;
}
string s = value as string;
if (s != null && s.Length == 0)
{
continue;
}
Expression expr = Expression.Equals;
if (expressionIndex != null && expressionIndex.ContainsKey(property.MemberName))
{
expr = expressionIndex[property.MemberName];
}
this.AddCriteria(property.MemberName, expr, value);
}
}
public override ExtendedValue Evaluate(LiveStackFrame frame)
{
ExtendedValue result;
if (Target.Value is ObjectMirror || Target.Value is StructMirror)
{
Value value = EvalMember.Evaluate(frame.Thread, Target.Value, m_memberName);
if (value != null)
result = new ExtendedValue(value);
else if (Target.Value is ObjectMirror)
throw new Exception(string.Format("Couldn't find a field or property for {0}.{1}", ((ObjectMirror) Target.Value).Type.FullName, m_memberName));
else
throw new Exception(string.Format("Couldn't find a field or property for {0}.{1}", ((StructMirror) Target.Value).Type.FullName, m_memberName));
}
else
{
throw new Exception("Member access target should be an object, struct, or type name, not a " + Target.Value.GetType());
}
return result;
}
private static void AddCellRequest(List <Request> requests, List <Anime> tier, int j, int i)
{
requests.Add(new Request
{
UpdateCells = new UpdateCellsRequest
{
Rows = new List <RowData>
{
new()
{
Values = new List <CellData>
{
new()
{
UserEnteredValue = new ExtendedValue
{
FormulaValue = $"=HYPERLINK(\"{tier[j].URL}\", \"{tier[j].Name}\")"
}
}
}
}
},
public static object GetValue(this ExtendedValue value)
{
if (value.BoolValue.HasValue)
{
return(value.BoolValue.Value);
}
if (value.NumberValue.HasValue)
{
return(value.NumberValue.Value);
}
if (!string.IsNullOrEmpty(value.StringValue))
{
return(value.StringValue);
}
if (value.NumberValue.HasValue)
{
return(value.NumberValue.Value);
}
return("");
}
private object DoGetPrimitive(ExtendedValue value)
{
if (value.Value is PrimitiveValue)
{
var pv = (PrimitiveValue) value.Value;
return pv.Value;
}
else if (value.Value is StringMirror)
{
var sv = (StringMirror) value.Value;
return sv.Value;
}
else
{
throw new Exception("Expected a PrimitiveValue or StringMirror not " + value.Value.GetType());
}
}
private CellData CreateCellData(GoogleSheetCell cell)
{
if (cell == null)
{
return(new CellData());
}
var numberValue = cell.NumberValue;
var numberFormat = cell.NumberFormat == null ? null : new NumberFormat
{
Type = "number",
Pattern = cell.NumberFormat,
};
if (cell.DateTimeValue.HasValue)
{
numberValue = cell.DateTimeValue.Value.ToOADate();
numberFormat = new NumberFormat
{
Type = "number",
Pattern = cell.DateTimeFormat ?? DateTimeFormat
};
}
var userEnteredValue = new ExtendedValue
{
StringValue = cell.StringValue,
NumberValue = numberValue,
BoolValue = cell.BoolValue,
};
CellFormat userEnteredFormat = null;
if (numberFormat != null)
{
if (userEnteredFormat == null)
{
userEnteredFormat = new CellFormat();
}
userEnteredFormat.NumberFormat = numberFormat;
}
if (cell.Bold.HasValue)
{
if (userEnteredFormat == null)
{
userEnteredFormat = new CellFormat();
}
if (userEnteredFormat.TextFormat == null)
{
userEnteredFormat.TextFormat = new TextFormat();
}
userEnteredFormat.TextFormat.Bold = cell.Bold;
}
if (cell.BackgroundColor.HasValue)
{
if (userEnteredFormat == null)
{
userEnteredFormat = new CellFormat();
}
userEnteredFormat.BackgroundColor = cell.BackgroundColor.Value.ToGoogleColor();
}
if (cell.HorizontalAlignment.HasValue)
{
if (userEnteredFormat == null)
{
userEnteredFormat = new CellFormat();
}
userEnteredFormat.HorizontalAlignment = cell.HorizontalAlignment.ToString();
}
var cellData = new CellData()
{
UserEnteredValue = userEnteredValue,
UserEnteredFormat = userEnteredFormat,
};
return(cellData);
}
/// <summary>
/// Crée la requête SQL de mise à jour d'un bean.
/// </summary>
/// <param name="bean">Bean à mettre à jour.</param>
/// <param name="beanDefinition">Définition du bean.</param>
/// <param name="primaryKey">Définition de la clef primaire.</param>
/// <param name="columnSelector">Selecteur de colonnes à mettre à jour ou à ignorer.</param>
/// <returns>Requête SQL.</returns>
protected string BuildUpdateQuery(T bean, BeanDefinition beanDefinition, BeanPropertyDescriptor primaryKey, ColumnSelector columnSelector)
{
if (beanDefinition == null)
{
throw new ArgumentNullException("beanDefinition");
}
if (primaryKey == null)
{
throw new ArgumentNullException("primaryKey");
}
StringBuilder sbUpdate = new StringBuilder(CurrentUserStatementLog);
sbUpdate.Append("update ");
StringBuilder sbUpdateSet = new StringBuilder(beanDefinition.ContractName);
sbUpdateSet.Append(" set");
StringBuilder sbUpdateWhere = new StringBuilder(" where ");
sbUpdateWhere.Append(primaryKey.MemberName).Append(" = ").Append(VariablePrefix).Append(primaryKey.MemberName);
// Construction des champs de l'update SET et du WHERE
int count = 0;
foreach (BeanPropertyDescriptor property in beanDefinition.Properties)
{
// Si la propriété est une clé primaire ou n'est pas défini,
// on passe à la propriété suivante.
if (property.MemberName == null || property.IsPrimaryKey || property.IsReadOnly ||
(columnSelector != null && !columnSelector.ColumnList.Contains(property.MemberName)))
{
continue;
}
// Dans le cas où la règle appliquée au champ n'a pas pour action
// DoNothing, le champ est ajouté dans la liste pour être mis à jour.
IStoreRule rule = this.GetStoreRule(property.PropertyName);
ValueRule valueRule = null;
if (rule != null)
{
object value = property.GetValue(bean);
if (value != null)
{
ExtendedValue extValue = value as ExtendedValue;
if (extValue != null)
{
value = extValue.Value;
}
}
valueRule = rule.GetUpdateValue(value);
if (valueRule != null && ActionRule.DoNothing.Equals(valueRule.Action))
{
continue;
}
}
BuildUpdateSet(sbUpdateSet, count, property, valueRule);
// Contrainte de la valeur à mettre à jour
BuildUpdateWhere(bean, sbUpdateWhere, property, rule);
count++;
}
sbUpdate.Append(sbUpdateSet).Append(sbUpdateWhere);
return(sbUpdate.ToString());
}
/// <summary>
/// Ajoute les paramètres à une commande de mise à jour.
/// </summary>
/// <param name="bean">Bean à mettre à jour.</param>
/// <param name="beanDefinition">Définition du bean.</param>
/// <param name="parameters">Paramètres de la commande SQL.</param>
/// <param name="columnSelector">Selecteur de colonnes à mettre à jour ou à ignorer.</param>
protected void AddUpdateParameters(T bean, BeanDefinition beanDefinition, SqlServerParameterCollection parameters, ColumnSelector columnSelector)
{
if (beanDefinition == null)
{
throw new ArgumentNullException("beanDefinition");
}
if (parameters == null)
{
throw new ArgumentNullException("parameters");
}
foreach (BeanPropertyDescriptor property in beanDefinition.Properties)
{
if (property.MemberName == null || (columnSelector != null && !columnSelector.ColumnList.Contains(property.MemberName) && !property.IsPrimaryKey))
{
continue;
}
object value = property.GetValue(bean);
if (value != null)
{
ExtendedValue extValue = value as ExtendedValue;
if (extValue != null)
{
value = extValue.Value;
}
}
if (property.IsPrimaryKey)
{
AddPrimaryKeyParameter(parameters, property.MemberName, value);
}
IStoreRule rule = this.GetStoreRule(property.PropertyName);
ValueRule valueRule = null;
if (rule != null)
{
valueRule = rule.GetUpdateValue(value);
if (valueRule != null && valueRule.Action == ActionRule.DoNothing)
{
continue;
}
}
if (valueRule == null)
{
// Ajout du paramètre en entrée de la commande envoyée à SQL Server.
SqlServerParameter parameter = AddParameter(parameters, property, value);
if (property.PrimitiveType == typeof(byte[]))
{
parameter.DbType = DbType.Binary;
}
}
else
{
parameters.AddWithValue(property.MemberName, valueRule.Value);
}
// Contrainte de la valeur à mettre à jour
if (rule != null)
{
valueRule = rule.GetWhereClause(value);
if (valueRule != null && valueRule.Action != ActionRule.DoNothing)
{
parameters.AddWithValue("RU_" + property.MemberName, valueRule.Value);
}
}
}
}
private static RowData CreateRowData(int sheetId, DataRow row, int fgColorRow, int bgColorRow)
{
// https://github.com/opendatakit/aggregate/blob/master/src/main/java/org/opendatakit/aggregate/externalservice/GoogleSpreadsheet.java
// define row cell formats
var stringFormat = new CellFormat()
{
BackgroundColor = GetColor(bgColorRow),
TextFormat = new TextFormat()
{
ForegroundColor = GetColor(fgColorRow)
}
};
var dateFormat = new CellFormat()
{
BackgroundColor = GetColor(bgColorRow),
TextFormat = new TextFormat()
{
ForegroundColor = GetColor(fgColorRow)
},
NumberFormat = new NumberFormat()
{
Type = "DATE",
Pattern = "dd.MM.yyyy"
}
};
var percentFormat = new CellFormat()
{
BackgroundColor = GetColor(bgColorRow),
TextFormat = new TextFormat()
{
ForegroundColor = GetColor(fgColorRow)
},
NumberFormat = new NumberFormat()
{
Type = "NUMBER",
Pattern = "##.#%"
}
};
var numberFormat = new CellFormat()
{
BackgroundColor = GetColor(bgColorRow),
TextFormat = new TextFormat()
{
ForegroundColor = GetColor(fgColorRow)
},
NumberFormat = new NumberFormat()
{
Type = "NUMBER",
Pattern = "#,##0.00;[Red]-#,##0.00;"
}
};
var cellDataList = new List <CellData>();
foreach (var item in row.ItemArray)
{
var cellData = new CellData();
if (item == null)
{
cellData.UserEnteredValue = new ExtendedValue();
}
else
{
var extendedValue = new ExtendedValue();
switch (item)
{
case bool boolValue:
extendedValue.BoolValue = boolValue;
cellData.UserEnteredValue = extendedValue;
cellData.UserEnteredFormat = stringFormat;
break;
case int intValue:
extendedValue.NumberValue = intValue;
cellData.UserEnteredValue = extendedValue;
cellData.UserEnteredFormat = numberFormat;
break;
case decimal decimalValue:
extendedValue.NumberValue = (double)decimalValue;
cellData.UserEnteredValue = extendedValue;
cellData.UserEnteredFormat = numberFormat;
break;
case DateTime dateTimeValue:
// 04.05.2018 23:59:00
// Google Sheets uses a form of epoch date that is commonly used in spreadsheets.
// The whole number portion of the value (left of the decimal) counts the days since
// December 30th 1899. The fractional portion (right of the decimal)
// counts the time as a fraction of one day.
// For example, January 1st 1900 at noon would be 2.5,
// 2 because it's two days after December 30th, 1899,
// and .5 because noon is half a day.
// February 1st 1900 at 3pm would be 33.625.
extendedValue.NumberValue = dateTimeValue.ToOADate();
cellData.UserEnteredValue = extendedValue;
cellData.UserEnteredFormat = dateFormat;
break;
case string stringValue:
extendedValue.StringValue = stringValue;
cellData.UserEnteredValue = extendedValue;
cellData.UserEnteredFormat = stringFormat;
break;
default:
extendedValue.StringValue = item.ToString();
cellData.UserEnteredValue = extendedValue;
cellData.UserEnteredFormat = stringFormat;
break;
}
}
cellDataList.Add(cellData);
}
var rowData = new RowData()
{
Values = cellDataList
};
return(rowData);
}
public void AddCells(GoogleSheetParameters googleSheetParameters, List <GoogleSheetRow> rows)
{
var requests = new BatchUpdateSpreadsheetRequest {
Requests = new List <Request>()
};
var sheetId = GetSheetId(_sheetsService, _spreadsheetId, googleSheetParameters.SheetName);
GridCoordinate gc = new GridCoordinate
{
ColumnIndex = googleSheetParameters.RangeColumnStart - 1,
RowIndex = googleSheetParameters.RangeRowStart - 1,
SheetId = sheetId
};
var request = new Request {
UpdateCells = new UpdateCellsRequest {
Start = gc, Fields = "*"
}
};
var listRowData = new List <RowData>();
foreach (var row in rows)
{
var rowData = new RowData();
var listCellData = new List <CellData>();
foreach (var cell in row.Cells)
{
var cellData = new CellData();
var extendedValue = new ExtendedValue {
StringValue = cell.CellValue
};
cellData.UserEnteredValue = extendedValue;
var cellFormat = new CellFormat {
TextFormat = new TextFormat()
};
if (cell.IsBold)
{
cellFormat.TextFormat.Bold = true;
}
cellFormat.BackgroundColor = new Color {
Blue = (float)cell.BackgroundColor.B / 255, Red = (float)cell.BackgroundColor.R / 255, Green = (float)cell.BackgroundColor.G / 255
};
cellData.UserEnteredFormat = cellFormat;
listCellData.Add(cellData);
}
rowData.Values = listCellData;
listRowData.Add(rowData);
}
request.UpdateCells.Rows = listRowData;
// It's a batch request so you can create more than one request and send them all in one batch. Just use reqs.Requests.Add() to add additional requests for the same spreadsheet
requests.Requests.Add(request);
_sheetsService.Spreadsheets.BatchUpdate(requests, _spreadsheetId).Execute();
}
public void AddCells(GoogleSheetParameters googleSheetParameters, List <GoogleSheetRow> rows)
{
try
{
var requests = new BatchUpdateSpreadsheetRequest {
Requests = new List <Request>()
};
var sheetId = GetSheetId(_sheetsService, _spreadsheetId, googleSheetParameters.SheetName);
GridCoordinate gc = new GridCoordinate
{
ColumnIndex = googleSheetParameters.RangeColumnStart - 1,
RowIndex = googleSheetParameters.RangeRowStart - 1,
SheetId = sheetId
};
var request = new Request {
UpdateCells = new UpdateCellsRequest {
Start = gc, Fields = "*"
}
};
var listRowData = new List <RowData>();
foreach (var row in rows)
{
var rowData = new RowData();
var listCellData = new List <CellData>();
foreach (var cell in row.Cells)
{
var cellData = new CellData();
var extendedValue = new ExtendedValue {
StringValue = cell.CellValue
};
cellData.UserEnteredValue = extendedValue;
var cellFormat = new CellFormat {
TextFormat = new TextFormat()
};
if (cell.IsBold)
{
cellFormat.TextFormat.Bold = true;
}
cellFormat.BackgroundColor = new Color {
Blue = (float)cell.BackgroundColor.B / 255, Red = (float)cell.BackgroundColor.R / 255, Green = (float)cell.BackgroundColor.G / 255
};
cellData.UserEnteredFormat = cellFormat;
listCellData.Add(cellData);
}
rowData.Values = listCellData;
listRowData.Add(rowData);
}
request.UpdateCells.Rows = listRowData;
// It's a batch request so you can create more than one request and send them all in one batch. Just use reqs.Requests.Add() to add additional requests for the same spreadsheet
requests.Requests.Add(request);
_sheetsService.Spreadsheets.BatchUpdate(requests, _spreadsheetId).Execute();
}
catch (AggregateException err)
{
foreach (var errInner in err.InnerExceptions)
{
Console.WriteLine(errInner); //this will call ToString() on the inner execption and get you message, stacktrace and you could perhaps drill down further into the inner exception of it if necessary
}
}
}
public List <RowData> createTitleBar()
{
string monthDay = "Date";
string dayName = "Day";
string timeStr = "Time";
string descriptionStr = "Description";
ExtendedValue valEmpty = new ExtendedValue();
ExtendedValue valMonthDay = new ExtendedValue();
ExtendedValue valDayName = new ExtendedValue();
ExtendedValue valTime = new ExtendedValue();
ExtendedValue valDescription = new ExtendedValue();
valEmpty.StringValue = "";
valMonthDay.StringValue = monthDay;
valDayName.StringValue = dayName;
valTime.StringValue = timeStr;
valDescription.StringValue = descriptionStr;
CellData cellEmpty = new CellData();
CellData cellMonthDay = new CellData();
CellData cellDayName = new CellData();
CellData cellTime = new CellData();
CellData cellDescription = new CellData();
CellFormat formatTitle = m_formatTitle;
cellEmpty.UserEnteredValue = valEmpty;
cellEmpty.UserEnteredFormat = formatTitle;
cellMonthDay.UserEnteredValue = valMonthDay;
cellMonthDay.UserEnteredFormat = formatTitle;
cellDayName.UserEnteredValue = valDayName;
cellDayName.UserEnteredFormat = formatTitle;
cellTime.UserEnteredValue = valTime;
cellTime.UserEnteredFormat = formatTitle;
cellDescription.UserEnteredValue = valDescription;
cellDescription.UserEnteredFormat = formatTitle;
RowData rowData1 = new RowData();
rowData1.Values = new List <CellData>();
rowData1.Values.Add(cellEmpty);
rowData1.Values.Add(cellMonthDay);
rowData1.Values.Add(cellDayName);
rowData1.Values.Add(cellTime);
rowData1.Values.Add(cellDescription);
RowData rowData2 = new RowData();
rowData2.Values = new List <CellData>();
rowData2.Values.Add(cellEmpty);
rowData2.Values.Add(cellEmpty);
rowData2.Values.Add(cellEmpty);
rowData2.Values.Add(cellEmpty);
rowData2.Values.Add(cellEmpty);
List <RowData> rowsData = new List <RowData>();
rowsData.Add(rowData1);
rowsData.Add(rowData2);
return(rowsData);
}
public override int GetHashCode()
{
return(ExtendedValue.GetHashCode());
}
