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; }