public void SaveXMLPart(WorkbookModel workbook, XElement root, string id)
{
if (root == null)
{
return;
}
var masterRoot = new XElement(id);
masterRoot.Add(new XAttribute("company", "University of Stuttgart, ISTE"));
masterRoot.Add(new XAttribute("product", "Spreadsheet Inspection Framework (SIF"));
masterRoot.Add(root);
//clear old
var oldPart = GetCustomXLPart(workbook, id);
if (oldPart != null)
{
oldPart.Delete();
}
//save
var scenarioXMLPart = workbook.Workbook.CustomXMLParts.Add(masterRoot.ToString());
Debug.WriteLine(masterRoot.ToString());
}
/// <summary>
/// Gets the selected cells in the current workbook
/// </summary>
/// <param name="wb">workbook model</param>
/// <returns>List of Cell</returns>
public List <Cell> GetSelectedCells(WorkbookModel wb)
{
var cellList = new List <Cell>();
Range selectedCells = (wb.Workbook.Application.Selection as Range).Cells;
Debug.WriteLine("SELECTED CELLS: Creating List ...");
DateTime start = DateTime.Now;
foreach (var c in selectedCells.Cells)
{
var currentCell = c as Range;
String currentLocation = "=" + (currentCell.Parent as Worksheet).Name as String + "!" + currentCell.Address as String;
var selectedCell = new Cell()
{
Id = Convert.ToInt32(currentCell.ID),
Location = GetUserCellName(wb, currentLocation),
SifLocation = GetSIFCellName(wb, currentLocation),
Content = currentCell.Formula as String
};
cellList.Add(selectedCell);
}
Debug.WriteLine("SELECTED CELLS: List created! Time: " + (DateTime.Now - start).ToString() + ", Items: " + cellList.Count);
return(cellList);
}
/// <summary>
/// Updates the changes in the scenario values into the datamodel. Occurs when the save button is clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void SaveDataButton_OnClickDataButton_Click(object sender, RoutedEventArgs e)
{
BindingExpression be = TitleTextBox.GetBindingExpression(TextBox.TextProperty);
if (be != null)
{
be.UpdateSource();
}
be = AuthorTextbox.GetBindingExpression(TextBox.TextProperty);
if (be != null)
{
be.UpdateSource();
}
be = DescriptionTextBox.GetBindingExpression(TextBox.TextProperty);
if (be != null)
{
be.UpdateSource();
}
be = CreateDatePicker.GetBindingExpression(DatePicker.SelectedDateProperty);
if (be != null)
{
be.UpdateSource();
}
be = RatingTextBox.GetBindingExpression(TextBox.TextProperty);
if (be != null)
{
be.UpdateSource();
}
WorkbookModel workbook = new WorkbookModel(Globals.ThisAddIn.Application.ActiveWorkbook);
workbook.ShouldScanAfterSave = false;
Globals.ThisAddIn.Application.ActiveWorkbook.Save();
workbook.ShouldScanAfterSave = true;
}
/// <summary>
/// Gets the sif cell name of a cell location in a1style
/// </summary>
/// <param name="wb">workbook model</param>
/// <param name="a1Adress">cell location in a1 style</param>
/// <returns>sif cell name</returns>
public String GetSIFCellName(WorkbookModel wb, String a1Adress)
{
// Important: There might be more than just one name for this cell!
var name = new CellLocation(wb.Workbook, a1Adress).ScenarioNames.FirstOrDefault();
if (name != null)
{
return(name.Name);
}
else
{
return(string.Empty);
}
//foreach (Name n in wb.Workbook.Application.Names)
//{
// if (n.RefersTo as String == a1Adress
// && n.Name.Contains(Properties.Settings.Default.CellNameTag))
// {
// return n.Name;
// }
//}
//return String.Empty;
}
private void Application_WorkbookActivate(Workbook Wb)
{
// This method is called whenever a workbook comes to the front
// Does not necessarily need to be a workbook that is persisted on the disk
var workbook = DataModel.Instance.WorkbookModels.Where(p => ReferenceEquals(p.Workbook, Wb))
.FirstOrDefault();
if (workbook == null)
{
workbook = new WorkbookModel(Wb);
DataModel.Instance.WorkbookModels.Add(workbook);
DataModel.Instance.CurrentWorkbook = workbook;
DataModel.Instance.CurrentWorkbook.LoadExtraInformation();
/// create shared pane
var SidebarContainer = new SidebarContainer();
var Sidebar = CustomTaskPanes.Add(SidebarContainer, "Sidebar");
SidebarContainer.Sidebar.DataContext = workbook;
Sidebar.Width = 320;
TaskPanes.Add(new Tuple <WorkbookModel, string>(workbook, "Sidebar"), Sidebar);
//add selection changed event handler for ribbon
Wb.Application.SheetSelectionChange += DataModel.Instance.WorkbookSelectionChangedEventHandler;
}
DataModel.Instance.CurrentWorkbook = workbook;
}
private XElement CreateSanityCheckingCells(WorkbookModel n)
{
var root = new XElement("sanityCheckingCells");
foreach (var cell in n.SanityCheckingCells)
{
root.Add(new XElement("location", NullCheck(new CellLocation(DataModel.Instance.CurrentWorkbook.Workbook, cell.Location).Location)));
}
return(root);
}
private XElement CreateOutputCells(WorkbookModel n)
{
var root = new XElement("outputCells");
foreach (var cell in n.OutputCells)
{
var cellElement = new XElement("outputCell");
cellElement.Add(new XElement("name", NullCheck(new CellLocation(DataModel.Instance.CurrentWorkbook.Workbook, cell.Location).ShortLocation)));
}
return(root);
}
/// <summary>
/// Get the a1Adress of a cell name
/// </summary>
/// <param name="wb">workbook model</param>
/// <param name="name">cell location name</param>
/// <returns>cell location in a1 style</returns>
public String GetA1Adress(WorkbookModel wb, String name)
{
foreach (Name n in wb.Workbook.Application.Names)
{
if (n.Name == name)
{
return(n.RefersTo as String);
}
}
return(name);
}
public XElement LoadXMLPart(WorkbookModel workbook, string id)
{
var part = GetCustomXLPart(workbook, id);
if (part != null)
{
var result = XElement.Parse(part.XML).Elements().First();
Debug.WriteLine(result.ToString());
return(result);
}
else
{
return(null);
}
}
private void Application_WorkbookActivate(Microsoft.Office.Interop.Excel.Workbook Wb)
{
// This method is called whenever a workbook comes to the front
// Does not necessarily need to be a workbook that is persisted on the disk
var workbook = DataModel.Instance.WorkbookModels.Where(p => ReferenceEquals(p.Workbook, Wb)).FirstOrDefault();
if (workbook == null)
{
workbook = new WorkbookModel(Wb);
DataModel.Instance.WorkbookModels.Add(workbook);
DataModel.Instance.CurrentWorkbook = workbook;
DataModel.Instance.CurrentWorkbook.LoadExtraInformation();
/// create shared pane
var sharedPaneContainer = new SharedPaneContainer();
var sharedPane = CustomTaskPanes.Add(sharedPaneContainer, "Inspection");
sharedPaneContainer.VisibleChanged += SharedPaneContainer_VisibleChanged;
sharedPaneContainer.SharedPane.DataContext = workbook;
sharedPane.Width = 340;
TaskPanes.Add(new Tuple<WorkbookModel, string>(workbook, "shared Pane"), sharedPane);
// create findings pane
var violationViewContainer = new ViolationsViewContainer();
var taskPane = CustomTaskPanes.Add(violationViewContainer, "Violations");
violationViewContainer.VisibleChanged += FindingsPaneContainer_VisibleChanged;
violationViewContainer.ViolationsView.DataContext = workbook;
TaskPanes.Add(new Tuple<WorkbookModel, string>(workbook, "Violations"), taskPane);
//create scenario detail pane
var scenarioDetailPainContainer = new ScenarioDetailPaneContainer();
var scenarioDetailPane = CustomTaskPanes.Add(scenarioDetailPainContainer, "Scenario");
scenarioDetailPane.Width = 260;
scenarioDetailPainContainer.VisibleChanged += ScenarioDetailPaneContainer_VisibleChanged;
TaskPanes.Add(new Tuple<WorkbookModel, string>(workbook, "Scenario Details"), scenarioDetailPane);
//add selection changed event handler for ribbon
Wb.Application.SheetSelectionChange += DataModel.Instance.WorkbookSelectionChangedEventHandler;
workbook.CellDefinitionChange += DataModel.Instance.CellDefinitionChangedEventHandler;
}
DataModel.Instance.CurrentWorkbook = workbook;
}
/// <summary>
/// Gets the first selected cells in the current workbook
/// </summary>
/// <param name="wb">workbook model</param>
/// <returns>List of Cell or null if no cell is selected</returns>
public Cell GetFirstSelectedCell(WorkbookModel wb)
{
Range selectedCell = (wb.Workbook.Application.Selection as Range).Cells.Cells[1] as Range;
String currentLocation = "=" + (selectedCell.Parent as Worksheet).Name as String + "!" + selectedCell.Address as String;
var resultCell = new Cell()
{
Id = Convert.ToInt32(selectedCell.ID),
Location = GetUserCellName(wb, currentLocation),
SifLocation = GetSIFCellName(wb, currentLocation),
Content = selectedCell.Formula as String
};
// Take the first cell and return it.
return(resultCell);
}
/// <summary>
/// Find a document property
/// </summary>
/// <param name="n">Workbook model with the excel workbook</param>
/// <param name="propertyName">name of the requested property</param>
/// <returns></returns>
private string GetDocumentProperty(WorkbookModel n, string propertyName)
{
var properties = (Microsoft.Office.Core.DocumentProperties)n.Workbook.BuiltinDocumentProperties;
string value;
try
{
value = (properties[propertyName].Value != null) ? properties[propertyName].Value.ToString() : String.Empty;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
value = String.Empty;
}
return(value);
}
public void SetRuleCells(WorkbookModel wb)
{
currentRule.RuleCells.Clear();
foreach (var c in DataModel.Instance.CurrentWorkbook.RuleCells)
{
try
{
var ruleCells = new RuleCells(c.Location);
currentRule.RuleCells.Add(ruleCells);
}
catch
{
}
}
DataModel.Instance.CurrentWorkbook.RuleCells.Clear();
}
public static void CreateExcel(WorkbookModel workbook)
{
var file = Utils.GetFileInfo(workbook.FileName, workbook.FilePath);
using (ExcelPackage xlPackage = new ExcelPackage(file))
{
foreach (var sheet in workbook.WorksheetModels)
{
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add(sheet.WorksheetName);
if (worksheet != null)
{
//Add Logo
AddImage(worksheet, workbook.ImageModel);
//Create Headers and format them
int cellNumber = 0;
foreach (var worksheetHeaderModel in sheet.WorksheetHeaderModels)
{
if (cellNumber == 0)
{
cellNumber = Utils.GetNumberFromString(worksheetHeaderModel.HeaderCell);
}
string cellLetter = new String(worksheetHeaderModel.HeaderCell.Where(Char.IsLetter).ToArray());
worksheetHeaderModel.HeaderCellRange = string.Format("{0}{1}:{2}{3}", cellLetter, cellNumber, Utils.GetExcelColumnName(sheet.WorksheetData.Columns.Count), cellNumber);
worksheetHeaderModel.HeaderCell = string.Format("{0}{1}", cellLetter, cellNumber);
CreateWorksheetHeader(worksheetHeaderModel, worksheet);
cellNumber++;
}
//Set from which row Column header should start
sheet.DataColumnHeaderModel.StartRow = cellNumber + 1;
//Set from which row data writing should start
sheet.WorksheetDataStartRow = cellNumber + 2;
//Create Column Header
CreateColumnHeader(sheet, worksheet);
//Add data to excel
int row = AddDataToExcel(sheet, worksheet, sheet.WorksheetData);
//Create footer
CreateFooter(worksheet, workbook.Disclaimer, row, sheet.WorksheetData.Columns.Count);
}
}
// save the new spreadsheet
xlPackage.Save();
}
//return file.FullName;
}
private string GetDocumentProperty(WorkbookModel n, string propertyName)
{
var properties = (DocumentProperties)n.Workbook.BuiltinDocumentProperties;
string value;
try
{
value = properties[propertyName].Value.ToString();
}
catch (Exception e)
{
value = String.Empty;
Console.WriteLine(e.Message);
}
return(value);
}
public object Visit(WorkbookModel n)
{
var root = new XElement("SIF.Scenario");
root.Add(new XAttribute("Title", NullCheck(n.Title)));
root.Add(new XAttribute("Spreadsheet", NullCheck(n.Spreadsheet)));
root.Add(new XAttribute("PolicyPath", NullCheck(n.PolicyPath)));
//save scenarios
foreach (var scen in n.Scenarios)
{
if (scen != null)
{
root.Add(scen.Accept(this) as XElement);
}
}
return(root);
}
/// <summary>
/// Creates a invisible sif cell name with the propertys cell name tag and a guid (without '-')
/// ex. SIF.Visualisation.0f8fad5bd9cb469fa16570867728950e
/// <returns></returns>
public String CreateSIFCellName(WorkbookModel wb, String a1Adress)
{
return(new CellLocation(wb.Workbook, a1Adress).AddName(Settings.Default.CellNameTag, false).Name);
//if (a1Adress == null) return null;
//String sifName = Properties.Settings.Default.CellNameTag + (Guid.NewGuid().ToString()).Replace("-", String.Empty);
//try
//{
// var Name = wb.Workbook.Application.Names.Add(sifName, a1Adress, false);
// return sifName;
//}
//catch (Exception e)
//{
// return null;
//}
}
// Sample code for inserting a work sheet.
protected override void OnCellDoubleClick(GridCellClickEventArgs e)
{
WorkbookForm form = FindForm() as WorkbookForm;
if (form != null)
{
WorkbookModel workbook = form.WorkbookView.Workbook;
GridModel sheet3 = new SampleGridModel();
SampleGrid.SetupGridModel(sheet3);
WorksheetModel workSheetModel = new WorksheetModel(workbook, "Sheet 3", sheet3);
workbook.Worksheets.Add(workSheetModel);
form.WorkbookView.ActivePage = form.WorkbookView.TabBarPages[workbook.Worksheets.Count - 1];
}
base.OnCellDoubleClick(e);
}
public override void InvokeAction(object sender, EventArgs e)
{
windowCount++;
workbook = new WorkbookModel("Workbook");
GridModel sheet1 = new SampleGridModel();
SampleGrid.SetupGridModel(sheet1);
sheet1.EnableGridListControlInComboBox = false;
GridModel sheet2 = new SampleGridModel();
SampleGrid.SetupGridModel(sheet2);
sheet2.EnableGridListControlInComboBox=false;
workbook.Worksheets.Add(new WorksheetModel(workbook, "Sheet 1", sheet1));
workbook.Worksheets.Add(new WorksheetModel(workbook, "Sheet 2", sheet2));
WorkbookForm doc = new WorkbookForm(workbook);
doc.ThemesEnabled = true;
doc.Text = workbook.Name + windowCount.ToString();
doc.MdiParent = MainWindow;
doc.Show();
}
private Microsoft.Office.Core.CustomXMLPart GetCustomXLPart(WorkbookModel workbook, string id)
{
Microsoft.Office.Core.CustomXMLPart resultPart = null;
foreach (Microsoft.Office.Core.CustomXMLPart part in workbook.Workbook.CustomXMLParts)
{
try
{
var xml = XElement.Parse(part.XML);
if (xml.Name == XName.Get(id))
{
resultPart = part;
break;
}
}
catch (Exception e)
{ Console.WriteLine(e.Message); }
}
return(resultPart);
}
private XElement CreateSanityRules(WorkbookModel n)
{
var root = new XElement("sanityRules");
XElement checking = CreateSanityCheckingCells(n);
if (!checking.HasElements)
{
return(null);
}
root.Add(checking);
root.Add(CreateSanityValueCells(n));
root.Add(CreateSanityConstraintCells(n));
root.Add(CreateSanityExplanationCells(n));
root.Add(new XElement("sanityWarnings", n.SanityWarnings));
return(root);
}
/// <summary>
/// Gets the user name of a cell location in a1style
/// </summary>
/// <param name="wb">workbook model</param>
/// <param name="a1Adress">cell location in a1 style</param>
/// <returns>if a user name is defined: user name, else cell location</returns>
public String GetUserCellName(WorkbookModel wb, String a1Adress)
{
// Without FalsePositive.* and SIF.Visualization.*
var name = this.GetUserCellNames(wb.Workbook, a1Adress).FirstOrDefault();
if (name != null)
{
return(name.Name);
}
return(a1Adress);
//String name = a1Adress;
//foreach (Name n in wb.Workbook.Application.Names)
//{
// if (n.RefersTo as String == a1Adress
// && !n.Name.Contains(Properties.Settings.Default.CellNameTag))
// {
// name = n.Name;
// }
//}
//return name;
}
/// <summary>
/// Gets the user name of a cell location in sif name style
/// </summary>
/// <param name="wb">workbook model</param>
/// <param name="sifName">sif cell name</param>
/// <returns>if a user name is defined: user name, else cell location</returns>
public String GetUserCellNameWithSIFName(WorkbookModel wb, String sifName)
{
var a1Adress = this.GetA1Adress(wb, sifName);
return(this.GetUserCellName(wb, a1Adress));
}
/// <summary>
/// Creating response object after reading workbook(s) details from database
/// </summary>
/// <param name="query"></param>
/// <param name="parameters"></param>
/// <returns>WorkbookModel</returns>
public List <WorkbookModel> ReadWorkBookDetails(string query, Dictionary <string, string> parameters)
{
List <WorkbookModel> workbookList = new List <WorkbookModel>();
try
{
//Read the data from the database
using (IDataReader dataReader = ExecuteDataReader(query, ParameterHelper.CreateSqlParameter(parameters)))
{
if (dataReader != null)
{
while (dataReader.Read())
{
DataTable dataTable = dataReader.GetSchemaTable();
//Get the workbook details from the database
TaskModel taskComment = (dataTable.Select("ColumnName = 'Attempt_Comment'").Count() == 1) ? JsonConvert.DeserializeObject <TaskModel>(Convert.ToString(dataReader["Attempt_Comment"])) : null;
WorkbookModel workbookResponse = new WorkbookModel
{
EmployeeName = (dataTable.Select("ColumnName = 'employeeName'").Count() == 1) ? Convert.ToString(dataReader["employeeName"]) : (dataTable.Select("ColumnName = 'Employee_Full_Name'").Count() == 1) ? Convert.ToString(dataReader["Employee_Full_Name"]) : null,
WorkBookName = (dataTable.Select("ColumnName = 'workbookName'").Count() == 1) ? Convert.ToString(dataReader["workbookName"]) : (dataTable.Select("ColumnName = 'OJT_Name'").Count() == 1) ? Convert.ToString(dataReader["OJT_Name"]) : null,
Description = (dataTable.Select("ColumnName = 'Description'").Count() == 1) ? Convert.ToString(dataReader["Description"]) : null,
WorkbookCreated = (dataTable.Select("ColumnName = 'datecreated'").Count() == 1) ? Convert.ToString(dataReader["datecreated"]) : null,
WorkbookEnabled = (dataTable.Select("ColumnName = 'isEnabled'").Count() == 1) ? (bool?)(dataReader["isEnabled"]) : null,
WorkBookId = (dataTable.Select("ColumnName = 'Id'").Count() == 1) ? (dataReader["Id"] != DBNull.Value ? (int?)dataReader["Id"] : 0) : (dataTable.Select("ColumnName = 'OJT_Id'").Count() == 1) ? (dataReader["OJT_Id"] != DBNull.Value ? (int?)dataReader["OJT_Id"] : 0) : null,
RepsRequired = (dataTable.Select("ColumnName = 'OJT_Reps_Required_Count'").Count() == 1) ? (dataReader["OJT_Reps_Required_Count"] != DBNull.Value ? (int?)dataReader["OJT_Reps_Required_Count"] : 0) : null,
RepsCompleted = (dataTable.Select("ColumnName = 'OJT_Reps_Completed_Count'").Count() == 1) ? (dataReader["OJT_Reps_Completed_Count"] != DBNull.Value ? (int?)dataReader["OJT_Reps_Completed_Count"] : 0) : null,
LastSignoffBy = (dataTable.Select("ColumnName = 'LastSignOffBy'").Count() == 1) ? Convert.ToString((dataReader["LastSignOffBy"])) : null,
WorkbookAssignedDate = (dataTable.Select("ColumnName = 'DateAdded'").Count() == 1) ? !string.IsNullOrEmpty(Convert.ToString(dataReader["DateAdded"])) ? Convert.ToDateTime(dataReader["DateAdded"]).ToString("MM/dd/yyyy") : default(DateTime).ToString("MM/dd/yyyy") : null,
Repetitions = (dataTable.Select("ColumnName = 'Repetitions'").Count() == 1) ? Convert.ToString((dataReader["Repetitions"])) : null,
FirstAttemptDate = (dataTable.Select("ColumnName = 'FirstAttemptDate'").Count() == 1) ? !string.IsNullOrEmpty(Convert.ToString(dataReader["FirstAttemptDate"])) ? Convert.ToDateTime(dataReader["FirstAttemptDate"]).ToString("MM/dd/yyyy") : default(DateTime).ToString("MM/dd/yyyy") : null,
LastAttemptDate = (dataTable.Select("ColumnName = 'LastAttemptDate'").Count() == 1) ? !string.IsNullOrEmpty(Convert.ToString(dataReader["LastAttemptDate"])) ? Convert.ToDateTime(dataReader["LastAttemptDate"]).ToString("MM/dd/yyyy") : default(DateTime).ToString("MM/dd/yyyy") : null,
NumberCompleted = (dataTable.Select("ColumnName = 'NumberCompleted'").Count() == 1) ? (int?)(dataReader["NumberCompleted"]) : null,
Role = (dataTable.Select("ColumnName = 'Role'").Count() == 1) ? Convert.ToString(dataReader["Role"]) : (dataTable.Select("ColumnName = 'Employee_Role'").Count() == 1) ? Convert.ToString(dataReader["Employee_Role"]) : null,
CompletedWorkbook = (dataTable.Select("ColumnName = 'OJT_Completed'").Count() == 1) ? (int?)(dataReader["OJT_Completed"]) : null,
TotalTasks = (dataTable.Select("ColumnName = 'OJT_Task_Count'").Count() == 1) ? (int?)(dataReader["OJT_Task_Count"]) : null,
TotalWorkbook = (dataTable.Select("ColumnName = 'TotalWorkbooks'").Count() == 1) ? (int?)(dataReader["TotalWorkbooks"]) : null,
PastDueWorkBook = (dataTable.Select("ColumnName = 'OJT_Past_Due_Count'").Count() == 1) ? (int?)(dataReader["OJT_Past_Due_Count"]) : null,
InDueWorkBook = (dataTable.Select("ColumnName = 'OJT_Due_Count'").Count() == 1) ? (int?)(dataReader["OJT_Due_Count"]) : null,
AssignedWorkBook = (dataTable.Select("ColumnName = 'OJT_Assigned_Count'").Count() == 1) ? (int?)(dataReader["OJT_Assigned_Count"]) : null,
UserCount = (dataTable.Select("ColumnName = 'userCount'").Count() == 1) ? (int?)(dataReader["userCount"]) : null,
EntityCount = (dataTable.Select("ColumnName = 'entityCount'").Count() == 1) ? (int?)(dataReader["entityCount"]) : null,
UserName = (dataTable.Select("ColumnName = 'UserName'").Count() == 1) ? Convert.ToString(dataReader["UserName"]) : (dataTable.Select("ColumnName = 'Employee_User_Name'").Count() == 1) ? Convert.ToString(dataReader["Employee_User_Name"]) : null,
Status = (dataTable.Select("ColumnName = 'status'").Count() == 1) ? Convert.ToString(dataReader["status"]) : null,
DaysToComplete = (dataTable.Select("ColumnName = 'daystocomplete'").Count() == 1) ? Convert.ToString(dataReader["daystocomplete"]) : null,
AlternateName = (dataTable.Select("ColumnName = 'UserName2'").Count() == 1) ? Convert.ToString(dataReader["UserName2"]) : null,
Email = (dataTable.Select("ColumnName = 'email'").Count() == 1) ? Convert.ToString(dataReader["email"]) : null,
CreatedBy = (dataTable.Select("ColumnName = 'CreatedBy'").Count() == 1) ? Convert.ToString(dataReader["CreatedBy"]) : null,
Address = (dataTable.Select("ColumnName = 'Address'").Count() == 1) ? Convert.ToString(dataReader["Address"]) : null,
Phone = (dataTable.Select("ColumnName = 'Phone'").Count() == 1) ? Convert.ToString(dataReader["Phone"]) : null,
TotalEmployees = (dataTable.Select("ColumnName = 'Subordinate_Count'").Count() == 1) ? (int?)(dataReader["Subordinate_Count"]) : null,
InCompleteWorkBook = (dataTable.Select("ColumnName = 'InCompletedWorkbooks'").Count() == 1) ? (int?)(dataReader["InCompletedWorkbooks"]) : null,
UserId = (dataTable.Select("ColumnName = 'UserId'").Count() == 1) ? (dataReader["UserId"] != DBNull.Value ? (int?)dataReader["UserId"] : 0) : (dataTable.Select("ColumnName = 'Employee_Id'").Count() == 1) ? (dataReader["Employee_Id"] != DBNull.Value ? (int?)dataReader["Employee_Id"] : 0) : null,
DueDate = (dataTable.Select("ColumnName = 'OJT_Due_Date'").Count() == 1) ? !string.IsNullOrEmpty(Convert.ToString(dataReader["OJT_Due_Date"])) ? Convert.ToDateTime(dataReader["OJT_Due_Date"]).ToString("MM/dd/yyyy") : null : null,
TaskId = (dataTable.Select("ColumnName = 'taskId'").Count() == 1) ? (dataReader["taskId"] != DBNull.Value ? (int?)dataReader["taskId"] : 0) : (dataTable.Select("ColumnName = 'Task_Id'").Count() == 1) ? (dataReader["Task_Id"] != DBNull.Value ? (int?)dataReader["Task_Id"] : 0) : null,
TaskName = (dataTable.Select("ColumnName = 'taskName'").Count() == 1) ? Convert.ToString(dataReader["taskName"]) : (dataTable.Select("ColumnName = 'Task_Name'").Count() == 1) ? Convert.ToString(dataReader["Task_Name"]) : null,
TaskCode = (dataTable.Select("ColumnName = 'Code'").Count() == 1) ? Convert.ToString(dataReader["Code"]) : (dataTable.Select("ColumnName = 'Task_Code'").Count() == 1) ? Convert.ToString(dataReader["Task_Code"]) : null,
NumberofAttempts = (dataTable.Select("ColumnName = 'Attempt'").Count() == 1) ? Convert.ToString(dataReader["Attempt"]) : null,
LastAttemptDate_tasks = (dataTable.Select("ColumnName = 'Date_Attempted'").Count() == 1) ? !string.IsNullOrEmpty(Convert.ToString(dataReader["Date_Attempted"])) ? Convert.ToDateTime(dataReader["Date_Attempted"]).ToString("MM/dd/yyyy") : default(DateTime).ToString("MM/dd/yyyy") : null,
Location = (dataTable.Select("ColumnName = 'Attempt_Location'").Count() == 1) ? Convert.ToString(dataReader["Attempt_Location"]) : null,
Comments = taskComment?.Comment,
EvaluatorName = (dataTable.Select("ColumnName = 'Submitted_By_User_Id'").Count() == 1) ? Convert.ToString(dataReader["Submitted_By_User_Id"]) : null,
CompletedTasks = (dataTable.Select("ColumnName = 'OJT_Task_Completed_Count'").Count() == 1) ? Convert.ToString((dataReader["OJT_Task_Completed_Count"])) : null
};
// Adding each workbook details in array list
workbookList.Add(workbookResponse);
}
}
else
{
return(null);
}
}
return(workbookList);
}
catch (Exception readWorkbookDetailsException)
{
LambdaLogger.Log(readWorkbookDetailsException.ToString());
return(null);
}
}
static void Main(string[] args)
{
List <Customer> customers = new List <Customer>();
ExcelExport.Export.Utils.OutputDir = new DirectoryInfo($"{AppDomain.CurrentDomain.BaseDirectory}SampleApp");
using (SqlConnection sqlConn = new SqlConnection(@"Data Source=JAYANTGURU-PC\SQLSERVER2017;Initial Catalog=Practice;Persist Security Info=True;User ID=sa;Password=guru@000"))
{
sqlConn.Open();
using (SqlCommand sqlCmd = new SqlCommand(@"SELECT [CustomerID]
,[CompanyName]
,[ContactName]
,[ContactTitle]
,[Address]
,[City]
,[Region]
,[PostalCode]
,[Country]
,[Phone]
,[Fax]
FROM [Practice].[dbo].[Customers]", sqlConn))
{
using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
{
// get the data and fill rows 5 onwards
while (sqlReader.Read())
{
Customer customer = new Customer();
customer.CustomerID = sqlReader["CustomerID"] != DBNull.Value ? Convert.ToInt32(sqlReader["CustomerID"]) : 0;
customer.CompanyName = sqlReader["CompanyName"] != DBNull.Value ? sqlReader["CompanyName"].ToString() : string.Empty;
customer.ContactName = sqlReader["ContactName"] != DBNull.Value ? sqlReader["ContactName"].ToString() : string.Empty;
customer.ContactTitle = sqlReader["ContactTitle"] != DBNull.Value ? sqlReader["ContactTitle"].ToString() : string.Empty;
customer.Address = sqlReader["Address"] != DBNull.Value ? sqlReader["Address"].ToString() : string.Empty;
customer.City = sqlReader["City"] != DBNull.Value ? sqlReader["City"].ToString() : string.Empty;
customer.Region = sqlReader["Region"] != DBNull.Value ? sqlReader["Region"].ToString() : string.Empty;
customer.PostalCode = sqlReader["PostalCode"] != DBNull.Value ? sqlReader["PostalCode"].ToString() : string.Empty;
customer.Country = sqlReader["Country"] != DBNull.Value ? sqlReader["Country"].ToString() : string.Empty;
customer.Phone = sqlReader["Phone"] != DBNull.Value ? sqlReader["Phone"].ToString() : string.Empty;
customer.Fax = sqlReader["Fax"] != DBNull.Value ? sqlReader["Fax"].ToString() : string.Empty;
customers.Add(customer);
}
sqlReader.Close();
}
}
sqlConn.Close();
}
DataTable dataTable = Utils.ToDataTable <Customer>(customers);
WorkbookModel workbook = new WorkbookModel();
workbook.FilePath = ConfigurationManager.AppSettings["ExcelFilePath"];
workbook.Disclaimer = ConfigurationManager.AppSettings["Disclaimer"];
workbook.ImageModel.ImagePath = ConfigurationManager.AppSettings["LogoPath"];
WorksheetModel worksheetModel = new WorksheetModel();
worksheetModel.WorksheetData = dataTable;
foreach (var item in dataTable.Columns)
{
ColumnModel columnModel = new ColumnModel();
columnModel.ColumnName = item.ToString();
worksheetModel.DataColumnHeaderModel.Columns.Add(columnModel);
}
WorksheetHeaderModel worksheetHeaderModel = new WorksheetHeaderModel();
worksheetHeaderModel.HeaderFontSize = 15;
worksheetHeaderModel.HeaderText = "Transaction Detailed Report";
worksheetHeaderModel.MergeHeader = true;
worksheetModel.WorksheetHeaderModels.Add(worksheetHeaderModel);
WorksheetHeaderModel worksheetHeaderModel2 = new WorksheetHeaderModel();
worksheetHeaderModel2.HeaderFontSize = 15;
worksheetHeaderModel2.HeaderText = "Report Dated 01-May-2018 to 31-July-2018";
worksheetHeaderModel2.MergeHeader = true;
worksheetModel.WorksheetHeaderModels.Add(worksheetHeaderModel2);
WorksheetHeaderModel worksheetHeaderModel3 = new WorksheetHeaderModel();
worksheetHeaderModel3.HeaderFontSize = 15;
worksheetHeaderModel3.HeaderText = "AMC: Aditya Birla Mutual Funds";
worksheetHeaderModel3.MergeHeader = true;
worksheetModel.WorksheetHeaderModels.Add(worksheetHeaderModel3);
WorksheetHeaderModel worksheetHeaderModel4 = new WorksheetHeaderModel();
worksheetHeaderModel4.HeaderFontSize = 15;
worksheetHeaderModel4.HeaderText = "Asset Type: Equity";
worksheetHeaderModel4.MergeHeader = true;
worksheetHeaderModel4.HorizontalAlignment = HorizontalAlignment.Left;
worksheetModel.WorksheetHeaderModels.Add(worksheetHeaderModel4);
WorksheetHeaderModel worksheetHeaderModel5 = new WorksheetHeaderModel();
worksheetHeaderModel5.HeaderFontSize = 10;
worksheetHeaderModel5.HeaderText = "Sub Type: Mid Cap";
worksheetHeaderModel5.MergeHeader = true;
worksheetHeaderModel5.HorizontalAlignment = HorizontalAlignment.Left;
worksheetModel.WorksheetHeaderModels.Add(worksheetHeaderModel5);
workbook.WorksheetModels.Add(worksheetModel);
Excel.CreateExcel(workbook);
}
/// <summary>
/// Create the sprudel xml document
/// </summary>
/// <param name="n">WorkbookModel</param>
/// <returns>complete sprudel xml as XElement</returns>
public object Visit(WorkbookModel n)
{
PolicyConfigurationModel settings = n.PolicySettings;
var root = new XElement("policyList");
var dynamicPolicy = new XElement("dynamicPolicy");
//attributes
dynamicPolicy.Add(new XAttribute("name", NullCheck(n.Title) + " Inspection"));
dynamicPolicy.Add(new XAttribute("description", GetDocumentProperty(n, "Comments")));
dynamicPolicy.Add(new XAttribute("author", GetDocumentProperty(n, "Author")));
//rules
var rules = new XElement("rules");
foreach (var scenario in n.Scenarios)
{
rules.Add(scenario.Accept(this) as XElement);
}
dynamicPolicy.Add(rules);
//spreadsheet file path
dynamicPolicy.Add(new XElement("spreadsheetFilePath", NullCheck(n.Spreadsheet)));
//input cells
dynamicPolicy.Add(CreateInputCells(n));
//output cells
dynamicPolicy.Add(CreateOutputCells(n));
// TODO: don't add when no scenario is present
root.Add(dynamicPolicy);
XElement sanityRules = CreateSanityRules(n);
if (sanityRules != null)
{
root.Add(sanityRules);
}
if ((settings.ReadingDirection && type == InspectionType.MANUAL) ||
(settings.ReadingDirection && settings.ReadingDirectionAutomatic))
{
XElement readingDirection = createReadingDirection();
root.Add(readingDirection);
}
if ((settings.NoConstantsInFormulas && type == InspectionType.MANUAL) ||
(settings.NoConstantsInFormulas && settings.NoConstantsInFormulasAutomatic))
{
XElement constants = createNoConstants();
root.Add(constants);
}
if ((settings.FormulaComplexity && type == InspectionType.MANUAL) ||
(settings.FormulaComplexity && settings.FormulaComplexityAutomatic))
{
XElement formulaComplexity = createFormulaComplexity();
root.Add(formulaComplexity);
}
if ((settings.NonConsideredConstants && type == InspectionType.MANUAL) ||
(settings.NonConsideredConstants && settings.NonConsideredConstantsAutomatic))
{
XElement nonConsidered = createNonConsideredValues();
root.Add(nonConsidered);
}
if ((settings.OneAmongOthers && type == InspectionType.MANUAL) ||
(settings.OneAmongOthers && settings.OneAmongOthersAutomatic))
{
XElement oneAmongOthers = createOneAmongOthers();
root.Add(oneAmongOthers);
}
if ((settings.RefToNull && type == InspectionType.MANUAL) ||
(settings.RefToNull && settings.RefToNullAutomatic))
{
XElement refToNull = createRefToNull();
root.Add(refToNull);
}
if ((settings.StringDistance && type == InspectionType.MANUAL) ||
(settings.StringDistance && settings.StringDistanceAutomatic))
{
XElement stringDistance = createStringDistance(settings);
root.Add(stringDistance);
}
if ((settings.MultipleSameRef && type == InspectionType.MANUAL) ||
(settings.MultipleSameRef && settings.MultipleSameRefAutomatic))
{
XElement msr = createMultipleSameRef();
root.Add(msr);
}
if ((settings.ErrorInCells && type == InspectionType.MANUAL) ||
(settings.ErrorInCells && settings.ErrorInCellsAutomatic))
{
XElement eic = createErrorInCells();
root.Add(eic);
}
return(root);
}
/// <summary>
/// Selects a cell in a workbook
/// </summary>
/// <param name="wb">workbook model</param>
/// <param name="location">location of the cell to select</param>
public void SelectCell(WorkbookModel wb, string location)
{
new CellLocation(wb.Workbook, location).Select();
}
public void Start(WorkbookModel wb, string scenarioTitle)
{
if (newScenario != null)
{
return;
}
lock (syncScenario)
{
newScenario = new Scenario
{
Title = scenarioTitle,
CreationDate = DateTime.Now
};
}
workbook = wb.Workbook;
var workingList = wb.ScenarioCells.ToList();
//sort working list column first
#region sort
workingList.Sort(delegate(Cell x, Cell y)
{
//sort by worksheet
var xSheet = workbook.Sheets[x.WorksheetKey] as Worksheet;
var ySheet = workbook.Sheets[y.WorksheetKey] as Worksheet;
if (xSheet.Index < ySheet.Index)
{
return(-1);
}
if (xSheet.Index > ySheet.Index)
{
return(1);
}
//sort by column
var xRange = xSheet.Range[x.ShortLocation];
var yRange = ySheet.Range[y.ShortLocation];
if (xRange.Column < yRange.Column)
{
return(-1);
}
if (xRange.Column > yRange.Column)
{
return(1);
}
//sort by row
if (xRange.Row < yRange.Row)
{
return(-1);
}
return(xRange.Row > yRange.Row ? 1 : 0);
});
#endregion
foreach (var c in DataModel.Instance.CurrentWorkbook.ScenarioCells)
{
switch (c.ScenarioCellType)
{
case ScenarioCellType.INPUT:
var inputData = new InputData(c.Location);
createContainer(c, inputData);
newScenario.Inputs.Add(inputData);
break;
case ScenarioCellType.INVARIANT:
var invariantData = new InvariantData(c.Location);
newScenario.Invariants.Add(invariantData);
break;
case ScenarioCellType.CONDITION:
var conditionData = new ConditionData(c.Location);
createContainer(c, conditionData);
newScenario.Conditions.Add(conditionData);
break;
}
}
//set focus to first control
if (containers.Count > 0)
{
foreach (var c in containers)
{
c.ScenarioDataField.RegisterNextFocusField(c.ScenarioDataField);
}
containers.First().ScenarioDataField.SetFocus();
}
}
public void Start(WorkbookModel wb, string scenarioTitle)
{
if (newScenario != null)
{
return;
}
lock (syncScenario)
{
if (newScenario != null)
{
return;
}
newScenario = new Scenario
{
Title = scenarioTitle,
CrationDate = DateTime.Now,
Author = GetDocumentProperty(wb, "Last Author")
};
}
workbook = wb.Workbook;
var workingList = wb.InputCells.Union(wb.IntermediateCells).Union(wb.OutputCells).ToList();
//sort working list column first
#region sort
workingList.Sort(delegate(Cell x, Cell y)
{
//sort by worksheet
var xSheet = workbook.Sheets[CellManager.Instance.ParseWorksheetName(x.Location)] as Worksheet;
var ySheet = workbook.Sheets[CellManager.Instance.ParseWorksheetName(x.Location)] as Worksheet;
if (xSheet.Index < ySheet.Index)
{
return(-1);
}
if (xSheet.Index > ySheet.Index)
{
return(1);
}
//sort by column
var xRange = xSheet.Range[CellManager.Instance.ParseCellLocation(x.Location)];
var yRange = ySheet.Range[CellManager.Instance.ParseCellLocation(y.Location)];
if (xRange.Column < yRange.Column)
{
return(-1);
}
if (xRange.Column > yRange.Column)
{
return(1);
}
//sort by row
if (xRange.Row < yRange.Row)
{
return(-1);
}
return(xRange.Row > yRange.Row ? 1 : 0);
});
#endregion
CreateScenarioDataFieldContainer containerFirst = null;
CreateScenarioDataFieldContainer containerBefore = null;
foreach (var c in workingList)
{
//create cell data
CellData cellData;
#region create cell data
if (c is InputCell)
{
cellData = new InputCellData();
cellData.Location = c.Location;
cellData.SifLocation = c.SifLocation;
newScenario.Inputs.Add(cellData as InputCellData);
}
else if (c is IntermediateCell)
{
cellData = new IntermediateCellData();
cellData.Location = c.Location;
cellData.SifLocation = c.SifLocation;
newScenario.Intermediates.Add(cellData as IntermediateCellData);
}
else if (c is OutputCell)
{
cellData = new ResultCellData();
cellData.Location = c.Location;
cellData.SifLocation = c.SifLocation;
newScenario.Results.Add(cellData as ResultCellData);
}
else
{
//abort
lock (syncScenario)
{
//clear this object
containers.Clear();
workbook = null;
newScenario = null;
return;
}
}
#endregion
//get worksheet
var currentWorksheet = workbook.Sheets[CellManager.Instance.ParseWorksheetName(c.Location)] as Worksheet;
var vsto = Globals.Factory.GetVstoObject(currentWorksheet);
//create container
var container = new CreateScenarioDataFieldContainer();
container.createScenarioDataField.DataContext = cellData;
containers.Add(container);
//register for focus handling
#region focus handling
if (c == workingList.First())
{
containerFirst = container;
}
else if (containerBefore != null)
{
containerBefore.createScenarioDataField.RegisterNextFocusField(container.createScenarioDataField);
}
containerBefore = container;
#endregion
//create control
var control = vsto.Controls.AddControl(
container,
currentWorksheet.Range[CellManager.Instance.ParseCellLocation(c.Location)],
Guid.NewGuid().ToString());
control.Placement = XlPlacement.xlMove;
}
//set focus to first control
if (containerFirst != null)
{
containerFirst.createScenarioDataField.SetFocus();
}
}
private static void CreateExcel <T>(WorkbookModel workbook, List <T> exportDataList)
{
PropertyInfo[] piT = typeof(T).GetProperties();
var file = Utils.GetFileInfo("Sample3.xlsx", "");
// ok, we can run the real code of the sample now
using (ExcelPackage xlPackage = new ExcelPackage(file))
{
// get handle to the existing worksheet
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Sales");
//var namedStyle = xlPackage.Workbook.Styles.CreateNamedStyle("HyperLink"); //This one is language dependent
//namedStyle.Style.Font.UnderLine = true;
//namedStyle.Style.Font.Color.SetColor(Color.Blue);
if (worksheet != null)
{
const int startRow = 5;
int row = startRow;
//Create Headers and format them
worksheet.Cells["A1"].Value = "Transaction Detailed Report.";
using (ExcelRange r = worksheet.Cells["A1:G1"])
{
r.Merge = true;
r.Style.Font.SetFromFont(new Font("Britannic Bold", 22));
r.Style.Font.Color.SetColor(Color.White);
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
}
worksheet.Cells["A2"].Value = "From Date 01-April-2018 To 31-July-2018";
using (ExcelRange r = worksheet.Cells["A2:G2"])
{
r.Merge = true;
r.Style.Font.SetFromFont(new Font("Britannic Bold", 18));
r.Style.Font.Color.SetColor(Color.Black);
r.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
r.Style.Fill.PatternType = ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
}
worksheet.Cells["A4"].Value = "CustomerID";
worksheet.Cells["B4"].Value = "CompanyName";
worksheet.Cells["C4"].Value = "ContactName";
worksheet.Cells["D4"].Value = "ContactTitle";
worksheet.Cells["E4"].Value = "Address";
worksheet.Cells["F4"].Value = "City";
worksheet.Cells["G4"].Value = "Region";
worksheet.Cells["H4"].Value = "PostalCode";
worksheet.Cells["I4"].Value = "Country";
worksheet.Cells["J4"].Value = "Phone";
worksheet.Cells["K4"].Value = "Fax";
worksheet.Cells["A4:K4"].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells["A4:K4"].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
worksheet.Cells["A4:K4"].Style.Font.Bold = true;
foreach (var item in exportDataList)
{
int col = 1;
for (int property = 0; property < piT.Count(); property++)
{
worksheet.Cells[row, col].Value = piT[property].GetValue(item, null);
col++;
}
row++;
}
//Set column width
worksheet.Column(1).Width = 20;
worksheet.Column(2).Width = 50;
worksheet.Column(3).Width = 50;
worksheet.Column(4).Width = 50;
worksheet.Column(5).Width = 55;
worksheet.Column(6).Width = 40;
worksheet.Column(7).Width = 12;
worksheet.Column(8).Width = 15;
worksheet.Column(9).Width = 20;
worksheet.Column(10).Width = 25;
worksheet.Column(11).Width = 25;
// lets set the header text
worksheet.HeaderFooter.OddHeader.CenteredText = "AdventureWorks Inc. Sales Report";
// add the page number to the footer plus the total number of pages
worksheet.HeaderFooter.OddFooter.RightAlignedText =
string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
// add the sheet name to the footer
worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
// add the file path to the footer
worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
}
// we had better add some document properties to the spreadsheet
// set some core property values
xlPackage.Workbook.Properties.Title = "Sample 3";
xlPackage.Workbook.Properties.Author = "John Tunnicliffe";
xlPackage.Workbook.Properties.Subject = "ExcelPackage Samples";
xlPackage.Workbook.Properties.Keywords = "Office Open XML";
xlPackage.Workbook.Properties.Category = "ExcelPackage Samples";
xlPackage.Workbook.Properties.Comments = "This sample demonstrates how to create an Excel 2007 file from scratch using the Packaging API and Office Open XML";
// set some extended property values
xlPackage.Workbook.Properties.Company = "AdventureWorks Inc.";
xlPackage.Workbook.Properties.HyperlinkBase = new Uri("http://www.codeplex.com/MSFTDBProdSamples");
// set some custom property values
xlPackage.Workbook.Properties.SetCustomPropertyValue("Checked by", "John Tunnicliffe");
xlPackage.Workbook.Properties.SetCustomPropertyValue("EmployeeID", "1147");
xlPackage.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "ExcelPackage");
// save the new spreadsheet
xlPackage.Save();
}
//return file.FullName;
}
