Пример #1
0
        public static void CopyAndInsertRows(int fromRowIndex, int toRowIndex, int rowsCount, int wellIndex,
                                             WorkbookPart workbookPart, string sheetId, List <TableToInsert> dataTables)
        {
            var processedTablesRows = dataTables.ToDictionary(x => x.TableName, y => 0);
            var worksheetPart       = (WorksheetPart)workbookPart.GetPartById(sheetId);
            var mergeCells          = MergeCellHelper.GetMergeCells(workbookPart, sheetId).FirstOrDefault()
                                      .ChildElements.Select(x => x as MergeCell);

            Row oldRow            = null;
            Row generatedRow      = null;
            var mergeCellsForCopy = new List <MergeCell>();

            for (int i = 0; i < rowsCount; i++)
            {
                var oldRowIndex = fromRowIndex + i;
                oldRow       = RowHelper.GetRow(worksheetPart.Worksheet, (uint)oldRowIndex);
                generatedRow = CreateRow(worksheetPart.Worksheet, oldRow, (uint)(toRowIndex + i), dataTables, 0, new List <LocationWithValue>(), processedTablesRows);
                foreach (var cell in generatedRow.Descendants <Cell>())
                {
                    if (cell == null)
                    {
                        continue;
                    }
                    var cellValue = CellHelper.GetCellValue(cell, workbookPart);
                    if (String.IsNullOrWhiteSpace(cellValue) || cellValue.Length <= 4)
                    {
                        continue;
                    }
                    if (!cellValue.StartsWith("{{Well1"))
                    {
                        continue;
                    }
                    cellValue = cellValue.Replace("{{Well1", $"{{{{Well{wellIndex}");
                    SetCellValues(cell, new ValueToInsert(null, typeof(string), cellValue));
                }
                InsertRowHelper.InsertRow((uint)(toRowIndex + i), worksheetPart, generatedRow);

                var rowMergeCellsForCopy = mergeCells.Where(x => new MergeCellReference(x.Reference).CellFrom.RowIndex == oldRowIndex);
                mergeCellsForCopy.AddRange(rowMergeCellsForCopy);
            }
            foreach (var mergeCellForCopy in mergeCellsForCopy)
            {
                var newMergeCell = MergeCellReferenceMoveByRows(new MergeCellReference(mergeCellForCopy.Reference), toRowIndex - fromRowIndex);
                MergeCellHelper.MergeTwoCells(worksheetPart.Worksheet, newMergeCell.CellFrom.Reference, newMergeCell.CellTo.Reference);
            }
        }
Пример #2
0
        private static void FillTables(WorkbookPart workbookPart, string sheetId, List <TableToInsert> dataTables)
        {
            var processedTablesRows = dataTables.ToDictionary(x => x.TableName, y => 0);
            var worksheetPart       = (WorksheetPart)workbookPart.GetPartById(sheetId);
            var sheetData           = worksheetPart.Worksheet.GetFirstChild <SheetData>();

            ExcelHelper.AddEmptyRows(sheetData);
            var rows = sheetData.Elements <Row>().ToList();

            foreach (var row in rows)
            {
                if (!IsRowContainsCellsForFill(row, workbookPart, dataTables.Select(x => x.TableName).ToArray()))
                {
                    continue;
                }
                var fields            = GetRowFieldsForFill(row, workbookPart, dataTables.Select(x => x.TableName).ToArray());
                var generatedRowIndex = row.RowIndex;
                if (fields.Any())
                {
                    var tableNamesForAddOneRow = new List <string>();
                    // Кол-во строк которое надо обработать
                    int rowsForProcess = 0;
                    // Если нет полей, которые надо оставить без изменений
                    if (!fields.Any(x => x.ValueName.Contains(":1")))
                    {
                        // Кол-во строк из задействованных таблиц, которое осталось обработать
                        var maxTableRowsForAdd = 0;
                        foreach (var dataTable in dataTables.Where(x => fields.Any(y => y.ValueName.Split('.')[0] == x.TableName)))
                        {
                            // Если таблица пустая, то указываем, единицу для пустой строки
                            var rowsCount = dataTable.Rows.Count > 0 ? dataTable.Rows.Count : 1;
                            // Оставшиеся количество строк для таблицы, которое осталось обработать
                            var remainingTableRowsForAdd = rowsCount - processedTablesRows[dataTable.TableName];
                            if (maxTableRowsForAdd == 0 &&
                                processedTablesRows[dataTable.TableName] > 0 &&
                                remainingTableRowsForAdd <= 0)
                            {
                                // Добавление пустой строки
                                maxTableRowsForAdd = 1;
                            }
                            else
                            {
                                maxTableRowsForAdd = Math.Max(maxTableRowsForAdd, remainingTableRowsForAdd);
                            }
                        }
                        rowsForProcess = maxTableRowsForAdd;
                    }
                    else
                    {
                        // Т.к. есть поля которые надо оставить без изменений,
                        // то кол-во строк для обработки равно единице
                        rowsForProcess = 1;
                        for (int i = 0; i < fields.Count; i++)
                        {
                            fields[i] = new LocationWithValue(fields[i].RowIndex, fields[i].ColumnIndex, fields[i].ValueName.Replace(":1", ""));
                            // Имена таблиц (списков) для которых надо указать что еще одна строка обработана
                            tableNamesForAddOneRow.Add(fields[i].ValueName.Split('.')[0]);
                        }
                    }
                    // Обработка строк
                    //var merges = new List<MergeCell>();
                    for (int i = 0; i < rowsForProcess; i++)
                    {
                        var generatedRow = CreateRow(worksheetPart.Worksheet, row, generatedRowIndex, dataTables, i, fields, processedTablesRows);
                        if (i == 0)
                        {
                            row.InsertBeforeSelf(generatedRow);
                        }
                        else
                        {
                            InsertRowHelper.InsertRow(generatedRowIndex, worksheetPart, generatedRow);
                        }
                        if (row.RowIndex != generatedRowIndex)
                        {
                            if (worksheetPart.Worksheet.Elements <MergeCells>().Count() > 0)
                            {
                                // if (!merges.Any())
                                // {
                                var worksheet              = worksheetPart.Worksheet;
                                var mergeCells             = worksheet.Elements <MergeCells>().First();
                                var rowMergeCellsList      = new List <MergeCell>();
                                var mergeCellChildElements = mergeCells.ChildElements;
                                foreach (MergeCell item in mergeCellChildElements)
                                {
                                    var mergeCellReference = new MergeCellReference(item.Reference);
                                    if (mergeCellReference.CellFrom.RowIndex == row.RowIndex)
                                    {
                                        // merges.Add(item);
                                        var cellFrom = mergeCellReference.CellFrom;
                                        var cellTo   = mergeCellReference.CellTo;
                                        cellFrom.RowIndex = (int)generatedRowIndex.Value;
                                        cellTo.RowIndex   = (int)generatedRowIndex.Value;
                                        var mergeCell = new MergeCell {
                                            Reference = $"{cellFrom.Reference}:{cellTo.Reference}"
                                        };
                                        mergeCells.Append(mergeCell);
                                    }
                                }
                                // }
                                // else
                                // {
                                //     var worksheet = worksheetPart.Worksheet;
                                //     var mergeCells = worksheet.Elements<MergeCells>().First();
                                //     foreach (var merge in merges)
                                //     {
                                //         var mergeCellReference = new MergeCellReference(merge.Reference);
                                //
                                //         var cellFrom = mergeCellReference.CellFrom;
                                //         var cellTo = mergeCellReference.CellTo;
                                //         cellFrom.RowIndex = (int)generatedRowIndex.Value;
                                //         cellTo.RowIndex = (int)generatedRowIndex.Value;
                                //         var mergeCell = new MergeCell { Reference = $"{cellFrom.Reference}:{cellTo.Reference}" };
                                //         mergeCells.Append(mergeCell);
                                //     }
                                // }
                            }
                        }
                        generatedRowIndex++;
                    }

                    foreach (var tableNameForAddOneRow in tableNamesForAddOneRow.Distinct())
                    {
                        processedTablesRows[tableNameForAddOneRow]++;
                    }
                    row.Remove();
                }
            }
        }