public FieldText Update(FieldText updatedFieldText)
{
var entity = db.FieldText.Attach(updatedFieldText);
entity.State = Microsoft.EntityFrameworkCore.EntityState.Modified;
return(updatedFieldText);
}
static void CreateExpenseSiteColumns()
{
fldExpenseCategory = clientContext.CastTo <FieldChoice>(CreateSiteColumn("ExpenseCategory", "Expense Category", "Choice"));
string[] choicesExpenseCategory = ExpenseCategory.GetAll();
fldExpenseCategory.Choices = choicesExpenseCategory;
fldExpenseCategory.Update();
clientContext.ExecuteQuery();
fldExpenseDate = clientContext.CastTo <FieldDateTime>(CreateSiteColumn("ExpenseDate", "Expense Date", "DateTime"));;
fldExpenseDate.DisplayFormat = DateTimeFieldFormatType.DateOnly;
fldExpenseDate.Update();
fldExpenseAmount = clientContext.CastTo <FieldCurrency>(CreateSiteColumn("ExpenseAmount", "Expense Amount", "Currency"));
fldExpenseAmount.MinimumValue = 0;
fldExpenseBudgetYear = clientContext.CastTo <FieldText>(CreateSiteColumn("ExpenseBudgetYear", "Budget Year", "Text"));
fldExpenseBudgetQuarter = clientContext.CastTo <FieldText>(CreateSiteColumn("ExpenseBudgetQuarter", "Budget Quarter", "Text"));
fldExpenseBudgetQuarter.Update();
fldExpenseBudgetAmount = clientContext.CastTo <FieldCurrency>(CreateSiteColumn("ExpenseBudgetAmount", "Budget Amount", "Currency"));
clientContext.ExecuteQuery();
}
protected void AutoComplete()
{
string complete = string.Empty;
var command = FieldText.ToString();
string[] cmd;
if (!Util.TrySplitCommand(command, out cmd))
{
return;
}
if (cmd.Length > 1)
{
Command result;
if (CommandSet.TryGetValue(cmd [0], out result))
{
complete = result.AutoComplete(command);
}
}
else
{
foreach (var id in CommandSet.Keys)
{
if (id.StartsWith(command))
{
complete = id.Remove(0, command.Length);
}
}
}
AddText(complete);
Console.Write(complete);
}
public async Task <IActionResult> PutFieldText([FromRoute] int id, [FromBody] FieldText FieldText)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
if (id != FieldText.Id)
{
return(BadRequest());
}
_context.Entry(FieldText).State = EntityState.Modified;
try
{
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!FieldTextExists(id))
{
return(NotFound());
}
else
{
throw;
}
}
return(NoContent());
}
public IActionResult OnGet(int id)
{
FieldText = languageData.GetById(id);
if (FieldText == null)
{
return(RedirectToPage("./NotFound"));
}
return(Page());
}
public FieldText Update(FieldText updatedFieldText)
{
var FieldText = FieldTexts.SingleOrDefault(x => x.Id == updatedFieldText.Id);
if (FieldText != null)
{
}
return(FieldText);
}
public async Task <IActionResult> PostFieldText([FromBody] FieldText FieldText)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
_context.FieldText.Add(FieldText);
await _context.SaveChangesAsync();
return(CreatedAtAction("GetFieldText", new { id = FieldText.Id }, FieldText));
}
public void ToCustomText()
{
var f1 = new FieldText("Format", "Format to apply", "yyyy/MM/dd HH:mm:ss", ".+", "Format is empty");
new InputBox(_excelapp, "To custom date-time", f1).Show(delegate {
var format = f1.Value;
new CellsOperator(_range).ForEachValue("@", delegate(ref object value) {
System.DateTime datetime;
if (Parser.ToDateTime(value, _culture, out datetime))
value = datetime.ToString(format, _culture);
});
});
}
public static void SetUp(ClientContext ctx)
{
Web root = ctx.Site.RootWeb;
string pathToXML = AppDomain.CurrentDomain.BaseDirectory + "CVContentType.xml";
root.CreateFieldsFromXMLFile(pathToXML);
root.CreateContentTypeFromXMLFile(pathToXML);
List CVs = null;
if (root.ListExists("CV's"))
{
CVs = root.GetListByTitle("CV's");
}
else
{
CVs = root.CreateList(ListTemplateType.DocumentLibrary, "CV's", true, true, "CVS", true);
}
CVs.AddContentTypeToListByName("CV", true);
CVs.RemoveContentTypeByName("Document");
FieldText titleField = CVs.GetFieldById <FieldText>("{fa564e0f-0c70-4ab9-b863-0177e6ddd247}".ToGuid());
titleField.Title = "CV Description";
titleField.Update();
ctx.ExecuteQuery();
AddViewFields(ctx, CVs);
string query = @"<OrderBy>
<FieldRef Name='Modified' Ascending='FALSE' />
</OrderBy>
<Where>
<And>
<Eq>
<FieldRef Name='CV_bool' />
<Value Type='Boolean' >1</Value>
</Eq>
<Gt>
<FieldRef Name='Created' />
<Value Type='DateTime' >2017-01-01T00:00:00Z</Value>
</Gt>
</And>
</Where>
";
CVs.CreateView("Active CV's 2", ViewType.Html, new string[] { "Title", "CV_Picture", "CV_Person" }, 10, false, query);
}
private void GuessFieldName(object fielddata)
{
FieldData fd = (FieldData)fielddata;
try
{
if (Same(GetPageResource(fd.SourceUrl + "and exists (select [" + fd.FieldName + "] from [" + fd.ListName + "])").Length, fd.HtmlText.Length))
{
Dispatcher.Invoke(DispatcherPriority.Normal, new Action(() => { FieldText.AppendText(fd.FieldName + "\r\n"); }));
}
Dispatcher.Invoke(DispatcherPriority.Normal, new Action(() => { progressBar.Value += 1; }));
}
catch { Dispatcher.Invoke(DispatcherPriority.Normal, new Action(() => { progressBar.Value += 1; })); }
return;
}
public void Add()
{
var f1 = new FieldText("Characters", "Characters to add", "");
var f2 = new FieldRadioBT("", new string[] { "Left", "Rigth" }, "Position", 0, true);
new InputBox(_excelapp, "Add", f1, f2).Show(delegate {
string str = f1.Value;
bool left = f2.Value == 0;
new CellsOperator(_range).ForEachValue(null, delegate(ref object value) {
if (value is string) {
if (left)
value = str + (string)value;
else
value = (string)value + str;
}
});
});
}
public void CellsValue()
{
var f1 = new FieldText("String surround", "Surround character for strings", "\"", true);
var f2 = new FieldText("Number surround", "Surround character for numbers", "", true);
var f3 = new FieldText("Empty value", "Text for empty values", "\"\"", true);
var f7 = new FieldText("DateTime format", "DateTime format", "yyyy-MM-ddTHH:mm:ss", true);
var f4 = new FieldText("Cells delimiter", "Delimiter character for cells", ",", true);
var f5 = new FieldText("Row surround", "Surround character for rows", "{}", true);
var f6 = new FieldText("Rows delimiter", "Delimiter character for rows", @",\r\n", true);
new InputBox(_excelapp, "Copy joined cells value", f1, f2, f3, f7, f4, f5, f6).Show(delegate {
string stringSurroundLeft, stringSurroundRight, numSurroundLeft, numSurroundRight, rowSurroundLeft, rowSurroundRight;
ExtractSurround(f1.Value, out stringSurroundLeft, out stringSurroundRight);
ExtractSurround(f2.Value, out numSurroundLeft, out numSurroundRight);
ExtractSurround(f5.Value, out rowSurroundLeft, out rowSurroundRight);
string emptyValue = f3.Value == null ? "" : f3.Value;
string dtFormat = f7.Value;
string cellsDelimiter = f4.Value.Replace(@"\r", "\r").Replace(@"\n", "\n").Replace(@"\t", "\t");
string rowsDelimiter = f6.Value.Replace(@"\r", "\r").Replace(@"\n", "\n").Replace(@"\t", "\t");
StringBuilder sb = new StringBuilder();
new CellsOperator(_range, true).ForEachValue(delegate(object value, bool isFirstCol, bool isLastCol) {
if (isFirstCol) {
if (sb.Length != 0)
sb.Append(rowsDelimiter);
sb.Append(rowSurroundLeft);
} else
sb.Append(cellsDelimiter);
if (value == null)
sb.Append(emptyValue);
else if (value is double)
sb.Append(numSurroundLeft + value + numSurroundRight);
else if (value is string)
sb.Append(stringSurroundLeft + (string)value + stringSurroundRight);
else if (value is DateTime)
sb.Append(stringSurroundLeft + ((DateTime)value).ToString(dtFormat) + stringSurroundLeft);
else
sb.Append(stringSurroundLeft + value.ToString() + stringSurroundRight);
if (isLastCol)
sb.Append(rowSurroundRight);
});
if (sb.Length != 0)
System.Windows.Forms.Clipboard.SetText(sb.ToString());
});
}
public void CellsText()
{
var f1 = new FieldText("Cells delimiter", "Delimiter character", ";", true);
var f2 = new FieldCheckBox("Include empty cells", "", false);
new InputBox(_excelapp, "Copy joined cells text", f1, f2).Show(delegate {
var cellDelimiter = f1.Value;
var rowDelimiter = f1.Value;
var skipEmptyCells = f2.Value == false;
try {
_excelapp.ScreenUpdating = false;
System.Windows.Forms.Clipboard.Clear();
StringBuilder sb = new StringBuilder();
foreach (Excel.Range area in _range.Areas) {
var range = _excelapp.Intersect(area, area.Worksheet.UsedRange);
if (range == null) break;
range.Copy();
sb.Append(System.Windows.Forms.Clipboard.GetText());
}
_excelapp.CutCopyMode = 0;
StringBuilder res = new StringBuilder();
int len = sb.Length - 2;
for (int i = 0; i < len; i++) {
if (sb[i] == '\t') {
if (skipEmptyCells && (sb[i + 1] == '\t' || (sb[i + 1] == '\r' && sb[i + 2] == '\n')))
continue;
else
res.Append(cellDelimiter);
} else if (sb[i] == '\r' && sb[i + 1] == '\n') {
i++;
if (skipEmptyCells && (sb[i + 2] == '\r' || sb[i + 2] == '\t'))
continue;
else
res.Append(rowDelimiter);
} else {
res.Append(sb[i]);
}
}
System.Windows.Forms.Clipboard.SetText(res.ToString());
} catch { throw; } finally {
_excelapp.ScreenUpdating = true;
}
});
}
public void Pad()
{
var f1 = new FieldText("Character", "Character to add", "0", "^.$", "One character only");
var f2 = new FieldNumber("Length", "Total length", 10d, 1, 1000);
var f3 = new FieldRadioBT("Insert position", new[] { "Left", "Rigth" }, "Insert position", 0);
new InputBox(_excelapp, "Padding", f1, f2, f3).Show(delegate {
char char_ = f1.Value[0];
int len = (int)(f2.Value ?? 0);
if (f3.Value == 0) {
new CellsOperator(_range).ForEachValue("@", delegate(ref object value) {
value = value.ToString().PadLeft(len, char_);
});
} else if (f3.Value == 1) {
new CellsOperator(_range).ForEachValue("@", delegate(ref object value) {
value = value.ToString().PadRight(len, char_);
});
}
});
}
public void Combination()
{
var f1 = new FieldText("Set of values", "List of values seperated by a semicolon", "a;b;c", "^.+;.+$", "A minimum of 2 values is required", true);
var f2 = new FieldNumber("Length (k)", "Length (k)", 2, 1, true);
var f3 = new FieldCheckBox("With permutation : AB => AB,BA...", "", false, true);
var f4 = new FieldCheckBox("With repetition : AB => AA,BB,AB...", "", false, true);
var f5 = new FieldCheckBox("With sub length : AB => A,B,AB...", "", false, true);
new InputBox(_excelapp, "Generate combination", f1, f2, f3, f4, f5).Show(delegate {
var values = f1.Value.Split(';');
var n = values.Length;
var k = (int)f2.Value;
var order = f3.Value;
var repeat = f4.Value;
var subset = f5.Value;
if (k > n)
throw new InputError("Length (k) has to be superior or equal to the number of values");
var data = Combinaison.Get(values, k, order, repeat, subset);
CellsOperator.AddDataToRange(_range, data);
});
}
public IActionResult OnGet(int?id)
{
if (id.HasValue)
{
FieldText = languageData.GetById(id.Value);
ViewData["Title"] = "Edit";
}
else
{
ViewData["Title"] = "Add a word/phrase";
FieldText = new FieldText();
FieldText.FieldType = FieldType.XAMARIN;
}
Languages = htmlHelper.GetEnumSelectList <FieldType>();
if (FieldText == null)
{
return(RedirectToPage("./NotFound"));
}
return(Page());
}
protected void ProcessCommand()
{
string cmd = FieldText.ToString();
CommandHistory.Add(cmd);
Console.WriteLine();
string cmdId = cmd.ReadToCharOrEnd(' ');
if (CommandSet.ContainsKey(cmdId))
{
var result = RunCommand(FieldText.ToString());
if (result.Resultcode != 0)
{
Console.WriteLine(result.Message);
}
}
else
{
Console.WriteLine("\"{0}\" is not recognized as a command. Try \"help\"", cmdId);
}
EndOfInput = true;
}
private static string GetDefaultFormat(Field f)
{
string rc = string.Empty;
switch (f.FieldTypeKind)
{
case FieldType.Text:
{
FieldText ft = f as FieldText;
if (ft != null)
{
rc = string.Format("$CHAR{0}.", ft.MaxLength);
}
}
break;
case FieldType.DateTime:
rc = "DATETIME16.";
break;
}
return(rc);
}
protected virtual void RegisterBaseKeys()
{
FunctionKeys[new ConsoleKeyInfo('\0', ConsoleKey.RightArrow, false, false, false)] = () => MoveCursor();
FunctionKeys[new ConsoleKeyInfo('\0', ConsoleKey.RightArrow, false, false, true)] = () => {
MoveCursor(FieldText.ToString().Substring(CurrentChar)
.TakeWhile(c => !char.IsWhiteSpace(c)).Count() + 1);
};
FunctionKeys[new ConsoleKeyInfo('\0', ConsoleKey.LeftArrow, false, false, false)] = () => MoveCursor(-1);
FunctionKeys[new ConsoleKeyInfo('\0', ConsoleKey.LeftArrow, false, false, true)] = () => {
MoveCursor(-FieldText.ToString().Reverse().Skip(FieldText.Length - CurrentChar + 1)
.TakeWhile(c => !char.IsWhiteSpace(c)).Count() - 1);
};
FunctionKeys[new ConsoleKeyInfo('\r', ConsoleKey.Enter, false, false, false)] = () => {
AddText('\n');
MoveCursor();
};
FunctionKeys[new ConsoleKeyInfo('\b', ConsoleKey.Backspace, false, false, false)] = () => {
if (FieldText.Length == 0 || CurrentChar == 0)
{
return;
}
FieldRows[CurrentRow].Remove(CurrentChar - 1, 1);
SyncBuffer(CurrentRow);
MoveCursor(-1);
};
FunctionKeys[new ConsoleKeyInfo('\0', ConsoleKey.Delete, false, false, false)] = () => {
if (FieldText.Length == 0 || CurrentChar == FieldText.Length)
{
return;
}
ClearBuffer();
FieldText.Remove(CurrentChar, 1);
};
}
private void GenExperiment(Experiment experiment)
{
var fields = new FieldText[experiment.NbFactor];
for (int i = 0; i < experiment.NbFactor; i++) {
fields[i] = new FieldText("F" + (i + 1).ToString() + " values (" + experiment.NbLevels[i] + ")", "List of values seperated by a semicolon", "");
fields[i].MaxValues = experiment.NbLevels[i];
}
new InputBox(_excelapp, "Generate experiment", fields).Show(delegate {
var valuesList = new List<string[]>(experiment.NbFactor);
for (int i = 0; i < fields.Length; i++) {
if (string.IsNullOrEmpty(fields[i].Value))
valuesList.Add(null);
else {
valuesList.Add(fields[i].Value.Split(';'));
}
}
CellsOperator.AddDataToRange(_range, experiment.GetArray(valuesList));
});
}
public void String()
{
var f1 = new FieldText("Pattern", @"\d=0-9 \w=A-Za-z0-9_ \H=0-9A-F", @"[A-F]-\d{3}-\d{3,10}", true);
var f2 = new FieldCheckBox("Generate unique values", "", false, true);
new InputBox(_excelapp, "Random string generation", f1, f2, _fnb).Show(delegate {
if (f1.Value.Length < 1)
throw new InputError("Pattern field is empty");
var gen = new RandomString(f1.Value, f2.Value);
string format = "@";
if (_singlesel)
CellsOperator.AddDataToRange(_range, (int)_fnb.Value, format, (ref object value) => { value = gen.Next(); });
else
new CellsOperator(_range, true).ForEachValue(format, (ref object value) => { value = gen.Next(); });
});
}
public void Split()
{
if (_range.Areas.Count != 1 || _range.Areas[1].Columns.Count != 1) {
MsgBox.ShowError(_excelapp, "Select one column only.");
return;
}
var f0 = new FieldRadioBT("Type", new string[] { "Separator", "Regex" }, null, 0, true);
var f1 = new FieldText("Separator", "String separator", "", true);
var f2 = new FieldText("Regex pattern", "Regex pattern", @"[^ ]+", true);
f0.ValueChanged += (sender, e) => {
f1.Control.Enabled = f0.Value == 0;
f2.Control.Enabled = f0.Value == 1;
};
f2.Control.Enabled = false;
new InputBox(_excelapp, "Replace (Regex)", f0, f1, f2).Show(delegate {
bool useSeparator = f0.Value == 0;
var range = (Excel.Range)_range.Areas[1].Columns[1];
try {
_excelapp.ScreenUpdating = false;
Undo.AddUndoArea(range);
range.Copy();
var data = Clipboard.GetText();
_excelapp.CutCopyMode = 0;
var res = new StringBuilder();
if (useSeparator) {
var separator = f1.Value;
res.Append(data.Replace(separator, "\t"));
} else {
string pattern = f2.Value;
var rows = data.Split(new string[] { "\r\n" }, StringSplitOptions.None);
int nbRows = rows.Length;
for (int r = 0; r < nbRows; r++) {
var matches = Regex.Matches(rows[r], pattern);
int nbMatch = matches.Count;
for (int i = 0; i < nbMatch; i++) {
if (i != 0) res.Append('\t');
res.Append(matches[i].Value);
}
res.Append("\r\n");
}
}
Clipboard.SetText(res.ToString());
range.PasteSpecial();
Clipboard.Clear();
(_excelapp.ActiveWindow.RangeSelection).NumberFormat = "";
} catch { throw; } finally {
_excelapp.ScreenUpdating = true;
}
});
}
public SPRemoteEventResult ProcessEvent(SPRemoteEventProperties properties)
{
SPRemoteEventResult result = new SPRemoteEventResult();
//get the host web url
Uri hostUrl = properties.AppEventProperties.HostWebFullUrl;
//get the operation context so we can figure out the host URL for this service, from
//which we can get the Authority for a client context
System.ServiceModel.OperationContext oc = System.ServiceModel.OperationContext.Current;
Uri localUrl = null;
//UPDATE: THIS CODE WORKED FINE FOR WHEN YOU USE THE WEB APP CREATED BY VS.NET
//FOR THE SHAREPOINT APP, BUT IT BREAKS WHEN YOU DEPLOY TO A REAL IIS SERVER
//BECAUSE YOU END UP GETTING TWO BASEADDRESSES, BUT NOT WITH THE RIGHT SCHEME.
//FOR EXAMPLE, THE FIRST ONE IS THE ADDRESS OF THIS HOST BUT WITH THE HTTP SCHEME.
//THE SECOND ONE IS HTTPS, BUT THE HOST NAME IS THE FQDN OF THE SERVER. SINCE
//SHAREPOINT DOESN'T RECOGNIZE THAT AS THE ENDPOINT IT TRUSTS FOR THIS CODE, IT BLOWS
//UP WHEN RUNNING THE CODE BELOW AND THE WHOLE THING FAILS
#region Code That Breaks In IIS
////now enumerate through the Host base addresses and look for the SSL connection
//foreach (Uri u in oc.Host.BaseAddresses)
//{
// if (u.Scheme.ToLower() == "https")
// {
// localUrl = u;
// break;
// }
//}
#endregion
//assume first base address is ours, which it has been so far
if (oc.Host.BaseAddresses.Count > 0)
{
localUrl = oc.Host.BaseAddresses[0];
}
//make sure we found our local URL
if (localUrl != null)
{
//using (ClientContext ctx = TokenHelper.CreateAppEventClientContext(properties, false))
using (ClientContext ctx = TokenHelper.GetClientContextWithContextToken(hostUrl.ToString(), properties.ContextToken, localUrl.Authority))
{
if (ctx != null)
{
//try to retrieve the list first to see if it exists
List l = ctx.Web.Lists.GetByTitle(LIST_NAME);
ctx.Load(l);
//have to put in a try block because of course it throw an exception if
//list doesn't exist
try
{
ctx.ExecuteQuery();
}
catch (Exception noListEx)
{
//look to see if the exception is that the list doesn't exist
if (noListEx.Message.ToLower().Contains("does not exist"))
{
//code here to create list
Web web = ctx.Web;
ListCreationInformation ci = new ListCreationInformation();
ci.Title = LIST_NAME;
ci.TemplateType = (int)ListTemplateType.GenericList;
ci.QuickLaunchOption = QuickLaunchOptions.Off;
l = web.Lists.Add(ci);
l.Description = "List for tracking events with the Event Planner Social App";
Field fldEventName = l.Fields.AddFieldAsXml("<Field DisplayName='EventName' Type='Text' />", true, AddFieldOptions.DefaultValue);
Field fldSiteUrl = l.Fields.AddFieldAsXml("<Field DisplayName='SiteUrl' Type='Text' />", true, AddFieldOptions.DefaultValue);
Field fldTwitterTags = l.Fields.AddFieldAsXml("<Field DisplayName='TwitterTags' Type='Text' />", true, AddFieldOptions.DefaultValue);
Field fldEventDate = l.Fields.AddFieldAsXml("<Field DisplayName='EventDate' Type='DateTime' />", true, AddFieldOptions.DefaultValue);
FieldDateTime dtEventDate = ctx.CastTo <FieldDateTime>(fldEventDate);
dtEventDate.DisplayFormat = DateTimeFieldFormatType.DateOnly;
dtEventDate.Update();
Field fldGraphID = l.Fields.AddFieldAsXml("<Field DisplayName='ObjectGraphID' Type='Text' />", true, AddFieldOptions.DefaultValue);
FieldText txtGraphID = ctx.CastTo <FieldText>(fldGraphID);
txtGraphID.Indexed = true;
txtGraphID.Update();
l.Hidden = true;
l.Update();
try
{
//this creates the list
ctx.ExecuteQuery();
//all of the rest of this is to remove the list from the "Recent" list that appears
//in sites by default, which is really a set of navigation links
//get the site and root web, where the navigation lives
Site s = ctx.Site;
Web rw = s.RootWeb;
//get the QuickLaunch navigation, which is where the Recent nav lives
ctx.Load(rw, x => x.Navigation, x => x.Navigation.QuickLaunch);
ctx.ExecuteQuery();
//now extract the Recent navigation node from the collection
var vNode = from NavigationNode nn in rw.Navigation.QuickLaunch
where nn.Title == "Recent"
select nn;
NavigationNode nNode = vNode.First <NavigationNode>();
//now we need to get the child nodes of Recent, that's where our list should be found
ctx.Load(nNode.Children);
ctx.ExecuteQuery();
var vcNode = from NavigationNode cn in nNode.Children
where cn.Title == LIST_NAME
select cn;
//now that we have the node representing our list, delete it
NavigationNode cNode = vcNode.First <NavigationNode>();
cNode.DeleteObject();
ctx.ExecuteQuery();
}
catch (Exception newListFailEx)
{
Debug.WriteLine("Creation of new list failed: " + newListFailEx.Message);
}
}
}
//okay, so if we're here then the list should exist, and we should be good to go at this point
}
}
}
#region OOB Template Code
//using (ClientContext clientContext = TokenHelper.CreateAppEventClientContext(properties, false))
//{
// if (clientContext != null)
// {
// clientContext.Load(clientContext.Web);
// clientContext.ExecuteQuery();
// }
//}
#endregion
return(result);
}
public static void CreateOrderDetailsList()
{
Console.WriteLine("Creating order details list...");
if (OrdersListExists())
{
ListCreationInformation listInformationOrderDetails = new ListCreationInformation();
listInformationOrderDetails.Title = "OrderDetails";
listInformationOrderDetails.Url = "Lists/OrderDetails";
listInformationOrderDetails.QuickLaunchOption = QuickLaunchOptions.On;
listInformationOrderDetails.TemplateType = (int)ListTemplateType.GenericList;
listOrderDetails = site.Lists.Add(listInformationOrderDetails);
listOrderDetails.OnQuickLaunch = true;
listOrderDetails.EnableAttachments = false;
listOrderDetails.Update();
clientContext.ExecuteQuery();
listOrderDetails.DefaultView.ViewFields.RemoveAll();
listOrderDetails.DefaultView.ViewFields.Add("ID");
listOrderDetails.DefaultView.Update();
clientContext.ExecuteQuery();
var fldTitle = listOrderDetails.Fields.GetByInternalNameOrTitle("Title");
fldTitle.Required = false;
fldTitle.Update();
clientContext.ExecuteQuery();
string fldOrderLookupXml = @"<Field Name='OrderId' DisplayName='Order' Type='Lookup' ></Field>";
FieldLookup fldOrderLookup =
clientContext.CastTo <FieldLookup>(listOrderDetails.Fields.AddFieldAsXml(fldOrderLookupXml,
true,
AddFieldOptions.AddFieldInternalNameHint));
// add cusotmer lookup field
fldOrderLookup.LookupField = "ID";
fldOrderLookup.LookupList = listOrders.Id.ToString();
fldOrderLookup.Indexed = true;
fldOrderLookup.RelationshipDeleteBehavior = RelationshipDeleteBehaviorType.Cascade;
fldOrderLookup.Update();
// add quantity field
string fldQuantityXml = @"<Field Name='Quantity' DisplayName='Quantity' Type='Number' ></Field>";
FieldNumber fldQuantity =
clientContext.CastTo <FieldNumber>(listOrderDetails.Fields.AddFieldAsXml(fldQuantityXml,
true,
AddFieldOptions.DefaultValue));
fldQuantity.Update();
// add product field
string fldProductXml = @"<Field Name='Product' DisplayName='Product' Type='Text' ></Field>";
FieldText fldProduct =
clientContext.CastTo <FieldText>(listOrderDetails.Fields.AddFieldAsXml(fldProductXml,
true,
AddFieldOptions.DefaultValue));
fldProduct.Update();
string fldSalesAmountXml = @"<Field Name='SalesAmount' DisplayName='SalesAmount' Type='Currency' ></Field>";
FieldCurrency fldSalesAmount =
clientContext.CastTo <FieldCurrency>(listOrderDetails.Fields.AddFieldAsXml(fldSalesAmountXml,
true,
AddFieldOptions.DefaultValue));
fldSalesAmount.Update();
clientContext.ExecuteQuery();
//listOrderDetails.DefaultView.ViewFields.Remove("Title");
listOrderDetails.DefaultView.Update();
clientContext.ExecuteQuery();
}
else
{
Console.WriteLine("Cannot create OrderDetails list because Orders list does not exist.");
}
}
public FieldText Add(FieldText newFieldText)
{
FieldTexts.Add(newFieldText);
newFieldText.Id = FieldTexts.Max(l => l.Id) + 1;
return(newFieldText);
}
public void RegexReplace()
{
var f1 = new FieldText("Pattern", "Regular expression pattern to match.", @"^(.*)$", true);
var f2 = new FieldText("Replacement", "Replacement string", @"\1", true);
new InputBox(_excelapp, "Replace (Regex)", f1, f2).Show(delegate {
string pattern = f1.Value;
string replacement = System.Text.RegularExpressions.Regex.Replace(f2.Value, @"\\(\d)", @"$$1").Replace(@"\t", "\t");
new CellsOperator(_range).ForEachValue("@", delegate(ref object value) {
value = System.Text.RegularExpressions.Regex.Replace(value.ToString(), pattern, replacement);
});
});
}
public void Columns()
{
var defaultTemplate = new StringBuilder();
var nbCol = _range.Columns.Count;
for (int i = 1; i <= nbCol; i++)
defaultTemplate.Append("\\" + i);
var f0 = new FieldRadioBT("Use", new string[] { "Delimiter", "Template" }, "Select delimiter or template", 0, true);
var f1 = new FieldText("Delimiter", "Delimiter to add between columns", "", true);
var f2 = new FieldText("Template", "Template - \\n is the cell text at column n", defaultTemplate.ToString(), true);
f0.ValueChanged += (sender, e) => {
f1.Control.Enabled = f0.Value == 0;
f2.Control.Enabled = f0.Value == 1;
};
f2.Control.Enabled = false;
new InputBox(_excelapp, "Copy merged columns", f0, f1, f2).Show(delegate {
bool useDeleimiter = f0.Value == 0;
var delimiter = f1.Value;
var pattern = f2.Value;
try {
_excelapp.ScreenUpdating = false;
System.Windows.Forms.Clipboard.Clear();
var range = _excelapp.Intersect(_range, _range.Worksheet.UsedRange);
if (range == null) return;
range.Copy();
var data = System.Windows.Forms.Clipboard.GetText();
_excelapp.CutCopyMode = 0;
var res = new StringBuilder();
if (useDeleimiter) {
res.Append(data.Replace("\t", delimiter));
} else {
var rows = data.TrimEnd('\r', '\n').Split(new string[] { "\r\n" }, StringSplitOptions.None);
foreach (string row in rows) {
var cells = row.Split('\t');
string strRow = pattern;
for (int i = 0; i < cells.Length; i++)
strRow = strRow.Replace("\\" + (i + 1), cells[i]);
res.Append(strRow + "\r\n");
}
}
System.Windows.Forms.Clipboard.SetText(res.ToString());
} catch { throw; } finally {
_excelapp.ScreenUpdating = true;
}
});
}
private void populateItem(ListItem item, Row row, IDictionary <string, string> fieldMap, IDictionary <string, string> defaults, FieldCollection fields)
{
// helper
Action <string, string> setField = (fieldName, val) => {
if (!string.IsNullOrWhiteSpace(val))
{
// check if we need to convert value
var field = fields.Where(x => x.InternalName == fieldName).First();
switch (field.FieldTypeKind)
{
case FieldType.DateTime:
DateTime dtVal;
if (!DateTime.TryParse(val, out dtVal))
{
double dblVal;
if (double.TryParse(val, out dblVal))
{
dtVal = DateTime.FromOADate(dblVal);
}
else
{
throw new Exception("Could not parse " + val + " as DateTime");
}
}
item[fieldName] = dtVal;
break;
case FieldType.URL:
if (!val.StartsWith("http://", StringComparison.InvariantCultureIgnoreCase) &&
!val.StartsWith("https://", StringComparison.InvariantCultureIgnoreCase))
{
val = "http://" + val;
}
item[fieldName] = val;
break;
case FieldType.Lookup:
FieldLookup lookup = (FieldLookup)field;
item[fieldName] = getLookupValue(val, lookup);
break;
case FieldType.Currency:
double dbVal;
if (double.TryParse(val, out dbVal))
{
item[fieldName] = dbVal;
}
else
{
Console.WriteLine("Could not convert {0} to currency", val);
}
break;
case FieldType.Text:
FieldText tfield = (FieldText)field;
if (val.Length > tfield.MaxLength)
{
val = val.Substring(0, tfield.MaxLength);
Console.WriteLine("Truncating value for {0}", tfield.Title);
}
item[fieldName] = val;
break;
default:
item[fieldName] = val;
break;
}
}
};
foreach (var colname in fieldMap.Keys)
{
var fieldName = fieldMap[colname];
if (fieldName == "") // if empty, it doesn't exist in target list so skip
{
continue;
}
var val = "";
if (row.ColumnNames.Contains(colname))
{
val = row.GetValueOrEmpty(colname);
}
if (string.IsNullOrWhiteSpace(val) && defaults.ContainsKey(colname))
{
val = defaults[colname];
}
setField(fieldName, val);
}
}
/// <summary>
/// Lanza el proceso de creación de nuevas columnas
/// </summary>
/// <returns></returns>
public bool CreateColumns()
{
bool result = true;
List list = null;
bool continueProcess = true;
if (ConectionSiteOK)
{
if (SPFields.Count > 0)
{
foreach (SPFields infoField in SPFields)
{
continueProcess = true;
try
{
list = site.Web.Lists.GetByTitle(infoField.ListName);
}
catch (Exception ex)
{
SHPExecOperationsErrors.Add(GetInfoError(ex, string.Format("Alta de nuevo campo {0}", infoField.ColumnName), TypeError.Error));
continueProcess = false;
}
if (continueProcess)
{
//Configuramos...
switch (infoField.Column)
{
case TypeColumn.LineOfText:
//Linea de texto
Field newField1 = list.Fields.AddFieldAsXml(string.Format(schemaXML, infoField.Type, infoField.ColumnName), true, AddFieldOptions.AddToDefaultContentType);
newField1.StaticName = infoField.ColumnName;
newField1.Description = infoField.Description;
if (infoField.FieldText != null)
{
FieldText fldText = site.CastTo <FieldText>(newField1);
fldText.Required = infoField.FieldText.ContainInformation;
fldText.EnforceUniqueValues = infoField.FieldText.UniqueValues;
fldText.MaxLength = infoField.FieldText.MaxLength;
fldText.DefaultValue = infoField.FieldText.DefaultValue;
if (infoField.FieldText.UniqueValues)
{
fldText.Indexed = true;
}
fldText.Update();
}
else
{
continueProcess = false;
}
break;
case TypeColumn.Multiline:
//Varias líneas de texto
Field newField2 = list.Fields.AddFieldAsXml(string.Format(schemaXML, infoField.Type, infoField.ColumnName), true, AddFieldOptions.AddToDefaultContentType);
newField2.StaticName = infoField.ColumnName;
newField2.Description = infoField.Description;
if (infoField.FieldMultiText != null)
{
FieldMultiLineText fldMultiLine = site.CastTo <FieldMultiLineText>(newField2);
fldMultiLine.Required = infoField.FieldMultiText.ContainInformation;
fldMultiLine.NumberOfLines = infoField.FieldMultiText.NumLines;
fldMultiLine.RichText = infoField.FieldMultiText.RichText;
fldMultiLine.Update();
}
else
{
continueProcess = false;
}
break;
case TypeColumn.Choice:
//Eleccion
Field newField3 = list.Fields.AddFieldAsXml(string.Format(schemaXML, infoField.Type, infoField.ColumnName), true, AddFieldOptions.AddToDefaultContentType);
newField3.StaticName = infoField.ColumnName;
newField3.Description = infoField.Description;
if (infoField.FieldChoice != null)
{
FieldMultiChoice fldChoice = site.CastTo <FieldMultiChoice>(newField3);
fldChoice.Required = infoField.FieldChoice.ContainInformation;
fldChoice.EnforceUniqueValues = infoField.FieldChoice.UniqueValues;
fldChoice.DefaultValue = infoField.FieldChoice.DefaultValue;
if (infoField.FieldChoice.UniqueValues)
{
fldChoice.Indexed = true;
}
fldChoice.Choices = infoField.FieldChoice.Options;
fldChoice.Update();
}
else
{
continueProcess = false;
}
break;
case TypeColumn.Number:
//Numero
Field newField4 = list.Fields.AddFieldAsXml(string.Format(schemaXML, infoField.Type, infoField.ColumnName), true, AddFieldOptions.AddToDefaultContentType);
newField4.StaticName = infoField.ColumnName;
newField4.Description = infoField.Description;
if (infoField.FieldNumber != null)
{
FieldNumber fldNumber = site.CastTo <FieldNumber>(newField4);
fldNumber.ShowAsPercentage = infoField.FieldNumber.ShowAsPercentaje;
if (infoField.FieldNumber.MinValue != 0)
{
fldNumber.MinimumValue = infoField.FieldNumber.MinValue;
}
if (infoField.FieldNumber.MaxValue != 0)
{
fldNumber.MaximumValue = infoField.FieldNumber.MaxValue;
}
fldNumber.Update();
}
else
{
continueProcess = false;
}
break;
case TypeColumn.Lookup:
if (infoField.FieldLookup != null)
{
string required = "FALSE";
if (infoField.FieldLookup.ContainInformation)
{
required = "TRUE";
}
try
{
List sourceList = site.Web.Lists.GetByTitle(infoField.FieldLookup.LookupList);
site.Load(sourceList);
List destinationList = site.Web.Lists.GetByTitle(infoField.ListName);
site.Load(destinationList);
site.ExecuteQuery();
Field LookUpField = destinationList.Fields.AddFieldAsXml(string.Format(schemaXMLLookUp, infoField.Type, infoField.ColumnName, required, sourceList.Id, infoField.FieldLookup.LookupField), true, AddFieldOptions.DefaultValue);
LookUpField.Description = infoField.Description;
LookUpField.Update();
}
catch (Exception ex)
{
SHPExecOperationsErrors.Add(GetInfoError(ex, string.Format("Alta de nuevo campo {0}", infoField.ColumnName), TypeError.Error));
continueProcess = false;
}
}
else
{
continueProcess = false;
}
break;
case TypeColumn.UsersAndGroups:
Field newField5 = list.Fields.AddFieldAsXml(string.Format(schemaXML, infoField.Type, infoField.ColumnName), true, AddFieldOptions.AddToDefaultContentType);
newField5.StaticName = infoField.ColumnName;
newField5.Description = infoField.Description;
if (infoField.FieldUserGroup != null)
{
FieldUser fldUser = site.CastTo <FieldUser>(newField5);
fldUser.Required = infoField.FieldUserGroup.ContainInformation;
switch (infoField.FieldUserGroup.UserSelectionMode)
{
case TypeUserSelectionMode.PeopleOnly:
fldUser.SelectionMode = FieldUserSelectionMode.PeopleOnly;
break;
case TypeUserSelectionMode.PeopleAndGroups:
fldUser.SelectionMode = FieldUserSelectionMode.PeopleAndGroups;
break;
}
if (infoField.FieldUserGroup.MultiUser)
{
fldUser.AllowMultipleValues = true;
}
if (!string.IsNullOrEmpty(infoField.FieldUserGroup.UsersFromGroup))
{
Group group = site.Web.SiteGroups.GetByName(infoField.FieldUserGroup.UsersFromGroup);
try
{
site.Load(group);
site.ExecuteQuery();
}
catch (Exception ex)
{
SHPExecOperationsErrors.Add(GetInfoError(ex, string.Format("Alta de nuevo campo {0}", infoField.ColumnName), TypeError.Warning));
group = null;
}
if (group != null)
{
fldUser.SelectionGroup = group.Id;
}
}
fldUser.Update();
}
else
{
continueProcess = false;
}
break;
}
//Añadimos...
if (continueProcess)
{
try
{
site.ExecuteQuery();
}
catch (Exception ex)
{
SHPExecOperationsErrors.Add(GetInfoError(ex, string.Format("Alta de nuevo campo {0}", infoField.ColumnName), TypeError.Error));
}
}
}
list = null;
}
}
else
{
SHPExecOperationsErrors.Add(GetInfoError("Creación de columnas (CreateColumns)", "No se han indicado campos a crear", "", TypeError.Warning));
result = false;
}
}
else
{
SHPExecOperationsErrors.Add(GetInfoError("Creación de columnas (CreateColumns)", "No se ha establecido conexión con el site", "", TypeError.Warning));
result = false;
}
SPFields.Clear();
return(result);
}
public FieldTextInformation(FieldText field) : base(field)
{
MaxLength = field.MaxLength;
}
public void ToCustomText()
{
var f1 = new FieldText("Format", "Format to apply", "# ###.00", ".+", "Format is empty", true);
new InputBox(_excelapp, "To text", f1).Show(delegate {
var format = f1.Value;
new CellsOperator(_range).ForEachValue("@", delegate(ref object value) {
object number;
if (Parser.Number(value, out number))
value = ((double)number).ToString(format, CultureInfo.InvariantCulture);
});
});
}
public void Remove()
{
var f1 = new FieldText("Characters", "Characters to remove", "");
new InputBox(_excelapp, "Remove", f1).Show(delegate {
string str = f1.Value;
new CellsOperator(_range).ForEachValue(null, delegate(ref object value) {
if (value is string && ((string)value).Length != 0)
value = ((string)value).Replace(str, "");
});
});
}
public void refresh(string fielpath)
{
if (fielpath.Length != 0)
{
//WWW www = new WWW(fielpath);
//yield return www;
//MemoryStream ms = new MemoryStream(www.bytes);
TextAsset txt = Resources.Load(fielpath) as TextAsset;
MemoryStream ms = new MemoryStream(txt.bytes);
StructArry pbi = Serializer.DeserializeWithLengthPrefix <StructArry>(ms, PrefixStyle.None);
Type type = pbi.GetType();
string strname = type.Name;
switch (strname)
{
case "MsgAttackData":
MsgAttackData struct1 = pbi as MsgAttackData;
DataPool = struct1.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
AttackData dt = DataPool[i] as AttackData;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgAwakeningSkill":
MsgAwakeningSkill struct2 = pbi as MsgAwakeningSkill;
DataPool = struct2.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
AwakeningSkill dt = DataPool[i] as AwakeningSkill;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgAwakeningSkillLevel":
MsgAwakeningSkillLevel struct3 = pbi as MsgAwakeningSkillLevel;
DataPool = struct3.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
AwakeningSkillLevel dt = DataPool[i] as AwakeningSkillLevel;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgElementFactor":
MsgElementFactor struct4 = pbi as MsgElementFactor;
DataPool = struct4.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
ElementFactor dt = DataPool[i] as ElementFactor;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgBuffData":
MsgBuffData struct5 = pbi as MsgBuffData;
DataPool = struct5.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
BuffData dt = DataPool[i] as BuffData;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgCombinationData":
MsgCombinationData struct6 = pbi as MsgCombinationData;
DataPool = struct6.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
CombinationData dt = DataPool[i] as CombinationData;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgConstData":
MsgConstData struct7 = pbi as MsgConstData;
DataPool = struct7.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
ConstData dt = DataPool[i] as ConstData;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgCurrency":
MsgCurrency struct8 = pbi as MsgCurrency;
DataPool = struct8.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
Currency dt = DataPool[i] as Currency;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgDungeon":
MsgDungeon struct9 = pbi as MsgDungeon;
DataPool = struct9.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
Dungeon dt = DataPool[i] as Dungeon;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgEnemyData":
MsgEnemyData struct10 = pbi as MsgEnemyData;
DataPool = struct10.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
EnemyData dt = DataPool[i] as EnemyData;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgEvaluateBonus":
MsgEvaluateBonus struct11 = pbi as MsgEvaluateBonus;
DataPool = struct11.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
EvaluateBonus dt = DataPool[i] as EvaluateBonus;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgFieldEffect":
MsgFieldEffect struct12 = pbi as MsgFieldEffect;
DataPool = struct12.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
FieldEffect dt = DataPool[i] as FieldEffect;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgHeroData":
MsgHeroData struct13 = pbi as MsgHeroData;
DataPool = struct13.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
HeroData dt = DataPool[i] as HeroData;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgHeroLvData":
MsgHeroLvData struct14 = pbi as MsgHeroLvData;
DataPool = struct14.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
HeroLvData dt = DataPool[i] as HeroLvData;
DataStore.Add((int)dt.level, DataPool[i]);
}
break;
case "MsgHeroEvolution":
MsgHeroEvolution struct15 = pbi as MsgHeroEvolution;
DataPool = struct15.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
HeroEvolution dt = DataPool[i] as HeroEvolution;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgPotentialData":
MsgPotentialData struct16 = pbi as MsgPotentialData;
DataPool = struct16.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
PotentialData dt = DataPool[i] as PotentialData;
DataStore.Add((int)dt.element, DataPool[i]);
}
break;
case "MsgItemData":
MsgItemData struct17 = pbi as MsgItemData;
DataPool = struct17.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
ItemData dt = DataPool[i] as ItemData;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgLeaderSkill":
MsgLeaderSkill struct18 = pbi as MsgLeaderSkill;
DataPool = struct18.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
LeaderSkill dt = DataPool[i] as LeaderSkill;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgMonsterData":
MsgMonsterData struct19 = pbi as MsgMonsterData;
DataPool = struct19.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
MonsterData dt = DataPool[i] as MonsterData;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgMonsterSkill":
MsgMonsterSkill struct20 = pbi as MsgMonsterSkill;
DataPool = struct20.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
MonsterSkill dt = DataPool[i] as MonsterSkill;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgNormalSkill":
MsgNormalSkill struct21 = pbi as MsgNormalSkill;
DataPool = struct21.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
NormalSkill dt = DataPool[i] as NormalSkill;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgNormalSkillLvUp":
MsgNormalSkillLvUp struct22 = pbi as MsgNormalSkillLvUp;
DataPool = struct22.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
NormalSkillLvUp dt = DataPool[i] as NormalSkillLvUp;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgRoleData":
MsgRoleData struct23 = pbi as MsgRoleData;
DataPool = struct23.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
RoleData dt = DataPool[i] as RoleData;
DataStore.Add((int)dt.level, DataPool[i]);
}
break;
case "MsgRuneData":
MsgRuneData struct24 = pbi as MsgRuneData;
DataPool = struct24.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
RuneData dt = DataPool[i] as RuneData;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgRuneSet":
MsgRuneSet struct25 = pbi as MsgRuneSet;
DataPool = struct25.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
RuneSet dt = DataPool[i] as RuneSet;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgRuneEnhance":
MsgRuneEnhance struct26 = pbi as MsgRuneEnhance;
DataPool = struct26.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
RuneEnhance dt = DataPool[i] as RuneEnhance;
DataStore.Add((int)dt.level, DataPool[i]);
}
break;
case "MsgRuneSynchro":
MsgRuneSynchro struct27 = pbi as MsgRuneSynchro;
DataPool = struct27.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
RuneSynchro dt = DataPool[i] as RuneSynchro;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgSpecialSkill":
MsgSpecialSkill struct28 = pbi as MsgSpecialSkill;
DataPool = struct28.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
SpecialSkill dt = DataPool[i] as SpecialSkill;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgSpecialSkillLvUp":
MsgSpecialSkillLvUp struct29 = pbi as MsgSpecialSkillLvUp;
DataPool = struct29.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
SpecialSkillLvUp dt = DataPool[i] as SpecialSkillLvUp;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgDecisionFactor":
MsgDecisionFactor struct30 = pbi as MsgDecisionFactor;
DataPool = struct30.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
DecisionFactor dt = DataPool[i] as DecisionFactor;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgTaskData":
MsgTaskData struct31 = pbi as MsgTaskData;
DataPool = struct31.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
TaskData dt = DataPool[i] as TaskData;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgHeroText":
MsgHeroText struct32 = pbi as MsgHeroText;
DataPool = struct32.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
HeroText dt = DataPool[i] as HeroText;
Util.TextDic.Add(Util.GetConfigString(dt.name), Util.GetConfigString(dt.textDes));
}
break;
case "MsgDungeonText":
MsgDungeonText struct33 = pbi as MsgDungeonText;
DataPool = struct33.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
DungeonText dt = DataPool[i] as DungeonText;
Util.TextDic.Add(Util.GetConfigString(dt.name), Util.GetConfigString(dt.textDes));
}
break;
case "MsgMonsterText":
MsgMonsterText struct34 = pbi as MsgMonsterText;
DataPool = struct34.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
MonsterText dt = DataPool[i] as MonsterText;
Util.TextDic.Add(Util.GetConfigString(dt.name), Util.GetConfigString(dt.textDes));
}
break;
case "MsgFieldText":
MsgFieldText struct35 = pbi as MsgFieldText;
DataPool = struct35.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
FieldText dt = DataPool[i] as FieldText;
Util.TextDic.Add(Util.GetConfigString(dt.name), Util.GetConfigString(dt.textDes));
}
break;
case "MsgCombinationText":
MsgCombinationText struct36 = pbi as MsgCombinationText;
DataPool = struct36.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
CombinationText dt = DataPool[i] as CombinationText;
Util.TextDic.Add(Util.GetConfigString(dt.name), Util.GetConfigString(dt.textDes));
}
break;
case "MsgSkillText":
MsgSkillText struct37 = pbi as MsgSkillText;
DataPool = struct37.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
SkillText dt = DataPool[i] as SkillText;
Util.TextDic.Add(Util.GetConfigString(dt.name), Util.GetConfigString(dt.textDes));
}
break;
case "MsgCurrencyText":
MsgCurrencyText struct38 = pbi as MsgCurrencyText;
DataPool = struct38.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
CurrencyText dt = DataPool[i] as CurrencyText;
Util.TextDic.Add(Util.GetConfigString(dt.name), Util.GetConfigString(dt.textDes));
}
break;
case "MsgBuffText":
MsgBuffText struct39 = pbi as MsgBuffText;
DataPool = struct39.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
BuffText dt = DataPool[i] as BuffText;
Util.TextDic.Add(Util.GetConfigString(dt.name), Util.GetConfigString(dt.textDes));
}
break;
case "MsgItemText":
MsgItemText struct40 = pbi as MsgItemText;
DataPool = struct40.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
ItemText dt = DataPool[i] as ItemText;
Util.TextDic.Add(Util.GetConfigString(dt.name), Util.GetConfigString(dt.textDes));
}
break;
case "MsgRuneText":
MsgRuneText struct41 = pbi as MsgRuneText;
DataPool = struct41.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
RuneText dt = DataPool[i] as RuneText;
Util.TextDic.Add(Util.GetConfigString(dt.name), Util.GetConfigString(dt.textDes));
}
break;
case "MsgTaskText":
MsgTaskText struct42 = pbi as MsgTaskText;
DataPool = struct42.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
TaskText dt = DataPool[i] as TaskText;
Util.TextDic.Add(Util.GetConfigString(dt.name), Util.GetConfigString(dt.textDes));
}
break;
case "MsgWorldData":
MsgWorldData struct43 = pbi as MsgWorldData;
DataPool = struct43.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
WorldData dt = DataPool[i] as WorldData;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgAirRaidData":
MsgAirRaidData struct44 = pbi as MsgAirRaidData;
DataPool = struct44.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
AirRaidData dt = DataPool[i] as AirRaidData;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgTalentData":
MsgTalentData struct45 = pbi as MsgTalentData;
DataPool = struct45.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
TalentData dt = DataPool[i] as TalentData;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgSummonData":
MsgSummonData struct46 = pbi as MsgSummonData;
DataPool = struct46.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
SummonData dt = DataPool[i] as SummonData;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgSummonPrize":
MsgSummonPrize struct47 = pbi as MsgSummonPrize;
DataPool = struct47.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
SummonPrize dt = DataPool[i] as SummonPrize;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgStarWeight":
MsgStarWeight struct48 = pbi as MsgStarWeight;
DataPool = struct48.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
StarWeight dt = DataPool[i] as StarWeight;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgSpecialEffect":
MsgSpecialEffect struct49 = pbi as MsgSpecialEffect;
DataPool = struct49.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
SpecialEffect dt = DataPool[i] as SpecialEffect;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgFlyingEffect":
MsgFlyingEffect struct50 = pbi as MsgFlyingEffect;
DataPool = struct50.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
FlyingEffect dt = DataPool[i] as FlyingEffect;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgDistanceFactor":
MsgDistanceFactor struct51 = pbi as MsgDistanceFactor;
DataPool = struct51.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
DistanceFactor dt = DataPool[i] as DistanceFactor;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgEvaluateData":
MsgEvaluateData struct52 = pbi as MsgEvaluateData;
DataPool = struct52.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
EvaluateData dt = DataPool[i] as EvaluateData;
DataStore.Add((int)dt.id, DataPool[i]);
}
break;
case "MsgPromptText":
MsgPromptText struct53 = pbi as MsgPromptText;
DataPool = struct53.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
PromptText dt = DataPool[i] as PromptText;
Util.TextDic.Add(Util.GetConfigString(dt.name), Util.GetConfigString(dt.textDes));
}
break;
case "MsgStoryText":
MsgStoryText struct54 = pbi as MsgStoryText;
DataPool = struct54.struct_data as List <Struct>;
for (int i = 0; i < DataPool.Count; i++)
{
StoryText dt = DataPool[i] as StoryText;
Util.TextDic.Add(Util.GetConfigString(dt.name), Util.GetConfigString(dt.textDes));
}
break;
}
}
}
public void TrimCharacter()
{
var f1 = new FieldText("Characters", "Leading or trailing characters to remove", "");
var f2 = new FieldCheckBox("Remove leading characters", "", true);
var f3 = new FieldCheckBox("Remove trailing characters", "", true);
new InputBox(_excelapp, "Trim characters", f1, f2, f3).Show(delegate {
char[] chars = f1.Value.ToCharArray();
bool trimLeft = f2.Value;
bool trimRigth = f3.Value;
new CellsOperator(_range).ForEachValue(null, delegate(ref object value) {
if (value is string) {
if (trimLeft && trimRigth)
value = ((string)value).Trim(chars);
else if (trimLeft)
value = ((string)value).TrimStart(chars);
else if (trimRigth)
value = ((string)value).TrimEnd(chars);
}
});
});
}
public void ExhaustiveCombinations()
{
var fields = new FieldText[15];
for (int i = 0; i < fields.Length; i++)
fields[i] = new FieldText("Factor " + (i + 1).ToString() + " values", "List of values seperated by a semicolon", "");
new InputBox(_excelapp, "Generate exhaustive combinations", fields).Show(delegate {
List<string[]> valuesList = new List<string[]>();
for (int i = fields.Length - 1; i >= 0; i--) {
if (string.IsNullOrEmpty(fields[i].Value)) continue;
string[] fieldValues = fields[i].Value.Split(';');
if (fieldValues.Length != 0)
valuesList.Add(fieldValues);
}
if (valuesList.Count == 0)
return;
var data = Combinaison.Get(valuesList);
CellsOperator.AddDataToRange(_range, data);
});
}
public void List()
{
var f1 = new FieldText("List of values", "List of values seperated by a semicolon", "value1;value2", true);
new InputBox(_excelapp, "Random generation from a list", f1, _fnb).Show(delegate {
var values = f1.Value.Split(';');
var max = values.Length;
var random = new Random();
string format = null;
CellsOperator.CellWriter writer = (ref object value) => { value = values[random.Next(max)]; };
if (_singlesel)
CellsOperator.AddDataToRange(_range, (int)_fnb.Value, format, writer);
else
new CellsOperator(_range, true).ForEachValue(format, writer);
});
}
public FieldText Add(FieldText newFieldText)
{
db.Add(newFieldText);
return(newFieldText);
}
public void OnGet(int Id)
{
Language = new FieldText();
Language.Id = Id;
}
public void ToSQLdelete()
{
Excel.Range dataRange; string[] headers;
GetDataRangeAndHeaders(_range, out dataRange, out headers);
var f1 = new FieldText("Table name", "Name of the table", _range.ListObject == null ? "TABLENAME" : _range.ListObject.Name);
var f2 = new FieldText("Key fields", "Key fields name separated by a semicolon", string.Join(";", headers));
f2.NumberOfValues = headers.Length;
var f3 = new FieldText("Null value", "Null expression", "NULL", true);
var f4 = new FieldText("DateTime", "DateTime format", "yyyy-MM-ddTHH:mm:ss", true);
new InputBox(_excelapp, "Copy to SQL DELETE", f1, f2, f3, f4).Show(delegate {
var tablename = f1.Value;
var columns = f2.Value.Split(';');
var nullValue = f3.Value;
var datetimeformat = f4.Value;
int colId = 0;
var sql = new StringBuilder();
var condition = new StringBuilder();
new CellsOperator(_range).ForEachValue(delegate(object value, bool isFirstCol, bool isLastCol) {
if (isFirstCol == false)
condition.Append(" AND ");
condition.Append(columns[colId++] + "=");
if (value == null)
condition.Append(nullValue);
else if (value is string)
condition.Append("'" + ((string)value).Replace("'", "''") + "'");
else if (value is bool)
condition.Append((bool)value ? "TRUE" : "FALSE");
else if (value is System.DateTime)
condition.Append("'" + ((System.DateTime)value).ToString(datetimeformat) + "'");
else
condition.Append(value);
if (isLastCol) {
sql.Append("DELETE FROM " + tablename + " WHERE " + condition + ";\r\n");
condition.Length = 0;
colId = 0;
}
});
if (sql.Length != 0)
System.Windows.Forms.Clipboard.SetText(sql.ToString());
});
}
public static void CreateListColumn()
{
var clientContext = Helper.GetClientContext();
List oList = clientContext.Web.Lists.GetByTitle(listName);
//Number DataType Column
Field numberField = oList.Fields.AddFieldAsXml("<Field DisplayName='Age' Type='Number' />", true, AddFieldOptions.DefaultValue);
FieldNumber fieldNumber = clientContext.CastTo <FieldNumber>(numberField);
fieldNumber.MaximumValue = 100;
fieldNumber.MinimumValue = 35;
fieldNumber.Update();
clientContext.Load(fieldNumber);
// //Single Line Of Text DataType Column
Field textField = oList.Fields.AddFieldAsXml("<Field DisplayName='SingleLine' Type='Text' />", true, AddFieldOptions.DefaultValue);
FieldText fieldText = clientContext.CastTo <FieldText>(textField);
fieldText.Update();
clientContext.Load(fieldText);
//Multi Line Of Text DataType Column
Field multiLineField = oList.Fields.AddFieldAsXml("<Field DisplayName='MultiLine' Type='Note' />", true, AddFieldOptions.DefaultValue);
FieldMultiLineText fieldmultiLineText = clientContext.CastTo <FieldMultiLineText>(multiLineField);
fieldmultiLineText.Update();
clientContext.Load(fieldmultiLineText);
//Multi Line Rich Text DataType Column
Field multiLineRichTextField = oList.Fields.AddFieldAsXml("<Field DisplayName='Multi Line RichText' Type='Note' />", true, AddFieldOptions.DefaultValue);
FieldMultiLineText fieldmultiLineRichText = clientContext.CastTo <FieldMultiLineText>(multiLineRichTextField);
fieldmultiLineRichText.AllowHyperlink = true;
fieldmultiLineRichText.RichText = true;
fieldmultiLineRichText.Update();
fieldmultiLineRichText.UpdateAndPushChanges(true);
clientContext.Load(fieldmultiLineRichText);
//An enhanced multi line text field
string schemaRichTextField = "<Field Type='Note' Name='EnhancedmultiLine' StaticName='EnhancedmultiLine' DisplayName = 'Enhanced multiLine' NumLines = '6' RichText = 'TRUE' RichTextMode = 'FullHtml' IsolateStyles = 'TRUE' Sortable = 'FALSE' /> ";
Field multilineenhancedTextField = oList.Fields.AddFieldAsXml(schemaRichTextField, true, AddFieldOptions.AddFieldInternalNameHint);
clientContext.Load(multilineenhancedTextField);
// DropDown Choice
string schemaChoiceFieldDDL = "<Field Type='Choice' DisplayName='ChoiceDDL' Name='ChoiceDDL' StaticName='ChoiceDDL' Format = 'Dropdown' >"
+ "<Default>Option 2</Default>"
+ "<CHOICES>"
+ " <CHOICE>Option 2</CHOICE>"
+ " <CHOICE>Option 3</CHOICE>"
+ "</CHOICES>"
+ "</Field>";
Field choiceDDLField = oList.Fields.AddFieldAsXml(schemaChoiceFieldDDL, true, AddFieldOptions.AddFieldInternalNameHint);
FieldChoice fieldChoice = clientContext.CastTo <FieldChoice>(choiceDDLField);
fieldChoice.Required = true;
fieldChoice.Update();
clientContext.Load(fieldChoice);
//Radio buttons
string schemaRadioChoiceField = "<Field Type='Choice' Name='ChoiceRadio' StaticName='ChoiceRadio' DisplayName = 'Choice Radio' Format = 'RadioButtons' > "
+ "<Default>Opt Radio 3</Default>"
+ "<CHOICES>"
+ " <CHOICE>Opt Radio 1</CHOICE>"
+ " <CHOICE>Opt Radio 2</CHOICE>"
+ " <CHOICE>Opt Radio 3</CHOICE>"
+ "</CHOICES>"
+ "</Field>";
Field choiceField = oList.Fields.AddFieldAsXml(schemaRadioChoiceField, true, AddFieldOptions.AddFieldInternalNameHint);
clientContext.Load(choiceField);
//Checkboxes
string schemaMultiChoiceField = "<Field Type='MultiChoice' Name='ChoiceMulti' StaticName='ChoiceMulti' DisplayName = 'Choice Multi' > "
+ "<Default>MultiChoice 2</Default>"
+ "<CHOICES>"
+ " <CHOICE>MultiChoice 1</CHOICE>"
+ " <CHOICE>MultiChoice 2</CHOICE>"
+ " <CHOICE>MultiChoice 3</CHOICE>"
+ "</CHOICES>"
+ "</Field>";
Field choiceMultiChoiceField = oList.Fields.AddFieldAsXml(schemaMultiChoiceField, true, AddFieldOptions.AddFieldInternalNameHint);
clientContext.Load(choiceMultiChoiceField);
//Fill In option
string schemaFillInChoiceField = "<Field Type='Choice' DisplayName='Fill In Choice' Name='FillInChoice' StaticName='FillInChoice' Format = 'Dropdown' FillInChoice = 'TRUE' > "
+ "<Default>My Choices Data will come here</Default>"
+ "<CHOICES>"
+ " <CHOICE>FillInChoice 1</CHOICE>"
+ " <CHOICE>FillInChoice 2</CHOICE>"
+ " <CHOICE>FillInChoice 3</CHOICE>"
+ "</CHOICES>"
+ "</Field>";
Field choiceFillInChoiceField = oList.Fields.AddFieldAsXml(schemaFillInChoiceField, true, AddFieldOptions.AddFieldInternalNameHint);
clientContext.Load(choiceFillInChoiceField);
//Picture field
string schemaPictureField = "<Field Type='URL' Name='EmployeePicture' StaticName='EmployeePicture' DisplayName = 'Employee Picture' Format = 'Image' /> ";
Field pictureField = oList.Fields.AddFieldAsXml(schemaPictureField, true, AddFieldOptions.AddFieldInternalNameHint);
clientContext.Load(pictureField);
//URL field
string schemaUrlField = "<Field Type='URL' Name='BlogUrl' StaticName='BlogUrl' DisplayName='Blog URL' Format='Hyperlink'/>";
Field urlField = oList.Fields.AddFieldAsXml(schemaUrlField, true, AddFieldOptions.AddFieldInternalNameHint);
clientContext.Load(urlField);
clientContext.ExecuteQuery();
// Lookup field
List countryList = clientContext.Web.Lists.GetByTitle("Countries");
clientContext.Load(countryList, c => c.Id);
clientContext.ExecuteQuery();
// //define the relationship with the lookup field, in that case the field needs to be indexed:
// string schemaLookupField = "<Field Type='Lookup' Name='Country' StaticName='Country' DisplayName='Country Name' List = '{B5E2D800F-E739-401F-983F-B40984B70273}' ShowField = 'Title' RelationshipDeleteBehavior = 'Restrict' Indexed = 'TRUE' /> ";
//string schemaLookupField = "<Field Type='Lookup' Name='Country' StaticName='Country' DisplayName='Country Name' List = 'Countries' ShowField = 'Title' RelationshipDeleteBehavior = 'Restrict' Indexed = 'TRUE' /> ";
string schemaLookupField = "<Field Type='Lookup' Name='Country' StaticName='Country' DisplayName='Country Name' List = '" + countryList.Id + "' ShowField = 'Title' /> ";
Field lookupField = oList.Fields.AddFieldAsXml(schemaLookupField, true, AddFieldOptions.AddFieldInternalNameHint);
lookupField.Update();
clientContext.Load(lookupField);
//// multi-select lookup field
string schemaMultiLookupField = "<Field Type='LookupMulti' Name='Country' StaticName='Country' DisplayName='Country' List = '" + countryList.Id + "' ShowField = 'Title' Mult = 'TRUE' /> ";
//string schemaMultiLookupField = "<Field Type='LookupMulti' Name='Country' StaticName='Country' DisplayName='Country' List = 'Countries' ShowField = 'Title' Mult = 'TRUE' /> ";
Field lookupFieldmulti = oList.Fields.AddFieldAsXml(schemaMultiLookupField, true, AddFieldOptions.AddFieldInternalNameHint);
lookupFieldmulti.Update();
clientContext.Load(lookupFieldmulti);
////Ref: https://karinebosch.wordpress.com/my-articles/creating-fields-using-csom/
// //User Field
string schemaUserField = "<Field Type='User' Name='UserName' StaticName='UserName' DisplayName='User Name' />";
Field userField = oList.Fields.AddFieldAsXml(schemaUserField, true, AddFieldOptions.AddFieldInternalNameHint);
clientContext.Load(userField);
////User Field multiple
//string schemaUserGroupField = "<Field Type='UserMulti' Name='EmployeeName' StaticName='EmployeeName' DisplayName='Employee Name' UserSelectionMode = 'PeopleOnly' UserSelectionScope = '7' Mult = 'TRUE' /> ";
string schemaUserGroupField = "<Field Type='UserMulti' Name='EmployeeName' StaticName='EmployeeName' DisplayName='Employee Name' UserSelectionMode = 'PeopleAndGroups' UserSelectionScope = '7' Mult = 'TRUE' /> ";
Field userGroupField = oList.Fields.AddFieldAsXml(schemaUserGroupField, true, AddFieldOptions.AddFieldInternalNameHint);
clientContext.Load(userGroupField);
////boolean field
string schemaBooleanField = "<Field Type='Boolean' Name='Married' StaticName='Married' DisplayName='Married'> <Default>1</Default> </Field>";
Field booleanField = oList.Fields.AddFieldAsXml(schemaBooleanField, true, AddFieldOptions.AddFieldInternalNameHint);
clientContext.Load(booleanField);
////DateTime Field
//Date only field
string schemaBirthDate = "<Field Type='DateTime' Name='BirthDate' StaticName='BirthDate' DisplayName = 'Birth date' Format = 'DateOnly'> <Default>[Today]</Default></Field>";
Field birthDateField = oList.Fields.AddFieldAsXml(schemaBirthDate, true, AddFieldOptions.AddFieldInternalNameHint);
clientContext.Load(birthDateField);
////Date and time field
string schemaArrivalField = "<Field Type='DateTime' Name='ArrivalDateTime' StaticName='ArrivalDateTime' DisplayName = 'Arrival' Format = 'DateTime'> <Default>[Now]</Default></Field>";
Field DateTimeField = oList.Fields.AddFieldAsXml(schemaArrivalField, true, AddFieldOptions.AddFieldInternalNameHint);
clientContext.Load(DateTimeField);
////hidden field
string schemaHiddenTextField = "<Field Type='Text' Name='HiddenField' StaticName='HiddenField' DisplayName='Hidden Field' Hidden='TRUE' />";
Field hiddenTextField = oList.Fields.AddFieldAsXml(schemaHiddenTextField, true, AddFieldOptions.AddFieldInternalNameHint);
clientContext.Load(hiddenTextField);
//indexed field // Not Working as of now
//Field f = oList.Fields.GetByInternalNameOrTitle("ID");
//clientContext.Load(f);
//clientContext.ExecuteQuery();
//f.Indexed = true;
//f.Update();
//Managed Metadata field
Guid termStoreId = Guid.Empty;
Guid termSetId = Guid.Empty;
GetTaxonomyFieldInfo(clientContext, out termStoreId, out termSetId);
// Single selection Taxonomy field
string schemaTaxonomyField = "<Field Type='TaxonomyFieldType' Name='TaxonomyField' StaticName='TaxonomyField' DisplayName = 'Taxonomy Field' /> ";
Field taxonomyFieldSingle = oList.Fields.AddFieldAsXml(schemaTaxonomyField, true, AddFieldOptions.AddFieldInternalNameHint);
clientContext.Load(taxonomyFieldSingle);
// Retrieve the field as a Taxonomy Field
TaxonomyField taxonomyField = clientContext.CastTo <TaxonomyField>(taxonomyFieldSingle);
taxonomyField.SspId = termStoreId;
taxonomyField.TermSetId = termSetId;
taxonomyField.TargetTemplate = String.Empty;
taxonomyField.AnchorId = Guid.Empty;
taxonomyField.Update();
// Multi selection Taxonomy field
string schemaTaxonomyFieldMulti = "<Field Type='TaxonomyFieldTypeMulti' Name='TaxonomyFieldMulti' StaticName='TaxonomyFieldMulti' DisplayName = 'Taxonomy Field Multi' Mult = 'TRUE' /> ";
Field taxonomyFieldMulti = oList.Fields.AddFieldAsXml(schemaTaxonomyFieldMulti, false, AddFieldOptions.AddFieldInternalNameHint);
clientContext.Load(taxonomyFieldMulti);
// Retrieve the field as a Taxonomy Field
TaxonomyField taxonomyField1 = clientContext.CastTo <TaxonomyField>(taxonomyFieldMulti);
taxonomyField1.SspId = termStoreId;
taxonomyField1.TermSetId = termSetId;
taxonomyField1.TargetTemplate = String.Empty;
taxonomyField1.AnchorId = Guid.Empty;
taxonomyField1.Update();
clientContext.ExecuteQuery();
//Calculated field
// Not Working
//string formula = "<Formula>=Age&\"\"&SingleLine&\"(id:\"&ID&\"\"</Formula>"
// + "<FieldRefs>"
// + "<FieldRef Name='Age' />"
// + "<FieldRef Name='SingleLine' />"
// + "<FieldRef Name='ID' />"
// + "</FieldRefs>";
//string schemaCalculatedField = "<Field Type='Calculated' Name='CalculatedField' StaticName='CalculatedField' DisplayName = 'Calculated Field' ResultType = 'Text' Required = 'TRUE' ReadOnly = 'TRUE' > " + formula + " </ Field > ";
//Field fullNameField = oList.Fields.AddFieldAsXml(schemaCalculatedField, true, AddFieldOptions.AddFieldInternalNameHint);
//clientContext.ExecuteQuery();
string fieldXml = "<Field Name='CalculatedField_Year' StaticName='CalculatedField_Year' DisplayName='CalculatedField Year' Type='Text' ReadOnly = 'TRUE'>"
+ "<DefaultFormula>=CONCATENATE(YEAR(Today))</DefaultFormula>"
+ "</Field>";
Field field = oList.Fields.AddFieldAsXml(fieldXml, true, AddFieldOptions.DefaultValue);
clientContext.ExecuteQuery();
}
public void ToSQLinsert()
{
Excel.Range dataRange; string[] headers;
GetDataRangeAndHeaders(_range, out dataRange, out headers);
var f1 = new FieldText("Table name", "Name of the table", _range.ListObject == null ? "TABLENAME" : _range.ListObject.Name);
var f2 = new FieldText("Fields", "Fields name separated by a semicolon", string.Join(";", headers));
var f3 = new FieldText("Null value", "Null expression", "NULL", true);
var f4 = new FieldText("DateTime", "DateTime format", "yyyy-MM-ddTHH:mm:ss", true);
var f5 = new FieldRadioBT("Insert type", new[] { "SingleInsert", "MultipleInsert" }, "Type of insert", 0, true);
new InputBox(_excelapp, "Copy to SQL INSERT", f1, f2, f3, f4, f5).Show(delegate {
var rows = new List<string>();
var tablename = f1.Value;
var columns = f2.Value.Replace(';', ',');
var nullValue = f3.Value;
var datetimeformat = f4.Value;
var singleinsert = f5.Value == 0;
var multipleinsert = f5.Value == 1;
const bool unionall = false;
var row = new StringBuilder();
new CellsOperator(dataRange).ForEachValue(delegate(object value, bool isFirstCol, bool isLastCol) {
if (isFirstCol == false)
row.Append(",");
if (value == null)
row.Append(nullValue);
else if (value is string)
row.Append("'" + ((string)value).Replace("'", "''") + "'");
else if (value is bool)
row.Append((bool)value ? "TRUE" : "FALSE");
else if (value is System.DateTime)
row.Append("'" + ((System.DateTime)value).ToString(datetimeformat) + "'");
else
row.Append(value);
if (isLastCol) {
rows.Add(row.ToString());
row.Length = 0;
}
}
);
var sql = new StringBuilder();
if (!string.IsNullOrEmpty(columns))
columns = "(" + columns + ")";
if (singleinsert) {
sql.Append("INSERT INTO " + tablename + " VALUES\r\n");
var nbRow = rows.Count;
for (var r = 0; r < rows.Count; r++) {
if (r == (nbRow - 1))
sql.Append("(" + rows[r] + ");");
else
sql.Append("(" + rows[r] + "),\r\n");
}
} else if (multipleinsert) {
foreach (var s in rows)
sql.Append("INSERT INTO " + tablename + columns + " VALUES(" + s + ");\r\n");
} else if (unionall) {
//sql.Append("INSERT INTO " + tablename + "\r\n");
//int nbRow = rows.Count;
//for (int r = 0; r < rows.Count; r++) {
// if (r == (nbRow - 1))
// sql.Append("SELECT " + rows[r] + ";");
// else
// sql.Append("SELECT " + rows[r] + "\r\nUNION ALL");
//}
}
if (sql.Length != 0)
System.Windows.Forms.Clipboard.SetText(sql.ToString());
});
}
