如何使用OpenXML在Excel工作表中添加行 [英] How to add rows in excel sheet using OpenXML

问题描述

我在Excel工作表之间添加/插入新/现有行时遇到问题.
任何人都可以帮助我实现这一目标吗?

例如，我需要复制第10行并将其粘贴到第20行之后.

请帮忙.

谢谢和问候，
YKK Reddy

Hi,

I am facing a problem in adding/inserting new/existing rows in between a excel sheet.
Can anyone help me in achieving this ?

For example, i need to copy Row# 10 and paste it after Row# 20.

Kindly help.

Thanks and Regards,
YKK Reddy

推荐答案

private static Row CreateRow(Row refRow, SheetData sheetData, Row refRow1)
        {
            uint newRowIndex = 0;
            var newRow = new Row() { RowIndex = refRow.RowIndex.Value };

            // Loop through all the rows in the worksheet with higher row
            // index values than the one you just added. For each one,
            // increment the existing row index.
            IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value > refRow.RowIndex.Value);
            foreach (Row row in rows)
            {
                newRowIndex = System.Convert.ToUInt32(row.RowIndex.Value + 1);

                foreach (Cell cell in row.Elements<Cell>())
                {
                    // Update the references for reserved cells.
                    string cellReference = cell.CellReference.Value;
                    cell.CellReference = new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), newRowIndex.ToString()));
                }
                // Update the row index.
                row.RowIndex = new UInt32Value(newRowIndex);
            }

            //sheetData.InsertAt(newRow, 40);
            sheetData.InsertBefore(newRow, refRow);

            return newRow;
        }

Herez示例代码:

Herez sample code:

private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
        {
            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            string cellReference = columnName + rowIndex;

            // If the worksheet does not contain a row with the specified row index, insert one.
            Row row;
            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }

            // If there is not a cell with the specified column name, insert one.
            if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                Cell refCell = null;
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                    {
                        refCell = cell;
                        break;
                    }
                }

                Cell newCell = new Cell() { CellReference = cellReference };
                row.InsertBefore(newCell, refCell);

                worksheet.Save();
                return newCell;
            }
        }

这篇关于如何使用OpenXML在Excel工作表中添加行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！