public static SpreadSheetDocument CreateDocument1() { SpreadSheetDocument retVal = new SpreadSheetDocument(); retVal.Template.Rows.Add(new Row("R1", "Затрата 1")); retVal.Template.Rows.Add(new Row("R2", "Затрата 2")); retVal.Template.Rows.Add(new Row("R3", "Затрата 3")); retVal.AddCell("2006-Q1-01", "R1", 1); retVal.AddCell("2006-Q1-02", "R1", 2); retVal.AddCell("2006-Q1-03", "R1", 3); retVal.AddCell("2006-Q2-04", "R1", 4); retVal.AddCell("2006-Q2-05", "R1", 5); retVal.AddCell("2006-Q2-06", "R1", 6); retVal.AddCell("2006-Q3-07", "R1", 7); retVal.AddCell("2006-Q3-08", "R1", 8); retVal.AddCell("2006-Q3-09", "R1", 9); retVal.AddCell("2006-Q4-10", "R1", 10); retVal.AddCell("2006-Q4-11", "R1", 11); retVal.AddCell("2006-Q4-12", "R1", 12); retVal.AddCell("2006-Q1-01", "R3", 1.01); retVal.AddCell("2006-Q1-02", "R3", 2.01); retVal.AddCell("2006-Q1-03", "R3", 3.01); retVal.AddCell("2006-Q4-10", "R3", 10.01); retVal.AddCell("2006-Q4-11", "R3", 11.01); retVal.AddCell("2006-Q4-12", "R3", 12.01); return(retVal); }
public static SpreadSheetDocument CreateDocument1() { SpreadSheetDocument retVal = new SpreadSheetDocument(); retVal.Template.Rows.Add(new Row("R1","Затрата 1")); retVal.Template.Rows.Add(new Row("R2","Затрата 2")); retVal.Template.Rows.Add(new Row("R3","Затрата 3")); retVal.AddCell("2006-Q1-01", "R1", 1); retVal.AddCell("2006-Q1-02", "R1", 2); retVal.AddCell("2006-Q1-03", "R1", 3); retVal.AddCell("2006-Q2-04", "R1", 4); retVal.AddCell("2006-Q2-05", "R1", 5); retVal.AddCell("2006-Q2-06", "R1", 6); retVal.AddCell("2006-Q3-07", "R1", 7); retVal.AddCell("2006-Q3-08", "R1", 8); retVal.AddCell("2006-Q3-09", "R1", 9); retVal.AddCell("2006-Q4-10", "R1", 10); retVal.AddCell("2006-Q4-11", "R1", 11); retVal.AddCell("2006-Q4-12", "R1", 12); retVal.AddCell("2006-Q1-01", "R3", 1.01); retVal.AddCell("2006-Q1-02", "R3", 2.01); retVal.AddCell("2006-Q1-03", "R3", 3.01); retVal.AddCell("2006-Q4-10", "R3", 10.01); retVal.AddCell("2006-Q4-11", "R3", 11.01); retVal.AddCell("2006-Q4-12", "R3", 12.01); return retVal; }
/// <summary> /// Initializes a new instance of the <see cref="SpreadSheetView"/> class. /// </summary> /// <param name="Document">The document.</param> /// <param name="FromYear">From year.</param> /// <param name="ToYear">To year.</param> public SpreadSheetView(SpreadSheetDocument Document, int FromYear, int ToYear) { _document = Document; _fromYear = FromYear; _toYear = ToYear; Init(); }
/// <summary> /// Initializes a new instance of the <see cref="SpreadSheetView"/> class. /// </summary> /// <param name="Document">The document.</param> /// <param name="FromYear">From year.</param> /// <param name="ToYear">To year.</param> public SpreadSheetView(SpreadSheetDocument Document, int FromYear, int ToYear) { _document = Document; _fromYear = FromYear; _toYear = ToYear; Init(); }
public static SpreadSheetDocument CreateDocument2() { SpreadSheetDocument retVal = new SpreadSheetDocument(); retVal.Template.Rows.Add(new Row("R1", "Затрата 1")); retVal.Template.Rows.Add(new Row("R2", "Затрата 2")); Block block = new Block("B1", "Блок Затрат"); block.ChildRows.Add(new Row("B1-1", "Item 1")); block.ChildRows.Add(new Row("B1-2", "Item 2")); block.ChildRows.Add(new Row("B1-3", "Item 3")); retVal.Template.Rows.Add(block); retVal.Template.Rows.Add(new Row("R3", "Затрата 3")); // retVal.AddCell("2005-Q2-06", "R1", 100); // retVal.AddCell("2007-Q2-06", "R1", -100); retVal.AddCell("2006-Q1-01", "R1", 1); retVal.AddCell("2006-Q1-02", "R1", 2); retVal.AddCell("2006-Q1-03", "R1", 3); retVal.AddCell("2006-Q2-04", "R1", 4); retVal.AddCell("2006-Q2-05", "R1", 5); retVal.AddCell("2006-Q2-06", "R1", 6); retVal.AddCell("2006-Q3-07", "R1", 7); retVal.AddCell("2006-Q3-08", "R1", 8); retVal.AddCell("2006-Q3-09", "R1", 9); retVal.AddCell("2006-Q4-10", "R1", 10); retVal.AddCell("2006-Q4-11", "R1", 11); retVal.AddCell("2006-Q4-12", "R1", 12); retVal.AddCell("2006-Q1-01", "R3", 1.01); retVal.AddCell("2006-Q1-02", "R3", 2.01); retVal.AddCell("2006-Q1-03", "R3", 3.01); retVal.AddCell("2006-Q4-10", "R3", 10.01); retVal.AddCell("2006-Q4-11", "R3", 11.01); retVal.AddCell("2006-Q4-12", "R3", 12.01); return(retVal); }
public static SpreadSheetDocument CreateDocument2() { SpreadSheetDocument retVal = new SpreadSheetDocument(); retVal.Template.Rows.Add(new Row("R1","Затрата 1")); retVal.Template.Rows.Add(new Row("R2","Затрата 2")); Block block = new Block("B1", "Блок Затрат"); block.ChildRows.Add(new Row("B1-1","Item 1")); block.ChildRows.Add(new Row("B1-2","Item 2")); block.ChildRows.Add(new Row("B1-3","Item 3")); retVal.Template.Rows.Add(block); retVal.Template.Rows.Add(new Row("R3","Затрата 3")); // retVal.AddCell("2005-Q2-06", "R1", 100); // retVal.AddCell("2007-Q2-06", "R1", -100); retVal.AddCell("2006-Q1-01", "R1", 1); retVal.AddCell("2006-Q1-02", "R1", 2); retVal.AddCell("2006-Q1-03", "R1", 3); retVal.AddCell("2006-Q2-04", "R1", 4); retVal.AddCell("2006-Q2-05", "R1", 5); retVal.AddCell("2006-Q2-06", "R1", 6); retVal.AddCell("2006-Q3-07", "R1", 7); retVal.AddCell("2006-Q3-08", "R1", 8); retVal.AddCell("2006-Q3-09", "R1", 9); retVal.AddCell("2006-Q4-10", "R1", 10); retVal.AddCell("2006-Q4-11", "R1", 11); retVal.AddCell("2006-Q4-12", "R1", 12); retVal.AddCell("2006-Q1-01", "R3", 1.01); retVal.AddCell("2006-Q1-02", "R3", 2.01); retVal.AddCell("2006-Q1-03", "R3", 3.01); retVal.AddCell("2006-Q4-10", "R3", 10.01); retVal.AddCell("2006-Q4-11", "R3", 11.01); retVal.AddCell("2006-Q4-12", "R3", 12.01); return retVal; }
public static SpreadSheetView CreateView(SpreadSheetDocument doc) { SpreadSheetView view = new SpreadSheetView(doc, 2006, 2006); // // for (int i = 0; i < 12; i++) // { // Column col = new Column(); // col.AllowUserValue = true; // col.Id = i.ToString();//String.Format("2006-Q{0}-{1}", (int)((i / 3) + 1), ((i % 12) + 1)); // col.Name = String.Format("2006-Q{0}-{1}", (int)((i / 3) + 1), ((i % 12) + 1)); // view.Columns.Add(col); // } // // if (_testView == null) // _testView = view; return(view); }
// internal const string Year = "{0:0000}"; // internal const string YearWeek = "{0:0000}-W{1:00}"; // internal const string YearQuarter = "{0:0000}-Q{1:0}"; // internal const string YearMonth = "{0:0000}-{1:00}"; // internal const string Total = "TT"; #endregion /// <summary> /// Initializes a new instance of the <see cref="SpreadSheetView"/> class. /// </summary> /// <param name="Document">The document.</param> public SpreadSheetView(SpreadSheetDocument Document) { _document = Document; Init(); }
/// <summary> /// Initializes a new instance of the <see cref="Cell"/> class. /// </summary> public Cell(SpreadSheetDocument Owner, string ColumnId, string RowId) { _owner = Owner; _pos = new CellPosition(ColumnId, RowId); }
/// <summary> /// Initializes a new instance of the <see cref="Cell"/> class. /// </summary> public Cell(SpreadSheetDocument Owner, string ColumnId, string RowId) { _owner = Owner; _pos = new CellPosition(ColumnId, RowId); }
public static SpreadSheetView CreateView(SpreadSheetDocument doc) { SpreadSheetView view = new SpreadSheetView(doc, 2006, 2006); // // for (int i = 0; i < 12; i++) // { // Column col = new Column(); // col.AllowUserValue = true; // col.Id = i.ToString();//String.Format("2006-Q{0}-{1}", (int)((i / 3) + 1), ((i % 12) + 1)); // col.Name = String.Format("2006-Q{0}-{1}", (int)((i / 3) + 1), ((i % 12) + 1)); // view.Columns.Add(col); // } // // if (_testView == null) // _testView = view; return view; }
public static SpreadSheetView CompareProjects(ArrayList ProjectIdList, SpreadSheetDocumentType DocumentType, int Index, int FromYear, int ToYear) { if (!(Security.IsUserInGroup(InternalSecureGroups.PowerProjectManager) || Security.IsUserInGroup(InternalSecureGroups.ExecutiveManager))) throw new AccessDeniedException(); SpreadSheetDocument document = new SpreadSheetDocument(DocumentType); // Step 1. Create Template #region Create Template SpreadSheetTemplate template = document.Template; BusinessScore[] businessScoreList = BusinessScore.List(); foreach (int projectId in ProjectIdList) { if (!IsActive(projectId)) continue; string projectName = string.Empty; using (IDataReader reader = Project.GetProject(projectId, false)) { if (reader.Read()) { projectName = (string)reader["Title"]; } } Block projectBlock = new Block(string.Format("Prj{0:00000}", projectId), projectName, false, true, string.Empty, string.Empty); foreach (BusinessScore score in businessScoreList) { Row scoreRow = new Row(string.Format("{0}_{1}", projectBlock.Id, score.Key), score.Name, true, string.Empty, string.Empty); projectBlock.ChildRows.Add(scoreRow); } string expression = projectBlock.Expression; template.Rows.Add(projectBlock); } #endregion // Step 2. Load Document #region Load Document Hashtable hashBusinessScoreKeyById = new Hashtable(); Hashtable hashBusinessScoreIdByKey = new Hashtable(); // Load hashBusinessScoreKeyById foreach (BusinessScore bs in businessScoreList) { hashBusinessScoreKeyById.Add(bs.BusinessScoreId, bs.Key); hashBusinessScoreIdByKey.Add(bs.Key, bs.BusinessScoreId); } if (Index >= 0) { foreach (BusinessScoreDataRow dataRow in BusinessScoreDataRow.List(Index)) { if (ProjectIdList.Contains(dataRow.ProjectId) && hashBusinessScoreKeyById.ContainsKey(dataRow.BusinessScoreId)) { string ColumnId = SpreadSheetView.GetColumnByDate(DocumentType, dataRow.Date); string RowId = string.Format("Prj{0:00000}_{1}", dataRow.ProjectId, hashBusinessScoreKeyById[dataRow.BusinessScoreId]); Cell cell = document.GetCell(ColumnId, RowId); if (cell == null) { cell = document.AddCell(ColumnId, RowId, CellType.Common, 0); } cell.Value += dataRow.Value; } } } else { foreach (int ProjectId in ProjectIdList) { foreach (ActualFinances finance in ActualFinances.List(ProjectId, ObjectTypes.Project)) { SpreadSheetView projectFactView = LoadView(ProjectId, -1, FromYear, ToYear); if (projectFactView != null) { foreach (string key in hashBusinessScoreIdByKey.Keys) { string srcColumnId = SpreadSheetView.GetColumnByDate(projectFactView.Document.DocumentType, finance.Date); int srcColumnIndex = projectFactView.GetColumnIndex(srcColumnId); int srcRowIndex = projectFactView.GetRowIndex(key); if (srcColumnIndex != -1 && srcRowIndex != -1) { Cell srcCell = projectFactView.GetCell(srcColumnIndex, srcRowIndex); if (srcCell != null) { string ColumnId = SpreadSheetView.GetColumnByDate(DocumentType, finance.Date); string RowId = string.Format("Prj{0:00000}_{1}", ProjectId, key); Cell destCell = document.GetCell(ColumnId, RowId); if (destCell == null) { destCell = document.AddCell(ColumnId, RowId, CellType.Common, 0); destCell.Value += srcCell.Value; } else { if (srcCell.Type != CellType.AutoCalc) { destCell.Value += srcCell.Value; } } } } } } } /*foreach(ActualFinances finance in ActualFinances.List(ProjectId, ObjectTypes.Project)) { if(hashBusinessScoreIdByKey.ContainsKey(finance.RowId)) { string ColumnId = SpreadSheetView.GetColumnByDate(DocumentType,finance.Date); string RowId = string.Format("Prj{0:00000}_{1}", ProjectId , finance.RowId); Cell cell = document.GetCell(ColumnId, RowId); if(cell==null) { cell = document.AddCell(ColumnId, RowId, CellType.Common, 0); } cell.Value += finance.Value; } }*/ } } #endregion // Step 3. Create View And Return return new SpreadSheetView(document, FromYear, ToYear); }
internal static void SaveBusinessScore(int ProjectId, int Index, SpreadSheetDocument Document) { using (DbTransaction tran = DbTransaction.Begin()) { // Delete prev results by ProjectId and Index BusinessScoreDataRow.Delete(ProjectId, Index); if (Document.Cells.Length != 0) { // Fill Business Score Hash BusinessScore[] bsList = BusinessScore.List(); Hashtable bsHash = new Hashtable(); foreach (BusinessScore bs in bsList) { bsHash.Add(bs.Key, bs.BusinessScoreId); } bool bContainsBusinessScoreRow = false; foreach (Row row in Document.Template.Rows) { if (bsHash.ContainsKey(row.Id)) { bContainsBusinessScoreRow = true; break; } } if (bContainsBusinessScoreRow) { SpreadSheetView bsView = new SpreadSheetView(Document); Hashtable businessScoreHash = new Hashtable(); List<ExpressionInfo> userValueExpList = new List<ExpressionInfo>(); List<string> checkRelaitedList = new List<string>(); // Calculate Business Score foreach (Row row in Document.Template.Rows) { if (bsHash.ContainsKey(row.Id)) { // Save Business Score foreach (Column column in bsView.Columns) { Cell dataCell = bsView.GetCell(column.Id, row.Id); switch (dataCell.Type) { case CellType.Common: BusinessScoreDataRow newRowCommon = new BusinessScoreDataRow(); newRowCommon.BusinessScoreId = (int)bsHash[row.Id]; newRowCommon.ProjectId = ProjectId; newRowCommon.Index = Index; newRowCommon.Date = SpreadSheetView.GetDateByColumn(column.Id); newRowCommon.Value = dataCell.Value; businessScoreHash.Add(dataCell.Uid, newRowCommon); break; case CellType.AutoCalc: if (column.Id.IndexOf("T") == -1 || (Document.DocumentType == SpreadSheetDocumentType.Total) || (Document.DocumentType == SpreadSheetDocumentType.Year && column.Id != "TT")) { //userValueExpList.Add(dataCell.Expression); userValueExpList.Add(dataCell.GetExpressionInfo()); BusinessScoreDataRow newRowAutoCalc = new BusinessScoreDataRow(); newRowAutoCalc.BusinessScoreId = (int)bsHash[row.Id]; newRowAutoCalc.ProjectId = ProjectId; newRowAutoCalc.Index = Index; newRowAutoCalc.Date = SpreadSheetView.GetDateByColumn(column.Id); if (newRowAutoCalc.Date == DateTime.MinValue) newRowAutoCalc.Date = DateTime.Now; newRowAutoCalc.Value = dataCell.Value; businessScoreHash.Add(dataCell.Uid, newRowAutoCalc); } break; case CellType.UserValue: //userValueExpList.Add(dataCell.Expression); userValueExpList.Add(dataCell.GetExpressionInfo()); checkRelaitedList.Add(dataCell.Uid); BusinessScoreDataRow newRowUserValue = new BusinessScoreDataRow(); newRowUserValue.BusinessScoreId = (int)bsHash[row.Id]; newRowUserValue.ProjectId = ProjectId; newRowUserValue.Index = Index; newRowUserValue.Date = SpreadSheetView.GetDateByColumn(column.Id); newRowUserValue.Value = dataCell.Value; businessScoreHash.Add(dataCell.Uid, newRowUserValue); break; } } } } // Check Relaited with checkRelaitedList Items // Remove Common Items by UserValue foreach (ExpressionInfo exInfo in userValueExpList) { //ExpressionInfo exInfo = ExpressionInfo.Parse(Expression); foreach (string CellUID in exInfo.Params) { if (businessScoreHash.ContainsKey(CellUID)) { businessScoreHash.Remove(CellUID); } } } // Save Business Score foreach (BusinessScoreDataRow data in businessScoreHash.Values) { if (data.Value != 0) data.Update(); } } } tran.Commit(); } }
internal static SpreadSheetDocument InitProjectDocument(int ProjectId, out int ProjectSpreadSheetId) { ProjectSpreadSheetId = 0; string SpreadSheetTemplateDirectory = ProjectSpreadSheet.TemplateDirectory; ProjectSpreadSheetRow[] SpreadSheets = ProjectSpreadSheetRow.List(ProjectId); if (SpreadSheets.Length == 0) return null; ProjectSpreadSheetRow prjSpreadSheetRow = SpreadSheets[0]; ProjectSpreadSheetId = prjSpreadSheetRow.ProjectSpreadSheetId; // Load Document SpreadSheetDocument document = new SpreadSheetDocument((SpreadSheetDocumentType)prjSpreadSheetRow.DocumentType); // Step 1. Load Template document.Template.Load(Path.Combine(SpreadSheetTemplateDirectory, prjSpreadSheetRow.BaseTemplateName)); if (prjSpreadSheetRow.UserRows != string.Empty) { try { document.Template.LoadXml(prjSpreadSheetRow.UserRows); } catch (Exception ex) { System.Diagnostics.Trace.WriteLine(ex); } } return document; }
/// <summary> /// Initializes a new instance of the <see cref="SpreadSheetView"/> class. /// </summary> /// <param name="Document">The document.</param> public SpreadSheetView(SpreadSheetDocument Document) { _document = Document; Init(); }