/// <summary>
/// return value: array [] of string-Lists
/// </summary>
/// <param name="keywords"></param>
/// <returns></returns>
public static List <string>[] SeparateKeywords(string[] keywords)
{
var cSharpWords = new List <string>();
var footballWords = new List <string>();
foreach (var word in keywords)
{
if (KeywordDictionary.ContainsKey(word))
{
var isFootball = KeywordDictionary[word].ToLowerInvariant() == "football";
if (isFootball)
{
footballWords.Add(word);
}
else
{
cSharpWords.Add(word);
}
}
}
cSharpWords.Sort();
footballWords.Sort();
return(new List <string>[2] {
cSharpWords, footballWords
});
}
public ImportService()
{
Dictionary = new KeywordDictionary();
var s3Response = S3Helper.GetFile(new S3GetFileRequest()
{
FilePath = MTGServiceConstants.KeywordDictionaryFilepath
}).Result;
if (s3Response.IsSuccess)
{
Dictionary = JsonConvert.DeserializeObject <KeywordDictionary>(s3Response.FileContent);
}
}
public static List <string>[] SeparateKeywords(string[] keywords)
{
var result = new [] { new List <string>(), new List <string>() };
foreach (var word in keywords)
{
if (!KeywordDictionary.ContainsKey(word))
{
continue;
}
if (KeywordDictionary[word] == "C#")
{
result[0].Add(word);
}
else
{
result[1].Add(word);
}
}
result[0].Sort();
result[1].Sort();
return(result);
}
public void ExportToExcel(int ReportId, string Quarter, string InvoicePeriod, int ProgramTypeId = 0)
{
UserAccount u = (UserAccount)Session["UserAccount"];
DataTable list = new DataTable();
list = GetData(ReportId, ProgramTypeId, Quarter, InvoicePeriod);
string ReportName1 = "";
switch (ProgramTypeId)
{
case 1:
ReportName1 = "(PV DRMF)";
break;
case 2:
ReportName1 = "(CV DRMF)";
break;
case 3:
ReportName1 = "";
break;
}
string ReportName = "";
switch (ReportId)
{
case 1:
ReportName = "Distributor_Budget" + ReportName1;
break;
case 2:
ReportName = "Claim" + ReportName1;
break;
case 3:
ReportName = "Spending" + ReportName1;
break;
case 4:
ReportName = "Payment_Manager" + ReportName1;
break;
case 5:
ReportName = "Distributor" + ReportName1;
break;
case 6:
ReportName = "Program_Owner_Payment" + ReportName1;
break;
case 7:
ReportName = "Claim_Validation" + ReportName1;
break;
}
DataRow dr1 = list.NewRow();
if (list != null && list.Columns != null && list.Columns.Count > 0 && ReportId != 7)
{
foreach (DataColumn col in list.Columns)
{
list.Columns[col.ColumnName].ColumnName = !string.IsNullOrEmpty(KeywordDictionary.FindEnglishText(col.ColumnName)) ? KeywordDictionary.FindEnglishText(col.ColumnName) : col.ColumnName;
}
}
if (ReportId > 0 && ReportId == 2)
{
int i = 0;
foreach (DataRow dr in list.Rows)
{
list.Rows[i][6] = (list.Rows[i][6] == DBNull.Value) ? null : Convert.ToDateTime(list.Rows[i][6].ToString()).ToString("dd/MM/yyyy");
i++;
}
}
//if (ReportId > 0 && ReportId == 4)
//{
// int i = 0;
// foreach (DataRow dr in list.Rows)
// {
// list.Rows[i][4] = (list.Rows[i][4] == DBNull.Value) ? null : Convert.ToDateTime(list.Rows[i][4].ToString()).ToString("dd/MM/yyyy");
// i++;
// }
//}
//if (ReportId > 0 && ReportId == 1)
//{
// int i = 0;
// foreach (DataRow dr in list.Rows)
// {
// list.Rows[i][4] = (list.Rows[i][4] == DBNull.Value) ? null : Convert.ToDecimal(list.Rows[i][4].ToString()).ToString("0.00##");
// i++;
// }
//}
list.Rows.InsertAt(dr1, 0);
list.Rows[0].Delete();
list.AcceptChanges();
//var grid = new System.Web.UI.WebControls.GridView();
//grid.ApplyStyle(new Style() { BorderStyle = BorderStyle.Solid });
//grid.Font.Name = "Calibri";
//grid.Font.Size = 11;
//grid.DataSource = list;
//grid.DataBind();
//Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=" + ReportName + "_" + DateTime.Now.ToString() + "Reports.xls");
Response.ContentType = "application/vnd.ms-excel";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
var workbook = new XLWorkbook();
string range = "A1:B2";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
var worksheet = workbook.AddWorksheet(list, ReportName);
worksheet.Row(1).InsertRowsAbove(1);
worksheet.Range(range).Row(1);
worksheet.Range(range).Row(1).Merge();
//if (ReportId > 0 && ReportId == 4)
//{
// string range1 = (ProgramTypeId > 0 && ProgramTypeId == 2 ? "F1:I1" : "F1:J1");
// string range2 = (ProgramTypeId > 0 && ProgramTypeId == 2 ? "J1:M1" : "K1:O1");
// worksheet.Range(range1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
// worksheet.Range(range1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
// worksheet.Range(range1).Style.Font.FontSize = 11;
// worksheet.Range(range1).Style.Font.Bold = true;
// worksheet.Range(range1).Style.Fill.BackgroundColor = XLColor.LightGray;
// worksheet.Range(range1).Row(1).Merge().Value = "(RMB，不含增值税）";
// worksheet.Range(range2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
// worksheet.Range(range2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
// worksheet.Range(range2).Style.Font.FontSize = 11;
// worksheet.Range(range2).Style.Font.Bold = true;
// worksheet.Range(range2).Style.Fill.BackgroundColor = XLColor.Gray;
// worksheet.Range(range2).Row(1).Merge().Value = "(RMB，税额 ）";
//}
using (var ms = new MemoryStream())
{
workbook.SaveAs(ms);
ms.WriteTo(Response.OutputStream);
ms.Close();
}
Response.Flush();
Response.End();
//Response.ContentEncoding = System.Text.Encoding.Unicode;
//Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());
//StringWriter sw = new StringWriter();
//HtmlTextWriter htw = new HtmlTextWriter(sw);
//grid.RenderControl(htw);
//Response.Write(sw.ToString());
//Response.End();
}
//TestScript excel generation
private void GenerateXlsTestScript(UmlModel model)
{
int currentLine = 0;
String modelName = model.Name;
String directory = CreateTestScriptDirectory();
#region Styles and SLDocument
SLDocument xlsx = new SLDocument();
SLStyle style = xlsx.CreateStyle();
style.SetWrapText(true);
SLStyle style_aux = xlsx.CreateStyle();
style_aux.SetWrapText(true);
style_aux.Alignment.Horizontal = HorizontalAlignmentValues.Center;
SLStyle headerStyle = xlsx.CreateStyle();
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.FromArgb(0x8EB4E3), System.Drawing.Color.Black);
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Font.FontName = "Calibri";
headerStyle.Border.RightBorder.Color = System.Drawing.Color.Black;
headerStyle.Border.LeftBorder.Color = System.Drawing.Color.Black;
headerStyle.Border.TopBorder.Color = System.Drawing.Color.Black;
headerStyle.Border.BottomBorder.Color = System.Drawing.Color.Black;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
xlsx.DeleteWorksheet(SLDocument.DefaultFirstSheetName);
#endregion
if (model.Diagrams.Count(X => X is UmlActivityDiagram) > 1)
{
throw new Exception("Only one Activity diagram allowed! (For now)");
}
UmlActivityDiagram act = model.Diagrams.OfType <UmlActivityDiagram>().First();
if (act.Name.Length > 31)
{
String aux = act.Name;
aux = aux.Substring(0, 25) + aux.Substring(aux.Length - 5, 5);
aux = HttpUtility.UrlDecode(aux);
xlsx.AddWorksheet(aux);
xlsx.SelectWorksheet(aux);
}
else
{
xlsx.AddWorksheet(HttpUtility.UrlDecode(act.Name));
xlsx.SelectWorksheet(HttpUtility.UrlDecode(act.Name));
}
ReadXLsx read = new ReadXLsx();
//Must be in the output folder
KeywordDictionary dic = read.Leitor(@"KeyWords_2_2.xlsx");
#region Header
xlsx.SetCellValue(1, 1, "StepRunMode");
xlsx.SetCellValue(1, 2, "Keyword");
xlsx.SetCellValue(1, 3, "Object");
xlsx.SetCellValue(1, 4, "ActionValue1");
xlsx.SetCellValue(1, 5, "ManualStepDescription");
xlsx.SetCellValue(1, 6, "StopOnFail");
xlsx.SetCellStyle("A1", "F1", headerStyle);
#endregion
#region Dados Fixos
xlsx.SetCellValue(2, 1, "r");
xlsx.SetCellStyle(2, 1, style_aux);
xlsx.SetCellValue(2, 2, "calludf");
xlsx.SetCellStyle(2, 2, style);
xlsx.SetCellValue(2, 3, "setbasestate");
xlsx.SetCellStyle(2, 3, style);
xlsx.SetCellValue(3, 1, "w");
xlsx.SetCellStyle(3, 1, style_aux);
xlsx.SetCellValue(3, 2, "perform");
xlsx.SetCellStyle(3, 2, style);
xlsx.SetCellValue(3, 3, "browser;*about:blank*");
xlsx.SetCellStyle(3, 3, style);
xlsx.SetCellValue(3, 4, "navigate;dt_URL");
xlsx.SetCellStyle(3, 4, style);
xlsx.SetCellValue(4, 1, "w");
xlsx.SetCellStyle(4, 1, style_aux);
xlsx.SetCellValue(4, 2, "perform");
xlsx.SetCellStyle(4, 2, style);
xlsx.SetCellValue(4, 3, "browser;*about:blank*");
xlsx.SetCellStyle(4, 3, style);
xlsx.SetCellValue(4, 4, "waitforpage;dt_MaxWebPageSyncTime");
xlsx.SetCellStyle(4, 4, style);
currentLine = 4;
#endregion
for (int i = 0; i < act.UmlObjects.OfType <UmlTransition>().Count(); i++)
{
UmlTransition tran = act.UmlObjects.OfType <UmlTransition>().ElementAt(i);
String[] tdAction = { "" };
String tdObject = "";
if (tran.Target is UmlFinalState)
{
continue;
}
foreach (KeyValuePair <String, String> pair in tran.TaggedValues)
{
if (pair.Key.Equals("TDACTION"))
{
tdAction = HttpUtility.UrlDecode(pair.Value).Split(',');
}
if (pair.Key.Equals("TDOBJECT"))
{
tdObject = HttpUtility.UrlDecode(pair.Value);
tdObject = tdObject.Substring(1);
tdObject = tdObject.Substring(0, tdObject.Length - 1);
}
}
for (int j = 0; j < tdAction.Count(); j++)
{
//if (!tdObject.Split(';')[0].Equals("window"))
//{
String tdActionSingle = tdAction[j];
tdActionSingle = tdActionSingle.Substring(1);
tdActionSingle = tdActionSingle.Substring(0, tdActionSingle.Length - 1);
String[] tdActionAux = tdActionSingle.Split(';');
if (tdActionAux[0].Equals("wait"))
{
xlsx.SetCellValue(currentLine + 1, 1, "w");
xlsx.SetCellStyle(currentLine + 1, 1, style_aux);
xlsx.SetCellValue(currentLine + 1, 2, tdActionAux[0]);
xlsx.SetCellStyle(currentLine + 1, 2, style);
xlsx.SetCellValue(currentLine + 1, 3, tdActionAux[1]);
xlsx.SetCellStyle(currentLine + 1, 3, style);
xlsx.SetCellValue(currentLine + 1, 4, "");
xlsx.SetCellStyle(currentLine + 1, 4, style);
}
else
{
String tdObjectToCompare = tdObject.Split(';')[0];
String tdActionToCompare = tdActionSingle.Split(';')[0];
tdObjectToCompare = tdObjectToCompare.ToLower();
tdActionToCompare = tdActionToCompare.ToLower();
String keyword = dic.GetKeyword(tdObjectToCompare, tdActionToCompare);
xlsx.SetCellValue(currentLine + 1, 1, "w");
xlsx.SetCellStyle(currentLine + 1, 1, style_aux);
xlsx.SetCellValue(currentLine + 1, 2, keyword);
xlsx.SetCellStyle(currentLine + 1, 2, style);
xlsx.SetCellValue(currentLine + 1, 3, tdObject);
xlsx.SetCellStyle(currentLine + 1, 3, style);
xlsx.SetCellValue(currentLine + 1, 4, tdActionSingle);
xlsx.SetCellStyle(currentLine + 1, 4, style);
}
currentLine++;
//}
}
}
xlsx.AutoFitColumn(1, 6);
xlsx.AutoFitRow(1, currentLine);
xlsx.DeleteWorksheet(SLDocument.DefaultFirstSheetName);
if (!File.Exists(directory + @"\" + modelName + "_TestScript.xlsx"))
{
xlsx.SaveAs(directory + @"\" + modelName + "_TestScript.xlsx");
}
else
{
int i = 1;
string name;
string[] sub;
string[] files = System.IO.Directory.GetFiles(directory);
foreach (String file in files)
{
name = System.IO.Path.GetFileNameWithoutExtension(file);
if (name.Contains("("))
{
char[] splitTokens = { '(', ')' };
sub = name.Split(splitTokens);
if (sub[0] == modelName)
{
//descubro o maior index
if (Convert.ToInt16(sub[1]) > i)
{
i = Convert.ToInt16(sub[1]);
}
}
}
}
xlsx.SaveAs(directory + @"\" + modelName + "(" + (i + 1) + ")" + "_TestScript.xlsx");
}
}
