public WorkbookDfn GenerateExcelSheet(DailyReportData dailyReportSheetData) { WorkbookDfn wb = new WorkbookDfn(); WorksheetDfn ws = new WorksheetDfn(); Rows = new List <RowDfn>(); SetWorksheetNames(ws, dailyReportSheetData.SheetName); SetColumnHeaders(ws); SetDailyReportInfo(ws, dailyReportSheetData.ReportInfo); Rows.Add(new RowDfn { Cells = new CellDfn[] { new CellDfn { Value = "" } } }); SetActivityLogHeader(ws, dailyReportSheetData.ActivityLogHeaders); SetActivityLogRows(ws, dailyReportSheetData.ActivityLog); Rows.Add(new RowDfn { Cells = new CellDfn[] { new CellDfn { Value = "" } } }); SetReportBudgetHeader(ws, dailyReportSheetData.ReportBudgetHeaders); SetReportBudgetRows(ws, dailyReportSheetData.ReportBudget); ws.Rows = Rows; List <WorksheetDfn> worksheetDfns = new List <WorksheetDfn> { ws }; wb.Worksheets = worksheetDfns; return(wb); }
private void SetDailyReportInfo(WorksheetDfn worksheet, List <DailyReportInfo> dailyReportInfo) { RowDfn row = new RowDfn(); List <CellDfn> cells = new List <CellDfn>(); for (int i = 1; i <= dailyReportInfo.Count; i++) { CellDfn cell1 = new CellDfn(); cell1.Value = dailyReportInfo[i - 1].Header; cell1.Bold = true; cells.Add(cell1); CellDfn cell2 = new CellDfn(); cell2.Value = dailyReportInfo[i - 1].Value; cells.Add(cell2); CellDfn cell3 = new CellDfn(); cell3.Value = ""; cells.Add(cell3); if (i % 3 == 0) { row.Cells = cells.ToList(); Rows.Add(row); cells = new List <CellDfn>(); row = new RowDfn(); } } }
private void SetReportBudgetRows(WorksheetDfn worksheet, List <DailyReportBudgetData> reportBudgetData) { RowDfn row; List <CellDfn> cells; foreach (var budgetData in reportBudgetData) { cells = new List <CellDfn>(); row = new RowDfn(); CellDfn cell = new CellDfn(); cell.Value = budgetData.LineItem; cells.Add(cell); foreach (var milestone in budgetData.Milestones) { CellDfn milestoneCell = new CellDfn(); milestoneCell.Value = milestone; cells.Add(milestoneCell); } row.Cells = cells.ToList(); Rows.Add(row); } }
private void SetColumnHeaders(WorksheetDfn worksheet, ProductionSheetData productionSheetData) { List <CellDfn> columnHeaders = new List <CellDfn>(); foreach (var header in productionSheetData.ColumnHeaders) { CellDfn cell = new CellDfn(); cell.Value = header; cell.Bold = true; columnHeaders.Add(cell); } worksheet.ColumnHeadings = columnHeaders; }
public WorkbookDfn GenerateExcelSheet(ProductionSheetData productionSheetData) { WorkbookDfn wb = new WorkbookDfn(); WorksheetDfn ws = new WorksheetDfn(); SetWorksheetNames(ws); SetColumnHeaders(ws, productionSheetData); SetRowData(ws, productionSheetData); List <WorksheetDfn> worksheetDfns = new List <WorksheetDfn> { ws }; wb.Worksheets = worksheetDfns; return(wb); }
private void SetActivityLogHeader(WorksheetDfn worksheet, List <string> activityLogHeaders) { RowDfn row = new RowDfn(); List <CellDfn> cells = new List <CellDfn>(); foreach (var header in activityLogHeaders) { CellDfn cell = new CellDfn(); cell.Value = header; cell.Bold = true; cells.Add(cell); } row.Cells = cells; Rows.Add(row); }
private void SetRowData(WorksheetDfn worksheet, ProductionSheetData productionSheetData) { List <RowDfn> rows = new List <RowDfn>(); foreach (var dataRow in productionSheetData.DataRows) { RowDfn row = new RowDfn(); List <CellDfn> cells = new List <CellDfn>(); foreach (var cellData in dataRow) { CellDfn cell = new CellDfn(); cell.Value = cellData; cells.Add(cell); } row.Cells = cells; rows.Add(row); } worksheet.Rows = rows; }
private void SetColumnHeaders(WorksheetDfn worksheet) { List <CellDfn> columnHeaders = new List <CellDfn>(); for (int i = 0; i < 13; i++) { CellDfn cell = new CellDfn(); if (i == 6) { cell.Value = "DAILY REPORT SHEET"; cell.Bold = true; columnHeaders.Add(cell); } else { cell.Value = "-"; columnHeaders.Add(cell); } } worksheet.ColumnHeadings = columnHeaders; }
private void SetWorksheetNames(WorksheetDfn worksheet) { worksheet.Name = "production data"; worksheet.TableName = "Production Data"; }
public static void AddWorksheet(SpreadsheetDocument sDoc, WorksheetDfn worksheetData) { var validSheetName = new Regex(@"^[^'*\[\]/\\:?][^*\[\]/\\:?]{0,30}$"); if (!validSheetName.IsMatch(worksheetData.Name)) { throw new InvalidSheetNameException(worksheetData.Name); } // throw WorksheetAlreadyExistsException if a sheet with the same name (case-insensitive) already exists in the workbook var UCName = worksheetData.Name.ToUpper(); var wXDoc = sDoc.WorkbookPart.GetXDocument(); if (wXDoc.Root .Elements(S.sheets) .Elements(S.sheet) .Attributes(SSNoNamespace.name) .Select(a => ((string)a).ToUpper()) .Contains(UCName)) { throw new WorksheetAlreadyExistsException(worksheetData.Name); } // create the worksheet with the supplied name var appXDoc = sDoc .ExtendedFilePropertiesPart .GetXDocument(); var vector = appXDoc.Root .Elements(EP.TitlesOfParts) .Elements(VT.vector) .FirstOrDefault(); if (vector != null) { var size = (int?)vector.Attribute(SSNoNamespace.size) ?? 0; size += 1; vector.SetAttributeValue(SSNoNamespace.size, size); vector.Add(new XElement(VT.lpstr, worksheetData.Name)); var i4 = appXDoc.Root .Elements(EP.HeadingPairs) .Elements(VT.vector) .Elements(VT.variant) .Elements(VT.i4) .FirstOrDefault(); if (i4 is not null) { i4.Value = ((int)i4 + 1).ToString(); } sDoc.ExtendedFilePropertiesPart.PutXDocument(); } var workbook = sDoc.WorkbookPart; var rId = PresentationBuilderTools.NewRelationshipId(); var worksheetPart = workbook.AddNewPart <WorksheetPart>(rId); var wbXDoc = workbook.GetXDocument(); var sheets = wbXDoc.Descendants(S.sheets).FirstOrDefault(); sheets.Add( new XElement(S.sheet, new XAttribute(SSNoNamespace.name, worksheetData.Name), new XAttribute(SSNoNamespace.sheetId, sheets.Elements(S.sheet).Count() + 1), new XAttribute(R.id, rId))); workbook.PutXDocument(); var ws = S.s.ToString(); var relns = R.r.ToString(); using (var partStream = worksheetPart.GetStream(FileMode.Create, FileAccess.Write)) { using (var partXmlWriter = XmlWriter.Create(partStream)) { partXmlWriter.WriteStartDocument(); partXmlWriter.WriteStartElement("worksheet", ws); partXmlWriter.WriteStartElement("sheetData", ws); var numColumnHeadingRows = 0; var numColumns = 0; if (worksheetData.ColumnHeadings != null) { var row = new RowDfn { Cells = worksheetData.ColumnHeadings }; SerializeRows(sDoc, partXmlWriter, new[] { row }, 1, out numColumns, out numColumnHeadingRows); } SerializeRows(sDoc, partXmlWriter, worksheetData.Rows, numColumnHeadingRows + 1, out var numColumnsInRows, out var numRows); var totalRows = numColumnHeadingRows + numRows; var totalColumns = Math.Max(numColumns, numColumnsInRows); if (worksheetData.ColumnHeadings != null && worksheetData.TableName != null) { partXmlWriter.WriteEndElement(); var rId2 = PresentationBuilderTools.NewRelationshipId(); partXmlWriter.WriteStartElement("tableParts", ws); partXmlWriter.WriteStartAttribute("count"); partXmlWriter.WriteValue(1); partXmlWriter.WriteEndAttribute(); partXmlWriter.WriteStartElement("tablePart", ws); partXmlWriter.WriteStartAttribute("id", relns); partXmlWriter.WriteValue(rId2); var tdp = worksheetPart.AddNewPart <TableDefinitionPart>(rId2); var tXDoc = tdp.GetXDocument(); // TODO: Optimize var tableCount = sDoc.Parts .SelectMany(x => x.OpenXmlPart.Parts) .SelectMany(x => x.OpenXmlPart.Parts) .Where(x => x.OpenXmlPart is TableDefinitionPart) .Select(x => x.OpenXmlPart.Uri) .Distinct().Count(); var table = new XElement(S.table, new XAttribute(SSNoNamespace.id, tableCount + 1), new XAttribute(SSNoNamespace.name, worksheetData.TableName), new XAttribute(SSNoNamespace.displayName, worksheetData.TableName), new XAttribute(SSNoNamespace._ref, "A1:" + SpreadsheetMLUtil.IntToColumnId(totalColumns - 1) + totalRows), new XAttribute(SSNoNamespace.totalsRowShown, 0), new XElement(S.autoFilter, new XAttribute(SSNoNamespace._ref, "A1:" + SpreadsheetMLUtil.IntToColumnId(totalColumns - 1) + totalRows)), new XElement(S.tableColumns, new XAttribute(SSNoNamespace.count, totalColumns), worksheetData.ColumnHeadings.Select((ch, i) => new XElement(S.tableColumn, new XAttribute(SSNoNamespace.id, i + 1), new XAttribute(SSNoNamespace.name, ch.Value)))), new XElement(S.tableStyleInfo, new XAttribute(SSNoNamespace.name, "TableStyleMedium2"), new XAttribute(SSNoNamespace.showFirstColumn, 0), new XAttribute(SSNoNamespace.showLastColumn, 0), new XAttribute(SSNoNamespace.showRowStripes, 1), new XAttribute(SSNoNamespace.showColumnStripes, 0))); tXDoc.Add(table); tdp.PutXDocument(); } } } sDoc.WorkbookPart.WorkbookStylesPart.PutXDocument(); sDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Save(); }
private void SetWorksheetNames(WorksheetDfn worksheet, string sheetName) { worksheet.Name = sheetName; worksheet.TableName = sheetName; }
private void SetActivityLogRows(WorksheetDfn worksheet, List <ActivityLogEntryData> activityLogEntry) { RowDfn row; List <CellDfn> cells; foreach (var activityLog in activityLogEntry) { cells = new List <CellDfn>(); row = new RowDfn(); CellDfn from = new CellDfn(); from.Value = activityLog.From; cells.Add(from); CellDfn to = new CellDfn(); to.Value = activityLog.To; cells.Add(to); CellDfn elapsedTime = new CellDfn(); elapsedTime.Value = activityLog.ElapsedTime; cells.Add(elapsedTime); CellDfn cumTime = new CellDfn(); cumTime.Value = activityLog.CumulativeTime; cells.Add(cumTime); CellDfn depth = new CellDfn(); depth.Value = activityLog.Depth; cells.Add(depth); CellDfn mudWeight = new CellDfn(); mudWeight.Value = activityLog.MudWeight; cells.Add(mudWeight); CellDfn activity = new CellDfn(); activity.Value = activityLog.Activity; cells.Add(activity); CellDfn milestone = new CellDfn(); milestone.Value = activityLog.Milestone; cells.Add(milestone); CellDfn unplanned_planned = new CellDfn(); unplanned_planned.Value = activityLog.Unplanned_Planned; cells.Add(unplanned_planned); CellDfn hasNPT = new CellDfn(); hasNPT.Value = activityLog.HasNpt; cells.Add(hasNPT); CellDfn NPTReference = new CellDfn(); NPTReference.Value = activityLog.NptReference; cells.Add(NPTReference); CellDfn descrption = new CellDfn(); descrption.Value = activityLog.Description; cells.Add(descrption); row.Cells = cells.ToList(); Rows.Add(row); } }