Exemple #1
0
        private void CopyRange(ref CellRangeTemplate sourceRange, SheetData sheetData, Worksheet worksheet, ref CellPosition target, double?rowHeight = null)
        {
            #region
            Sheet      sheetTemplate        = sourceRange.CellRange.SheetTemplate;
            var        workbookPartTemplate = sourceRange.CellRange.WorksheetPart;
            MergeCells mergeCellsTemplate   = sourceRange.CellRange.MergeCells;

            MergeCells mergeCells = worksheet.GetFirstChild <MergeCells>();

            if (false && workbookPartTemplate.DrawingsPart != null && worksheet.WorksheetPart.DrawingsPart == null)
            {
                var drawingsPart = worksheet.WorksheetPart.AddPart <DrawingsPart>(workbookPartTemplate.DrawingsPart);

                drawingsPart = worksheet.WorksheetPart.DrawingsPart;

                if (!worksheet.WorksheetPart.Worksheet.ChildElements.OfType <Drawing>().Any())
                {
                    worksheet.WorksheetPart.Worksheet.Append(new Drawing {
                        Id = worksheet.WorksheetPart.GetIdOfPart(drawingsPart)
                    });
                }
            }

            Dictionary <string, MergeCell> mergeCellTemplateDic = sourceRange.CellRange.MergeCellsDic;
            #endregion

            CellPosition source = sourceRange.CellRange.Start;
            CellPosition offset = target.CalculateOffset(source);

            var cellsToCopy = document.FindCellsByRange(sourceRange.CellRange);
            for (int i = 0; i < cellsToCopy.Count(); i++)
            {
                var rowGroup = cellsToCopy.ElementAt(i);
                Row keyRow   = rowGroup.Key;

                Row targetRow = new Row()
                {
                    RowIndex = (UInt32)(keyRow.RowIndex + offset.Row),
                    Height   = (short)-1,
                };

                if (rowHeight != null)
                {
                    targetRow.Height       = rowHeight;
                    targetRow.CustomHeight = true;
                }

                MoveCurrentRow((int)targetRow.RowIndex.Value);
                sheetData.InsertBefore(targetRow, currentRow);

                foreach (Cell cellToCopy in rowGroup)
                {
                    Cell targetCell = (Cell)cellToCopy.Clone();

                    targetCell.CellReference = CellPosition.OffsetIt(targetCell.CellReference, offset);

                    targetRow.Append(targetCell);

                    MergeCell _findMerge;
                    if (mergeCellTemplateDic != null && mergeCellTemplateDic.TryGetValue(cellToCopy.CellReference.Value.ToUpper(), out _findMerge))
                    {
                        var          positionParent = _findMerge.Reference.Value.Split(':');
                        CellPosition offsetStart    = new CellPosition(positionParent[0]);
                        CellPosition offsetEnd      = new CellPosition(positionParent[1]);

                        var celRefNew = new CellPosition(targetCell.CellReference);


                        if (mergeCells == null)
                        {
                            var a = new MergeCells();
                            worksheet.InsertAfter(a, sheetData);
                            mergeCells = worksheet.GetFirstChild <MergeCells>();
                        }
                        var mergeCell = new MergeCell();
                        mergeCell.Reference = celRefNew.ToString() + ":" + new CellPosition(celRefNew.Row + (offsetEnd.Row - offsetStart.Row), celRefNew.Column + (offsetEnd.Column - offsetStart.Column)).ToString();
                        mergeCells.Append(mergeCell);
                        mergeCells.Count = (mergeCells.Count ?? 0) + 1;
                    }
                }
            }
        }
Exemple #2
0
        public void Init(SpreadsheetDocument document)
        {
            AllCells = document.FindCellsByRange(CellRange);

            CellRange.SheetTemplate = document.FindSheetByName(CellRange.SheetName);
            CellRange.WorksheetPart = ((WorksheetPart)document.WorkbookPart.GetPartById(CellRange.SheetTemplate.Id));
            CellRange.MergeCells    = CellRange.WorksheetPart.Worksheet.GetFirstChild <MergeCells>();

            if (CellRange.MergeCells != null)
            {
                CellRange.MergeCellsDic = CellRange.MergeCells.ToDictionary(e => (e as MergeCell).Reference.Value.ToUpper().Split(':')[0], e => (e as MergeCell));
            }

            foreach (var rowGroup in AllCells)
            {
                foreach (Cell cell in rowGroup)
                {
                    if (cell.DataType != null && cell.CellValue != null)
                    {
                        string stringValue = null;

                        switch (cell.DataType.Value)
                        {
                        case CellValues.SharedString:
                        {
                            int index = int.Parse(cell.CellValue.Text);
                            SharedStringItem stringItem = document.WorkbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(index);

                            stringValue = stringItem.InnerText;
                            break;
                        }

                        case CellValues.String:
                        {
                            stringValue = cell.CellValue.Text;
                            break;
                        }
                        }

                        if (PARAM_PATTERN == null)
                        {
                            PARAM_PATTERN = new Regex(this.regexVariable);
                        }
                        if (!string.IsNullOrEmpty(stringValue) && PARAM_PATTERN.IsMatch(stringValue))
                        {
                            var    match        = PARAM_PATTERN.Match(stringValue);
                            var    Func         = "";
                            string Variable     = "";
                            string VariableFull = Variable = match.Groups[1].Value;


                            string regexFunc       = @".\:(.*)";
                            var    regexFuncRexgex = new Regex(regexFunc);
                            if (regexFuncRexgex.IsMatch(VariableFull))
                            {
                                var match1 = regexFuncRexgex.Match(VariableFull);

                                Func     = match1.Groups[1].Value;
                                Variable = VariableFull.Replace(":" + Func, "");
                            }

                            VariableFull = string.Format("[%{0}%]", VariableFull);
                            AllCellsConfig.Add(new
                            {
                                TextOrigin   = stringValue,
                                Cell         = cell,
                                VariableFull = VariableFull,
                                Variable     = Variable,
                                Func         = Func,
                            });
                        }
                    }
                }
            }
        }