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 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);
}
public void Write(IEnumerable <IRow> rows)
{
foreach (var row in rows)
{
var cellDfns = new List <CellDfn>();
var rowDef = new RowDfn {
Cells = cellDfns
};
cellDfns.AddRange(_fields.Select(field => new CellDfn {
CellDataType = field.ToCellDataType(),
Value = field.Type == "guid" ? row[field].ToString() : row[field],
FormatCode = field.Format.Replace("tt", "AM/PM")
}));
_rowDfns.Add(rowDef);
}
SpreadsheetWriter.Write(_fileInfo.FullName, _workbook);
}
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 static async Task WriteTestDataSampleAsync()
{
var sw = new Stopwatch();
sw.Start();
var header = new RowDfn
{
Cells = new List <CellDfn>
{
new CellDfn("Nam®e"),
new CellDfn("Index"),
new CellDfn("noVal")
}
};
var oddSheet = new WorkSheetDfn("Odd", header); //add headers
var evenSheet = new WorkSheetDfn("Even", header);
var wb = new WorkBookDfn("tempFolder");
wb.Sheets.Add(oddSheet);
wb.Sheets.Add(evenSheet);
foreach (var outerIndex in Enumerable.Range(0, 10))
{
var size = 10000;
var strad = (char)0xb;
var inValidStr = new string(new[] { strad });
foreach (var index in Enumerable.Range(outerIndex * size, size))
{
if (index % 2 == 0)
{
evenSheet.BufferedRows.Add(new RowDfn
{
Cells = new List <CellDfn>
{
new CellDfn("Name", inValidStr),
new CellDfn("Index", index.ToString())
}
});
}
else
{
oddSheet.BufferedRows.Add(new RowDfn
{
Cells = new List <CellDfn>
{
new CellDfn("Nam®e", $"f￥￥￥©$ \"oo{index}"),
new CellDfn("Index", index.ToString())
}
});
}
}
await wb.FlushBufferedRowsAsync(true); //flushDataAndClean
}
var insertSheet = new WorkSheetDfn("aaa", header);
wb.Sheets.Add(insertSheet);
evenSheet.Header.Cells.Add(new CellDfn("Latter1"));
oddSheet.Header.Cells.Add(new CellDfn("Latter2"));
using (var fs = File.Create($"{DateTime.Now.Ticks}.xlsx"))
{
using (var stream = wb.BuildExcelAndGetStream())
{
stream.Position = 0;
stream.CopyTo(fs);
}
}
wb.Dispose();
sw.Stop();
Console.WriteLine($"WriteTestDataSampleAsync cost : {sw.Elapsed}");
}
private static void SerializeRow(SpreadsheetDocument sDoc, XmlWriter xw, int rowCount, RowDfn row, out int numColumns)
{
var ns = S.s.NamespaceName;
xw.WriteStartElement("row", ns);
xw.WriteStartAttribute("r");
xw.WriteValue(rowCount);
xw.WriteEndAttribute();
xw.WriteStartAttribute("spans");
xw.WriteValue("1:" + row.Cells.Count().ToString());
xw.WriteEndAttribute();
var cellCount = 0;
foreach (var cell in row.Cells)
{
if (cell != null)
{
xw.WriteStartElement("c", ns);
xw.WriteStartAttribute("r");
xw.WriteValue(SpreadsheetMLUtil.IntToColumnId(cellCount) + rowCount.ToString());
xw.WriteEndAttribute();
if (cell.Bold != null ||
cell.Italic != null ||
cell.FormatCode != null ||
cell.HorizontalCellAlignment != null)
{
xw.WriteStartAttribute("s");
xw.WriteValue(GetCellStyle(sDoc, cell));
xw.WriteEndAttribute();
}
switch (cell.CellDataType)
{
case CellDataType.Boolean:
xw.WriteStartAttribute("t");
xw.WriteValue("b");
xw.WriteEndAttribute();
break;
case CellDataType.Date:
xw.WriteStartAttribute("t");
xw.WriteValue("d");
xw.WriteEndAttribute();
break;
case CellDataType.Number:
xw.WriteStartAttribute("t");
xw.WriteValue("n");
xw.WriteEndAttribute();
break;
case CellDataType.String:
xw.WriteStartAttribute("t");
xw.WriteValue("str");
xw.WriteEndAttribute();
break;
default:
xw.WriteStartAttribute("t");
xw.WriteValue("str");
xw.WriteEndAttribute();
break;
}
if (cell.Value != null)
{
xw.WriteStartElement("v", ns);
if (cell.Value is DateTime dt)
{
xw.WriteValue(dt.ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fff"));
}
else if (cell.Value is DateTimeOffset dts)
{
xw.WriteValue(dts.ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fffzzz"));
}
else
{
xw.WriteValue(cell.Value);
}
xw.WriteEndElement();
}
xw.WriteEndElement();
}
cellCount++;
}
xw.WriteEndElement();
numColumns = cellCount;
}
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 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);
}
}