Ejemplo n.º 1
0
        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();
                }
            }
        }
Ejemplo n.º 2
0
        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);
            }
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 4
0
        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;
        }
Ejemplo n.º 6
0
        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}");
        }
Ejemplo n.º 7
0
        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;
        }
Ejemplo n.º 8
0
        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();
        }
Ejemplo n.º 9
0
        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);
            }
        }