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